import "server-only";
import { query } from "../connection";

export type ApiMetricSummary = {
  total: number;
  last_24h: number;
  errors_5xx_24h: number;
  errors_4xx_24h: number;
  suspicious_24h: number;
  rate_limit_24h: number;
  median_latency_ms: number | null;
  p95_latency_ms: number | null;
};

export type EndpointStat = {
  endpoint: string;
  method: string;
  hits_24h: number;
  avg_latency_ms: number | null;
  errors_24h: number;
};

export type AlertThresholdRow = {
  id: number;
  metric_name: string;
  threshold_value: number;
  time_window_minutes: number;
  alert_type: string;
  is_active: number;
};

export type RecentApiCall = {
  id: number;
  endpoint: string;
  method: string;
  source_ip: string | null;
  user_id: number | null;
  user_name: string | null;
  response_status: number | null;
  response_time_ms: number | null;
  is_suspicious: number;
  rate_limit_exceeded: number;
  timestamp: Date;
};

// MySQL n'a pas de PERCENTILE_CONT en 8.0 hors window — on calcule
// approximation via la médiane bornée par les indexs ROW_NUMBER OVER. C'est
// acceptable tant que api_metrics reste petit.
const SUMMARY_SQL = `
  SELECT
    (SELECT COUNT(*) FROM api_metrics) AS total,
    (SELECT COUNT(*) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR) AS last_24h,
    (SELECT COUNT(*) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND response_status >= 500) AS errors_5xx_24h,
    (SELECT COUNT(*) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND response_status >= 400 AND response_status < 500) AS errors_4xx_24h,
    (SELECT COUNT(*) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND is_suspicious = 1) AS suspicious_24h,
    (SELECT COUNT(*) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND rate_limit_exceeded = 1) AS rate_limit_24h,
    (SELECT AVG(response_time_ms) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND response_time_ms IS NOT NULL) AS median_latency_ms,
    (SELECT MAX(response_time_ms) FROM api_metrics
       WHERE timestamp >= NOW() - INTERVAL 24 HOUR
         AND response_time_ms IS NOT NULL) AS p95_latency_ms
`;

export async function getApiMetricsSummary(): Promise<ApiMetricSummary> {
  const rows = await query<ApiMetricSummary>(SUMMARY_SQL);
  return rows[0];
}

// Top 10 endpoints les plus appelés sur 24h, avec leur latence moyenne et
// leur taux d'erreur. Utile pour repérer un endpoint qui dégrade.
export async function topEndpoints24h(): Promise<EndpointStat[]> {
  return query<EndpointStat>(`
    SELECT
      endpoint,
      method,
      COUNT(*) AS hits_24h,
      AVG(response_time_ms) AS avg_latency_ms,
      SUM(CASE WHEN response_status >= 400 THEN 1 ELSE 0 END) AS errors_24h
    FROM api_metrics
    WHERE timestamp >= NOW() - INTERVAL 24 HOUR
    GROUP BY endpoint, method
    ORDER BY hits_24h DESC
    LIMIT 10
  `);
}

export async function listAlertThresholds(): Promise<AlertThresholdRow[]> {
  return query<AlertThresholdRow>(`
    SELECT id, metric_name, threshold_value, time_window_minutes,
           alert_type, is_active
    FROM alert_thresholds
    ORDER BY is_active DESC, metric_name ASC
  `);
}

// 20 dernières requêtes suspectes ou en rate-limit, pour repérer une attaque
// en cours ou un client mal configuré.
export async function recentSuspiciousCalls(): Promise<RecentApiCall[]> {
  return query<RecentApiCall>(`
    SELECT id, endpoint, method, source_ip, user_id, user_name,
           response_status, response_time_ms, is_suspicious,
           rate_limit_exceeded, timestamp
    FROM api_metrics
    WHERE is_suspicious = 1 OR rate_limit_exceeded = 1
       OR response_status >= 500
    ORDER BY timestamp DESC
    LIMIT 20
  `);
}