"""
Estadisticas Jugadores CRUD Operations

Functions to compute aggregated data for chart display using live User data
and satellite tables (UserProfile, UserSports, etc.).
"""

from sqlalchemy.orm import Session
from sqlalchemy import func
from datetime import datetime, date
from collections import Counter
from .user import (
    User, UserProfile, UserSports, UserHealth, 
    UserMarketing, UserConsent, UserGuardians, UserFinance
)

# Constantes de Etiquetas (Mismos diccionarios que antes para mantener compatibilidad)
LABELS_FOUND_US = {
    'boca-a-boca': 'Boca a boca', 'youtube': 'YouTube', 'instagram': 'Instagram',
    'facebook': 'Facebook', 'tiktok': 'TikTok', 'web-google': 'Web / Google',
}
LABELS_CATEGORIA = {
    'prebenjamin': 'Prebenjamín', 'benjamín': 'Benjamín', 'benjamin': 'Benjamín',
    'alevín': 'Alevín', 'alevin': 'Alevín', 'infantil': 'Infantil',
    'cadete': 'Cadete', 'juvenil': 'Juvenil', 'senior': 'Senior',
}
LABELS_PIERNA = { 'derecha': 'Derecha', 'izquierda': 'Izquierda' }
LABELS_SEXO = { 'masculino': 'Masculino', 'femenino': 'Femenino' }
LABELS_OCUPACION = { 'estudio': 'Estudia', 'trabajo': 'Trabaja', 'ambos': 'Estudia y trabaja' }
LABELS_ENTRENAMIENTO = { 'academy': 'L4F Academy', 'personal': 'Personal', 'ambos': 'Ambos' }
LABELS_ALCOHOL = {
    'muy-seguido': 'Muy seguido', 'casualmente': 'Casualmente',
    'ocasiones-especiales': 'Ocasiones especiales', 'no-bebo': 'No bebe',
}
LABELS_TABACO = {
    'muy-seguido': 'Muy seguido', 'casualmente': 'Casualmente',
    'ocasiones-especiales': 'Ocasiones especiales', 'no-fumo': 'No fuma',
}
LABELS_SI_NO = { 'si': 'Sí', 'no': 'No' }
LABELS_TRABAJO_FISICO = { 'si': 'Sí', 'no': 'No', 'no-lesionado': 'No se ha lesionado' }
LABELS_RECUPERACION = { 'si': 'Le cuesta centrarse', 'no': 'Se reactiva bien' }


def crear_estadistica(db: Session, **kwargs):
    """
    Legacy support: Keeps the interface but does nothing or could log.
    Now data is sourced directly from User profile/satellites.
    """
    pass


def _count_by_field(db: Session, model, column, labels=None) -> dict:
    """Helper to count grouped by a specific column in a model."""
    if column is None:
        return {}
        
    rows = (
        db.query(column, func.count(column))
        .join(User, User.id == model.user_id) # Ensure we only count existing users
        .filter(User.role == 'alumno')        # Only count students for stats
        .filter(column.isnot(None), column != '')
        .group_by(column)
        .all()
    )
    
    data = {str(val): count for val, count in rows}
    if labels:
        return {labels.get(k, k): v for k, v in data.items()}
    return data


def _calcular_edades(db: Session) -> dict:
    """Calculate age distribution based on UserProfile.birth_date"""
    rows = (
        db.query(UserProfile.birth_date)
        .join(User, User.id == UserProfile.user_id)
        .filter(User.role == 'alumno')
        .filter(UserProfile.birth_date.isnot(None))
        .filter(UserProfile.birth_date != '')
        .all()
    )
    
    rangos = Counter()
    today = date.today()
    
    for (fecha_str,) in rows:
        try:
            # Try ISO format (YYYY-MM-DD)
            # Some data might be full datetime string or date string
            clean_date = fecha_str.strip()[:10]
            birth = datetime.strptime(clean_date, '%Y-%m-%d').date()
            age = today.year - birth.year - ((today.month, today.day) < (birth.month, birth.day))
            
            if age < 10:
                rangos['Menos de 10'] += 1
            elif age <= 12:
                rangos['10-12'] += 1
            elif age <= 15:
                rangos['13-15'] += 1
            elif age <= 18:
                rangos['16-18'] += 1
            else:
                rangos['19+'] += 1
        except (ValueError, AttributeError):
            continue
    
    return dict(rangos)


def obtener_estadisticas_agregadas(db: Session) -> dict:
    """
    Compute aggregated statistics for charts using live user data.
    """
    # Total Active Students
    total = db.query(User).filter(User.role == 'alumno').count()
    
    return {
        'total_jugadores': total,
        
        # UserSports
        'posiciones': _count_by_field(db, UserSports, UserSports.position),
        'categorias': _count_by_field(db, UserSports, UserSports.category, LABELS_CATEGORIA),
        'pierna_dominante': _count_by_field(db, UserSports, UserSports.dominant_foot, LABELS_PIERNA),
        'tipo_entrenamiento': _count_by_field(db, UserSports, UserSports.training_type, LABELS_ENTRENAMIENTO),
        
        # UserProfile
        'edades': _calcular_edades(db),
        'ciudades': _count_by_field(db, UserProfile, UserProfile.city),
        'sexo': _count_by_field(db, UserProfile, UserProfile.gender, LABELS_SEXO),
        
        # UserGuardians (Note: occupation is stored here for some reason in legacy form)
        'ocupacion': _count_by_field(db, UserGuardians, UserGuardians.occupation, LABELS_OCUPACION),
        
        # UserMarketing
        'como_nos_encontro': _count_by_field(db, UserMarketing, UserMarketing.found_us, LABELS_FOUND_US),
        'nervios_confianza': _count_by_field(db, UserMarketing, UserMarketing.nerves_confidence, LABELS_SI_NO),
        'disfrute': _count_by_field(db, UserMarketing, UserMarketing.enjoyment, LABELS_SI_NO),
        
        # UserHealth
        'alcohol': _count_by_field(db, UserHealth, UserHealth.alcohol, LABELS_ALCOHOL),
        'tabaco': _count_by_field(db, UserHealth, UserHealth.smoking, LABELS_TABACO),
        'trabajo_fisico': _count_by_field(db, UserHealth, UserHealth.physical_work, LABELS_TRABAJO_FISICO),
        'dolor_pecho': _count_by_field(db, UserHealth, UserHealth.chest_pain, LABELS_SI_NO),
        'recuperacion': _count_by_field(db, UserHealth, UserHealth.recovery, LABELS_RECUPERACION),
        'malestar': _count_by_field(db, UserHealth, UserHealth.discomfort, LABELS_SI_NO),
        
        # UserConsent
        'permiso_video': _count_by_field(db, UserConsent, UserConsent.video_permission, LABELS_SI_NO),
        'contenido_whatsapp': _count_by_field(db, UserConsent, UserConsent.whatsapp_content, LABELS_SI_NO),
    }