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

export type BillingStatusBreakdown = {
  trialing: number;
  active: number;
  past_due: number;
  canceled: number;
};

export type BillingPlanBreakdown = {
  plan_id: number;
  plan_code: string;
  plan_name: string;
  monthly_price: string;
  yearly_price: string;
  active_count: number;
  monthly_count: number;
  yearly_count: number;
  // MRR contribué par ce plan : monthly_price × monthly + (yearly_price/12) × yearly
  mrr_contribution: string;
};

export type BillingMonthlyRow = {
  month: string; // YYYY-MM
  signups: number;
  conversions_to_paid: number;
  cancellations: number;
};

export type ExpiringTrialRow = {
  tenant_id: number;
  tenant_name: string;
  tenant_slug: string;
  plan_name: string | null;
  trial_end: Date;
  days_left: number;
};

export type PastDueRow = {
  tenant_id: number;
  tenant_name: string;
  tenant_slug: string;
  plan_name: string | null;
  current_period_end: Date | null;
  monthly_price: string | null;
};

// --- Stats globales ---

export async function getBillingSummary(): Promise<{
  total_tenants: number;
  total_active_subs: number;
  mrr_eur: string;
  arr_eur: string;
  trials_active: number;
  past_due: number;
  canceled_30j: number;
  signups_30j: number;
}> {
  const rows = await query<{
    total_tenants: number;
    total_active_subs: number;
    mrr_eur: string;
    arr_eur: string;
    trials_active: number;
    past_due: number;
    canceled_30j: number;
    signups_30j: number;
  }>(`
    SELECT
      (SELECT COUNT(*) FROM tenants) AS total_tenants,
      (SELECT COUNT(*) FROM tenant_subscriptions
         WHERE status IN ('active','trialing','past_due')) AS total_active_subs,
      -- MRR : on additionne les revenus mensualisés des subs en 'active' uniquement.
      -- past_due ne compte pas (paiement en échec). trialing ne compte pas (gratuit).
      (SELECT COALESCE(SUM(
         CASE ts.billing_period
           WHEN 'monthly' THEN p.monthly_price
           WHEN 'yearly'  THEN p.yearly_price / 12
           ELSE 0
         END
       ), 0)
       FROM tenant_subscriptions ts
       JOIN plans p ON p.id = ts.plan_id
       WHERE ts.status = 'active') AS mrr_eur,
      (SELECT COALESCE(SUM(
         CASE ts.billing_period
           WHEN 'monthly' THEN p.monthly_price * 12
           WHEN 'yearly'  THEN p.yearly_price
           ELSE 0
         END
       ), 0)
       FROM tenant_subscriptions ts
       JOIN plans p ON p.id = ts.plan_id
       WHERE ts.status = 'active') AS arr_eur,
      (SELECT COUNT(*) FROM tenant_subscriptions WHERE status='trialing') AS trials_active,
      (SELECT COUNT(*) FROM tenant_subscriptions WHERE status='past_due') AS past_due,
      (SELECT COUNT(*) FROM tenant_subscriptions
         WHERE status='canceled'
           AND canceled_at >= NOW() - INTERVAL 30 DAY) AS canceled_30j,
      (SELECT COUNT(*) FROM tenants
         WHERE created_at >= NOW() - INTERVAL 30 DAY) AS signups_30j
  `);
  return rows[0];
}

export async function getStatusBreakdown(): Promise<BillingStatusBreakdown> {
  const rows = await query<{ status: string; n: number }>(`
    SELECT status, COUNT(*) AS n
    FROM tenant_subscriptions
    GROUP BY status
  `);
  const acc: BillingStatusBreakdown = {
    trialing: 0,
    active: 0,
    past_due: 0,
    canceled: 0,
  };
  for (const r of rows) {
    if (r.status in acc) {
      acc[r.status as keyof BillingStatusBreakdown] = r.n;
    }
  }
  return acc;
}

export async function getPlanBreakdown(): Promise<BillingPlanBreakdown[]> {
  return query<BillingPlanBreakdown>(`
    SELECT
      p.id AS plan_id, p.code AS plan_code, p.name AS plan_name,
      p.monthly_price, p.yearly_price,
      COUNT(ts.id) AS active_count,
      SUM(CASE WHEN ts.billing_period='monthly' THEN 1 ELSE 0 END) AS monthly_count,
      SUM(CASE WHEN ts.billing_period='yearly'  THEN 1 ELSE 0 END) AS yearly_count,
      COALESCE(SUM(
        CASE ts.billing_period
          WHEN 'monthly' THEN p.monthly_price
          WHEN 'yearly'  THEN p.yearly_price / 12
          ELSE 0
        END
      ), 0) AS mrr_contribution
    FROM plans p
    LEFT JOIN tenant_subscriptions ts
      ON ts.plan_id = p.id AND ts.status = 'active'
    GROUP BY p.id, p.code, p.name, p.monthly_price, p.yearly_price
    ORDER BY p.sort_order ASC, p.id ASC
  `);
}

// Évolution sur 12 mois : signups, conversions vers payant, annulations.
// Conversion = subscription qui a passé de 'trialing' à 'active' (approximé
// via current_period_start renseigné dans le mois, alors que trial_end est
// avant ce mois).
export async function getMonthlyEvolution(): Promise<BillingMonthlyRow[]> {
  return query<BillingMonthlyRow>(`
    SELECT
      DATE_FORMAT(m.month_start, '%Y-%m') AS month,
      (SELECT COUNT(*) FROM tenants
         WHERE created_at >= m.month_start
           AND created_at <  m.month_end) AS signups,
      (SELECT COUNT(*) FROM tenant_subscriptions
         WHERE status='active'
           AND current_period_start >= m.month_start
           AND current_period_start <  m.month_end) AS conversions_to_paid,
      (SELECT COUNT(*) FROM tenant_subscriptions
         WHERE canceled_at >= m.month_start
           AND canceled_at <  m.month_end) AS cancellations
    FROM (
      SELECT
        DATE_SUB(DATE_FORMAT(NOW() - INTERVAL n MONTH, '%Y-%m-01'), INTERVAL 0 DAY) 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 async function getExpiringTrials(): Promise<ExpiringTrialRow[]> {
  return query<ExpiringTrialRow>(`
    SELECT
      t.id AS tenant_id, t.name AS tenant_name, t.slug AS tenant_slug,
      p.name AS plan_name,
      ts.trial_end,
      DATEDIFF(ts.trial_end, NOW()) AS days_left
    FROM tenant_subscriptions ts
    JOIN tenants t ON t.id = ts.tenant_id
    LEFT JOIN plans p ON p.id = ts.plan_id
    WHERE ts.status = 'trialing'
      AND ts.trial_end IS NOT NULL
      AND ts.trial_end <= NOW() + INTERVAL 14 DAY
    ORDER BY ts.trial_end ASC
    LIMIT 20
  `);
}

export async function getPastDueTenants(): Promise<PastDueRow[]> {
  return query<PastDueRow>(`
    SELECT
      t.id AS tenant_id, t.name AS tenant_name, t.slug AS tenant_slug,
      p.name AS plan_name,
      ts.current_period_end,
      p.monthly_price
    FROM tenant_subscriptions ts
    JOIN tenants t ON t.id = ts.tenant_id
    LEFT JOIN plans p ON p.id = ts.plan_id
    WHERE ts.status = 'past_due'
    ORDER BY ts.current_period_end ASC
    LIMIT 20
  `);
}