#!/usr/bin/env python3
"""
Genera SQL aggregato per sync su Turso prod dei 4 DVR riallineati il 2026-06-15:
- TORRE ANNUNZ. AG.GEN. (id 731)
- FROSINONE CENTRO/BOVILLE (id 292)
- FROSINONE CENTRO AG.GEN. ex-Alta (id 290)
- GALLIPOLI/CENTRO URBANO (id 297)

Strategia:
- DELETE FROM dvr WHERE agenzia_id IN (...) → CASCADE pulisce figlie
- INSERT nuovi dvr + figlie
- 3 INSERT OR REPLACE in filename_overrides
- Le ref a lookup_pericoli_combo/lookup_valutazione_misure vengono convertite in
  *_override testuale per non dipendere da ID divergenti dev↔prod
- Le ref a lookup stabili (master) restano (documenti_incendio, stati_incendio,
  elementi_valutazione, azioni_piano_miglioramento)
- dvr_id nelle figlie usa subquery `(SELECT id FROM dvr WHERE agenzia_id=X)`
"""
import sqlite3
from pathlib import Path

ROOT = Path(__file__).resolve().parents[2]
DB = ROOT / "import-tools" / "db" / "dev.db"
OUT = ROOT / "app" / "migrations" / "2026-06-15-sync-4dvr.sql"

AGENZIE = [290, 292, 297, 731]


def q(s):
    if s is None:
        return "NULL"
    if isinstance(s, (int, float)):
        return str(s)
    return "'" + str(s).replace("'", "''") + "'"


def main():
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    lines = []
    a = lines.append
    a("-- 2026-06-15 — Sync prod dei 4 DVR riallineati dopo riorganizzazione cliente")
    a("-- 290 = FROSINONE CENTRO (ex Alta) AG.GEN.")
    a("-- 292 = FROSINONE CENTRO/BOVILLE (ex AG declassata)")
    a("-- 297 = GALLIPOLI/CENTRO URBANO (ex AG declassata)")
    a("-- 731 = TORRE ANNUNZ. AG.GEN. (docx riparato)")
    a("")
    a("BEGIN;")
    a("")

    # 1) filename_overrides
    a("-- 1) Filename overrides")
    rows = cur.execute(
        """
        SELECT filename, agenzia_id, match_score, reason FROM filename_overrides
        WHERE filename IN (
          'FROSINONE CENTRO_CENTRO AGENZIALE_Rev02_09092025.docx',
          'FROSINONE ALTA_CENTRO AGENZIALE_Rev04_09092025.docx',
          'GALLIPOLI_CENTRO AGENZIALE_Rev04_15102025.xlsx'
        )
        ORDER BY filename
        """
    ).fetchall()
    for r in rows:
        a(
            f"INSERT OR REPLACE INTO filename_overrides(filename, agenzia_id, match_score, reason) "
            f"VALUES ({q(r['filename'])}, {r['agenzia_id']}, {q(r['match_score'])}, {q(r['reason'])});"
        )
    a("")

    # 2) DELETE FROM dvr (CASCADE pulisce figlie)
    a("-- 2) DELETE dvr per le 4 agenzie (CASCADE pulisce dvr_pericoli, dvr_valutazione,")
    a("--    dvr_valutazione_misure, dvr_incendio_doc, dvr_piano_miglioramento, dvr_attivita_miglioramento)")
    a(f"DELETE FROM dvr WHERE agenzia_id IN ({','.join(str(x) for x in AGENZIE)});")
    a("")

    # 3) Per ogni agenzia: INSERT dvr + figlie
    for ag_id in AGENZIE:
        dvr = cur.execute("SELECT * FROM dvr WHERE agenzia_id = ?", (ag_id,)).fetchone()
        if not dvr:
            a(f"-- NO DVR per agenzia {ag_id} (skip)")
            continue
        ag_meta = cur.execute(
            "SELECT codice_fisico, nome_agenzia, nome_sede FROM agenzie WHERE id = ?",
            (ag_id,),
        ).fetchone()
        a(
            f"-- ==== Agenzia {ag_id}: {ag_meta['codice_fisico']} — "
            f"{ag_meta['nome_agenzia']} / {ag_meta['nome_sede']} ===="
        )
        a("")
        a(
            "INSERT INTO dvr (agenzia_id, rev_corrente, data_emissione, "
            "descrizione_immobile, rischio_sismico_descr, rischio_idro_si_no, "
            "rischio_ambientale_si_no, rischio_ambientale_descr, "
            "piano_emergenze_testo, rischio_incendio_testo, conclusione_testo, "
            "source_file, source_format, imported_at, "
            "rischio_idro_descr, luogo_sicuro_esterno_descr) VALUES ("
        )
        a(
            f"  {ag_id}, {dvr['rev_corrente']}, {q(dvr['data_emissione'])}, "
            f"{q(dvr['descrizione_immobile'])}, {q(dvr['rischio_sismico_descr'])}, "
            f"{q(dvr['rischio_idro_si_no'])}, {q(dvr['rischio_ambientale_si_no'])}, "
            f"{q(dvr['rischio_ambientale_descr'])}, {q(dvr['piano_emergenze_testo'])}, "
            f"{q(dvr['rischio_incendio_testo'])}, {q(dvr['conclusione_testo'])}, "
            f"{q(dvr['source_file'])}, {q(dvr['source_format'])}, {q(dvr['imported_at'])}, "
            f"{q(dvr['rischio_idro_descr'])}, {q(dvr['luogo_sicuro_esterno_descr'])});"
        )
        a("")

        dvr_id_sub = f"(SELECT id FROM dvr WHERE agenzia_id={ag_id})"

        # 3a) dvr_pericoli (override testuale per col_c/col_f)
        pericoli = cur.execute(
            """
            SELECT p.punto_id, p.col_c_override, p.col_d, p.col_e, p.col_f_override,
                   (SELECT testo FROM lookup_pericoli_combo WHERE id = p.col_c_lookup_id) AS col_c_lkp,
                   (SELECT testo FROM lookup_pericoli_combo WHERE id = p.col_f_lookup_id) AS col_f_lkp
            FROM dvr_pericoli p WHERE p.dvr_id = ?
            ORDER BY p.id
            """,
            (dvr["id"],),
        ).fetchall()
        if pericoli:
            a(f"-- {len(pericoli)} pericoli")
            a(
                "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"
            )
            vals = []
            for p in pericoli:
                col_c_text = p["col_c_override"] or p["col_c_lkp"]
                col_f_text = p["col_f_override"] or p["col_f_lkp"]
                vals.append(
                    f"  ({dvr_id_sub}, {p['punto_id']}, NULL, {q(col_c_text)}, "
                    f"{q(p['col_d'])}, {q(p['col_e'])}, NULL, {q(col_f_text)})"
                )
            a(",\n".join(vals) + ";")
            a("")

        # 3b) dvr_valutazione + dvr_valutazione_misure
        valutazioni = cur.execute(
            """
            SELECT v.id AS vid, v.elemento_id, v.figure_esposte, v.rischi_descr,
                   v.misure_prevenzione_lookup_id, v.misure_prevenzione_override,
                   v.p, v.g,
                   (SELECT testo FROM lookup_valutazione_misure WHERE id = v.misure_prevenzione_lookup_id) AS prev_lkp
            FROM dvr_valutazione v WHERE v.dvr_id = ?
            ORDER BY v.id
            """,
            (dvr["id"],),
        ).fetchall()
        if valutazioni:
            a(f"-- {len(valutazioni)} valutazioni")
            for v in valutazioni:
                prev_text = v["misure_prevenzione_override"] or v["prev_lkp"]
                a(
                    "INSERT INTO dvr_valutazione (dvr_id, elemento_id, figure_esposte, "
                    "rischi_descr, misure_prevenzione_lookup_id, misure_prevenzione_override, "
                    f"p, g) VALUES ({dvr_id_sub}, {v['elemento_id']}, "
                    f"{q(v['figure_esposte'])}, {q(v['rischi_descr'])}, NULL, "
                    f"{q(prev_text)}, {q(v['p'])}, {q(v['g'])});"
                )
                # misure H di questa valutazione
                misure = cur.execute(
                    """
                    SELECT vm.lookup_id, vm.testo_override, vm.p_var, vm.g_var, vm.r_var, vm.ordine,
                           (SELECT testo FROM lookup_valutazione_misure WHERE id = vm.lookup_id) AS lkp_testo
                    FROM dvr_valutazione_misure vm WHERE vm.valutazione_id = ?
                    ORDER BY vm.id
                    """,
                    (v["vid"],),
                ).fetchall()
                for m in misure:
                    text = m["testo_override"] or m["lkp_testo"]
                    vsub = (
                        f"(SELECT id FROM dvr_valutazione "
                        f"WHERE dvr_id={dvr_id_sub} AND elemento_id={v['elemento_id']})"
                    )
                    a(
                        "INSERT INTO dvr_valutazione_misure (valutazione_id, lookup_id, "
                        f"testo_override, p_var, g_var, r_var, ordine) VALUES ({vsub}, NULL, "
                        f"{q(text)}, {q(m['p_var'])}, {q(m['g_var'])}, {q(m['r_var'])}, {q(m['ordine'])});"
                    )
            a("")

        # 3c) dvr_incendio_doc — stati e documenti sono stabili (master)
        inc = cur.execute(
            """
            SELECT documento_id, stato_lookup_id, stato_override, note
            FROM dvr_incendio_doc WHERE dvr_id = ? ORDER BY id
            """,
            (dvr["id"],),
        ).fetchall()
        if inc:
            a(f"-- {len(inc)} doc incendio")
            a(
                "INSERT INTO dvr_incendio_doc (dvr_id, documento_id, stato_lookup_id, "
                "stato_override, note) VALUES"
            )
            vals = [
                f"  ({dvr_id_sub}, {r['documento_id']}, {q(r['stato_lookup_id'])}, "
                f"{q(r['stato_override'])}, {q(r['note'])})"
                for r in inc
            ]
            a(",\n".join(vals) + ";")
            a("")

        # 3d) dvr_piano_miglioramento — azioni stabili (master)
        piano = cur.execute(
            """
            SELECT attivita_lookup_id, attivita_override, responsabile,
                   data_avvio, data_conclusione, ordine
            FROM dvr_piano_miglioramento WHERE dvr_id = ? ORDER BY id
            """,
            (dvr["id"],),
        ).fetchall()
        if piano:
            a(f"-- {len(piano)} piano migl")
            a(
                "INSERT INTO dvr_piano_miglioramento (dvr_id, attivita_lookup_id, "
                "attivita_override, responsabile, data_avvio, data_conclusione, ordine) VALUES"
            )
            vals = [
                f"  ({dvr_id_sub}, {q(r['attivita_lookup_id'])}, {q(r['attivita_override'])}, "
                f"{q(r['responsabile'])}, {q(r['data_avvio'])}, {q(r['data_conclusione'])}, "
                f"{q(r['ordine'])})"
                for r in piano
            ]
            a(",\n".join(vals) + ";")
            a("")

    a("COMMIT;")
    a("")

    OUT.parent.mkdir(parents=True, exist_ok=True)
    OUT.write_text("\n".join(lines), encoding="utf-8")
    print(f"Scritto: {OUT}")
    print(f"Righe: {len(lines)}")
    print(f"Dimensione: {OUT.stat().st_size:,} byte")


if __name__ == "__main__":
    main()