"""
Sales dashboard: live pipeline position of every chassis plus period figures.

Live figures (pipeline, holds, ready for dispatch) describe the plant right now. Period
figures (arrivals, dispatches, dealers, model mix, activity) use the selected date range.
Stations come from station_master in sequence order, so the pipeline follows the plant
layout configured in the database.
"""
from datetime import date, datetime, time, timezone

from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.yard_models import ChassisMaster, ChassisStatusLog, VehicleModelMaster
from app.models.production_models import ChassisStationLog
from app.models.rm_models import StationMaster
from app.models.sales_models import CustomerMaster

TOP_DEALER_LIMIT = 8
ACTIVITY_LIMIT = 12


def _period_bounds(start: date, end: date) -> tuple[datetime, datetime]:
    return (
        datetime.combine(start, time.min, tzinfo=timezone.utc),
        datetime.combine(end, time.max, tzinfo=timezone.utc),
    )


async def _count(db: AsyncSession, *conditions) -> int:
    return (await db.execute(select(func.count()).select_from(ChassisMaster).where(*conditions))).scalar() or 0


async def build_dashboard(db: AsyncSession, start: date, end: date) -> dict:
    period_from, period_to = _period_bounds(start, end)
    not_dispatched = ChassisMaster.tracking_status != 'DISPATCHED'

    kpis = {
        'in_yard': await _count(db, ChassisMaster.tracking_status == 'CHASSIS_IN'),
        'in_production': await _count(db, ChassisMaster.tracking_status == 'WIP'),
        'da_lite_hold': await _count(db, ChassisMaster.da_lite_status == 'HOLD', not_dispatched),
        'ready_for_dispatch': await _count(db, ChassisMaster.tracking_status == 'READY_FOR_DISPATCH'),
        'arrived_in_period': await _count(db, ChassisMaster.arrival_date.between(start, end)),
        'dispatched_in_period': await _count(db, ChassisMaster.dispatched_at.between(period_from, period_to)),
    }

    # Pipeline: yard, then each active station (chassis checked in there now), then dispatch.
    at_station = dict((await db.execute(
        select(ChassisStationLog.station_id, func.count(func.distinct(ChassisStationLog.chassis_id)))
        .where(ChassisStationLog.event_type == 'ARRIVAL', ChassisStationLog.status == 'AT_STATION')
        .group_by(ChassisStationLog.station_id)
    )).all())
    stations = (await db.execute(
        select(StationMaster).where(StationMaster.is_active.is_(True)).order_by(StationMaster.sequence_no)
    )).scalars().all()
    pipeline = [{'code': 'YARD', 'label': 'Yard', 'count': kpis['in_yard']}]
    pipeline += [
        {'code': s.station_code, 'label': s.station_name, 'count': at_station.get(s.station_id, 0)}
        for s in stations
    ]
    pipeline += [
        {'code': 'RFD', 'label': 'Ready for dispatch', 'count': kpis['ready_for_dispatch']},
        {'code': 'DISPATCHED', 'label': 'Dispatched (period)', 'count': kpis['dispatched_in_period']},
    ]

    in_period = ChassisMaster.arrival_date.between(start, end)

    dealer_rows = (await db.execute(
        select(
            CustomerMaster.customer_id, CustomerMaster.name, CustomerMaster.city, CustomerMaster.state,
            func.count(ChassisMaster.chassis_id).label('chassis'),
            func.count(ChassisMaster.chassis_id).filter(ChassisMaster.da_lite_status == 'HOLD').label('on_hold'),
            func.count(ChassisMaster.chassis_id).filter(ChassisMaster.tracking_status == 'DISPATCHED').label('dispatched'),
        )
        .join(ChassisMaster, ChassisMaster.dealer_id == CustomerMaster.customer_id)
        .where(in_period)
        .group_by(CustomerMaster.customer_id, CustomerMaster.name, CustomerMaster.city, CustomerMaster.state)
        .order_by(func.count(ChassisMaster.chassis_id).desc())
        .limit(TOP_DEALER_LIMIT)
    )).all()
    top_dealers = [
        {
            'dealer_id': r.customer_id, 'name': r.name,
            'location': ', '.join(p for p in (r.city, r.state) if p),
            'chassis': r.chassis, 'on_hold': r.on_hold, 'dispatched': r.dispatched,
        }
        for r in dealer_rows
    ]

    mix_rows = (await db.execute(
        select(func.coalesce(VehicleModelMaster.model_number, 'Model not set').label('model'), func.count().label('n'))
        .select_from(ChassisMaster)
        .outerjoin(VehicleModelMaster, VehicleModelMaster.model_id == ChassisMaster.model_id)
        .where(in_period)
        .group_by('model')
        .order_by(func.count().desc())
    )).all()
    mix_total = sum(r.n for r in mix_rows)
    model_mix = {
        'total': mix_total,
        'models': [
            {'model': r.model, 'count': r.n, 'percent': round(r.n * 100 / mix_total) if mix_total else 0}
            for r in mix_rows
        ],
    }

    activity_rows = (await db.execute(
        select(ChassisStatusLog, ChassisMaster.chassis_no)
        .join(ChassisMaster, ChassisMaster.chassis_id == ChassisStatusLog.chassis_id)
        .where(ChassisStatusLog.changed_at.between(period_from, period_to))
        .order_by(ChassisStatusLog.changed_at.desc())
        .limit(ACTIVITY_LIMIT)
    )).all()
    recent_activity = [
        {
            'chassis_no': chassis_no,
            'from_status': log.from_status,
            'to_status': log.to_status,
            'location': log.scan_location,
            'at': log.changed_at,
        }
        for log, chassis_no in activity_rows
    ]

    return {
        'period': {'start': start, 'end': end},
        'kpis': kpis,
        'pipeline': pipeline,
        'top_dealers': top_dealers,
        'model_mix': model_mix,
        'recent_activity': recent_activity,
    }