#!/usr/bin/env python3
"""Synthetic fixture DB for tests — no BTS data needed."""
import random
import sqlite3
import sys
from pathlib import Path

DEFAULT_OUT = Path(__file__).parent / "fixture.db"

AIRPORTS = [
    ("ATL", "Hartsfield-Jackson Atlanta Intl",    "Atlanta",       "GA", 33.6407, -84.4277),
    ("LAX", "Los Angeles Intl",                   "Los Angeles",   "CA", 33.9425, -118.4081),
    ("ORD", "O'Hare Intl",                        "Chicago",       "IL", 41.9742, -87.9073),
    ("DFW", "Dallas/Fort Worth Intl",             "Dallas",        "TX", 32.8998, -97.0403),
    ("DEN", "Denver Intl",                        "Denver",        "CO", 39.8561, -104.6737),
    ("JFK", "John F Kennedy Intl",                "New York",      "NY", 40.6413, -73.7781),
    ("SFO", "San Francisco Intl",                 "San Francisco", "CA", 37.6213, -122.3790),
    ("SEA", "Seattle-Tacoma Intl",                "Seattle",       "WA", 47.4502, -122.3088),
    ("MIA", "Miami Intl",                         "Miami",         "FL", 25.7959, -80.2870),
    ("BOS", "Logan Intl",                         "Boston",        "MA", 42.3656, -71.0096),
    ("LAS", "Harry Reid Intl",                    "Las Vegas",     "NV", 36.0840, -115.1537),
    ("PHX", "Phoenix Sky Harbor Intl",            "Phoenix",       "AZ", 33.4373, -112.0078),
]

CARRIERS = ["AA", "DL", "UA", "WN"]
DEP_TIMES = [600, 900, 1200, 1500, 1800, 2100]

ROUTE_PAIRS = [
    ("ATL", "LAX"), ("ATL", "ORD"), ("ATL", "DFW"),
    ("LAX", "SFO"), ("LAX", "DEN"), ("LAX", "JFK"),
    ("ORD", "DFW"), ("ORD", "DEN"), ("ORD", "JFK"),
    ("DFW", "DEN"), ("DFW", "MIA"), ("JFK", "BOS"),
    ("SFO", "SEA"), ("SFO", "LAS"), ("DEN", "PHX"),
]


def _create_schema(cur):
    cur.executescript("""
        CREATE TABLE IF NOT EXISTS flights (
            id                  INTEGER PRIMARY KEY,
            fl_date             TEXT,
            year                INTEGER,
            month               INTEGER,
            carrier             TEXT,
            flight_num          TEXT,
            tail_number         TEXT,
            origin              TEXT,
            dest                TEXT,
            crs_dep_time        INTEGER,
            arr_delay           REAL,
            dep_delay           REAL,
            arr_del15           INTEGER,
            carrier_delay       REAL,
            weather_delay       REAL,
            nas_delay           REAL,
            security_delay      REAL,
            late_aircraft_delay REAL,
            cancelled           INTEGER,
            diverted            INTEGER,
            distance            REAL
        );

        CREATE TABLE IF NOT EXISTS airports (
            iata                TEXT PRIMARY KEY,
            name                TEXT,
            city                TEXT,
            state               TEXT,
            lat                 REAL,
            lon                 REAL,
            total_flights       INTEGER,
            total_departures    INTEGER,
            total_arrivals      INTEGER,
            avg_arr_delay       REAL,
            avg_dep_delay       REAL,
            pct_delayed         REAL,
            delay_score         REAL,
            total_carrier_delay REAL,
            total_weather_delay REAL,
            total_nas_delay     REAL,
            total_late_aircraft_delay REAL,
            carrier_pct         REAL,
            weather_pct         REAL,
            nas_pct             REAL,
            late_aircraft_pct   REAL,
            recovery_score      REAL,
            propagation_legs    INTEGER,
            last_updated        TEXT DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS routes (
            id                      INTEGER PRIMARY KEY,
            origin                  TEXT,
            dest                    TEXT,
            flight_count            INTEGER,
            avg_delay               REAL,
            pct_delayed             REAL,
            delay_risk              TEXT,
            avg_distance            REAL,
            avg_carrier_delay       REAL,
            avg_weather_delay       REAL,
            avg_nas_delay           REAL,
            avg_late_aircraft_delay REAL
        );
    """)


def _generate_flights(rng):
    rows = []
    tail_pool = [f"N{n:04d}" for n in range(100, 160)]
    flight_id = 1

    for month in range(1, 13):
        for origin, dest in ROUTE_PAIRS:
            for carrier in rng.sample(CARRIERS, k=rng.randint(2, 4)):
                for _ in range(rng.randint(3, 6)):
                    cancelled = 1 if rng.random() < 0.03 else 0
                    diverted  = 1 if (not cancelled and rng.random() < 0.01) else 0

                    arr_delay = dep_delay = None
                    arr_del15 = 0
                    c_delay = w_delay = n_delay = s_delay = la_delay = None

                    if not cancelled:
                        dep_delay = rng.gauss(5, 15)
                        arr_delay = dep_delay + rng.gauss(-2, 8)
                        arr_del15 = 1 if arr_delay > 15 else 0

                        if arr_delay > 15:
                            total = max(arr_delay, 0)
                            c_delay  = round(total * rng.uniform(0, 0.4), 1)
                            w_delay  = round(total * rng.uniform(0, 0.2), 1)
                            n_delay  = round(total * rng.uniform(0, 0.2), 1)
                            s_delay  = round(total * rng.uniform(0, 0.05), 1)
                            la_delay = round(total * rng.uniform(0, 0.3), 1)

                    rows.append((
                        flight_id,
                        f"2023-{month:02d}-{rng.randint(1, 28):02d}",
                        2023, month, carrier,
                        f"{carrier}{rng.randint(100, 9999)}",
                        rng.choice(tail_pool),
                        origin, dest,
                        rng.choice(DEP_TIMES) + rng.randint(0, 59),
                        round(arr_delay, 1) if arr_delay is not None else None,
                        round(dep_delay, 1) if dep_delay is not None else None,
                        arr_del15,
                        c_delay, w_delay, n_delay, s_delay, la_delay,
                        cancelled, diverted,
                        round(rng.uniform(300, 2800), 0),
                    ))
                    flight_id += 1

    return rows


def _populate_airports(cur):
    rows = [
        (iata, name, city, state, lat, lon,
         5000, 2500, 2500,
         8.5, 10.2, 0.21, 45.6,
         12000.0, 4000.0, 8000.0, 6000.0,
         0.40, 0.13, 0.27, 0.20,
         -2.3, 80)
        for iata, name, city, state, lat, lon in AIRPORTS
    ]
    cur.executemany("""
        INSERT OR IGNORE INTO airports VALUES (
            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,datetime('now')
        )
    """, rows)


def _populate_routes(cur):
    rows = [
        (i, origin, dest, 420, 12.4, 0.22, "medium", 1250.0, 5.0, 2.0, 3.0, 4.0)
        for i, (origin, dest) in enumerate(ROUTE_PAIRS, start=1)
    ]
    cur.executemany(
        "INSERT OR IGNORE INTO routes VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
        rows,
    )


def main(out_path=None):
    if out_path is None:
        out_path = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_OUT

    out_path = Path(out_path)
    out_path.parent.mkdir(parents=True, exist_ok=True)
    if out_path.exists():
        out_path.unlink()

    rng = random.Random(42)
    conn = sqlite3.connect(out_path)
    cur = conn.cursor()

    _create_schema(cur)
    _populate_airports(cur)
    _populate_routes(cur)

    flights = _generate_flights(rng)
    cur.executemany(
        "INSERT INTO flights VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        flights,
    )

    conn.commit()
    conn.close()

    size_kb = out_path.stat().st_size / 1024
    print(f"{len(flights)} flights, {size_kb:.0f} KB → {out_path}")


if __name__ == "__main__":
    main()
