#!/usr/bin/env python3
"""
Audit completo del file master 07_DVR AGENZIE_TWISTER.xlsx.

Per ogni foglio:
- struttura (righe, colonne)
- celle popolate raggruppate per "blocco logico" (header capitolo, riga
  dato, riga note, ecc.)
- data validation list (sqref + range delle opzioni)

Output: report markdown completo in docs/01_dizionario_master.md aggiornato
e copia di backup in /tmp/twister_audit.md.

Lo scopo è avere una mappa esaustiva PRIMA di rifare il parser di
import: quali fogli, quali celle, quali tendine, dove vanno nei campi
del gestionale.
"""
from __future__ import annotations
import re
import sys
import warnings
import zipfile
from pathlib import Path

warnings.filterwarnings("ignore")
from openpyxl import load_workbook  # noqa
from openpyxl.cell import Cell  # noqa

ROOT = Path(__file__).resolve().parents[2]
MASTER = ROOT / "-mat" / "07_DVR AGENZIE_TWISTER.xlsx"


def cellref(col: int, row: int) -> str:
    s = ""
    n = col
    while n > 0:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return f"{s}{row}"


def dump_sheet_structure(ws, max_rows: int = 60, max_cols: int = 8) -> list[str]:
    """Stampa righe popolate, troncate a 80 char, con coordinate Excel."""
    out = []
    out.append(f"### Foglio: {ws.title}  (righe={ws.max_row}, colonne={ws.max_column})")
    out.append("")
    for r in range(1, min(max_rows + 1, ws.max_row + 1)):
        row_cells = []
        for c in range(1, min(max_cols + 1, ws.max_column + 1)):
            v = ws.cell(r, c).value
            if v is None:
                continue
            s = re.sub(r"\s+", " ", str(v).strip())
            if len(s) > 90:
                s = s[:87] + "…"
            row_cells.append(f"{cellref(c, r)}={s!r}")
        if row_cells:
            out.append("  " + " | ".join(row_cells))
    if ws.max_row > max_rows:
        out.append(f"  …(altre {ws.max_row - max_rows} righe)…")
    out.append("")
    return out


def get_data_validations(zip_path: Path, sheet_xml_path: str) -> list[tuple[str, str]]:
    """Ritorna lista di (sqref, formula1) per le dataValidation list di un foglio."""
    with zipfile.ZipFile(zip_path) as z:
        try:
            content = z.read(sheet_xml_path).decode("utf-8", errors="replace")
        except KeyError:
            return []
    rules: list[tuple[str, str]] = []
    # Standard dataValidation
    for m in re.finditer(
        r'<dataValidation[^>]*type="list"[^>]*sqref="([^"]+)".*?<formula1[^>]*>(.*?)</formula1>',
        content,
        re.DOTALL,
    ):
        rules.append((m.group(1), m.group(2)))
    # Extension dataValidation (x14)
    for m in re.finditer(
        r"<x14:dataValidation[^>]*type=\"list\".*?</x14:dataValidation>",
        content,
        re.DOTALL,
    ):
        sqref_m = re.search(r"<xm:sqref>(.*?)</xm:sqref>", m.group(0), re.DOTALL)
        formula_m = re.search(r"<xm:f>(.*?)</xm:f>", m.group(0), re.DOTALL)
        if sqref_m and formula_m:
            rules.append((sqref_m.group(1), formula_m.group(1)))
    return rules


def main():
    if not MASTER.exists():
        sys.exit(f"Master non trovato: {MASTER}")

    wb = load_workbook(MASTER, data_only=True, read_only=False)

    out_lines: list[str] = []
    out_lines.append("# Audit master TWISTER — `07_DVR AGENZIE_TWISTER.xlsx`")
    out_lines.append("")
    out_lines.append(f"Totale fogli: {len(wb.sheetnames)}")
    out_lines.append("")
    out_lines.append("## Indice fogli")
    for s in wb.sheetnames:
        ws = wb[s]
        out_lines.append(f"- **{s}** — {ws.max_row} righe × {ws.max_column} col")
    out_lines.append("")

    # Mapping path → name
    with zipfile.ZipFile(MASTER) as z:
        wb_xml = z.read("xl/workbook.xml").decode()
        wb_rels = z.read("xl/_rels/workbook.xml.rels").decode()
    rid_to_target = {
        m.group(1): m.group(2)
        for m in re.finditer(
            r'Id="([^"]+)"[^/]*Target="([^"]+)"', wb_rels
        )
        if "worksheet" in m.group(0)
    }
    name_to_path: dict[str, str] = {}
    for m in re.finditer(
        r'<sheet[^/]*name="([^"]+)"[^/]*r:id="([^"]+)"', wb_xml
    ):
        target = rid_to_target.get(m.group(2))
        if target:
            name_to_path[m.group(1)] = "xl/" + target

    # Per ogni foglio: struttura + data validations
    for s in wb.sheetnames:
        ws = wb[s]
        out_lines.extend(dump_sheet_structure(ws))

        sheet_xml = name_to_path.get(s)
        if sheet_xml:
            dvs = get_data_validations(MASTER, sheet_xml)
            if dvs:
                out_lines.append(f"#### Tendine (data validation list) — {len(dvs)} regole")
                for sqref, formula in dvs:
                    out_lines.append(f"- `{sqref}` → `{formula}`")
                out_lines.append("")

    out_path = Path("/tmp/twister_audit.md")
    out_path.write_text("\n".join(out_lines))
    print(f"Audit completo scritto in: {out_path}")
    print(f"Lines: {len(out_lines)}")


if __name__ == "__main__":
    main()