"""Build dca756_pairings.html from the monthly trip pairing spreadsheets.

Reads every outputs/*_DCA_756_Trip_Pairings.xlsx that uses the Sep-2026-onward
column layout, plus the pay rates from the newest one, and fills template.html.

    python3 build_site.py
"""
import json
import re
from datetime import datetime
from pathlib import Path

import openpyxl

HERE = Path(__file__).resolve().parent
OUTPUTS = HERE.parent / "outputs"
HEADER = ["Trip ID", "Days", "Type", "EFF From", "EFF Thru", "CRD (HH.MM)", "TAFB (HH.MM)", "Overnights"]
TITLE = re.compile(r"—\s*(\w+ \d{4})\s*\(Bid Period:\s*([^)]+)\)")


def read_month(path):
    wb = openpyxl.load_workbook(path, data_only=True)
    if "Trip Pairings" not in wb.sheetnames or "Pay Rates" not in wb.sheetnames:
        return None, None
    ws = wb["Trip Pairings"]
    title = ws["A1"].value or ""
    header = [c.value for c in ws[2]]
    if header[:8] != HEADER or "Crew Type" not in header:
        return None, None
    col = {name: i for i, name in enumerate(header) if name}
    m = TITLE.search(title)
    label, period = (m.group(1), m.group(2).replace("–", " – ")) if m else (path.stem, "")
    trips = []
    for r in ws.iter_rows(min_row=3, values_only=True):
        if not (isinstance(r[0], str) and re.fullmatch(r"W\d+", r[0])):
            continue
        trips.append({
            "id": r[0], "days": r[1], "type": r[2],
            "from": str(r[3])[:10], "thru": str(r[4])[:10],
            "crd": r[5], "tafb": r[6], "on": r[7] or "",
            "crew": r[col["Crew Type"]],
            "u": r[col["FTM 76U"]] or 0, "non": r[col["FTM Non-76U"]] or 0,
            "match": r[col["Matched IRO"]] if "Matched IRO" in col else None,
        })
    month = {"key": datetime.strptime(label, "%B %Y").strftime("%Y-%m") if m else path.stem,
             "label": label, "period": period, "trips": trips}
    return month, wb["Pay Rates"]


def read_rates(ws):
    # FO block comes first, CA block second; both use the same row labels.
    blocks, cur = [], None
    for r in ws.iter_rows(values_only=True):
        if r[0] in ("FIRST OFFICER RATES", "CAPTAIN RATES"):
            cur = {}
            blocks.append(cur)
        elif cur is not None and r[1] and r[1] != "Year →":
            cur[r[1]] = [float(x) for x in r[2:14]]
    pick = lambda b: {"non": b["76Q/76L (Cat B)"], "u": b["76U (Cat A)"], "tc": b["T/C Blend"]}
    return {"FO": pick(blocks[0]), "CA": pick(blocks[1])}


def main():
    months, rates = [], None
    for path in sorted(OUTPUTS.glob("*_DCA_756_Trip_Pairings.xlsx")):
        month, rate_ws = read_month(path)
        if month:
            months.append((month, rate_ws))
            print(f"{path.name}: {month['label']}, {len(month['trips'])} trips")
        else:
            print(f"{path.name}: older layout, skipped")
    months.sort(key=lambda x: x[0]["key"])
    rates = read_rates(months[-1][1])
    data = {"months": [m for m, _ in months], "rates": rates}
    html = (HERE / "template.html").read_text()
    html = html.replace("/*__DATA__*/null", json.dumps(data, separators=(",", ":")))
    out = HERE / "dca756_pairings.html"
    out.write_text(html)
    print(f"Wrote {out.name}")

    # Standalone copy for Cloudflare Pages: a full HTML document named index.html.
    site = HERE / "site"
    site.mkdir(exist_ok=True)
    (site / "index.html").write_text(
        '<!doctype html>\n<html lang="en">\n<head>\n<meta charset="utf-8">\n'
        '<meta name="viewport" content="width=device-width, initial-scale=1, viewport-fit=cover">\n'
        '<meta name="robots" content="noindex, nofollow">\n'
        '<style>:root{padding-top:env(safe-area-inset-top,0px);padding-bottom:env(safe-area-inset-bottom,0px)}'
        'body{margin:0}[hidden]{display:none!important}</style>\n'
        "</head>\n<body>\n" + html + "\n</body>\n</html>\n"
    )
    print("Wrote site/index.html")


if __name__ == "__main__":
    main()