Api-DataSentinel/routers/monitorings.py

# ============================================================
#  routers/monitorings.py — Nomenclature et données détaillées
#
#  Chaque monitoring dispose d'une table MONITO_[NOM] dédiée,
#  résolue via le mapping MONITO_TABLES (domain.py).
# ============================================================

from typing import Optional

from fastapi import APIRouter, Depends, HTTPException, Query, status
from pydantic import BaseModel, Field

from auth import get_current_user, require_admin
from config import get_cursor
from domain import AuditAction, SEARCH_COLS
from helpers import get_table_name, journaliser, row_to_dict, rows_to_list, serialize_row

router = APIRouter(dependencies=[Depends(get_current_user)], tags=["Monitorings"])


class MonitoringBody(BaseModel):
    monito_intitule: str = Field(min_length=1, max_length=200)
    id_service: int
    id_categorie: int
    table_source: str = Field(min_length=1, max_length=100)
    bdd_source: str = Field(min_length=1, max_length=50)


class MonitoringUpdate(BaseModel):
    monito_intitule: Optional[str] = Field(default=None, min_length=1, max_length=200)
    id_service: Optional[int] = None
    id_categorie: Optional[int] = None
    table_source: Optional[str] = Field(default=None, min_length=1, max_length=100)
    bdd_source: Optional[str] = Field(default=None, min_length=1, max_length=50)
    actif: Optional[bool] = None


@router.get("")
def get_monitorings(
    id_service   : Optional[int] = Query(None, description="Filtrer par service"),
    id_categorie : Optional[int] = Query(None, description="Filtrer par catégorie"),
):
    """Liste des monitorings actifs avec table_source et bdd_source."""
    query  = (
        "SELECT id_monito, monito_intitule, id_service, id_categorie, "
        "table_source, bdd_source "
        "FROM NOMENCLATURE_MONITO WHERE actif = 1"
    )
    params = []

    if id_service:
        query += " AND id_service = ?"
        params.append(id_service)
    if id_categorie:
        query += " AND id_categorie = ?"
        params.append(id_categorie)

    query += " ORDER BY id_monito"

    with get_cursor() as cursor:
        cursor.execute(query, *params)
        return rows_to_list(cursor, cursor.fetchall())


@router.post("", status_code=status.HTTP_201_CREATED)
def create_monitoring(body: MonitoringBody, admin: dict = Depends(require_admin)):
    """
    Déclare un monitoring dans la nomenclature.

    Attention : cela n'ajoute que l'entrée de référentiel. Pour qu'il remonte
    des données, il faut aussi créer la table MONITO_[NOM] correspondante et
    l'ajouter au mapping MONITO_TABLES — voir l'en-tête de data_sentinel_init.sql.
    """
    with get_cursor() as cursor:
        cursor.execute(
            "INSERT INTO NOMENCLATURE_MONITO "
            "(monito_intitule, id_service, id_categorie, table_source, bdd_source, actif) "
            "OUTPUT INSERTED.id_monito VALUES (?, ?, ?, ?, ?, 1)",
            body.monito_intitule, body.id_service, body.id_categorie,
            body.table_source, body.bdd_source
        )
        id_monito = cursor.fetchone()[0]
        journaliser(cursor, admin, AuditAction.CREATE_REFERENTIEL,
                    f"NOMENCLATURE_MONITO {id_monito} : {body.monito_intitule}")
    return {"id_monito": id_monito, **body.model_dump()}


@router.put("/{id_monito}")
def update_monitoring(id_monito: int, body: MonitoringUpdate, admin: dict = Depends(require_admin)):
    champs = {
        "monito_intitule": body.monito_intitule,
        "id_service":      body.id_service,
        "id_categorie":    body.id_categorie,
        "table_source":    body.table_source,
        "bdd_source":      body.bdd_source,
        "actif":           None if body.actif is None else int(body.actif),
    }
    modifications = {col: val for col, val in champs.items() if val is not None}
    if not modifications:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
                            detail="Aucun champ à modifier")

    affectations = ", ".join(f"{col} = ?" for col in modifications)
    params = [*modifications.values(), id_monito]

    with get_cursor() as cursor:
        cursor.execute(
            f"UPDATE NOMENCLATURE_MONITO SET {affectations} WHERE id_monito = ?", *params
        )
        if cursor.rowcount == 0:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                                detail=f"Monitoring {id_monito} introuvable.")
        journaliser(cursor, admin, AuditAction.UPDATE_REFERENTIEL,
                    f"NOMENCLATURE_MONITO {id_monito}")
    return {"status": "updated", "id_monito": id_monito}


@router.delete("/{id_monito}")
def desactiver_monitoring(id_monito: int, admin: dict = Depends(require_admin)):
    """
    Désactivation (actif = 0), pas de suppression physique : TABLE_FINAL
    référence id_monito et l'historique doit rester consultable.
    """
    with get_cursor() as cursor:
        cursor.execute("UPDATE NOMENCLATURE_MONITO SET actif = 0 WHERE id_monito = ?", id_monito)
        if cursor.rowcount == 0:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                                detail=f"Monitoring {id_monito} introuvable.")
        journaliser(cursor, admin, AuditAction.DELETE_REFERENTIEL,
                    f"NOMENCLATURE_MONITO {id_monito} désactivé")
    return {"status": "deactivated", "id_monito": id_monito}


@router.get("/{id_monito}")
def get_monitoring_by_id(id_monito: int):
    """Détail d'un monitoring."""
    with get_cursor() as cursor:
        cursor.execute(
            "SELECT id_monito, monito_intitule, id_service, id_categorie, "
            "table_source, bdd_source "
            "FROM NOMENCLATURE_MONITO WHERE id_monito = ? AND actif = 1",
            id_monito
        )
        row = cursor.fetchone()
        if not row:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                                detail=f"Monitoring {id_monito} introuvable.")
        return row_to_dict(cursor, row)


@router.get("/{id_monito}/details")
def get_monitoring_details(
    id_monito : int,
    search    : Optional[str] = Query(
        None,
        description="Recherche sur colonnes texte (ct_intitule, agence, STE...)"
    ),
    limit     : int = Query(500,  ge=1, le=5000, description="Lignes max — mettre 5000 pour export CSV complet"),
    offset    : int = Query(0,    ge=0,           description="Offset pagination"),
):
    """
    Retourne toutes les lignes en erreur depuis la table MONITO_[NOM] dédiée.
    Toutes les colonnes métier sont exposées → utilisable pour export CSV côté frontend.
    """
    table  = get_table_name(id_monito)
    query  = f"SELECT * FROM {table} WHERE 1=1"
    params = []

    if search and table in SEARCH_COLS:
        cols       = SEARCH_COLS[table]
        conditions = " OR ".join([f"{col} LIKE ?" for col in cols])
        query     += f" AND ({conditions})"
        params.extend([f"%{search}%"] * len(cols))

    query += " ORDER BY date_extraction DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
    params += [offset, limit]

    with get_cursor() as cursor:
        cursor.execute(query, *params)
        rows = rows_to_list(cursor, cursor.fetchall())
        return [serialize_row(r) for r in rows]


@router.get("/{id_monito}/count")
def get_monitoring_count(id_monito: int):
    """Nombre d'erreurs dans la table dédiée du monitoring."""
    table = get_table_name(id_monito)
    with get_cursor() as cursor:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        return {
            "id_monito" : id_monito,
            "table"     : table,
            "nb_erreurs": cursor.fetchone()[0],
        }


@router.get("/{id_monito}/columns")
def get_monitoring_columns(id_monito: int):
    """
    Retourne les colonnes de la table dédiée.
    Permet au frontend de générer dynamiquement les en-têtes du tableau.
    """
    table = get_table_name(id_monito)
    with get_cursor() as cursor:
        cursor.execute(
            "SELECT COLUMN_NAME, DATA_TYPE "
            "FROM INFORMATION_SCHEMA.COLUMNS "
            "WHERE TABLE_NAME = ? ORDER BY ORDINAL_POSITION",
            table
        )
        return {
            "id_monito": id_monito,
            "table"    : table,
            "columns"  : rows_to_list(cursor, cursor.fetchall()),
        }