"""
Production Tracking service — scan logic, hold management, overdue detection, dashboard.
"""
import uuid
from datetime import datetime, timezone
from typing import Optional, List
from uuid import UUID

from sqlalchemy import select, func, case
from sqlalchemy.ext.asyncio import AsyncSession
from fastapi import HTTPException

from app.models.production_models import (
    DelayReasonMaster,
    ChassisStationLog,
    ProductionHold,
)
from app.models.yard_models import ChassisMaster, VehicleModelMaster
from app.models.rm_models import StationMaster
from . import schemas
from app.modules.station_work import flow


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


def _fmt_minutes(minutes: int) -> str:
    hours, mins = divmod(int(minutes or 0), 60)
    return f"{hours}h {mins}m" if hours else f"{mins}m"


def _elapsed(start: datetime) -> int:
    """Minutes elapsed since `start`."""
    return max(0, int((_now() - start).total_seconds() / 60))


# ═══════════════════════════════════════════════════════════
# SCAN
# ═══════════════════════════════════════════════════════════
async def scan_chassis(
    db: AsyncSession,
    req: schemas.ScanRequest,
    user_id: UUID,
) -> schemas.ScanResponse:
    """
    Dual-scan logic:
    - No open ARRIVAL at this station → record ARRIVAL
    - Open ARRIVAL at this station → record COMPLETION
    Also auto-closes any open log at OTHER stations.
    """
    # 1. Find chassis
    chassis = (
        await db.execute(
            select(ChassisMaster).where(ChassisMaster.chassis_no == req.chassis_no)
        )
    ).scalars().first()
    if not chassis:
        raise HTTPException(404, f"Chassis '{req.chassis_no}' not found")

    # 2. Reject if on hold
    if chassis.production_hold_status == "ON_HOLD":
        raise HTTPException(
            409,
            f"Chassis '{req.chassis_no}' is ON HOLD. Resolve hold before scanning.",
        )

    # 3. Get station
    station = await db.get(StationMaster, req.station_id)
    if not station:
        raise HTTPException(404, "Station not found")

    # 4. Check for open ARRIVAL at THIS station
    open_log_here = (
        await db.execute(
            select(ChassisStationLog).where(
                ChassisStationLog.chassis_id == chassis.chassis_id,
                ChassisStationLog.station_id == req.station_id,
                ChassisStationLog.event_type == "ARRIVAL",
                ChassisStationLog.status == "AT_STATION",
            )
        )
    ).scalars().first()

    if open_log_here:
        # ── COMPLETION scan ──
        elapsed = _elapsed(open_log_here.scanned_at)
        is_overdue = (
            station.standard_cycle_time_min is not None
            and elapsed > station.standard_cycle_time_min
        )

        completion_log = ChassisStationLog(
            log_id=uuid.uuid4(),
            chassis_id=chassis.chassis_id,
            station_id=req.station_id,
            event_type="COMPLETION",
            scanned_at=_now(),
            scanned_by=user_id,
            scan_mode=req.scan_mode,
            elapsed_minutes=elapsed,
            is_overdue=is_overdue,
            status="COMPLETED",
            remarks=req.remarks,
        )
        db.add(completion_log)

        # Close the arrival log
        open_log_here.status = "COMPLETED"
        open_log_here.elapsed_minutes = elapsed
        open_log_here.is_overdue = is_overdue

        # Update chassis
        chassis.station_completed_at = _now()
        handoff = await flow.on_completion(db, chassis.chassis_id, station, user_id)
        if handoff.status == 'AWAITING_QA':
            next_step = "Waiting for QA before it moves on"
        else:
            next_step = f"Passed to {await flow.station_name(db, handoff.to_station_id)}"

        await db.commit()
        return schemas.ScanResponse(
            event_type="COMPLETION",
            chassis_no=chassis.chassis_no,
            station_name=station.station_name,
            message=f"Completion recorded at {station.station_name} after {_fmt_minutes(elapsed)}. {next_step}.",
            log_id=completion_log.log_id,
            elapsed_minutes=elapsed,
            is_overdue=is_overdue,
        )

    else:
        # ── ARRIVAL scan (IN) ──
        # ── VALIDATION 1: Has chassis already completed this station? ──
        completed_log_here = (
            await db.execute(
                select(ChassisStationLog).where(
                    ChassisStationLog.chassis_id == chassis.chassis_id,
                    ChassisStationLog.station_id == req.station_id,
                    ChassisStationLog.event_type == "COMPLETION",
                ).order_by(ChassisStationLog.scanned_at.desc()).limit(1)
            )
        ).scalars().first()

        is_already_completed = (
            completed_log_here is not None
            or (chassis.current_station_id == station.station_id and chassis.station_completed_at is not None)
        )

        if is_already_completed:
            comp_dt = (
                chassis.station_completed_at
                if (chassis.current_station_id == station.station_id and chassis.station_completed_at)
                else (completed_log_here.scanned_at if completed_log_here else None)
            )
            time_str = f" at {format_ist_time(comp_dt)}" if comp_dt else ""
            raise HTTPException(
                status_code=400,
                detail=f"Chassis #{chassis.chassis_no} has already completed {station.station_name}{time_str}.",
            )

        # QA-gating & handoff: prevent checking in if awaiting QA at the station it just finished
        pending_handoff = await flow.check_arrival(db, chassis.chassis_id, station)
        await flow.mark_received(db, pending_handoff)

        # Auto-close any open arrival logs at other stations
        open_elsewhere = (
            await db.execute(
                select(ChassisStationLog).where(
                    ChassisStationLog.chassis_id == chassis.chassis_id,
                    ChassisStationLog.station_id != req.station_id,
                    ChassisStationLog.event_type == "ARRIVAL",
                    ChassisStationLog.status == "AT_STATION",
                )
            )
        ).scalars().all()

        for old_log in open_elsewhere:
            elapsed = _elapsed(old_log.scanned_at)
            old_log.status = "COMPLETED"
            old_log.elapsed_minutes = elapsed
            old_station = await db.get(StationMaster, old_log.station_id)
            if old_station and old_station.standard_cycle_time_min and old_station.standard_cycle_time_min > 0:
                old_log.is_overdue = elapsed > old_station.standard_cycle_time_min

        # Auto-close any open logs at OTHER stations
        open_elsewhere = (
            await db.execute(
                select(ChassisStationLog).where(
                    ChassisStationLog.chassis_id == chassis.chassis_id,
                    ChassisStationLog.event_type == "ARRIVAL",
                    ChassisStationLog.status == "AT_STATION",
                    ChassisStationLog.station_id != req.station_id,
                )
            )
        ).scalars().all()

        for old_log in open_elsewhere:
            elapsed = _elapsed(old_log.scanned_at)
            old_log.status = "COMPLETED"
            old_log.elapsed_minutes = elapsed
            # Determine overdue from the old station's cycle time
            old_station = await db.get(StationMaster, old_log.station_id)
            if old_station and old_station.standard_cycle_time_min:
                old_log.is_overdue = elapsed > old_station.standard_cycle_time_min

        arrival_log = ChassisStationLog(
            log_id=uuid.uuid4(),
            chassis_id=chassis.chassis_id,
            station_id=req.station_id,
            event_type="ARRIVAL",
            scanned_at=_now(),
            scanned_by=user_id,
            scan_mode=req.scan_mode,
            status="AT_STATION",
            remarks=req.remarks,
        )
        db.add(arrival_log)

        # Update chassis tracking
        chassis.current_station_id = req.station_id
        chassis.station_arrived_at = _now()
        chassis.station_completed_at = None
        chassis.tracking_status = "WIP"

        await db.commit()
        return schemas.ScanResponse(
            event_type="ARRIVAL",
            chassis_no=chassis.chassis_no,
            station_name=station.station_name,
            message=f"Arrival recorded at {station.station_name}",
            log_id=arrival_log.log_id,
        )


# ═══════════════════════════════════════════════════════════
# DASHBOARD
# ═══════════════════════════════════════════════════════════
async def get_dashboard(db: AsyncSession) -> schemas.DashboardResponse:
    now = _now()
    today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)

    # KPIs
    wip = (await db.execute(
        select(func.count()).select_from(ChassisMaster).where(
            ChassisMaster.tracking_status == "WIP"
        )
    )).scalar() or 0

    completed_today = (await db.execute(
        select(func.count()).select_from(ChassisStationLog).where(
            ChassisStationLog.event_type == "COMPLETION",
            ChassisStationLog.scanned_at >= today_start,
        )
    )).scalar() or 0

    on_hold = (await db.execute(
        select(func.count()).select_from(ChassisMaster).where(
            ChassisMaster.production_hold_status == "ON_HOLD"
        )
    )).scalar() or 0

    # Get all active stations
    stations_result = await db.execute(
        select(StationMaster).where(StationMaster.is_active == True).order_by(StationMaster.sequence_no)
    )
    stations = stations_result.scalars().all()

    station_views = []
    total_overdue = 0
    placed_ids = set()

    for st in stations:
        # Open arrival logs at this station
        open_logs = (await db.execute(
            select(ChassisStationLog, ChassisMaster, VehicleModelMaster.model_number).join(
                ChassisMaster, ChassisMaster.chassis_id == ChassisStationLog.chassis_id
            ).outerjoin(
                VehicleModelMaster, VehicleModelMaster.model_id == ChassisMaster.model_id
            ).where(
                ChassisStationLog.station_id == st.station_id,
                ChassisStationLog.event_type == "ARRIVAL",
                ChassisStationLog.status == "AT_STATION",
            )
        )).all()

        chassis_list = []
        overdue_count = 0

        for log, cm, model_name in open_logs:
            placed_ids.add(cm.chassis_id)
            elapsed = _elapsed(log.scanned_at)
            is_overdue = bool(
                st.standard_cycle_time_min is not None
                and st.standard_cycle_time_min > 0
                and elapsed > st.standard_cycle_time_min
            )
            is_warning = bool(
                not is_overdue
                and st.alert_threshold_min is not None
                and st.alert_threshold_min > 0
                and elapsed > st.alert_threshold_min
            )
            if is_overdue:
                overdue_count += 1

            # Check for delay reason
            delay_label = None
            if log.delay_reason_id:
                dr = await db.get(DelayReasonMaster, log.delay_reason_id)
                if dr:
                    delay_label = dr.reason_label

            chassis_list.append(schemas.ChassisAtStation(
                chassis_id=cm.chassis_id,
                chassis_no=cm.chassis_no,
                log_id=log.log_id,
                arrived_at=log.scanned_at,
                elapsed_minutes=elapsed,
                cycle_time_min=st.standard_cycle_time_min,
                is_overdue=is_overdue,
                is_warning=is_warning,
                hold_status=cm.production_hold_status or "NONE",
                delay_reason=delay_label,
                model_name=model_name,
            ))

        total_overdue += overdue_count

        station_views.append(schemas.StationLiveView(
            station_id=st.station_id,
            station_name=st.station_name,
            station_code=st.station_code,
            sequence_no=st.sequence_no,
            requires_qa=bool(st.requires_qa),
            cycle_time_min=st.standard_cycle_time_min,
            alert_threshold_min=st.alert_threshold_min,
            chassis_count=len(chassis_list),
            overdue_count=overdue_count,
            chassis_list=chassis_list,
        ))

    # WIP chassis with no open station check-in (e.g. marked "In production" by the
    # yard scanner but not yet scanned at a production station).
    wip_rows = (await db.execute(
        select(ChassisMaster, VehicleModelMaster.model_number).outerjoin(
            VehicleModelMaster, VehicleModelMaster.model_id == ChassisMaster.model_id
        ).where(ChassisMaster.tracking_status == "WIP").order_by(ChassisMaster.chassis_no)
    )).all()
    unplaced = [
        schemas.UnplacedChassis(
            chassis_id=cm.chassis_id,
            chassis_no=cm.chassis_no,
            model_name=model_name,
            hold_status=cm.production_hold_status or "NONE",
            since=cm.station_completed_at or cm.handed_to_production_at or cm.updated_at,
        )
        for cm, model_name in wip_rows if cm.chassis_id not in placed_ids
    ]

    return schemas.DashboardResponse(
        kpis=schemas.DashboardKPIs(
            wip_count=wip,
            completed_today=completed_today,
            on_hold_count=on_hold,
            overdue_count=total_overdue,
        ),
        stations=station_views,
        unplaced=unplaced,
    )


# ═══════════════════════════════════════════════════════════
# CHASSIS TIMELINE
# ═══════════════════════════════════════════════════════════
async def get_chassis_timeline(
    db: AsyncSession, chassis_no: str
) -> List[schemas.TimelineEntry]:
    chassis = (await db.execute(
        select(ChassisMaster).where(ChassisMaster.chassis_no == chassis_no)
    )).scalars().first()
    if not chassis:
        raise HTTPException(404, "Chassis not found")

    logs = (await db.execute(
        select(ChassisStationLog, StationMaster).join(
            StationMaster, StationMaster.station_id == ChassisStationLog.station_id
        ).where(
            ChassisStationLog.chassis_id == chassis.chassis_id
        ).order_by(ChassisStationLog.scanned_at)
    )).all()

    entries = []
    for log, st in logs:
        delay_label = None
        if log.delay_reason_id:
            dr = await db.get(DelayReasonMaster, log.delay_reason_id)
            if dr:
                delay_label = dr.reason_label

        entries.append(schemas.TimelineEntry(
            log_id=log.log_id,
            station_id=st.station_id,
            station_name=st.station_name,
            event_type=log.event_type,
            scanned_at=log.scanned_at,
            elapsed_minutes=log.elapsed_minutes,
            is_overdue=log.is_overdue,
            delay_reason=delay_label,
            delay_remarks=log.delay_remarks,
            status=log.status,
        ))
    return entries


# ═══════════════════════════════════════════════════════════
# OVERDUE
# ═══════════════════════════════════════════════════════════
async def get_overdue_chassis(db: AsyncSession) -> List[schemas.OverdueEntry]:
    # All open arrival logs where elapsed > cycle_time
    stations = (await db.execute(
        select(StationMaster).where(StationMaster.is_active == True)
    )).scalars().all()

    overdue_list = []
    for st in stations:
        if not st.standard_cycle_time_min or st.standard_cycle_time_min <= 0:
            continue

        open_logs = (await db.execute(
            select(ChassisStationLog, ChassisMaster).join(
                ChassisMaster, ChassisMaster.chassis_id == ChassisStationLog.chassis_id
            ).where(
                ChassisStationLog.station_id == st.station_id,
                ChassisStationLog.event_type == "ARRIVAL",
                ChassisStationLog.status == "AT_STATION",
            )
        )).all()

        for log, cm in open_logs:
            elapsed = _elapsed(log.scanned_at)
            if elapsed > st.standard_cycle_time_min:
                delay_label = None
                if log.delay_reason_id:
                    dr = await db.get(DelayReasonMaster, log.delay_reason_id)
                    if dr:
                        delay_label = dr.reason_label

                overdue_list.append(schemas.OverdueEntry(
                    chassis_id=cm.chassis_id,
                    chassis_no=cm.chassis_no,
                    station_name=st.station_name,
                    arrived_at=log.scanned_at,
                    elapsed_minutes=elapsed,
                    cycle_time_min=st.standard_cycle_time_min,
                    delay_reason=delay_label,
                    delay_remarks=log.delay_remarks,
                    delay_logged=log.delay_reason_id is not None,
                ))

    return sorted(overdue_list, key=lambda x: -x.elapsed_minutes)


# ═══════════════════════════════════════════════════════════
# DELAY LOG
# ═══════════════════════════════════════════════════════════
async def log_delay(
    db: AsyncSession,
    log_id: UUID,
    req: schemas.DelayLogRequest,
    user_id: UUID,
) -> dict:
    log_entry = await db.get(ChassisStationLog, log_id)
    if not log_entry:
        raise HTTPException(404, "Station log not found")

    log_entry.delay_reason_id = req.delay_reason_id
    log_entry.delay_remarks = req.delay_remarks
    log_entry.delay_logged_by = user_id
    log_entry.delay_logged_at = _now()

    await db.commit()
    return {"message": "Delay reason logged successfully", "log_id": str(log_id)}


# ═══════════════════════════════════════════════════════════
# HOLDS
# ═══════════════════════════════════════════════════════════
async def create_hold(
    db: AsyncSession,
    req: schemas.HoldCreate,
    user_id: UUID,
) -> schemas.HoldOut:
    chassis = (await db.execute(
        select(ChassisMaster).where(ChassisMaster.chassis_no == req.chassis_no)
    )).scalars().first()
    if not chassis:
        raise HTTPException(404, "Chassis not found")

    hold = ProductionHold(
        hold_id=uuid.uuid4(),
        chassis_id=chassis.chassis_id,
        station_id=req.station_id,
        hold_type=req.hold_type,
        hold_reason=req.hold_reason,
        raised_by=user_id,
        raised_at=_now(),
        status="ACTIVE",
    )
    db.add(hold)
    chassis.production_hold_status = "ON_HOLD"
    await db.commit()

    station_name = None
    if hold.station_id:
        st = await db.get(StationMaster, hold.station_id)
        if st:
            station_name = st.station_name

    return schemas.HoldOut(
        hold_id=hold.hold_id,
        chassis_id=hold.chassis_id,
        chassis_no=chassis.chassis_no,
        station_id=hold.station_id,
        station_name=station_name,
        hold_type=hold.hold_type,
        hold_reason=hold.hold_reason,
        raised_by=hold.raised_by,
        raised_at=hold.raised_at,
        status=hold.status,
    )


async def resolve_hold(
    db: AsyncSession,
    hold_id: UUID,
    req: schemas.HoldResolve,
    user_id: UUID,
) -> schemas.HoldOut:
    hold = await db.get(ProductionHold, hold_id)
    if not hold:
        raise HTTPException(404, "Hold not found")
    if hold.status != "ACTIVE":
        raise HTTPException(409, "Hold is already resolved")

    hold.resolved_by = user_id
    hold.resolved_at = _now()
    hold.resolution_notes = req.resolution_notes
    hold.status = "RESOLVED"

    # Check if chassis has any other active holds
    other_active = (await db.execute(
        select(func.count()).select_from(ProductionHold).where(
            ProductionHold.chassis_id == hold.chassis_id,
            ProductionHold.status == "ACTIVE",
            ProductionHold.hold_id != hold_id,
        )
    )).scalar() or 0

    chassis = await db.get(ChassisMaster, hold.chassis_id)
    if chassis and other_active == 0:
        chassis.production_hold_status = "RELEASED"

    await db.commit()

    station_name = None
    if hold.station_id:
        st = await db.get(StationMaster, hold.station_id)
        if st:
            station_name = st.station_name

    return schemas.HoldOut(
        hold_id=hold.hold_id,
        chassis_id=hold.chassis_id,
        chassis_no=chassis.chassis_no if chassis else None,
        station_id=hold.station_id,
        station_name=station_name,
        hold_type=hold.hold_type,
        hold_reason=hold.hold_reason,
        raised_by=hold.raised_by,
        raised_at=hold.raised_at,
        resolved_by=hold.resolved_by,
        resolved_at=hold.resolved_at,
        resolution_notes=hold.resolution_notes,
        status=hold.status,
    )


async def list_holds(
    db: AsyncSession,
    status: Optional[str] = None,
) -> List[schemas.HoldOut]:
    q = select(ProductionHold, ChassisMaster).join(
        ChassisMaster, ChassisMaster.chassis_id == ProductionHold.chassis_id
    )
    if status:
        q = q.where(ProductionHold.status == status)
    q = q.order_by(ProductionHold.raised_at.desc())

    results = (await db.execute(q)).all()
    out = []
    for hold, cm in results:
        station_name = None
        if hold.station_id:
            st = await db.get(StationMaster, hold.station_id)
            if st:
                station_name = st.station_name
        out.append(schemas.HoldOut(
            hold_id=hold.hold_id,
            chassis_id=hold.chassis_id,
            chassis_no=cm.chassis_no,
            station_id=hold.station_id,
            station_name=station_name,
            hold_type=hold.hold_type,
            hold_reason=hold.hold_reason,
            raised_by=hold.raised_by,
            raised_at=hold.raised_at,
            resolved_by=hold.resolved_by,
            resolved_at=hold.resolved_at,
            resolution_notes=hold.resolution_notes,
            status=hold.status,
        ))
    return out


# ═══════════════════════════════════════════════════════════
# STATION LIVE VIEW
# ═══════════════════════════════════════════════════════════
async def get_station_live(
    db: AsyncSession, station_id: UUID
) -> schemas.StationLiveView:
    st = await db.get(StationMaster, station_id)
    if not st:
        raise HTTPException(404, "Station not found")

    open_logs = (await db.execute(
        select(ChassisStationLog, ChassisMaster).join(
            ChassisMaster, ChassisMaster.chassis_id == ChassisStationLog.chassis_id
        ).where(
            ChassisStationLog.station_id == station_id,
            ChassisStationLog.event_type == "ARRIVAL",
            ChassisStationLog.status == "AT_STATION",
        )
    )).all()

    chassis_list = []
    overdue_count = 0
    for log, cm in open_logs:
        elapsed = _elapsed(log.scanned_at)
        is_overdue = (
            st.standard_cycle_time_min is not None
            and elapsed > st.standard_cycle_time_min
        )
        is_warning = (
            not is_overdue
            and st.alert_threshold_min is not None
            and elapsed > st.alert_threshold_min
        )
        if is_overdue:
            overdue_count += 1

        delay_label = None
        if log.delay_reason_id:
            dr = await db.get(DelayReasonMaster, log.delay_reason_id)
            if dr:
                delay_label = dr.reason_label

        chassis_list.append(schemas.ChassisAtStation(
            chassis_id=cm.chassis_id,
            chassis_no=cm.chassis_no,
            log_id=log.log_id,
            arrived_at=log.scanned_at,
            elapsed_minutes=elapsed,
            cycle_time_min=st.standard_cycle_time_min,
            is_overdue=is_overdue,
            is_warning=is_warning,
            hold_status=cm.production_hold_status or "NONE",
            delay_reason=delay_label,
        ))

    return schemas.StationLiveView(
        station_id=st.station_id,
        station_name=st.station_name,
        station_code=st.station_code,
        cycle_time_min=st.standard_cycle_time_min,
        alert_threshold_min=st.alert_threshold_min,
        chassis_count=len(chassis_list),
        overdue_count=overdue_count,
        chassis_list=chassis_list,
    )


# ═══════════════════════════════════════════════════════════
# DELAY REASON CRUD
# ═══════════════════════════════════════════════════════════
async def list_reason_codes(db: AsyncSession) -> List[schemas.DelayReasonOut]:
    result = await db.execute(
        select(DelayReasonMaster).order_by(DelayReasonMaster.reason_code)
    )
    return [schemas.DelayReasonOut.model_validate(r) for r in result.scalars().all()]


async def create_reason_code(
    db: AsyncSession, req: schemas.DelayReasonCreate
) -> schemas.DelayReasonOut:
    dr = DelayReasonMaster(
        delay_reason_id=uuid.uuid4(),
        reason_code=req.reason_code,
        reason_label=req.reason_label,
        category=req.category,
    )
    db.add(dr)
    await db.commit()
    await db.refresh(dr)
    return schemas.DelayReasonOut.model_validate(dr)


async def update_reason_code(
    db: AsyncSession, reason_id: UUID, req: schemas.DelayReasonUpdate
) -> schemas.DelayReasonOut:
    dr = await db.get(DelayReasonMaster, reason_id)
    if not dr:
        raise HTTPException(404, "Delay reason not found")
    if req.reason_label is not None:
        dr.reason_label = req.reason_label
    if req.category is not None:
        dr.category = req.category
    if req.is_active is not None:
        dr.is_active = req.is_active
    await db.commit()
    await db.refresh(dr)
    return schemas.DelayReasonOut.model_validate(dr)


async def delete_reason_code(db: AsyncSession, reason_id: UUID) -> None:
    dr = await db.get(DelayReasonMaster, reason_id)
    if not dr:
        raise HTTPException(404, "Delay reason not found")
    dr.is_active = False
    await db.commit()


# ═══════════════════════════════════════════════════════════
# BOTTLENECK ANALYTICS
# ═══════════════════════════════════════════════════════════
async def get_bottleneck_analytics(
    db: AsyncSession,
) -> List[schemas.StationBottleneck]:
    # Aggregate completed logs per station
    result = await db.execute(
        select(
            ChassisStationLog.station_id,
            StationMaster.station_name,
            StationMaster.standard_cycle_time_min,
            func.avg(ChassisStationLog.elapsed_minutes).label("avg_elapsed"),
            func.count().label("total"),
            func.sum(
                case((ChassisStationLog.is_overdue == True, 1), else_=0)
            ).label("overdue_cnt"),
        )
        .join(StationMaster, StationMaster.station_id == ChassisStationLog.station_id)
        .where(ChassisStationLog.event_type == "COMPLETION")
        .group_by(
            ChassisStationLog.station_id,
            StationMaster.station_name,
            StationMaster.standard_cycle_time_min,
        )
        .order_by(func.avg(ChassisStationLog.elapsed_minutes).desc())
    )

    out = []
    for row in result.all():
        total = row.total or 1
        out.append(schemas.StationBottleneck(
            station_id=row.station_id,
            station_name=row.station_name,
            avg_elapsed_min=round(float(row.avg_elapsed or 0), 1),
            cycle_time_min=row.standard_cycle_time_min,
            total_completions=total,
            overdue_count=row.overdue_cnt or 0,
            overdue_pct=round((row.overdue_cnt or 0) / total * 100, 1),
        ))
    return out