"""
GemmaReport — Report settimanale GEMMA
Genera Excel con i prodotti entrati in GEMMA nella settimana corrente e invia via Outlook.

Logica filtri:
  1. A_Data compresa nella finestra settimanale (mercoledì precedente → martedì)
  2. A_Tipologia NOT IN (DOCUMENTARI, FILM, INTRATTENIMENTO LEGGERO)
  3. PAESE <> 'I' (escludi prodotti italiani — da prodotti.parquet via imdb.parquet)

Evidenziazione Excel:
  - Riga gialla = prodotto con IMDB non ancora presente in osservatorio.parquet

Sorgenti: gemma.parquet + prodotti.parquet + imdb.parquet + osservatorio.parquet
"""

import argparse
import json
from datetime import datetime, timedelta
from pathlib import Path

import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment
from openpyxl.utils import get_column_letter
from loguru import logger

BASE_DIR = Path(__file__).parent


# ---------------------------------------------------------------------------
# Config
# ---------------------------------------------------------------------------

def load_config() -> dict:
    with open(BASE_DIR / "config.json", encoding="utf-8") as f:
        return json.load(f)


# ---------------------------------------------------------------------------
# Finestra temporale
# ---------------------------------------------------------------------------

def get_date_window() -> tuple:
    """Finestra: mercoledì precedente → martedì (giorno prima dell'esecuzione)."""
    today = datetime.now().date()
    end_date = today - timedelta(days=1)
    start_date = today - timedelta(days=7)
    return datetime(start_date.year, start_date.month, start_date.day), \
           datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59)


# ---------------------------------------------------------------------------
# Parquet L2
# ---------------------------------------------------------------------------

def get_gemma_data(cfg: dict, start_date: datetime, end_date: datetime) -> pd.DataFrame:
    base = cfg["parquet_base_path"]

    df = pd.read_parquet(f"{base}/gemma.parquet", columns=[
        "A_DATA", "A_TIPOLOGIA", "A_TITOLO", "A_DISTRIBUTORE",
        "A_COD_IMDB", "A_AUTORE_REGISTA", "A_CAST",
    ])
    df = df.rename(columns={
        "A_DATA": "A_Data", "A_TIPOLOGIA": "A_Tipologia", "A_TITOLO": "A_Titolo",
        "A_DISTRIBUTORE": "A_Distributore", "A_COD_IMDB": "imdb",
        "A_AUTORE_REGISTA": "A_Autore_Regista", "A_CAST": "A_Cast",
    })

    df["A_Data"] = pd.to_datetime(df["A_Data"], errors="coerce", dayfirst=True)
    df = df[(df["A_Data"] >= pd.Timestamp(start_date)) & (df["A_Data"] <= pd.Timestamp(end_date))]
    df = df[~df["A_Tipologia"].isin(["DOCUMENTARI", "FILM", "INTRATTENIMENTO LEGGERO", "CORTOMETRAGGIO"])]
    df = df.sort_values(["A_Data", "A_Titolo"]).reset_index(drop=True)
    logger.info(f"GEMMA: {len(df)} righe nel periodo {start_date.date()} → {end_date.date()}")

    # PAESE da prodotti.parquet via imdb.parquet
    df_imdb = pd.read_parquet(f"{base}/imdb.parquet")
    df_prod = pd.read_parquet(f"{base}/prodotti.parquet", columns=["prodotto", "paesi_produzione1"])
    df_paese = (
        df_imdb.merge(df_prod, left_on="codice", right_on="prodotto", how="left")
        [["riferimento_imdb", "paesi_produzione1"]]
        .rename(columns={"riferimento_imdb": "imdb", "paesi_produzione1": "PAESE"})
        .drop_duplicates(subset=["imdb"])
    )
    df = df.merge(df_paese, on="imdb", how="left")

    # Escludi italiani
    n0 = len(df)
    df = df[df["PAESE"].isna() | (df["PAESE"] != "I")]
    logger.debug(f"Dopo filtro PAESE: {len(df)} (rimossi {n0 - len(df)})")
    df = df.drop(columns=["PAESE"])

    # Flag: IMDB presente in osservatorio?
    df_osserv = pd.read_parquet(f"{base}/osservatorio.parquet", columns=["imdb"])
    osserv_imdbs = set(df_osserv["imdb"].dropna().unique())
    logger.info(f"osservatorio.parquet: {len(osserv_imdbs)} IMDB presenti")

    df["_highlight"] = (
        df["imdb"].notna() &
        (df["imdb"] != "no imdb") &
        ~df["imdb"].isin(osserv_imdbs)
    )

    return df.reset_index(drop=True)


# ---------------------------------------------------------------------------
# Generazione Excel
# ---------------------------------------------------------------------------

HEADERS = ["DATA", "TIPOLOGIA", "TITOLO", "DISTRIBUTORE", "REGISTA", "CAST", "IMDB"]
COL_MAP = ["A_Data", "A_Tipologia", "A_Titolo", "A_Distributore", "A_Autore_Regista", "A_Cast", "imdb"]
COL_WIDTHS = [12, 16, 42, 32, 26, 36, 14]
YELLOW_HEADER = PatternFill(start_color="FFFFCC", end_color="FFFFCC", fill_type="solid")
YELLOW_ROW    = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
FONT_NAME = "Aptos Narrow"


def generate_excel(df: pd.DataFrame, start_date: datetime, end_date: datetime) -> Path:
    output_dir = BASE_DIR / "output"
    output_dir.mkdir(exist_ok=True)
    output_path = output_dir / f"GEMMA_{datetime.now().strftime('%Y%m%d')}.xlsx"

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "GEMMA"

    # Riga 1: titolo con periodo
    ws.merge_cells("A1:G1")
    title_cell = ws["A1"]
    title_cell.value = f"GEMMA — {start_date.strftime('%d/%m/%Y')} → {end_date.strftime('%d/%m/%Y')}"
    title_cell.font = Font(name=FONT_NAME, bold=True, size=14)
    title_cell.fill = YELLOW_HEADER
    title_cell.alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[1].height = 22

    # Riga 2: intestazioni + autofilter
    for col, header in enumerate(HEADERS, 1):
        cell = ws.cell(row=2, column=col, value=header)
        cell.font = Font(name=FONT_NAME, bold=True, size=9)
        cell.fill = YELLOW_HEADER
        cell.alignment = Alignment(horizontal="center", vertical="center")
    ws.auto_filter.ref = "A2:G2"
    ws.row_dimensions[2].height = 14

    # Larghezze colonne
    for i, width in enumerate(COL_WIDTHS, 1):
        ws.column_dimensions[get_column_letter(i)].width = width

    # Dati (riga 3+)
    for row_idx, row in df.iterrows():
        excel_row = row_idx + 3
        highlight = bool(row.get("_highlight", False))
        for col_idx, col_name in enumerate(COL_MAP, 1):
            value = row[col_name]
            if pd.isna(value):
                value = None
            cell = ws.cell(row=excel_row, column=col_idx, value=value)
            cell.font = Font(name=FONT_NAME, size=9)
            if highlight:
                cell.fill = YELLOW_ROW
            if col_idx == 1 and value is not None:
                cell.number_format = "DD/MM/YYYY"

    wb.save(output_path)
    logger.info(f"Excel generato: {output_path.name} ({len(df)} righe)")
    return output_path


# ---------------------------------------------------------------------------
# Invio email
# ---------------------------------------------------------------------------

def send_email(excel_path: "Path | None", cfg: dict, n_rows: int):
    import win32com.client

    email_cfg = cfg["email"]
    subject = email_cfg["subject"].replace("{date}", datetime.now().strftime("%d/%m/%Y"))
    if excel_path is None:
        body = email_cfg.get("body_empty", "Ciao a tutti,\nnon ci sono prodotti da segnalare per questa settimana.\n\nMauro")
    else:
        body = email_cfg.get("body", f"Report GEMMA allegato ({n_rows} nuovi prodotti).")
    recipients = "; ".join(email_cfg["to"])

    outlook = win32com.client.Dispatch("Outlook.Application")
    mail = outlook.CreateItem(0)
    mail.Subject = subject
    mail.Body = body
    mail.To = recipients
    if email_cfg.get("cc"):
        mail.CC = "; ".join(email_cfg["cc"])
    if excel_path is not None:
        mail.Attachments.Add(str(excel_path.resolve()))
    mail.Send()
    logger.info(f"Email inviata — {'con allegato' if excel_path else 'senza allegato'} — TO: {len(email_cfg['to'])}, CC: {len(email_cfg.get('cc', []))}")


# ---------------------------------------------------------------------------
# Entry point
# ---------------------------------------------------------------------------

def run(dry_run: bool = False):
    logs_dir = BASE_DIR / "logs"
    logs_dir.mkdir(exist_ok=True)
    logger.add(
        logs_dir / "gemma_report_{time:YYYY-MM-DD}.log",
        rotation="7 days",
        retention="30 days",
        level="DEBUG",
    )

    mode = "[DRY-RUN] " if dry_run else ""
    logger.info(f"=== GemmaReport avviato {mode}===")

    cfg = load_config()

    start_date, end_date = get_date_window()
    logger.info(f"Finestra: {start_date.date()} → {end_date.date()}")

    df = get_gemma_data(cfg, start_date, end_date)
    logger.info(f"Prodotti nel report: {len(df)} (di cui {df['_highlight'].sum()} con IMDB non in Osservatorio)")

    if df.empty:
        logger.info("Nessun prodotto questa settimana — invio email vuota")
        if not dry_run:
            send_email(None, cfg, 0)
        else:
            logger.info("[DRY-RUN] Email vuota NON inviata")
        logger.info(f"=== GemmaReport completato {mode}===")
        return

    excel_path = generate_excel(df, start_date, end_date)

    if dry_run:
        logger.info(f"[DRY-RUN] Email NON inviata — Excel generato in: {excel_path}")
        logger.info(f"[DRY-RUN] Destinatari TO: {cfg['email']['to']}")
        logger.info(f"[DRY-RUN] Destinatari CC: {cfg['email'].get('cc', [])}")
    else:
        send_email(excel_path, cfg, len(df))

    logger.info(f"=== GemmaReport completato {mode}===")


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--dry-run", action="store_true", help="Genera Excel ma non invia email")
    args = parser.parse_args()
    run(dry_run=args.dry_run)