#!/usr/bin/env python3
"""
Import di un singolo file DVR (xlsx) nelle tabelle dvr / dvr_pericoli / dvr_valutazione / dvr_incendio_doc / dvr_piano_miglioramento.

Strategia:
1. Parse del filename → (NOME_AGENZIA, SEDE, REV, DATA, EXT)
2. Match nella tabella `agenzie` con varie strategie di normalizzazione
3. Lettura del file (xlsx in questo MVP, docx in step successivo)
4. Estrazione dati e insert
5. Le combo Pericoli e Valutazione vengono create on-the-fly in `lookup_pericoli_combo` /
   `lookup_valutazione_misure` se il testo trovato non è ancora presente.

Uso:
    python3 scripts/import_dvr.py "<path/file.xlsx>"
    python3 scripts/import_dvr.py --all   # importa tutta la cartella -mat/DVR/
"""
from __future__ import annotations
import argparse
import os
import re
import sqlite3
import sys
import warnings
from datetime import datetime
from pathlib import Path

warnings.filterwarnings("ignore")

try:
    from openpyxl import load_workbook
except ImportError:
    sys.exit("openpyxl non installato. pip3 install --user openpyxl")
try:
    from docx import Document
except ImportError:
    sys.exit("python-docx non installato. pip3 install --user python-docx")

ROOT = Path(__file__).resolve().parents[2]
# DB configurabile via env IMPORT_DB (per lavorare su una copia della produzione).
DB = Path(os.environ["IMPORT_DB"]) if os.environ.get("IMPORT_DB") else ROOT / "import-tools" / "db" / "dev.db"
DVR_DIR = ROOT / "-mat" / "DVR"

FNAME_PAT = re.compile(
    # accetta anche estensioni doppie come ".pdf.xlsx"
    r"^(?P<ag>.+?)_(?P<sede>.+?)_Rev_?(?P<rev>\d+)_(?P<data>\d{6,8})(?:\.[a-z0-9]+)?\.(?P<ext>xlsx|docx)$",
    re.IGNORECASE,
)


def norm(s) -> str | None:
    if s is None:
        return None
    out = re.sub(r"\s+", " ", str(s).replace("\xa0", " ").strip())
    return out or None


def norm_key(s: str) -> str:
    """Per matching: tutto MAIUSCOLO, no parentesi, no apostrofi/trattini."""
    if not s:
        return ""
    s = re.sub(r"\s*\([^)]*\)\s*", " ", s)  # rimuovi "(... TW)"
    s = s.replace("'", " ").replace("-", " ").replace(".", " ")
    s = re.sub(r"\s+", " ", s.strip().upper())
    return s


def parse_filename(fn: str):
    m = FNAME_PAT.match(fn)
    if not m:
        return None
    rev = int(m["rev"])
    raw_data = m["data"]
    data_iso = None
    if len(raw_data) == 8:
        try:
            data_iso = datetime.strptime(raw_data, "%d%m%Y").date().isoformat()
        except ValueError:
            data_iso = None
    return {
        "nome_agenzia": m["ag"].strip(),
        "sede": m["sede"].strip(),
        "rev": rev,
        "data": data_iso,
        "ext": m["ext"].lower(),
    }


def lookup_override(cur: sqlite3.Cursor, filename: str):
    """Consulta filename_overrides per un match già deciso dal rematch intelligente."""
    row = cur.execute(
        """SELECT a.id, a.codice_fisico, a.tipologia, a.nome_agenzia, a.nome_sede
           FROM filename_overrides f JOIN agenzie a ON a.id = f.agenzia_id
           WHERE f.filename = ?""",
        (filename,),
    ).fetchone()
    return row


def find_agenzia(cur: sqlite3.Cursor, nome_ag: str, sede: str):
    """Strategie di match progressive. Ritorna la riga `agenzie` o None.

    Strategie nell'ordine:
    1. AG.GEN: filename `<X>_CENTRO AGENZIALE` ↔ anagrafica `<X>` + `AGENZIA GENERALE`
    2. AG.GEN: anagrafica `<Y> (<X> TW)` o `<Y> (... <X> ...)` (accorpamento storico)
    3. ISP.AG: match esatto per (nome_agenzia, nome_sede)
    4. ISP.AG: anagrafica `<Z> (<X> TW)` con sede troncata (es. SANTA MARIA DI CASTELLABATE → "SANTA MARIA DI CAS")
    5. ISP.AG: gestione "SANT'/SAN'/SANT/SAN" alternativi
    """
    nome_ag_k = norm_key(nome_ag)
    sede_k = norm_key(sede)
    ag_rows = list(cur.execute(
        "SELECT id, codice_fisico, tipologia, nome_agenzia, nome_sede FROM agenzie"
    ))

    def name_contains_tw(name: str, x: str) -> bool:
        m = re.search(r"\(([^)]+)\)", name or "")
        if not m:
            return False
        return x in norm_key(m.group(1))

    if sede_k == "CENTRO AGENZIALE":
        target = "AGENZIA GENERALE"
        for row in ag_rows:
            if row[2] == "AG. GEN." and norm_key(row[3]) == nome_ag_k and norm_key(row[4]) == target:
                return row
        for row in ag_rows:
            if row[2] == "AG. GEN." and norm_key(row[4]) == target and name_contains_tw(row[3], nome_ag_k):
                return row
        return None

    for row in ag_rows:
        if norm_key(row[3]) == nome_ag_k and norm_key(row[4]) == sede_k:
            return row

    sede_alt = sede_k.replace("SANT ", "S ").replace("SAN ", "S ")
    if sede_alt != sede_k:
        for row in ag_rows:
            if norm_key(row[3]) == nome_ag_k and norm_key(row[4]) == sede_alt:
                return row

    for row in ag_rows:
        if name_contains_tw(row[3], nome_ag_k) and (
            norm_key(row[4]) == sede_k or norm_key(row[4]) == sede_alt
        ):
            return row

    for row in ag_rows:
        if name_contains_tw(row[3], nome_ag_k) and norm_key(row[4]) and (
            sede_k.startswith(norm_key(row[4])[:12]) or norm_key(row[4]).startswith(sede_k[:12])
        ):
            return row

    for row in ag_rows:
        if row[2] != "AG. GEN." and norm_key(row[3]).startswith(nome_ag_k) and norm_key(row[4]).startswith(sede_k):
            return row
    return None


def get_or_create_pericolo_combo(cur, punto_id: int, colonna: str, testo: str) -> int | None:
    """Match esatto col lookup canonico (popolato da extract_lookups.py).
    Se non trovato, ritorna None (il chiamante salverà il testo come override).
    NON crea più nuove combo on-the-fly: il master TWISTER è la sola fonte
    di verità per le tendine.
    """
    if not testo:
        return None
    testo_n = norm(testo)
    if not testo_n:
        return None
    row = cur.execute(
        "SELECT id FROM lookup_pericoli_combo WHERE punto_id = ? AND colonna = ? AND testo = ?",
        (punto_id, colonna, testo_n),
    ).fetchone()
    return row[0] if row else None


def get_or_create_valutazione_misura(cur, elemento_id: int, colonna: str, testo: str) -> int | None:
    """Match esatto col lookup canonico (popolato da extract_lookups.py)."""
    if not testo:
        return None
    testo_n = norm(testo)
    if not testo_n:
        return None
    row = cur.execute(
        "SELECT id FROM lookup_valutazione_misure WHERE elemento_id = ? AND colonna = ? AND testo = ?",
        (elemento_id, colonna, testo_n),
    ).fetchone()
    return row[0] if row else None


def parse_dati_xlsx(ws):
    out = {
        "sede_label": None,
        "indirizzo": None,
        "piano": None,
        "citta_provincia": None,
        "datore_lavoro": None,
        "rspp": None,
        "descrizione_immobile": None,
    }
    label_to_field = {
        "indirizzo": "indirizzo",
        "piano": "piano",
        "città": "citta_provincia",
        "datore di lavoro": "datore_lavoro",
        "rspp": "rspp",
    }
    for r in range(15, 35):
        d = norm(ws.cell(r, 4).value)
        f = norm(ws.cell(r, 6).value)
        if d and d.lower() in label_to_field:
            out[label_to_field[d.lower()]] = f
        if d and d.lower().startswith("sede:"):
            out["sede_label"] = d
    a29 = norm(ws.cell(29, 1).value)
    if a29 and "DESCRIZIONE IMMOBILE" in a29.upper():
        out["descrizione_immobile"] = norm(ws.cell(30, 1).value)
    return out


def parse_pericoli_xlsx(ws):
    """Restituisce lista di dict {rif, col_c, col_d, col_e, col_f} per le righe compilate.

    Un punto può occupare PIÙ righe del foglio: rif/requisito/U/P stanno in celle
    unite, mentre "verifica e commento" (C) e "note" (F) hanno una riga di testo
    ciascuna. È il caso delle "combo uniche" richieste dal cliente (1.4.1, 1.5.6,
    1.5.7, 1.6.10, 1.9.2.1, 1.13.3.2): es. su 1.5.7 le tre righe sono apertura
    porta sede + porta condominio + vie di fuga, tutte parte della stessa nota.
    Le righe di continuazione (rif vuoto, testo in C o F) vengono quindi ACCUMULATE
    sul punto corrente e unite con a capo, come già avviene per i .docx dove lo
    stesso contenuto sta in una cella sola. Prima venivano scartate, perdendo
    l'informazione (~2.900 campi troncati sui DVR importati da Excel)."""
    rows = []
    corrente = None
    for r in range(2, ws.max_row + 1):
        rif = norm(ws.cell(r, 1).value)
        col_c = norm(ws.cell(r, 3).value)
        col_f = norm(ws.cell(r, 6).value)
        if rif and re.match(r"^\d+(\.\d+)*$", rif):
            corrente = {
                "rif": rif,
                "col_c": col_c,
                "col_d": norm(ws.cell(r, 4).value),
                "col_e": norm(ws.cell(r, 5).value),
                "col_f": col_f,
            }
            rows.append(corrente)
            continue
        # riga di continuazione del punto precedente
        if corrente and (col_c or col_f):
            for chiave, valore in (("col_c", col_c), ("col_f", col_f)):
                if not valore:
                    continue
                corrente[chiave] = f"{corrente[chiave]}\n{valore}" if corrente[chiave] else valore
    return rows


def coerce_int_p(v):
    """P deve essere 0..4. Valori fuori range → None (track come anomalia)."""
    if not isinstance(v, int):
        return None
    return v if 0 <= v <= 4 else None


def coerce_int_g(v):
    """G deve essere 1..4. Valori fuori range (incluso 0) → None."""
    if not isinstance(v, int):
        return None
    return v if 1 <= v <= 4 else None


def parse_valutazione_xlsx(ws):
    rows = []
    last = None
    for r in range(5, ws.max_row + 1):
        a = norm(ws.cell(r, 1).value)
        if a and re.fullmatch(r"\d+", a):
            continue
        b = norm(ws.cell(r, 2).value)
        c = norm(ws.cell(r, 3).value)
        d = norm(ws.cell(r, 4).value)
        e = ws.cell(r, 5).value
        f = ws.cell(r, 6).value
        h = norm(ws.cell(r, 8).value)
        if a:
            last = {
                "elemento": a,
                "figure_esposte": b,
                "rischi_descr": c,
                "misure_prevenzione": d,
                "p": coerce_int_p(e),
                "g": coerce_int_g(f),
                "misure_h": [h] if h else [],
            }
            rows.append(last)
        elif last is not None and h:
            last["misure_h"].append(h)
    return rows


def parse_incendio_xlsx(ws):
    """Estrae stato per ciascun documento. Modello: A=nome, B=stato OPPURE A=None+B=sub-tipo+C=stato per la dichiarazione di conformità.
    Inserisce SEMPRE il documento padre (anche solo come "contenitore vuoto") quando è presente almeno un sub-impianto, così l'UI può raggruppare correttamente."""
    rows = []
    parent_doc = None
    parent_inserted = False
    for r in range(11, 26):
        a = norm(ws.cell(r, 1).value)
        b = norm(ws.cell(r, 2).value)
        c = norm(ws.cell(r, 3).value)
        if a and a.startswith("Dichiarazione di conformità"):
            parent_doc = a
            parent_inserted = False
            # Inserisci il padre come riga "contenitore" per permettere il raggruppamento UI
            rows.append({"nome_documento": parent_doc, "parent": None, "stato": None, "note": None})
            parent_inserted = True
            if b:
                rows.append({"nome_documento": b, "parent": parent_doc, "stato": c or None, "note": None})
        elif a:
            rows.append({"nome_documento": a, "parent": None, "stato": b or None, "note": None})
        elif b and parent_doc:
            rows.append({"nome_documento": b, "parent": parent_doc, "stato": c or None, "note": None})
    return rows


def _ynbool(v) -> int | None:
    """Normalizza testo Sì/No → 1/0/None."""
    if v is None:
        return None
    s = str(v).strip().upper()
    if s in ("SI", "SÌ", "S", "YES", "Y", "1", "TRUE"):
        return 1
    if s in ("NO", "N", "0", "FALSE"):
        return 0
    return None


def parse_rischi_xlsx(ws):
    """Foglio Rischi del DVR singola sede.

    Layout:
      A1: 'TIPOLOGIA DI RISCHIO'
      A2: 'RISCHIO SISMICO'           B2: descr (es. 'Zona 4')
      A3: 'RISCHIO IDROGEOLOGICO'     B3: 'SI/NO' (template) o valore
      A4-A5: vuoti                    B4-B5: 'Rischio Frane', 'Rischio Alluvioni' + eventuale grado
      A6: 'RISCHIO AMBIENTALE'        B6: 'SI/NO' (template) o valore
      A7-A11: vuoti                  B7-B11: lista sub-rischi (Benzinai, Industriali, …)
      A13: 'PIANO DI GESTIONE EMERGENZE:'
      A14: testo lungo del piano emergenze
      A28: 'PIANO DI MIGLIORAMENTO'
      A33+: tabella attività × responsabile × date avvio/conclusione
    """
    out = {
        "rischio_sismico_descr": None,
        "rischio_idro_si_no": None,
        "rischio_idro_descr": None,
        "rischio_ambientale_si_no": None,
        "rischio_ambientale_descr": None,
        "piano_emergenze_testo": None,
        "azioni_piano": [],
    }

    # Sismico (riga 2)
    out["rischio_sismico_descr"] = norm(ws.cell(2, 2).value)

    # Idrogeologico: riga 3 ha titolo, righe 3-5 contengono dettagli in col B
    idro_b = [norm(ws.cell(r, 2).value) for r in (3, 4, 5)]
    idro_b_clean = [x for x in idro_b if x and x.upper() != "SI/NO"]
    if idro_b_clean:
        out["rischio_idro_descr"] = "\n".join(idro_b_clean)
        out["rischio_idro_si_no"] = 1
    else:
        # Se la prima cella è "SI"/"NO" letterale, rispettiamo, altrimenti None
        out["rischio_idro_si_no"] = _ynbool(idro_b[0])

    # Ambientale: riga 6 titolo, righe 6-11 dettagli
    amb_b = [norm(ws.cell(r, 2).value) for r in range(6, 12)]
    amb_b_clean = [x for x in amb_b if x and x.upper() != "SI/NO"]
    if amb_b_clean:
        out["rischio_ambientale_descr"] = "\n".join(amb_b_clean)
        out["rischio_ambientale_si_no"] = 1
    else:
        out["rischio_ambientale_si_no"] = _ynbool(amb_b[0])

    # Piano emergenze (riga 13 = header, riga 14+ = testo)
    for r in range(13, 28):
        v = norm(ws.cell(r, 1).value)
        if v and len(v) > 50:
            out["piano_emergenze_testo"] = v
            break

    # Piano miglioramento: tabella da riga 33
    last_az = None
    for r in range(33, ws.max_row + 1):
        a = norm(ws.cell(r, 1).value)
        b = norm(ws.cell(r, 2).value)
        c_val = ws.cell(r, 3).value
        if a:
            last_az = {"attivita": a, "responsabile": b, "data_avvio": None, "data_conclusione": None}
            if isinstance(c_val, datetime):
                last_az["data_avvio"] = c_val.date().isoformat()
            elif isinstance(c_val, str):
                last_az["data_avvio"] = c_val
            out["azioni_piano"].append(last_az)
        elif last_az is not None:
            if isinstance(c_val, datetime):
                last_az["data_conclusione"] = c_val.date().isoformat()
            elif isinstance(c_val, str):
                last_az["data_conclusione"] = c_val
    return out


def parse_incendio_intro_xlsx(ws) -> str | None:
    """Foglio Incendio: righe 2-9 contengono il testo introduttivo del rischio
    incendio (la riga 1 è solo l'intestazione "RISCHIO INCENDIO:" già usata
    come label nell'UI; l'elenco documentazione inizia a riga 10).
    """
    parts: list[str] = []
    for r in range(2, 10):
        v = norm(ws.cell(r, 1).value)
        if v:
            parts.append(v)
    if not parts:
        return None
    return "\n".join(parts).strip() or None


def parse_conclusione_xlsx(ws) -> str | None:
    """Foglio Conclusione: cerca testo conclusivo (di solito vuoto nei DVR
    esistenti, contiene solo allegati). Restituisce testo cumulativo se presente.
    """
    parts: list[str] = []
    for r in range(1, ws.max_row + 1):
        a = norm(ws.cell(r, 1).value)
        b = norm(ws.cell(r, 2).value)
        # salta righe ALL.1 / ALL.2 (sono header allegati)
        if a and a.startswith("ALL"):
            continue
        if a and len(a) > 80:
            parts.append(a)
        elif b and len(b) > 80 and not (a or "").startswith("ALL"):
            parts.append(b)
    if not parts:
        return None
    return "\n\n".join(parts)


def cell_text(cell) -> str | None:
    return norm(cell.text) if cell is not None else None


def parse_dati_docx(table):
    out = {"sede_label": None, "indirizzo": None, "piano": None, "citta_provincia": None,
           "datore_lavoro": None, "rspp": None, "descrizione_immobile": None}
    label_to_field = {"indirizzo": "indirizzo", "piano": "piano", "città": "citta_provincia",
                      "datore di lavoro": "datore_lavoro", "rspp": "rspp"}
    for row in table.rows:
        cells = [cell_text(c) for c in row.cells]
        if cells and cells[0] and cells[0].lower().startswith("sede:"):
            out["sede_label"] = cells[0]
            continue
        if len(cells) >= 3 and cells[1] and cells[1].lower() in label_to_field:
            out[label_to_field[cells[1].lower()]] = cells[2]
    return out


def parse_pericoli_docx(table):
    """Solo righe con rif valido. Niente concatenazione multi-row (era la fonte
    dei testi 'concatenati' che inquinavano lookup_pericoli_combo)."""
    rows = []
    for row in table.rows[1:]:
        cells = [cell_text(c) for c in row.cells]
        cells = (cells + [None] * 6)[:6]
        rif, _req, col_c, col_d, col_e, col_f = cells
        if not rif:
            continue
        rif = rif.rstrip(".").strip()  # FIX: i docx scrivono "1.1.2." col punto finale
        if not re.match(r"^\d+(\.\d+)*$", rif):
            continue
        rows.append({"rif": rif, "col_c": col_c, "col_d": col_d, "col_e": col_e, "col_f": col_f})
    return rows


def parse_valutazione_docx(table):
    rows = []
    last = None
    headers_seen = False
    for row in table.rows:
        cells = [cell_text(c) for c in row.cells]
        cells = (cells + [None] * 10)[:10]
        a = cells[0]
        if not headers_seen:
            if a and "Elementi" in (a or ""):
                headers_seen = True
            continue
        if a and re.fullmatch(r"\d+", a):
            continue
        b, c, d = cells[1], cells[2], cells[3]
        e_raw, f_raw = cells[4], cells[5]
        h = next((cells[i] for i in range(7, 10) if cells[i]), None)

        def to_int(v):
            if v is None:
                return None
            try:
                return int(str(v).strip())
            except (ValueError, AttributeError):
                return None

        if a:
            last = {"elemento": a, "figure_esposte": b, "rischi_descr": c,
                    "misure_prevenzione": d,
                    "p": coerce_int_p(to_int(e_raw)),
                    "g": coerce_int_g(to_int(f_raw)),
                    "misure_h": [h] if h else []}
            rows.append(last)
        elif last is not None and h:
            last["misure_h"].append(h)
    return rows


def parse_incendio_docx(table):
    rows = []
    parent_doc = None
    for row in table.rows[1:]:
        cells = [cell_text(c) for c in row.cells]
        cells = (cells + [None] * 3)[:3]
        a, b, c = cells
        if a and a.startswith("Dichiarazione di conformità"):
            parent_doc = a
            if b:
                rows.append({"nome_documento": b, "parent": parent_doc, "stato": c, "note": None})
        elif a:
            rows.append({"nome_documento": a, "parent": None, "stato": b, "note": None})
        elif b and parent_doc:
            rows.append({"nome_documento": b, "parent": parent_doc, "stato": c, "note": None})
    return rows


def parse_piano_docx(table):
    azioni = []
    last = None
    for row in table.rows[1:]:
        cells = [cell_text(c) for c in row.cells]
        cells = (cells + [None] * 3)[:3]
        a, b, c = cells
        if a:
            last = {"attivita": a, "responsabile": b, "data_avvio": None, "data_conclusione": None}
            azioni.append(last)
            if c:
                # Date possono essere su una sola cella separate da \n
                parts = [p.strip() for p in c.split("\n") if p.strip()]
                if parts:
                    last["data_avvio"] = parts[0]
                if len(parts) > 1:
                    last["data_conclusione"] = parts[1]
        elif last is not None and c:
            if not last["data_conclusione"]:
                last["data_conclusione"] = c
    return azioni


def identify_docx_tables(doc):
    """Mappa tabelle docx a sezioni DVR. Ritorna dict con tabelle pericoli, valutazione, incendio, piano, dati."""
    tables = doc.tables
    mapping = {"dati": None, "pericoli": None, "valutazione": None,
               "rischi": None, "incendio": None, "piano": None}
    for i, t in enumerate(tables):
        if not t.rows:
            continue
        first = (t.rows[0].cells[0].text or "").strip()
        first_upper = first.upper()
        rows_n = len(t.rows)
        cols_n = len(t.columns)
        if first.startswith("SEDE:"):
            mapping["dati"] = t
        elif rows_n > 60 and cols_n == 6 and first == "1":
            mapping["pericoli"] = t
        elif "Elementi" in (t.rows[1].cells[0].text if rows_n > 1 else "") and cols_n >= 8:
            mapping["valutazione"] = t
        elif first_upper.startswith("ELENCO DOCUMENTAZIONE"):
            mapping["incendio"] = t
        elif first_upper.startswith("ATTIVITA"):
            mapping["piano"] = t
        elif first_upper.startswith("TIPOLOGIA DI"):
            mapping["rischi"] = t
    return mapping


def import_xlsx(conn: sqlite3.Connection, path: Path) -> dict:
    cur = conn.cursor()
    fn = path.name
    info = parse_filename(fn)
    if not info:
        cur.execute(
            "INSERT INTO import_log(filename, stato, note) VALUES (?, 'filename_malformato', ?)",
            (fn, "Pattern filename non riconosciuto"),
        )
        return {"file": fn, "stato": "filename_malformato"}

    ag = lookup_override(cur, fn) or find_agenzia(cur, info["nome_agenzia"], info["sede"])
    if not ag:
        cur.execute(
            "INSERT INTO import_log(filename, stato, note) VALUES (?, 'no_match', ?)",
            (fn, f'agenzia="{info["nome_agenzia"]}" sede="{info["sede"]}"'),
        )
        return {"file": fn, "stato": "no_match", "agenzia": info["nome_agenzia"], "sede": info["sede"]}
    agenzia_id = ag[0]
    wb = load_workbook(path, data_only=True)

    def get_sheet(*names):
        for n in names:
            for sn in wb.sheetnames:
                if sn.strip().lower() == n.lower():
                    return wb[sn]
        return None

    dati = parse_dati_xlsx(get_sheet("Dati")) if get_sheet("Dati") else {}
    pericoli = parse_pericoli_xlsx(get_sheet("Pericoli")) if get_sheet("Pericoli") else []
    valutazione = parse_valutazione_xlsx(get_sheet("Valutazione")) if get_sheet("Valutazione") else []
    inc_ws = get_sheet("Incendio", "incendio dati generali")
    incendio = parse_incendio_xlsx(inc_ws) if inc_ws else []
    rischio_incendio_testo = parse_incendio_intro_xlsx(inc_ws) if inc_ws else None
    rischi = parse_rischi_xlsx(get_sheet("Rischi")) if get_sheet("Rischi") else {
        "rischio_sismico_descr": None,
        "rischio_idro_si_no": None,
        "rischio_idro_descr": None,
        "rischio_ambientale_si_no": None,
        "rischio_ambientale_descr": None,
        "piano_emergenze_testo": None,
        "azioni_piano": [],
    }
    concl_ws = get_sheet("Conclusione")
    conclusione_testo = parse_conclusione_xlsx(concl_ws) if concl_ws else None

    if dati.get("piano"):
        cur.execute("UPDATE agenzie SET piano = COALESCE(piano, ?) WHERE id = ?", (dati["piano"], agenzia_id))
    if dati.get("datore_lavoro"):
        cur.execute("UPDATE agenzie SET datore_lavoro = COALESCE(datore_lavoro, ?) WHERE id = ?", (dati["datore_lavoro"], agenzia_id))
    if dati.get("rspp"):
        cur.execute("UPDATE agenzie SET rspp = COALESCE(rspp, ?) WHERE id = ?", (dati["rspp"], agenzia_id))

    cur.execute("DELETE FROM dvr WHERE agenzia_id = ?", (agenzia_id,))
    cur.execute(
        """INSERT INTO dvr(agenzia_id, rev_corrente, data_emissione,
                            descrizione_immobile, rischio_sismico_descr,
                            rischio_idro_si_no, rischio_idro_descr,
                            rischio_ambientale_si_no, rischio_ambientale_descr,
                            piano_emergenze_testo, rischio_incendio_testo,
                            conclusione_testo,
                            source_file, source_format, imported_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'xlsx', CURRENT_TIMESTAMP)""",
        (
            agenzia_id, info["rev"], info["data"],
            dati.get("descrizione_immobile"),
            rischi.get("rischio_sismico_descr"),
            rischi.get("rischio_idro_si_no"),
            rischi.get("rischio_idro_descr"),
            rischi.get("rischio_ambientale_si_no"),
            rischi.get("rischio_ambientale_descr"),
            rischi.get("piano_emergenze_testo"),
            rischio_incendio_testo,
            conclusione_testo,
            fn,
        ),
    )
    dvr_id = cur.lastrowid

    punto_idmap = {row[0]: row[1] for row in cur.execute("SELECT rif, id FROM lookup_pericoli_punti")}

    n_per = 0
    n_per_skip = 0
    for p in pericoli:
        rif = p["rif"]
        punto_id = punto_idmap.get(rif)
        if punto_id is None or len(rif.split(".")) < 3:
            n_per_skip += 1
            continue
        col_c_lookup = get_or_create_pericolo_combo(cur, punto_id, "C", p["col_c"]) if p["col_c"] else None
        col_f_lookup = get_or_create_pericolo_combo(cur, punto_id, "F", p["col_f"]) if p["col_f"] else None
        col_c_override = norm(p["col_c"]) if p["col_c"] and not col_c_lookup else None
        col_f_override = norm(p["col_f"]) if p["col_f"] and not col_f_lookup else None
        cur.execute(
            """INSERT INTO dvr_pericoli(dvr_id, punto_id, col_c_lookup_id, col_c_override, col_d, col_e, col_f_lookup_id, col_f_override)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)
               ON CONFLICT(dvr_id, punto_id) DO NOTHING""",
            (dvr_id, punto_id, col_c_lookup, col_c_override, p["col_d"], p["col_e"], col_f_lookup, col_f_override),
        )
        if cur.rowcount > 0:
            n_per += 1
        else:
            n_per_skip += 1

    elem_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_valutazione_elementi")}
    n_val = 0
    n_misure_h = 0
    for v in valutazione:
        elem_id = elem_idmap.get(v["elemento"])
        if not elem_id:
            continue
        prev_lookup = get_or_create_valutazione_misura(cur, elem_id, "D", v["misure_prevenzione"]) if v.get("misure_prevenzione") else None
        prev_override = norm(v["misure_prevenzione"]) if v.get("misure_prevenzione") and not prev_lookup else None
        cur.execute(
            """INSERT INTO dvr_valutazione(dvr_id, elemento_id, figure_esposte, rischi_descr,
                                            misure_prevenzione_lookup_id, misure_prevenzione_override, p, g)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (dvr_id, elem_id, v["figure_esposte"], v["rischi_descr"],
             prev_lookup, prev_override, v["p"], v["g"]),
        )
        val_id = cur.lastrowid
        n_val += 1
        for h_text in v["misure_h"]:
            h_lookup = get_or_create_valutazione_misura(cur, elem_id, "H", h_text)
            h_override = norm(h_text) if not h_lookup else None
            cur.execute(
                "INSERT INTO dvr_valutazione_misure(valutazione_id, lookup_id, testo_override) VALUES (?, ?, ?)",
                (val_id, h_lookup, h_override),
            )
            n_misure_h += 1

    doc_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_documenti_incendio")}
    stato_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_stati_documento_incendio")}
    n_inc = 0
    for d in incendio:
        doc_id = doc_idmap.get(d["nome_documento"])
        if not doc_id:
            continue
        stato_lookup_id = stato_idmap.get(d["stato"]) if d["stato"] else None
        stato_override = d["stato"] if d["stato"] and not stato_lookup_id else None
        cur.execute(
            """INSERT INTO dvr_incendio_doc(dvr_id, documento_id, stato_lookup_id, stato_override, note)
               VALUES (?, ?, ?, ?, ?)""",
            (dvr_id, doc_id, stato_lookup_id, stato_override, d.get("note")),
        )
        n_inc += 1

    az_idmap = {row[0]: row[1] for row in cur.execute("SELECT attivita, id FROM lookup_azioni_piano_miglioramento")}
    n_az = 0
    for i, a in enumerate(rischi["azioni_piano"], start=1):
        az_lookup_id = az_idmap.get(a["attivita"])
        cur.execute(
            """INSERT INTO dvr_piano_miglioramento(dvr_id, attivita_lookup_id, attivita_override,
                                                    responsabile, data_avvio, data_conclusione, ordine)
               VALUES (?, ?, ?, ?, ?, ?, ?)""",
            (dvr_id, az_lookup_id, None if az_lookup_id else a["attivita"],
             a["responsabile"] or "Operation di rete - Alleanza Assicurazioni",
             a["data_avvio"], a["data_conclusione"], i),
        )
        n_az += 1

    cur.execute(
        "INSERT INTO import_log(filename, agenzia_id, stato, note) VALUES (?, ?, 'ok', ?)",
        (fn, agenzia_id,
         f"per={n_per}(skip {n_per_skip}) val={n_val} misure_H={n_misure_h} inc={n_inc} azioni={n_az}"),
    )

    return {
        "file": fn, "stato": "ok",
        "agenzia_id": agenzia_id, "codice_fisico": ag[1], "tipologia": ag[2],
        "nome_agenzia": ag[3], "nome_sede": ag[4],
        "dvr_id": dvr_id, "rev": info["rev"], "data": info["data"],
        "pericoli": n_per, "pericoli_skip": n_per_skip,
        "valutazione": n_val, "misure_H": n_misure_h,
        "incendio": n_inc, "azioni_piano": n_az,
    }


def import_docx(conn: sqlite3.Connection, path: Path) -> dict:
    cur = conn.cursor()
    fn = path.name
    info = parse_filename(fn)
    if not info:
        cur.execute(
            "INSERT INTO import_log(filename, stato, note) VALUES (?, 'filename_malformato', ?)",
            (fn, "Pattern filename non riconosciuto"),
        )
        return {"file": fn, "stato": "filename_malformato"}

    ag = lookup_override(cur, fn) or find_agenzia(cur, info["nome_agenzia"], info["sede"])
    if not ag:
        cur.execute(
            "INSERT INTO import_log(filename, stato, note) VALUES (?, 'no_match', ?)",
            (fn, f'agenzia="{info["nome_agenzia"]}" sede="{info["sede"]}"'),
        )
        return {"file": fn, "stato": "no_match", "agenzia": info["nome_agenzia"], "sede": info["sede"]}

    agenzia_id = ag[0]
    doc = Document(path)
    tabs = identify_docx_tables(doc)

    dati = parse_dati_docx(tabs["dati"]) if tabs["dati"] else {}
    pericoli = parse_pericoli_docx(tabs["pericoli"]) if tabs["pericoli"] else []
    valutazione = parse_valutazione_docx(tabs["valutazione"]) if tabs["valutazione"] else []
    incendio = parse_incendio_docx(tabs["incendio"]) if tabs["incendio"] else []
    azioni_piano = parse_piano_docx(tabs["piano"]) if tabs["piano"] else []

    if dati.get("piano"):
        cur.execute("UPDATE agenzie SET piano = COALESCE(piano, ?) WHERE id = ?", (dati["piano"], agenzia_id))
    if dati.get("datore_lavoro"):
        cur.execute("UPDATE agenzie SET datore_lavoro = COALESCE(datore_lavoro, ?) WHERE id = ?", (dati["datore_lavoro"], agenzia_id))
    if dati.get("rspp"):
        cur.execute("UPDATE agenzie SET rspp = COALESCE(rspp, ?) WHERE id = ?", (dati["rspp"], agenzia_id))

    cur.execute("DELETE FROM dvr WHERE agenzia_id = ?", (agenzia_id,))
    cur.execute(
        """INSERT INTO dvr(agenzia_id, rev_corrente, data_emissione, descrizione_immobile,
                            source_file, source_format, imported_at)
           VALUES (?, ?, ?, ?, ?, 'docx', CURRENT_TIMESTAMP)""",
        (agenzia_id, info["rev"], info["data"], dati.get("descrizione_immobile"), fn),
    )
    dvr_id = cur.lastrowid

    punto_idmap = {row[0]: row[1] for row in cur.execute("SELECT rif, id FROM lookup_pericoli_punti")}
    n_per = n_per_skip = 0
    for p in pericoli:
        rif = p["rif"]
        punto_id = punto_idmap.get(rif)
        if punto_id is None or len(rif.split(".")) < 3:
            n_per_skip += 1
            continue
        col_c_lookup = get_or_create_pericolo_combo(cur, punto_id, "C", p["col_c"]) if p["col_c"] else None
        col_f_lookup = get_or_create_pericolo_combo(cur, punto_id, "F", p["col_f"]) if p["col_f"] else None
        col_c_override = norm(p["col_c"]) if p["col_c"] and not col_c_lookup else None
        col_f_override = norm(p["col_f"]) if p["col_f"] and not col_f_lookup else None
        cur.execute(
            """INSERT INTO dvr_pericoli(dvr_id, punto_id, col_c_lookup_id, col_c_override, col_d, col_e, col_f_lookup_id, col_f_override)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)
               ON CONFLICT(dvr_id, punto_id) DO NOTHING""",
            (dvr_id, punto_id, col_c_lookup, col_c_override, p["col_d"], p["col_e"], col_f_lookup, col_f_override),
        )
        if cur.rowcount > 0:
            n_per += 1
        else:
            n_per_skip += 1

    elem_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_valutazione_elementi")}
    n_val = n_misure_h = 0
    for v in valutazione:
        elem_id = elem_idmap.get(v["elemento"])
        if not elem_id:
            continue
        prev_lookup = get_or_create_valutazione_misura(cur, elem_id, "D", v["misure_prevenzione"]) if v.get("misure_prevenzione") else None
        prev_override = norm(v["misure_prevenzione"]) if v.get("misure_prevenzione") and not prev_lookup else None
        cur.execute(
            """INSERT INTO dvr_valutazione(dvr_id, elemento_id, figure_esposte, rischi_descr,
                                            misure_prevenzione_lookup_id, misure_prevenzione_override, p, g)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (dvr_id, elem_id, v["figure_esposte"], v["rischi_descr"],
             prev_lookup, prev_override, v["p"], v["g"]),
        )
        val_id = cur.lastrowid
        n_val += 1
        for h_text in v["misure_h"]:
            h_lookup = get_or_create_valutazione_misura(cur, elem_id, "H", h_text)
            h_override = norm(h_text) if not h_lookup else None
            cur.execute(
                "INSERT INTO dvr_valutazione_misure(valutazione_id, lookup_id, testo_override) VALUES (?, ?, ?)",
                (val_id, h_lookup, h_override),
            )
            n_misure_h += 1

    doc_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_documenti_incendio")}
    stato_idmap = {row[0]: row[1] for row in cur.execute("SELECT nome, id FROM lookup_stati_documento_incendio")}
    n_inc = 0
    for d in incendio:
        doc_id = doc_idmap.get(d["nome_documento"])
        if not doc_id:
            continue
        stato_lookup_id = stato_idmap.get(d["stato"]) if d["stato"] else None
        stato_override = d["stato"] if d["stato"] and not stato_lookup_id else None
        cur.execute(
            """INSERT INTO dvr_incendio_doc(dvr_id, documento_id, stato_lookup_id, stato_override, note)
               VALUES (?, ?, ?, ?, ?)""",
            (dvr_id, doc_id, stato_lookup_id, stato_override, d.get("note")),
        )
        n_inc += 1

    az_idmap = {row[0]: row[1] for row in cur.execute("SELECT attivita, id FROM lookup_azioni_piano_miglioramento")}
    n_az = 0
    for i, a in enumerate(azioni_piano, start=1):
        az_lookup_id = az_idmap.get(a["attivita"])
        cur.execute(
            """INSERT INTO dvr_piano_miglioramento(dvr_id, attivita_lookup_id, attivita_override,
                                                    responsabile, data_avvio, data_conclusione, ordine)
               VALUES (?, ?, ?, ?, ?, ?, ?)""",
            (dvr_id, az_lookup_id, None if az_lookup_id else a["attivita"],
             a["responsabile"] or "Operation di rete - Alleanza Assicurazioni",
             a["data_avvio"], a["data_conclusione"], i),
        )
        n_az += 1

    cur.execute(
        "INSERT INTO import_log(filename, agenzia_id, stato, note) VALUES (?, ?, 'ok', ?)",
        (fn, agenzia_id,
         f"per={n_per}(skip {n_per_skip}) val={n_val} misure_H={n_misure_h} inc={n_inc} azioni={n_az}"),
    )

    return {
        "file": fn, "stato": "ok",
        "agenzia_id": agenzia_id, "codice_fisico": ag[1], "tipologia": ag[2],
        "nome_agenzia": ag[3], "nome_sede": ag[4],
        "dvr_id": dvr_id, "rev": info["rev"], "data": info["data"],
        "pericoli": n_per, "pericoli_skip": n_per_skip,
        "valutazione": n_val, "misure_H": n_misure_h,
        "incendio": n_inc, "azioni_piano": n_az,
    }


def import_one(conn: sqlite3.Connection, path: Path) -> dict:
    if path.suffix.lower() == ".xlsx":
        return import_xlsx(conn, path)
    if path.suffix.lower() == ".docx":
        return import_docx(conn, path)
    raise ValueError(f"Estensione non supportata: {path.suffix}")


def main():
    p = argparse.ArgumentParser()
    p.add_argument("file", nargs="?", help="path al file DVR (xlsx o docx)")
    p.add_argument("--all", action="store_true", help="importa tutti i file in -mat/DVR/")
    args = p.parse_args()

    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA foreign_keys = ON")

    if args.all:
        n_dvr = conn.execute("SELECT COUNT(*) FROM dvr").fetchone()[0]
        if n_dvr > 0:
            sys.exit(
                f"Il DB contiene già {n_dvr} DVR. Per un import pulito ricrea il DB:\n"
                "  python3 app/scripts/reset_db.py\n"
                "  python3 app/scripts/import_anagrafica.py\n"
                "  python3 app/scripts/import_seed_master.py\n"
                "  python3 app/scripts/import_dvr.py --all"
            )

        files = sorted([f for f in DVR_DIR.iterdir() if f.suffix.lower() in (".xlsx", ".docx")])
        print(f"Elaboro {len(files)} file...")
        results: dict[str, int] = {}
        for i, f in enumerate(files, 1):
            try:
                r = import_one(conn, f)
                results[r["stato"]] = results.get(r["stato"], 0) + 1
            except Exception as e:
                conn.execute(
                    "INSERT INTO import_log(filename, stato, note) VALUES (?, 'parse_error', ?)",
                    (f.name, f"{type(e).__name__}: {e}"),
                )
                results["parse_error"] = results.get("parse_error", 0) + 1
            if i % 50 == 0:
                conn.commit()
                print(f"  ... {i}/{len(files)}: {results}")
        conn.commit()
        print()
        print(f"FINITO. Risultati: {results}")
    else:
        if not args.file:
            sys.exit("Specificare il file o usare --all")
        r = import_one(conn, Path(args.file))
        conn.commit()
        for k, v in r.items():
            print(f"  {k}: {v}")
    conn.close()


if __name__ == "__main__":
    main()