"""
Query per la tab "Diritti in Scadenza".
Utilizza CUSTOM_DIRITTI e CUSTOM_ANAGR_ORIZ (via ParquetCatalog).
"""

import datetime
import logging

import pandas as pd

from app.modules.linker.src.database.parquet_db import ParquetCatalog

logger = logging.getLogger(__name__)

# Nomi alternativi VEG FREE — stesso set usato in linker_engine
VEG_COL_CANDIDATES = ['VEG_FREE', 'VEG FREE', 'VEGFREE', 'VEG-FREE', 'VEG_FREE_', 'VEG']


def _safe_rifer(val) -> str:
    if val is None or (isinstance(val, float) and pd.isna(val)):
        return ''
    s = str(val).strip()
    if s.endswith('.0'):
        s = s[:-2]
    return s


class DiritiDatabase:
    """Wrapper query per la tab Diritti in Scadenza."""

    def __init__(self, catalog: ParquetCatalog):
        self.catalog = catalog

    def get_filter_options(self) -> dict:
        anno = datetime.date.today().year
        anni = list(range(anno - 1, anno + 6))
        tipologie = self.catalog.get_tipologie_diritti() or \
            ['FILM', 'TV MOVIE', 'TELEFILM', 'MINISERIE', 'SIT COM', 'SOAP', 'TELENOVELAS']
        distributori = self.catalog.get_distributori()
        return {'tipologie': tipologie, 'anni': anni, 'distributori': distributori}

    def get_diritti_rifer(self, tipologie=None) -> list:
        return self.catalog.get_diritti_rifer(tipologie)

    def get_anagr_and_diritti(self, rifer_set: set) -> pd.DataFrame:
        """
        Carica CUSTOM_ANAGR_ORIZ e filtra per i RIFER di interesse.
        RIFER presenti in CUSTOM_DIRITTI ma assenti da CUSTOM_ANAGR_ORIZ
        vengono aggiunti come righe stub (solo colonna RIFER).
        """
        try:
            df = self.catalog.get_all_custom()
            logger.info(f"CUSTOM_ANAGR_ORIZ caricata: {len(df)} righe totali")

            df['_RIFER_STR'] = df['RIFER'].apply(_safe_rifer)
            df_match = df[df['_RIFER_STR'].isin(rifer_set)].copy()

            # Deduplicazione equivalente al GROUP BY VBA (PROD + IMDB_CODICE)
            imdb_col = 'IMDB_CODICE'
            if imdb_col in df_match.columns:
                before = len(df_match)
                df_match = df_match.drop_duplicates(
                    subset=['_RIFER_STR', imdb_col], keep='first'
                )
                if len(df_match) < before:
                    logger.info(
                        f"Diritti: deduplicazione (RIFER,IMDB) "
                        f"{before} → {len(df_match)} righe"
                    )

            # LEFT JOIN: RIFER in CUSTOM_DIRITTI ma assenti da CUSTOM_ANAGR_ORIZ
            matched_rifers = set(df_match['_RIFER_STR'])
            missing = rifer_set - matched_rifers
            if missing:
                logger.info(
                    f"Diritti LEFT JOIN: {len(missing)} RIFER senza corrispondenza "
                    f"in CUSTOM_ANAGR_ORIZ → righe stub aggiunte "
                    f"(es: {sorted(missing)[:5]})"
                )
                stub = pd.DataFrame([{'RIFER': r} for r in missing])
                df_match = pd.concat([df_match, stub], ignore_index=True)

            df_match = df_match.drop(columns=['_RIFER_STR'])

            logger.info(
                f"Diritti: {len(rifer_set)} RIFER da CUSTOM_DIRITTI → "
                f"{len(df_match) - len(missing)} righe ANAGR "
                f"+ {len(missing)} stub = {len(df_match)} righe totali"
            )
            return df_match
        except Exception as e:
            logger.error(f"Errore get_anagr_and_diritti: {e}")
            return pd.DataFrame()