"""Turn the CAA's monthly files (data/raw/) into one table of flight counts.

    uv run build_data.py

writes data/punctuality.parquet, with one row for each month, reporting airport,
origin or destination, airline (as named by the CAA), direction and type of
service, holding counts of flights, not percentages, so that rows can be added:

    planned       flights planned: operated + cancelled + unmatched
    operated      flights matched to a planned time ("number_flights_matched")
    cancelled     cancelled less than 24 hours before (or after) their time
    unmatched     flights that operated but whose planned time is unknown
    early_15      more than 15 minutes early
    early         1 to 15 minutes early
    late_0_15     on time to 15 minutes late
    late_16_30, late_31_60, late_61_120, late_121_180, late_181_360, late_360
    delay_min     total minutes of delay, with early flights counted as zero

The CAA gives each band as a percentage of the planned flights, so the counts are
these percentages times `planned`, rounded (they are whole numbers to within
rounding in all but a few dozen of the 660,000 rows). Its average delay, in
minutes over the operated flights, is rounded to a whole minute in these files,
which makes `delay_min` uncertain by half a minute per operated flight in each
row; over an airport's month the error averages out to a few hundredths of a
minute (see check_against_caa).

Airline names are mapped to an operator, brand and group by airlines.csv; a name
that it does not list keeps a tidied version of the CAA's name at all three
levels, and the build lists those with many flights so that they can be added.
"""

import io
import os
import re
import sys
from pathlib import Path

import numpy as np
import pandas as pd

from caa import RAW_DIR, decode

HERE = Path(__file__).parent
OUTPUT = HERE / "data" / "punctuality.parquet"
AIRLINES = HERE / "airlines.csv"

BANDS = {
    "early_15": "flights_more_than_15_minutes_early_percent",
    "early": "flights_15_minutes_early_to_1_minute_early_percent",
    "late_0_15": "flights_0_to_15_minutes_late_percent",
    "late_16_30": "flights_between_16_and_30_minutes_late_percent",
    "late_31_60": "flights_between_31_and_60_minutes_late_percent",
    "late_61_120": "flights_between_61_and_120_minutes_late_percent",
    "late_121_180": "flights_between_121_and_180_minutes_late_percent",
    "late_181_360": "flights_between_181_and_360_minutes_late_percent",
    "late_360": "flights_more_than_360_minutes_late_percent",
}
ON_TIME = ["early_15", "early", "late_0_15"]

# The reporting airports: the CAA's name, the name used here and the IATA code.
# Durham Tees Valley was renamed Teesside International in 2019.
AIRPORTS = {
    "ABERDEEN": ("Aberdeen", "ABZ"),
    "BELFAST CITY (GEORGE BEST)": ("Belfast City", "BHD"),
    "BELFAST INTERNATIONAL": ("Belfast International", "BFS"),
    "BIRMINGHAM": ("Birmingham", "BHX"),
    "BOURNEMOUTH": ("Bournemouth", "BOH"),
    "BRISTOL": ("Bristol", "BRS"),
    "CARDIFF WALES": ("Cardiff", "CWL"),
    "DONCASTER SHEFFIELD": ("Doncaster Sheffield", "DSA"),
    "DURHAM TEES VALLEY": ("Teesside", "MME"),
    "TEESSIDE INTERNATIONAL AIRPORT": ("Teesside", "MME"),
    "EAST MIDLANDS INTERNATIONAL": ("East Midlands", "EMA"),
    "EDINBURGH": ("Edinburgh", "EDI"),
    "EXETER": ("Exeter", "EXT"),
    "GATWICK": ("Gatwick", "LGW"),
    "GLASGOW": ("Glasgow", "GLA"),
    "HEATHROW": ("Heathrow", "LHR"),
    "ISLE OF MAN": ("Isle of Man", "IOM"),
    "JERSEY": ("Jersey", "JER"),
    "LEEDS BRADFORD": ("Leeds Bradford", "LBA"),
    "LIVERPOOL (JOHN LENNON)": ("Liverpool", "LPL"),
    "LONDON CITY": ("London City", "LCY"),
    "LUTON": ("Luton", "LTN"),
    "MANCHESTER": ("Manchester", "MAN"),
    "NEWCASTLE": ("Newcastle", "NCL"),
    "SOUTHAMPTON": ("Southampton", "SOU"),
    "SOUTHEND": ("Southend", "SEN"),
    "STANSTED": ("Stansted", "STN"),
}

SMALL_WORDS = {"and", "de", "del", "la", "of", "the"}
# Names that tidy() gets wrong, and the CAA's misspellings.
PLACE_FIXES = {
    "Usa": "USA",
    "Portsmouth (Usa)": "Portsmouth (USA)",
    "Portugal (Excluding Madeira)": "Portugal (excluding Madeira)",
    "Isle of Curacao Neth.Antilles": "Curaçao",
    "Isle of Diego Garcia": "Diego Garcia",
    "New York (Jf Kennedy)": "New York (JFK)",
    "Chicago (O'hare)": "Chicago (O'Hare)",
    "Camp Springs (Andrews Afb)": "Camp Springs (Andrews AFB)",
    "Hyderabad ( Rajiv Ghandi )": "Hyderabad (Rajiv Gandhi)",
    "Tenerife (Surreina Sofia)": "Tenerife (Sur Reina Sofia)",
    "Ilha Do Sal C.Verde": "Sal (Cape Verde)",
    "Seattle (Boeing Fld Int)": "Seattle (Boeing Field)",
    "Casablanca Mohamed V": "Casablanca (Mohammed V)",
    "Wick John O Groats": "Wick John O'Groats",
    "Enfidha - Hammamet Intl": "Enfidha-Hammamet",
    "Cotswold Apt - Kemble": "Cotswold (Kemble)",
    "Abu Dhabi - Bateen": "Abu Dhabi (Bateen)",
}
LEGAL_SUFFIX = re.compile(r"\s+(LTD|LIMITED|PLC|GMBH|S\.?A\.?|AS|AB|SPA|INC|LLC|CO KG|KFT)\.?$")


def clean(name: str) -> str:
    """The CAA's name in one form: upper case, single spaces, no non-breaking spaces."""
    return re.sub(r"\s+", " ", name.replace("\xa0", " ")).strip().upper()


def tidy(name: str, legal: bool = False) -> str:
    """'PARIS (CHARLES DE GAULLE)' -> 'Paris (Charles de Gaulle)'."""
    if legal:
        name = LEGAL_SUFFIX.sub("", name)
    name = re.sub(r"(\S)\(", r"\1 (", name)  # 'SPAIN(CANARY ISLANDS)'

    def word(w: str, first: bool) -> str:
        low = w.lower()
        if not first and low in SMALL_WORDS:
            return low
        # Capitalise after any start, hyphen, slash or bracket, not after an apostrophe.
        return re.sub(r"(^|[-/(.])([a-z])", lambda m: m[1] + m[2].upper(), low)

    return " ".join(word(w, i == 0) for i, w in enumerate(name.split()))


def place(name: str) -> str:
    t = tidy(clean(name))
    return PLACE_FIXES.get(t, t)


def read_month(path: Path) -> pd.DataFrame:
    df = pd.read_csv(io.StringIO(decode(path.read_bytes())), dtype={"reporting_period": str})
    return df


def load_airlines() -> pd.DataFrame:
    a = pd.read_csv(AIRLINES, dtype=str, keep_default_na=False)
    a["caa_name"] = a.caa_name.map(clean)
    a["from"] = a["from"].replace("", "000000")
    a["to"] = a["to"].replace("", "999999")
    for name, rows in a.groupby("caa_name"):
        rows = rows.sort_values("from")
        if (rows["from"].iloc[1:].values <= rows["to"].iloc[:-1].values).any():
            raise ValueError(f"airlines.csv: overlapping periods for {name}")
    return a


def map_airlines(df: pd.DataFrame, airlines: pd.DataFrame) -> pd.DataFrame:
    keys = df[["period", "caa_airline"]].drop_duplicates()
    m = keys.merge(airlines, left_on="caa_airline", right_on="caa_name", how="left")
    in_range = m["from"].isna() | ((m.period >= m["from"]) & (m.period <= m["to"]))
    m = m[in_range]
    listed = set(airlines.caa_name)
    missing = m.operator.isna()
    gaps = m[missing & m.caa_airline.isin(listed)]
    if len(gaps):
        examples = gaps[["caa_airline", "period"]].drop_duplicates().head().values.tolist()
        raise ValueError(f"airlines.csv: no period covers {examples}")
    default = m.loc[missing, "caa_airline"].map(lambda n: tidy(n, legal=True))
    for level in ("operator", "brand", "group"):
        m.loc[missing, level] = default
    m = m[["period", "caa_airline", "operator", "brand", "group"]]
    out = df.merge(m, on=["period", "caa_airline"], how="left", validate="many_to_one")
    assert out.operator.notna().all()
    return out


def build() -> pd.DataFrame:
    paths = sorted(RAW_DIR.glob("[0-9]" * 6 + ".csv"))
    raw = pd.concat([read_month(p) for p in paths], ignore_index=True)

    unknown = set(raw.reporting_airport) - set(AIRPORTS)
    if unknown:
        raise ValueError(f"New reporting airports, to add to AIRPORTS: {sorted(unknown)}")

    planned = raw.number_flights_matched + raw.number_flights_cancelled + raw.actual_flights_unmatched
    bands = raw[list(BANDS.values())].mul(planned / 100, axis=0)
    bands.columns = list(BANDS)
    # In a few rows the bands are percentages of a slightly larger number (a flight
    # with no recorded time, perhaps); scale those to the operated flights.
    total = bands.sum(axis=1)
    odd = (raw.number_flights_matched > 0) & ~np.isclose(total, raw.number_flights_matched, atol=0.01)
    bands.loc[odd] = bands.loc[odd].mul(raw.number_flights_matched[odd] / total[odd], axis=0)
    bands = bands.round().astype("int32")

    df = pd.DataFrame(
        {
            "period": raw.reporting_period,
            "month": pd.to_datetime(raw.reporting_period, format="%Y%m"),
            "airport": raw.reporting_airport.map(lambda a: AIRPORTS[a][0]),
            "airport_code": raw.reporting_airport.map(lambda a: AIRPORTS[a][1]),
            "country": raw.origin_destination_country.map(place),
            "destination": raw.origin_destination.map(place),
            "caa_airline": raw.airline_name.map(clean),
            "direction": raw.arrival_departure,
            "service": raw.scheduled_charter,
            "planned": planned.astype("int32"),
            "operated": raw.number_flights_matched.astype("int32"),
            "cancelled": raw.number_flights_cancelled.astype("int32"),
            "unmatched": raw.actual_flights_unmatched.astype("int32"),
        }
    )
    df = pd.concat([df, bands], axis=1)
    df["delay_min"] = (raw.average_delay_mins.fillna(0) * raw.number_flights_matched).astype("float32")
    # Rows with no flights carry only last year's figures for comparison.
    df = df[df.planned > 0].reset_index(drop=True)

    df = map_airlines(df, load_airlines())
    for col in ("airport", "airport_code", "country", "destination", "caa_airline", "operator",
                "brand", "group", "direction", "service"):  # fmt: skip
        df[col] = df[col].astype("category")
    return df


def report_unlisted(df: pd.DataFrame, airlines: pd.DataFrame, threshold: int = 300) -> None:
    """List the airline names not in airlines.csv with many flights in the last year."""
    last = df.month.max()
    recent = df[df.month > last - pd.DateOffset(months=12)]
    flights = recent[~recent.caa_airline.isin(set(airlines.caa_name))].groupby("caa_airline", observed=True).planned.sum()
    flights = flights[flights >= threshold].sort_values(ascending=False)
    prefix = "::warning::" if os.environ.get("GITHUB_ACTIONS") else ""  # an annotation in GitHub Actions
    for name, n in flights.items():
        print(f"{prefix}not in airlines.csv: {name} ({n} flights in the last 12 months)", file=sys.stderr)


def read_caa_table(path: Path, sheet: str) -> pd.DataFrame:
    """Read a sheet of the CAA's On Time Performance Tables, whose layout varies."""
    raw = pd.read_excel(path, sheet_name=sheet, header=None)
    header = raw.index[raw.apply(lambda r: r.astype(str).str.strip().eq("Reporting Period").any(), axis=1)][0]
    cols = [re.sub(r"\s+", " ", str(v)).strip() for v in raw.iloc[header]]
    t = raw.iloc[header + 1 :].copy()
    t.columns = cols

    def current(prefix: str) -> str:
        return next(c for c in cols if c.startswith(prefix) and "Previous" not in c)

    out = pd.DataFrame(
        {
            "name": t.iloc[:, cols.index("Reporting Period") + 1],
            "on_time_pct": t[current("Flights on time")],
            "avg_delay": t[current("Average Delay")],
            "cancelled_pct": t[current("Flights Cancelled")],
            "planned": t["Total Flights"],
        }
    )
    out = out[out.name.notna() & out.planned.notna() & (out.name != "All Airports")]
    out["period"] = path.stem
    return out.astype({"on_time_pct": float, "avg_delay": float, "cancelled_pct": float, "planned": float})


def check_against_caa(df: pd.DataFrame) -> None:
    """Compare each airport's month with the CAA's own figures (from October 2024)."""
    paths = sorted(RAW_DIR.glob("*.xlsx"))
    caa = pd.concat([read_caa_table(p, "Airport") for p in paths])
    caa["airport"] = caa.name.map(lambda a: AIRPORTS[a][0])
    g = df.groupby(["period", "airport"], observed=True)
    ours = pd.DataFrame(
        {
            "planned": g.planned.sum(),
            "on_time_pct": 100 * g[ON_TIME].sum().sum(axis=1) / g.planned.sum(),
            "avg_delay": g.delay_min.sum() / g.operated.sum(),
            "cancelled_pct": 100 * g.cancelled.sum() / g.planned.sum(),
        }
    )
    j = caa.set_index(["period", "airport"]).join(ours.add_suffix("_ours"), how="inner")
    # The CSV files leave out operators who have not consented to publication, so
    # compare only the airport-months whose totals agree to within 1 per cent.
    same = (j.planned_ours / j.planned - 1).abs() < 0.01
    d_otp = (j.on_time_pct_ours - j.on_time_pct)[same].abs()
    d_avg = (j.avg_delay_ours - j.avg_delay)[same].abs()
    d_canc = (j.cancelled_pct_ours - j.cancelled_pct)[same].abs()
    print(
        f"check against the CAA's tables: {same.sum()} of {len(j)} airport-months with the same total;"
        f" largest differences: on time {d_otp.max():.2f} points, average delay {d_avg.max():.2f} min,"
        f" cancelled {d_canc.max():.2f} points",
        file=sys.stderr,
    )
    # The rounded averages (see the module's docstring) and a handful of flights
    # make differences of a few tenths at the smallest airports.
    if len(j) < len(caa) or same.mean() < 0.9 or d_otp.max() > 1 or d_avg.max() > 0.5 or d_canc.max() > 1:
        raise ValueError("our figures differ from the CAA's: see above")


def main() -> None:
    df = build()
    report_unlisted(df, load_airlines())
    check_against_caa(df)
    OUTPUT.parent.mkdir(exist_ok=True)
    df.to_parquet(OUTPUT, index=False)
    print(
        f"{OUTPUT.name}: {len(df)} rows, {df.period.nunique()} months ({df.period.min()} to {df.period.max()}),"
        f" {df.planned.sum():,} planned flights, {OUTPUT.stat().st_size / 1e6:.1f} MB",
        file=sys.stderr,
    )


if __name__ == "__main__":
    main()
