"""
ParquetToAccessWeekly — orchestratore principale.

Flusso:
1. Legge settings.json
2. Copia master DB → output_db
3. Costruisce DataFrame (CUSTOM_EMESSO, CUSTOM_ANAGR_ORIZ, EDIZIONI,
   SUPERSERIE, CUSTOM_DIRITTI, BOXOFFICE)
4. Scrive ogni tabella in Access via pyodbc (DELETE * + INSERT)
5. Pubblica output_db in tutte le destinazioni

Uso:
    python main.py [--dry-run]
"""
import argparse
import json
import logging
import shutil
import sys
from pathlib import Path

import pandas as pd

# -- WriteToAccess Lego brick ------------------------------------------------
_PIPELINE_DIR = Path(__file__).parent.parent
if str(_PIPELINE_DIR) not in sys.path:
    sys.path.insert(0, str(_PIPELINE_DIR))
from WriteToAccess import df_to_access

# -- core builders -----------------------------------------------------------
from core.custom_emesso     import build as build_emesso
from core.custom_anagrafica import build as build_anagrafica
from core.custom_diritti    import build as build_diritti
from core.boxoffice         import build as build_boxoffice

# ---------------------------------------------------------------------------
_SCRIPT_DIR = Path(__file__).parent
_SETTINGS   = _SCRIPT_DIR / 'config' / 'settings.json'
_TYFX_CSV   = str(_SCRIPT_DIR / 'config' / 'emesso_tyfx0160.csv')

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s  %(levelname)-8s  %(message)s',
    datefmt='%H:%M:%S',
    stream=sys.stdout,
)
log = logging.getLogger(__name__)


def _load_settings() -> dict:
    with open(_SETTINGS, encoding='utf-8') as f:
        cfg = json.load(f)
    # Risolvi master_db relativo alla directory dello script
    master = cfg['master_db']
    if not Path(master).is_absolute():
        cfg['master_db'] = str(_SCRIPT_DIR / master)
    return cfg


def _copy_master(cfg: dict, dry_run: bool) -> Path:
    src = Path(cfg['master_db'])
    dst = Path(cfg['output_db'])
    dst.parent.mkdir(parents=True, exist_ok=True)
    if not dry_run:
        shutil.copy2(src, dst)
        log.info('Master copiato: %s', dst)
    else:
        log.info('[dry-run] Copia master → %s', dst)
    return dst


def _write_table(db_path: Path, table: str, df: pd.DataFrame,
                 dry_run: bool) -> None:
    """Wrapper dry-run attorno al mattoncino df_to_access."""
    if df is None or df.empty:
        log.warning('  %s: DataFrame vuoto — salto', table)
        return
    if dry_run:
        log.info('  [dry-run] %s: %d righe', table, len(df))
        return
    log.info('  %s: %d righe...', table, len(df))
    n = df_to_access(df, db_path, table)
    log.info('  %s: importate %d righe', table, n)


def _compact(db_path: Path, dry_run: bool) -> None:
    """Compatta e ripara il DB Access (equivalente di Compact & Repair)."""
    if dry_run:
        log.info('[dry-run] Compact & Repair → %s', db_path)
        return
    tmp = db_path.with_suffix('.tmp.accdb')
    try:
        import win32com.client
        app = win32com.client.Dispatch('Access.Application')
        try:
            app.CompactRepair(str(db_path), str(tmp))
        finally:
            app.Quit()
            del app
        tmp.replace(db_path)
        log.info('Compact & Repair completato -> %s', db_path)
    except Exception as e:
        log.warning('Compact & Repair fallito (non bloccante): %s', e)
        if tmp.exists():
            tmp.unlink()


def _publish(cfg: dict, dry_run: bool) -> None:
    src = Path(cfg['output_db'])
    for entry in cfg.get('publish', []):
        dst = Path(entry['dest'])
        dst.parent.mkdir(parents=True, exist_ok=True)
        if dry_run:
            log.info('[dry-run] Pubblica → %s', dst)
        else:
            shutil.copy2(src, dst)
            log.info('Pubblicato -> %s', dst)


def run(dry_run: bool = False) -> None:
    cfg = _load_settings()
    parquet_dir = Path(cfg['parquet_dir'])
    db_warehouse = cfg.get('db_warehouse', '')

    # ------------------------------------------------------------------ #
    # 1. Copia master                                                      #
    # ------------------------------------------------------------------ #
    db_path = _copy_master(cfg, dry_run)

    # ------------------------------------------------------------------ #
    # 2. Build DataFrame                                                   #
    # ------------------------------------------------------------------ #
    log.info('[1/4] CUSTOM_EMESSO ...')
    df_emesso = build_emesso(parquet_dir, _TYFX_CSV, db_warehouse)
    log.info('      %d righe', len(df_emesso))

    log.info('[2/4] CUSTOM_ANAGR_ORIZ / EDIZIONI / SUPERSERIE ...')
    df_anagr, df_ediz, df_super = build_anagrafica(parquet_dir, _TYFX_CSV)
    log.info('      ANAGR=%d  EDIZ=%d  SUPER=%d',
             len(df_anagr), len(df_ediz), len(df_super))

    log.info('[3/4] CUSTOM_DIRITTI ...')
    df_diritti = build_diritti(parquet_dir, df_emesso)
    log.info('      %d righe', len(df_diritti))

    log.info('[4/4] BOXOFFICE ...')
    df_box = build_boxoffice(df_anagr)
    log.info('      %d righe', len(df_box))
    df_anagr = df_anagr.drop(columns=['BOX_STAGIONE'], errors='ignore')

    # ------------------------------------------------------------------ #
    # 3. Scrivi in Access                                                  #
    # ------------------------------------------------------------------ #
    tables = [
        ('CUSTOM_EMESSO',     df_emesso),
        ('CUSTOM_ANAGR_ORIZ', df_anagr),
        ('EDIZIONI',          df_ediz),
        ('SUPERSERIE',        df_super),
        ('CUSTOM_DIRITTI',    df_diritti),
        ('BOXOFFICE',         df_box),
    ]
    for tname, df in tables:
        _write_table(db_path, tname, df, dry_run)

    # ------------------------------------------------------------------ #
    # 4. Compact & Repair                                                  #
    # ------------------------------------------------------------------ #
    _compact(db_path, dry_run)

    # ------------------------------------------------------------------ #
    # 5. Pubblica                                                          #
    # ------------------------------------------------------------------ #
    _publish(cfg, dry_run)
    log.info('Completato.')


def main() -> None:
    parser = argparse.ArgumentParser(description='ParquetToAccessWeekly')
    parser.add_argument('--dry-run', action='store_true',
                        help='Mostra cosa farebbe senza scrivere nulla')
    parser.add_argument('--settings', default=None,
                        help='Path alternativo a settings.json')
    args = parser.parse_args()
    global _SETTINGS
    if args.settings:
        _SETTINGS = Path(args.settings)
    run(dry_run=args.dry_run)


if __name__ == '__main__':
    main()