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

// Toutes les agrégations temporelles consommées par les composants
// <DonutChart>, <BarChart> et <LineChart>. Les queries sont triviales
// (GROUP BY DATE_FORMAT / HOUR) mais regroupées ici pour éviter de polluer
// les fichiers métier (billing.ts, monitoring.ts, etc.).

// ─── Séries temporelles JOURNALIÈRES (30j) ──────────────────────────────

export type DailyCount = {
  day: string; // YYYY-MM-DD
  count: number;
};

/**
 * Renvoie une série journalière sur les 30 derniers jours (incluant aujourd'hui),
 * remplie même pour les jours sans donnée (count=0). Le paramètre `table` est
 * statiquement contraint : pas d'injection possible côté caller.
 */
async function dailyCount30j(
  table: "interventions" | "rapports" | "factures" | "activities_log",
  dateColumn: "created_at" | "date_creation",
): Promise<DailyCount[]> {
  // Génère 30 jours puis LEFT JOIN sur la table cible — garantit qu'il y a
  // toujours 30 points même si la table est vide.
  // CAST en SIGNED → le driver renvoie un number JS, sinon BIGINT → string et
  // les `sum + d.count` font de la concaténation au lieu de l'addition.
  return query<DailyCount>(`
    SELECT
      DATE_FORMAT(d.day, '%Y-%m-%d') AS day,
      CAST(COALESCE(SUM(CASE WHEN t.${dateColumn} IS NOT NULL THEN 1 ELSE 0 END), 0) AS SIGNED) AS count
    FROM (
      SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS day
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
        UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
        UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
        UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
        UNION ALL SELECT 28 UNION ALL SELECT 29
      ) seq
    ) d
    LEFT JOIN ${table} t
      ON DATE(t.${dateColumn}) = d.day
    GROUP BY d.day
    ORDER BY d.day ASC
  `);
}

export const interventionsPerDay30j = () =>
  dailyCount30j("interventions", "created_at");
export const rapportsPerDay30j = () =>
  dailyCount30j("rapports", "date_creation");
export const facturesPerDay30j = () =>
  dailyCount30j("factures", "created_at");
export const activitiesPerDay30j = () =>
  dailyCount30j("activities_log", "created_at");

// ─── Séries temporelles MENSUELLES (12 mois) ────────────────────────────

export type MonthlyCount = {
  month: string; // YYYY-MM
  count: number;
};

async function monthlyCount12m(
  table: "tenants" | "interventions" | "rapports" | "factures" | "techniciens",
  dateColumn: "created_at" | "date_creation",
): Promise<MonthlyCount[]> {
  return query<MonthlyCount>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      CAST((SELECT COUNT(*) FROM ${table} x
              WHERE x.${dateColumn} >= m.month_start
                AND x.${dateColumn} <  m.month_end) AS SIGNED) AS count
    FROM (
      SELECT
        DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01') AS month_start,
        DATE_FORMAT(NOW() - INTERVAL (n-1) MONTH, '%Y-%m-01') AS month_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
      ) seq
    ) m
    ORDER BY m.month_start ASC
  `);
}

export const interventionsPerMonth12m = () =>
  monthlyCount12m("interventions", "created_at");
export const rapportsPerMonth12m = () =>
  monthlyCount12m("rapports", "date_creation");
export const facturesPerMonth12m = () =>
  monthlyCount12m("factures", "created_at");
export const techniciensCreatedPerMonth12m = () =>
  monthlyCount12m("techniciens", "created_at");

// ─── Tenants cumulés sur 12 mois ────────────────────────────────────────

/**
 * Pour chaque mois, total de tenants créés ≤ fin du mois. Donne une courbe
 * croissante (sauf si suppressions, qu'on ignore — le panel ne supprime pas).
 */
export async function tenantsTotalCumulativeMonthly(): Promise<MonthlyCount[]> {
  return query<MonthlyCount>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      CAST((SELECT COUNT(*) FROM tenants
              WHERE created_at < m.month_end) AS SIGNED) AS count
    FROM (
      SELECT
        DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01') AS month_start,
        DATE_FORMAT(NOW() - INTERVAL (n-1) MONTH, '%Y-%m-01') AS month_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
      ) seq
    ) m
    ORDER BY m.month_start ASC
  `);
}

// ─── MRR cumulé / mois ───────────────────────────────────────────────────

export type MonthlyMrr = {
  month: string;
  mrr_eur: string; // décimal MySQL
};

/**
 * MRR à la FIN de chaque mois : on additionne tous les abonnements 'active'
 * dont `current_period_start <= month_end`. Approximation : on n'a pas un
 * historique des transitions, donc on infère le MRR comme étant celui des
 * subs actives en fin de mois (sans tenir compte des annulations passées).
 */
export async function mrrCumulativeMonthly(): Promise<MonthlyMrr[]> {
  return query<MonthlyMrr>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      COALESCE((
        SELECT SUM(
          CASE ts.billing_period
            WHEN 'monthly' THEN p.monthly_price
            WHEN 'yearly'  THEN p.yearly_price / 12
            ELSE 0
          END)
        FROM tenant_subscriptions ts
        JOIN plans p ON p.id = ts.plan_id
        WHERE ts.status = 'active'
          AND ts.current_period_start <= m.month_end
          AND (ts.canceled_at IS NULL OR ts.canceled_at > m.month_end)
      ), 0) AS mrr_eur
    FROM (
      SELECT
        DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01') AS month_start,
        DATE_FORMAT(NOW() - INTERVAL (n-1) MONTH, '%Y-%m-01') AS month_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
      ) seq
    ) m
    ORDER BY m.month_start ASC
  `);
}

// ─── Churn rate mensuel (%) ─────────────────────────────────────────────

export type ChurnRow = {
  month: string;
  active_start: number;
  canceled: number;
  rate_pct: string; // décimal
};

export async function churnRateMonthly(): Promise<ChurnRow[]> {
  return query<ChurnRow>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      CAST((SELECT COUNT(*) FROM tenant_subscriptions
              WHERE status IN ('active','past_due')
                AND current_period_start < m.month_start
                AND (canceled_at IS NULL OR canceled_at >= m.month_start)) AS SIGNED) AS active_start,
      CAST((SELECT COUNT(*) FROM tenant_subscriptions
              WHERE canceled_at >= m.month_start
                AND canceled_at <  m.month_end) AS SIGNED) AS canceled,
      ROUND(
        100 * (
          (SELECT COUNT(*) FROM tenant_subscriptions
             WHERE canceled_at >= m.month_start
               AND canceled_at <  m.month_end) /
          GREATEST(1, (SELECT COUNT(*) FROM tenant_subscriptions
                         WHERE status IN ('active','past_due')
                           AND current_period_start < m.month_start
                           AND (canceled_at IS NULL OR canceled_at >= m.month_start)))
        ),
        2
      ) AS rate_pct
    FROM (
      SELECT
        DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01') AS month_start,
        DATE_FORMAT(NOW() - INTERVAL (n-1) MONTH, '%Y-%m-01') AS month_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
      ) seq
    ) m
    ORDER BY m.month_start ASC
  `);
}

// ─── Taux de conversion trial → payant mensuel (%) ──────────────────────

export type TrialConversionRow = {
  month: string;
  signups: number;
  conversions: number;
  rate_pct: string;
};

export async function trialConversionRateMonthly(): Promise<TrialConversionRow[]> {
  return query<TrialConversionRow>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      CAST((SELECT COUNT(*) FROM tenants
              WHERE created_at >= m.month_start
                AND created_at <  m.month_end) AS SIGNED) AS signups,
      CAST((SELECT COUNT(*) FROM tenant_subscriptions
              WHERE status = 'active'
                AND current_period_start >= m.month_start
                AND current_period_start <  m.month_end) AS SIGNED) AS conversions,
      ROUND(
        100 * (
          (SELECT COUNT(*) FROM tenant_subscriptions
             WHERE status = 'active'
               AND current_period_start >= m.month_start
               AND current_period_start <  m.month_end) /
          GREATEST(1, (SELECT COUNT(*) FROM tenants
                         WHERE created_at >= m.month_start
                           AND created_at <  m.month_end))
        ),
        2
      ) AS rate_pct
    FROM (
      SELECT
        DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01') AS month_start,
        DATE_FORMAT(NOW() - INTERVAL (n-1) MONTH, '%Y-%m-01') AS month_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
      ) seq
    ) m
    ORDER BY m.month_start ASC
  `);
}

// ─── Top tenants par interventions 30j ──────────────────────────────────

export type TopTenantInterventions = {
  tenant_id: number;
  tenant_slug: string;
  tenant_name: string;
  intervention_count: number;
};

/**
 * Top N tenants par volume d'interventions sur les 30 derniers jours. Étend
 * `topActiveTenants` qui était limité à 5.
 */
export async function topTenantsByInterventions(
  limit = 10,
): Promise<TopTenantInterventions[]> {
  return query<TopTenantInterventions>(
    `SELECT
       t.id AS tenant_id, t.slug AS tenant_slug, t.name AS tenant_name,
       CAST(COUNT(i.id) AS SIGNED) AS intervention_count
     FROM tenants t
     LEFT JOIN interventions i
       ON i.tenant_id = t.id
      AND i.created_at >= NOW() - INTERVAL 30 DAY
     GROUP BY t.id, t.slug, t.name
     HAVING intervention_count > 0
     ORDER BY intervention_count DESC, t.name ASC
     LIMIT ?`,
    [limit],
  );
}

// ─── Audit / activités log ──────────────────────────────────────────────

export type ActivityTypeRow = {
  type: string;
  count: number;
};

export async function activityTypesTop10(): Promise<ActivityTypeRow[]> {
  return query<ActivityTypeRow>(`
    SELECT
      COALESCE(type, '(sans type)') AS type,
      CAST(COUNT(*) AS SIGNED) AS count
    FROM activities_log
    WHERE created_at >= NOW() - INTERVAL 30 DAY
    GROUP BY type
    ORDER BY count DESC
    LIMIT 10
  `);
}

export type SuperadminTenantSplit = {
  scope: "superadmin" | "tenant";
  count: number;
};

export async function activitiesSuperadminVsTenant24h(): Promise<SuperadminTenantSplit[]> {
  // Une activité est 'superadmin' si tenant_id IS NULL OU si le user_id
  // correspond à un super-admin. On approxime ici en regardant juste
  // tenant_id NULL pour rester sur une seule passe sans jointure techniciens.
  return query<SuperadminTenantSplit>(`
    SELECT
      CASE WHEN tenant_id IS NULL THEN 'superadmin' ELSE 'tenant' END AS scope,
      CAST(COUNT(*) AS SIGNED) AS count
    FROM activities_log
    WHERE created_at >= NOW() - INTERVAL 24 HOUR
    GROUP BY scope
  `);
}

// ─── Monitoring API ─────────────────────────────────────────────────────

export type HourlyApiCount = {
  hour: string; // YYYY-MM-DD HH:00
  hour_label: string; // HH:00
  total: number;
  errors_4xx: number;
  errors_5xx: number;
};

/**
 * 24 derniers buckets horaires (inclus l'heure courante en cours). Buckets
 * vides remplis avec 0. Utilisé pour la sparkline et la bar groupée
 * (total/4xx/5xx) sur la page monitoring.
 */
export async function apiCallsPerHour24h(): Promise<HourlyApiCount[]> {
  return query<HourlyApiCount>(`
    SELECT
      DATE_FORMAT(h.hour_start, '%Y-%m-%d %H:00') AS hour,
      DATE_FORMAT(h.hour_start, '%H:00') AS hour_label,
      CAST((SELECT COUNT(*) FROM api_metrics am
              WHERE am.timestamp >= h.hour_start
                AND am.timestamp <  h.hour_end) AS SIGNED) AS total,
      CAST((SELECT COUNT(*) FROM api_metrics am
              WHERE am.timestamp >= h.hour_start
                AND am.timestamp <  h.hour_end
                AND am.response_status BETWEEN 400 AND 499) AS SIGNED) AS errors_4xx,
      CAST((SELECT COUNT(*) FROM api_metrics am
              WHERE am.timestamp >= h.hour_start
                AND am.timestamp <  h.hour_end
                AND am.response_status >= 500) AS SIGNED) AS errors_5xx
    FROM (
      SELECT
        DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL n HOUR) AS hour_start,
        DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL (n-1) HOUR) AS hour_end
      FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
        UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
        UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
      ) seq
    ) h
    ORDER BY h.hour_start ASC
  `);
}

// ─── Techniciens actifs / inactifs ──────────────────────────────────────

export type TechniciensActiviteSplit = {
  actif: number; // 1 ou 0 (driver renvoie un tinyint en number)
  count: number;
};

export async function techniciensActifsRatio(): Promise<TechniciensActiviteSplit[]> {
  return query<TechniciensActiviteSplit>(`
    SELECT actif, CAST(COUNT(*) AS SIGNED) AS count
    FROM techniciens
    WHERE is_superadmin = 0
    GROUP BY actif
  `);
}

// ─── HTTP status codes split 24h ────────────────────────────────────────

export type StatusBucket = {
  bucket: "2xx" | "3xx" | "4xx" | "5xx" | "other";
  count: number;
};

export async function apiStatusBuckets24h(): Promise<StatusBucket[]> {
  return query<StatusBucket>(`
    SELECT
      CASE
        WHEN response_status BETWEEN 200 AND 299 THEN '2xx'
        WHEN response_status BETWEEN 300 AND 399 THEN '3xx'
        WHEN response_status BETWEEN 400 AND 499 THEN '4xx'
        WHEN response_status >= 500 THEN '5xx'
        ELSE 'other'
      END AS bucket,
      CAST(COUNT(*) AS SIGNED) AS count
    FROM api_metrics
    WHERE timestamp >= NOW() - INTERVAL 24 HOUR
    GROUP BY bucket
  `);
}