"""
Station-to-station hand-over rules.

1. Operator marks work complete at station S  → a hand-over S → next(S) is created.
   - S has QA checksheets  → AWAITING_QA (chassis stays at S for QA)
   - S has no checksheets  → RELEASED (goes straight to the next station's queue)
2. QA passes every mandatory checksheet at S  → QA_PASSED; QA is asked to release it.
3. QA releases it                              → RELEASED; it appears at next(S).
4. Operator checks it in at next(S)            → RECEIVED.
The next station refuses a check-in until the hand-over to it is RELEASED.

next(S) follows the chassis's production BOM: the next active station (by sequence) at
which its BOM snapshot has parts. With no station data in the BOM it is simply the next
station by sequence. Chassis with no hand-over history (already on the floor before this
existed) are not blocked.
"""
from datetime import datetime, timezone
from typing import Optional, Tuple
from uuid import UUID

from fastapi import HTTPException
from sqlalchemy import select, desc
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.rm_models import StationMaster
from app.models.procurement_models import ChassisBomSnapshot
from app.models.qa_models import QaStationTemplateMap, QaChecksheetInstance
from app.models.station_work_models import ChassisStationHandoff

OPEN_STATUSES = ('AWAITING_QA', 'QA_PASSED', 'RELEASED')


def _now():
    return datetime.now(timezone.utc)


async def station_has_qa(db: AsyncSession, station_id: UUID) -> bool:
    found = (await db.execute(
        select(QaStationTemplateMap.map_id).where(QaStationTemplateMap.station_id == station_id).limit(1)
    )).first()
    return found is not None


async def next_station(db: AsyncSession, chassis_id: UUID, station: StationMaster) -> Optional[StationMaster]:
    if station.sequence_no is None:
        return None
    candidates = select(StationMaster).where(
        StationMaster.is_active == True,  # noqa: E712
        StationMaster.sequence_no > station.sequence_no,
    ).order_by(StationMaster.sequence_no)

    bom_stations = (await db.execute(
        select(ChassisBomSnapshot.station_id).where(
            ChassisBomSnapshot.chassis_id == chassis_id, ChassisBomSnapshot.station_id.isnot(None)
        ).distinct()
    )).scalars().all()
    if bom_stations:
        nxt = (await db.execute(candidates.where(StationMaster.station_id.in_(bom_stations)).limit(1))).scalars().first()
        if nxt:
            return nxt
    return (await db.execute(candidates.limit(1))).scalars().first()


async def latest_handoff(db: AsyncSession, chassis_id: UUID) -> Optional[ChassisStationHandoff]:
    return (await db.execute(
        select(ChassisStationHandoff).where(
            ChassisStationHandoff.chassis_id == chassis_id,
            ChassisStationHandoff.status != 'SUPERSEDED',
        ).order_by(desc(ChassisStationHandoff.completed_at)).limit(1)
    )).scalars().first()


async def station_name(db: AsyncSession, station_id: Optional[UUID]) -> str:
    if not station_id:
        return 'end of line'
    st = await db.get(StationMaster, station_id)
    return st.station_name if st else 'another station'


async def check_arrival(db: AsyncSession, chassis_id: UUID, station: StationMaster) -> Optional[ChassisStationHandoff]:
    """Raise 409 if the chassis may not be checked in at `station`; return the hand-over it fulfils."""
    h = await latest_handoff(db, chassis_id)
    if h is None or h.status == 'RECEIVED':
        return None  # no history, or already received somewhere: nothing pending to enforce
    if h.from_station_id == station.station_id and h.status in ('AWAITING_QA', 'QA_PASSED'):
        return None  # back at the same station for rework before release
    if h.status in ('AWAITING_QA', 'QA_PASSED'):
        where = await station_name(db, h.from_station_id)
        raise HTTPException(409, f"Not released yet: this chassis is waiting for QA at {where}.")
    # RELEASED: may be checked in at any station
    return h


async def mark_received(db: AsyncSession, handoff: Optional[ChassisStationHandoff]) -> None:
    if handoff is not None:
        handoff.status = 'RECEIVED'
        handoff.received_at = _now()


async def on_completion(db: AsyncSession, chassis_id: UUID, station: StationMaster, user_id: UUID) -> ChassisStationHandoff:
    # A repeat completion here (after rework) replaces any open hand-over from this station.
    open_here = (await db.execute(select(ChassisStationHandoff).where(
        ChassisStationHandoff.chassis_id == chassis_id,
        ChassisStationHandoff.from_station_id == station.station_id,
        ChassisStationHandoff.status.in_(OPEN_STATUSES),
    ))).scalars().all()
    for h in open_here:
        h.status = 'SUPERSEDED'

    nxt = await next_station(db, chassis_id, station)
    needs_qa = await station_has_qa(db, station.station_id)
    handoff = ChassisStationHandoff(
        chassis_id=chassis_id, from_station_id=station.station_id,
        to_station_id=nxt.station_id if nxt else None,
        status='AWAITING_QA' if needs_qa else 'RELEASED',
        completed_by=user_id, completed_at=_now(),
        released_at=None if needs_qa else _now(), released_by=None if needs_qa else user_id,
    )
    db.add(handoff)
    return handoff


async def open_handoff_from(db: AsyncSession, chassis_id: UUID, station_id: UUID) -> Optional[ChassisStationHandoff]:
    return (await db.execute(select(ChassisStationHandoff).where(
        ChassisStationHandoff.chassis_id == chassis_id,
        ChassisStationHandoff.from_station_id == station_id,
        ChassisStationHandoff.status.in_(('AWAITING_QA', 'QA_PASSED', 'RELEASED')),
    ).order_by(desc(ChassisStationHandoff.completed_at)).limit(1))).scalars().first()


async def set_qa_outcome(db: AsyncSession, chassis_id: UUID, station_id: UUID, all_passed: bool) -> Optional[ChassisStationHandoff]:
    """After a QA sign-off: QA_PASSED when everything mandatory passed, else back to AWAITING_QA."""
    h = await open_handoff_from(db, chassis_id, station_id)
    if h is not None:
        h.status = 'QA_PASSED' if all_passed else 'AWAITING_QA'
    elif all_passed:
        st = await db.get(StationMaster, station_id)
        if st:
            nxt = await next_station(db, chassis_id, st)
            h = ChassisStationHandoff(
                chassis_id=chassis_id,
                from_station_id=station_id,
                to_station_id=nxt.station_id if nxt else None,
                status='QA_PASSED',
                completed_at=_now(),
            )
            db.add(h)
    return h


async def release(db: AsyncSession, chassis_id: UUID, station_id: UUID, user_id: UUID, remarks: Optional[str]) -> Tuple[ChassisStationHandoff, str]:
    h = await open_handoff_from(db, chassis_id, station_id)
    st = await db.get(StationMaster, station_id)
    if h is None:
        nxt = await next_station(db, chassis_id, st) if st else None
        h = ChassisStationHandoff(
            chassis_id=chassis_id,
            from_station_id=station_id,
            to_station_id=nxt.station_id if nxt else None,
            status='RELEASED',
            completed_by=user_id,
            completed_at=_now(),
            released_at=_now(),
            released_by=user_id,
        )
        db.add(h)
    else:
        h.status = 'RELEASED'
        h.released_at = _now()
        h.released_by = user_id
    if remarks:
        h.remarks = remarks
    return h, await station_name(db, h.to_station_id)


async def is_qa_cleared_at_station(db: AsyncSession, chassis_id: UUID, station_id: UUID) -> Tuple[bool, str]:
    """
    Check if a station requires QA clearance before 'Chassis Out' or downstream 'Chassis In'.
    Returns (cleared: bool, reason: str).
    """
    station = await db.get(StationMaster, station_id)
    if not station or not station.requires_qa:
        return True, "Station does not require QA"

    has_qa = await station_has_qa(db, station_id)
    if not has_qa:
        return True, "No QA checksheets mapped to station"

    # Check if handoff was released or marked QA_PASSED
    h = await open_handoff_from(db, chassis_id, station_id)
    if h and h.status in ('QA_PASSED', 'RELEASED'):
        return True, "QA clearance granted"

    # Check if all instances for this station are completed and passed
    instances = (await db.execute(
        select(QaChecksheetInstance).where(
            QaChecksheetInstance.chassis_id == chassis_id,
            QaChecksheetInstance.station_id == station_id,
        )
    )).scalars().all()
    if not instances:
        return False, f"QA inspection has not been completed at {station.station_name}"

    if all(inst.overall_result == 'PASSED' or inst.station_qa_status == 'PASSED' for inst in instances):
        return True, "All QA checksheets passed"

    return False, f"QA clearance for Chassis Out has not been granted at {station.station_name}"