#!/usr/bin/env python3
"""Compute pre-aggregated airport + route stats from the flights table.

Run AFTER populate_db.py. Populates `airports` and `routes` so the API
never touches the big flights table at request time.
"""
import sys
from pathlib import Path

import airportsdata
import pandas as pd
from sqlalchemy import text

sys.path.insert(0, str(Path(__file__).parent.parent))

from core.database import engine
from app.models.route import DelayRisk


def classify_delay_risk(pct_delayed: float) -> str:
    """Classify route delay risk based on % flights delayed >15min."""
    if pd.isna(pct_delayed) or pct_delayed < 0.15:
        return DelayRisk.LOW.value
    elif pct_delayed < 0.25:
        return DelayRisk.MEDIUM.value
    elif pct_delayed < 0.40:
        return DelayRisk.HIGH.value
    else:
        return DelayRisk.SEVERE.value


def compute_airport_stats():
    print("Computing airport statistics...")

    # Departure stats
    dep_query = """
        SELECT
            origin AS iata,
            COUNT(*) AS total_departures,
            AVG(dep_delay) AS avg_dep_delay,
            SUM(CASE WHEN carrier_delay > 0 THEN carrier_delay ELSE 0 END) AS total_carrier_delay,
            SUM(CASE WHEN weather_delay > 0 THEN weather_delay ELSE 0 END) AS total_weather_delay,
            SUM(CASE WHEN nas_delay > 0 THEN nas_delay ELSE 0 END) AS total_nas_delay,
            SUM(CASE WHEN late_aircraft_delay > 0 THEN late_aircraft_delay ELSE 0 END) AS total_late_aircraft_delay
        FROM flights
        WHERE cancelled = 0
        GROUP BY origin
    """

    # Arrival stats
    arr_query = """
        SELECT
            dest AS iata,
            COUNT(*) AS total_arrivals,
            AVG(arr_delay) AS avg_arr_delay,
            AVG(CASE WHEN arr_del15 = 1 THEN 1.0 ELSE 0.0 END) AS pct_delayed
        FROM flights
        WHERE cancelled = 0
        GROUP BY dest
    """

    dep_df = pd.read_sql(dep_query, engine)
    arr_df = pd.read_sql(arr_query, engine)

    # Merge departure and arrival stats
    airports_df = dep_df.merge(arr_df, on="iata", how="outer")
    airports_df["total_departures"] = airports_df["total_departures"].fillna(0).astype(int)
    airports_df["total_arrivals"] = airports_df["total_arrivals"].fillna(0).astype(int)
    airports_df["total_flights"] = airports_df["total_departures"] + airports_df["total_arrivals"]

    # Compute delay cause percentages
    total_delay = (
        airports_df["total_carrier_delay"].fillna(0) +
        airports_df["total_weather_delay"].fillna(0) +
        airports_df["total_nas_delay"].fillna(0) +
        airports_df["total_late_aircraft_delay"].fillna(0)
    )
    total_delay = total_delay.replace(0, 1)  # Avoid division by zero

    airports_df["carrier_pct"] = (airports_df["total_carrier_delay"].fillna(0) / total_delay * 100).round(1)
    airports_df["weather_pct"] = (airports_df["total_weather_delay"].fillna(0) / total_delay * 100).round(1)
    airports_df["nas_pct"] = (airports_df["total_nas_delay"].fillna(0) / total_delay * 100).round(1)
    airports_df["late_aircraft_pct"] = (airports_df["total_late_aircraft_delay"].fillna(0) / total_delay * 100).round(1)

    # Compute delay score: pct_delayed * sqrt(total_flights) -- busier + more delayed = higher score
    airports_df["delay_score"] = (
        airports_df["pct_delayed"].fillna(0) * airports_df["total_flights"].pow(0.5)
    ).round(1)

    # Add lat/lon and names from airportsdata
    ap_data = airportsdata.load("IATA")
    airports_df["lat"] = airports_df["iata"].map(lambda c: ap_data.get(c, {}).get("lat"))
    airports_df["lon"] = airports_df["iata"].map(lambda c: ap_data.get(c, {}).get("lon"))
    airports_df["name"] = airports_df["iata"].map(lambda c: ap_data.get(c, {}).get("name", ""))
    airports_df["city"] = airports_df["iata"].map(lambda c: ap_data.get(c, {}).get("city", ""))
    airports_df["state"] = airports_df["iata"].map(lambda c: ap_data.get(c, {}).get("subd", ""))

    # Drop airports without coordinates
    before = len(airports_df)
    airports_df = airports_df.dropna(subset=["lat", "lon"])
    after = len(airports_df)
    if before != after:
        print(f"  Dropped {before - after} airports without coordinates")

    # Select columns matching Airport model
    final_cols = [
        "iata", "name", "city", "state", "lat", "lon",
        "total_flights", "total_departures", "total_arrivals",
        "avg_arr_delay", "avg_dep_delay", "pct_delayed", "delay_score",
        "total_carrier_delay", "total_weather_delay", "total_nas_delay",
        "total_late_aircraft_delay",
        "carrier_pct", "weather_pct", "nas_pct", "late_aircraft_pct",
    ]

    # Clear and repopulate
    with engine.connect() as conn:
        conn.execute(text("DELETE FROM airports"))
        conn.commit()

    airports_df[final_cols].to_sql("airports", engine, if_exists="append", index=False)

    print(f"  Populated {len(airports_df)} airports")
    print(f"  Top 5 by delay score:")
    top = airports_df.nlargest(5, "delay_score")[["iata", "name", "total_flights", "pct_delayed", "delay_score"]]
    for _, row in top.iterrows():
        print(f"    {row['iata']:5s}  {row['name'][:40]:40s}  "
              f"flights={row['total_flights']:>10,}  "
              f"delayed={row['pct_delayed']:.1%}  "
              f"score={row['delay_score']:.1f}")


def compute_route_stats():
    print("\nComputing route statistics...")

    route_query = """
        SELECT
            origin,
            dest,
            COUNT(*) AS flight_count,
            AVG(arr_delay) AS avg_delay,
            AVG(CASE WHEN arr_del15 = 1 THEN 1.0 ELSE 0.0 END) AS pct_delayed,
            AVG(carrier_delay) AS avg_carrier_delay,
            AVG(weather_delay) AS avg_weather_delay,
            AVG(nas_delay) AS avg_nas_delay,
            AVG(late_aircraft_delay) AS avg_late_aircraft_delay,
            AVG(distance) AS avg_distance
        FROM flights
        WHERE cancelled = 0
        GROUP BY origin, dest
        HAVING COUNT(*) >= 100
    """

    routes_df = pd.read_sql(route_query, engine)

    # Classify delay risk
    routes_df["delay_risk"] = routes_df["pct_delayed"].apply(classify_delay_risk)

    # Compute median and std delay per route (more expensive, sampled)
    # For MVP, set these to None — can be computed later if needed
    routes_df["median_delay"] = None
    routes_df["std_delay"] = None

    # Clear and repopulate
    with engine.connect() as conn:
        conn.execute(text("DELETE FROM routes"))
        conn.commit()

    routes_df.to_sql("routes", engine, if_exists="append", index=False)

    print(f"  Populated {len(routes_df)} routes")
    print(f"\n  Risk distribution:")
    dist = routes_df["delay_risk"].value_counts()
    for risk, count in dist.items():
        pct = count / len(routes_df) * 100
        print(f"    {risk:8s}: {count:5d} ({pct:.1f}%)")


def compute_recovery_scores():
    """How well does an airport recover when an inbound aircraft arrives late?

    Pairs consecutive legs by the same tail number on the same day.
    If leg N arrives >15min late, we look at leg N+1's departure delay.
    recovery_score = avg(prev_arr_delay - next_dep_delay) across those pairs.
    Positive = airport absorbed some of the delay; negative = made it worse.
    """
    print("\nComputing recovery scores...")

    # Window function to number legs per aircraft per day
    recovery_query = """
        WITH ordered_legs AS (
            SELECT origin, dest, tail_number, fl_date, arr_delay, dep_delay,
                   ROW_NUMBER() OVER (
                       PARTITION BY tail_number, fl_date
                       ORDER BY dep_delay
                   ) AS leg_num
            FROM flights
            WHERE cancelled = 0 AND diverted = 0
              AND tail_number IS NOT NULL AND tail_number != ''
        ),
        pairs AS (
            SELECT curr.origin,
                   prev.arr_delay AS prev_arr_delay,
                   curr.dep_delay
            FROM ordered_legs curr
            JOIN ordered_legs prev
              ON curr.tail_number = prev.tail_number
             AND curr.fl_date = prev.fl_date
             AND curr.leg_num = prev.leg_num + 1
            WHERE prev.arr_delay > 15
        )
        SELECT origin AS iata,
               AVG(prev_arr_delay - dep_delay) AS recovery_score,
               COUNT(*) AS propagation_legs
        FROM pairs
        GROUP BY origin
        HAVING COUNT(*) >= 30
    """

    recovery_df = pd.read_sql(recovery_query, engine)
    print(f"  Got recovery data for {len(recovery_df)} airports")

    if recovery_df.empty:
        print("  No recovery data computed (need tail_number in flights)")
        return

    # UPDATE existing airport rows rather than re-inserting
    with engine.connect() as conn:
        for _, row in recovery_df.iterrows():
            conn.execute(
                text("""
                    UPDATE airports
                    SET recovery_score = :score, propagation_legs = :legs
                    WHERE iata = :iata
                """),
                {
                    "score": round(row["recovery_score"], 2),
                    "legs": int(row["propagation_legs"]),
                    "iata": row["iata"],
                },
            )
        conn.commit()

    # Quick summary
    top_recover = recovery_df.nlargest(5, "recovery_score")
    print("  Best recovery (absorb delay):")
    for _, r in top_recover.iterrows():
        print(f"    {r['iata']:5s}  score={r['recovery_score']:+.1f} min  "
              f"({int(r['propagation_legs'])} leg pairs)")

    worst = recovery_df.nsmallest(5, "recovery_score")
    print("  Worst recovery (amplify delay):")
    for _, r in worst.iterrows():
        print(f"    {r['iata']:5s}  score={r['recovery_score']:+.1f} min  "
              f"({int(r['propagation_legs'])} leg pairs)")


def main():
    # Verify flights table has data
    with engine.connect() as conn:
        flight_count = conn.execute(text("SELECT COUNT(*) FROM flights")).scalar()

    if flight_count == 0:
        print("ERROR: flights table is empty. Run populate_db.py first.")
        sys.exit(1)

    print(f"Computing aggregations from {flight_count:,} flight records...\n")

    compute_airport_stats()
    compute_route_stats()
    compute_recovery_scores()

    print("\nAggregation complete.")


if __name__ == "__main__":
    main()
