#!/usr/bin/env python3
"""Re-runs the check behind the post "Bank statement to Excel or CSV: which one to open".

lab   writes a six-row statement through the product's own writers, renderCsv
      (packages/exports/src/csv.ts) and renderXlsx (packages/exports/src/xlsx.ts, SheetJS),
      bundled with the repository's esbuild and run under node, then reads both files back
      with the Python standard library: csv.reader for the CSV, and the raw sheet XML of
      the workbook for each cell's type, value and number format.
http  fetches every cited page and reports whether the quoted sentence is still on it.

Exit 0 when every lab value matches dataset.json and every quoted page still carries its
quote, 1 otherwise. --recheck re-reads the pages and skips the lab when the repository's
dependencies are not installed (pnpm install), since it cannot run the writers without them.

Run from the repository: apps/web/public/blog/<slug> sits five levels below its root.
"""

from __future__ import annotations

import argparse
import base64
import csv
import glob
import html
import io
import json
import pathlib
import re
import subprocess
import sys
import tempfile
import time
import urllib.error
import urllib.request
import zipfile

HERE = pathlib.Path(__file__).resolve().parent
REPO = HERE.parents[4]
DATASET = HERE / "dataset.json"

# Four descriptions open on a character a spreadsheet reads as a formula, one holds a comma,
# one holds quotation marks. Amounts are USD minor units.
STATEMENT_ROWS = [
    ["2026-09-02", "-ATM WITHDRAWAL 1043 MAIN ST", -12000],
    ["2026-09-05", "+DEPOSIT MOBILE", 250075],
    ["2026-09-08", "=HYPERLINK FEE", -3550],
    ["2026-09-11", "@HOME SUPPLY", -1200],
    ["2026-09-13", "Payment, thank you", -123456789],
    ["2026-09-20", 'Check 1044 "rent"', -104400],
]

DRIVER = """
import { renderCsv } from "%(repo)s/packages/exports/src/csv.ts";
import { renderXlsx } from "%(repo)s/packages/exports/src/xlsx.ts";
import { money } from "@sc/shared";

const rows: [string, string, number][] = JSON.parse(process.argv[2]);
const statement: any = {
  bankId: null, layoutFamily: "digital-table", accountId: null,
  period: { from: "2026-09-01", to: "2026-09-30" },
  openingBalance: null, closingBalance: null, pageCount: 1,
  rows: rows.map(([date, description, minor], i) => ({
    ordinal: i + 1, date, description, counterparty: description,
    amount: money(BigInt(minor), "USD"), direction: minor < 0 ? "debit" : "credit",
    runningBalance: null, symbols: { variable: null, constant: null, specific: null },
    page: 1, note: null,
  })),
};
process.stdout.write(JSON.stringify({
  csv: Buffer.from(renderCsv(statement)).toString("base64"),
  xlsx: Buffer.from(renderXlsx(statement)).toString("base64"),
}));
"""

BUILTIN_NUMFMTS = {"0": "General", "1": "0", "2": "0.00", "3": "#,##0", "4": "#,##0.00"}


def dependencies_installed() -> bool:
    return bool(glob.glob(str(REPO / "node_modules/.pnpm/esbuild@*/node_modules/esbuild/bin/esbuild"))) and (
        REPO / "packages/exports/node_modules/xlsx"
    ).exists()


def run_product_writers() -> tuple[bytes, bytes]:
    esbuild = sorted(glob.glob(str(REPO / "node_modules/.pnpm/esbuild@*/node_modules/esbuild/bin/esbuild")))[-1]
    with tempfile.TemporaryDirectory() as tmp:
        source = pathlib.Path(tmp) / "driver.ts"
        bundle = pathlib.Path(tmp) / "driver.cjs"
        source.write_text(DRIVER % {"repo": REPO.as_posix()}, encoding="utf-8")
        subprocess.run(
            [esbuild, str(source), "--bundle", "--platform=node", "--format=cjs", "--log-level=warning",
             f"--alias:@sc/shared={REPO / 'packages/shared/src/index.ts'}", f"--outfile={bundle}"],
            check=True,
        )
        out = subprocess.run(
            ["node", str(bundle), json.dumps(STATEMENT_ROWS)], check=True, capture_output=True, text=True
        ).stdout
    files = json.loads(out)
    return base64.b64decode(files["csv"]), base64.b64decode(files["xlsx"])


def read_xlsx(data: bytes) -> tuple[dict[str, dict], int]:
    """Every cell as {t, v, format} from the raw sheet XML, and the number of formula elements."""
    with zipfile.ZipFile(io.BytesIO(data)) as archive:
        names = archive.namelist()
        shared = []
        if "xl/sharedStrings.xml" in names:
            shared = [
                html.unescape(re.sub(r"<[^>]+>", "", block))
                for block in re.findall(r"<si>(.*?)</si>", archive.read("xl/sharedStrings.xml").decode(), re.S)
            ]
        styles = archive.read("xl/styles.xml").decode()
        sheet = archive.read("xl/worksheets/sheet1.xml").decode()

    custom = dict(re.findall(r'<numFmt numFmtId="(\d+)" formatCode="([^"]*)"', styles))
    cell_xfs = re.search(r"<cellXfs[^>]*>(.*?)</cellXfs>", styles, re.S).group(1)
    xf_formats = []
    for xf in re.findall(r"<xf\b[^>]*/?>", cell_xfs):
        fmt_id = re.search(r'numFmtId="(\d+)"', xf).group(1)
        xf_formats.append(html.unescape(custom.get(fmt_id, BUILTIN_NUMFMTS.get(fmt_id, "id " + fmt_id))))

    cells: dict[str, dict] = {}
    for match in re.finditer(r'<c r="([A-Z]+\d+)"([^>]*)>(.*?)</c>', sheet, re.S):
        ref, attrs, body = match.groups()
        kind = re.search(r'\bt="(\w+)"', attrs)
        style = re.search(r'\bs="(\d+)"', attrs)
        raw = re.search(r"<v>(.*?)</v>", body, re.S)
        cell_type = kind.group(1) if kind else "n"
        value = html.unescape(raw.group(1)) if raw else ""
        if cell_type == "s":
            value = shared[int(value)]
        cells[ref] = {
            "t": cell_type,
            "v": value,
            "format": xf_formats[int(style.group(1))] if style else "General",
        }
    return cells, len(re.findall(r"<f[\s>]", sheet))


def measure_lab() -> dict[str, str]:
    csv_bytes, xlsx_bytes = run_product_writers()
    csv_text = csv_bytes.decode("utf-8")
    lines = csv_text.split("\r\n")
    parsed = list(csv.reader(io.StringIO(csv_text, newline="")))
    cells, formulas = read_xlsx(xlsx_bytes)
    body = range(2, 8)

    measured = {
        "csv_header": lines[0],
        "csv_ends_with_crlf": str(csv_text.endswith("\r\n")),
        "csv_starts_with_bom": str(csv_bytes[:3] == b"\xef\xbb\xbf"),
        "csv_date_cells": " | ".join(row[0] for row in parsed[1:]),
        "csv_amount_cells": " | ".join(row[2] for row in parsed[1:]),
        "csv_reader_row3_description": parsed[3][1],
        "xlsx_date_cell_types": " | ".join(cells[f"A{n}"]["t"] for n in body),
        "xlsx_date_cells": " | ".join(cells[f"A{n}"]["v"] for n in body),
        "xlsx_description_cell_types": " | ".join(cells[f"B{n}"]["t"] for n in body),
        "xlsx_description_cells": " | ".join(cells[f"B{n}"]["v"] for n in body),
        "xlsx_amount_cell_types": " | ".join(cells[f"C{n}"]["t"] for n in body),
        "xlsx_amount_cells": " | ".join(cells[f"C{n}"]["v"] for n in body),
        "xlsx_amount_number_formats": " | ".join(cells[f"C{n}"]["format"] for n in body),
        "xlsx_formula_elements": str(formulas),
    }
    for i in range(1, 7):
        measured[f"csv_line_{i}"] = lines[i]
    return measured


def normalise(text: str) -> str:
    text = re.sub(r"<script.*?</script>", " ", text, flags=re.S | re.I)
    text = re.sub(r"<style.*?</style>", " ", text, flags=re.S | re.I)
    text = re.sub(r"<[^>]+>", " ", text)
    text = html.unescape(text)
    for typographic, plain in (("’", "'"), ("‘", "'"), ("“", '"'), ("”", '"'), (" ", " ")):
        text = text.replace(typographic, plain)
    text = re.sub(r"\s+", " ", text).strip()
    return re.sub(r"\s+([,.;:])", r"\1", text)


def fetch(url: str) -> str:
    request = urllib.request.Request(
        url,
        headers={
            "User-Agent": (
                "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 "
                "(KHTML, like Gecko) Chrome/124.0 Safari/537.36"
            )
        },
    )
    with urllib.request.urlopen(request, timeout=45) as response:
        return response.read().decode("utf-8", "replace")


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--recheck", action="store_true", help="re-read the pages, skip a lab that cannot run")
    arguments = parser.parse_args()

    dataset = json.loads(DATASET.read_text(encoding="utf-8"))
    failures: list[str] = []
    warnings: list[str] = []
    not_run = 0

    lab: dict[str, str] | None = None
    if arguments.recheck and not dependencies_installed():
        warnings.append("lab skipped: the repository's dependencies are not installed")
    else:
        started = time.monotonic()
        lab = measure_lab()
        print(f"lab: {len(lab)} values measured in {time.monotonic() - started:.1f}s")

    for result in dataset["results"]:
        identifier = result["id"]
        if result["recheck"] == "lab":
            if lab is None:
                not_run += 1
            elif lab.get(identifier) != result["value"]:
                failures.append(f"{identifier}: dataset says {result['value']!r}, measured {lab.get(identifier)!r}")
            continue

        try:
            page = normalise(fetch(result["url"]))
        except (urllib.error.URLError, urllib.error.HTTPError, OSError) as error:
            if result["recheck"] == "manual":
                warnings.append(f"{identifier}: {result['url']} did not answer this host ({error}); re-read it by hand")
                not_run += 1
            else:
                failures.append(f"{identifier}: {result['url']} did not answer ({error})")
            continue
        if normalise(result["quote"]) not in page:
            failures.append(f"{identifier}: quote gone from {result['url']}")

    for line in warnings:
        print(f"warning: {line}")
    for line in failures:
        print(f"FAIL: {line}")
    total = len(dataset["results"])
    print(f"{total - not_run - len(failures)}/{total} rows hold, {not_run} not re-read, {len(failures)} failed")
    return 0 if not failures else 1


if __name__ == "__main__":
    sys.exit(main())