#!/usr/bin/env python3
"""Machine-logged furnace GT, replacing the hand-written 人機圖 CSV.

Why this exists. The CSV we had been scoring against (`20260630-recoreds.csv`)
is dated 06/30 while the videos are 06/17, and a null test showed its alignment
is not distinguishable from chance: shifting the whole GT by +60 s scored HIGHER
(recall 0.895) than the true offset (0.842), and any shift of +-3 min or more
still averaged 0.654. With load events every ~210 s and a +-90 s matching window,
event-matching recall simply cannot discriminate.

`20260617_強化爐進爐升降溫時間表.xlsx` fixes both problems at once:

  - it is machine-generated, so timestamps are exact, not minute-quantized
  - it is dated 2026-06-17, matching the videos
  - it covers 00:03:59 -> 23:53:42 (196 batches), so every one of the eight
    clips is covered, including the evening and night shifts the CSV never
    reached

Columns: 進入加熱段時間 (charge -> compare against load cycles), 進入冷卻段時間,
出爐時間 (discharge -> compare against UNLOAD cycles when that camera is built),
配方名稱, 加熱/急冷/慢冷秒數, 玻璃排列長度.

Caveat kept in the output: the furnace runs on a near-fixed period (median 214 s,
range 212-229 s in the pilot hour). A metronome cannot be matched event-by-event.
Use `a1_phase_metric.py` for scoring; count/rate agreement is the secondary check.
"""

from __future__ import annotations

import argparse
import json
from datetime import datetime, timedelta
from pathlib import Path

EXCEL_EPOCH = datetime(1899, 12, 30)

# clip_id -> wall-clock second of that clip's t=0
CLIP_T0 = {
    "load-0800": 8 * 3600,
    "load-1300": 13 * 3600,
    "load-1730": 17 * 3600 + 30 * 60,
    "load-2200": 22 * 3600,
    "unload-0800": 8 * 3600,
    "unload-1300": 13 * 3600,
    "unload-1730": 17 * 3600 + 30 * 60,
    "unload-2200": 22 * 3600,
}


def to_dt(v):
    if isinstance(v, datetime):
        return v
    try:
        return EXCEL_EPOCH + timedelta(days=float(v))
    except (TypeError, ValueError):
        return None


def main() -> int:
    ap = argparse.ArgumentParser()
    ap.add_argument("--xlsx", required=True)
    ap.add_argument("--out", required=True)
    args = ap.parse_args()

    import openpyxl

    ws = openpyxl.load_workbook(args.xlsx, data_only=True).worksheets[0]
    rows = list(ws.iter_rows(values_only=True))
    head = [str(c or "").strip() for c in rows[0]]
    col = {name: i for i, name in enumerate(head)}
    need = ["進入加熱段時間", "出爐時間"]
    for n in need:
        if n not in col:
            raise SystemExit(f"missing column {n}; got {head}")

    batches = []
    for r in rows[1:]:
        charge = to_dt(r[col["進入加熱段時間"]])
        if charge is None:
            continue
        out = to_dt(r[col["出爐時間"]])
        cool = to_dt(r[col.get("進入冷卻段時間", -1)]) if "進入冷卻段時間" in col else None
        sec = lambda d: None if d is None else d.hour * 3600 + d.minute * 60 + d.second
        batches.append(
            {
                "charge_clock_sec": sec(charge),
                "charge_hhmmss": charge.strftime("%H:%M:%S"),
                "cool_clock_sec": sec(cool),
                "discharge_clock_sec": sec(out),
                "discharge_hhmmss": out.strftime("%H:%M:%S") if out else None,
                "recipe": r[col["配方名稱"]] if "配方名稱" in col else None,
                "glass_len": r[col["玻璃排列長度"]] if "玻璃排列長度" in col else None,
            }
        )
    batches.sort(key=lambda b: b["charge_clock_sec"])

    per_clip = {}
    for cid, t0 in CLIP_T0.items():
        lo, hi = t0, t0 + 4 * 3600
        sel = [b for b in batches if b["charge_clock_sec"] is not None and lo <= b["charge_clock_sec"] < hi]
        per_clip[cid] = {
            "video_t0_wall_sec": t0,
            "n_batches": len(sel),
            "charge_video_t": [b["charge_clock_sec"] - t0 for b in sel],
            "discharge_video_t": [
                b["discharge_clock_sec"] - t0
                for b in sel
                if b["discharge_clock_sec"] is not None and lo <= b["discharge_clock_sec"] < hi
            ],
        }

    gaps = [
        batches[i + 1]["charge_clock_sec"] - batches[i]["charge_clock_sec"] for i in range(len(batches) - 1)
    ]
    gaps = sorted(g for g in gaps if 0 < g < 3600)
    out = {
        "source": str(args.xlsx),
        "date": "2026-06-17",
        "n_batches": len(batches),
        "first_charge": batches[0]["charge_hhmmss"],
        "last_charge": batches[-1]["charge_hhmmss"],
        "median_period_sec": gaps[len(gaps) // 2] if gaps else None,
        "period_p10_p90": [gaps[int(0.1 * len(gaps))], gaps[int(0.9 * len(gaps))]] if gaps else None,
        "warning": (
            "Near-periodic source: event-matching recall/precision is not "
            "discriminating at this density. Score with phase concentration "
            "(a1_phase_metric.py) plus count agreement."
        ),
        "batches": batches,
        "per_clip": per_clip,
    }
    Path(args.out).write_text(json.dumps(out, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
    print(
        f"{len(batches)} batches {out['first_charge']}-{out['last_charge']}  "
        f"median period {out['median_period_sec']}s (p10-p90 {out['period_p10_p90']})"
    )
    for cid, v in per_clip.items():
        print(f"  {cid:<12} charges={v['n_batches']:>3}  discharges={len(v['discharge_video_t']):>3}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())