src/api/monitoring/metrics.py

from src.api.monitoring.db import get_connection


def _apply_monitoring_filters(query, params, endpoint=None, time_window=None):
    """
    Applique les filtres endpoint et fenêtre temporelle à une requête SQL.
    """

    if endpoint and endpoint != "all":
        query += " AND endpoint = %s"
        params.append(endpoint.replace("/", ""))

    if time_window == "24h":
        query += " AND timestamp >= NOW() - INTERVAL '24 hours'"

    elif time_window == "7j":
        query += " AND timestamp >= NOW() - INTERVAL '7 days'"

    elif time_window == "30j":
        query += " AND timestamp >= NOW() - INTERVAL '30 days'"

    return query, params


def get_monitoring_summary():
    """
    Récupère les indicateurs globaux de monitoring.
    """

    query = """
    SELECT
        COUNT(*) AS total_requests,
        ROUND(AVG(latency_ms)::numeric, 2) AS avg_latency,
        SUM(CASE WHEN success = true THEN 1 ELSE 0 END) AS success_count,
        SUM(CASE WHEN success = false THEN 1 ELSE 0 END) AS error_count
    FROM api_requests;
    """

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query)
            row = cursor.fetchone()

    total_requests, avg_latency, success_count, error_count = row

    success_rate = 0

    if total_requests and total_requests > 0:
        success_rate = round((success_count / total_requests) * 100, 2)

    return {
        "total_requests": total_requests,
        "avg_latency_ms": float(avg_latency) if avg_latency is not None else 0,
        "success_count": success_count,
        "error_count": error_count,
        "success_rate": success_rate,
    }


def get_endpoint_usage(endpoint=None, time_window=None):
    """
    Récupère le nombre de requêtes par endpoint.
    """

    query = """
    SELECT
        endpoint,
        COUNT(*) AS total
    FROM api_requests
    WHERE 1=1
    """

    params = []

    query, params = _apply_monitoring_filters(
        query=query,
        params=params,
        endpoint=endpoint,
        time_window=time_window,
    )

    query += """
    GROUP BY endpoint
    ORDER BY total DESC;
    """

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "endpoint": row[0],
            "total": row[1],
        }
        for row in rows
    ]


def get_recent_requests(limit=20, endpoint=None, time_window=None):
    """
    Récupère les requêtes récentes.

    Si limit = 0, toutes les requêtes sont retournées.
    """

    query = """
    SELECT
        timestamp,
        endpoint,
        status_code,
        success,
        latency_ms,
        model_name
    FROM api_requests
    WHERE 1=1
    """

    params = []

    query, params = _apply_monitoring_filters(
        query=query,
        params=params,
        endpoint=endpoint,
        time_window=time_window,
    )

    query += """
    ORDER BY timestamp DESC
    """

    if limit and limit > 0:
        query += " LIMIT %s"
        params.append(limit)

    query += ";"

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "timestamp": row[0],
            "endpoint": row[1],
            "status_code": row[2],
            "success": row[3],
            "latency_ms": row[4],
            "model_name": row[5],
        }
        for row in rows
    ]


def get_latency_by_endpoint(endpoint=None, time_window=None):
    """
    Récupère les métriques de latence par endpoint.
    """

    query = """
    SELECT
        endpoint,
        COUNT(*) AS total_requests,
        ROUND(AVG(latency_ms)::numeric, 2) AS avg_latency_ms,
        ROUND(MIN(latency_ms)::numeric, 2) AS min_latency_ms,
        ROUND(MAX(latency_ms)::numeric, 2) AS max_latency_ms
    FROM api_requests
    WHERE 1=1
    """

    params = []

    query, params = _apply_monitoring_filters(
        query=query,
        params=params,
        endpoint=endpoint,
        time_window=time_window,
    )

    query += """
    GROUP BY endpoint
    ORDER BY AVG(latency_ms) DESC;
    """

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "endpoint": row[0],
            "total_requests": row[1],
            "avg_latency_ms": float(row[2]) if row[2] is not None else 0,
            "min_latency_ms": float(row[3]) if row[3] is not None else 0,
            "max_latency_ms": float(row[4]) if row[4] is not None else 0,
        }
        for row in rows
    ]


def get_inference_step_metrics():
    """
    Récupère les métriques de latence par étape d'inférence.
    """

    query = """
    SELECT
        step_name,
        COUNT(*) AS total_runs,
        ROUND(AVG(duration_ms)::numeric, 2) AS avg_duration_ms,
        ROUND(MIN(duration_ms)::numeric, 2) AS min_duration_ms,
        ROUND(MAX(duration_ms)::numeric, 2) AS max_duration_ms
    FROM inference_steps
    GROUP BY step_name
    ORDER BY avg_duration_ms DESC;
    """

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query)
            rows = cursor.fetchall()

    return [
        {
            "step_name": row[0],
            "total_runs": row[1],
            "avg_duration_ms": float(row[2]) if row[2] is not None else 0,
            "min_duration_ms": float(row[3]) if row[3] is not None else 0,
            "max_duration_ms": float(row[4]) if row[4] is not None else 0,
        }
        for row in rows
    ]


def get_recent_errors(limit=20, endpoint=None, time_window=None):
    """
    Récupère les erreurs récentes.

    Si limit = 0, toutes les erreurs sont retournées.
    """

    query = """
    SELECT
        timestamp,
        endpoint,
        status_code,
        error_message,
        latency_ms
    FROM api_requests
    WHERE success = false
    """

    params = []

    query, params = _apply_monitoring_filters(
        query=query,
        params=params,
        endpoint=endpoint,
        time_window=time_window,
    )

    query += """
    ORDER BY timestamp DESC
    """

    if limit and limit > 0:
        query += " LIMIT %s"
        params.append(limit)

    query += ";"

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "timestamp": row[0],
            "endpoint": row[1],
            "status_code": row[2],
            "error_message": row[3],
            "latency_ms": row[4],
        }
        for row in rows
    ]


def get_top_recommended_crops(limit=10):
    """
    Récupère les cultures les plus recommandées.
    """

    query = """
    SELECT
        recommended_crop,
        COUNT(*) AS total
    FROM api_predictions
    WHERE prediction_type = 'recommend'
      AND recommended_crop IS NOT NULL
    GROUP BY recommended_crop
    ORDER BY total DESC
    """

    params = []

    if limit and limit > 0:
        query += " LIMIT %s"
        params.append(limit)

    query += ";"

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "recommended_crop": row[0],
            "total": row[1],
        }
        for row in rows
    ]


def get_requests_over_time(endpoint=None, time_window=None):
    """
    Récupère l'évolution du nombre de requêtes dans le temps.
    """

    query = """
    SELECT
        DATE_TRUNC('hour', timestamp) AS period,
        COUNT(*) AS total_requests
    FROM api_requests
    WHERE 1=1
    """

    params = []

    query, params = _apply_monitoring_filters(
        query=query,
        params=params,
        endpoint=endpoint,
        time_window=time_window,
    )

    query += """
    GROUP BY period
    ORDER BY period ASC;
    """

    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()

    return [
        {
            "period": row[0],
            "total_requests": row[1],
        }
        for row in rows
    ]