// Requêtes typées pour la page Tenants. Les colonnes de tenants/plans/
// tenant_subscriptions sont définies par missioflow-app (database/schema.sql).
// Si une de ces colonnes change ou disparaît, la requête plante à l'exécution
// — c'est le signal pour adapter ici.

import "server-only";
import { query } from "../connection";
import { getLatestExportJob, type RgpdExportJob } from "./rgpd-exports";

// Aligné sur enum tenants.status MySQL.
export type TenantStatus = "trial" | "active" | "suspended" | "canceled";

export type TenantRow = {
  id: number;
  slug: string;
  name: string;
  admin_email: string | null;
  status: TenantStatus;
  plan_code: "starter" | "pro" | "enterprise" | "flex";
  trial_end_at: Date | null;
  created_at: Date;
  // Données dérivées (jointure tenant_subscriptions).
  subscription_status: "trialing" | "active" | "past_due" | "canceled" | null;
  subscription_period_end: Date | null;
  // Plan détaillé (via tenant_subscriptions.plan_id → plans).
  plan_name: string | null;
  plan_monthly_price: string | null; // decimal renvoyé en string par mysql2
  // Compteurs cross-table (sous-requêtes corrélées — à plat tant que peu de tenants).
  tech_count: number;
  site_count: number;
  machine_count: number;
};

export type TenantDetail = {
  id: number;
  slug: string;
  name: string;
  admin_email: string | null;
  status: TenantStatus;
  plan_code: "starter" | "pro" | "enterprise" | "flex";
  trial_end_at: Date | null;
  onboarding_completed: number;
  stripe_customer_id: string | null;
  stripe_subscription_id: string | null;
  created_at: Date;
  updated_at: Date;
  active_subscription: {
    id: number;
    plan_id: number;
    plan_name: string | null;
    plan_code: string | null;
    plan_monthly_price: string | null;
    billing_period: "monthly" | "yearly";
    status: "trialing" | "active" | "past_due" | "canceled";
    current_period_start: Date | null;
    current_period_end: Date | null;
    trial_end: Date | null;
    canceled_at: Date | null;
    // 1 = résiliation programmée à échéance (sub reste active jusqu'à
    // current_period_end puis s'annule) ; 0 = renouvellement normal.
    // Colonne livrée par missioflow-app (migration 2026-06-12, mf #192).
    cancel_at_period_end: number;
    stripe_subscription_id: string | null;
    stripe_customer_id: string | null;
  } | null;
  past_subscriptions: Array<{
    id: number;
    plan_name: string | null;
    status: string;
    canceled_at: Date | null;
    current_period_end: Date | null;
  }>;
  recent_activities: Array<{
    id: number;
    type: string | null;
    title: string | null;
    description: string | null;
    user_name: string | null;
    created_at: Date;
  }>;
  stats: {
    tech_count: number;
    site_count: number;
    machine_count: number;
    intervention_count: number;
    intervention_30j: number;
  };
  // Dernier export RGPD (portabilité art. 20) connu pour ce tenant, lu en DB
  // directe. null = aucun export demandé (ou table pas encore déployée). Le
  // déclenchement/téléchargement passent par les endpoints sysop (cf. mf #151).
  latest_export_job: RgpdExportJob | null;
};

// Détail complet d'un tenant — combiné en une seule fonction async qui fait
// 4 requêtes en parallèle pour limiter le round-trip total. ID est paramétré
// via placeholder, jamais concat. La fonction retourne null si le tenant
// n'existe pas (la route handler renvoie alors 404).
export async function getTenantDetail(id: number): Promise<TenantDetail | null> {
  const [tenantRows, activeSubs, pastSubs, activities, statsRows, exportJob] =
    await Promise.all([
      query<{
        id: number;
        slug: string;
        name: string;
        admin_email: string | null;
        status: TenantStatus;
        plan_code: "starter" | "pro" | "enterprise" | "flex";
        trial_end_at: Date | null;
        onboarding_completed: number;
        stripe_customer_id: string | null;
        stripe_subscription_id: string | null;
        created_at: Date;
        updated_at: Date;
      }>(
        `SELECT id, slug, name, admin_email, status, plan AS plan_code,
                trial_end_at, onboarding_completed, stripe_customer_id,
                stripe_subscription_id, created_at, updated_at
         FROM tenants WHERE id = ? LIMIT 1`,
        [id],
      ),
      query<TenantDetail["active_subscription"] & { _x?: never }>(
        `SELECT ts.id, ts.plan_id, ts.billing_period, ts.status,
                ts.current_period_start, ts.current_period_end,
                ts.trial_end, ts.canceled_at, ts.cancel_at_period_end,
                ts.stripe_subscription_id, ts.stripe_customer_id,
                p.name AS plan_name,
                p.code AS plan_code,
                p.monthly_price AS plan_monthly_price
         FROM tenant_subscriptions ts
         LEFT JOIN plans p ON p.id = ts.plan_id
         WHERE ts.tenant_id = ?
           AND ts.status IN ('trialing','active','past_due')
         ORDER BY ts.created_at DESC LIMIT 1`,
        [id],
      ),
      query<TenantDetail["past_subscriptions"][number]>(
        `SELECT ts.id, ts.status, ts.canceled_at, ts.current_period_end,
                p.name AS plan_name
         FROM tenant_subscriptions ts
         LEFT JOIN plans p ON p.id = ts.plan_id
         WHERE ts.tenant_id = ?
           AND ts.status = 'canceled'
         ORDER BY COALESCE(ts.canceled_at, ts.updated_at) DESC LIMIT 5`,
        [id],
      ),
      query<TenantDetail["recent_activities"][number]>(
        `SELECT a.id, a.type, a.title, a.description, a.created_at,
                CONCAT_WS(' ', t.prenom, t.nom) AS user_name
         FROM activities_log a
         LEFT JOIN techniciens t ON t.id = a.user_id
         WHERE a.tenant_id = ?
         ORDER BY a.created_at DESC LIMIT 10`,
        [id],
      ),
      query<TenantDetail["stats"]>(
        `SELECT
           (SELECT COUNT(*) FROM techniciens WHERE tenant_id = ?) AS tech_count,
           (SELECT COUNT(*) FROM sites_clients WHERE tenant_id = ?) AS site_count,
           (SELECT COUNT(*) FROM machines WHERE tenant_id = ?) AS machine_count,
           (SELECT COUNT(*) FROM interventions WHERE tenant_id = ?) AS intervention_count,
           (SELECT COUNT(*) FROM interventions WHERE tenant_id = ?
              AND created_at >= NOW() - INTERVAL 30 DAY) AS intervention_30j`,
        [id, id, id, id, id],
      ),
      getLatestExportJob(id),
    ]);

  const tenant = tenantRows[0];
  if (!tenant) return null;

  return {
    ...tenant,
    active_subscription: activeSubs[0] ?? null,
    past_subscriptions: pastSubs,
    recent_activities: activities,
    stats: statsRows[0],
    latest_export_job: exportJob,
  };
}

// ⚠ Pas de paramètres utilisateur ici, donc pas de risque d'injection. Si on
// ajoute filtres/recherche plus tard, TOUJOURS passer par `?` placeholders
// (cf. query() helper).
export async function listTenants(): Promise<TenantRow[]> {
  const sql = `
    SELECT
      t.id,
      t.slug,
      t.name,
      t.admin_email,
      t.status,
      t.plan AS plan_code,
      t.trial_end_at,
      t.created_at,
      ts.status                AS subscription_status,
      ts.current_period_end    AS subscription_period_end,
      p.name                   AS plan_name,
      p.monthly_price          AS plan_monthly_price,
      (SELECT COUNT(*) FROM techniciens WHERE tenant_id = t.id) AS tech_count,
      (SELECT COUNT(*) FROM sites_clients WHERE tenant_id = t.id) AS site_count,
      (SELECT COUNT(*) FROM machines WHERE tenant_id = t.id) AS machine_count
    FROM tenants t
    LEFT JOIN tenant_subscriptions ts
      ON ts.tenant_id = t.id
     AND ts.status IN ('trialing', 'active', 'past_due')
    LEFT JOIN plans p
      ON p.id = ts.plan_id
    ORDER BY t.created_at DESC
  `;
  return query<TenantRow>(sql);
}