#!/usr/bin/env python3
"""
Riclassifica retroattivamente le immagini ancorate al foglio "Dati" che lo
script originale aveva messe tutte sotto `planimetria_layout`. La nuova
regola distingue:
- riga ≤ 25 (sopra la tabella anagrafica) → foto_immobile
- riga > 25 (sotto la descrizione immobile) → planimetria_layout

Lavora sul DB locale dev.db. Stampa un dump SQL pronto per `turso db shell`
con le UPDATE da applicare anche su prod.

Uso:
  python3 import-tools/scripts/reclassify_dati_images.py
  python3 import-tools/scripts/reclassify_dati_images.py --apply  # applica al dev.db
  python3 import-tools/scripts/reclassify_dati_images.py --dump > /tmp/reclassify.sql
"""
from __future__ import annotations
import sqlite3
import sys
import warnings
import xml.etree.ElementTree as ET
import zipfile
from pathlib import Path

warnings.filterwarnings("ignore")

ROOT = Path(__file__).resolve().parents[2]
DB = ROOT / "import-tools" / "db" / "dev.db"
DVR_DIR = ROOT / "-mat" / "DVR"

NS_R = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"
NS_XDR = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"


def map_xlsx_images(path: Path) -> dict[str, tuple[str, int]]:
    """Mapping image_filename → (sheet_lower, from_row)."""
    out: dict[str, tuple[str, int]] = {}
    try:
        with zipfile.ZipFile(path) as z:
            names = z.namelist()
            wb_rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))
            sheet_rels = {
                rel.get("Id"): rel.get("Target")
                for rel in wb_rels
                if "worksheet" in (rel.get("Type") or "")
            }
            wb = ET.fromstring(z.read("xl/workbook.xml"))
            sheets = []
            for sh in wb.iter("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheet"):
                rid = sh.get(f"{NS_R}id")
                name = sh.get("name")
                target = sheet_rels.get(rid)
                if not target:
                    continue
                sheet_path = "xl/" + target
                sheets.append((name, sheet_path))

            for sheet_name, sheet_path in sheets:
                rels_name = sheet_path.replace("worksheets/", "worksheets/_rels/") + ".rels"
                if rels_name not in names:
                    continue
                rels = ET.fromstring(z.read(rels_name))
                drawings = [
                    rel.get("Target")
                    for rel in rels
                    if "drawing" in (rel.get("Type") or "")
                ]
                for d in drawings:
                    drawing_path = "xl/" + d.replace("../", "")
                    if drawing_path not in names:
                        continue
                    dxml = ET.fromstring(z.read(drawing_path))
                    drels_name = drawing_path.replace("drawings/", "drawings/_rels/") + ".rels"
                    if drels_name not in names:
                        continue
                    drels = ET.fromstring(z.read(drels_name))
                    rid_to_img = {
                        rel.get("Id"): rel.get("Target", "").split("/")[-1]
                        for rel in drels
                    }
                    for anchor in dxml:
                        from_n = anchor.find(f"{{{NS_XDR}}}from")
                        if from_n is None:
                            continue
                        row_node = from_n.find(f"{{{NS_XDR}}}row")
                        row = int(row_node.text) if row_node is not None else 0
                        blip = anchor.iter(
                            "{http://schemas.openxmlformats.org/drawingml/2006/main}blip"
                        )
                        for b in blip:
                            embed = b.get(f"{NS_R}embed")
                            img = rid_to_img.get(embed)
                            if img:
                                out[img] = (sheet_name.lower(), row)
                                break
    except Exception:
        return {}
    return out


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()

    # Trova tutte le righe planimetria_layout con file xlsx
    cur.execute("""
        SELECT a.id, a.filename, a.tipo, d.source_file
        FROM agenzie_allegati a
        JOIN dvr d ON d.agenzia_id = a.agenzia_id
        WHERE a.tipo = 'planimetria_layout'
          AND d.source_format = 'xlsx'
          AND a.excluded = 0
        ORDER BY a.agenzia_id, a.id
    """)
    rows = cur.fetchall()
    print(f"# {len(rows)} candidate planimetria_layout da riconsiderare", file=sys.stderr)

    # Cache file → mappa anchor
    file_cache: dict[str, dict[str, tuple[str, int]]] = {}
    updates: list[tuple[int, str]] = []
    n_xlsx_open = 0
    n_image_found = 0
    n_changed = 0

    for allegato_id, filename, tipo, source_file in rows:
        # Estrai il nome originale ("image3.png") dal filename salvato
        # ("03-image3.png" o "01-image1.jpeg" ecc.)
        parts = filename.split("-", 1)
        original_name = parts[1] if len(parts) == 2 else filename

        if source_file not in file_cache:
            xlsx_path = DVR_DIR / source_file
            if not xlsx_path.exists():
                file_cache[source_file] = {}
                continue
            file_cache[source_file] = map_xlsx_images(xlsx_path)
            n_xlsx_open += 1
            if n_xlsx_open % 50 == 0:
                print(f"  ...{n_xlsx_open} xlsx aperti", file=sys.stderr)

        anchors = file_cache[source_file]
        if original_name not in anchors:
            continue
        n_image_found += 1
        sheet, row = anchors[original_name]
        if sheet == "dati" and row <= 25:
            updates.append((allegato_id, "foto_immobile"))
            n_changed += 1

    print(f"# xlsx aperti: {n_xlsx_open}", file=sys.stderr)
    print(f"# immagini risolte: {n_image_found}", file=sys.stderr)
    print(f"# da riclassificare in foto_immobile: {n_changed}", file=sys.stderr)

    if dump:
        for aid, t in updates:
            print(f"UPDATE agenzie_allegati SET tipo = '{t}' WHERE id = {aid};")

    if apply_local:
        for aid, t in updates:
            cur.execute("UPDATE agenzie_allegati SET tipo = ? WHERE id = ?", (t, aid))
        conn.commit()
        print(f"# {n_changed} righe aggiornate su dev.db", file=sys.stderr)

    conn.close()


if __name__ == "__main__":
    main()