unknown
320 lines
10 KiB
Python
320 lines
10 KiB
Python
"""
|
||
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,
|
||
} |