# --- FILE AGGIORNATO: containers/dashboards/dash_em_pivot/queries.py ---
"""
Query specifiche e AUTONOME per la Dashboard Pivot.
Contiene le sue query e copie di quelle condivise.
"""
import pandas as pd
from typing import Dict, Any, List, Tuple
from collections import defaultdict
from loguru import logger

from app.database.connection import db_manager

# --- Query specifica per questa dashboard (invariata) ---
def get_pivot_emesso_data(filters: Dict[str, Any]) -> pd.DataFrame:
    """
    Query per i dati della tabella pivot Tipologia vs Anno.
    """
    base_query = """
    SELECT
        e.tipologia,
        year(CAST(e.data_emissione AS DATE)) as anno_emissione,
        COUNT(*) as numero_emissioni,
        SUM(CAST(e.durata_lorda AS REAL)) as durata_lorda_totale,
        SUM(CAST(e.durata_netta AS REAL)) as durata_netta_totale
    FROM emesso e
    LEFT JOIN emesso_enrich ee
        ON e.rete = ee.rete AND e.data_emissione = ee.data_emissione AND e.ora_inizio = ee.ora_inizio
    WHERE e.tipologia IS NOT NULL AND e.tipologia != ''
    """
    conditions, params = [], []

    if filters.get('reti'):
        placeholders = ', '.join('?' for _ in filters['reti'])
        conditions.append(f"AND e.rete IN ({placeholders})")
        params.extend(filters['reti'])
    if filters.get('data_inizio'):
        conditions.append("AND e.data_emissione >= ?")
        params.append(filters['data_inizio'])
    if filters.get('data_fine'):
        conditions.append("AND e.data_emissione <= ?")
        params.append(filters['data_fine'])
    if filters.get('fascia'):
        conditions.append("AND e.fascia = ?")
        params.append(filters['fascia'])
    if filters.get('unico_pr'):
        conditions.append("AND ee.is_primetime_main = 1")
    if filters.get('prima_visione'):
        conditions.append("AND ee.prima_visione_recalc = ?")
        params.append(filters['prima_visione'])
    if filters.get('fornitore_cluster'):
        conditions.append("AND ee.fornitore_cluster = ?")
        params.append(filters['fornitore_cluster'])
    if filters.get('tipologia') and filters['tipologia'] != 'Tutte':
        conditions.append("AND e.tipologia = ?")
        params.append(filters['tipologia'])

    if conditions:
        base_query += " " + " ".join(conditions)
    base_query += " GROUP BY e.tipologia, anno_emissione ORDER BY e.tipologia, anno_emissione"
    
    return db_manager.execute_query(base_query, tuple(params))

# --- MODIFICA CHIAVE: Query di dettaglio standardizzata ---
def get_dettaglio_emissioni(filters: Dict[str, Any]) -> pd.DataFrame:
    """Query per il dettaglio emissioni."""
    base_query = """
    SELECT
        e.rete, e.data_emissione, e.ora_inizio, e.ora_fine, e.durata_lorda, e.durata_netta, ee.prima_visione_recalc AS prima_visione,
        e.fascia, e.audience, e.share, p.titolo_italiano, p.titolo_originale,
        p.anno_produzione, p.paesi_produzione1, p.genere1, ee.fornitore_diritto, e.prodotto
    FROM emesso e
    LEFT JOIN prodotti p ON e.prodotto = p.prodotto AND e.edizione = p.edizione
    LEFT JOIN emesso_enrich ee
        ON e.rete = ee.rete AND e.data_emissione = ee.data_emissione AND e.ora_inizio = ee.ora_inizio
    WHERE 1=1
    """
    conditions, params = [], []
    if filters.get('reti'):
        placeholders = ', '.join('?' for _ in filters['reti'])
        conditions.append(f"AND e.rete IN ({placeholders})")
        params.extend(filters['reti'])
    if filters.get('data_inizio'):
        conditions.append("AND e.data_emissione >= ?")
        params.append(filters['data_inizio'])
    if filters.get('data_fine'):
        conditions.append("AND e.data_emissione <= ?")
        params.append(filters['data_fine'])
    if filters.get('fascia'):
        conditions.append("AND e.fascia = ?")
        params.append(filters['fascia'])
    if filters.get('tipologia'):
        conditions.append("AND e.tipologia = ?")
        params.append(filters['tipologia'])
    if filters.get('unico_pr'):
        conditions.append("AND ee.is_primetime_main = 1")
    if filters.get('prima_visione'):
        conditions.append("AND ee.prima_visione_recalc = ?")
        params.append(filters['prima_visione'])
    if filters.get('fornitore_cluster'):
        conditions.append("AND ee.fornitore_cluster = ?")
        params.append(filters['fornitore_cluster'])
    
    if conditions: base_query += " " + " ".join(conditions)
    limit = int(filters.get('limit', 5000))
    base_query += f" ORDER BY e.data_emissione DESC, e.ora_inizio DESC LIMIT {limit}"
    return db_manager.execute_query(base_query, tuple(params))

def get_network_clusters() -> Tuple[Dict[str, List[str]], Dict[str, str]]:
    """Legge la tabella reti_cluster."""
    query = "SELECT cluster_rete, rete_6chr, codice_rete FROM reti_cluster WHERE cluster_rete IS NOT NULL AND cluster_rete != ''"
    try:
        df = db_manager.execute_query(query)
        clusters = defaultdict(list)
        name_to_code_map = {}
        for _, row in df.iterrows():
            clusters[row['cluster_rete']].append(row['rete_6chr'])
            name_to_code_map[row['rete_6chr']] = row['codice_rete']
        final_clusters = {name: sorted(list(set(nets))) for name, nets in clusters.items()}
        return final_clusters, name_to_code_map
    except Exception as e:
        logger.error(f"Impossibile caricare i cluster delle reti: {e}", exc_info=True)
        return {}, {}

def get_available_filters() -> Dict[str, List[str]]:
    """Ottiene tutti i valori disponibili per i filtri dal DB."""
    filters = {}
    filters['reti'] = db_manager.execute_query("SELECT DISTINCT rete FROM emesso WHERE rete IS NOT NULL ORDER BY rete")['rete'].tolist()
    filters['tipologie'] = db_manager.execute_query("SELECT DISTINCT tipologia FROM emesso WHERE tipologia IS NOT NULL AND tipologia != '' ORDER BY tipologia")['tipologia'].tolist()
    
    fasce_db = db_manager.execute_query("SELECT DISTINCT fascia FROM emesso WHERE fascia IS NOT NULL ORDER BY fascia")['fascia'].tolist()
    if 'PR' in fasce_db:
        fasce_db.insert(fasce_db.index('PR') + 1, 'PR (uno solo per serata)')
    filters['fasce'] = fasce_db

    query_fornitori = "SELECT DISTINCT fornitore_cluster FROM emesso_enrich WHERE fornitore_cluster IS NOT NULL AND fornitore_cluster != '' ORDER BY fornitore_cluster"
    filters['fornitori_cluster'] = db_manager.execute_query(query_fornitori)['fornitore_cluster'].tolist()
    
    return filters