"""Figure 2 pipeline: 12 NSW FuelCheck monthly files (mixed CSV and XLSX) -> daily median U91 price for Sydney.

Usage:
  python3 build.py raw/ ../../site/data/fuel.json          rebuild the published file, with checks
  python3 build.py raw/ --trace 2026-03-29                 show how one day's number is made
"""
import argparse, csv, glob, json, os, statistics, sys
from datetime import datetime, timedelta
import openpyxl

SOURCE = "NSW FuelCheck price history, Data.NSW"
MIN_STATIONS = 200          # a day with fewer stations reporting is left out
PRICE_RANGE = (100, 400)    # cents a litre; anything outside is a keying error


def sydney(pc):
    """Sydney means postcodes 2000 to 2234 and 2740 to 2786."""
    try:
        p = int(str(pc).strip()[:4])
    except ValueError:
        return False
    return 2000 <= p <= 2234 or 2740 <= p <= 2786


def parse_dt(v):
    """The files use four date layouts; XLSX cells may already be datetimes."""
    if isinstance(v, datetime):
        return v
    s = str(v).strip()
    for f in ("%d/%m/%Y %H:%M", "%d/%m/%Y %H:%M:%S", "%Y-%m-%d %H:%M:%S", "%d/%m/%Y %I:%M:%S %p", "%d/%m/%Y"):
        try:
            return datetime.strptime(s, f)
        except ValueError:
            pass
    return None


def read(src):
    """Every U91 price change at a Sydney station: (time, station, price, file, row)."""
    events, per_file = [], {}
    for f in sorted(glob.glob(os.path.join(src, "*"))):
        name = os.path.basename(f)
        if f.endswith(".csv"):
            fh = open(f, encoding="utf-8-sig", errors="ignore")
            rows = csv.reader(fh)
        elif f.endswith(".xlsx"):
            rows = openpyxl.load_workbook(f, read_only=True).active.iter_rows(values_only=True)
        else:
            continue
        seen = kept = 0
        for i, row in enumerate(rows, start=1):
            seen += 1
            if not row or len(row) < 8:
                continue
            station, addr, suburb, pc, brand, code, when, price = row[:8]
            if code != "U91" or not sydney(pc):
                continue
            dt = parse_dt(when)
            try:
                p = float(price)
            except (TypeError, ValueError):
                continue
            if dt and PRICE_RANGE[0] < p < PRICE_RANGE[1]:
                events.append((dt, f"{station}|{addr}", p, name, i))
                kept += 1
        per_file[name] = (seen, kept)
    events.sort()
    return events, per_file


def daily(events, trace=None):
    """Each day: every station's latest price by midnight, then the median across stations."""
    last, out, i = {}, [], 0
    d, end = events[0][0].date(), events[-1][0].date()
    while d <= end:
        cut = datetime.combine(d, datetime.max.time())
        while i < len(events) and events[i][0] <= cut:
            last[events[i][1]] = events[i]
            i += 1
        if len(last) > MIN_STATIONS:
            out.append({"d": d.isoformat(), "p": round(statistics.median(e[2] for e in last.values()), 1)})
            if trace == d.isoformat():
                return out[-1], dict(last)
        d += timedelta(days=1)
    return out, None


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("raw")
    ap.add_argument("out", nargs="?")
    ap.add_argument("--trace")
    a = ap.parse_args()

    events, per_file = read(a.raw)
    for name, (seen, kept) in per_file.items():
        print(f"{name:22} {seen:>9,} rows read  {kept:>7,} Sydney U91 changes kept", file=sys.stderr)

    if a.trace:
        day, last = daily(events, a.trace)
        prices = sorted(e[2] for e in last.values())
        print(json.dumps({
            "day": day, "stations": len(last),
            "lowest": prices[0], "highest": prices[-1],
            "middle_two": prices[(len(prices) - 1) // 2: len(prices) // 2 + 1],
            "same_day_changes": sum(1 for e in last.values() if e[0].date().isoformat() == a.trace),
            "example": [{"station": e[1].split("|")[0], "time": e[0].isoformat(" ", "minutes"), "price": e[2], "file": e[3], "row": e[4]}
                        for e in sorted(last.values(), key=lambda e: e[0])[-3:]],
        }, indent=1))
        return

    days, _ = daily(events)
    problems = []
    if len(per_file) != 12:
        problems.append(f"expected 12 monthly files, found {len(per_file)}")
    if len(days) < 360:
        problems.append(f"only {len(days)} days have more than {MIN_STATIONS} stations reporting")
    for prev, cur in zip(days, days[1:]):
        if abs(cur["p"] - prev["p"]) > 50:
            problems.append(f"median moved {cur['p'] - prev['p']:+.1f}c on {cur['d']}: check the file for that month")
    print(f"{len(events):,} changes, {len(days)} days, {days[0]['d']} to {days[-1]['d']}", file=sys.stderr)
    if problems:
        print("CHECKS FAILED\n  " + "\n  ".join(problems), file=sys.stderr)
        sys.exit(1)
    print("Checks passed", file=sys.stderr)
    if a.out:
        with open(a.out, "w") as fh:
            json.dump({"meta": {"source": SOURCE, "licence": "CC BY-SA", "files": len(per_file), "changes": len(events)}, "days": days}, fh, separators=(",", ":"))
        print(f"Wrote {a.out}", file=sys.stderr)


if __name__ == "__main__":
    main()