#!/usr/bin/env python3
"""Importa i codici trigger del master TWISTER v2:
- Foglio Rischi (col A=attività, D=codice) → lookup_azioni_piano_miglioramento + lookup_attivita_famiglie
- Foglio Pericoli (col A=rif, G=codice) → aggiorna lookup_pericoli_punti.codice_attivita
- Foglio Valutazione (col H=testo, I=codice) → aggiorna lookup_valutazione_misure.codice_attivita
- Estrae testo_pulito + r_score dalle parentesi (1-2-3) in lookup_valutazione_misure
"""
import re
import sqlite3
import sys
from pathlib import Path
import openpyxl

V2 = Path(__file__).resolve().parents[2] / "-mat" / "07_DVR AGENZIE_TWISTER_v2.xlsx"
DB = Path(__file__).resolve().parents[1] / "db" / "dev.db"

# Nomi generici delle famiglie. Codici da master Rischi.D.
# Per le famiglie multi-variante (codici concatenati o ripetuti) uso un nome generico
# che copre tutte le varianti.
FAMIGLIE_NOMI = {
    "A": "Reperimento documentazione fabbricato",
    "B": "Riposizionamento degli arredi",
    "C": "Apertura meccanica delle porte di accesso",
    "D": "Messa in sicurezza del vetro di porte e serramenti",
    "E": "Segnalazione visiva di sicurezza (vetri, gradini, dislivelli)",
    "F": "Strisce antiscivolo sulle scale",
    "G": "Verifica presenza Legionella",
    "H": "Adeguamento dell'impianto di illuminazione",
    "I": "Riposizionamento delle stampanti",
    "L": "Sostituzione arredo non conforme",
    "M": "Installazione rilevatore di gas in prossimità della caldaia",
    "N": "Verifica Gas Radon e pertinenze interrate",
    "O": "Installazione nottolini nelle porte dei bagni",
    "P": "Installazione segnaletica di emergenza",
    "Q": "Installazione illuminazione di emergenza",
    "R": "Sistemazione cablaggi in prossimità delle postazioni di lavoro",
    "S": "Adeguamento dei parapetti di altezza non regolamentare",
    "T": "Installazione tende sui serramenti",
    "U": "Segnaletica per divisione percorso pedonale e veicolare",
    "V": "Installazione impianto di aerazione meccanizzata",
    "Z": "Verifica e sistemazione impianto di riscaldamento/raffrescamento",
}


def norm(v):
    if v is None:
        return None
    s = str(v).strip()
    return s if s else None


def famiglia_di(codice_variante: str) -> str:
    """Da 'D1', 'C2', 'P1-P2', 'A' → 'D', 'C', 'P', 'A'."""
    # Composto 'X-Y' → prima parte
    head = codice_variante.split("-")[0]
    # Toglie cifre finali → 'D1' → 'D', 'P12' → 'P'
    m = re.match(r"^([A-Z]+)", head)
    return m.group(1) if m else head


# Estrae (p, g, r) dal testo se contiene "(a-b-c)" come ultima parentesi.
# Restituisce (testo_senza_parentesi, p, g, r) oppure (testo, None, None, None).
RX_PGR = re.compile(r"\(\s*(\d+)\s*-\s*(\d+)\s*-\s*(\d+)\s*\)\s*$")


def estrai_pgr(testo: str):
    if not testo:
        return testo, None, None, None
    m = RX_PGR.search(testo)
    if not m:
        return testo, None, None, None
    p, g, r = int(m.group(1)), int(m.group(2)), int(m.group(3))
    pulito = testo[: m.start()].rstrip(" \n\t-_·.,;")
    return pulito, p, g, r


def main():
    if not V2.exists():
        print(f"FATAL: file v2 non trovato in {V2}", file=sys.stderr)
        sys.exit(2)

    print(f"Apertura master v2: {V2}")
    wb = openpyxl.load_workbook(V2, data_only=True)

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

    # =================================================================
    # 1. Foglio Rischi → famiglie + varianti
    # =================================================================
    ws = wb["Rischi"]
    print("\n[1] Foglio Rischi → famiglie + varianti")

    # Famiglie: dall'union di tutti i codici presenti in Rischi.D
    codici_trovati = set()
    for r in range(33, ws.max_row + 1):
        cod = norm(ws.cell(r, 4).value)
        if cod:
            codici_trovati.add(cod)

    famiglie_codici = sorted({famiglia_di(c) for c in codici_trovati})
    fam_id_map: dict[str, int] = {}
    # UPSERT manuale per non rompere FK eventuali
    for i, fcod in enumerate(famiglie_codici, start=1):
        nome = FAMIGLIE_NOMI.get(fcod, f"(codice {fcod})")
        cur.execute("SELECT id FROM lookup_attivita_famiglie WHERE codice = ?", (fcod,))
        row = cur.fetchone()
        if row:
            cur.execute(
                "UPDATE lookup_attivita_famiglie SET nome_generico = ?, ordine = ? WHERE id = ?",
                (nome, i, row[0]),
            )
            fam_id_map[fcod] = row[0]
        else:
            cur.execute(
                "INSERT INTO lookup_attivita_famiglie(codice, nome_generico, ordine) VALUES (?, ?, ?)",
                (fcod, nome, i),
            )
            fam_id_map[fcod] = cur.lastrowid
    print(f"  Famiglie: {len(fam_id_map)} → {', '.join(famiglie_codici)}")

    # Varianti: per ogni riga (attivita+codice) del v2, UPDATE se esiste già nel lookup,
    # altrimenti INSERT. Match basato sul testo dell'attività (trim case-sensitive).
    cur.execute("SELECT id, attivita FROM lookup_azioni_piano_miglioramento")
    varianti_esistenti = {(t or "").strip(): mid for mid, t in cur.fetchall()}

    varianti_upd = 0
    varianti_ins = 0
    ordine = 0
    for r in range(33, ws.max_row + 1):
        attivita = norm(ws.cell(r, 1).value)
        cod = norm(ws.cell(r, 4).value)
        if not attivita or not cod:
            continue
        ordine += 1
        fam_cod = famiglia_di(cod)
        fam_id = fam_id_map.get(fam_cod)
        existing_id = varianti_esistenti.get(attivita.strip())
        if existing_id:
            cur.execute(
                """UPDATE lookup_azioni_piano_miglioramento
                   SET codice_variante = ?, famiglia_id = ?, ordine = ?
                   WHERE id = ?""",
                (cod, fam_id, ordine, existing_id),
            )
            varianti_upd += 1
        else:
            cur.execute(
                """INSERT INTO lookup_azioni_piano_miglioramento
                   (attivita, codice_variante, famiglia_id, ordine)
                   VALUES (?, ?, ?, ?)""",
                (attivita, cod, fam_id, ordine),
            )
            varianti_ins += 1
    print(f"  Varianti aggiornate: {varianti_upd} | nuove: {varianti_ins}")

    # =================================================================
    # 2. Foglio Pericoli → aggiorna lookup_pericoli_punti.codice_attivita
    # =================================================================
    ws = wb["Pericoli"]
    print("\n[2] Foglio Pericoli → codice attività per punto")

    cur.execute("UPDATE lookup_pericoli_punti SET codice_attivita = NULL")

    pericoli_aggiornati = 0
    for r in range(2, ws.max_row + 1):
        rif = norm(ws.cell(r, 1).value)
        cod = norm(ws.cell(r, 7).value)
        if not rif or not cod:
            continue
        # Solo punti foglia (es. 1.1.1), il foglio ha anche capitoli "1" e sotto-capitoli "1.1"
        if not re.match(r"^\d+(\.\d+)*$", rif):
            continue
        cur.execute(
            "UPDATE lookup_pericoli_punti SET codice_attivita = ? WHERE rif = ?",
            (cod, rif),
        )
        if cur.rowcount > 0:
            pericoli_aggiornati += 1
    print(f"  Punti con codice attività: {pericoli_aggiornati}")

    # =================================================================
    # 3. Foglio Valutazione → popola opzioni H + codice_attivita + r_score
    # =================================================================
    ws = wb["Valutazione"]
    print("\n[3] Foglio Valutazione → opzioni H combo, codice attività, r_score")

    # Mappa elementi: nome normalizzato → id
    cur.execute("SELECT id, nome FROM lookup_valutazione_elementi")
    elementi_map = {}
    for eid, enome in cur.fetchall():
        # normalizzazione: minuscolo, spazi multipli/punteggiatura → spazio
        key = re.sub(r"\s+", " ", (enome or "").lower()).strip()
        elementi_map[key] = eid

    def trova_elemento_id(nome: str):
        if not nome:
            return None
        # Le celle A possono contenere "Nome\nutilizzate per:\n- raggiungere il..."
        # Provo prima la prima riga, poi nome completo, poi nome senza punteggiatura.
        prima_riga = nome.split("\n")[0]
        for cand in (prima_riga, nome):
            key = re.sub(r"\s+", " ", cand.lower()).strip()
            if key in elementi_map:
                return elementi_map[key]
        # Match parziale: cerco la chiave del lookup come prefix nel nome
        for key, eid in elementi_map.items():
            if key.startswith(re.sub(r"\s+", " ", prima_riga.lower()).strip()[:30]):
                return eid
        return None

    # Cancello le opzioni H esistenti (verranno repopolate ex-novo dal v2)
    cur.execute("DELETE FROM lookup_valutazione_misure WHERE colonna = 'H'")

    elemento_corrente_id = None
    h_inserite = 0
    h_skippate = 0
    ordine_g = 0

    # Pattern per estrarre N opzioni da un testo H multi-riga.
    # Le opzioni iniziano con "1- ", "2- ", "1_", "2_" ecc.
    rx_split = re.compile(r"(?m)^\s*(\d+)\s*[-_)]\s*")

    for r in range(2, ws.max_row + 1):
        a_val = norm(ws.cell(r, 1).value)
        h_val = norm(ws.cell(r, 8).value)
        i_val = norm(ws.cell(r, 9).value)

        if a_val:
            new_eid = trova_elemento_id(a_val)
            if new_eid:
                elemento_corrente_id = new_eid

        if not h_val:
            continue

        if not elemento_corrente_id:
            h_skippate += 1
            continue

        # Splitto H in opzioni. Trovo tutte le posizioni "N- " e tappo lì.
        matches = list(rx_split.finditer(h_val))
        if not matches:
            # Nessun numero esplicito: tratto tutto H come unica opzione
            opzioni = [h_val.strip()]
        else:
            opzioni = []
            for i, m in enumerate(matches):
                start = m.start()
                end = matches[i + 1].start() if i + 1 < len(matches) else len(h_val)
                opt = h_val[start:end].strip()
                if opt:
                    opzioni.append(opt)

        # Inserisco le opzioni
        for opt in opzioni:
            pulito, pp, gg, rr = estrai_pgr(opt)
            ordine_g += 1
            cur.execute(
                """INSERT INTO lookup_valutazione_misure
                   (elemento_id, colonna, testo, testo_pulito, p_score, g_score, r_score, codice_attivita, ordine)
                   VALUES (?, 'H', ?, ?, ?, ?, ?, ?, ?)""",
                (elemento_corrente_id, opt, pulito, pp, gg, rr, i_val, ordine_g),
            )
            h_inserite += 1

    print(f"  Opzioni H inserite: {h_inserite}")
    if h_skippate:
        print(f"  Righe H skippate (elemento non risolto): {h_skippate}")

    conn.commit()
    conn.close()
    print("\nFatto.")


if __name__ == "__main__":
    main()