from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import func

from core.database import get_db
from ..models import Airport, Route

router = APIRouter(prefix="/api/stats", tags=["stats"])


@router.get("/")
def get_stats(db: Session = Depends(get_db)):
    """Dashboard summary stats."""
    n_airports = db.query(func.count(Airport.iata)).scalar() or 0
    n_routes = db.query(func.count(Route.id)).scalar() or 0
    n_flights = db.query(func.sum(Airport.total_departures)).scalar() or 0
    avg_delay = db.query(func.avg(Airport.avg_arr_delay)).scalar() or 0.0
    avg_pct = db.query(func.avg(Airport.pct_delayed)).scalar() or 0.0

    avg_carrier = db.query(func.avg(Airport.carrier_pct)).scalar() or 0.0
    avg_weather = db.query(func.avg(Airport.weather_pct)).scalar() or 0.0
    avg_nas = db.query(func.avg(Airport.nas_pct)).scalar() or 0.0
    avg_late = db.query(func.avg(Airport.late_aircraft_pct)).scalar() or 0.0

    worst_airports = (
        db.query(Airport)
        .filter(Airport.total_flights >= 10000)
        .order_by(Airport.pct_delayed.desc())
        .limit(5).all()
    )
    worst_routes = (
        db.query(Route)
        .filter(Route.flight_count >= 500)
        .order_by(Route.pct_delayed.desc())
        .limit(5).all()
    )

    return {
        "total_flights": n_flights,
        "total_airports": n_airports,
        "total_routes": n_routes,
        "avg_delay_minutes": round(avg_delay, 1),
        "pct_flights_delayed": round(avg_pct * 100, 1),
        "worst_airports": [
            {"iata": a.iata, "name": a.name, "total_flights": a.total_flights,
             "pct_delayed": round((a.pct_delayed or 0) * 100, 1),
             "avg_delay": round(a.avg_arr_delay or 0, 1)}
            for a in worst_airports
        ],
        "worst_routes": [
            {"origin": r.origin, "dest": r.dest, "flight_count": r.flight_count,
             "pct_delayed": round((r.pct_delayed or 0) * 100, 1),
             "avg_delay": round(r.avg_delay or 0, 1)}
            for r in worst_routes
        ],
        "delay_causes": {
            "carrier_pct": round(avg_carrier, 1),
            "weather_pct": round(avg_weather, 1),
            "nas_pct": round(avg_nas, 1),
            "late_aircraft_pct": round(avg_late, 1),
        },
    }
