"""
Main ETL pipeline for carrier reliability analysis.
"""
import json
import pandas as pd
from core.database import engine
from core.utils import get_output_path
from .compute import compute_carrier_comparison
from .stats import find_carrier_gap, classify_gap


def main():
    print("Loading flight data from database...")

    query = """
        SELECT origin, dest, carrier,
               arr_delay, arr_del15,
               carrier_delay, cancelled, diverted
        FROM flights
        WHERE origin IS NOT NULL
          AND dest IS NOT NULL
          AND carrier IS NOT NULL
    """

    df = pd.read_sql(query, engine)
    print(f"Loaded {len(df):,} flights")

    print("Computing carrier reliability by route...")
    carrier_data = compute_carrier_comparison(df)
    print(f"Found {len(carrier_data)} routes with multiple carriers")

    print("Calculating carrier gaps...")
    for route_key, carriers in carrier_data.items():
        gap_info = find_carrier_gap(carriers)
        if gap_info:
            carrier_data[route_key]['gap'] = gap_info
            carrier_data[route_key]['gap']['risk'] = classify_gap(gap_info['gap_pp'])

    output_path = get_output_path('carrier_reliability.json')
    output_path.parent.mkdir(parents=True, exist_ok=True)
    with open(output_path, 'w') as f:
        json.dump(carrier_data, f, separators=(',', ':'))

    print(f"Done! {output_path.name} ({output_path.stat().st_size / 1024:.1f} KB)")


if __name__ == '__main__':
    main()
