""" Production Dashboard — aggregation service. Computes KPIs, station pipeline counts with exact IN/OUT breakdown, chassis list with live station status and dwell timers, and today's activity. """ from datetime import date, datetime, timezone from sqlalchemy import select, func, and_ from sqlalchemy.ext.asyncio import AsyncSession from app.models.yard_models import ChassisMaster, ChassisStatusLog, VehicleModelMaster from app.models.rm_models import StationMaster from app.models.location_models import ChassisLocationMaster def _fmt_minutes(mins: int | None) -> str: if mins is None or mins < 0: return "0m" hours = mins // 60 rem = mins % 60 if hours > 0: return f"{hours}h {rem}m" return f"{rem}m" async def get_production_dashboard(db: AsyncSession) -> dict: """Return the full production dashboard payload.""" today = date.today() now_utc = datetime.now(timezone.utc) # ── KPI Counts ───────────────────────────────────────────────── kpi_q = await db.execute( select( func.count().filter(ChassisMaster.tracking_status == 'WIP').label('wip'), func.count().filter( and_( ChassisMaster.current_status == 'HANDED_TO_PRODUCTION', ChassisMaster.tracking_status == 'CHASSIS_IN', ) ).label('awaiting_induction'), func.count().filter(ChassisMaster.tracking_status == 'READY_FOR_DISPATCH').label('ready_for_dispatch'), func.count().filter( and_( ChassisMaster.tracking_status == 'DISPATCHED', func.date(ChassisMaster.dispatched_at) == today, ) ).label('dispatched_today'), func.count().filter(ChassisMaster.tracking_status == 'CHASSIS_IN').label('in_yard'), func.count().label('total'), ) ) row = kpi_q.one() kpis = { 'wip': row.wip, 'in_production': row.wip, 'on_floor': row.wip, 'awaiting_induction': row.awaiting_induction, 'ready_for_dispatch': row.ready_for_dispatch, 'dispatched_today': row.dispatched_today, 'in_yard': row.in_yard, 'total': row.total, } # ── Station Pipeline with IN / OUT breakdown ────────────────── stations_q = await db.execute( select( StationMaster.station_id, StationMaster.station_code, StationMaster.station_name, StationMaster.sequence_no, func.count().filter( and_( ChassisMaster.current_station_id == StationMaster.station_id, ChassisMaster.station_arrived_at.isnot(None), ChassisMaster.station_completed_at.is_(None), ChassisMaster.tracking_status == 'WIP', ) ).label('in_count'), func.count().filter( and_( ChassisMaster.current_station_id == StationMaster.station_id, ChassisMaster.station_completed_at.isnot(None), ChassisMaster.tracking_status == 'WIP', ) ).label('out_count'), ) .outerjoin( ChassisMaster, ChassisMaster.current_station_id == StationMaster.station_id, ) .where(StationMaster.is_active == True) .group_by( StationMaster.station_id, StationMaster.station_code, StationMaster.station_name, StationMaster.sequence_no, ) .order_by(StationMaster.sequence_no) ) station_pipeline = [ { 'station_id': str(r.station_id), 'station_code': r.station_code, 'station_name': r.station_name, 'sequence_no': r.sequence_no or 0, 'in_count': r.in_count, 'out_count': r.out_count, 'chassis_count': r.in_count + r.out_count, } for r in stations_q.all() ] # ── Active Chassis List with Exact Station Name & IN/OUT Status ── active_q = await db.execute( select( ChassisMaster.chassis_id, ChassisMaster.chassis_no, ChassisMaster.fuel_type, ChassisMaster.tracking_status, ChassisMaster.current_status, ChassisMaster.updated_at, ChassisMaster.station_arrived_at, ChassisMaster.station_completed_at, ChassisMaster.handed_to_production_at, VehicleModelMaster.model_number, StationMaster.station_name.label('current_station_name'), StationMaster.station_code.label('current_station_code'), ChassisLocationMaster.location_name.label('current_location_name'), ) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) .outerjoin(StationMaster, ChassisMaster.current_station_id == StationMaster.station_id) .outerjoin(ChassisLocationMaster, ChassisMaster.current_location_id == ChassisLocationMaster.location_id) .where( ChassisMaster.tracking_status.in_(['WIP', 'READY_FOR_DISPATCH']) ) .order_by(ChassisMaster.updated_at.desc()) .limit(50) ) chassis_list = [] for r in active_q.all(): st_name = r.current_station_name dwell_mins = None dwell_fmt = None if st_name and r.station_arrived_at and not r.station_completed_at: st_state = "IN" st_status = f"{st_name} IN" dwell_mins = max(0, int((now_utc - r.station_arrived_at).total_seconds() / 60)) dwell_fmt = _fmt_minutes(dwell_mins) elif st_name and r.station_completed_at: st_state = "OUT" st_status = f"{st_name} OUT" arr = r.station_arrived_at or r.station_completed_at dwell_mins = max(0, int((r.station_completed_at - arr).total_seconds() / 60)) dwell_fmt = _fmt_minutes(dwell_mins) elif r.tracking_status == 'READY_FOR_DISPATCH': st_state = "READY" st_status = "Ready for Dispatch" else: st_state = "PENDING" st_status = "Awaiting Station 1" chassis_list.append({ 'chassis_id': str(r.chassis_id), 'chassis_no': r.chassis_no, 'fuel_type': r.fuel_type, 'tracking_status': r.tracking_status, 'current_status': r.current_status, 'model_number': r.model_number or '—', 'current_station_name': st_name or '—', 'current_station_code': r.current_station_code or '—', 'station_state': st_state, 'station_status': st_status, 'dwell_minutes': dwell_mins, 'dwell_formatted': dwell_fmt, 'current_location_name': r.current_location_name or '—', 'last_updated': r.updated_at.isoformat() if r.updated_at else None, 'handed_to_production_at': r.handed_to_production_at.isoformat() if r.handed_to_production_at else None, }) # ── Today's Activity Feed (last 20 scans) ────────────────────── activity_q = await db.execute( select( ChassisStatusLog.log_id, ChassisStatusLog.from_status, ChassisStatusLog.to_status, ChassisStatusLog.station_code, ChassisStatusLog.scan_location, ChassisStatusLog.changed_at, ChassisStatusLog.remarks, ChassisMaster.chassis_no, ) .join(ChassisMaster, ChassisStatusLog.chassis_id == ChassisMaster.chassis_id) .where(func.date(ChassisStatusLog.changed_at) == today) .order_by(ChassisStatusLog.changed_at.desc()) .limit(20) ) activity_feed = [ { 'log_id': str(r.log_id), 'chassis_no': r.chassis_no, 'from_status': r.from_status, 'to_status': r.to_status, 'station_code': r.station_code or '—', 'scan_location': r.scan_location or '—', 'changed_at': r.changed_at.isoformat() if r.changed_at else None, 'remarks': r.remarks, } for r in activity_q.all() ] return { 'kpis': kpis, 'station_pipeline': station_pipeline, 'chassis_list': chassis_list, 'activity_feed': activity_feed, }