#!/usr/bin/env python3
"""Reconcile NHS England's "Chest X-ray AI - Studies Successfully Processed by NHS Trust, by Month"
(July 2026), the source the Budget 2026 cites in footnote 66.

  python3 -I cxr-throughput-reconciliation.py.txt 20260707---cxr-throughput-statistics---aidf1.xlsx cxr-throughput-reconciliation.json

Standard library only. Reads the single worksheet, then reports: the national monthly totals and running
count; the sum of trust rows and of network subtotals against the national row, month by month; any
network whose subtotal differs from the sum of its trusts in a month; trusts whose Total column differs
from the sum of their monthly columns; and the three Greater Manchester trusts whose notes give a larger
"reconciled cumulative" total. Checks the sheet's own grand total and the ministerial answer to PQ 30882
(781,000 for Jan-Mar 2026), then writes the JSON named as the second argument."""
import hashlib, html, json, re, sys, zipfile

src, dest = sys.argv[1], sys.argv[2]

def colidx(c):
    n = 0
    for ch in c: n = n * 26 + ord(ch) - 64
    return n

z = zipfile.ZipFile(src)
ss = [html.unescape(re.sub(r"<[^>]+>", "", m)) for m in re.findall(r"<si>(.*?)</si>", z.read("xl/sharedStrings.xml").decode(), re.S)]
rows = {}
for r in re.finditer(r'<row [^>]*r="(\d+)"[^>]*>(.*?)</row>', z.read("xl/worksheets/sheet1.xml").decode(), re.S):
    cells = {}
    for c in re.finditer(r'<c r="([A-Z]+)\d+"([^>]*?)(?:/>|>(.*?)</c>)', r.group(2), re.S):
        col, attrs, inner = c.groups()
        v = re.search(r"<v>([^<]*)</v>", inner or "")
        if v: cells[col] = ss[int(v.group(1))] if 't="s"' in attrs else float(v.group(1))
    rows[int(r.group(1))] = cells

header = next(i for i in sorted(rows) if rows[i].get("A") == "Imaging Network")
grand = next(i for i in rows if any(isinstance(v, str) and "GRAND TOTAL" in v for v in rows[i].values()))
H, G = rows[header], rows[grand]
tot_col = next(c for c in H if H[c] == "Total")
mcols = sorted((c for c in H if colidx(c) >= 4 and c != tot_col), key=colidx)
is_sub = lambda i: "subtotal" in (str(rows[i].get("A", "")) + str(rows[i].get("B", ""))).lower()
body = [i for i in range(header + 1, grand) if rows.get(i)]
sub_rows = [i for i in body if is_sub(i)]
trust_rows = [i for i in body if not is_sub(i) and rows[i].get("B")]
num = lambda i, c: rows[i].get(c, 0) if isinstance(rows[i].get(c, 0), float) else 0
msum = lambda i: sum(num(i, c) for c in mcols)

# Each network's trust rows sit directly above its subtotal row.
networks, pending = [], []
for i in body:
    if is_sub(i):
        networks.append((rows[i].get("A") or rows[i].get("B"), i, pending)); pending = []
    elif i in trust_rows:
        pending.append(i)

months, cum = [], 0
for c in mcols:
    if isinstance(G.get(c), float):
        cum += G[c]
        months.append({"month": H[c], "studies": int(G[c]), "cumulative": int(cum)})
total = int(G[tot_col])
by = {m["month"]: m for m in months}
jan_mar_2026 = sum(by[m]["studies"] for m in ("Jan 26", "Feb 26", "Mar 26"))
assert jan_mar_2026 == 781_000, jan_mar_2026          # PQ 30882 answer, 19 Oct 2026
assert total == 4_303_064, total                      # sheet's own GRAND TOTAL

unassigned_note = next(s for s in ss if s.startswith("Bolton, Tameside and WWL"))
gm = {n: {"listed_total": int(a.replace(",", "")), "reconciled_total_in_notes": int(b.replace(",", ""))}
      for n, a, b in re.findall(r"(Bolton|Tameside|WWL) ([\d,]+) of ([\d,]+)", unassigned_note)}
out = {
    "source": "NHS England, “Chest X-ray AI – Studies Successfully Processed by NHS Trust, by Month” (July 2026), "
              "file 20260707---cxr-throughput-statistics---aidf1.xlsx; retrieved 31 October 2026 from a public mirror of digital.nhs.uk.",
    "licence_note": "Derived from NHS England data. Totals only; no patient-level data.",
    "sha256": hashlib.sha256(open(src, "rb").read()).hexdigest(),
    "metric": next(s for s in ss if s.startswith("Metric:")),
    "grand_total": total,
    "monthly_sum_to_may_2026": months[-1]["cumulative"],
    "not_in_monthly_columns": total - months[-1]["cumulative"],
    "first_month_cumulative_over_4m": next(m["month"] for m in months if m["cumulative"] >= 4_000_000),
    "jan_mar_2026": jan_mar_2026,
    "trust_rows": len(trust_rows),
    "trust_rows_total": int(sum(num(i, tot_col) for i in trust_rows)),
    "trust_rows_monthly_total": int(sum(msum(i) for i in trust_rows)),
    "unexplained_difference_trust_rows_vs_national": total - int(sum(num(i, tot_col) for i in trust_rows)),
    "trusts_with_total_not_equal_monthly_sum": {rows[i]["B"]: int(num(i, tot_col) - msum(i))
                                                for i in trust_rows if abs(num(i, tot_col) - msum(i)) > 0.5},
    "gm_trusts_listed_vs_reconciled": gm,
    "notes_reconciled_minus_listed_totals_3_gm_trusts": sum(v["reconciled_total_in_notes"] - v["listed_total"] for v in gm.values()),
    "unassigned_note": unassigned_note,
    "network_subtotals": {name: int(num(s, tot_col)) for name, s, _ in networks},
    "network_month_mismatches": [
        {"network": name, "month": H[c], "subtotal_cell": f"{c}{s}", "subtotal": int(num(s, c)),
         "sum_of_trusts": int(sum(num(t, c) for t in ts)), "trust_cells": [f"{c}{t}" for t in ts]}
        for name, s, ts in networks for c in mcols if abs(num(s, c) - sum(num(t, c) for t in ts)) > 0.5],
    "monthly_reconciliation": [
        {"month": H[c], "national_row": int(G.get(c, 0)), "sum_of_trust_rows": int(sum(num(i, c) for i in trust_rows)),
         "sum_of_network_subtotals": int(sum(num(i, c) for i in sub_rows))}
        for c in mcols if isinstance(G.get(c), float)],
    "months": months,
    "quarter_end_cumulative_millions": {m["month"]: round(m["cumulative"] / 1e6, 2)
                                        for m in months if m["month"][:3] in ("Mar", "Jun", "Sep", "Dec")},
}
json.dump(out, open(dest, "w"), indent=1, ensure_ascii=False)
print(json.dumps({k: out[k] for k in ("grand_total", "trust_rows_total", "network_month_mismatches",
                                      "notes_reconciled_minus_listed_totals_3_gm_trusts")}, indent=1, ensure_ascii=False))