import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { childLogger } from '@server/logger';

const log = childLogger('billing.service');

export async function getSubscription(restaurantId: string) {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT s.*, p.name as plan_name, p.price_monthly, p.price_annual, p.features, p.limits,
           p.trial_days
    FROM subscriptions s
    LEFT JOIN plans p ON s.plan_id = p.id
    WHERE s.restaurant_id = ${restaurantId}
    ORDER BY s.created_at DESC
    LIMIT 1
  `);
  const sub = rows[0] ?? null;

  if (sub && (sub as Record<string, unknown>).status === 'past_due') {
    await _enforceGracePeriod(restaurantId, sub as Record<string, unknown>);
    const { rows: fresh } = await db.execute(sql`
      SELECT s.*, p.name as plan_name, p.price_monthly, p.price_annual, p.features, p.limits,
             p.trial_days
      FROM subscriptions s
      LEFT JOIN plans p ON s.plan_id = p.id
      WHERE s.restaurant_id = ${restaurantId}
      ORDER BY s.created_at DESC
      LIMIT 1
    `);
    return fresh[0] ?? null;
  }

  return sub;
}

async function _enforceGracePeriod(restaurantId: string, sub: Record<string, unknown>) {
  try {
    const { getGatewayConfig } = await import('@server/services/gateway.service');
    const cfg = await getGatewayConfig();
    const graceDays = cfg.paymentGracePeriodDays ?? 3;

    let overdueAt = sub.overdue_at ? new Date(sub.overdue_at as string) : null;
    if (!overdueAt) {
      overdueAt = new Date();
      await db.execute(sql`
        UPDATE public.subscriptions SET overdue_at = ${overdueAt.toISOString()} WHERE restaurant_id = ${restaurantId} AND overdue_at IS NULL
      `);
    }

    const graceCutoff = new Date(overdueAt.getTime() + graceDays * 86400000);
    if (new Date() > graceCutoff) {
      log.info({ restaurantId, overdueAt, graceDays }, 'Billing: grace period expired, suspending subscription');
      await db.execute(sql`
        UPDATE public.subscriptions SET status = 'suspended' WHERE restaurant_id = ${restaurantId} AND status = 'past_due'
      `);
    }
  } catch (err) {
    log.warn({ err }, 'Billing: grace period enforcement failed');
  }
}

export async function getUsage(restaurantId: string) {
  await initDatabase();
  const [convResult, branchResult, apiCallsResult, voiceResult] = await Promise.all([
    db.execute(sql`SELECT COUNT(*) as count FROM conversations WHERE restaurant_id = ${restaurantId} AND created_at >= date_trunc('month', CURRENT_DATE)`),
    db.execute(sql`SELECT COUNT(*) as count FROM branches WHERE restaurant_id = ${restaurantId} AND is_active = true`),
    db.execute(sql`SELECT COALESCE(SUM(calls_this_month), 0) as total FROM api_keys WHERE restaurant_id = ${restaurantId}`),
    db.execute(sql`SELECT COALESCE(SUM(duration_seconds), 0) as total FROM call_logs WHERE restaurant_id = ${restaurantId} AND created_at >= date_trunc('month', CURRENT_DATE)`),
  ]);

  return {
    conversations: parseInt((convResult.rows[0] as { count: string })?.count ?? '0', 10),
    branches: parseInt((branchResult.rows[0] as { count: string })?.count ?? '0', 10),
    apiCalls: parseInt((apiCallsResult.rows[0] as { total: string })?.total ?? '0', 10),
    voiceMinutes: Math.ceil(parseInt((voiceResult.rows[0] as { total: string })?.total ?? '0', 10) / 60),
  };
}

export async function listPlans(currency?: string) {
  await initDatabase();
  if (currency) {
    const upperCurrency = currency.toUpperCase();
    const { rows } = await db.execute(sql`
      SELECT p.*,
             COALESCE(pp.price_monthly, p.price_monthly) as effective_price_monthly,
             COALESCE(pp.price_annual, p.price_annual) as effective_price_annual,
             pp.price_monthly as custom_price_monthly,
             pp.price_annual as custom_price_annual
      FROM plans p
      LEFT JOIN plan_prices pp ON pp.plan_id = p.id AND pp.currency_code = ${upperCurrency}
      WHERE p.is_active = true
      ORDER BY p.price_monthly ASC
    `);
    return rows;
  }
  const { rows } = await db.execute(sql`SELECT * FROM plans WHERE is_active = true ORDER BY price_monthly ASC`);
  return rows;
}

export async function getApiUsageChart(restaurantId: string) {
  await initDatabase();

  const { rows: keysResult } = await db.execute(sql`
    SELECT id, COALESCE(calls_this_month, 0)::text as calls_this_month
    FROM api_keys WHERE restaurant_id = ${restaurantId}
  `);

  const totalCalls = (keysResult as { calls_this_month: string }[]).reduce((sum, k) => sum + parseInt(k.calls_this_month, 10), 0);
  const currentDay = new Date().getDate();
  const dailyAvg = currentDay > 0 ? Math.round(totalCalls / currentDay) : 0;

  const rows: { day: string; calls: number }[] = [];
  for (let i = 6; i >= 0; i--) {
    const d = new Date();
    d.setDate(d.getDate() - i);
    const label = d.toLocaleDateString('en-US', { month: 'short', day: '2-digit' });
    rows.push({ day: label, calls: dailyAvg });
  }
  return rows;
}

export async function getPlanPrices(planId: string) {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT id, plan_id, currency_code, price_monthly, price_annual
    FROM plan_prices
    WHERE plan_id = ${planId}
    ORDER BY currency_code ASC
  `);
  return rows.map((r: Record<string, unknown>) => ({
    id: r.id as string,
    currencyCode: r.currency_code as string,
    priceMonthly: parseFloat(r.price_monthly as string) || 0,
    priceAnnual: parseFloat(r.price_annual as string) || 0,
  }));
}

export async function upsertPlanPrice(planId: string, currencyCode: string, priceMonthly: number, priceAnnual: number) {
  await initDatabase();
  await db.execute(sql`
    INSERT INTO plan_prices (plan_id, currency_code, price_monthly, price_annual)
    VALUES (${planId}, ${currencyCode.toUpperCase()}, ${priceMonthly}, ${priceAnnual})
    ON CONFLICT (plan_id, currency_code)
    DO UPDATE SET price_monthly = EXCLUDED.price_monthly, price_annual = EXCLUDED.price_annual
  `);
}