"""
Stats.py
Contiene la lógica de los endpoints utilizados para crear y consultar datos estadísticos.

"""


from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import func, cast, Integer, extract
from database import get_db
from typing import Optional
from datetime import datetime, timedelta
import models
from auth import get_current_user

router = APIRouter()


@router.get("/stats/", tags=["Stats"])
def get_stats(
    date_from: Optional[datetime] = None,
    date_to:   Optional[datetime] = None,
    db:        Session = Depends(get_db),
    current_user: str = Depends(get_current_user),
):
    """
    Devuelve todas las estadísticas agregadas en una sola llamada.
    Si no se pasan fechas, usa los últimos 30 días.
    """
    if date_to is None:
        date_to = datetime.utcnow()
    if date_from is None:
        date_from = date_to - timedelta(days=30)

    # ------------------------------------------------------------------
    # 1. Resumen ejecutivo
    # ------------------------------------------------------------------
    total_alertas       = db.query(models.Alert).filter(
        models.Alert.created_at >= date_from,
        models.Alert.created_at <= date_to
    ).count()

    alertas_abiertas    = db.query(models.Alert).filter(
        models.Alert.created_at >= date_from,
        models.Alert.created_at <= date_to,
        models.Alert.status == "open"
    ).count()

    alertas_en_progreso = db.query(models.Alert).filter(
        models.Alert.created_at >= date_from,
        models.Alert.created_at <= date_to,
        models.Alert.status == "in_progress"
    ).count()

    alertas_cerradas    = total_alertas - alertas_abiertas - alertas_en_progreso

    total_mensajes      = db.query(models.Message).filter(
        models.Message.date >= date_from,
        models.Message.date <= date_to
    ).count()

    grupos_activos      = db.query(models.Group).filter(
        models.Group.type != "P"
    ).count()

    reglas_activas      = db.query(models.Rule).filter(
        models.Rule.is_active == True
    ).count()

    # ------------------------------------------------------------------
    # 2. Alertas por día (para gráfico de línea)
    # ------------------------------------------------------------------
    alertas_por_dia_raw = (
        db.query(
            func.date(models.Alert.created_at).label("dia"),
            models.Alert.status,
            func.count(models.Alert.id).label("total")
        )
        .filter(
            models.Alert.created_at >= date_from,
            models.Alert.created_at <= date_to
        )
        .group_by(
            func.date(models.Alert.created_at),
            models.Alert.status
        )
        .order_by(func.date(models.Alert.created_at))
        .all()
    )

    alertas_por_dia = {}
    for row in alertas_por_dia_raw:
        dia = str(row.dia)
        if dia not in alertas_por_dia:
            alertas_por_dia[dia] = {"abiertas": 0, "cerradas": 0}
        if row.status == "open":
            alertas_por_dia[dia]["abiertas"] = row.total
        else:
            alertas_por_dia[dia]["cerradas"] = row.total

    # ------------------------------------------------------------------
    # 3. Distribución por severidad
    # ------------------------------------------------------------------
    sev_raw = (
        db.query(
            models.Rule.severity,
            func.count(models.Alert.id).label("total")
        )
        .join(models.Alert, models.Alert.rule_id == models.Rule.id)
        .filter(
            models.Alert.created_at >= date_from,
            models.Alert.created_at <= date_to
        )
        .group_by(models.Rule.severity)
        .all()
    )
    distribucion_severidad = {row.severity: row.total for row in sev_raw}

    # ------------------------------------------------------------------
    # 4. Alertas por grupo
    # ------------------------------------------------------------------
    alertas_por_grupo_raw = (
        db.query(
            models.Alert.group_id,
            func.count(models.Alert.id).label("total")
        )
        .filter(
            models.Alert.created_at >= date_from,
            models.Alert.created_at <= date_to
        )
        .group_by(models.Alert.group_id)
        .order_by(func.count(models.Alert.id).desc())
        .limit(10)
        .all()
    )

    # Resolver nombres de grupos
    group_ids = [r.group_id for r in alertas_por_grupo_raw]
    grupos_map = {
        g.id_telegram: g.name
        for g in db.query(models.Group).filter(
            models.Group.id_telegram.in_(group_ids)
        ).all()
    }
    alertas_por_grupo = [
        {
            "grupo":   grupos_map.get(r.group_id, str(r.group_id)),
            "total":   r.total
        }
        for r in alertas_por_grupo_raw
    ]

    # ------------------------------------------------------------------
    # 5. Reglas más disparadas (top 10)
    # ------------------------------------------------------------------
    reglas_top_raw = (
        db.query(
            models.Rule.id,
            models.Rule.description,
            models.Rule.severity,
            func.count(models.Alert.id).label("total")
        )
        .join(models.Alert, models.Alert.rule_id == models.Rule.id)
        .filter(
            models.Alert.created_at >= date_from,
            models.Alert.created_at <= date_to
        )
        .group_by(models.Rule.id, models.Rule.description, models.Rule.severity)
        .order_by(func.count(models.Alert.id).desc())
        .limit(10)
        .all()
    )
    reglas_top = [
        {
            "id":          row.id,
            "description": row.description,
            "severity":    row.severity,
            "total":       row.total
        }
        for row in reglas_top_raw
    ]

    # ------------------------------------------------------------------
    # 6. Volumen de mensajes por día
    # ------------------------------------------------------------------
    mensajes_por_dia_raw = (
        db.query(
            func.date(models.Message.date).label("dia"),
            func.count(models.Message.id_mess_g).label("total")
        )
        .filter(
            models.Message.date >= date_from,
            models.Message.date <= date_to
        )
        .group_by(func.date(models.Message.date))
        .order_by(func.date(models.Message.date))
        .all()
    )
    mensajes_por_dia = {str(row.dia): row.total for row in mensajes_por_dia_raw}

    # ------------------------------------------------------------------
    # 7. Heatmap: mensajes por hora × día de semana
    # ------------------------------------------------------------------
    # MariaDB usa DAYOFWEEK() (1=Dom..7=Sab) y HOUR() en lugar de extract("dow"/"hour")
    from sqlalchemy import text
    heatmap_raw = (
        db.query(
            func.dayofweek(models.Message.date).label("dia_semana"),
            func.hour(models.Message.date).label("hora"),
            func.count(models.Message.id_mess_g).label("total")
        )
        .filter(
            models.Message.date >= date_from,
            models.Message.date <= date_to
        )
        .group_by(
            func.dayofweek(models.Message.date),
            func.hour(models.Message.date)
        )
        .all()
    )

    # MariaDB DAYOFWEEK: 1=Dom, 2=Lun, ..., 7=Sáb
    DIAS = {1: "Dom", 2: "Lun", 3: "Mar", 4: "Mié", 5: "Jue", 6: "Vie", 7: "Sáb"}
    heatmap = [
        {
            "dia":   DIAS.get(int(row.dia_semana), str(row.dia_semana)),
            "hora":  int(row.hora),
            "total": row.total
        }
        for row in heatmap_raw
    ]

    # ------------------------------------------------------------------
    # 8. Top senders por actividad
    # ------------------------------------------------------------------
    top_senders_raw = (
        db.query(
            models.Message.sender_id,
            func.count(models.Message.id_mess_g).label("mensajes")
        )
        .filter(
            models.Message.date >= date_from,
            models.Message.date <= date_to
        )
        .group_by(models.Message.sender_id)
        .order_by(func.count(models.Message.id_mess_g).desc())
        .limit(10)
        .all()
    )

    sender_ids = [r.sender_id for r in top_senders_raw]
    senders_map = {
        s.id_telegram: s
        for s in db.query(models.Sender).filter(
            models.Sender.id_telegram.in_(sender_ids)
        ).all()
    }

    # Contar alertas por sender
    alertas_sender_raw = (
        db.query(
            models.Message.sender_id,
            func.count(models.Alert.id).label("alertas")
        )
        .join(models.Alert, (
            models.Alert.message_id == models.Message.id_mess_g) & (
            models.Alert.group_id   == models.Message.group_id)
        )
        .filter(
            models.Message.date >= date_from,
            models.Message.date <= date_to,
            models.Message.sender_id.in_(sender_ids)
        )
        .group_by(models.Message.sender_id)
        .all()
    )
    alertas_por_sender = {r.sender_id: r.alertas for r in alertas_sender_raw}

    top_senders = []
    for row in top_senders_raw:
        s       = senders_map.get(row.sender_id)
        nombre  = ""
        if s:
            nombre = f"{s.first_name or ''} {s.last_name or ''}".strip() or s.username or str(row.sender_id)
        else:
            nombre = str(row.sender_id)
        top_senders.append({
            "id_telegram": row.sender_id,
            "nombre":      nombre,
            "username":    s.username if s else None,
            "mensajes":    row.mensajes,
            "alertas":     alertas_por_sender.get(row.sender_id, 0),
        })

    # ------------------------------------------------------------------
    # Respuesta final
    # ------------------------------------------------------------------
    return {
        "periodo": {
            "desde": date_from.isoformat(),
            "hasta": date_to.isoformat(),
        },
        "resumen": {
            "total_alertas":    total_alertas,
            "alertas_abiertas": alertas_abiertas,
            "alertas_cerradas": alertas_cerradas,
            "total_mensajes":   total_mensajes,
            "grupos_activos":   grupos_activos,
            "reglas_activas":   reglas_activas,
        },
        "alertas_por_dia":       alertas_por_dia,
        "distribucion_severidad": distribucion_severidad,
        "alertas_por_grupo":     alertas_por_grupo,
        "reglas_top":            reglas_top,
        "mensajes_por_dia":      mensajes_por_dia,
        "heatmap":               heatmap,
        "top_senders":           top_senders,
    }