#!/usr/bin/env python3
"""
Estrae le tendine canoniche dal master TWISTER (foglio ListaF1 e ListaF2)
e le inserisce in:
- lookup_pericoli_combo (svuotato e ripopolato)
- lookup_valutazione_misure (svuotato e ripopolato)

ListaF1 — pericoli:
- Header row con rif numerici X.Y.Z in una colonna
- Le opzioni della tendina di quel rif sono nelle righe successive nella
  stessa colonna, finché non si incontra un nuovo rif (in qualsiasi colonna)
- col 1 e 2 → tendine col C (Verifica e Commento) di rif diversi
- col 3+ → tendine col F (Note) del rif dichiarato in col 1 dello stesso
  blocco (se ce n'è uno)
- Righe meta scartate: "1_…", "2a:…", "nota per compilazione", multilinea
  con "\\n" (sono concatenazioni di tutte le opzioni come summary)

ListaF2 — valutazione misure di prevenzione:
- Header row con rif = id intero dell'elemento di valutazione
- Opzioni nelle righe successive in col 1
- Stessi filtri meta

Uso:
  python3 import-tools/scripts/extract_lookups.py            # solo dump
  python3 import-tools/scripts/extract_lookups.py --apply    # popola dev.db
"""
from __future__ import annotations
import re
import sqlite3
import sys
import warnings
from pathlib import Path

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

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

RIF_RE = re.compile(r"^\d+(\.\d+)+$")
INT_RE = re.compile(r"^\d+$")


def is_meta(s: str) -> bool:
    if not s:
        return True
    sl = s.lower()
    if sl.startswith("nota per compilazione"):
        return True
    if "\n" in s:
        return True
    if re.match(r"^\d+_", s) or re.match(r"^\d+[a-z]:", s) or re.match(r"^\d+\)", s):
        return True
    if sl in ("u/p", "c/n", "si/no"):
        return True
    return False


def parse_columns(ws, rif_pattern: re.Pattern, max_col: int) -> dict[int, list[tuple[str, list[str]]]]:
    """Per ogni colonna ritorna [(rif, [opzioni]), ...]."""
    out: dict[int, list[tuple[str, list[str]]]] = {c: [] for c in range(1, max_col + 1)}
    for c in range(1, max_col + 1):
        current_rif: str | None = None
        current_opts: list[str] = []
        for r in range(1, ws.max_row + 1):
            v = ws.cell(r, c).value
            s = str(v).strip() if v is not None else ""
            if not s:
                continue
            if rif_pattern.match(s):
                if current_rif is not None:
                    out[c].append((current_rif, current_opts))
                current_rif = s
                current_opts = []
            else:
                if current_rif is None or is_meta(s):
                    continue
                current_opts.append(s)
        if current_rif is not None:
            out[c].append((current_rif, current_opts))
    return out


def parse_listaf1(wb) -> list[tuple[str, str, str]]:
    """Ritorna lista di (rif, colonna, testo) per lookup_pericoli_combo.
    colonna = 'C' o 'F'.
    """
    ws = wb["ListaF1"]
    cols = parse_columns(ws, RIF_RE, ws.max_column)

    out: list[tuple[str, str, str]] = []

    # col 1 e col 2: tendine C (di rif diversi)
    for c in (1, 2):
        for rif, opts in cols.get(c, []):
            for o in opts:
                out.append((rif, "C", o))

    # col 3+: tendine F. Si applicano al rif dichiarato in col 1 dello stesso blocco.
    # Approssimazione: per ogni blocco di col 3, cerchiamo il rif col 1 più vicino (sopra)
    # nello stesso intervallo di righe. Se col 3 ha un proprio rif, usiamo quello.
    for c in range(3, ws.max_column + 1):
        for rif, opts in cols.get(c, []):
            for o in opts:
                out.append((rif, "F", o))

    return out


def parse_listaf2(wb) -> list[tuple[int, str]]:
    """Ritorna lista di (elemento_id, testo) per lookup_valutazione_misure.
    L'elemento_id è il rif intero in col 1 di ListaF2.
    """
    ws = wb["ListaF2"]
    cols = parse_columns(ws, INT_RE, ws.max_column)

    out: list[tuple[int, str]] = []
    # Solo col 1 (col 2 sembra accessoria, vedi note: "se si opta per la voce 2:")
    for rif, opts in cols.get(1, []):
        try:
            elem_id = int(rif)
        except ValueError:
            continue
        for o in opts:
            out.append((elem_id, o))
    return out


def main():
    apply = "--apply" in sys.argv

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

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

    pericoli_lookups = parse_listaf1(wb)
    misure_lookups = parse_listaf2(wb)

    print(f"ListaF1: {len(pericoli_lookups)} opzioni totali per pericoli")
    print(f"  - col C: {sum(1 for _,c,_ in pericoli_lookups if c == 'C')}")
    print(f"  - col F: {sum(1 for _,c,_ in pericoli_lookups if c == 'F')}")
    print(f"  - rif distinti: {len(set(r for r,_,_ in pericoli_lookups))}")

    print(f"\nListaF2: {len(misure_lookups)} opzioni totali per valutazione misure")
    print(f"  - elementi distinti: {len(set(e for e,_ in misure_lookups))}")

    if not apply:
        # Dump esempi
        print("\n=== Esempi pericoli col C ===")
        seen = set()
        for rif, col, testo in pericoli_lookups:
            if col != "C" or rif in seen:
                continue
            seen.add(rif)
            opts = [t for r, c, t in pericoli_lookups if r == rif and c == "C"]
            print(f"  [{rif}] {len(opts)} opzioni:")
            for o in opts[:3]:
                print(f"    • {o[:80]}")
            if len(seen) >= 5:
                break

        print("\n=== Esempi valutazione misure ===")
        seen = set()
        for eid, testo in misure_lookups:
            if eid in seen:
                continue
            seen.add(eid)
            opts = [t for e, t in misure_lookups if e == eid]
            print(f"  [elem {eid}] {len(opts)} opzioni:")
            for o in opts[:3]:
                print(f"    • {o[:80]}")
            if len(seen) >= 5:
                break
        return

    # APPLY
    conn = sqlite3.connect(DB)
    cur = conn.cursor()

    # Risolvi rif → punto_id
    cur.execute("SELECT id, rif FROM lookup_pericoli_punti")
    rif_to_pid = {row[1]: row[0] for row in cur.fetchall()}

    # Cleanup
    cur.execute("DELETE FROM lookup_pericoli_combo")
    cur.execute("DELETE FROM lookup_valutazione_misure")
    print("# lookup pericoli_combo e valutazione_misure svuotati")

    n_inserted_p = 0
    skipped_no_punto = 0
    for rif, colonna, testo in pericoli_lookups:
        pid = rif_to_pid.get(rif)
        if not pid:
            skipped_no_punto += 1
            continue
        cur.execute(
            "INSERT INTO lookup_pericoli_combo (punto_id, colonna, testo) VALUES (?, ?, ?)",
            (pid, colonna, testo),
        )
        n_inserted_p += 1

    n_inserted_m = 0
    cur.execute("SELECT id FROM lookup_valutazione_elementi")
    valid_elem_ids = {row[0] for row in cur.fetchall()}
    for eid, testo in misure_lookups:
        if eid not in valid_elem_ids:
            continue
        cur.execute(
            "INSERT INTO lookup_valutazione_misure (elemento_id, colonna, testo) VALUES (?, 'D', ?)",
            (eid, testo),
        )
        n_inserted_m += 1

    conn.commit()
    conn.close()
    print(f"# {n_inserted_p} righe in lookup_pericoli_combo (skip {skipped_no_punto} per rif non in master)")
    print(f"# {n_inserted_m} righe in lookup_valutazione_misure")


if __name__ == "__main__":
    main()