"""
Export dati da Parquet a MS Access (v2)
"""
import shutil
import logging
from pathlib import Path
import pyodbc

logger = logging.getLogger(__name__)

# Colonne Access IMDB (11 — OTT_EMESSO escluso, non nello schema template)
_ACCESS_COLUMNS = [
    'IMDB_CODICE', 'TIPOL', 'TI', 'TO', 'ANNO', 'DUR',
    'GENERE', 'REGISTA', 'CAST', 'VOTO', 'VOTANTI',
]


class AccessExporter:
    """Gestisce export da Parquet a MS Access."""

    def __init__(self):
        pass

    def create_access_db(self, template_path: Path, output_path: Path):
        """
        Crea database Access copiando template vuoto.
        """
        logger.info(f"Copia template Access: {template_path.name} -> {output_path.name}")

        output_path.parent.mkdir(parents=True, exist_ok=True)

        if output_path.exists():
            output_path.unlink()

        shutil.copy2(template_path, output_path)
        logger.info(f"Database Access creato: {output_path}")

    def export_parquet_to_access(
        self,
        parquet_path: Path,
        access_db_path: Path,
        table_name: str = "IMDB"
    ):
        """
        Esporta imdb.parquet in Access via pandas + TransferText.
        DELETE + import CSV preserva struttura e indici del template.
        """
        import tempfile, duckdb, win32com.client
        logger.info(f"Export Parquet -> Access: {access_db_path}")

        tmp      = Path(tempfile.gettempdir()).resolve()
        csv_path = tmp / f'_imdb_{table_name}.csv'
        ini_path = tmp / 'schema.ini'

        try:
            # 1. Svuota tabella (mantiene struttura/indici del template)
            conn_str = (
                'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
                f'DBQ={access_db_path};'
            )
            with pyodbc.connect(conn_str, autocommit=True) as conn:
                conn.execute(f'DELETE * FROM [{table_name}]')

            # 2. Parquet → CSV via DuckDB
            parquet_str = str(parquet_path).replace('\\', '/')
            col_str = ', '.join(f'"{c}"' for c in _ACCESS_COLUMNS)
            con = duckdb.connect()
            total = con.execute(f"SELECT COUNT(*) FROM '{parquet_str}'").fetchone()[0]
            con.execute(f"""
                COPY (SELECT {col_str} FROM '{parquet_str}')
                TO '{csv_path.as_posix()}'
                (FORMAT CSV, HEADER true, DELIMITER ',')
            """)
            con.close()
            ini_path.write_text(
                f'[{csv_path.name}]\nCharacterSet=UTF-8\nFormat=Delimited(,)\nColNameHeader=True\n',
                encoding='utf-8'
            )

            # 3. CSV → Access via TransferText
            app = win32com.client.Dispatch('Access.Application')
            app.Visible = False
            try:
                app.OpenCurrentDatabase(str(access_db_path))
                app.DoCmd.TransferText(0, None, table_name, str(csv_path), True)
                app.CloseCurrentDatabase()
            finally:
                app.Quit()
                del app

        finally:
            for f in (csv_path, ini_path):
                if f.exists():
                    try:
                        f.unlink()
                    except Exception:
                        pass

        logger.info(f"Export completato: {total:,} record in {table_name}")

    def compact_access_db(self, db_path: Path):
        """Compatta db_split_IMDB.accdb via DAO (nessuna password)."""
        import win32com.client
        compact_tmp = db_path.with_suffix('.compact.accdb')
        logger.info(f"Compattamento {db_path.name}...")
        try:
            dao = win32com.client.Dispatch('DAO.DBEngine.120')
            dao.CompactDatabase(str(db_path), str(compact_tmp), '', 0, '')
            del dao
            if compact_tmp.exists():
                db_path.unlink()
                compact_tmp.rename(db_path)
                logger.info(f"  Compattamento completato")
        except Exception as e:
            logger.warning(f"  Compattamento fallito (non bloccante): {e}")
            if compact_tmp.exists():
                compact_tmp.unlink()

    def copy_to_destinations(self, source_db: Path, destinations: list):
        """
        Copia database Access in multiple destinazioni.
        """
        logger.info(f"Copia {source_db.name} in {len(destinations)} destinazioni")

        for dest in destinations:
            try:
                dest_path = Path(dest)
                dest_path.mkdir(parents=True, exist_ok=True)
                dest_file = dest_path / source_db.name
                shutil.copy2(source_db, dest_file)
                logger.info(f"  Copiato in: {dest_file}")
            except Exception as e:
                logger.warning(f"  Errore copia in {dest}: {e}")

    def enrich_ott_in_access(self, access_db_path: Path):
        """
        Aggiorna OTT_EMESSO in IMDB tramite tabella OTT linkata nel template Access.
        Usa l'ultimo provider SVOD per ogni titolo (MAX data inizio).

        UPDATE ... INNER JOIN non è supportato via ODBC su Access:
        si legge OTT con SELECT semplice, si deduplica in Python,
        poi si aggiorna IMDB con executemany UPDATE parametrico.
        """
        logger.info("Arricchimento OTT_EMESSO via tabella linkata OTT...")
        try:
            conn_str = (
                r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
                f'DBQ={access_db_path};'
            )
            conn = pyodbc.connect(conn_str, autocommit=False)
            cur = conn.cursor()

            # Leggi SVOD ordinati per data DESC — nessun JOIN, compatibile ODBC
            rows = cur.execute("""
                SELECT Qlik_Imdb_id, QliK_Provider
                FROM OTT
                WHERE [QliK_Tipo Finestra] = 'SVOD'
                ORDER BY Qlik_Imdb_id, [Qlik_Data Inizio] DESC
            """).fetchall()

            # Tieni solo il record più recente per imdb_id (primo nella lista DESC)
            seen = set()
            updates = []
            for imdb_id, provider in rows:
                if imdb_id not in seen:
                    seen.add(imdb_id)
                    updates.append((provider, imdb_id))

            logger.info(f"  Titoli OTT SVOD distinti: {len(updates):,}")

            CHUNK = 5000
            for i in range(0, len(updates), CHUNK):
                cur.executemany(
                    "UPDATE IMDB SET OTT_EMESSO = ? WHERE IMDB_CODICE = ?",
                    updates[i:i + CHUNK]
                )
                conn.commit()
            cur.close()
            conn.close()
            logger.info(f"  OTT_EMESSO aggiornato: {len(updates):,} record")
        except pyodbc.Error as e:
            logger.warning(f"  Arricchimento OTT fallito (non bloccante): {e}")

    def copy_to_gemma_loader(self, parquet_path: Path,
                              gemma_db_path: str, password: str = None):
        """
        Copia tabella IMDB in GemmaLoader.accdb via csv_transfer:
        1. DuckDB scrive CSV da parquet (locale)
        2. shutil.copy2 GemmaLoader → locale (bypass Access lock)
        3. Access.Application importa CSV in GemmaLoader locale (TransferText)
        3b. CompactRepair GemmaLoader locale (ottimizzazione)
        4. shutil.copy2 locale → GemmaLoader
        Opzionale — fallisce silenziosamente se GemmaLoader non raggiungibile.
        """
        import tempfile, win32com.client, duckdb
        tmp = Path(tempfile.gettempdir()).resolve()
        local_gemma  = tmp / 'GemmaLoader_local.accdb'
        compact_path = tmp / 'GemmaLoader_compact.accdb'
        csv_path     = tmp / '_imdb_gemma.csv'
        ini_path     = tmp / 'schema.ini'
        logger.info(f"Copia IMDB in GemmaLoader: {gemma_db_path}")
        try:
            # 1. Scrivi CSV da parquet
            parquet_str = str(parquet_path).replace('\\', '/')
            col_str = ', '.join(f'"{c}"' for c in _ACCESS_COLUMNS)
            con = duckdb.connect()
            con.execute(f"""
                COPY (SELECT {col_str} FROM '{parquet_str}')
                TO '{csv_path.as_posix()}'
                (FORMAT CSV, HEADER true, DELIMITER ',')
            """)
            con.close()
            ini_path.write_text(
                f'[{csv_path.name}]\nCharacterSet=UTF-8\nFormat=Delimited(,)\nColNameHeader=True\n',
                encoding='utf-8'
            )

            # 2. Copia GemmaLoader in locale (OS-level, bypass Access lock)
            logger.info("  Download GemmaLoader in locale...")
            shutil.copy2(gemma_db_path, local_gemma)

            # 3. Importa CSV in GemmaLoader locale via Access.Application
            logger.info("  Aggiornamento IMDB locale (csv_transfer)...")
            app = win32com.client.Dispatch('Access.Application')
            app.Visible = False
            try:
                if password:
                    app.OpenCurrentDatabase(str(local_gemma), False, password)
                else:
                    app.OpenCurrentDatabase(str(local_gemma))
                try:
                    app.CurrentDb().TableDefs.Delete('IMDB')
                except Exception:
                    pass
                app.DoCmd.TransferText(0, None, 'IMDB', str(csv_path), True)
                app.CloseCurrentDatabase()
            finally:
                app.Quit()
                del app

            # 3b. Compact & Repair GemmaLoader locale via DAO (supporta password)
            logger.info("  Compattamento GemmaLoader locale...")
            try:
                dao = win32com.client.Dispatch('DAO.DBEngine.120')
                pwd_locale = f';pwd={password}' if password else ''
                dao.CompactDatabase(str(local_gemma), str(compact_path), pwd_locale, 0, pwd_locale)
                del dao
                if compact_path.exists():
                    local_gemma.unlink()
                    compact_path.rename(local_gemma)
                    logger.info("  Compattamento completato")
            except Exception as e:
                logger.warning(f"  Compattamento fallito (non bloccante): {e}")

            # 4. Ricopia in rete
            logger.info("  Upload GemmaLoader in rete...")
            shutil.copy2(local_gemma, gemma_db_path)
            logger.info("  Copia GemmaLoader completata")

        except Exception as e:
            logger.warning(f"  Copia GemmaLoader fallita (non bloccante): {e}")
        finally:
            for f in (local_gemma, compact_path, csv_path, ini_path):
                if f.exists():
                    try:
                        f.unlink()
                    except Exception:
                        pass