"""Verify original browser output without modifying or re-exporting the workbook."""

import csv
import argparse
import hashlib
import json
import sys
import xml.etree.ElementTree as ET
from pathlib import Path
from zipfile import ZipFile

root = Path(__file__).resolve().parents[1]
evidence = root / "public/evidence/2026-09-24"
parser = argparse.ArgumentParser()
parser.add_argument("workbook", nargs="?", type=Path, default=evidence / "csv-leading-zeros.xlsx")
parser.add_argument("--write-report", action="store_true")
args = parser.parse_args()
output = args.workbook
source = evidence / "csv-leading-zeros.csv"
ns = {"s": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
expected = list(csv.reader(source.read_text().splitlines()))

with ZipFile(output) as archive:
    workbook = ET.fromstring(archive.read("xl/workbook.xml"))
    sheets = workbook.findall("s:sheets/s:sheet", ns)
    assert len(sheets) == 1 and sheets[0].get("name") == "Public sample"
    shared = []
    if "xl/sharedStrings.xml" in archive.namelist():
        shared = ["".join(item.itertext()) for item in ET.fromstring(archive.read("xl/sharedStrings.xml"))]
    sheet = ET.fromstring(archive.read("xl/worksheets/sheet1.xml"))
    cells = []
    for cell in sheet.findall("s:sheetData/s:row/s:c", ns):
        address, kind = cell.get("r"), cell.get("t")
        assert cell.find("s:f", ns) is None, "Test output must not contain formulas"
        value = cell.findtext("s:v", default="", namespaces=ns)
        if kind == "s":
            value = shared[int(value)]
        elif kind == "inlineStr":
            value = "".join(cell.find("s:is", ns).itertext())
        assert kind in ("s", "str", "inlineStr"), "Expected text storage for these CSV identifiers"
        column = ord(address[0]) - ord("A")
        row = int(address[1:]) - 1
        assert value == expected[row][column], f"Public fixture mismatch in {address}"
        cells.append({"address": address, "value": value, "storageType": kind})
    assert len(cells) == len({cell["address"] for cell in cells}) == 20

report = {
    "date": "2026-09-24",
    "toolUrl": "https://privconvert.app/tools/csv-to-excel",
    "method": "First-party live browser run in Chrome 150 on macOS, using pasted generated CSV, automatic delimiter detection, and sheet name Public sample. Original downloaded XLSX inspected with an independent workbook importer and XML storage checks; not edited or re-exported.",
    "scope": "One valid comma-separated fixture with a header and three data rows. Not a large-file, malformed-input, native Excel UI, or formatting-fidelity benchmark.",
    "sheet": "Public sample",
    "rowsIncludingHeader": 4,
    "columns": 5,
    "allValuesMatch": True,
    "allCellsStoredAsText": True,
    "cells": cells,
    "files": [
        {"name": name, "bytes": path.stat().st_size, "sha256": hashlib.sha256(path.read_bytes()).hexdigest()}
        for name, path in [("csv-leading-zeros.csv", source), ("csv-leading-zeros.xlsx", output)]
    ],
}
if args.write_report:
    (evidence / "results.json").write_text(json.dumps(report, ensure_ascii=False, indent=2) + "\n")
print(json.dumps({"result": "PASS", "cells": len(cells), "allValuesMatch": True, "allCellsStoredAsText": True, "files": report["files"]}, indent=2))