"""Check the frozen ONS cells and independently reproduce all 30 scenarios.

Save beside report.json, inputs.json, annual-pay-gross.xlsx and annual-pay-gross-cv.xlsx.
Run with Python 3. Standard library only; no account, installation or network.
This checks published assumptions, not individual payroll or professional advice.
Reuse with credit and a link to the report URL recorded in report.json.
"""
import hashlib
import json
import zipfile
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
from xml.etree import ElementTree as ET

D = Decimal
PENNY = D("0.01")
NS = {"s": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
BANDS = {
    "england": [(37700, "0.20"), (125140, "0.40"), (None, "0.45")],
    "scotland": [(3967, "0.19"), (16956, "0.20"), (31092, "0.21"),
                 (62430, "0.42"), (125140, "0.45"), (None, "0.48")],
}
LOANS = {"plan2": (29385, "0.09"), "plan5": (25000, "0.09"), "postgraduate": (21000, "0.06")}


def money(value):
    return D(value).quantize(PENNY, rounding=ROUND_HALF_UP)


def annual(salary, region, plans):
    salary = D(str(salary))
    allowance = max(D(0), D(12570) - max(D(0), salary - 100000) / 2)
    taxable = max(D(0), salary - allowance)
    tax, lower = D(0), D(0)
    for upper, rate in BANDS[region]:
        upper = taxable if upper is None else D(upper)
        tax += money(max(D(0), min(taxable, upper) - lower) * D(rate))
        lower = upper
    ni = money(max(D(0), min(salary, D(50270)) - 12570) * D("0.08"))
    ni += money(max(D(0), salary - 50270) * D("0.02"))
    repayments = sum((money(max(D(0), salary - LOANS[p][0]) * D(LOANS[p][1])) for p in plans), D(0))
    return money(salary - tax - ni - repayments)


def sheet_cells(path, sheet_name):
    with zipfile.ZipFile(path) as book:
        workbook = ET.fromstring(book.read("xl/workbook.xml"))
        sheet = next(s for s in workbook.findall("s:sheets/s:sheet", NS) if s.attrib["name"] == sheet_name)
        links = ET.fromstring(book.read("xl/_rels/workbook.xml.rels"))
        link = next(item for item in links if item.attrib["Id"] == sheet.attrib[f"{{{R}}}id"])
        target = link.attrib["Target"]
        target = target.lstrip("/") if target.startswith("/") else f"xl/{target}"
        xml = ET.fromstring(book.read(target))
        return {cell.attrib["r"]: cell.findtext("s:v", namespaces=NS)
                for cell in xml.findall("s:sheetData/s:row/s:c", NS)}


def verify():
    root = Path(__file__).parent
    report = json.loads((root / "report.json").read_text(encoding="utf-8"))
    inputs = json.loads((root / "inputs.json").read_text(encoding="utf-8"))
    assert report["version"] == "1.0" and report["taxYear"] == "2026-27"
    assert report["inputs"] == inputs
    cells = []
    for workbook in inputs["workbooks"]:
        path = root / workbook["file"]
        assert hashlib.sha256(path.read_bytes()).hexdigest() == workbook["sha256"]
        cells.append(sheet_cells(path, workbook["sheet"]))
    for benchmark in inputs["benchmarks"]:
        assert D(cells[0][benchmark["cell"]]) == D(str(benchmark["grossAnnual"]))
        assert D(cells[1][benchmark["cell"]]) == D(str(benchmark["cvPercent"]))
        assert D(str(benchmark["cvPercent"])) <= 5
    assert len(report["rows"]) == report["scenarioCount"] == 30
    identities = set()
    for row in report["rows"]:
        identity = (row["benchmark"], row["region"], row["loanScenario"])
        assert identity not in identities
        identities.add(identity)
        source = next(b for b in inputs["benchmarks"] if b["label"] == row["benchmark"])
        assert D(str(row["salaryBefore"])) == D(str(source["grossAnnual"]))
        assert D(str(row["salaryAfter"])) - D(str(row["salaryBefore"])) == 1000
        before = annual(row["salaryBefore"], row["region"], row["plans"])
        after = annual(row["salaryAfter"], row["region"], row["plans"])
        expected = {"netBefore": before, "netAfter": after, "extraNetAnnual": after - before,
                    "extraNetMonthlyEquivalent": money((after - before) / 12)}
        for field, value in expected.items():
            assert money(str(row[field])) == value, (identity, field, row[field], value)
    print("Verified both original workbook checksums and five source cells/CVs; reproduced all 30 scenarios.")


if __name__ == "__main__":
    verify()