#!/usr/bin/env python3
"""
Estrae le tabelle di lookup ("vocabolari") dal master TWISTER e dal foglio Sismica.
Sorgente: -mat/07_DVR AGENZIE_TWISTER.xlsx

Popola:
- lookup_pericoli_punti (~108 punti normativi)
- lookup_valutazione_elementi (~30 elementi)
- lookup_documenti_incendio (~14 voci con parent/child)
- lookup_stati_documento_incendio (4 valori canonici)
- lookup_zone_sismiche (13 valori)
- lookup_gradi_rischio_idro (6 valori comuni a frane e alluvioni)
- lookup_rischi_ambientali (categorie multi-valore per sede)
- lookup_imprese_sopralluogo (7 valori)
- lookup_categorie_protette (gruppi categorie protette)
- lookup_azioni_piano_miglioramento (~27 azioni)

Le combo Pericoli (ListaF1) e Valutazione misure (ListaF2) NON sono popolate qui:
verranno create on-the-fly durante l'import dei singoli file DVR.

Esegui da app/: python3 scripts/import_seed_master.py
"""
from __future__ import annotations
import re
import sqlite3
import sys
import warnings
from pathlib import Path

warnings.filterwarnings("ignore")
try:
    from openpyxl import load_workbook
except ImportError:
    sys.exit("openpyxl non installato. pip3 install --user openpyxl")

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


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


def main():
    wb = load_workbook(MASTER, data_only=True, read_only=False)
    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA foreign_keys = ON")
    cur = conn.cursor()

    # Pulisco le lookup (riprendibile)
    for t in [
        "lookup_pericoli_punti", "lookup_valutazione_elementi",
        "lookup_documenti_incendio", "lookup_stati_documento_incendio",
        "lookup_zone_sismiche", "lookup_gradi_rischio_idro",
        "lookup_rischi_ambientali", "lookup_imprese_sopralluogo",
        "lookup_categorie_protette", "lookup_azioni_piano_miglioramento",
    ]:
        cur.execute(f"DELETE FROM {t}")
        cur.execute("DELETE FROM sqlite_sequence WHERE name = ?", (t,))

    print(">>> PERICOLI PUNTI")
    ws = wb["Pericoli"]
    inserted = 0
    for r in range(2, ws.max_row + 1):
        rif = norm(ws.cell(r, 1).value)
        req = norm(ws.cell(r, 2).value)
        if not rif:
            continue
        if not re.match(r"^\d+(\.\d+)*$", rif):
            continue
        livello = len(rif.split("."))
        capitolo = ".".join(rif.split(".")[:2])  # "1.4.1" → "1.4"
        cur.execute(
            "INSERT INTO lookup_pericoli_punti(rif, capitolo, livello, requisito, ordine) "
            "VALUES (?, ?, ?, ?, ?)",
            (rif, capitolo, livello, req or "(requisito da definire)", inserted + 1),
        )
        inserted += 1
    print(f"    inseriti: {inserted}")

    print(">>> VALUTAZIONE ELEMENTI")
    ws = wb["Valutazione"]
    inserted = 0
    visti = set()
    for r in range(5, ws.max_row + 1):  # i dati partono da R5
        a = norm(ws.cell(r, 1).value)
        b = norm(ws.cell(r, 2).value)
        if not a:
            continue
        # Salto i raggruppamenti capitolo (A numerico, B titolo) come "1 / AMBIENTI DI LAVORO"
        if re.fullmatch(r"\d+", a):
            continue
        if a in visti:
            continue
        visti.add(a)
        cur.execute(
            "INSERT INTO lookup_valutazione_elementi(nome, ordine) VALUES (?, ?)",
            (a, inserted + 1),
        )
        inserted += 1
    print(f"    inseriti: {inserted}")

    print(">>> DOCUMENTI INCENDIO")
    ws = wb["Incendio"]
    # Modello del foglio:
    #   R11-25: ogni cella A è un documento. R18 ha figli in colonna B (R18 elettrici, R19 idrici, R20 termici,
    #   R21 aeraulici, R22 allarme, R23 insegne) — sono sotto-tipi della "Dichiarazione di conformità impianti".
    inserted = 0
    parent_id_dichiarazione = None
    ord_n = 0
    for r in range(11, 26):
        a = norm(ws.cell(r, 1).value)
        b = norm(ws.cell(r, 2).value)
        if a:
            ord_n += 1
            cur.execute(
                "INSERT INTO lookup_documenti_incendio(nome, ordine, parent_id) VALUES (?, ?, NULL)",
                (a, ord_n),
            )
            inserted += 1
            if a.startswith("Dichiarazione di conformità"):
                parent_id_dichiarazione = cur.lastrowid
                # questa riga ha anche il PRIMO sotto-tipo in colonna B (es. "elettrici")
                if b:
                    ord_n += 1
                    cur.execute(
                        "INSERT INTO lookup_documenti_incendio(nome, ordine, parent_id) VALUES (?, ?, ?)",
                        (b, ord_n, parent_id_dichiarazione),
                    )
                    inserted += 1
        elif b and parent_id_dichiarazione:
            ord_n += 1
            cur.execute(
                "INSERT INTO lookup_documenti_incendio(nome, ordine, parent_id) VALUES (?, ?, ?)",
                (b, ord_n, parent_id_dichiarazione),
            )
            inserted += 1
    print(f"    inseriti: {inserted}")

    print(">>> STATI DOCUMENTO INCENDIO")
    stati = ["Acquisito", "In corso di reperimento", "Impianto non presente", "Non Applicabile"]
    cur.executemany(
        "INSERT INTO lookup_stati_documento_incendio(nome, ordine) VALUES (?, ?)",
        [(s, i) for i, s in enumerate(stati, start=1)],
    )
    print(f"    inseriti: {len(stati)}")

    print(">>> ZONE SISMICHE")
    ws = wb["Lista F3"]
    zone = []
    for r in range(3, 16):
        v = norm(ws.cell(r, 1).value)
        if v and "zona" in v.lower():
            codice = v.lower().replace("entità sismica zona", "").strip().upper()
            zone.append(codice)
    # aggiungo "3S" osservato nel foglio Sismica del master
    if "3S" not in zone:
        zone.append("3S")
    cur.executemany(
        "INSERT INTO lookup_zone_sismiche(codice, descrizione, ordine) VALUES (?, ?, ?)",
        [(c, f"Zona sismica {c}", i) for i, c in enumerate(zone, start=1)],
    )
    print(f"    inseriti: {zone}")

    print(">>> GRADI RISCHIO IDRO (frane/alluvioni)")
    gradi = [
        ("NO", "Non presente", 1),
        ("AA", "Area di Attenzione", 2),
        ("P1", "Moderato", 3),
        ("P2", "Medio", 4),
        ("P3", "Elevato", 5),
        ("P4", "Molto Elevato", 6),
    ]
    cur.executemany(
        "INSERT INTO lookup_gradi_rischio_idro(codice, descrizione, ordine) VALUES (?, ?, ?)",
        gradi,
    )
    print(f"    inseriti: {len(gradi)}")

    print(">>> RISCHI AMBIENTALI")
    rischi_amb = [
        ("BENZINAIO", 1),
        ("FERROVIA", 2),
        ("ATTIVITA INDUSTRIALI", 3),
        ("PORTO", 4),
        ("CENTRALE IDROELETTRICA", 5),
        ("FARMACEUTICA", 6),
        ("ALTRO", 99),
    ]
    cur.executemany(
        "INSERT INTO lookup_rischi_ambientali(nome, ordine) VALUES (?, ?)",
        rischi_amb,
    )
    print(f"    inseriti: {len(rischi_amb)}")

    print(">>> IMPRESE SOPRALLUOGO")
    imprese = ["TWISTER", "MASTER IMPIANTI", "FRACARO", "CARLIN", "VIGORITO", "GENEROSO", "COIS"]
    cur.executemany(
        "INSERT INTO lookup_imprese_sopralluogo(nome, ordine) VALUES (?, ?)",
        [(n, i) for i, n in enumerate(imprese, start=1)],
    )
    print(f"    inseriti: {len(imprese)}")

    print(">>> CATEGORIE PROTETTE (gruppi)")
    ws = wb["Categorie Protette"]
    distinti = set()
    for r in range(2, ws.max_row + 1):
        v = norm(ws.cell(r, 1).value)
        if v:
            # normalizzo case ("Inv. civile" / "Inv. Civile")
            distinti.add(v.lower())
    canonici = sorted({d.title() for d in distinti})
    cur.executemany(
        "INSERT INTO lookup_categorie_protette(nome, ordine) VALUES (?, ?)",
        [(n, i) for i, n in enumerate(canonici, start=1)],
    )
    print(f"    inseriti: {canonici}")

    print(">>> AZIONI PIANO MIGLIORAMENTO")
    ws = wb["Rischi"]
    inserted = 0
    for r in range(33, ws.max_row + 1):
        a = norm(ws.cell(r, 1).value)
        if not a:
            continue
        cur.execute(
            "INSERT INTO lookup_azioni_piano_miglioramento(attivita, ordine) VALUES (?, ?)",
            (a, inserted + 1),
        )
        inserted += 1
    print(f"    inseriti: {inserted}")

    conn.commit()

    print()
    print("=" * 60)
    print("Conta finali per tabella:")
    for t in [
        "lookup_pericoli_punti", "lookup_valutazione_elementi",
        "lookup_documenti_incendio", "lookup_stati_documento_incendio",
        "lookup_zone_sismiche", "lookup_gradi_rischio_idro",
        "lookup_rischi_ambientali", "lookup_imprese_sopralluogo",
        "lookup_categorie_protette", "lookup_azioni_piano_miglioramento",
    ]:
        n = cur.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
        print(f"  {t:<40} {n}")
    conn.close()


if __name__ == "__main__":
    main()