#!/usr/bin/env python3
"""
Re-import dei testi originali del foglio "Pericoli" per ogni dvr_pericoli.

Causa: il primo import_dvr ha creato in lookup_pericoli_combo molti
testi "concatenati" (unione di righe consecutive senza rif) e ha
collegato i dvr_pericoli a questi combo errati.

Fix di questo script:
- Per ogni dvr xlsx, riapro il foglio "Pericoli"
- Per ogni riga compilabile (livello >= 3), leggo (col_c, col_d, col_e, col_f)
- UPDATE dvr_pericoli con:
    col_c_override = col_c_letto, col_c_lookup_id = NULL
    col_d, col_e   = valori normalizzati (S/X/X_COND/-/N) o NULL
    col_f_override = col_f_letto, col_f_lookup_id = NULL

Lascia intatti i DVR importati da docx (~70 sedi: il pattern di lettura
è diverso e l'override testuale c'è già).

Uso:
  python3 import-tools/scripts/reimport_pericoli.py            # solo dump SQL
  python3 import-tools/scripts/reimport_pericoli.py --apply    # applica al dev.db
  python3 import-tools/scripts/reimport_pericoli.py --dump > /tmp/pericoli.sql
"""
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"
DVR_DIR = ROOT / "-mat" / "DVR"

RIF_RE = re.compile(r"^\d+(\.\d+)+$")
STATI_VALIDI = {"S", "X", "X_COND", "-", "N"}


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


def norm_stato(v) -> str | None:
    if v is None:
        return None
    s = str(v).strip().upper()
    if s in ("X COND", "X_COND", "X COND.", "XCOND"):
        return "X_COND"
    if s in STATI_VALIDI:
        return s
    return None


def read_pericoli_from_xlsx(path: Path) -> dict[str, dict[str, str | None]]:
    """Mappa rif → {col_c, col_d, col_e, col_f}. Solo righe con rif valido."""
    out: dict[str, dict[str, str | None]] = {}
    try:
        wb = load_workbook(path, data_only=True, read_only=True)
        if "Pericoli" not in wb.sheetnames:
            return {}
        ws = wb["Pericoli"]
        for r in range(2, ws.max_row + 1):
            rif_raw = ws.cell(r, 1).value
            rif = norm(rif_raw)
            if not rif or not RIF_RE.match(rif):
                continue
            col_c = norm(ws.cell(r, 3).value)
            col_d = norm_stato(ws.cell(r, 4).value)
            col_e = norm_stato(ws.cell(r, 5).value)
            col_f = norm(ws.cell(r, 6).value)
            out[rif] = {"col_c": col_c, "col_d": col_d, "col_e": col_e, "col_f": col_f}
    except Exception as e:
        print(f"  ERR leggendo {path.name}: {e}", file=sys.stderr)
    return out


def sql_str(s: str | None) -> str:
    if s is None:
        return "NULL"
    return "'" + s.replace("'", "''") + "'"


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

    if not DB.exists():
        sys.exit(f"DB non trovato: {DB}")
    conn = sqlite3.connect(DB)
    cur = conn.cursor()

    # Tutte le sedi con DVR xlsx
    cur.execute("""
        SELECT d.id AS dvr_id, d.source_file, a.codice_fisico
        FROM dvr d
        JOIN agenzie a ON a.id = d.agenzia_id
        WHERE d.source_format = 'xlsx'
        ORDER BY a.codice_fisico
    """)
    dvr_rows = cur.fetchall()
    print(f"# {len(dvr_rows)} DVR xlsx da rileggere", file=sys.stderr)

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

    n_files_ok = 0
    n_files_err = 0
    n_rows_updated = 0
    n_rows_unchanged = 0
    sql_lines: list[str] = []

    for dvr_id, source_file, cf in dvr_rows:
        xlsx_path = DVR_DIR / source_file
        if not xlsx_path.exists():
            n_files_err += 1
            continue
        rif_to_data = read_pericoli_from_xlsx(xlsx_path)
        if not rif_to_data:
            n_files_err += 1
            continue
        n_files_ok += 1

        # Tutte le righe dvr_pericoli per questo dvr_id
        cur.execute(
            "SELECT id, punto_id, col_c_lookup_id, col_c_override, col_d, col_e, col_f_lookup_id, col_f_override FROM dvr_pericoli WHERE dvr_id = ?",
            (dvr_id,),
        )
        per_rows = cur.fetchall()

        for (pid, punto_id, c_lid_old, c_ov_old, d_old, e_old, f_lid_old, f_ov_old) in per_rows:
            rif = punto_to_rif.get(punto_id)
            if not rif:
                continue
            data = rif_to_data.get(rif)
            if not data:
                continue
            new_c = data["col_c"]
            new_d = data["col_d"]
            new_e = data["col_e"]
            new_f = data["col_f"]
            # Determine if changed
            changed = (
                c_lid_old is not None
                or (c_ov_old or None) != new_c
                or (d_old or None) != new_d
                or (e_old or None) != new_e
                or f_lid_old is not None
                or (f_ov_old or None) != new_f
            )
            if not changed:
                n_rows_unchanged += 1
                continue
            n_rows_updated += 1
            sql = (
                f"UPDATE dvr_pericoli SET "
                f"col_c_lookup_id = NULL, col_c_override = {sql_str(new_c)}, "
                f"col_d = {sql_str(new_d)}, col_e = {sql_str(new_e)}, "
                f"col_f_lookup_id = NULL, col_f_override = {sql_str(new_f)} "
                f"WHERE id = {pid};"
            )
            sql_lines.append(sql)

        if (n_files_ok + n_files_err) % 100 == 0:
            print(f"  ...{n_files_ok + n_files_err}/{len(dvr_rows)} file processati", file=sys.stderr)

    print(f"# file ok: {n_files_ok}, err: {n_files_err}", file=sys.stderr)
    print(f"# righe aggiornate: {n_rows_updated} (lasciate intatte: {n_rows_unchanged})", file=sys.stderr)

    if dump:
        for line in sql_lines:
            print(line)

    if apply_local:
        for line in sql_lines:
            cur.execute(line.rstrip(";"))
        conn.commit()
        print(f"# {n_rows_updated} UPDATE applicate al dev.db", file=sys.stderr)

    conn.close()


if __name__ == "__main__":
    main()