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

export type DashboardStats = {
  tenants_total: number;
  tenants_active: number;
  tenants_trial: number;
  tenants_suspended: number;
  techniciens_total: number;
  techniciens_actifs: number;
  superadmins: number;
  sites: number;
  machines: number;
  interventions_total: number;
  interventions_30j: number;
  rapports_30j: number;
  factures_30j: number;
  api_calls_24h: number;
  api_errors_24h: number;
};

// Une seule passe SQL qui regroupe tous les counts. Les sous-requêtes
// scalaires sont triviales tant que les tables restent < 100k lignes ; à
// renforcer avec des index ou des cache vues si ça devient lent.
export async function getDashboardStats(): Promise<DashboardStats> {
  const rows = await query<DashboardStats>(`
    SELECT
      (SELECT COUNT(*) FROM tenants) AS tenants_total,
      (SELECT COUNT(*) FROM tenants WHERE status='active') AS tenants_active,
      (SELECT COUNT(*) FROM tenants WHERE status='trial') AS tenants_trial,
      (SELECT COUNT(*) FROM tenants WHERE status='suspended') AS tenants_suspended,
      (SELECT COUNT(*) FROM techniciens) AS techniciens_total,
      (SELECT COUNT(*) FROM techniciens WHERE actif=1) AS techniciens_actifs,
      (SELECT COUNT(*) FROM techniciens WHERE is_superadmin=1) AS superadmins,
      (SELECT COUNT(*) FROM sites_clients) AS sites,
      (SELECT COUNT(*) FROM machines) AS machines,
      (SELECT COUNT(*) FROM interventions) AS interventions_total,
      (SELECT COUNT(*) FROM interventions
         WHERE created_at >= NOW() - INTERVAL 30 DAY) AS interventions_30j,
      (SELECT COUNT(*) FROM rapports
         WHERE date_creation >= NOW() - INTERVAL 30 DAY) AS rapports_30j,
      (SELECT COUNT(*) FROM factures
         WHERE created_at >= NOW() - INTERVAL 30 DAY) AS factures_30j,
      (SELECT COUNT(*) FROM api_metrics
         WHERE timestamp >= NOW() - INTERVAL 24 HOUR) AS api_calls_24h,
      (SELECT COUNT(*) FROM api_metrics
         WHERE timestamp >= NOW() - INTERVAL 24 HOUR
           AND response_status >= 400) AS api_errors_24h
  `);
  return rows[0];
}

// Top 5 tenants par nombre d'interventions sur 30 jours (signal d'activité).
export type TopTenant = {
  id: number;
  slug: string;
  name: string;
  intervention_count_30j: number;
};

export async function topActiveTenants(): Promise<TopTenant[]> {
  return query<TopTenant>(`
    SELECT
      t.id, t.slug, t.name,
      COUNT(i.id) AS intervention_count_30j
    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
    ORDER BY intervention_count_30j DESC, t.name ASC
    LIMIT 5
  `);
}