"""
sqlite_mirror.py — esporta Layer 2 parquet → SQLite per uso con MS Access via ODBC.

Output: \\...PYTHON_SRV\sqlite_layer2\mirror_l2.sqlite
emesso / emesso_enrich : copia full (nessun filtro anno)
tutto il resto          : copia full da L2

Nota: la prima esecuzione scarica l'estensione duckdb-sqlite (~2 MB, serve internet).
Le esecuzioni successive usano la cache locale.

Uso: python sqlite_mirror.py
"""

import sys
import duckdb
from pathlib import Path

_L2   = Path(r"\\mediaset.it\share\Indirizzo_controllo_risorse\SOFTWARE\PYTHON_SRV\PYTHON_LOCAL\MyICR_Suite\local_db\parquet")
_SQLITE_DIR  = Path(r"\\mediaset.it\share\Indirizzo_controllo_risorse\SOFTWARE\PYTHON_SRV\sqlite_layer2")
_SQLITE_NAME = "mirror_l2.sqlite"


# tabelle copiate full da L2 (ordinate per dimensione crescente)
_FULL_TABLES = [
    "reti_cluster",
    "osservatorio",
    "imdb",
    "scelte_rete",
    "boxoffice",
    "prodotti",
    "diritti_enrich",
    "diritti",
    "cast",
    "gemma",
]

# tabelle senza PK intera usabile da Access ODBC: aggiunge _id INTEGER surrogato
_NEEDS_SURROGATE_ID = {
    "emesso", "emesso_enrich",
    "prodotti", "cast",
    "scelte_rete", "imdb",
    "gemma", "osservatorio", "reti_cluster",
}


def _pq(name: str) -> str:
    return str(_L2 / f"{name}.parquet").replace('\\', '/')


def run() -> None:
    _SQLITE_DIR.mkdir(parents=True, exist_ok=True)

    sqlite_file = _SQLITE_DIR / _SQLITE_NAME
    sqlite_path = str(sqlite_file).replace('\\', '/')

    print(f"SQLite output : {sqlite_file}")
    print()

    # ricrea da zero per evitare residui da run precedenti
    if sqlite_file.exists():
        sqlite_file.unlink()

    ok, failed = 0, []

    with duckdb.connect() as con:
        # carica estensione sqlite (auto-installa al primo uso)
        try:
            con.execute("LOAD sqlite")
        except Exception:
            print("  [setup] estensione sqlite non trovata, installo (richiede internet)...")
            con.execute("INSTALL sqlite")
            con.execute("LOAD sqlite")

        con.execute(f"ATTACH '{sqlite_path}' AS sl (TYPE SQLITE)")

        def _create(tname: str, where: str = "") -> None:
            nonlocal ok
            pq = _pq(tname)
            if not (_L2 / f"{tname}.parquet").exists():
                print(f"  SKIP  {tname} — non trovato in L2")
                return
            try:
                con.execute(f'DROP TABLE IF EXISTS sl."{tname}"')
                if tname in _NEEDS_SURROGATE_ID:
                    sel = f"ROW_NUMBER() OVER () AS _id, *"
                else:
                    sel = "*"
                where_clause = f"WHERE {where}" if where else ""
                con.execute(f'CREATE TABLE sl."{tname}" AS SELECT {sel} FROM \'{pq}\' {where_clause}')
                n = con.execute(f'SELECT COUNT(*) FROM sl."{tname}"').fetchone()[0]
                print(f"  OK    {tname:20s} {n:>10,} righe")
                ok += 1
            except Exception as e:
                print(f"  ERR   {tname} — {e}")
                failed.append(tname)

        # emesso e emesso_enrich — full (nessun filtro anno)
        for tname in ("emesso", "emesso_enrich"):
            _create(tname)

        # tabelle full
        for tname in _FULL_TABLES:
            _create(tname)

    print()
    size_mb = sqlite_file.stat().st_size / 1_048_576 if sqlite_file.exists() else 0
    print(f"sqlite_mirror: {ok} tabelle, {len(failed)} errori. Dimensione: {size_mb:.0f} MB")

    if failed:
        print(f"Tabelle fallite: {', '.join(failed)}")
        sys.exit(1)


if __name__ == "__main__":
    run()