"""
ReportReti — data layer.
Queries gemma.parquet for affiliated distributors, enriches with OmdbData.
"""
import sqlite3
import logging
from datetime import date
from pathlib import Path
from typing import Optional
import duckdb

logger = logging.getLogger(__name__)

DISTRIBUTORS = {
    'SIC CONTENT DISTRIBUTION':           'Portogallo',
    'Seven One Studios International':    'Germania',
    'Mediterráneo Mediaset España Group': 'Spagna',
}

# Same V_* columns as Linker sheet + INF
V_COLS = [
    'V_RDA', 'V_C5',  'V_I1',  'V_R4',  'V_LA5',
    'V_I2',  'V_IRIS','V_TOP', 'V_FOC', 'V_C20',
    'V_CI34','V_C27', 'V_INF',
]
V_LABELS = [c[2:] for c in V_COLS]  # strip 'V_'

_OMDB_COLS = [
    'codice_imdb', 'titolo_omdb', 'sinossi', 'anno_produzione',
    'durata', 'generi', 'registi', 'attori', 'premi', 'url_poster',
    'imdb_rating', 'imdb_voti', 'metascore',
]
_OMDB_SELECT = """SELECT codice_imdb, titolo_omdb, sinossi,
                         anno_produzione, durata, generi, registi, attori,
                         premi, url_poster, imdb_rating, imdb_voti, metascore
                  FROM OmdbData"""

_V_SELECT = ',\n            '.join(
    f"COALESCE(CAST({c} AS VARCHAR), '') AS {c}" for c in V_COLS
)
_HAS_VAL = ' OR '.join(
    f"(COALESCE(CAST({c} AS VARCHAR), '') NOT IN ('!', ''))" for c in V_COLS
)


def _gemma_base_select() -> str:
    return f"""
        CAST(A_ID AS VARCHAR)                            AS gemma_id,
        COALESCE(CAST(A_TITOLO AS VARCHAR), '')          AS titolo,
        COALESCE(CAST(A_COD_IMDB AS VARCHAR), '')        AS cod_imdb,
        COALESCE(CAST(A_DATA AS VARCHAR), '')             AS data_gem,
        COALESCE(CAST(A_DISTRIBUTORE AS VARCHAR), '')     AS distributore,
        COALESCE(CAST(A_AUTORE_REGISTA AS VARCHAR), '')   AS regia_gem,
        COALESCE(CAST(A_CAST AS VARCHAR), '')             AS cast_gem,
        COALESCE(CAST(A_TIPOLOGIA AS VARCHAR), '')        AS tipologia_gem,
        COALESCE(CAST(A_ACQUISTATO_VENDUTO AS VARCHAR), '') AS acq_vend,
        {_V_SELECT}
    """


_MEDIASET_RETI = {
    'B5','B6','BP','C5','FU','I1','I2','KA','KB','KI','KQ','LA','LB','LT','R4','TS',
}


def _enrich_with_emesso(titles: list, parquet_dir: Path) -> None:
    """Attach most recent emission data to each title in-place."""
    ids = [t['gemma_id'] for t in titles if t['gemma_id']]
    if not ids:
        return
    emesso_f = str(parquet_dir / 'emesso.parquet').replace('\\', '/')
    ids_sql = ', '.join(ids)
    sql = f"""
        SELECT CAST(prodotto AS VARCHAR) AS gemma_id,
               rete, data_emissione, ora_inizio, fascia, audience, share
        FROM read_parquet('{emesso_f}')
        WHERE prodotto IN ({ids_sql})
        QUALIFY ROW_NUMBER() OVER (
            PARTITION BY prodotto
            ORDER BY data_emissione DESC NULLS LAST, ora_inizio DESC NULLS LAST
        ) = 1
    """
    try:
        rows = duckdb.connect().execute(sql).fetchall()
        em_map = {
            r[0]: {'rete': r[1], 'data': r[2], 'ora': r[3],
                   'fascia': r[4], 'audience': r[5], 'share': r[6]}
            for r in rows
        }
    except Exception as e:
        logger.warning(f'[SisterCo] emesso join error: {e}')
        em_map = {}
    for t in titles:
        em = em_map.get(t['gemma_id'], {})
        rete = em.get('rete', '')
        t['em_rete']        = rete
        t['em_concorrenza'] = bool(rete and rete not in _MEDIASET_RETI)
        t['em_data']        = em.get('data', '')
        t['em_ora']         = em.get('ora')
        t['em_fascia']      = em.get('fascia', '')
        t['em_audience']    = em.get('audience')
        t['em_share']       = em.get('share')


def _enrich_with_omdb(titles: list, mediatrack_db: Path) -> None:
    """Attach omdb dict to each title in-place."""
    imdb_codes = [
        t['cod_imdb'] for t in titles
        if t['cod_imdb'] and t['cod_imdb'].lower() not in ('no imdb', '', 'n/a')
    ]
    omdb_map: dict = {}
    if imdb_codes and mediatrack_db.exists():
        try:
            con = sqlite3.connect(str(mediatrack_db))
            placeholders = ','.join('?' for _ in imdb_codes)
            rows = con.execute(
                f"{_OMDB_SELECT} WHERE codice_imdb IN ({placeholders})",
                imdb_codes,
            ).fetchall()
            con.close()
            for row in rows:
                d = dict(zip(_OMDB_COLS, row))
                omdb_map[d['codice_imdb']] = d
        except Exception as e:
            logger.warning(f'[SisterCo] OMDB join error: {e}')
    for t in titles:
        t['omdb'] = omdb_map.get(t['cod_imdb'], {})


def fetch_titles(parquet_dir: Path, mediatrack_db: Path,
                 date_from: date, date_to: date) -> dict:
    """
    Returns {'Germania': [...], 'Spagna': [...], 'Portogallo': [...]}
    Each entry is a dict with title data + 'omdb' sub-dict.
    Only titles with at least one V_* evaluated (not '!' and not empty).
    """
    gemma_f  = str(parquet_dir / 'gemma.parquet').replace('\\', '/')
    distr_sql = ', '.join(f"'{k}'" for k in DISTRIBUTORS)

    sql = f"""
        SELECT {_gemma_base_select()}
        FROM read_parquet('{gemma_f}') g
        WHERE A_DISTRIBUTORE IN ({distr_sql})
          AND ({_HAS_VAL})
          AND TRY_CAST(
                strptime(CAST(A_DATA AS VARCHAR), '%d/%m/%Y') AS DATE
              ) BETWEEN DATE '{date_from.isoformat()}' AND DATE '{date_to.isoformat()}'
        ORDER BY A_DISTRIBUTORE,
                 TRY_CAST(strptime(CAST(A_DATA AS VARCHAR), '%d/%m/%Y') AS DATE)
                 DESC NULLS LAST
    """
    try:
        rows = duckdb.connect().execute(sql).fetchall()
    except Exception as e:
        logger.error(f'[SisterCo] DuckDB query error: {e}')
        raise

    col_names = ['gemma_id', 'titolo', 'cod_imdb', 'data_gem', 'distributore', 'regia_gem', 'cast_gem', 'tipologia_gem', 'acq_vend'] + V_COLS
    titles = [dict(zip(col_names, r)) for r in rows]
    _enrich_with_omdb(titles, mediatrack_db)
    _enrich_with_emesso(titles, parquet_dir)

    result: dict = {'Germania': [], 'Spagna': [], 'Portogallo': []}
    for t in titles:
        paese = DISTRIBUTORS.get(t['distributore'])
        if paese:
            t['paese'] = paese
            result[paese].append(t)

    return result


def fetch_title_by_id(parquet_dir: Path, mediatrack_db: Path,
                      gemma_id: str) -> Optional[dict]:
    """Returns a single fully-enriched title dict, or None if not found."""
    gemma_f = str(parquet_dir / 'gemma.parquet').replace('\\', '/')

    sql = f"""
        SELECT {_gemma_base_select()}
        FROM read_parquet('{gemma_f}') g
        WHERE CAST(A_ID AS VARCHAR) = '{gemma_id}'
        LIMIT 1
    """
    try:
        rows = duckdb.connect().execute(sql).fetchall()
    except Exception as e:
        logger.error(f'[SisterCo] fetch_title_by_id error: {e}')
        return None

    if not rows:
        return None

    col_names = ['gemma_id', 'titolo', 'cod_imdb', 'data_gem', 'distributore', 'regia_gem', 'cast_gem', 'tipologia_gem', 'acq_vend'] + V_COLS
    t = dict(zip(col_names, rows[0]))
    _enrich_with_omdb([t], mediatrack_db)
    t['paese'] = DISTRIBUTORS.get(t['distributore'], '')
    return t