# MyICR_Suite/app/modules/mediatrack/backend/database_logic.py

import os
import sqlite3
import threading
import requests
import logging
import sys
import json
from datetime import datetime
from pathlib import Path

# Setup logging
logger = logging.getLogger(__name__)

# =================================================================
# Database Condiviso — config via app.config_loader (config.json)
# =================================================================

# Fix PYTHONPATH: aggiunge MyICR_Suite/ al path per import app.*
_current_file = Path(__file__).resolve()
_myicr_suite_dir = _current_file.parent.parent.parent.parent
if str(_myicr_suite_dir) not in sys.path:
    sys.path.insert(0, str(_myicr_suite_dir))

from .config_loader import get_database_path

# Carica path database
NOME_DATABASE = get_database_path()
logger.info(f"MEDIATRACK - Database: {NOME_DATABASE}")

# =================================================================

def _conn():
    """
    Crea e restituisce una connessione al database.
    Ottimizzato per database condiviso su rete con concorrenza multi-utente.
    """
    # Timeout 30s per gestire lock su database condiviso (2-5 utenti concorrenti)
    # Se il DB è locked da altro client, attende fino a 30s prima di fallire
    con = sqlite3.connect(NOME_DATABASE, timeout=30.0)

    # Abilitiamo il supporto per le foreign keys
    con.execute("PRAGMA foreign_keys = 1")

    # Write-Ahead Logging: migliora concorrenza su database condiviso
    # - Lettori non bloccano scrittori
    # - Scrittori non bloccano lettori
    # - Ideale per 2-10 utenti concorrenti su network share
    try:
        con.execute("PRAGMA journal_mode = WAL")
    except Exception:
        pass  # WAL non supportato su SMB — DELETE mode sufficiente con timeout=30s

    # Ottimizzazione per database su rete: riduce numero di fsync
    # NORMAL = buon bilancio tra sicurezza e performance
    con.execute("PRAGMA synchronous = NORMAL")

    # Migration: aggiunge colonne mancanti se non esistono
    _ensure_offline_column(con)
    _ensure_visibilita_column(con)
    _ensure_anagrafica_extended_cols(con)
    _ensure_updated_cols(con)

    return con


def _get_gemma_parquet() -> Path:
    """Ritorna il path assoluto di gemma.parquet."""
    from app.config_loader import get_app_root, load_config
    _app_root = get_app_root()
    return _app_root / load_config().get('parquet_dir', 'local_db/parquet') / 'gemma.parquet'


# Cache DataFrame in-memory per gemma.parquet — letto una volta, query via DuckDB locale
_gemma_cached_df = None
_gemma_cache_lock = threading.Lock()
_gemma_cache_mtime = 0.0


def _get_gemma_cached():
    """Ritorna il DataFrame di gemma.parquet, leggendolo solo se mtime è cambiato."""
    global _gemma_cached_df, _gemma_cache_mtime
    import duckdb
    import pandas as pd
    p = _get_gemma_parquet()
    if not p.exists():
        return pd.DataFrame()
    mtime = p.stat().st_mtime
    with _gemma_cache_lock:
        if _gemma_cached_df is None or _gemma_cache_mtime != mtime:
            p_str = str(p).replace('\\', '/')
            con = duckdb.connect()
            try:
                _gemma_cached_df = con.execute(f"SELECT * FROM read_parquet('{p_str}')").df()
            finally:
                con.close()
            _gemma_cache_mtime = mtime
        return _gemma_cached_df


def _gemma_df(where: str = None, columns: str = '*'):
    """Legge gemma.parquet via cache in-memory. Connessione DuckDB per-call su DataFrame.
    Thread-safe: ogni thread ha la sua connessione locale, il DataFrame è condiviso read-only."""
    import duckdb
    df = _get_gemma_cached()
    if df.empty:
        return df
    con = duckdb.connect()
    con.register('gemma', df)
    sql = f"SELECT {columns} FROM gemma"
    if where:
        sql += f" WHERE {where}"
    try:
        return con.execute(sql).df()
    finally:
        con.close()


def _ensure_scaricato_il_column(con: sqlite3.Connection) -> None:
    """Garantisce che la colonna 'scaricato_il' esista in OmdbData (migration safe)."""
    try:
        cur = con.cursor()
        cur.execute("PRAGMA table_info(OmdbData)")
        cols = [row[1] for row in cur.fetchall()]
        if 'scaricato_il' not in cols:
            cur.execute("ALTER TABLE OmdbData ADD COLUMN scaricato_il TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
            con.commit()
    except sqlite3.Error:
        pass


def _ensure_offline_column(con: sqlite3.Connection) -> None:
    """Aggiunge is_offline_insert ad AnagraficaMedia se non esiste (migration safe)."""
    try:
        cols = [r[1] for r in con.execute("PRAGMA table_info(AnagraficaMedia)").fetchall()]
        if 'is_offline_insert' not in cols:
            con.execute("ALTER TABLE AnagraficaMedia ADD COLUMN is_offline_insert INTEGER DEFAULT 0")
            con.commit()
    except sqlite3.Error:
        pass


def _ensure_visibilita_column(con: sqlite3.Connection) -> None:
    """Aggiunge visibilita a TipiEvento se non esiste, con seed per i tipi predefiniti."""
    try:
        cols = [r[1] for r in con.execute("PRAGMA table_info(TipiEvento)").fetchall()]
        if 'visibilita' not in cols:
            con.execute("ALTER TABLE TipiEvento ADD COLUMN visibilita TEXT DEFAULT 'entrambi'")
            # Seed: Line-up solo distributore, Mercato entrambi, resto solo prodotto
            con.execute("UPDATE TipiEvento SET visibilita='distributore' WHERE LOWER(nome_evento) = 'line-up'")
            con.execute("UPDATE TipiEvento SET visibilita='prodotto' WHERE visibilita='entrambi' AND LOWER(nome_evento) NOT IN ('note')")
            con.commit()
    except sqlite3.Error:
        pass


def _is_offline() -> bool:
    """Legge il flag di rete scritto dal bootstrap. False = online (default sicuro)."""
    try:
        from app.config_loader import get_app_root
        flag = get_app_root() / "launcher_cache" / "network_status.json"
        if flag.exists():
            data = json.loads(flag.read_text(encoding="utf-8"))
            return not data.get("online", True)
    except Exception:
        pass
    return False


def _log_offline_insert(id_media: str, dati_anagrafica: dict, dati_evento: dict) -> None:
    """Appende il payload completo al log JSONL degli inserimenti offline.
    Safety net indipendente dal DB: sopravvive a corruzione di mediatrack.db."""
    try:
        from app.config_loader import get_app_root
        import datetime
        log_file = get_app_root() / "sync_db" / "offline_inserts_log.jsonl"
        entry = {
            "ts": datetime.datetime.now().isoformat(),
            "id_media": id_media,
            "anagrafica": dati_anagrafica,
            "evento": dati_evento,
        }
        with open(log_file, "a", encoding="utf-8") as f:
            f.write(json.dumps(entry, ensure_ascii=False) + "\n")
    except Exception as e:
        logger.warning(f"[offline] Log JSONL fallito: {e}")


def get_pending_offline_count() -> int:
    """Restituisce il numero di prodotti inseriti offline non ancora sincronizzati."""
    try:
        con = sqlite3.connect(NOME_DATABASE)
        count = con.execute(
            "SELECT COUNT(*) FROM AnagraficaMedia WHERE is_offline_insert = 1"
        ).fetchone()[0]
        con.close()
        return count
    except Exception:
        return 0


def _ensure_anagrafica_extended_cols(con: sqlite3.Connection) -> None:
    """Aggiunge i campi descrittivi mancanti ad AnagraficaMedia (migration idempotente)."""
    cur = con.cursor()
    try:
        cols = [row[1] for row in cur.execute("PRAGMA table_info(AnagraficaMedia)").fetchall()]
        new_cols = [
            ('anno_produzione',  'TEXT'),
            ('durata',           'TEXT'),
            ('paesi',            'TEXT'),
            ('generi',           'TEXT'),
            ('registi',          'TEXT'),
            ('attori',           'TEXT'),
            ('trama',            'TEXT'),
        ]
        for col_name, col_type in new_cols:
            if col_name not in cols:
                cur.execute(f"ALTER TABLE AnagraficaMedia ADD COLUMN {col_name} {col_type}")
        con.commit()
    except sqlite3.Error:
        pass


def _ensure_updated_cols(con: sqlite3.Connection) -> None:
    """Aggiunge updated_at e updated_by ad AnagraficaMedia (migration idempotente)."""
    try:
        cols = {r[1] for r in con.execute("PRAGMA table_info(AnagraficaMedia)").fetchall()}
        if 'updated_at' not in cols:
            con.execute("ALTER TABLE AnagraficaMedia ADD COLUMN updated_at TEXT")
        if 'updated_by' not in cols:
            con.execute("ALTER TABLE AnagraficaMedia ADD COLUMN updated_by TEXT")
        con.commit()
    except sqlite3.Error:
        pass


def _now_iso() -> str:
    return datetime.now().isoformat(timespec='seconds')


def _this_machine() -> str:
    return os.environ.get('COMPUTERNAME', os.environ.get('HOSTNAME', 'unknown'))


def _ensure_imdb_flag_column(con: sqlite3.Connection) -> None:
    """Garantisce che la colonna 'imdb_search_status' esista in AnagraficaMedia.

    Stati possibili:
    - NULL/0: non ancora controllato su OMDb
    - 1: controllato e trovato almeno un match
    - -1: controllato ma nessun match trovato

    Questo evita errori quando si esegue la scansione preliminare su DB non migrati.
    """
    cur = con.cursor()
    try:
        cur.execute("PRAGMA table_info(AnagraficaMedia)")
        cols = [row[1] for row in cur.fetchall()]
        if 'imdb_search_status' not in cols:
            cur.execute("ALTER TABLE AnagraficaMedia ADD COLUMN imdb_search_status INTEGER DEFAULT NULL")
            con.commit()
    except sqlite3.Error:
        # Non interrompere il flusso se il DB è in uno stato non previsto
        pass

# Funzione per creare la tabella OmdbData (utile per setup iniziale o test)
def create_omdb_table():
    con = _conn()
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE IF NOT EXISTS OmdbData (
            id_media_fk TEXT NOT NULL,
            codice_imdb TEXT,
            titolo_omdb TEXT,
            sinossi TEXT,
            anno_produzione TEXT,
            data_rilascio TEXT,
            durata TEXT,
            generi TEXT,
            registi TEXT,
            sceneggiatori TEXT,
            attori TEXT,
            lingue TEXT,
            paesi TEXT,
            premi TEXT,
            url_poster TEXT,
            metascore TEXT,
            imdb_rating TEXT,
            imdb_voti TEXT,
            box_office TEXT,
            produzione TEXT,
            scaricato_il TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id_media_fk),
            FOREIGN KEY (id_media_fk) REFERENCES AnagraficaMedia(id_media)
        );""")
    con.commit()
    con.close()


def get_dati_griglia_filtrati(titolo: str = '', distributore: str = '', contesto: str = '', includi_gemma: bool = False, codice_imdb: str = '', anno_min_gemma: str = '', tipologia: str = '', tipo_evento: str = '') -> list[dict]:
    """
    Restituisce i dati per la griglia principale, filtrati in base ai parametri.
    Questa è la funzione "trapiantata" dal vecchio database_manager.py.

    Args:
        titolo: Filtro per titolo
        distributore: Filtro per distributore
        contesto: Filtro per contesto/evento
        includi_gemma: Se True, include anche i risultati dalla tabella Gemma
    """
    con = _conn()
    # con.row_factory = sqlite3.Row permette di accedere ai risultati come dizionari.
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Lookup alias: se il contesto è nella tabella Contesti, usa gemma_supporto per il match su Gemma
    gemma_contesto = contesto  # default: stesso valore
    if contesto and contesto.strip():
        _ensure_contesti_table(con)
        row = con.execute(
            "SELECT gemma_supporto FROM Contesti WHERE LOWER(nome) = LOWER(?)",
            (contesto.strip(),)
        ).fetchone()
        if row and row[0]:
            gemma_contesto = row[0]

    query = """
        WITH ultimi AS (
            SELECT
                id_media_fk,
                costo_richiesto_ita,
                costo_richiesto_spa,
                ROW_NUMBER() OVER(
                    PARTITION BY id_media_fk
                    ORDER BY date(COALESCE(data_evento,'0001-01-01')) DESC, id_aggiornamento DESC
                ) rn
            FROM LogAggiornamenti
        ),
        ultimi_contesto AS (
            -- Ultimo evento con contesto non NULL (gli allegati hanno contesto NULL)
            SELECT
                id_media_fk,
                dettaglio_contesto,
                ROW_NUMBER() OVER(
                    PARTITION BY id_media_fk
                    ORDER BY date(COALESCE(data_evento,'0001-01-01')) DESC, id_aggiornamento DESC
                ) rn
            FROM LogAggiornamenti
            WHERE dettaglio_contesto IS NOT NULL AND TRIM(dettaglio_contesto) != ''
        )
        SELECT
            A.id_media,
            A.titolo_ufficiale,
            A.tipo,
            A.codice_imdb,
            O.url_poster,
            O.anno_produzione,
            A.distributore_nome AS distributore,
            UC.dettaglio_contesto AS contesto,
            U.costo_richiesto_ita,
            U.costo_richiesto_spa,
            'mediatrack' AS fonte,
            NULL AS rda, NULL AS c5, NULL AS i1, NULL AS r4, NULL AS la5,
            NULL AS i2, NULL AS iris, NULL AS top_crime, NULL AS foc,
            NULL AS c20, NULL AS cine34, NULL AS twent_seven,
            NULL AS supporto, NULL AS data_valutazione,
            (SELECT COUNT(*) FROM LogAggiornamenti WHERE id_media_fk = A.id_media) AS num_eventi,
            CASE WHEN EXISTS (
                SELECT 1 FROM LogAggiornamenti LV
                LEFT JOIN TipiEvento TE ON LV.id_tipo_evento_fk = TE.id_tipo_evento
                WHERE LV.id_media_fk = A.id_media
                  AND (
                    LOWER(COALESCE(TE.nome_evento,'')) = 'venduto'
                    OR (LV.venduto_a IS NOT NULL AND TRIM(COALESCE(LV.venduto_a,'')) != '')
                  )
            ) THEN 'Venduto' ELSE NULL END AS acquistato_venduto,
            A.imdb_search_status
        FROM AnagraficaMedia A
        LEFT JOIN ultimi U ON A.id_media = U.id_media_fk AND U.rn = 1
        LEFT JOIN ultimi_contesto UC ON A.id_media = UC.id_media_fk AND UC.rn = 1
        LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
        WHERE 1=1
          AND A.id_media NOT LIKE 'G_%'
          AND A.deleted_at IS NULL
    """

    # NOTA: Escludiamo SEMPRE i prodotti Gemma importati (G_%) da AnagraficaMedia
    # Se includi_gemma=True, verranno mostrati dalla tabella gemma (query UNION)

    params = []
    # Usiamo .strip() per pulire gli input prima di usarli nel LIKE
    if titolo and titolo.strip():
        query += " AND LOWER(A.titolo_ufficiale) LIKE LOWER(?)"
        params.append(f"%{titolo.strip()}%")
    if distributore and distributore.strip():
        # Ricerca esatta del distributore (non parziale)
        query += " AND LOWER(COALESCE(A.distributore_nome,'')) = LOWER(?)"
        params.append(distributore.strip())
    if tipo_evento and tipo_evento.strip():
        query += """ AND EXISTS (
            SELECT 1 FROM LogAggiornamenti L2
            JOIN TipiEvento TE ON L2.id_tipo_evento_fk = TE.id_tipo_evento
            WHERE L2.id_media_fk = A.id_media
              AND LOWER(COALESCE(TE.nome_evento,'')) = LOWER(?)
        )"""
        params.append(tipo_evento.strip())
    if contesto and contesto.strip():
        query += """ AND EXISTS (
            SELECT 1 FROM LogAggiornamenti L3
            WHERE L3.id_media_fk = A.id_media
              AND LOWER(COALESCE(L3.dettaglio_contesto,'')) LIKE LOWER(?)
        )"""
        params.append(f"%{contesto.strip()}%")
    if codice_imdb and codice_imdb.strip():
        query += " AND LOWER(COALESCE(A.codice_imdb,'')) = LOWER(?)"
        params.append(codice_imdb.strip())
    if tipologia and tipologia.strip():
        tips = [t.strip() for t in tipologia.split(',') if t.strip()]
        placeholders = ','.join(['LOWER(?)'] * len(tips))
        query += f" AND LOWER(COALESCE(A.tipo,'')) IN ({placeholders})"
        params.extend(tips)

    query += " ORDER BY titolo_ufficiale ASC"

    cur.execute(query, tuple(params))
    rows = [dict(r) for r in cur.fetchall()]
    con.close()

    # Se includi_gemma è True, carica i prodotti Gemma via DuckDB e mergia in Python
    if includi_gemma:
        rows = _merge_gemma_in_griglia(
            rows, titolo, distributore, contesto, gemma_contesto,
            codice_imdb, anno_min_gemma, tipologia, tipo_evento
        )

    return rows


def _merge_gemma_in_griglia(
    mt_rows: list,
    titolo: str, distributore: str, contesto: str, gemma_contesto: str,
    codice_imdb: str, anno_min_gemma: str, tipologia: str, tipo_evento: str
) -> list:
    """
    Carica i prodotti Gemma (da gemma.parquet via DuckDB), applica i filtri in Python,
    poi mergia con i dati MediaTrack già estratti da SQLite.

    I campi che dipendono da SQLite (num_eventi, costo_richiesto_*, contesto da LogAggiornamenti,
    acquistato_venduto da TipiEvento, url_poster da OmdbData) vengono risolti
    con una singola query SQLite bulk sui G_ IDs trovati.
    """
    # Costruisci il where DuckDB per i campi puri Gemma
    where_parts = []
    if titolo and titolo.strip():
        t_esc = titolo.strip().replace("'", "''")
        where_parts.append(f"LOWER(A_TITOLO) LIKE LOWER('%{t_esc}%')")
    if distributore and distributore.strip():
        d_esc = distributore.strip().replace("'", "''")
        where_parts.append(f"LOWER(COALESCE(A_DISTRIBUTORE,'')) = LOWER('{d_esc}')")
    if codice_imdb and codice_imdb.strip():
        i_esc = codice_imdb.strip().replace("'", "''")
        where_parts.append(f"LOWER(COALESCE(A_COD_IMDB,'')) = LOWER('{i_esc}')")
    if anno_min_gemma and anno_min_gemma.strip():
        a_esc = anno_min_gemma.strip().replace("'", "''")
        where_parts.append(f"SUBSTR(A_DATA, 7, 4) >= '{a_esc}'")
    if tipologia and tipologia.strip():
        tips = [t.strip().lower().replace("'", "''") for t in tipologia.split(',') if t.strip()]
        tips_str = "', '".join(tips)
        where_parts.append(f"LOWER(COALESCE(A_TIPOLOGIA,'')) IN ('{tips_str}')")

    gdf = _gemma_df(
        where=' AND '.join(where_parts) if where_parts else None,
        columns=(
            'A_ID, A_TITOLO, A_TIPOLOGIA, A_COD_IMDB, A_DATA, A_DISTRIBUTORE, A_SUPPORTO, '
            'A_ACQUISTATO_VENDUTO, V_RDA, V_C5, V_I1, V_R4, V_LA5, V_I2, V_IRIS, V_TOP, '
            'V_FOC, V_C20, V_CI34, V_C27'
        )
    )
    if gdf.empty:
        return mt_rows

    # Costruisci lista G_ IDs
    g_ids = ['G_' + str(aid) for aid in gdf['A_ID'].tolist()]

    # Query SQLite bulk: preleva per tutti i G_ IDs in una volta sola.
    # Usa tabella temp per aggirare il limite SQLite di 999 bind variables.
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    cur.execute("CREATE TEMPORARY TABLE _tmp_gids (id TEXT PRIMARY KEY)")
    for i in range(0, len(g_ids), 900):
        cur.executemany("INSERT OR IGNORE INTO _tmp_gids VALUES (?)",
                        [(x,) for x in g_ids[i:i + 900]])

    # url_poster da OmdbData
    cur.execute("SELECT id_media_fk, url_poster, codice_imdb FROM OmdbData WHERE id_media_fk IN (SELECT id FROM _tmp_gids)")
    omdb_map = {r['id_media_fk']: {'url_poster': r['url_poster'], 'codice_imdb': r['codice_imdb']} for r in cur.fetchall()}

    # num_eventi da LogAggiornamenti
    cur.execute("SELECT id_media_fk, COUNT(*) AS cnt FROM LogAggiornamenti WHERE id_media_fk IN (SELECT id FROM _tmp_gids) GROUP BY id_media_fk")
    eventi_map = {r['id_media_fk']: r['cnt'] for r in cur.fetchall()}

    # ultimo contesto + costi da LogAggiornamenti (ultimi)
    cur.execute("""
        SELECT id_media_fk, dettaglio_contesto, costo_richiesto_ita, costo_richiesto_spa
        FROM (
            SELECT id_media_fk, dettaglio_contesto, costo_richiesto_ita, costo_richiesto_spa,
                   ROW_NUMBER() OVER(PARTITION BY id_media_fk ORDER BY date(COALESCE(data_evento,'0001-01-01')) DESC, id_aggiornamento DESC) rn
            FROM LogAggiornamenti
            WHERE id_media_fk IN (SELECT id FROM _tmp_gids)
        ) WHERE rn = 1
    """)
    ultimi_map = {r['id_media_fk']: dict(r) for r in cur.fetchall()}

    # ultimi_contesto
    cur.execute("""
        SELECT id_media_fk, dettaglio_contesto
        FROM (
            SELECT id_media_fk, dettaglio_contesto,
                   ROW_NUMBER() OVER(PARTITION BY id_media_fk ORDER BY date(COALESCE(data_evento,'0001-01-01')) DESC, id_aggiornamento DESC) rn
            FROM LogAggiornamenti
            WHERE id_media_fk IN (SELECT id FROM _tmp_gids)
              AND dettaglio_contesto IS NOT NULL AND TRIM(dettaglio_contesto) != ''
        ) WHERE rn = 1
    """)
    contesto_map = {r['id_media_fk']: r['dettaglio_contesto'] for r in cur.fetchall()}

    # acquistato_venduto da TipiEvento (Venduto)
    cur.execute("""
        SELECT DISTINCT LV.id_media_fk
        FROM LogAggiornamenti LV
        LEFT JOIN TipiEvento TE ON LV.id_tipo_evento_fk = TE.id_tipo_evento
        WHERE LV.id_media_fk IN (SELECT id FROM _tmp_gids)
          AND (LOWER(COALESCE(TE.nome_evento,'')) = 'venduto'
               OR (LV.venduto_a IS NOT NULL AND TRIM(COALESCE(LV.venduto_a,'')) != ''))
    """)
    venduto_set = {r['id_media_fk'] for r in cur.fetchall()}

    # Se filtro tipo_evento, filtra i G_ IDs che hanno quell'evento
    if tipo_evento and tipo_evento.strip():
        cur.execute("""
            SELECT DISTINCT LG.id_media_fk
            FROM LogAggiornamenti LG
            JOIN TipiEvento TEG ON LG.id_tipo_evento_fk = TEG.id_tipo_evento
            WHERE LG.id_media_fk IN (SELECT id FROM _tmp_gids)
              AND LOWER(COALESCE(TEG.nome_evento,'')) = LOWER(?)
        """, [tipo_evento.strip()])
        tipo_evento_set = {r['id_media_fk'] for r in cur.fetchall()}
    else:
        tipo_evento_set = None

    con.close()

    # Costruisci righe Gemma
    gemma_rows = []
    contesto_lower = contesto.strip().lower() if contesto and contesto.strip() else None
    gemma_contesto_lower = gemma_contesto.strip().lower() if gemma_contesto and gemma_contesto.strip() else None

    for _, r in gdf.iterrows():
        id_media = 'G_' + str(r['A_ID'])

        # Filtro tipo_evento
        if tipo_evento_set is not None and id_media not in tipo_evento_set:
            continue

        # Filtro contesto (se attivo): il prodotto deve avere contesto matching in LogAggiornamenti O in A_SUPPORTO
        if contesto_lower:
            ct_log = (contesto_map.get(id_media) or '').lower()
            ct_sup = (str(r.get('A_SUPPORTO') or '')).lower()
            if contesto_lower not in ct_log and (not gemma_contesto_lower or gemma_contesto_lower not in ct_sup):
                continue

        omdb = omdb_map.get(id_media, {})
        ul = ultimi_map.get(id_media, {})
        ct = contesto_map.get(id_media)
        if ct is None:
            ct = r.get('A_SUPPORTO')

        # acquistato_venduto
        av_raw = r.get('A_ACQUISTATO_VENDUTO')
        if av_raw in ('Venduto', 'Acquistato'):
            acquistato_venduto = av_raw
        elif id_media in venduto_set:
            acquistato_venduto = 'Venduto'
        else:
            acquistato_venduto = None

        # anno_produzione da A_DATA (formato dd/mm/yyyy → yyyy)
        a_data = str(r.get('A_DATA') or '')
        anno_prod = a_data[6:10] if len(a_data) >= 10 else None

        gemma_rows.append({
            'id_media': id_media,
            'titolo_ufficiale': r.get('A_TITOLO'),
            'tipo': r.get('A_TIPOLOGIA'),
            'codice_imdb': r.get('A_COD_IMDB') or omdb.get('codice_imdb'),
            'url_poster': omdb.get('url_poster'),
            'anno_produzione': anno_prod,
            'distributore': r.get('A_DISTRIBUTORE'),
            'contesto': ct,
            'costo_richiesto_ita': ul.get('costo_richiesto_ita'),
            'costo_richiesto_spa': ul.get('costo_richiesto_spa'),
            'fonte': 'gemma',
            'rda': r.get('V_RDA'), 'c5': r.get('V_C5'), 'i1': r.get('V_I1'),
            'r4': r.get('V_R4'), 'la5': r.get('V_LA5'), 'i2': r.get('V_I2'),
            'iris': r.get('V_IRIS'), 'top_crime': r.get('V_TOP'), 'foc': r.get('V_FOC'),
            'c20': r.get('V_C20'), 'cine34': r.get('V_CI34'), 'twent_seven': r.get('V_C27'),
            'supporto': r.get('A_SUPPORTO'),
            'data_valutazione': None,
            'num_eventi': eventi_map.get(id_media, 0),
            'acquistato_venduto': acquistato_venduto,
            'imdb_search_status': None,
        })

    combined = mt_rows + gemma_rows
    combined.sort(key=lambda x: (x.get('titolo_ufficiale') or '').lower())
    return combined

def get_distributori_unici(includi_gemma: bool = False, anno_min_gemma: str = '') -> list[dict]:
    """
    Recupera una lista ordinata di distributori unici da Gemma (fonte unica).

    Args:
        includi_gemma: Se True, include distributori da Gemma
        anno_min_gemma: Se valorizzato, filtra Gemma a prodotti con anno >= valore
                        (usato in Modalità Mercato per limitare agli ultimi 3 anni)
    """
    con = _conn()
    cur = con.cursor()

    if includi_gemma:
        # Leggi distributori da MediaTrack (SQLite)
        cur.execute("""
            SELECT DISTINCT distributore_nome AS nome
            FROM AnagraficaMedia
            WHERE distributore_nome IS NOT NULL AND TRIM(distributore_nome) != ''
            AND id_media NOT LIKE 'G_%' AND deleted_at IS NULL
        """)
        mt_nomi = {r[0] for r in cur.fetchall()}

        # Leggi distributori da gemma.parquet (DuckDB)
        if anno_min_gemma and anno_min_gemma.strip():
            where_g = f"A_DISTRIBUTORE IS NOT NULL AND TRIM(A_DISTRIBUTORE) != '' AND substr(A_DATA, 7, 4) >= '{anno_min_gemma.strip()}'"
        else:
            where_g = "A_DISTRIBUTORE IS NOT NULL AND TRIM(A_DISTRIBUTORE) != ''"
        gdf = _gemma_df(where=where_g, columns='DISTINCT A_DISTRIBUTORE')
        gemma_nomi = set(gdf['A_DISTRIBUTORE'].dropna().tolist()) if not gdf.empty else set()

        tutti = mt_nomi | gemma_nomi
        out = sorted([{'nome': n, 'fonte': 'gemma'} for n in tutti], key=lambda x: (x['nome'] or '').lower())
    else:
        # Solo distributori usati in prodotti MediaTrack
        cur.execute("""
            SELECT DISTINCT distributore_nome AS nome, 'gemma' AS fonte
            FROM AnagraficaMedia
            WHERE distributore_nome IS NOT NULL AND TRIM(distributore_nome) != ''
            AND id_media NOT LIKE 'G_%' AND deleted_at IS NULL
            ORDER BY LOWER(distributore_nome) ASC
        """)
        out = [{'nome': r[0], 'fonte': r[1]} for r in cur.fetchall()]

    con.close()
    return out


def is_distributore_in_gemma(nome_distributore: str) -> bool:
    """
    Verifica se un distributore esiste nella tabella Gemma.

    Args:
        nome_distributore: Nome del distributore da cercare

    Returns:
        True se il distributore esiste in Gemma, False altrimenti
    """
    if not nome_distributore or not nome_distributore.strip():
        return False

    nome_lower = nome_distributore.strip().lower()

    # Cerca in gemma.parquet via DuckDB
    gdf = _gemma_df(
        where=f"LOWER(TRIM(A_DISTRIBUTORE)) = '{nome_lower.replace(chr(39), chr(39)+chr(39))}'",
        columns='A_DISTRIBUTORE'
    )
    if not gdf.empty:
        return True

    # Fallback: distributori inseriti manualmente prima della limitazione Gemma
    con = _conn()
    cur = con.cursor()
    cur.execute("""
        SELECT 1 FROM AnagraficaMedia
        WHERE LOWER(TRIM(distributore_nome)) = LOWER(TRIM(?))
        AND id_media NOT LIKE 'G_%' AND deleted_at IS NULL
        LIMIT 1
    """, (nome_distributore,))
    result = cur.fetchone() is not None
    con.close()
    return result


def get_contesti_unici() -> list[str]:
    """Recupera i contesti dalla tabella Contesti (vocabolario controllato), ordinati per ordine."""
    con = _conn()
    _ensure_contesti_table(con)
    rows = con.execute(
        "SELECT nome FROM Contesti WHERE attivo = 1 ORDER BY ordine, nome"
    ).fetchall()
    con.close()
    return [r[0] for r in rows]

def get_tipologie_uniche() -> list[str]:
    """Recupera lista ordinata di tipologie uniche da Gemma."""
    gdf = _gemma_df(
        where="A_TIPOLOGIA IS NOT NULL AND TRIM(A_TIPOLOGIA) <> ''",
        columns='DISTINCT A_TIPOLOGIA'
    )
    if gdf.empty:
        return []
    return sorted({str(v).upper() for v in gdf['A_TIPOLOGIA'].dropna()})

def get_storia_prodotto(id_media: str) -> tuple[dict | None, list[dict]]:
    """
    Restituisce i dati di anagrafica e la cronologia degli aggiornamenti per un dato id_media.
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Recupera i dati dell'anagrafica del prodotto, includendo il nome del distributore
    cur.execute("""
        SELECT A.*, A.distributore_nome AS distributore_nome
        FROM AnagraficaMedia A
        WHERE A.id_media = ? AND A.deleted_at IS NULL
    """, (id_media,))
    
    anagrafica_row = cur.fetchone()
    
    # Se non troviamo il prodotto, restituiamo subito None e una lista vuota
    # Aggiungiamo anche il recupero dei dati OMDB
    cur.execute("""
        SELECT * FROM OmdbData WHERE id_media_fk = ?
    """, (id_media,))
    omdb_data_row = cur.fetchone()
    omdb_data = dict(omdb_data_row) if omdb_data_row else None

    # Recupera tutti i log per quel prodotto (anche per prodotti Gemma G_ non in AnagraficaMedia)
    cur.execute("""
        SELECT
            log.*,
            tipi.nome_evento,
            tipi.colore_hex
        FROM LogAggiornamenti AS log
        JOIN TipiEvento AS tipi ON log.id_tipo_evento_fk = tipi.id_tipo_evento
        WHERE log.id_media_fk = ?
        ORDER BY date(COALESCE(log.data_evento,'0001-01-01')) DESC, log.id_aggiornamento DESC
    """, (id_media,))

    logs = []
    for r in cur.fetchall():
        log_dict = dict(r)
        if log_dict.get('percorso_file'):
            percorso_file = log_dict['percorso_file']
            if isinstance(percorso_file, str) and percorso_file.startswith('['):
                try:
                    log_dict['percorso_file'] = json.loads(percorso_file)
                except json.JSONDecodeError:
                    pass
        logs.append(log_dict)

    if not anagrafica_row:
        con.close()
        return None, logs, None

    anagrafica = dict(anagrafica_row)

    # Merge OmdbData come fallback per i campi editoriali ancora NULL in AnagraficaMedia.
    # Garantisce che i dati OMDB già scaricati in passato restino visibili nel form.
    if omdb_data:
        for field in ('anno_produzione', 'durata', 'paesi', 'generi', 'registi', 'attori'):
            if not anagrafica.get(field) and omdb_data.get(field):
                anagrafica[field] = omdb_data[field]

    con.close()
    return anagrafica, logs, omdb_data

def aggiungi_aggiornamento(id_media: str, dati_evento: dict) -> tuple[bool, str]:
    """Aggiunge un evento (log) a un prodotto esistente."""
    con = None
    try:
        con = _conn()
        cur = con.cursor()

        # Se la data non viene fornita, usa quella odierna come fallback.
        from datetime import date
        data_evento = dati_evento.get('data_evento')
        if not data_evento:
            data_evento = date.today().isoformat()

        # Gestione percorso_file: supporta sia array che singolo file
        percorso_file = dati_evento.get('percorso_file')
        if percorso_file is not None:
            if isinstance(percorso_file, list):
                # Array di file: serializza come JSON
                percorso_file = json.dumps(percorso_file) if percorso_file else None
            # Se è già una stringa (compatibilità) la usa così com'è

        # Gestione links: array di URL serializzato come JSON
        links = dati_evento.get('links')
        if links is not None:
            if isinstance(links, list):
                # Normalizza URL (aggiunge https:// se manca)
                normalized_links = []
                for url in links:
                    url = url.strip() if url else ''
                    if url:
                        if not url.startswith(('http://', 'https://')):
                            url = 'https://' + url
                        normalized_links.append(url)
                links = json.dumps(normalized_links) if normalized_links else None
            # Se è già una stringa JSON, la usa così com'è

        cur.execute("""
            INSERT INTO LogAggiornamenti
                (id_media_fk, id_tipo_evento_fk, data_evento, dettaglio_contesto,
                 costo_richiesto_ita, costo_richiesto_spa, note, percorso_file, buyer_contatto,
                 budget_produzione, data_reminder, preferito, venduto_a, privato,
                 delivery, links)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            id_media,
            dati_evento.get('id_tipo_evento_fk'),
            data_evento,
            dati_evento.get('dettaglio_contesto'),
            dati_evento.get('costo_richiesto_ita'),
            dati_evento.get('costo_richiesto_spa'),
            dati_evento.get('note'),
            percorso_file,
            dati_evento.get('buyer_contatto'),
            dati_evento.get('budget_produzione'),
            dati_evento.get('data_reminder'),
            dati_evento.get('preferito', 0),
            dati_evento.get('venduto_a'),
            dati_evento.get('privato', 0),
            dati_evento.get('delivery'),
            links  # NEW: Links field
        ))
        con.commit()

        # Aggiorna campi ereditati in AnagraficaMedia dall'evento più recente
        aggiorna_campi_ereditati(id_media)

        return True, "Evento aggiunto con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in aggiungi_aggiornamento: {e}")
        return False, str(e)
    finally:
        if con: con.close()

def modifica_aggiornamento(id_aggiornamento: int, dati_evento: dict) -> tuple[bool, str]:
    """Modifica un evento (log) esistente."""
    con = None
    try:
        con = _conn()
        cur = con.cursor()

        # Gestione percorso_file: supporta sia array che singolo file
        percorso_file = dati_evento.get('percorso_file')
        if percorso_file is not None:
            if isinstance(percorso_file, list):
                # Array di file: serializza come JSON
                percorso_file = json.dumps(percorso_file) if percorso_file else None
            # Se è già una stringa (compatibilità) la usa così com'è

        # Gestione links: array di URL serializzato come JSON
        links = dati_evento.get('links')
        if links is not None:
            if isinstance(links, list):
                # Normalizza URL (aggiunge https:// se manca)
                normalized_links = []
                for url in links:
                    url = url.strip() if url else ''
                    if url:
                        if not url.startswith(('http://', 'https://')):
                            url = 'https://' + url
                        normalized_links.append(url)
                links = json.dumps(normalized_links) if normalized_links else None
            # Se è già una stringa JSON, la usa così com'è

        query = """
            UPDATE LogAggiornamenti SET
                id_tipo_evento_fk = ?,
                dettaglio_contesto = ?,
                costo_richiesto_ita = ?,
                costo_richiesto_spa = ?,
                note = ?,
                percorso_file = ?,
                budget_produzione = ?,
                data_reminder = ?,
                preferito = ?,
                venduto_a = ?,
                privato = ?,
                delivery = ?,
                links = ?
            WHERE id_aggiornamento = ?
        """
        cur.execute(query, (
            dati_evento.get('id_tipo_evento_fk'),
            dati_evento.get('dettaglio_contesto'),
            dati_evento.get('costo_richiesto_ita'),
            dati_evento.get('costo_richiesto_spa'),
            dati_evento.get('note'),
            percorso_file,
            dati_evento.get('budget_produzione'),
            dati_evento.get('data_reminder'),
            dati_evento.get('preferito', 0),
            dati_evento.get('venduto_a'),
            dati_evento.get('privato', 0),
            dati_evento.get('delivery'),
            links,  # NEW: Links field
            id_aggiornamento
        ))
        con.commit()
        return True, "Modifica salvata con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in modifica_aggiornamento: {e}")
        return False, str(e)
    finally:
        if con: con.close()


def elimina_aggiornamento(id_aggiornamento: int) -> tuple[bool, str]:
    """Elimina un evento (log) dal database."""
    con = None
    try:
        con = _conn()
        cur = con.cursor()
        cur.execute("DELETE FROM LogAggiornamenti WHERE id_aggiornamento = ?", (id_aggiornamento,))
        con.commit()
        # Verifichiamo se una riga è stata effettivamente eliminata
        if cur.rowcount == 0:
            return False, "Nessun evento trovato con questo ID."
        return True, "Evento eliminato con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in elimina_aggiornamento: {e}")
        return False, str(e)
    finally:
        if con: con.close()

def get_tipi_evento() -> list[dict]:
    """Recupera tutti i tipi di evento disponibili con configurazione campi."""
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    cur.execute("""
        SELECT id_tipo_evento, nome_evento, colore_hex, icona, descrizione,
               predefinito, campi_visibili, attivo
        FROM TipiEvento
        WHERE attivo = 1
        ORDER BY predefinito DESC, nome_evento ASC
    """)
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows

# =================================================================
# >>> NUOVA FUNZIONE per modificare l'anagrafica
# =================================================================

def _validate_distributore_in_gemma(con, nome_distributore: str) -> tuple[bool, str]:
    """
    Valida che un distributore esista in Gemma (fonte unica).
    Restituisce (valido, messaggio).
    """
    if not nome_distributore or not nome_distributore.strip():
        return False, "Nome distributore vuoto"

    nome_pulito = nome_distributore.strip()
    nome_lower = nome_pulito.lower().replace("'", "''")

    # Verifica esistenza in gemma.parquet via DuckDB
    gdf = _gemma_df(
        where=f"LOWER(A_DISTRIBUTORE) = '{nome_lower}'",
        columns='A_DISTRIBUTORE'
    )
    if not gdf.empty:
        return True, nome_pulito

    # Fallback: distributori inseriti manualmente prima della limitazione Gemma
    cur = con.cursor()
    cur.execute("""
        SELECT 1 FROM AnagraficaMedia
        WHERE LOWER(distributore_nome) = LOWER(?)
        AND id_media NOT LIKE 'G_%' AND deleted_at IS NULL
        LIMIT 1
    """, (nome_pulito,))

    if cur.fetchone():
        return True, nome_pulito

    return False, f"Il distributore '{nome_pulito}' non esiste in Gemma. Seleziona un distributore valido dalla lista."

def modifica_anagrafica(id_media: str, dati_anagrafica: dict) -> tuple[bool, str]:
    """Modifica i dati anagrafici di un prodotto media. Solo prodotti MT (non Gemma)."""
    if id_media.startswith('G_'):
        return False, "I prodotti Gemma non sono modificabili."

    # I campi editoriali (anno, paesi, cast, generi, durata) vivono in AnagraficaMedia
    # che viene sincronizzata tra client e server. OmdbData è solo per i dati raw
    # dell'API OMDB (imdb_rating, sinossi, titolo_omdb, ecc.) e non entra nel sync.
    _OMDB_FIELDS = set()

    con = None
    try:
        con = _conn()
        cur = con.cursor()

        # --- AnagraficaMedia ---
        cur.execute("PRAGMA table_info(AnagraficaMedia)")
        colonne_am = {row[1] for row in cur.fetchall()}

        campi_am, valori_am = [], []
        campi_omdb, valori_omdb = [], []

        for chiave, valore in dati_anagrafica.items():
            v = valore if valore != '' else None
            if chiave in _OMDB_FIELDS:
                campi_omdb.append(chiave)
                valori_omdb.append(v)
            elif chiave in colonne_am:
                campi_am.append(f"{chiave} = ?")
                if chiave == 'tipo' and v:
                    v = v.strip().upper()
                valori_am.append(v)

        if not campi_am and not campi_omdb:
            return False, "Nessun dato valido da aggiornare."

        if campi_am:
            campi_am += ['updated_at = ?', 'updated_by = ?']
            valori_am += [_now_iso(), _this_machine()]
            query_am = f"UPDATE AnagraficaMedia SET {', '.join(campi_am)} WHERE id_media = ?"
            cur.execute(query_am, tuple(valori_am) + (id_media,))

        # --- OmdbData: upsert sui campi editoriali ---
        if campi_omdb:
            cur.execute("SELECT 1 FROM OmdbData WHERE id_media_fk = ?", (id_media,))
            if cur.fetchone():
                set_clause = ', '.join(f"{c} = ?" for c in campi_omdb)
                cur.execute(
                    f"UPDATE OmdbData SET {set_clause} WHERE id_media_fk = ?",
                    tuple(valori_omdb) + (id_media,)
                )
            else:
                cols = ', '.join(['id_media_fk'] + campi_omdb)
                placeholders = ', '.join(['?'] * (len(campi_omdb) + 1))
                cur.execute(
                    f"INSERT INTO OmdbData ({cols}) VALUES ({placeholders})",
                    (id_media,) + tuple(valori_omdb)
                )

        con.commit()
        return True, "Anagrafica aggiornata con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in modifica_anagrafica: {e}")
        return False, f"Errore del database: {e}"
    finally:
        if con: con.close()


def riconcilia_prodotti(mt_id: str, gemma_id: str) -> tuple[bool, str, int]:
    """
    Sposta tutti gli eventi LogAggiornamenti da un prodotto MT a uno Gemma
    ed elimina il prodotto MT (AnagraficaMedia + OmdbData).

    Vincoli: mt_id non deve iniziare con G_; gemma_id deve iniziare con G_.
    Ritorna (successo, messaggio, n_eventi_spostati).
    """
    if mt_id.startswith('G_'):
        return False, "Il prodotto di origine deve essere un prodotto MediaTrack (non Gemma).", 0
    if not gemma_id.startswith('G_'):
        return False, "Il prodotto di destinazione deve essere un prodotto Gemma (G_).", 0

    con = None
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("SELECT COUNT(*) FROM LogAggiornamenti WHERE id_media_fk = ?", (mt_id,))
        n_eventi = cur.fetchone()[0]

        # Sposta tutti gli eventi sul prodotto Gemma
        cur.execute(
            "UPDATE LogAggiornamenti SET id_media_fk = ? WHERE id_media_fk = ?",
            (gemma_id, mt_id)
        )
        # Rimuovi dati OMDB del prodotto MT
        cur.execute("DELETE FROM OmdbData WHERE id_media_fk = ?", (mt_id,))
        # Soft-delete del record MT (preserved per sync e recovery)
        cur.execute(
            "UPDATE AnagraficaMedia SET deleted_at = datetime('now'), updated_at = datetime('now') WHERE id_media = ?",
            (mt_id,)
        )

        con.commit()
        return True, f"{n_eventi} eventi spostati su {gemma_id}.", n_eventi

    except sqlite3.Error as e:
        if con:
            con.rollback()
        return False, f"Errore database: {e}", 0
    finally:
        if con:
            con.close()


def elimina_prodotto(id_media: str) -> tuple[bool, str, dict]:
    """
    Elimina completamente un prodotto MediaTrack (CASCADE).

    ATTENZIONE: Questa operazione è IRREVERSIBILE!

    Elimina:
    - Anagrafica prodotto (AnagraficaMedia)
    - Tutti gli eventi associati (LogAggiornamenti)
    - Dati OMDb (OmdbData)
    - Aggiorna items carrello orfani (CarrelloImport) impostando prodotto_json a NULL

    Args:
        id_media: ID del prodotto da eliminare

    Returns:
        (successo, messaggio, statistiche_eliminazione)
        statistiche_eliminazione: dict con conteggi di cosa è stato eliminato

    Raises:
        Blocca eliminazione se id_media inizia con 'G_' (prodotti Gemma read-only)
    """
    con = None
    try:
        # BLOCCO: Prodotti Gemma non possono essere eliminati
        if id_media.startswith('G_'):
            return False, "Impossibile eliminare prodotti Gemma (read-only)", {}

        con = _conn()
        cur = con.cursor()

        # Verifica che il prodotto esista
        cur.execute("SELECT id_media, titolo_ufficiale FROM AnagraficaMedia WHERE id_media = ? AND deleted_at IS NULL", (id_media,))
        prodotto = cur.fetchone()

        if not prodotto:
            con.close()
            return False, f"Prodotto {id_media} non trovato", {}

        titolo = prodotto[1] if prodotto[1] else "Senza titolo"

        # Raccolta statistiche PRIMA dell'eliminazione
        stats = {}

        # Conta eventi
        cur.execute("SELECT COUNT(*) FROM LogAggiornamenti WHERE id_media_fk = ?", (id_media,))
        stats['eventi'] = cur.fetchone()[0]

        # Conta dati OMDb
        cur.execute("SELECT COUNT(*) FROM OmdbData WHERE id_media_fk = ?", (id_media,))
        stats['omdb'] = cur.fetchone()[0]

        # Conta items carrello che verranno aggiornati (non eliminati, solo sganciati)
        cur.execute("""
            SELECT COUNT(*) FROM CarrelloImport
            WHERE prodotto_json LIKE ?
        """, (f'%{id_media}%',))
        stats['carrello_items'] = cur.fetchone()[0]

        # 1. Elimina dati OMDb
        cur.execute("DELETE FROM OmdbData WHERE id_media_fk = ?", (id_media,))

        # 2. "Sgancia" items carrello
        cur.execute("""
            UPDATE CarrelloImport
            SET prodotto_json = NULL,
                stato = 'orphaned'
            WHERE prodotto_json LIKE ?
        """, (f'%{id_media}%',))

        # 3. Soft-delete anagrafica (LogAggiornamenti preservati per recovery)
        cur.execute(
            "UPDATE AnagraficaMedia SET deleted_at = datetime('now'), updated_at = datetime('now') WHERE id_media = ?",
            (id_media,)
        )

        con.commit()

        messaggio = f"Prodotto '{titolo}' ({id_media}) eliminato con successo"
        return True, messaggio, stats

    except sqlite3.Error as e:
        if con:
            con.rollback()
        print(f"Errore DB in elimina_prodotto: {e}")
        return False, f"Errore database: {str(e)}", {}
    finally:
        if con:
            con.close()


# =================================================================
# >>> NUOVA FUNZIONE per scaricare dati da OMDB
# =================================================================

OMDB_API_KEY = "123637e0"  # <-- Chiave API inserita

def scarica_dati_omdb(imdb_id: str) -> dict:
    """Scarica i dati da OMDB dato un ID IMDb."""

    if not OMDB_API_KEY or OMDB_API_KEY == "YOUR_OMDB_API_KEY":
        raise ValueError("Chiave API OMDB non configurata. Modifica database_logic.py.")

    url = f"http://www.omdbapi.com/?i={imdb_id}&apikey={OMDB_API_KEY}"
    try:
        response = requests.get(url)
        response.raise_for_status()  # Lancia un'eccezione per codici di stato HTTP != 200
        data = response.json()
        if data.get('Error'):
            raise ValueError(data['Error'])
        return data
    except requests.exceptions.RequestException as e:
        raise Exception(f"Errore nella richiesta a OMDB: {e}")

# =================================================================
# >>> NUOVA FUNZIONE per importare e salvare dati da OMDB
# =================================================================

def importa_e_salva_da_omdb(id_media: str, imdb_id: str, connection: sqlite3.Connection | None = None) -> tuple[bool, str]:
    """
    Orchestra il processo di download da OMDB e salvataggio immediato nel database.
    Se viene fornita una connessione, la usa per operare all'interno di una transazione esistente.
    Altrimenti, ne crea una nuova e la gestisce autonomamente.
    """
    con = None
    try:
        # 1. Scarica i dati da OMDB
        omdb_data = scarica_dati_omdb(imdb_id)

        # Se non ci sono dati validi, esci
        if not omdb_data or omdb_data.get('Response') == 'False':
            return False, f"Nessun dato trovato su OMDB per {imdb_id}."

        # 2. Mappa i dati di OMDB nei nomi delle colonne del nostro database
        #    Questi dati verranno inseriti/aggiornati nella tabella OmdbData
        omdb_data_mapped = {
            'id_media_fk': id_media,
            'codice_imdb': imdb_id,
            'titolo_omdb': omdb_data.get('Title'),
            'sinossi': omdb_data.get('Plot'),
            'anno_produzione': omdb_data.get('Year'),
            'data_rilascio': omdb_data.get('Released'),
            'durata': omdb_data.get('Runtime'),
            'generi': omdb_data.get('Genre'),
            'registi': omdb_data.get('Director'),
            'sceneggiatori': omdb_data.get('Writer'),
            'attori': omdb_data.get('Actors'),
            'lingue': omdb_data.get('Language'),
            'paesi': omdb_data.get('Country'),
            'premi': omdb_data.get('Awards'),
            'url_poster': omdb_data.get('Poster'),
            'metascore': omdb_data.get('Metascore'),
            'imdb_rating': omdb_data.get('imdbRating'),
            'imdb_voti': omdb_data.get('imdbVotes'),
            'box_office': omdb_data.get('BoxOffice'),
            'produzione': omdb_data.get('Production'),
        }

        # 2b. Se tutti i campi chiave sono N/A il titolo non è censito — trattare come not found
        _key_fields = ['imdb_rating', 'registi', 'attori', 'sinossi', 'generi']
        if all(not omdb_data_mapped.get(f) or omdb_data_mapped.get(f) == 'N/A'
               for f in _key_fields):
            return False, "Error getting data"

        # 3. Inserisci o aggiorna i dati nella tabella OmdbData
        # Usa la connessione esistente se fornita, altrimenti ne crea una nuova.
        is_external_transaction = connection is not None
        con = connection if is_external_transaction else _conn()
        cur = con.cursor()

        # Assicura che le colonne necessarie esistano
        _ensure_imdb_flag_column(con)
        _ensure_scaricato_il_column(con)

        # Controlla se esiste già una riga per questo id_media_fk in OmdbData
        cur.execute("SELECT id_media_fk FROM OmdbData WHERE id_media_fk = ?", (id_media,))
        exists = cur.fetchone()

        if exists:
            # Aggiorna i dati esistenti
            campi_da_aggiornare = []
            valori = []
            for chiave, valore in omdb_data_mapped.items():
                if chiave != 'id_media_fk': # Non aggiorniamo la PK
                    campi_da_aggiornare.append(f"{chiave} = ?")
                    valori.append(valore if valore != '' else None)

            campi_da_aggiornare.append("scaricato_il = CURRENT_TIMESTAMP")
            query = f"UPDATE OmdbData SET {', '.join(campi_da_aggiornare)} WHERE id_media_fk = ?"
            valori.append(id_media)
            cur.execute(query, tuple(valori))
        else:
            # Inserisci nuovi dati
            campi = ', '.join(omdb_data_mapped.keys()) + ', scaricato_il'
            placeholders = ', '.join(['?' for _ in omdb_data_mapped.keys()]) + ', CURRENT_TIMESTAMP'
            valori = [v if v != '' else None for v in omdb_data_mapped.values()]

            query = f"INSERT INTO OmdbData ({campi}) VALUES ({placeholders})"
            cur.execute(query, tuple(valori))

        # 4. Aggiorna AnagraficaMedia: codice IMDb + campi editoriali da OMDB
        # (solo se non già valorizzati manualmente — non sovrascrivere dati utente)
        _am_fields = ('anno_produzione', 'durata', 'paesi', 'generi', 'registi', 'attori')
        am_sets = ['codice_imdb = ?', 'imdb_search_status = NULL']
        am_vals = [imdb_id]
        for f in _am_fields:
            v = omdb_data_mapped.get(f)
            if v and v != 'N/A':
                am_sets.append(f"{f} = COALESCE({f}, ?)")
                am_vals.append(v)
        am_vals.append(id_media)
        cur.execute(
            f"UPDATE AnagraficaMedia SET {', '.join(am_sets)} WHERE id_media = ?",
            tuple(am_vals)
        )

        if not is_external_transaction:
            con.commit()
            con.close()
        return True, "Dati OMDB importati e salvati con successo." if not exists else "Dati OMDB aggiornati con successo."

    except Exception as e:
        print(f"Errore in importa_e_salva_da_omdb: {e}")
        return False, str(e)
    finally:
        if con and not is_external_transaction:
            con.close()

# =================================================================
# >>> SEZIONE DISTRIBUTORI
# =================================================================

LINEUP_NESSUN_CONTESTO = '__nessuno__'


def get_all_allegati_paths() -> list[str]:
    """Restituisce tutti i path file allegati presenti in LogAggiornamenti."""
    import json as _json
    con = _conn()
    cur = con.cursor()
    cur.execute("SELECT percorso_file FROM LogAggiornamenti WHERE percorso_file IS NOT NULL AND percorso_file != ''")
    paths = []
    for (pf,) in cur.fetchall():
        try:
            parsed = _json.loads(pf)
            items = parsed if isinstance(parsed, list) else [parsed]
        except Exception:
            items = [pf]
        paths.extend(str(p) for p in items if p)
    con.close()
    return paths


def get_contesti_con_lineup() -> list[dict]:
    """Restituisce i contesti che hanno almeno un evento LINE-UP distributor-level.
    Include una voce speciale per le LINE-UP senza contesto."""
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    cur.execute("""
        SELECT
            COALESCE(L.dettaglio_contesto, '') AS contesto,
            COUNT(*) AS num_lineup
        FROM LogAggiornamenti L
        JOIN TipiEvento TE ON L.id_tipo_evento_fk = TE.id_tipo_evento
        WHERE UPPER(TE.nome_evento) = 'LINE-UP'
          AND L.id_media_fk IS NULL
        GROUP BY COALESCE(L.dettaglio_contesto, '')
        ORDER BY contesto ASC
    """)
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    # Separa il gruppo senza contesto e rinominalo con il sentinel
    result = []
    for r in rows:
        if r['contesto'] == '':
            r['contesto'] = LINEUP_NESSUN_CONTESTO
            r['label'] = '(Senza contesto)'
        else:
            r['label'] = r['contesto']
        result.append(r)
    return result


def get_lineup_by_contesto(contesto: str) -> list[dict]:
    """
    Restituisce tutti gli eventi LINE-UP associati a un contesto.
    Passare LINEUP_NESSUN_CONTESTO ('__nessuno__') per le LINE-UP senza contesto.
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    if contesto == LINEUP_NESSUN_CONTESTO:
        condition = "AND (L.dettaglio_contesto IS NULL OR L.dettaglio_contesto = '')"
        params = ()
    else:
        condition = "AND LOWER(COALESCE(L.dettaglio_contesto, '')) = LOWER(?)"
        params = (contesto,)
    cur.execute(f"""
        SELECT
            COALESCE(D.nome, '') AS distributore,
            L.data_evento,
            L.note,
            L.percorso_file,
            L.id_aggiornamento
        FROM LogAggiornamenti L
        JOIN TipiEvento TE ON L.id_tipo_evento_fk = TE.id_tipo_evento
        LEFT JOIN Distributori D ON L.id_distributore_fk = D.id_distributore
        WHERE UPPER(TE.nome_evento) = 'LINE-UP'
          AND L.id_media_fk IS NULL
          {condition}
        ORDER BY LOWER(COALESCE(D.nome, '')), L.data_evento
    """, params)
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows


def get_distributori_lista(nome: str = '', includi_gemma: bool = False) -> list[dict]:
    """
    Restituisce la lista dei distributori con conteggio prodotti associati.

    Args:
        nome: Filtro per nome distributore
        includi_gemma: Se True, include anche i distributori dalla tabella Gemma

    Note:
        - Con includi_gemma=True e senza filtro nome, limita a 100 risultati
        - Per vedere tutti i distributori Gemma, usare un filtro di ricerca
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    if not includi_gemma:
        # Query semplice solo per MediaTrack (escludi prodotti Gemma importati)
        query = """
            SELECT
                distributore_nome AS nome,
                COUNT(id_media) as num_prodotti,
                'mediatrack' AS fonte
            FROM AnagraficaMedia
            WHERE distributore_nome IS NOT NULL
            AND distributore_nome != ''
            AND id_media NOT LIKE 'G_%'
            AND deleted_at IS NULL
        """

        params = []
        if nome and nome.strip():
            query += " AND LOWER(distributore_nome) LIKE LOWER(?)"
            params.append(f"%{nome.strip()}%")

        query += " GROUP BY distributore_nome ORDER BY distributore_nome ASC"

        cur.execute(query, tuple(params))
        rows = [dict(r) for r in cur.fetchall()]
        con.close()
        return rows

    # Include distributori da MediaTrack E Gemma — split: SQLite + DuckDB, poi merge in Python
    params_mt = []
    query_mt = """
        SELECT
            distributore_nome AS nome,
            COUNT(id_media) as num_prodotti,
            'mediatrack' AS fonte
        FROM AnagraficaMedia
        WHERE distributore_nome IS NOT NULL
        AND distributore_nome != ''
        AND id_media NOT LIKE 'G_%'
        AND deleted_at IS NULL
    """
    if nome and nome.strip():
        query_mt += " AND LOWER(distributore_nome) LIKE LOWER(?)"
        params_mt.append(f"%{nome.strip()}%")
    query_mt += " GROUP BY distributore_nome"

    cur.execute(query_mt, tuple(params_mt))
    mt_rows = [dict(r) for r in cur.fetchall()]
    con.close()

    # Gemma via DuckDB
    where_g = "A_DISTRIBUTORE IS NOT NULL AND A_DISTRIBUTORE != ''"
    if nome and nome.strip():
        nome_esc = nome.strip().lower().replace("'", "''")
        where_g += f" AND LOWER(A_DISTRIBUTORE) LIKE '%{nome_esc}%'"
    gdf = _gemma_df(where=where_g, columns='A_DISTRIBUTORE')

    if not gdf.empty:
        gemma_counts = gdf['A_DISTRIBUTORE'].value_counts().reset_index()
        gemma_counts.columns = ['nome', 'num_prodotti']
        gemma_rows = [{'nome': r['nome'], 'num_prodotti': int(r['num_prodotti']), 'fonte': 'gemma'} for _, r in gemma_counts.iterrows()]
    else:
        gemma_rows = []

    # Merge: raggruppa per nome (case-insensitive)
    merged: dict[str, dict] = {}
    for r in mt_rows:
        key = (r['nome'] or '').lower()
        merged[key] = {'nome': r['nome'], 'num_prodotti': r['num_prodotti'], '_mt': True, '_gemma': False}
    for r in gemma_rows:
        key = (r['nome'] or '').lower()
        if key in merged:
            merged[key]['num_prodotti'] += r['num_prodotti']
            merged[key]['_gemma'] = True
        else:
            merged[key] = {'nome': r['nome'], 'num_prodotti': r['num_prodotti'], '_mt': False, '_gemma': True}

    rows = []
    for v in merged.values():
        if v['_mt'] and v['_gemma']:
            fonte = 'entrambi'
        elif v['_mt']:
            fonte = 'mediatrack'
        else:
            fonte = 'gemma'
        rows.append({'nome': v['nome'], 'num_prodotti': v['num_prodotti'], 'fonte': fonte})

    rows.sort(key=lambda x: (x['nome'] or '').lower())
    if not nome or not nome.strip():
        rows = rows[:100]
    return rows

def get_dettaglio_distributore(id_distributore: int) -> dict | None:
    """Recupera i dettagli completi di un distributore."""
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    cur.execute("""
        SELECT
            id_distributore,
            nome,
            nome_completo,
            paese,
            citta,
            email,
            telefono,
            persona_contatto,
            stato_rapporto,
            ultimo_contatto
        FROM Distributori
        WHERE id_distributore = ?
    """, (id_distributore,))

    row = cur.fetchone()
    con.close()

    return dict(row) if row else None

def get_dettaglio_distributore_gemma(nome_distributore: str) -> dict | None:
    """
    Recupera i dettagli limitati di un distributore dal database Gemma.

    Args:
        nome_distributore: Nome del distributore da cercare

    Returns:
        Dizionario con i dettagli del distributore Gemma o None
    """
    nome_esc = nome_distributore.replace("'", "''")
    gdf = _gemma_df(where=f"A_DISTRIBUTORE = '{nome_esc}'", columns='A_DISTRIBUTORE')
    if gdf.empty:
        return None
    return {
        'nome': nome_distributore,
        'num_prodotti': len(gdf),
        'fonte': 'gemma',
        'id_distributore': None
    }

def get_prodotti_distributore(id_distributore: int) -> list[dict]:
    """Recupera tutti i prodotti associati a un distributore."""
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    query = """
        WITH ultimi AS (
            SELECT
                id_media_fk,
                dettaglio_contesto,
                costo_richiesto_ita,
                costo_richiesto_spa,
                ROW_NUMBER() OVER(
                    PARTITION BY id_media_fk
                    ORDER BY date(COALESCE(data_evento,'0001-01-01')) DESC, id_aggiornamento DESC
                ) rn
            FROM LogAggiornamenti
        )
        SELECT
            A.id_media,
            A.titolo_ufficiale,
            A.tipo,
            O.url_poster,
            O.anno_produzione,
            U.dettaglio_contesto AS contesto,
            U.costo_richiesto_ita,
            U.costo_richiesto_spa
        FROM AnagraficaMedia A
        LEFT JOIN ultimi U ON A.id_media = U.id_media_fk AND U.rn = 1
        LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
        WHERE A.distributore_nome = ?
        AND A.deleted_at IS NULL
        ORDER BY A.titolo_ufficiale ASC
    """

    cur.execute(query, (id_distributore,))
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows

def get_prodotti_distributore_gemma(nome_distributore: str) -> list[dict]:
    """
    Recupera tutti i prodotti associati a un distributore dal database Gemma.

    Args:
        nome_distributore: Nome del distributore

    Returns:
        Lista di prodotti con fonte 'gemma'
    """
    nome_esc = nome_distributore.replace("'", "''")
    gdf = _gemma_df(
        where=f"LOWER(A_DISTRIBUTORE) = LOWER('{nome_esc}')",
        columns='A_ID, A_TITOLO, V_RDA, V_C5, V_I1, V_R4, V_LA5, V_I2, V_IRIS, V_TOP, V_FOC, V_C20, V_CI34, V_C27, A_SUPPORTO, A_DATA'
    )
    if gdf.empty:
        return []

    # Fetch url_poster da OmdbData (SQLite) per i G_ IDs trovati
    id_list = ['G_' + str(a) for a in gdf['A_ID'].tolist()]
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    placeholders = ','.join(['?' for _ in id_list])
    if id_list:
        cur.execute(f"SELECT id_media_fk, url_poster FROM OmdbData WHERE id_media_fk IN ({placeholders})", id_list)
        poster_map = {r['id_media_fk']: r['url_poster'] for r in cur.fetchall()}
    else:
        poster_map = {}
    con.close()

    rows = []
    for _, r in gdf.iterrows():
        id_media = 'G_' + str(r['A_ID'])
        rows.append({
            'id_media': id_media,
            'titolo_ufficiale': r.get('A_TITOLO'),
            'fonte': 'gemma',
            'rda': r.get('V_RDA'),
            'c5': r.get('V_C5'),
            'i1': r.get('V_I1'),
            'r4': r.get('V_R4'),
            'la5': r.get('V_LA5'),
            'i2': r.get('V_I2'),
            'iris': r.get('V_IRIS'),
            'top_crime': r.get('V_TOP'),
            'foc': r.get('V_FOC'),
            'c20': r.get('V_C20'),
            'cine34': r.get('V_CI34'),
            'twent_seven': r.get('V_C27'),
            'supporto': r.get('A_SUPPORTO'),
            'data_valutazione': r.get('A_DATA'),
            'url_poster': poster_map.get(id_media),
        })
    rows.sort(key=lambda x: (x.get('titolo_ufficiale') or '').lower())
    return rows

def get_prodotti_by_distributore_nome(nome_distributore: str) -> list[dict]:
    """
    Recupera tutti i prodotti MediaTrack associati a un distributore per nome.
    Esclude i prodotti Gemma importati (ID che inizia con 'G_').

    Args:
        nome_distributore: Nome del distributore

    Returns:
        Lista di prodotti con fonte 'mediatrack'
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    query = """
        SELECT
            A.id_media,
            A.titolo_ufficiale,
            A.distributore_nome,
            A.tipo,
            'mediatrack' AS fonte,
            A.ultimo_asking_ita,
            A.ultimo_asking_spa,
            O.url_poster
        FROM AnagraficaMedia A
        LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
        WHERE LOWER(A.distributore_nome) = LOWER(?)
        AND A.id_media NOT LIKE 'G_%'
        AND A.deleted_at IS NULL
        ORDER BY A.titolo_ufficiale ASC
    """

    cur.execute(query, (nome_distributore,))
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows

def get_eventi_by_distributore_nome(nome_distributore: str) -> list[dict]:
    """
    Recupera tutti gli eventi di un distributore per nome:
    - eventi sui prodotti del distributore (id_media_fk)
    - eventi a livello distributore (id_distributore_fk)

    Args:
        nome_distributore: Nome del distributore

    Returns:
        Lista di eventi
    """
    con = _conn()
    _ensure_distributori_table(con)
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Recupera id_distributore se esiste
    cur.execute("SELECT id_distributore FROM Distributori WHERE LOWER(nome) = LOWER(?)", (nome_distributore,))
    dist_row = cur.fetchone()
    id_distributore = dist_row['id_distributore'] if dist_row else -1

    query = """
        SELECT
            log.id_aggiornamento,
            log.id_aggiornamento as id_evento,
            log.id_media_fk as id_media,
            log.id_distributore_fk,
            log.id_tipo_evento_fk,
            log.data_evento,
            log.dettaglio_contesto,
            log.costo_richiesto_ita,
            log.costo_richiesto_spa,
            log.note,
            log.percorso_file,
            log.budget_produzione,
            log.data_reminder,
            log.preferito,
            log.venduto_a,
            log.privato,
            log.delivery,
            log.links,
            tipi.nome_evento as tipo_evento_nome,
            tipi.colore_hex,
            am.titolo_ufficiale,
            'prodotto' as tipo_evento
        FROM LogAggiornamenti AS log
        JOIN TipiEvento AS tipi ON log.id_tipo_evento_fk = tipi.id_tipo_evento
        LEFT JOIN AnagraficaMedia AS am ON log.id_media_fk = am.id_media
        WHERE LOWER(am.distributore_nome) = LOWER(?)
          AND log.id_distributore_fk IS NULL

        UNION ALL

        SELECT
            log.id_aggiornamento,
            log.id_aggiornamento as id_evento,
            NULL as id_media,
            log.id_distributore_fk,
            log.id_tipo_evento_fk,
            log.data_evento,
            log.dettaglio_contesto,
            log.costo_richiesto_ita,
            log.costo_richiesto_spa,
            log.note,
            log.percorso_file,
            log.budget_produzione,
            log.data_reminder,
            log.preferito,
            log.venduto_a,
            log.privato,
            log.delivery,
            log.links,
            tipi.nome_evento as tipo_evento_nome,
            tipi.colore_hex,
            NULL as titolo_ufficiale,
            'distributore' as tipo_evento
        FROM LogAggiornamenti AS log
        JOIN TipiEvento AS tipi ON log.id_tipo_evento_fk = tipi.id_tipo_evento
        WHERE log.id_distributore_fk = ?

        ORDER BY data_evento DESC, id_aggiornamento DESC
    """

    cur.execute(query, (nome_distributore, id_distributore))
    rows = []
    for r in cur.fetchall():
        row = dict(r)
        if row.get('percorso_file'):
            try: row['percorso_file'] = json.loads(row['percorso_file'])
            except Exception: pass
        if row.get('links'):
            try: row['links'] = json.loads(row['links'])
            except Exception: pass
        rows.append(row)
    con.close()
    return rows

def get_prodotto_gemma_dettaglio(gemma_id: str) -> dict | None:
    """
    Recupera i dettagli di un singolo prodotto dalla tabella Gemma.

    Args:
        gemma_id: ID del prodotto Gemma (senza prefisso G_)

    Returns:
        Dizionario con i dati del prodotto o None se non trovato
    """
    id_esc = str(gemma_id).replace("'", "''")
    gdf = _gemma_df(
        where=f"A_ID = '{id_esc}'",
        columns=(
            'A_ID, A_TITOLO, A_DISTRIBUTORE, A_TIPOLOGIA, A_AUTORE_REGISTA, A_CAST, '
            'A_COD_IMDB, A_EPISODIO, A_SUPPORTO, A_NOTE_SUPPORTO, A_DEADLINE, '
            'A_NOTE_PUBBLICHE, A_NOTE_PRIVATE, A_DATA, '
            'V_RDA, V_C5, V_I1, V_R4, V_LA5, V_I2, V_IRIS, V_TOP, V_FOC, V_C20, V_CI34, V_C27, '
            'A_CIN, A_INF, A_EMO, A_ENE, A_COM, A_STO, A_CRI, A_ACT, '
            'V_CIN, V_INF, V_EMO, V_ENE, V_COM, V_STO, V_CRI, V_ACT'
        )
    )
    if gdf.empty:
        return None
    r = gdf.iloc[0]
    return {
        'id_media': 'G_' + str(r['A_ID']),
        'titolo_ufficiale': r.get('A_TITOLO'),
        'distributore_nome': r.get('A_DISTRIBUTORE'),
        'tipo': r.get('A_TIPOLOGIA'),
        'tipologia': r.get('A_TIPOLOGIA'),
        'regista': r.get('A_AUTORE_REGISTA'),
        'cast': r.get('A_CAST'),
        'codice_imdb': r.get('A_COD_IMDB'),
        'episodio': r.get('A_EPISODIO'),
        'supporto': r.get('A_SUPPORTO'),
        'note_supporto': r.get('A_NOTE_SUPPORTO'),
        'deadline': r.get('A_DEADLINE'),
        'note_pubbliche': r.get('A_NOTE_PUBBLICHE'),
        'note_private': r.get('A_NOTE_PRIVATE'),
        'data_valutazione': r.get('A_DATA'),
        'rda': r.get('V_RDA'), 'c5': r.get('V_C5'), 'i1': r.get('V_I1'),
        'r4': r.get('V_R4'), 'la5': r.get('V_LA5'), 'i2': r.get('V_I2'),
        'iris': r.get('V_IRIS'), 'top_crime': r.get('V_TOP'), 'foc': r.get('V_FOC'),
        'c20': r.get('V_C20'), 'cine34': r.get('V_CI34'), 'twent_seven': r.get('V_C27'),
        'a_cin': r.get('A_CIN'), 'a_inf': r.get('A_INF'), 'a_emo': r.get('A_EMO'),
        'a_ene': r.get('A_ENE'), 'a_com': r.get('A_COM'), 'a_sto': r.get('A_STO'),
        'a_cri': r.get('A_CRI'), 'a_act': r.get('A_ACT'),
        'v_cin': r.get('V_CIN'), 'v_inf': r.get('V_INF'), 'v_emo': r.get('V_EMO'),
        'v_ene': r.get('V_ENE'), 'v_com': r.get('V_COM'), 'v_sto': r.get('V_STO'),
        'v_cri': r.get('V_CRI'), 'v_act': r.get('V_ACT'),
    }

def get_eventi_distributore(id_distributore: int, include_prodotti: bool = False) -> list[dict]:
    """
    Recupera gli eventi associati a un distributore.

    Args:
        id_distributore: ID del distributore
        include_prodotti: Se True, include anche eventi dei prodotti del distributore

    Returns:
        Lista di eventi con campo 'tipo_evento' = 'distributore' o 'prodotto'
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    if include_prodotti:
        # Eventi diretti del distributore + eventi dei prodotti
        cur.execute("""
            SELECT
                log.id_aggiornamento,
                log.id_media_fk,
                log.id_distributore_fk,
                log.id_tipo_evento_fk,
                log.data_evento,
                log.dettaglio_contesto,
                log.costo_richiesto_ita,
                log.costo_richiesto_spa,
                log.note,
                log.percorso_file,
                log.budget_produzione,
                log.data_reminder,
                log.preferito,
                log.venduto_a,
                log.privato,
                log.delivery,
                log.links,
                tipi.nome_evento,
                tipi.colore_hex,
                A.titolo_ufficiale,
                CASE
                    WHEN log.id_distributore_fk IS NOT NULL THEN 'distributore'
                    ELSE 'prodotto'
                END AS tipo_evento
            FROM LogAggiornamenti AS log
            JOIN TipiEvento AS tipi ON log.id_tipo_evento_fk = tipi.id_tipo_evento
            LEFT JOIN AnagraficaMedia AS A ON log.id_media_fk = A.id_media
            WHERE log.id_distributore_fk = ?
               OR (log.id_media_fk IS NOT NULL AND A.distributore_nome = ?)
            ORDER BY date(COALESCE(log.data_evento,'0001-01-01')) DESC, log.id_aggiornamento DESC
        """, (id_distributore, id_distributore))
    else:
        # Solo eventi diretti del distributore
        cur.execute("""
            SELECT
                log.id_aggiornamento,
                log.id_media_fk,
                log.id_distributore_fk,
                log.id_tipo_evento_fk,
                log.data_evento,
                log.dettaglio_contesto,
                log.costo_richiesto_ita,
                log.costo_richiesto_spa,
                log.note,
                log.percorso_file,
                log.budget_produzione,
                log.data_reminder,
                log.preferito,
                log.venduto_a,
                log.privato,
                log.delivery,
                log.links,
                tipi.nome_evento,
                tipi.colore_hex,
                NULL AS titolo_ufficiale,
                'distributore' AS tipo_evento
            FROM LogAggiornamenti AS log
            JOIN TipiEvento AS tipi ON log.id_tipo_evento_fk = tipi.id_tipo_evento
            WHERE log.id_distributore_fk = ?
            ORDER BY date(COALESCE(log.data_evento,'0001-01-01')) DESC, log.id_aggiornamento DESC
        """, (id_distributore,))

    rows = []
    for r in cur.fetchall():
        row = dict(r)
        if row.get('percorso_file'):
            try: row['percorso_file'] = json.loads(row['percorso_file'])
            except Exception: pass
        if row.get('links'):
            try: row['links'] = json.loads(row['links'])
            except Exception: pass
        rows.append(row)
    con.close()
    return rows

def aggiungi_evento_distributore(id_distributore: int, dati_evento: dict) -> tuple[bool, str]:
    """Aggiunge un evento direttamente a un distributore (non legato a un prodotto specifico)."""
    con = None
    try:
        con = _conn()
        cur = con.cursor()

        # Validazione data
        data_evento = dati_evento.get('data_evento')
        if data_evento == '':
            data_evento = None

        # Gestione percorso_file: array di path serializzato come JSON
        percorso_file = dati_evento.get('percorso_file')
        if isinstance(percorso_file, list):
            percorso_file = json.dumps(percorso_file) if percorso_file else None

        # Gestione links: array di URL serializzato come JSON
        links = dati_evento.get('links')
        if links is not None:
            if isinstance(links, list):
                # Normalizza URL (aggiunge https:// se manca)
                normalized_links = []
                for url in links:
                    url = url.strip() if url else ''
                    if url:
                        if not url.startswith(('http://', 'https://')):
                            url = 'https://' + url
                        normalized_links.append(url)
                links = json.dumps(normalized_links) if normalized_links else None
            # Se è già una stringa JSON, la usa così com'è

        cur.execute("""
            INSERT INTO LogAggiornamenti
                (id_distributore_fk, id_tipo_evento_fk, data_evento, dettaglio_contesto,
                 costo_richiesto_ita, costo_richiesto_spa, note, percorso_file,
                 budget_produzione, data_reminder, preferito, venduto_a, privato, links)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            id_distributore,
            dati_evento.get('id_tipo_evento_fk'),
            data_evento,
            dati_evento.get('dettaglio_contesto'),
            dati_evento.get('costo_richiesto_ita'),
            dati_evento.get('costo_richiesto_spa'),
            dati_evento.get('note'),
            percorso_file,
            dati_evento.get('budget_produzione'),
            dati_evento.get('data_reminder'),
            dati_evento.get('preferito', 0),
            dati_evento.get('venduto_a'),
            dati_evento.get('privato', 0),
            links  # NEW: Links field
        ))
        con.commit()
        return True, "Evento distributore aggiunto con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in aggiungi_evento_distributore: {e}")
        return False, str(e)
    finally:
        if con: con.close()

def modifica_distributore(id_distributore: int, dati: dict) -> tuple[bool, str]:
    """Modifica i dati di un distributore."""
    con = None
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("PRAGMA table_info(Distributori)")
        colonne_valide = {row[1] for row in cur.fetchall()}

        campi_da_aggiornare = []
        valori = []

        for chiave, valore in dati.items():
            if chiave in colonne_valide and chiave != 'id_distributore':
                campi_da_aggiornare.append(f"{chiave} = ?")
                valori.append(valore if valore != '' else None)

        if not campi_da_aggiornare:
            return False, "Nessun dato valido da aggiornare."

        query = f"UPDATE Distributori SET {', '.join(campi_da_aggiornare)} WHERE id_distributore = ?"
        valori.append(id_distributore)

        cur.execute(query, tuple(valori))
        con.commit()
        return True, "Distributore aggiornato con successo."
    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in modifica_distributore: {e}")
        return False, f"Errore del database: {e}"
    finally:
        if con: con.close()
# ==================================================================
# MILESTONE 2: Funzioni per Inserimento Prodotti
# ==================================================================

import time
import re


def _genera_id_media(tipo: str) -> str:
    """
    Genera ID univoco per nuovo prodotto.

    Args:
        tipo: "Film", "Serie", "Miniserie", "Doc"

    Returns:
        str: es. "FILM-1728217845123" o "SERIE-1728217845123"
    """
    timestamp = str(int(time.time() * 1000))  # millisecondi per unicità
    prefisso = tipo.upper() if tipo else "MEDIA"
    return f"{prefisso}-{timestamp}"


def inserisci_prodotto_completo(dati_anagrafica: dict, dati_evento: dict, validate_distributor: bool = True, skip_evento: bool = False) -> tuple[bool, str, str | None]:
    """
    Crea un nuovo prodotto completo (anagrafica + primo evento) in una transazione atomica.

    Args:
        dati_anagrafica: {
            'titolo_ufficiale': str (required),
            'tipo': str (Film/Serie/Miniserie/Doc),
            'distributore_nome': str (opzionale, crea se non esiste),
            'codice_imdb': str (opzionale, formato ttXXXXXXX),
            'budget_produzione': str (opzionale),
            'formato': str (opzionale, solo per Serie, es. "8x45'"),
            'piattaforma': str (opzionale, solo per Serie, es. "Netflix")
        }
        dati_evento: {
            'id_tipo_evento_fk': int (default: 1 = Mercato),
            'data_evento': str (ISO format YYYY-MM-DD),
            'dettaglio_contesto': str (es. "EFM 2025"),
            'costo_richiesto_ita': float (opzionale),
            'costo_richiesto_spa': float (opzionale),
            'note': str (opzionale),
            'buyer_contatto': str (opzionale, es. "Andrea"),
            'percorso_file': str (opzionale),
            'delivery': str (opzionale, per tutti i tipi, es. "Q1 2025")
        }

    Returns:
        tuple[bool, str, str | None]: (successo, messaggio, id_media_creato)

    Note:
        - Genera id_media automaticamente: "FILM-{timestamp}" o "SERIE-{timestamp}"
        - Se distributore_nome non esiste, lo crea automaticamente
        - Gestisce la transazione: rollback completo se errore
        - Popola ultimo_asking_* dal primo evento se presenti
    """
    con = None
    try:
        # Validazioni input
        if not dati_anagrafica.get('titolo_ufficiale'):
            return False, "Titolo obbligatorio mancante", None

        # Validazione codice IMDb (se fornito)
        codice_imdb = dati_anagrafica.get('codice_imdb')
        if codice_imdb and not re.match(r'^tt\d{7,}$', codice_imdb):
            return False, f"Codice IMDb non valido: {codice_imdb}. Formato atteso: ttXXXXXXX", None

        # Validazione data evento (se fornita)
        data_evento = dati_evento.get('data_evento')
        if data_evento:
            try:
                # Verifica formato ISO YYYY-MM-DD
                if not re.match(r'^\d{4}-\d{2}-\d{2}$', data_evento):
                    return False, f"Data evento non valida: {data_evento}. Formato atteso: YYYY-MM-DD", None
            except:
                return False, f"Data evento non valida: {data_evento}", None

        con = _conn()
        cur = con.cursor()

        # Inizia transazione
        cur.execute("BEGIN TRANSACTION")

        # 1. Genera ID univoco
        tipo = (dati_anagrafica.get('tipo') or 'FILM').strip().upper()
        id_media = _genera_id_media(tipo)

        # 2. Valida distributore (opzionale, solo se validate_distributor=True)
        distributore_nome = dati_anagrafica.get('distributore_nome')
        if distributore_nome:
            if validate_distributor:
                # Validazione Gemma: richiesta per insert manuale
                valido, result = _validate_distributore_in_gemma(con, distributore_nome)
                if not valido:
                    cur.execute("ROLLBACK")
                    con.close()
                    return False, result, None
                distributore_nome = result  # Nome pulito
            else:
                # Nessuna validazione: accetta qualsiasi distributore (import Word/esterni)
                distributore_nome = distributore_nome.strip()

        # 3. Prepara ultimo_asking da primo evento
        ultimo_asking_ita = dati_evento.get('costo_richiesto_ita')
        ultimo_asking_spa = dati_evento.get('costo_richiesto_spa')

        # 4. Inserisci anagrafica
        cur.execute("""
            INSERT INTO AnagraficaMedia
                (id_media, titolo_ufficiale, tipo, distributore_nome,
                 codice_imdb, budget_produzione, ultimo_asking_ita, ultimo_asking_spa,
                 formato, piattaforma,
                 anno_produzione, durata, paesi, generi, registi, attori, trama,
                 updated_at, updated_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            id_media,
            dati_anagrafica['titolo_ufficiale'],
            tipo,
            distributore_nome,
            codice_imdb if codice_imdb else None,
            dati_anagrafica.get('budget_produzione'),
            ultimo_asking_ita,
            ultimo_asking_spa,
            dati_anagrafica.get('formato'),
            dati_anagrafica.get('piattaforma'),
            dati_anagrafica.get('anno_produzione'),
            dati_anagrafica.get('durata'),
            dati_anagrafica.get('paesi'),
            dati_anagrafica.get('generi'),
            dati_anagrafica.get('registi'),
            dati_anagrafica.get('attori'),
            dati_anagrafica.get('trama'),
            _now_iso(),
            _this_machine()
        ))

        # 5. Inserisci primo evento (opzionale)
        if not skip_evento:
            if data_evento == '':
                data_evento = None

            percorso_file = dati_evento.get('percorso_file')
            if percorso_file is not None:
                if isinstance(percorso_file, list):
                    percorso_file = json.dumps(percorso_file) if percorso_file else None

            cur.execute("""
                INSERT INTO LogAggiornamenti
                    (id_media_fk, id_tipo_evento_fk, data_evento, dettaglio_contesto,
                     costo_richiesto_ita, costo_richiesto_spa, note, percorso_file, buyer_contatto,
                     delivery)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                id_media,
                dati_evento.get('id_tipo_evento_fk', 1),
                data_evento,
                dati_evento.get('dettaglio_contesto'),
                ultimo_asking_ita,
                ultimo_asking_spa,
                dati_evento.get('note'),
                percorso_file,
                dati_evento.get('buyer_contatto'),
                dati_evento.get('delivery')
            ))

        # Commit transazione
        con.commit()

        # Marca il record come offline se la rete non era disponibile all'inserimento
        if _is_offline():
            con.execute("UPDATE AnagraficaMedia SET is_offline_insert = 1 WHERE id_media = ?", (id_media,))
            con.commit()
            logger.info(f"[offline] Prodotto {id_media} marcato come offline insert")
            _log_offline_insert(id_media, dati_anagrafica, dati_evento)

        print(f"[OK] Prodotto creato con successo: {id_media}")
        return True, f"Prodotto '{dati_anagrafica['titolo_ufficiale']}' creato con successo", id_media

    except sqlite3.IntegrityError as e:
        if con: con.rollback()
        print(f"Errore integrità in inserisci_prodotto_completo: {e}")

        # Messaggio più user-friendly per constraint violation
        if "codice_imdb" in str(e).lower():
            return False, f"Codice IMDb '{codice_imdb}' già esistente nel database", None
        else:
            return False, f"Errore di integrità: {e}", None

    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in inserisci_prodotto_completo: {e}")
        return False, f"Errore database: {e}", None

    except Exception as e:
        if con: con.rollback()
        print(f"Errore inaspettato in inserisci_prodotto_completo: {e}")
        return False, f"Errore inaspettato: {e}", None

    finally:
        if con: con.close()


def aggiorna_campi_ereditati(id_media: str) -> tuple[bool, str]:
    """
    Aggiorna i campi ereditati in AnagraficaMedia dall'evento più recente.

    Args:
        id_media: ID del prodotto da aggiornare

    Returns:
        tuple[bool, str]: (successo, messaggio)

    Logica:
        1. Trova l'evento più recente per questo prodotto (ORDER BY data_evento DESC)
        2. Aggiorna AnagraficaMedia:
           - ultimo_asking_ita = costo_richiesto_ita (se non NULL)
           - ultimo_asking_spa = costo_richiesto_spa (se non NULL)
           - budget_produzione = estratto da note se contiene "bdg" o "budget"

    Pattern estrazione budget dalle note:
        - "bdg 7mln" -> "7mln"
        - "Bdg 35mln" -> "35mln"
        - "budget 12.5 mln" -> "12.5 mln"
        - Regex case-insensitive: r'b(?:dg|udget)\\s*([0-9.,]+\\s*mln)'
    """
    con = None
    try:
        con = _conn()
        cur = con.cursor()

        # Trova l'evento più recente per questo prodotto
        cur.execute("""
            SELECT costo_richiesto_ita, costo_richiesto_spa, note
            FROM LogAggiornamenti
            WHERE id_media_fk = ?
            ORDER BY
                CASE
                    WHEN data_evento IS NULL THEN 1
                    ELSE 0
                END,
                date(COALESCE(data_evento, '0001-01-01')) DESC,
                id_aggiornamento DESC
            LIMIT 1
        """, (id_media,))

        evento = cur.fetchone()

        if not evento:
            return False, "Nessun evento trovato per questo prodotto"

        costo_ita, costo_spa, note = evento

        # Estrai budget dalle note (se presente)
        budget = None
        if note:
            # Pattern per budget: "bdg X" o "budget X" (case-insensitive)
            match = re.search(r'b(?:dg|udget)\s*([0-9.,]+\s*mln)', note, re.IGNORECASE)
            if match:
                budget = match.group(1)

        # Aggiorna AnagraficaMedia
        cur.execute("""
            UPDATE AnagraficaMedia
            SET
                ultimo_asking_ita = COALESCE(?, ultimo_asking_ita),
                ultimo_asking_spa = COALESCE(?, ultimo_asking_spa),
                budget_produzione = COALESCE(?, budget_produzione),
                updated_at = ?,
                updated_by = ?
            WHERE id_media = ?
        """, (costo_ita, costo_spa, budget, _now_iso(), _this_machine(), id_media))

        con.commit()

        # Costruisci messaggio informativo
        aggiornamenti = []
        if costo_ita is not None:
            try:
                aggiornamenti.append(f"ITA: €{float(costo_ita):,.0f}")
            except (ValueError, TypeError):
                pass
        if costo_spa is not None:
            try:
                aggiornamenti.append(f"SPA: €{float(costo_spa):,.0f}")
            except (ValueError, TypeError):
                pass
        if budget:
            aggiornamenti.append(f"Budget: {budget}")

        msg = f"Campi ereditati aggiornati: {', '.join(aggiornamenti)}" if aggiornamenti else "Nessun campo da aggiornare"
        return True, msg

    except sqlite3.Error as e:
        if con: con.rollback()
        print(f"Errore DB in aggiorna_campi_ereditati: {e}")
        return False, str(e)
    finally:
        if con: con.close()


def cerca_prodotto_per_matching(titolo: str = None, distributore: str = None,
                                codice_imdb: str = None, includi_gemma: bool = True) -> list[dict]:
    """
    Cerca prodotti esistenti per identificare potenziali duplicati.
    Include ricerca sia in AnagraficaMedia che in Gemma.

    Args:
        titolo: Titolo da cercare (match parziale case-insensitive)
        distributore: Nome distributore (match parziale)
        codice_imdb: Codice IMDb (match esatto)
        includi_gemma: Se True, include anche ricerca nella tabella Gemma

    Returns:
        Lista di prodotti con campi: id_media, titolo_ufficiale, tipo,
        distributore_nome, codice_imdb, ultimo_asking_ita, ultimo_asking_spa, fonte

    Logica matching (in ordine di priorità):
        1. Se codice_imdb fornito: match esatto su codice_imdb (entrambe le tabelle)
        2. Se no IMDb: match parziale su titolo + distributore (entrambe le tabelle)
        3. Ordinati per similarità

    Note:
        - Utile per UI di verifica duplicati prima di insert
        - Restituisce max 20 risultati più simili (10 per tabella)
        - Campo 'fonte' indica se il record proviene da 'MediaTrack' o 'Gemma'
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    results = []

    # Priorità 1: Match esatto su codice IMDb
    if codice_imdb:
        # Cerca in AnagraficaMedia
        cur.execute("""
            SELECT
                A.id_media,
                A.titolo_ufficiale,
                A.tipo,
                A.distributore_nome AS distributore_nome,
                A.codice_imdb,
                A.ultimo_asking_ita,
                A.ultimo_asking_spa,
                'MediaTrack' AS fonte
            FROM AnagraficaMedia A
            WHERE A.codice_imdb = ? AND A.deleted_at IS NULL
            LIMIT 10
        """, (codice_imdb,))
        results.extend([dict(r) for r in cur.fetchall()])

        # Cerca in Gemma (se abilitato)
        if includi_gemma:
            imdb_esc = str(codice_imdb).replace("'", "''")
            gdf = _gemma_df(
                where=f"A_COD_IMDB = '{imdb_esc}'",
                columns='A_ID, A_TITOLO, A_TIPOLOGIA, A_DISTRIBUTORE, A_COD_IMDB'
            )
            if not gdf.empty:
                for _, r in gdf.head(10).iterrows():
                    results.append({
                        'id_media': 'G_' + str(r['A_ID']),
                        'titolo_ufficiale': r.get('A_TITOLO'),
                        'tipo': r.get('A_TIPOLOGIA'),
                        'distributore_nome': r.get('A_DISTRIBUTORE'),
                        'codice_imdb': r.get('A_COD_IMDB'),
                        'ultimo_asking_ita': None,
                        'ultimo_asking_spa': None,
                        'fonte': 'Gemma',
                    })

        con.close()
        return results

    # Priorità 2: Match parziale su titolo e/o distributore
    params = []
    where_conditions = []

    if titolo:
        where_conditions.append("LOWER(titolo_ufficiale) LIKE LOWER(?)")
        params.append(f"%{titolo}%")

    if distributore:
        where_conditions.append("LOWER(A.distributore_nome) LIKE LOWER(?)")
        params.append(f"%{distributore}%")

    # Se non ci sono filtri, restituisci lista vuota
    if not where_conditions:
        con.close()
        return []

    where_clause = " AND ".join(where_conditions)

    # Cerca in AnagraficaMedia
    query_anagrafica = f"""
        SELECT
            A.id_media,
            A.titolo_ufficiale,
            A.tipo,
            A.distributore_nome AS distributore_nome,
            A.codice_imdb,
            A.ultimo_asking_ita,
            A.ultimo_asking_spa,
            'MediaTrack' AS fonte
        FROM AnagraficaMedia A
        WHERE {where_clause} AND A.deleted_at IS NULL
        ORDER BY A.titolo_ufficiale ASC
        LIMIT 10
    """
    cur.execute(query_anagrafica, tuple(params))
    results.extend([dict(r) for r in cur.fetchall()])

    # Cerca in Gemma (se abilitato)
    if includi_gemma:
        gemma_where_parts = []
        if titolo:
            t_esc = titolo.replace("'", "''")
            gemma_where_parts.append(f"LOWER(A_TITOLO) LIKE LOWER('%{t_esc}%')")
        if distributore:
            d_esc = distributore.replace("'", "''")
            gemma_where_parts.append(f"LOWER(A_DISTRIBUTORE) LIKE LOWER('%{d_esc}%')")

        if gemma_where_parts:
            gdf = _gemma_df(
                where=' AND '.join(gemma_where_parts),
                columns='A_ID, A_TITOLO, A_TIPOLOGIA, A_DISTRIBUTORE, A_COD_IMDB'
            )
            if not gdf.empty:
                gdf = gdf.sort_values('A_TITOLO').head(10)
                for _, r in gdf.iterrows():
                    results.append({
                        'id_media': 'G_' + str(r['A_ID']),
                        'titolo_ufficiale': r.get('A_TITOLO'),
                        'tipo': r.get('A_TIPOLOGIA'),
                        'distributore_nome': r.get('A_DISTRIBUTORE'),
                        'codice_imdb': r.get('A_COD_IMDB'),
                        'ultimo_asking_ita': None,
                        'ultimo_asking_spa': None,
                        'fonte': 'Gemma',
                    })

    con.close()
    return results


def get_buyers_unici() -> list[str]:
    """
    Recupera lista ordinata di buyer unici da LogAggiornamenti.

    Returns:
        Lista di nomi buyer (es. ["Andrea", "Clemente", "Mercedes"])

    Query:
        SELECT DISTINCT buyer_contatto
        FROM LogAggiornamenti
        WHERE buyer_contatto IS NOT NULL
        AND TRIM(buyer_contatto) != ''
        ORDER BY buyer_contatto ASC
    """
    con = _conn()
    cur = con.cursor()

    cur.execute("""
        SELECT DISTINCT buyer_contatto
        FROM LogAggiornamenti
        WHERE buyer_contatto IS NOT NULL
        AND TRIM(buyer_contatto) != ''
        ORDER BY buyer_contatto ASC
    """)

    buyers = [row[0] for row in cur.fetchall()]
    con.close()
    return buyers

# ==================================================================
# MILESTONE 3: Funzioni per Manutenzione
# ==================================================================

def cerca_imdb_per_titolo(titolo: str, anno: str | None = None, tipo: str | None = None) -> list[dict] | None:
    """
    Cerca un film su OMDB per titolo e (opzionalmente) anno.
    Usa l'endpoint di ricerca 's' di OMDB.

    Args:
        titolo: Il titolo del film da cercare.
        anno: L'anno di produzione (opzionale).
        tipo: Il tipo di media (es. 'movie', 'series').

    Returns:
        Una lista di risultati di ricerca, o None se non trovato.
    """
    if not OMDB_API_KEY or OMDB_API_KEY == "YOUR_OMDB_API_KEY":
        raise ValueError("Chiave API OMDB non configurata.")

    params = {'s': titolo, 'apikey': OMDB_API_KEY}
    if anno:
        params['y'] = anno
    if tipo:
        params['type'] = tipo

    try:
        response = requests.get("http://www.omdbapi.com/", params=params)
        response.raise_for_status()
        data = response.json()

        if data.get('Response') == 'True' and 'Search' in data:
            # Restituisce l'intera lista di risultati
            return data['Search']
        return None
    except requests.exceptions.RequestException as e:
        print(f"Errore nella richiesta a OMDB (ricerca per titolo): {e}")
        return None


def associa_imdb_mancanti(dry_run: bool = True, years_limit: int | None = None) -> list[dict]:
    """
    DEPRECATA: Funzione di associazione automatica IMDb.
    Sostituita da IMDb Linker Manuale (più preciso e controllabile).

    Mantenuta per retrocompatibilità ma non più utilizzata nell'interfaccia.

    Args:
        dry_run: Se True, non esegue l'aggiornamento ma restituisce solo cosa farebbe.
        years_limit: Se fornito, limita la ricerca ai prodotti degli ultimi N anni.

    Returns:
        Una lista di dizionari, ognuno rappresentante un'azione (trovato, non trovato, errore).
    """
    import datetime

    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # La query ora seleziona:
    # 1. Prodotti senza codice IMDb.
    # 2. Prodotti con codice IMDb ma senza dati corrispondenti in OmdbData.
    query = """
        SELECT A.id_media, A.titolo_ufficiale, A.codice_imdb
        FROM AnagraficaMedia A
        LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
        WHERE 
            (A.codice_imdb IS NULL OR TRIM(A.codice_imdb) = '')
            OR (A.codice_imdb IS NOT NULL AND TRIM(A.codice_imdb) != '' AND O.id_media_fk IS NULL)
    """
    cur.execute(query)
    prodotti_da_controllare = [dict(r) for r in cur.fetchall()]

    report = []
    start_year = None
    if years_limit is not None and years_limit > 0:
        current_year = datetime.date.today().year
        start_year = current_year - years_limit + 1

    for prodotto in prodotti_da_controllare:
        risultato_omdb = None
        imdb_id_preesistente = prodotto.get('codice_imdb')

        if imdb_id_preesistente:
            # Caso 2: Il codice IMDb esiste, scarichiamo i dati completi per la verifica.
            try:
                risultato_omdb = scarica_dati_omdb(imdb_id_preesistente)
            except Exception as e:
                report.append({'status': 'NON_TROVATO', 'prodotto': prodotto, 'reason': f"Errore download per {imdb_id_preesistente}: {e}"})
        else:
            # Caso 1: Cerchiamo su OMDB usando SOLO il titolo.
            risultati_omdb = cerca_imdb_per_titolo(prodotto['titolo_ufficiale'], tipo='movie') # Nello script automatico cerchiamo solo film
            # Per la scansione automatica, usiamo solo il primo risultato (il più probabile)
            risultato_omdb = risultati_omdb[0] if risultati_omdb else None

        if risultato_omdb and 'imdbID' in risultato_omdb:
            imdb_id_trovato = risultato_omdb['imdbID']
            # Se abbiamo un limite di anni, applichiamo il filtro QUI, sul risultato di OMDB.
            if start_year:
                try:
                    # L'anno da OMDB può essere un range (es. "2011-2015"), prendiamo solo l'inizio.
                    anno_omdb_str = risultato_omdb.get('Year', '0').split('–')[0].strip()
                    anno_omdb = int(anno_omdb_str)

                    if anno_omdb >= start_year:
                        # L'anno è nel range corretto, procediamo.
                        report.append({'status': 'TROVATO', 'prodotto': prodotto, 'match_omdb': risultato_omdb})
                        if not dry_run:
                            # Aggiorna il codice IMDb in AnagraficaMedia (se non c'era già)
                            cur.execute("UPDATE AnagraficaMedia SET codice_imdb = ? WHERE id_media = ?",
                                        (imdb_id_trovato, prodotto['id_media']))
                            # Scarica e salva i dati completi da OMDB
                            importa_e_salva_da_omdb(prodotto['id_media'], imdb_id_trovato, connection=con)
                    else:
                        # Trovato, ma l'anno è troppo vecchio, quindi lo scartiamo.
                        report.append({'status': 'NON_TROVATO', 'prodotto': prodotto, 'reason': f"Trovato '{risultato_omdb.get('Title')}' ma l'anno ({anno_omdb}) è precedente al {start_year}."})
                except (ValueError, TypeError):
                    report.append({'status': 'NON_TROVATO', 'prodotto': prodotto, 'reason': f"Trovato '{risultato_omdb.get('Title')}' ma l'anno '{risultato_omdb.get('Year')}' non è valido."})
            else:
                # Nessun limite di anni, quindi qualsiasi risultato va bene.
                report.append({'status': 'TROVATO', 'prodotto': prodotto, 'match_omdb': risultato_omdb})
                if not dry_run:
                    # Aggiorna il codice IMDb in AnagraficaMedia (se non c'era già)
                    cur.execute("UPDATE AnagraficaMedia SET codice_imdb = ? WHERE id_media = ?",
                                (imdb_id_trovato, prodotto['id_media']))
                    # Scarica e salva i dati completi da OMDB
                    importa_e_salva_da_omdb(prodotto['id_media'], imdb_id_trovato, connection=con)
        else:
            # Se non abbiamo trovato un risultato e non c'era un errore precedente, aggiungiamo un report generico.
            if not any(r['prodotto']['id_media'] == prodotto['id_media'] for r in report):
                report.append({'status': 'NON_TROVATO', 'prodotto': prodotto})

    con.commit()
    con.close()
    return report

def get_prodotti_senza_imdb() -> list[dict]:
    """
    Restituisce una lista di prodotti in AnagraficaMedia che non hanno un codice IMDb.
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Assicura che il campo di flag sia presente per poterlo usare nel filtro
    _ensure_imdb_flag_column(con)

    # Include:
    # - Prodotti con status NULL (mai controllati)
    # - Prodotti con status 1 (match trovato ma non ancora associato)
    # - Prodotti con status -1 MA controllati più di 30 giorni fa (da ricontrollare)
    # ESCLUDI prodotti Gemma (id_media che inizia con 'G_')
    cur.execute("""
        SELECT A.id_media, A.titolo_ufficiale, A.distributore_nome as distributore_nome
        FROM AnagraficaMedia A
                WHERE
            A.id_media NOT LIKE 'G_%'
            AND (A.codice_imdb IS NULL OR TRIM(A.codice_imdb) = '')
            AND (
                A.imdb_search_status IS NULL
                OR A.imdb_search_status = 1
                OR (
                    A.imdb_search_status = -1
                    AND (
                        A.data_ultimo_controllo_imdb IS NULL
                        OR A.data_ultimo_controllo_imdb < date('now', '-30 day')
                    )
                )
            )
        ORDER BY A.titolo_ufficiale ASC
    """)
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows

def get_prodotti_da_arricchire(limit: int = 500, distributor: str = None) -> list[dict]:
    """
    Restituisce i prodotti che hanno codice_imdb ma NON hanno ancora dati in OmdbData,
    oppure hanno uno stub (solo id_media_fk + codice_imdb, titolo_omdb NULL) scaricato
    più di 30 giorni fa — così i titoli non ancora su OMDB vengono ritentati periodicamente.
    Usato dal batch enrichment schedulato da Frank.
    Include sia prodotti MediaTrack (MT_) che Gemma (G_).
    Se distributor è specificato, filtra per nome distributore (LIKE).
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    _ensure_scaricato_il_column(con)

    base_where = """
        A.codice_imdb IS NOT NULL
        AND TRIM(A.codice_imdb) != ''
        AND (
            O.id_media_fk IS NULL
            OR (O.titolo_omdb IS NULL AND (O.scaricato_il IS NULL OR O.scaricato_il < datetime('now', '-30 day')))
        )
    """

    if distributor:
        cur.execute(f"""
            SELECT A.id_media, A.titolo_ufficiale, A.codice_imdb
            FROM AnagraficaMedia A
            LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
            WHERE {base_where}
              AND A.distributore_nome LIKE ?
            ORDER BY A.titolo_ufficiale ASC
            LIMIT ?
        """, (f"%{distributor}%", limit))
    else:
        cur.execute(f"""
            SELECT A.id_media, A.titolo_ufficiale, A.codice_imdb
            FROM AnagraficaMedia A
            LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
            WHERE {base_where}
            ORDER BY A.id_media LIKE 'G_%' ASC, A.titolo_ufficiale ASC
            LIMIT ?
        """, (limit,))
    rows = [dict(r) for r in cur.fetchall()]
    con.close()
    return rows


def count_prodotti_da_arricchire() -> int:
    """Conta i prodotti con codice_imdb ma senza dati OmdbData (coda rimanente).
    Include anche stub 'error getting data' più vecchi di 30 giorni (da ritentare)."""
    con = _conn()
    cur = con.cursor()
    _ensure_scaricato_il_column(con)
    cur.execute("""
        SELECT COUNT(*)
        FROM AnagraficaMedia A
        LEFT JOIN OmdbData O ON A.id_media = O.id_media_fk
        WHERE A.codice_imdb IS NOT NULL
          AND TRIM(A.codice_imdb) != ''
          AND (
              O.id_media_fk IS NULL
              OR (O.titolo_omdb IS NULL AND (O.scaricato_il IS NULL OR O.scaricato_il < datetime('now', '-30 day')))
          )
    """)
    count = cur.fetchone()[0]
    con.close()
    return count


def get_poster_url(id_media: str) -> str | None:
    """Legge url_poster da OmdbData per un dato id_media."""
    con = _conn()
    cur = con.cursor()
    cur.execute("SELECT url_poster FROM OmdbData WHERE id_media_fk = ?", (id_media,))
    row = cur.fetchone()
    con.close()
    return row[0] if row and row[0] and row[0] != 'N/A' else None


def get_poster_url_by_imdb(codice_imdb: str) -> str | None:
    """Legge url_poster da OmdbData per un dato codice_imdb (fallback per poster non ancora scaricati)."""
    con = _conn()
    cur = con.cursor()
    cur.execute("SELECT url_poster FROM OmdbData WHERE codice_imdb = ? LIMIT 1", (codice_imdb,))
    row = cur.fetchone()
    con.close()
    return row[0] if row and row[0] and row[0] != 'N/A' else None


def mark_omdb_not_found(id_media: str, codice_imdb: str) -> None:
    """
    Inserisce un record stub in OmdbData con solo l'id_media_fk e codice_imdb.
    Serve a escludere il prodotto dalla coda del batch enrichment
    quando OMDB non trova il titolo (codice non valido o non ancora disponibile).
    """
    con = _conn()
    cur = con.cursor()
    _ensure_scaricato_il_column(con)
    cur.execute("SELECT id_media_fk FROM OmdbData WHERE id_media_fk = ?", (id_media,))
    if not cur.fetchone():
        cur.execute(
            "INSERT INTO OmdbData (id_media_fk, codice_imdb, scaricato_il) VALUES (?, ?, CURRENT_TIMESTAMP)",
            (id_media, codice_imdb)
        )
        con.commit()
    con.close()


def purge_omdb_stubs() -> int:
    """
    Elimina i record OmdbData che sono stub vuoti (tutti i campi chiave NULL o N/A).
    Restituisce il numero di record eliminati.
    """
    con = _conn()
    cur = con.cursor()
    cur.execute("""
        DELETE FROM OmdbData
        WHERE (imdb_rating IS NULL OR imdb_rating IN ('N/A', 'N/D', ''))
          AND (registi    IS NULL OR registi    IN ('N/A', 'N/D', ''))
          AND (attori     IS NULL OR attori     IN ('N/A', 'N/D', ''))
          AND (sinossi    IS NULL OR sinossi    IN ('N/A', 'N/D', ''))
          AND (generi     IS NULL OR generi     IN ('N/A', 'N/D', ''))
    """)
    deleted = cur.rowcount
    con.commit()
    con.close()
    return deleted


## scansione_preliminare_imdb rimossa: la potatura OMDb avviene lato client.
def marca_imdb_non_trovabile(id_media: str) -> tuple[bool, str]:
    """
    Imposta lo status a -1 (nessun match trovato) per un prodotto specifico
    e salva la data del controllo per permettere di riproporre dopo 30 giorni.

    Args:
        id_media: Identificativo del prodotto.

    Returns:
        (successo, messaggio)
    """
    import datetime
    con = _conn()
    cur = con.cursor()

    # Assicura la presenza della colonna
    _ensure_imdb_flag_column(con)

    oggi = datetime.date.today().isoformat()
    cur.execute("""
        UPDATE AnagraficaMedia
        SET imdb_search_status = -1, data_ultimo_controllo_imdb = ?
        WHERE id_media = ?
    """, (oggi, id_media))
    con.commit()
    updated = cur.rowcount
    con.close()

    if updated == 0:
        return False, f"Nessun prodotto aggiornato per id_media={id_media}"
    return True, "Prodotto marcato come non trovabile su OMDB"


def marca_imdb_match_trovato(id_media: str) -> tuple[bool, str]:
    """
    Imposta lo status a 1 (match trovato) per un prodotto specifico
    e resetta la data di ultimo controllo.

    Args:
        id_media: Identificativo del prodotto.

    Returns:
        (successo, messaggio)
    """
    con = _conn()
    cur = con.cursor()

    # Assicura la presenza della colonna
    _ensure_imdb_flag_column(con)

    cur.execute("""
        UPDATE AnagraficaMedia
        SET imdb_search_status = 1, data_ultimo_controllo_imdb = NULL
        WHERE id_media = ?
    """, (id_media,))
    con.commit()
    updated = cur.rowcount
    con.close()

    if updated == 0:
        return False, f"Nessun prodotto aggiornato per id_media={id_media}"
    return True, "Prodotto marcato come trovabile su OMDB"


def importa_da_gemma() -> dict:
    """
    Sincronizza i prodotti dalla tabella Gemma nella tabella AnagraficaMedia.
    PRIMA cancella tutti i prodotti Gemma già importati, poi importa tutti i prodotti da Gemma.

    Versione migliorata con:
    - Timeout aumentato a 60s per operazioni lunghe
    - Retry automatico (3 tentativi) per gestire lock temporanei
    - Commit in batch (ogni 1000 record) per ridurre tempo di lock
    - Gestione errori migliorata con messaggi chiari

    Returns:
        dict: {
            'success': bool,
            'imported': int,
            'deleted': int,
            'errors': int,
            'message': str
        }
    """
    max_retries = 3
    retry_delay = 2  # secondi tra i tentativi
    batch_size = 1000  # commit ogni 1000 record per ridurre lock time

    for attempt in range(max_retries):
        con = None
        try:
            # Connessione diretta con timeout aumentato (60s invece di 30s)
            print(f"[Import Gemma] Tentativo {attempt + 1}/{max_retries}...")
            con = sqlite3.connect(NOME_DATABASE, timeout=60.0)
            con.execute("PRAGMA foreign_keys = 1")
            try:
                con.execute("PRAGMA journal_mode = WAL")
            except Exception:
                pass  # WAL non supportato su SMB
            con.execute("PRAGMA synchronous = NORMAL")

            cur = con.cursor()

            # PASSO 1: Cancella tutti i prodotti Gemma già importati in AnagraficaMedia
            # I prodotti Gemma hanno id_media che inizia con 'G_'
            print("==> Cancellazione prodotti Gemma già importati da AnagraficaMedia...")
            cur.execute("DELETE FROM AnagraficaMedia WHERE id_media LIKE 'G_%'")
            deleted_count = cur.rowcount
            con.commit()
            print(f"==> Cancellati {deleted_count} prodotti Gemma da AnagraficaMedia")

            # PASSO 2: Leggi tutti i prodotti da Gemma per reimportarli (via DuckDB)
            gdf = _gemma_df(columns='A_ID, A_TITOLO, A_TIPO, A_TIPOLOGIA, A_DISTRIBUTORE, A_COD_IMDB')

            if gdf.empty:
                con.close()
                return {
                    'success': True,
                    'imported': 0,
                    'deleted': deleted_count,
                    'errors': 0,
                    'message': f'Cancellati {deleted_count} prodotti, ma nessun prodotto trovato in Gemma per reimportare'
                }

            imported = 0
            errors = 0
            total_products = len(gdf)
            print(f"==> Importazione di {total_products} prodotti da Gemma...")

            # PASSO 3: Importa tutti i prodotti da Gemma con commit in batch
            for idx, (_, row) in enumerate(gdf.iterrows(), start=1):
                try:
                    id_gemma = str(row['A_ID'])
                    titolo = row.get('A_TITOLO')
                    # A_TIPO è la colonna tipo originale; A_TIPOLOGIA è il campo normalizzato
                    tipo_raw = row.get('A_TIPO') or row.get('A_TIPOLOGIA') or 'FILM'
                    tipo = str(tipo_raw).upper() if tipo_raw else 'FILM'
                    distributore = row.get('A_DISTRIBUTORE')
                    codice_imdb = row.get('A_COD_IMDB')

                    cur.execute("""
                        INSERT OR IGNORE INTO AnagraficaMedia (id_media, titolo_ufficiale, tipo, distributore_nome, codice_imdb)
                        VALUES (?, ?, ?, ?, ?)
                    """, ('G_' + id_gemma, titolo, tipo, distributore, codice_imdb))

                    imported += 1

                    # Commit in batch per ridurre tempo di lock del database
                    if idx % batch_size == 0:
                        con.commit()
                        print(f"==> Progress: {idx}/{total_products} prodotti importati ({(idx/total_products)*100:.1f}%)")

                except Exception as e:
                    print(f"Errore nell'importazione di {row.get('A_ID', 'N/D')}: {e}")
                    errors += 1

            # Commit finale per i record rimanenti
            con.commit()
            print(f"==> Import completato: {imported} prodotti importati, {errors} errori")

            con.close()
            return {
                'success': True,
                'imported': imported,
                'deleted': deleted_count,
                'errors': errors,
                'message': f'Import completato: {deleted_count} cancellati, {imported} importati, {errors} errori'
            }

        except sqlite3.OperationalError as e:
            # Gestione specifica per errori di database lock
            if con:
                try:
                    con.rollback()
                    con.close()
                except:
                    pass

            error_msg = str(e).lower()
            if "database is locked" in error_msg or "locked" in error_msg:
                if attempt < max_retries - 1:
                    print(f"[WARN] Database bloccato (tentativo {attempt + 1}/{max_retries}), riprovo tra {retry_delay}s...")
                    import time
                    time.sleep(retry_delay)
                    continue  # Ritenta
                else:
                    # Ultimo tentativo fallito
                    return {
                        'success': False,
                        'imported': 0,
                        'deleted': 0,
                        'errors': 0,
                        'message': (
                            f'Database bloccato dopo {max_retries} tentativi. '
                            'Assicurati che MediaTrack non sia in uso da altri utenti o processi. '
                            'Chiudi MediaTrack e riprova.'
                        )
                    }
            else:
                # Altro tipo di errore operazionale
                print(f"[ERROR] Errore operazionale database: {e}")
                return {
                    'success': False,
                    'imported': 0,
                    'deleted': 0,
                    'errors': 0,
                    'message': f'Errore database: {str(e)}'
                }

        except Exception as e:
            # Gestione errori generici
            if con:
                try:
                    con.rollback()
                    con.close()
                except:
                    pass

            print(f"[ERROR] Errore generale nell'import da Gemma: {e}")

            if attempt < max_retries - 1:
                print(f"[WARN] Riprovo tra {retry_delay}s...")
                import time
                time.sleep(retry_delay)
                continue
            else:
                return {
                    'success': False,
                    'imported': 0,
                    'deleted': 0,
                    'errors': 0,
                    'message': f'Errore durante import: {str(e)}'
                }

    # Fallback (non dovrebbe mai arrivare qui)
    return {
        'success': False,
        'imported': 0,
        'deleted': 0,
        'errors': 0,
        'message': 'Errore imprevisto: massimo numero di tentativi raggiunto'
    }


# ==================================================================
# MILESTONE 4: Funzioni per Sessioni Import e Carrello
# ==================================================================


def _ensure_distributori_table(con: sqlite3.Connection) -> None:
    """Crea la tabella Distributori se non esiste (migration safe)."""
    try:
        cur = con.cursor()
        cur.execute("""
            CREATE TABLE IF NOT EXISTS Distributori (
                id_distributore INTEGER PRIMARY KEY AUTOINCREMENT,
                nome TEXT NOT NULL UNIQUE,
                nome_completo TEXT,
                paese TEXT,
                sito_web TEXT,
                email TEXT,
                telefono TEXT,
                persona_contatto TEXT,
                stato_rapporto TEXT,
                ultimo_contatto TEXT,
                note TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        con.commit()
    except sqlite3.Error:
        pass


def _ensure_contesti_table(con: sqlite3.Connection) -> None:
    """Crea la tabella Contesti se non esiste e pre-popola con i valori noti."""
    try:
        cur = con.cursor()
        cur.execute("""
            CREATE TABLE IF NOT EXISTS Contesti (
                id_contesto   INTEGER PRIMARY KEY AUTOINCREMENT,
                nome          TEXT NOT NULL UNIQUE,
                gemma_supporto TEXT,
                attivo        INTEGER DEFAULT 1,
                ordine        INTEGER DEFAULT 0
            )
        """)
        con.commit()
        # Migration: aggiungi colonna se mancante
        cols = [c[1] for c in cur.execute("PRAGMA table_info(Contesti)").fetchall()]
        if 'id_tipo_evento_default' not in cols:
            cur.execute("ALTER TABLE Contesti ADD COLUMN id_tipo_evento_default INTEGER")
            con.commit()
        # Pre-popola solo se la tabella è vuota
        count = cur.execute("SELECT COUNT(*) FROM Contesti").fetchone()[0]
        if count == 0:
            valori = [
                ('AFM 2025',           'AFM 2025',   1, 10),
                ('EFM 2026',           'EFM 2026',   1, 20),
                ('Mipcom2025',         'Mipcom2025', 1, 30),
                ('CFF 2026',            'CFF 2026',   1, 40),
                ('ACQUISTI',           None,          1, 50),
                ('Holdback - 2 window', None,         1, 60),
            ]
            cur.executemany(
                "INSERT OR IGNORE INTO Contesti (nome, gemma_supporto, attivo, ordine) VALUES (?,?,?,?)",
                valori
            )
            con.commit()
    except sqlite3.Error:
        pass


def get_contesti() -> list[dict]:
    """Restituisce tutti i contesti ordinati per ordine."""
    con = _conn()
    _ensure_contesti_table(con)
    rows = con.execute(
        "SELECT id_contesto, nome, gemma_supporto, attivo, ordine, id_tipo_evento_default FROM Contesti ORDER BY ordine, nome"
    ).fetchall()
    con.close()
    return [{'id_contesto': r[0], 'nome': r[1], 'gemma_supporto': r[2], 'attivo': r[3], 'ordine': r[4], 'id_tipo_evento_default': r[5]} for r in rows]


def aggiungi_contesto(nome: str, gemma_supporto: str | None, ordine: int = 0, id_tipo_evento_default: int | None = None) -> tuple[bool, str]:
    try:
        con = _conn()
        _ensure_contesti_table(con)
        con.execute(
            "INSERT INTO Contesti (nome, gemma_supporto, attivo, ordine, id_tipo_evento_default) VALUES (?,?,1,?,?)",
            (nome.strip(), gemma_supporto.strip() if gemma_supporto else None, ordine, id_tipo_evento_default)
        )
        con.commit()
        con.close()
        return True, 'Contesto aggiunto.'
    except sqlite3.IntegrityError:
        return False, 'Nome già esistente.'
    except Exception as e:
        return False, str(e)


def aggiorna_contesto(id_contesto: int, nome: str, gemma_supporto: str | None, attivo: int, ordine: int, id_tipo_evento_default: int | None = None) -> tuple[bool, str]:
    try:
        con = _conn()
        _ensure_contesti_table(con)
        row = con.execute("SELECT nome FROM Contesti WHERE id_contesto=?", (id_contesto,)).fetchone()
        vecchio_nome = row[0] if row else None
        nuovo_nome = nome.strip()
        con.execute(
            "UPDATE Contesti SET nome=?, gemma_supporto=?, attivo=?, ordine=?, id_tipo_evento_default=? WHERE id_contesto=?",
            (nuovo_nome, gemma_supporto.strip() if gemma_supporto else None, attivo, ordine, id_tipo_evento_default, id_contesto)
        )
        if vecchio_nome and vecchio_nome != nuovo_nome:
            con.execute(
                "UPDATE LogAggiornamenti SET dettaglio_contesto=? WHERE dettaglio_contesto=?",
                (nuovo_nome, vecchio_nome)
            )
        con.commit()
        con.close()
        return True, 'Contesto aggiornato.'
    except sqlite3.IntegrityError:
        return False, 'Nome già esistente.'
    except Exception as e:
        return False, str(e)


def elimina_contesto(id_contesto: int) -> tuple[bool, str]:
    try:
        con = _conn()
        _ensure_contesti_table(con)
        con.execute("DELETE FROM Contesti WHERE id_contesto=?", (id_contesto,))
        con.commit()
        con.close()
        return True, 'Contesto eliminato.'
    except Exception as e:
        return False, str(e)


def get_distributore_by_nome(nome: str) -> dict | None:
    """
    Recupera un distributore dal nome.

    Args:
        nome: Nome del distributore

    Returns:
        Dizionario con id_distributore e nome, oppure None se non trovato
    """
    con = _conn()
    _ensure_distributori_table(con)
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    cur.execute("""
        SELECT id_distributore, nome
        FROM Distributori
        WHERE LOWER(nome) = LOWER(?)
        LIMIT 1
    """, (nome,))

    row = cur.fetchone()
    con.close()

    return dict(row) if row else None


def get_or_create_distributore(nome: str) -> int:
    """
    Recupera o crea un distributore per nome.
    Se non esiste in Distributori, lo crea automaticamente.

    Returns:
        id_distributore (int)
    """
    con = _conn()
    _ensure_distributori_table(con)
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    cur.execute("SELECT id_distributore FROM Distributori WHERE LOWER(nome) = LOWER(?)", (nome,))
    row = cur.fetchone()

    if row:
        id_dist = row['id_distributore']
    else:
        cur.execute("INSERT INTO Distributori (nome) VALUES (?)", (nome,))
        con.commit()
        id_dist = cur.lastrowid

    con.close()
    return id_dist


def ricerca_prodotto_smart(titolo: str, distributore_nome: str = None, limit: int = 10) -> dict:
    """
    Ricerca intelligente prodotto in MediaTrack e Gemma (search-as-you-type).
    Usa ricerca substring (SQL LIKE) senza fuzzy matching.

    AGGIORNATO: Post-migrazione V11 - usa distributore_nome (fonte unica: Gemma)

    Args:
        titolo: Titolo da cercare (anche parziale)
        distributore_nome: Nome distributore per filtrare (opzionale, fonte unica: Gemma)
        limit: Numero massimo risultati per archivio

    Returns:
        {
            'mediatrack': [
                {
                    'id_media': 'FILM-123',
                    'titolo_ufficiale': 'XENO',
                    'tipo': 'Film',
                    'anno': '2024',
                    'distributore_nome': 'Blue Fox',
                    'codice_imdb': 'tt12345'
                }
            ],
            'gemma': [
                {
                    'id': 'G_ABC123',
                    'titolo': 'XENO',
                    'distributore': 'Blue Fox',
                    'tipo': 'Film',
                    'codice_imdb': 'tt12345'
                }
            ]
        }
    """
    if not titolo or len(titolo) < 2:
        return {'mediatrack': [], 'gemma': []}

    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Pattern ricerca (case-insensitive con LIKE)
    search_pattern = f"%{titolo}%"

    # === RICERCA MEDIATRACK (solo prodotti MediaTrack puri, no G_) ===
    mediatrack_results = []

    if distributore_nome and distributore_nome.strip():
        # Ricerca con filtro distributore
        cur.execute("""
            SELECT
                am.id_media,
                am.titolo_ufficiale,
                am.tipo,
                am.codice_imdb,
                am.distributore_nome,
                od.anno_produzione as anno
            FROM AnagraficaMedia am
            LEFT JOIN OmdbData od ON am.id_media = od.id_media_fk
            WHERE am.titolo_ufficiale LIKE ? COLLATE NOCASE
              AND LOWER(am.distributore_nome) = LOWER(?)
              AND am.id_media NOT LIKE 'G_%'
              AND am.deleted_at IS NULL
            ORDER BY am.titolo_ufficiale
            LIMIT ?
        """, (search_pattern, distributore_nome.strip(), limit))
    else:
        # Ricerca globale (tutti i distributori)
        cur.execute("""
            SELECT
                am.id_media,
                am.titolo_ufficiale,
                am.tipo,
                am.codice_imdb,
                am.distributore_nome,
                od.anno_produzione as anno
            FROM AnagraficaMedia am
            LEFT JOIN OmdbData od ON am.id_media = od.id_media_fk
            WHERE am.titolo_ufficiale LIKE ? COLLATE NOCASE
              AND am.id_media NOT LIKE 'G_%'
              AND am.deleted_at IS NULL
            ORDER BY am.titolo_ufficiale
            LIMIT ?
        """, (search_pattern, limit))

    mediatrack_results = [dict(row) for row in cur.fetchall()]

    # === RICERCA GEMMA (via DuckDB) ===
    titolo_esc = titolo.replace("'", "''")
    gemma_where = f"LOWER(A_TITOLO) LIKE LOWER('%{titolo_esc}%')"
    if distributore_nome and distributore_nome.strip():
        dist_esc = distributore_nome.strip().replace("'", "''")
        gemma_where += f" AND LOWER(A_DISTRIBUTORE) = LOWER('{dist_esc}')"
    gdf_g = _gemma_df(
        where=gemma_where,
        columns='A_ID, A_TITOLO, A_DISTRIBUTORE, A_TIPOLOGIA, A_COD_IMDB'
    )
    if not gdf_g.empty:
        gdf_g = gdf_g.sort_values('A_TITOLO').head(limit)
        gemma_results = [
            {
                'id': 'G_' + str(r['A_ID']),
                'titolo': r.get('A_TITOLO'),
                'distributore': r.get('A_DISTRIBUTORE'),
                'tipo': r.get('A_TIPOLOGIA'),
                'codice_imdb': r.get('A_COD_IMDB'),
            }
            for _, r in gdf_g.iterrows()
        ]
    else:
        gemma_results = []

    con.close()

    return {
        'mediatrack': mediatrack_results,
        'gemma': gemma_results
    }




# =================================================================
# >>> UTILITÀ RESET DATABASE
# =================================================================

def reset_mediatrack_completo() -> tuple[bool, str]:
    """
    Reset COMPLETO MediaTrack.

    ELIMINA:
    - AnagraficaMedia (tutti i prodotti MediaTrack)
    - LogAggiornamenti (tutti gli eventi prodotti)
    - LogEventiDistributore (eventi distributori)
    - OmdbData (cache dati OMDB)

    MANTIENE:
    - TipiEvento (configurazione base tipi evento)
    - gemma (database read-only, fonte unica)

    ATTENZIONE: Questa operazione è IRREVERSIBILE!
    Creare sempre backup prima di eseguire.

    Returns:
        (success, message)
    """
    import shutil
    from pathlib import Path
    from datetime import datetime

    try:
        # 1. BACKUP AUTOMATICO
        db_path = Path(NOME_DATABASE)
        backup_dir = db_path.parent / 'backups'
        backup_dir.mkdir(exist_ok=True)

        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_path = backup_dir / f"mediatrack_pre_reset_{timestamp}.db"

        shutil.copy2(db_path, backup_path)
        print(f"[BACKUP] Creato backup: {backup_path}")

        # 2. RESET DATABASE
        con = _conn()
        cur = con.cursor()

        # Conta record prima del reset (per messaggio finale)
        cur.execute("SELECT COUNT(*) FROM AnagraficaMedia")
        count_prodotti = cur.fetchone()[0]

        cur.execute("SELECT COUNT(*) FROM LogAggiornamenti")
        count_log = cur.fetchone()[0]

        cur.execute("SELECT COUNT(*) FROM OmdbData")
        count_omdb = cur.fetchone()[0]

        # DISABILITA TEMPORANEAMENTE I VINCOLI FOREIGN KEY per evitare errori durante reset
        print("[RESET] Disabilitazione vincoli FOREIGN KEY...")
        cur.execute("PRAGMA foreign_keys = OFF")

        # Elimina dati MediaTrack (ordine non più critico senza FK attivi)
        print("[RESET] Eliminazione OmdbData...")
        cur.execute("DELETE FROM OmdbData")

        print("[RESET] Eliminazione LogAggiornamenti...")
        cur.execute("DELETE FROM LogAggiornamenti")

        print("[RESET] Eliminazione LogEventiDistributore...")
        cur.execute("DELETE FROM LogEventiDistributore")

        print("[RESET] Eliminazione AnagraficaMedia...")
        cur.execute("DELETE FROM AnagraficaMedia")

        # RIABILITA i vincoli FOREIGN KEY
        print("[RESET] Riabilitazione vincoli FOREIGN KEY...")
        cur.execute("PRAGMA foreign_keys = ON")

        # Reset autoincrement counters
        cur.execute("""
            DELETE FROM sqlite_sequence
            WHERE name IN ('LogAggiornamenti', 'LogEventiDistributore')
        """)

        con.commit()
        con.close()

        message = (
            f"Reset MediaTrack completato! "
            f"Eliminati: {count_prodotti} prodotti, {count_log} log eventi, "
            f"{count_omdb} dati OMDB. "
            f"Backup salvato: {backup_path.name}"
        )

        print(f"[OK] {message}")
        return True, message

    except Exception as e:
        print(f"[ERRORE] Reset fallito: {e}")
        if 'con' in locals():
            con.rollback()
            con.close()
        return False, f"Errore durante reset: {str(e)}"


# =================================================================
# >>> BACKUP & RESTORE SYSTEM
# =================================================================

def get_current_schema_version() -> int:
    """
    Recupera versione schema corrente dal database.

    Returns:
        int: Versione schema corrente (es: 3)
             Se tabella schema_version non esiste, ritorna 1 (legacy)
    """
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("SELECT MAX(version) FROM schema_version")
        version = cur.fetchone()[0]
        con.close()

        return version if version else 1

    except sqlite3.OperationalError:
        # Tabella schema_version non esiste = schema v1 (legacy)
        if 'con' in locals():
            con.close()
        return 1
    except Exception as e:
        print(f"[ERROR] get_current_schema_version: {e}")
        if 'con' in locals():
            con.close()
        return 1


def get_backup_schema_version(backup_path: str) -> int:
    """
    Estrae versione schema da un file backup.

    Prova prima a estrarre dal filename (_vX_), poi interroga
    la tabella schema_version nel backup stesso.

    Args:
        backup_path: Path completo al file backup

    Returns:
        int: Versione schema del backup (es: 3)
             Se non trovata, assume v1 (legacy)
    """
    import re
    from pathlib import Path

    try:
        filename = Path(backup_path).name

        # Metodo 1: Estrai versione dal filename pattern _vX_
        match = re.search(r'_v(\d+)_', filename)
        if match:
            return int(match.group(1))

        # Metodo 2: Leggi da schema_version table nel backup (read-only)
        try:
            con = sqlite3.connect(f"file:{backup_path}?mode=ro", uri=True)
            cur = con.cursor()

            cur.execute("SELECT MAX(version) FROM schema_version")
            version = cur.fetchone()[0]
            con.close()

            return version if version else 1

        except:
            # Tabella non esiste o backup corrotto = assume v1
            if 'con' in locals():
                try:
                    con.close()
                except:
                    pass
            return 1

    except Exception as e:
        print(f"[ERROR] get_backup_schema_version: {e}")
        return 1


def check_schema_compatibility(backup_schema: int, current_schema: int) -> tuple[bool, str, str]:
    """
    Verifica compatibilità schema tra backup e database corrente.

    POLITICA:
    - Schema UGUALE: OK, restore diretto
    - Schema MINORE (backup < current): WARNING, potrebbe mancare dati post-backup
    - Schema MAGGIORE (backup > current): ERROR, backup da versione futura (blocca)

    Args:
        backup_schema: Versione schema del backup
        current_schema: Versione schema del database corrente

    Returns:
        tuple: (is_compatible, severity, message)
               severity: "ok" | "warning" | "error"
    """
    if backup_schema == current_schema:
        return True, "ok", f"Schema compatibile (v{current_schema})"

    if backup_schema > current_schema:
        return False, "error", (
            f"INCOMPATIBILE: Backup ha schema v{backup_schema}, "
            f"database corrente ha v{current_schema}. "
            f"Impossibile ripristinare backup da versione futura. "
            f"Aggiorna l'applicazione alla versione corretta."
        )

    # backup_schema < current_schema
    return True, "warning", (
        f"Schema NON IDENTICO: Backup v{backup_schema}, database corrente v{current_schema}. "
        f"Il restore funzionera' ma potrebbero mancare colonne/tabelle aggiunte dopo il backup. "
        f"Verifica la compatibilita' prima di procedere."
    )


def sanitize_nota(nota: str) -> str:
    """
    Sanitizza nota per uso sicuro in filename.

    Regole:
    - Solo alfanumerici, underscore, trattini
    - Spazi convertiti in underscore
    - Max 30 caratteri
    - Lowercase

    Args:
        nota: Nota descrittiva dall'utente

    Returns:
        str: Nota sanitizzata per filename (es: "prima_pulizia")
             Stringa vuota se nota è None/vuota
    """
    import re

    if not nota or not nota.strip():
        return ""

    # Lowercase e trim
    nota = nota.strip().lower()

    # Sostituisci spazi con underscore
    nota = nota.replace(" ", "_")

    # Rimuovi caratteri non validi (mantiene solo a-z, 0-9, _, -)
    nota = re.sub(r'[^a-z0-9_-]', '', nota)

    # Max 30 caratteri
    nota = nota[:30]

    return nota


def backup_database(nota: str = None) -> tuple[bool, str, str]:
    """
    Crea backup manuale del database con versione schema.

    Il filename include:
    - Tipo: 'manual'
    - Versione schema corrente (es: v3)
    - Nota sanitizzata (opzionale)
    - Timestamp

    Formato: mediatrack_manual_v3_nota_20251030_143052.db

    Args:
        nota: Nota descrittiva opzionale (max 30 char, sanitizzata)

    Returns:
        tuple: (success, message, backup_filename)
               success: True se backup creato con successo
               message: Messaggio descrittivo
               backup_filename: Nome file backup creato (None se error)
    """
    import shutil
    from pathlib import Path
    from datetime import datetime

    try:
        # Percorsi
        db_path = Path(NOME_DATABASE)
        backup_dir = db_path.parent / 'backups'
        backup_dir.mkdir(exist_ok=True)

        # Recupera versione schema corrente
        schema_version = get_current_schema_version()

        # Timestamp
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

        # Sanitizza nota
        nota_sanitized = sanitize_nota(nota) if nota else ""

        # Costruisci filename con versione schema
        if nota_sanitized:
            filename = f"mediatrack_manual_v{schema_version}_{nota_sanitized}_{timestamp}.db"
        else:
            filename = f"mediatrack_manual_v{schema_version}_{timestamp}.db"

        backup_path = backup_dir / filename

        # Copia file database
        shutil.copy2(db_path, backup_path)

        # Verifica integrità backup
        con_backup = sqlite3.connect(str(backup_path))
        cur_backup = con_backup.cursor()
        cur_backup.execute("PRAGMA integrity_check")
        integrity = cur_backup.fetchone()[0]
        con_backup.close()

        if integrity != "ok":
            # Elimina backup corrotto
            backup_path.unlink()
            return False, "Backup fallito: integrita' compromessa", None

        # Calcola size
        size_bytes = backup_path.stat().st_size
        size_mb = size_bytes / (1024 * 1024)

        message = f"Backup creato con successo: {filename} ({size_mb:.2f} MB, schema v{schema_version})"
        print(f"[BACKUP] {message}")

        return True, message, filename

    except Exception as e:
        print(f"[ERROR] backup_database: {e}")
        import traceback
        traceback.print_exc()
        return False, f"Errore durante backup: {str(e)}", None


def list_backups() -> list[dict]:
    """
    Recupera lista completa backup con metadata.

    Ogni backup include:
    - filename, tipo, nota, timestamp, data_creazione
    - schema_version: Versione schema del backup
    - size_mb, size_human: Dimensione file
    - num_prodotti, num_eventi: Conteggi record (query sul backup)
    - path_completo: Path assoluto al file

    Returns:
        list: Lista dizionari ordinata per timestamp DESC (più recenti prima)
    """
    from pathlib import Path
    from datetime import datetime
    import re

    try:
        backup_dir = Path(NOME_DATABASE).parent / 'backups'
        if not backup_dir.exists():
            return []

        backups = []

        # Pattern filename supporta versione opzionale
        # mediatrack_[TIPO]_v[SCHEMA_OPT]_[NOTA_OPT]_YYYYMMDD_HHMMSS.db
        pattern = re.compile(
            r'^mediatrack_(manual|auto_restore|pre_reset)'
            r'(?:_v(\d+))?'  # versione opzionale
            r'(?:_(.+?))?'   # nota opzionale (non greedy)
            r'_(\d{8}_\d{6})\.db$'
        )

        for file_path in backup_dir.glob("mediatrack_*.db"):
            match = pattern.match(file_path.name)
            if not match:
                continue  # Skip file non conformi

            tipo, schema_version, nota, timestamp = match.groups()

            # Se schema_version è None, assumere v1 (legacy backup)
            schema_version = int(schema_version) if schema_version else 1

            # Parse timestamp
            try:
                dt = datetime.strptime(timestamp, "%Y%m%d_%H%M%S")
                data_creazione = dt.strftime("%d/%m/%Y %H:%M:%S")
            except:
                data_creazione = timestamp

            # Size
            size_bytes = file_path.stat().st_size
            size_mb = size_bytes / (1024 * 1024)
            size_human = f"{size_mb:.2f} MB"

            # Conta prodotti ed eventi (connessione read-only al backup)
            try:
                con = sqlite3.connect(f"file:{file_path}?mode=ro", uri=True)
                cur = con.cursor()

                # Conta prodotti MediaTrack (escludi G_)
                cur.execute("SELECT COUNT(*) FROM AnagraficaMedia WHERE id_media NOT LIKE 'G_%'")
                num_prodotti = cur.fetchone()[0]

                # Conta eventi
                cur.execute("SELECT COUNT(*) FROM LogAggiornamenti")
                num_eventi = cur.fetchone()[0]

                con.close()

            except Exception as e:
                print(f"[WARNING] Impossibile leggere metadata backup {file_path.name}: {e}")
                num_prodotti = -1
                num_eventi = -1

            backups.append({
                "filename": file_path.name,
                "tipo": tipo,
                "nota": nota if nota else None,
                "timestamp": timestamp,
                "data_creazione": data_creazione,
                "schema_version": schema_version,
                "size_mb": round(size_mb, 2),
                "size_human": size_human,
                "num_prodotti": num_prodotti,
                "num_eventi": num_eventi,
                "path_completo": str(file_path)
            })

        # Ordina per timestamp DESC (più recenti prima)
        backups.sort(key=lambda x: x['timestamp'], reverse=True)

        return backups

    except Exception as e:
        print(f"[ERROR] list_backups: {e}")
        import traceback
        traceback.print_exc()
        return []


def get_backup_stats() -> dict:
    """
    Recupera statistiche aggregate sui backup.

    Returns:
        dict: {
            "total_backups": 12,
            "total_size_mb": 45.67,
            "total_size_human": "45.67 MB",
            "ultimo_backup": "30/10/2025 16:35:41",
            "manual_count": 8,
            "auto_restore_count": 3,
            "pre_reset_count": 1
        }
    """
    try:
        backups = list_backups()

        if not backups:
            return {
                "total_backups": 0,
                "total_size_mb": 0,
                "total_size_human": "0 MB",
                "ultimo_backup": None,
                "manual_count": 0,
                "auto_restore_count": 0,
                "pre_reset_count": 0
            }

        total_size = sum(b['size_mb'] for b in backups)

        return {
            "total_backups": len(backups),
            "total_size_mb": round(total_size, 2),
            "total_size_human": f"{total_size:.2f} MB",
            "ultimo_backup": backups[0]['data_creazione'],  # già ordinati DESC
            "manual_count": len([b for b in backups if b['tipo'] == 'manual']),
            "auto_restore_count": len([b for b in backups if b['tipo'] == 'auto_restore']),
            "pre_reset_count": len([b for b in backups if b['tipo'] == 'pre_reset'])
        }

    except Exception as e:
        print(f"[ERROR] get_backup_stats: {e}")
        return {
            "total_backups": 0,
            "total_size_mb": 0,
            "total_size_human": "Error",
            "ultimo_backup": None,
            "manual_count": 0,
            "auto_restore_count": 0,
            "pre_reset_count": 0
        }


def restore_database(backup_filename: str, force: bool = False) -> tuple[bool, str, str, dict]:
    """
    Ripristina database da backup con verifica compatibilità schema.

    ATTENZIONE: Operazione CRITICA!
    - Verifica compatibilità schema (blocca se backup da versione futura)
    - Crea backup automatico pre-restore OBBLIGATORIO
    - Sostituisce database corrente
    - Valida integrità pre e post-restore
    - Rollback automatico in caso di errore

    Args:
        backup_filename: Nome file backup da ripristinare (solo filename, no path)
        force: Se True, procede anche con warning compatibilità (non ignora errori)

    Returns:
        tuple: (success, message, auto_backup_filename, compatibility_info)
               compatibility_info: {
                   "backup_schema": 3,
                   "current_schema": 3,
                   "is_compatible": True,
                   "severity": "ok"|"warning"|"error",
                   "message": "..."
               }
    """
    import shutil
    from pathlib import Path
    from datetime import datetime
    import gc

    try:
        db_path = Path(NOME_DATABASE)
        backup_dir = db_path.parent / 'backups'
        source_backup = backup_dir / backup_filename

        # 1. VALIDAZIONE FILE
        if not source_backup.exists():
            return False, f"Backup non trovato: {backup_filename}", None, {}

        # 2. VERIFICA COMPATIBILITÀ SCHEMA
        backup_schema = get_backup_schema_version(str(source_backup))
        current_schema = get_current_schema_version()

        is_compatible, severity, compat_message = check_schema_compatibility(
            backup_schema, current_schema
        )

        compatibility_info = {
            "backup_schema": backup_schema,
            "current_schema": current_schema,
            "is_compatible": is_compatible,
            "severity": severity,
            "message": compat_message
        }

        print(f"[RESTORE] Compatibilita' schema: {severity} - {compat_message}")

        # BLOCCA se incompatibile (schema futuro)
        if severity == "error":
            return False, compat_message, None, compatibility_info

        # WARNING: richiede conferma utente (force=True)
        if severity == "warning" and not force:
            return False, f"Conferma richiesta: {compat_message}", None, compatibility_info

        # 3. VERIFICA INTEGRITÀ BACKUP SORGENTE
        print("[RESTORE] Validazione integrita' backup sorgente...")
        try:
            con_source = sqlite3.connect(f"file:{source_backup}?mode=ro", uri=True)
            cur_source = con_source.cursor()
            cur_source.execute("PRAGMA integrity_check")
            integrity = cur_source.fetchone()[0]
            con_source.close()

            if integrity != "ok":
                return False, "Backup sorgente corrotto (PRAGMA integrity_check fallito)", None, compatibility_info

        except Exception as e:
            return False, f"Impossibile validare backup sorgente: {str(e)}", None, compatibility_info

        # 4. BACKUP AUTOMATICO PRE-RESTORE (OBBLIGATORIO)
        print("[RESTORE] Creazione backup automatico pre-restore...")
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

        # Include versione schema nel filename auto-backup
        auto_schema = get_current_schema_version()
        auto_backup_filename = f"mediatrack_auto_restore_v{auto_schema}_{timestamp}.db"
        auto_backup_path = backup_dir / auto_backup_filename

        try:
            shutil.copy2(db_path, auto_backup_path)
            print(f"[RESTORE] Backup pre-restore creato: {auto_backup_filename}")

        except Exception as e:
            return False, f"ERRORE CRITICO: impossibile creare backup pre-restore: {str(e)}", None, compatibility_info

        # 5. CHIUDI CONNESSIONI AL DB CORRENTE
        print("[RESTORE] Chiusura connessioni database...")
        gc.collect()  # Force garbage collection per chiudere connessioni orphan

        # 6. RESTORE (sostituisci file)
        print(f"[RESTORE] Ripristino da: {backup_filename}")
        try:
            shutil.copy2(source_backup, db_path)
            print(f"[RESTORE] Database sostituito con successo")

        except Exception as e:
            # ROLLBACK: ripristina backup automatico
            print(f"[RESTORE] ERRORE durante restore, esecuzione rollback...")
            try:
                shutil.copy2(auto_backup_path, db_path)
                print("[RESTORE] Rollback completato, database ripristinato a stato precedente")
                return False, f"Restore fallito (rollback eseguito): {str(e)}", auto_backup_filename, compatibility_info

            except Exception as rollback_error:
                print(f"[RESTORE] ERRORE CRITICO: rollback fallito! {rollback_error}")
                return False, f"ERRORE CRITICO: restore fallito E rollback fallito! Database potrebbe essere corrotto!", auto_backup_filename, compatibility_info

        # 7. VALIDAZIONE POST-RESTORE
        print("[RESTORE] Validazione integrita' post-restore...")
        try:
            con_restored = _conn()
            cur_restored = con_restored.cursor()

            # Verifica integrità
            cur_restored.execute("PRAGMA integrity_check")
            integrity = cur_restored.fetchone()[0]

            if integrity != "ok":
                print(f"[RESTORE] Integrita' compromessa post-restore, esecuzione rollback...")
                con_restored.close()

                # ROLLBACK
                shutil.copy2(auto_backup_path, db_path)
                return False, "Restore fallito: integrita' database compromessa post-restore (rollback eseguito)", auto_backup_filename, compatibility_info

            # Conta prodotti
            cur_restored.execute("SELECT COUNT(*) FROM AnagraficaMedia WHERE id_media NOT LIKE 'G_%'")
            num_prodotti = cur_restored.fetchone()[0]

            # Verifica versione schema ripristinata (gestisce backup legacy senza tabella)
            try:
                cur_restored.execute("SELECT MAX(version) FROM schema_version")
                restored_schema = cur_restored.fetchone()[0] or 1
            except sqlite3.OperationalError:
                # Backup legacy senza tabella schema_version - assume schema dal backup
                restored_schema = backup_schema

            con_restored.close()

            print(f"[RESTORE] Validazione OK - Prodotti: {num_prodotti}, Schema: v{restored_schema}")

        except Exception as e:
            print(f"[RESTORE] Validazione post-restore fallita: {e}")
            return False, f"Restore completato ma validazione fallita: {str(e)}", auto_backup_filename, compatibility_info

        # 8. SUCCESS
        message = (
            f"Restore completato con successo! "
            f"Database ripristinato da: {backup_filename}. "
            f"Schema: v{restored_schema}, Prodotti: {num_prodotti}. "
            f"Backup pre-restore salvato: {auto_backup_filename}"
        )

        print(f"[RESTORE] {message}")
        return True, message, auto_backup_filename, compatibility_info

    except Exception as e:
        print(f"[ERROR] restore_database: {e}")
        import traceback
        traceback.print_exc()
        return False, f"Errore inaspettato durante restore: {str(e)}", None, {}


def delete_backup(backup_filename: str) -> tuple[bool, str]:
    """
    Elimina un backup.

    Validazioni sicurezza:
    - File deve esistere
    - Filename deve iniziare con "mediatrack_"
    - Path must be within backups directory (no path traversal)

    Args:
        backup_filename: Nome file backup da eliminare (solo filename)

    Returns:
        tuple: (success, message)
    """
    from pathlib import Path

    try:
        backup_dir = Path(NOME_DATABASE).parent / 'backups'
        backup_path = backup_dir / backup_filename

        # Validazione 1: File esiste
        if not backup_path.exists():
            return False, f"Backup non trovato: {backup_filename}"

        # Validazione 2: Nome file valido (security check)
        if not backup_path.name.startswith("mediatrack_"):
            return False, "File non valido (nome non conforme)"

        # Validazione 3: Path traversal check
        if not str(backup_path).startswith(str(backup_dir)):
            return False, "Percorso non valido (security violation)"

        # Elimina file
        backup_path.unlink()
        print(f"[DELETE] Backup eliminato: {backup_filename}")

        return True, f"Backup eliminato: {backup_filename}"

    except Exception as e:
        print(f"[ERROR] delete_backup: {e}")
        return False, f"Errore eliminazione backup: {str(e)}"


# =================================================================
# >>> SCREENPLAY MANAGEMENT SYSTEM
# =================================================================

def scan_pdf_directory(root_path: str) -> dict:
    """
    Scansiona ricorsivamente una directory per trovare tutti i file PDF.

    Args:
        root_path: Path della directory radice da scansionare

    Returns:
        dict: {
            'success': bool,
            'pdf_count': int,
            'pdfs': [
                {
                    'filename': str,      # Nome file (es: "Inception.pdf")
                    'full_path': str,     # Path completo assoluto
                    'relative_path': str, # Path relativo dalla root
                    'parent_folder': str, # Nome directory padre (potenziale fornitore)
                    'size_mb': float      # Dimensione file in MB
                },
                ...
            ],
            'error': str (opzionale)
        }
    """
    try:
        import pathlib

        root = pathlib.Path(root_path)

        # Validazione path
        if not root.exists():
            return {
                'success': False,
                'error': f"Directory non trovata: {root_path}",
                'pdf_count': 0,
                'pdfs': []
            }

        if not root.is_dir():
            return {
                'success': False,
                'error': f"Il path non è una directory: {root_path}",
                'pdf_count': 0,
                'pdfs': []
            }

        pdfs = []

        # Scansione ricorsiva
        for pdf_file in root.rglob('*.pdf'):
            if not pdf_file.is_file():
                continue

            try:
                # Calcola dimensione file
                size_bytes = pdf_file.stat().st_size
                size_mb = round(size_bytes / (1024 * 1024), 2)

                # Calcola path relativo
                try:
                    relative = pdf_file.relative_to(root)
                    relative_path = str(relative)
                except ValueError:
                    relative_path = str(pdf_file)

                # Estrai directory padre (potenziale fornitore)
                parent_folder = pdf_file.parent.name

                pdfs.append({
                    'filename': pdf_file.name,
                    'full_path': str(pdf_file.absolute()),
                    'relative_path': relative_path,
                    'parent_folder': parent_folder,
                    'size_mb': size_mb
                })

            except Exception as file_error:
                print(f"[WARNING] Errore lettura file {pdf_file}: {file_error}")
                continue

        # Ordina per nome file
        pdfs.sort(key=lambda x: x['filename'].lower())

        print(f"[SCREENPLAY] Scansionati {len(pdfs)} PDF in {root_path}")

        return {
            'success': True,
            'pdf_count': len(pdfs),
            'pdfs': pdfs
        }

    except Exception as e:
        print(f"[ERROR] scan_pdf_directory: {e}")
        import traceback
        traceback.print_exc()
        return {
            'success': False,
            'error': str(e),
            'pdf_count': 0,
            'pdfs': []
        }


# =================================================================
# >>> DASHBOARD STATISTICS
# =================================================================

def get_dashboard_stats():
    """
    Recupera le statistiche per la dashboard:
    - Prodotti totali in MediaTrack
    - Prodotti aggiunti negli ultimi 7 giorni
    - Distributori totali (da Gemma)
    - Eventi totali
    - Eventi aggiunti negli ultimi 7 giorni
    - Sessioni attive
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # Prodotti totali
    cur.execute("SELECT COUNT(*) as count FROM AnagraficaMedia")
    prodotti_totali = cur.execute("SELECT COUNT(*) as count FROM AnagraficaMedia WHERE deleted_at IS NULL").fetchone()['count']

    # Prodotti ultimi 7 giorni (basati sulla data di creazione nel LogAggiornamenti)
    cur.execute("""
        SELECT COUNT(DISTINCT id_media_fk) as count
        FROM LogAggiornamenti
        WHERE data_log >= date('now', '-7 days')
    """)
    prodotti_ultimi_7gg = cur.fetchone()['count']

    # Distributori totali (da Gemma) — via DuckDB
    _gdf_dist = _gemma_df(
        where="A_DISTRIBUTORE IS NOT NULL AND A_DISTRIBUTORE != ''",
        columns='DISTINCT A_DISTRIBUTORE'
    )
    distributori_totali = len(_gdf_dist) if not _gdf_dist.empty else 0

    # Eventi totali
    cur.execute("SELECT COUNT(*) as count FROM LogEventiDistributore")
    eventi_totali = cur.fetchone()['count']

    # Eventi ultimi 7 giorni
    cur.execute("""
        SELECT COUNT(*) as count
        FROM LogEventiDistributore
        WHERE data_evento >= date('now', '-7 days')
    """)
    eventi_ultimi_7gg = cur.fetchone()['count']

    # Sessioni attive
    cur.execute("SELECT COUNT(*) as count FROM SessioniImport WHERE stato = 'attiva'")
    sessioni_attive = cur.fetchone()['count']

    con.close()

    return {
        'prodotti_totali': prodotti_totali,
        'prodotti_ultimi_7gg': prodotti_ultimi_7gg,
        'distributori_totali': distributori_totali,
        'eventi_totali': eventi_totali,
        'eventi_ultimi_7gg': eventi_ultimi_7gg,
        'sessioni_attive': sessioni_attive
    }


def get_recent_activity(limit=10):
    """
    Recupera le attività recenti (ultimi log eventi con dettagli prodotto).
    Restituisce una lista di dict con:
    - id_media
    - titolo_ufficiale
    - distributore_nome
    - data_evento
    - dettaglio_contesto
    - tipo (nome tipo evento, se disponibile)
    """
    con = _conn()
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    cur.execute("""
        SELECT
            l.id_media_fk as id_media,
            a.titolo_ufficiale,
            a.distributore_nome,
            l.data_evento,
            l.dettaglio_contesto,
            t.nome_tipo as tipo
        FROM LogEventiDistributore l
        JOIN AnagraficaMedia a ON l.id_media_fk = a.id_media
        LEFT JOIN TipiEvento t ON l.id_tipo_evento_fk = t.id_tipo_evento
        ORDER BY l.data_evento DESC
        LIMIT ?
    """, (limit,))

    rows = cur.fetchall()
    con.close()

    # Converti Row objects in dict
    activities = []
    for row in rows:
        activities.append({
            'id_media': row['id_media'],
            'titolo_ufficiale': row['titolo_ufficiale'],
            'distributore_nome': row['distributore_nome'],
            'data_evento': row['data_evento'],
            'dettaglio_contesto': row['dettaglio_contesto'],
            'tipo': row['tipo']
        })

    return activities


# =================================================================
# >>> USER AUTHENTICATION
# =================================================================

# =================================================================
# >>> GESTIONE TIPI EVENTO
# =================================================================

def get_all_event_types():
    """
    Recupera tutti i tipi evento dal database.

    Returns:
        list: Lista di dizionari con i dati dei tipi evento
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cursor = con.cursor()

        cursor.execute("""
            SELECT id_tipo_evento, nome_evento, colore_hex, icona,
                   descrizione, funzione_speciale, predefinito, campi_visibili, attivo,
                   COALESCE(visibilita, 'entrambi') AS visibilita
            FROM TipiEvento
            WHERE attivo = 1
            ORDER BY predefinito DESC, nome_evento ASC
        """)

        rows = cursor.fetchall()
        con.close()

        event_types = [dict(row) for row in rows]
        return event_types

    except Exception as e:
        print(f"[ERROR] Errore recupero tipi evento: {e}")
        return []


def get_event_type_by_id(id_tipo_evento):
    """
    Recupera un tipo evento specifico per ID.

    Args:
        id_tipo_evento (int): ID del tipo evento

    Returns:
        dict or None: Dati del tipo evento o None se non trovato
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cursor = con.cursor()

        cursor.execute("""
            SELECT id_tipo_evento, nome_evento, colore_hex, icona,
                   descrizione, funzione_speciale, attivo
            FROM TipiEvento
            WHERE id_tipo_evento = ?
        """, (id_tipo_evento,))

        row = cursor.fetchone()
        con.close()

        return dict(row) if row else None

    except Exception as e:
        print(f"[ERROR] Errore recupero tipo evento: {e}")
        return None


def create_event_type(nome_evento, colore_hex, icona='fa-calendar',
                     descrizione=None, funzione_speciale='none', campi_visibili=None, visibilita='entrambi'):
    """
    Crea un nuovo tipo evento.

    Args:
        nome_evento (str): Nome del tipo evento
        colore_hex (str): Colore in formato hex (#RRGGBB)
        icona (str): Classe icona FontAwesome
        descrizione (str): Descrizione del tipo evento
        funzione_speciale (str): Funzione speciale associata
        campi_visibili (str): JSON con configurazione campi visibili

    Returns:
        tuple: (success: bool, message: str, id_tipo_evento: int or None)
    """
    try:
        # Validazione
        if not nome_evento or not nome_evento.strip():
            return False, "Nome tipo evento obbligatorio", None

        # Verifica unicità nome
        con = _conn()
        cursor = con.cursor()

        cursor.execute("SELECT id_tipo_evento FROM TipiEvento WHERE nome_evento = ?",
                      (nome_evento.strip(),))
        if cursor.fetchone():
            con.close()
            return False, "Esiste già un tipo evento con questo nome", None

        # Inserimento
        cursor.execute("""
            INSERT INTO TipiEvento (nome_evento, colore_hex, icona, descrizione, funzione_speciale, campi_visibili, predefinito, attivo, visibilita)
            VALUES (?, ?, ?, ?, ?, ?, 0, 1, ?)
        """, (nome_evento.strip(), colore_hex, icona, descrizione, funzione_speciale, campi_visibili, visibilita or 'entrambi'))

        id_tipo_evento = cursor.lastrowid
        con.commit()
        con.close()

        print(f"[INFO] Tipo evento creato: {nome_evento} (ID: {id_tipo_evento})")
        return True, "Tipo evento creato con successo", id_tipo_evento

    except Exception as e:
        print(f"[ERROR] Errore creazione tipo evento: {e}")
        return False, f"Errore: {str(e)}", None


def update_event_type(id_tipo_evento, nome_evento=None, colore_hex=None,
                     icona=None, descrizione=None, funzione_speciale=None, campi_visibili=None, visibilita=None):
    """
    Aggiorna un tipo evento esistente.

    Args:
        id_tipo_evento (int): ID del tipo evento da aggiornare
        nome_evento (str, optional): Nuovo nome
        colore_hex (str, optional): Nuovo colore
        icona (str, optional): Nuova icona
        descrizione (str, optional): Nuova descrizione
        funzione_speciale (str, optional): Nuova funzione speciale
        campi_visibili (str, optional): JSON con configurazione campi visibili

    Returns:
        tuple: (success: bool, message: str)
    """
    try:
        con = _conn()
        cursor = con.cursor()

        # Verifica esistenza e se è predefinito
        cursor.execute("SELECT predefinito FROM TipiEvento WHERE id_tipo_evento = ?",
                      (id_tipo_evento,))
        result = cursor.fetchone()
        if not result:
            con.close()
            return False, "Tipo evento non trovato"

        is_predefinito = result[0] == 1

        # Verifica unicità nome se viene modificato
        if nome_evento:
            cursor.execute("""
                SELECT id_tipo_evento FROM TipiEvento
                WHERE nome_evento = ? AND id_tipo_evento != ?
            """, (nome_evento.strip(), id_tipo_evento))
            if cursor.fetchone():
                con.close()
                return False, "Esiste già un tipo evento con questo nome"

        # Costruisci query di aggiornamento
        updates = []
        params = []

        # Per eventi predefiniti, permetti modifica colore e visibilita
        if is_predefinito:
            if colore_hex is not None:
                updates.append("colore_hex = ?")
                params.append(colore_hex)
            if visibilita is not None:
                updates.append("visibilita = ?")
                params.append(visibilita)
        else:
            # Eventi custom: permetti modifica di tutti i campi
            if nome_evento is not None:
                updates.append("nome_evento = ?")
                params.append(nome_evento.strip())
            if colore_hex is not None:
                updates.append("colore_hex = ?")
                params.append(colore_hex)
            if icona is not None:
                updates.append("icona = ?")
                params.append(icona)
            if descrizione is not None:
                updates.append("descrizione = ?")
                params.append(descrizione if descrizione.strip() else None)
            if funzione_speciale is not None:
                updates.append("funzione_speciale = ?")
                params.append(funzione_speciale)
            if campi_visibili is not None:
                updates.append("campi_visibili = ?")
                params.append(campi_visibili)
            if visibilita is not None:
                updates.append("visibilita = ?")
                params.append(visibilita)

        if not updates:
            con.close()
            return False, "Nessun campo da aggiornare"

        params.append(id_tipo_evento)
        query = f"UPDATE TipiEvento SET {', '.join(updates)} WHERE id_tipo_evento = ?"

        cursor.execute(query, params)
        con.commit()
        con.close()

        print(f"[INFO] Tipo evento aggiornato: ID {id_tipo_evento}")
        return True, "Tipo evento aggiornato con successo"

    except Exception as e:
        print(f"[ERROR] Errore aggiornamento tipo evento: {e}")
        return False, f"Errore: {str(e)}"


def delete_event_type(id_tipo_evento):
    """
    Elimina un tipo evento (soft delete - imposta attivo = 0).

    Args:
        id_tipo_evento (int): ID del tipo evento da eliminare

    Returns:
        tuple: (success: bool, message: str)
    """
    try:
        con = _conn()
        cursor = con.cursor()

        # Verifica esistenza
        cursor.execute("SELECT nome_evento FROM TipiEvento WHERE id_tipo_evento = ?",
                      (id_tipo_evento,))
        row = cursor.fetchone()
        if not row:
            con.close()
            return False, "Tipo evento non trovato"

        # Soft delete
        cursor.execute("UPDATE TipiEvento SET attivo = 0 WHERE id_tipo_evento = ?",
                      (id_tipo_evento,))
        con.commit()
        con.close()

        print(f"[INFO] Tipo evento disattivato: {row[0]} (ID: {id_tipo_evento})")
        return True, "Tipo evento eliminato con successo"

    except Exception as e:
        print(f"[ERROR] Errore eliminazione tipo evento: {e}")
        return False, f"Errore: {str(e)}"


# =================================================================
# >>> PREFERITI E REMINDER
# =================================================================

def get_favorite_events(user_id):
    """
    Recupera tutti gli eventi marcati come preferiti.
    Rispetta la privacy: eventi pubblici + eventi privati dell'utente corrente.

    Returns:
        list: Lista di dizionari con informazioni eventi preferiti
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cur = con.cursor()

        cur.execute("""
            SELECT
                l.id_aggiornamento,
                l.id_media_fk,
                l.id_tipo_evento_fk,
                l.data_evento,
                l.dettaglio_contesto,
                l.note,
                l.data_reminder,
                l.preferito,
                l.privato,
                l.venduto_a,
                m.titolo_ufficiale as titolo_prodotto,
                m.tipo as genere,
                te.nome_evento as tipo_evento,
                te.colore_hex as colore_evento
            FROM LogAggiornamenti l
            JOIN AnagraficaMedia m ON l.id_media_fk = m.id_media
            JOIN TipiEvento te ON l.id_tipo_evento_fk = te.id_tipo_evento
            WHERE l.preferito = 1
            AND (l.privato = 0 OR l.created_by = ?)
            ORDER BY l.data_evento DESC
        """, (user_id,))

        rows = cur.fetchall()
        con.close()

        favorites = [dict(row) for row in rows]
        return favorites

    except Exception as e:
        print(f"[ERROR] Errore recupero preferiti: {e}")
        return []


def get_reminder_events(user_id):
    """
    Recupera tutti gli eventi con reminder attivi.
    Rispetta la privacy: eventi pubblici + eventi privati dell'utente corrente.

    Returns:
        list: Lista di dizionari con informazioni eventi con reminder
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cur = con.cursor()

        cur.execute("""
            SELECT
                l.id_aggiornamento,
                l.id_media_fk,
                l.id_tipo_evento_fk,
                l.data_evento,
                l.dettaglio_contesto,
                l.note,
                l.data_reminder,
                l.preferito,
                l.privato,
                l.venduto_a,
                m.titolo_ufficiale as titolo_prodotto,
                m.tipo as genere,
                te.nome_evento as tipo_evento,
                te.colore_hex as colore_evento,
                -- Calcola giorni al reminder
                CAST(julianday(l.data_reminder) - julianday('now') as INTEGER) as giorni_al_reminder
            FROM LogAggiornamenti l
            JOIN AnagraficaMedia m ON l.id_media_fk = m.id_media
            JOIN TipiEvento te ON l.id_tipo_evento_fk = te.id_tipo_evento
            WHERE l.data_reminder IS NOT NULL
            AND l.data_reminder != ''
            AND (l.privato = 0 OR l.created_by = ?)
            ORDER BY l.data_reminder ASC
        """, (user_id,))

        rows = cur.fetchall()
        con.close()

        reminders = [dict(row) for row in rows]
        return reminders

    except Exception as e:
        print(f"[ERROR] Errore recupero reminder: {e}")
        return []


def count_favorite_events(user_id):
    """
    Conta gli eventi preferiti per l'utente corrente.

    Returns:
        int: Numero di eventi preferiti
    """
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("""
            SELECT COUNT(*)
            FROM LogAggiornamenti
            WHERE preferito = 1
            AND (privato = 0 OR created_by = ?)
        """, (user_id,))

        count = cur.fetchone()[0]
        con.close()

        return count

    except Exception as e:
        print(f"[ERROR] Errore conteggio preferiti: {e}")
        return 0


def count_reminder_events(user_id):
    """
    Conta gli eventi con reminder attivi per l'utente corrente.

    Returns:
        int: Numero di eventi con reminder
    """
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("""
            SELECT COUNT(*)
            FROM LogAggiornamenti
            WHERE data_reminder IS NOT NULL
            AND data_reminder != ''
            AND (privato = 0 OR created_by = ?)
        """, (user_id,))

        count = cur.fetchone()[0]
        con.close()

        return count

    except Exception as e:
        print(f"[ERROR] Errore conteggio reminder: {e}")
        return 0


# =================================================================
# >>> EXCEL EXPORT & USER PRESETS
# =================================================================

def get_export_data(filtri: dict, includi_gemma: bool = False):
    """
    Recupera dati prodotti per export Excel con filtri applicati.

    Args:
        filtri: dict con chiavi:
            - distributori: list[str] - lista distributori selezionati
            - tipologia: str - tipo prodotto (Film, Serie, etc.)
            - data_da: str - data evento minima (YYYY-MM-DD)
        includi_gemma: bool - Include prodotti da Gemma

    Returns:
        list[dict]: Lista di prodotti con tutti i campi necessari per Excel export
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cur = con.cursor()

        # Build query dinamica
        query_parts = []
        params = []

        # Query base MediaTrack
        query_mt = """
            SELECT DISTINCT
                a.id_media,
                'MediaTrack' as fonte,
                a.titolo_ufficiale as titolo,
                a.tipo,
                a.distributore_nome as distributore,
                o.anno_produzione,
                a.formato,
                a.piattaforma,
                l.dettaglio_contesto as contesto,
                l.data_evento,
                l.costo_richiesto_ita as ask_ita,
                l.costo_richiesto_spa as ask_spa,
                l.delivery,
                l.note,
                o.imdb_rating as rating_imdb,
                o.generi as genere,
                NULL as v_rda, NULL as v_c5, NULL as v_i1, NULL as v_r4,
                NULL as v_las, NULL as v_i2, NULL as v_iris, NULL as v_top,
                NULL as v_foc, NULL as v_c20, NULL as v_c34, NULL as v_27,
                l.id_aggiornamento  -- Per ordinamento (ultimo evento)
            FROM AnagraficaMedia a
            LEFT JOIN (
                SELECT id_media_fk, dettaglio_contesto, data_evento, costo_richiesto_ita,
                       costo_richiesto_spa, delivery, note, id_aggiornamento,
                       ROW_NUMBER() OVER (PARTITION BY id_media_fk ORDER BY id_aggiornamento DESC) as rn
                FROM LogAggiornamenti
                WHERE 1=1
        """

        # Aggiungi filtro data_da alla subquery eventi
        if filtri.get('data_da'):
            query_mt += " AND data_evento >= ?"
            params.append(filtri['data_da'])

        query_mt += """
            ) l ON a.id_media = l.id_media_fk AND l.rn = 1
            LEFT JOIN OmdbData o ON a.id_media = o.id_media_fk
            WHERE 1=1
              AND a.deleted_at IS NULL
        """

        # Filtri WHERE MediaTrack
        if filtri.get('distributori'):
            placeholders = ','.join(['?' for _ in filtri['distributori']])
            query_mt += f" AND a.distributore_nome IN ({placeholders})"
            params.extend(filtri['distributori'])

        if filtri.get('tipologia'):
            query_mt += " AND a.tipo = ?"
            params.append(filtri['tipologia'])

        if filtri.get('contesto'):
            query_mt += " AND l.dettaglio_contesto = ?"
            params.append(filtri['contesto'])

        query_parts.append(query_mt)

        # Query MediaTrack (SQLite)
        final_query = query_parts[0]
        final_query += " ORDER BY id_aggiornamento DESC NULLS LAST"

        print(f"[EXPORT] Query MT: {final_query}")
        print(f"[EXPORT] Params MT: {params}")

        cur.execute(final_query, params)
        rows = cur.fetchall()
        con.close()
        products = [dict(row) for row in rows]
        print(f"[EXPORT] MediaTrack: {len(products)} prodotti")

        # Gemma (se includi_gemma=True e nessun filtro contesto — Gemma non ha eventi)
        print(f"[EXPORT] includi_gemma parameter: {includi_gemma} (type: {type(includi_gemma)})")
        if includi_gemma and not filtri.get('contesto'):
            print("[EXPORT] Aggiunta Gemma via DuckDB")
            gemma_where_parts = []
            if filtri.get('distributori'):
                dist_list = "', '".join(d.replace("'", "''") for d in filtri['distributori'])
                gemma_where_parts.append(f"A_DISTRIBUTORE IN ('{dist_list}')")
            if filtri.get('tipologia'):
                tip_esc = filtri['tipologia'].replace("'", "''")
                gemma_where_parts.append(f"A_TIPOLOGIA = '{tip_esc}'")
            gemma_where = ' AND '.join(gemma_where_parts) if gemma_where_parts else None
            gdf_exp = _gemma_df(
                where=gemma_where,
                columns='A_ID, A_TITOLO, A_TIPOLOGIA, A_DISTRIBUTORE, A_EPISODIO, V_RDA, V_C5, V_I1, V_R4, V_LA5, V_I2, V_IRIS, V_TOP, V_FOC, V_C20, V_CI34, V_C27'
            )
            if not gdf_exp.empty:
                for _, r in gdf_exp.iterrows():
                    products.append({
                        'id_media': 'G_' + str(r['A_ID']),
                        'fonte': 'Gemma',
                        'titolo': r.get('A_TITOLO'),
                        'tipo': r.get('A_TIPOLOGIA'),
                        'distributore': r.get('A_DISTRIBUTORE'),
                        'anno_produzione': None,
                        'formato': r.get('A_EPISODIO'),
                        'piattaforma': None,
                        'contesto': None,
                        'data_evento': None,
                        'ask_ita': None,
                        'ask_spa': None,
                        'delivery': None,
                        'note': None,
                        'rating_imdb': None,
                        'genere': r.get('A_TIPOLOGIA'),
                        'v_rda': r.get('V_RDA'), 'v_c5': r.get('V_C5'), 'v_i1': r.get('V_I1'),
                        'v_r4': r.get('V_R4'), 'v_las': r.get('V_LA5'), 'v_i2': r.get('V_I2'),
                        'v_iris': r.get('V_IRIS'), 'v_top': r.get('V_TOP'), 'v_foc': r.get('V_FOC'),
                        'v_c20': r.get('V_C20'), 'v_c34': r.get('V_CI34'), 'v_c27': r.get('V_C27'),
                        'id_aggiornamento': None,
                    })
            print(f"[EXPORT] Gemma aggiunti: {len(gdf_exp) if not gdf_exp.empty else 0} prodotti")

        print(f"[EXPORT] Trovati {len(products)} prodotti totali")
        return products

    except Exception as e:
        print(f"[ERROR] get_export_data: {e}")
        import traceback
        traceback.print_exc()
        return []


def get_user_export_presets(user_id):
    """
    Recupera tutti i preset export di un utente.

    Args:
        user_id: ID utente

    Returns:
        list[dict]: Lista preset con tutti i campi
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cur = con.cursor()

        cur.execute("""
            SELECT id_preset, nome_preset, filtri_json, includi_gemma,
                   is_default, data_creazione, data_ultimo_uso
            FROM UserExportPresets
            WHERE user_id = ?
            ORDER BY is_default DESC, data_ultimo_uso DESC NULLS LAST, nome_preset ASC
        """, (user_id,))

        rows = cur.fetchall()
        con.close()

        presets = [dict(row) for row in rows]
        return presets

    except Exception as e:
        print(f"[ERROR] get_user_export_presets: {e}")
        return []


def get_export_preset_by_id(preset_id, user_id):
    """
    Recupera un preset specifico (con verifica ownership).

    Args:
        preset_id: ID preset
        user_id: ID utente (per verifica ownership)

    Returns:
        dict or None: Dati preset o None se non trovato/non autorizzato
    """
    try:
        con = _conn()
        con.row_factory = sqlite3.Row
        cur = con.cursor()

        cur.execute("""
            SELECT id_preset, nome_preset, filtri_json, includi_gemma,
                   is_default, data_creazione, data_ultimo_uso
            FROM UserExportPresets
            WHERE id_preset = ? AND user_id = ?
        """, (preset_id, user_id))

        row = cur.fetchone()
        con.close()

        return dict(row) if row else None

    except Exception as e:
        print(f"[ERROR] get_export_preset_by_id: {e}")
        return None


def create_export_preset(user_id, nome_preset, filtri_json, includi_gemma, is_default=False):
    """
    Crea un nuovo preset export.

    Args:
        user_id: ID utente
        nome_preset: Nome descrittivo del preset
        filtri_json: JSON string con filtri (distributori, tipologia, data_da)
        includi_gemma: bool - Include Gemma products
        is_default: bool - Marca come preset di default

    Returns:
        tuple: (success: bool, message: str, preset_id: int or None)
    """
    try:
        con = _conn()
        cur = con.cursor()

        # Se is_default=True, rimuovi default da altri preset dell'utente
        if is_default:
            cur.execute("""
                UPDATE UserExportPresets
                SET is_default = 0
                WHERE user_id = ? AND is_default = 1
            """, (user_id,))

        # Inserisci nuovo preset
        cur.execute("""
            INSERT INTO UserExportPresets (user_id, nome_preset, filtri_json, includi_gemma, is_default)
            VALUES (?, ?, ?, ?, ?)
        """, (user_id, nome_preset, filtri_json, includi_gemma, is_default))

        preset_id = cur.lastrowid
        con.commit()
        con.close()

        print(f"[PRESET] Creato preset '{nome_preset}' (ID: {preset_id}) per user {user_id}")
        return True, "Preset salvato con successo", preset_id

    except Exception as e:
        print(f"[ERROR] create_export_preset: {e}")
        return False, f"Errore salvataggio preset: {str(e)}", None


def update_export_preset(preset_id, user_id, nome_preset=None, filtri_json=None,
                        includi_gemma=None, is_default=None):
    """
    Aggiorna un preset esistente (con verifica ownership).

    Args:
        preset_id: ID preset
        user_id: ID utente (per verifica ownership)
        nome_preset: Nuovo nome (optional)
        filtri_json: Nuovi filtri JSON (optional)
        includi_gemma: Nuovo valore include Gemma (optional)
        is_default: Nuovo valore default (optional)

    Returns:
        tuple: (success: bool, message: str)
    """
    try:
        con = _conn()
        cur = con.cursor()

        # Verifica ownership
        cur.execute("SELECT user_id FROM UserExportPresets WHERE id_preset = ?", (preset_id,))
        row = cur.fetchone()
        if not row:
            con.close()
            return False, "Preset non trovato"
        if row[0] != user_id:
            con.close()
            return False, "Non autorizzato"

        # Se is_default=True, rimuovi default da altri preset
        if is_default:
            cur.execute("""
                UPDATE UserExportPresets
                SET is_default = 0
                WHERE user_id = ? AND id_preset != ? AND is_default = 1
            """, (user_id, preset_id))

        # Costruisci query dinamica
        updates = []
        params = []

        if nome_preset is not None:
            updates.append("nome_preset = ?")
            params.append(nome_preset)

        if filtri_json is not None:
            updates.append("filtri_json = ?")
            params.append(filtri_json)

        if includi_gemma is not None:
            updates.append("includi_gemma = ?")
            params.append(includi_gemma)

        if is_default is not None:
            updates.append("is_default = ?")
            params.append(is_default)

        # Aggiorna data_ultimo_uso
        updates.append("data_ultimo_uso = CURRENT_TIMESTAMP")

        if not updates:
            con.close()
            return False, "Nessun campo da aggiornare"

        params.append(preset_id)
        query = f"UPDATE UserExportPresets SET {', '.join(updates)} WHERE id_preset = ?"

        cur.execute(query, params)
        con.commit()
        con.close()

        print(f"[PRESET] Aggiornato preset ID {preset_id} per user {user_id}")
        return True, "Preset aggiornato con successo"

    except Exception as e:
        print(f"[ERROR] update_export_preset: {e}")
        return False, f"Errore aggiornamento preset: {str(e)}"


def delete_export_preset(preset_id, user_id):
    """
    Elimina un preset (con verifica ownership).

    Args:
        preset_id: ID preset
        user_id: ID utente (per verifica ownership)

    Returns:
        tuple: (success: bool, message: str)
    """
    try:
        con = _conn()
        cur = con.cursor()

        # Verifica ownership ed elimina
        cur.execute("""
            DELETE FROM UserExportPresets
            WHERE id_preset = ? AND user_id = ?
        """, (preset_id, user_id))

        if cur.rowcount == 0:
            con.close()
            return False, "Preset non trovato o non autorizzato"

        con.commit()
        con.close()

        print(f"[PRESET] Eliminato preset ID {preset_id} per user {user_id}")
        return True, "Preset eliminato con successo"

    except Exception as e:
        print(f"[ERROR] delete_export_preset: {e}")
        return False, f"Errore eliminazione preset: {str(e)}"


def update_preset_last_used(preset_id, user_id):
    """
    Aggiorna data_ultimo_uso di un preset (per ordinamento)

    Args:
        preset_id: ID preset
        user_id: ID utente (per verifica ownership)

    Returns:
        bool: Success
    """
    try:
        con = _conn()
        cur = con.cursor()

        cur.execute("""
            UPDATE UserExportPresets
            SET data_ultimo_uso = CURRENT_TIMESTAMP
            WHERE id_preset = ? AND user_id = ?
        """, (preset_id, user_id))

        con.commit()
        con.close()

        return True

    except Exception as e:
        print(f"[ERROR] update_preset_last_used: {e}")
        return False