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

export async function getSubscription(restaurantId: string) {
  await initDatabase();
  /* raw: SELECT s.*, p.name as plan_name, p.price_monthly, p.price_annual, p.features, p.limits FROM subscriptions s LEFT JOIN plans p ... WHERE s.restaurant_id = $1 ORDER BY s.created_at DESC LIMIT 1 */
  const { rows } = await db.execute(sql`
    SELECT s.*, p.name as plan_name, p.price_monthly, p.price_annual, p.features, p.limits
    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 rows[0] ?? null;
}

export async function getUsage(restaurantId: string) {
  await initDatabase();
  const [convResult, branchResult, apiCallsResult, voiceResult] = await Promise.all([
    /* raw: SELECT COUNT(*) as count FROM conversations WHERE restaurant_id = $1 AND created_at >= date_trunc('month', CURRENT_DATE) */
    db.execute(sql`SELECT COUNT(*) as count FROM conversations WHERE restaurant_id = ${restaurantId} AND created_at >= date_trunc('month', CURRENT_DATE)`),
    /* raw: SELECT COUNT(*) as count FROM branches WHERE restaurant_id = $1 AND is_active = true */
    db.execute(sql`SELECT COUNT(*) as count FROM branches WHERE restaurant_id = ${restaurantId} AND is_active = true`),
    /* raw: SELECT COALESCE(SUM(calls_this_month), 0) as total FROM api_keys WHERE restaurant_id = $1 */
    db.execute(sql`SELECT COALESCE(SUM(calls_this_month), 0) as total FROM api_keys WHERE restaurant_id = ${restaurantId}`),
    /* raw: SELECT COALESCE(SUM(duration_seconds), 0) as total FROM call_logs WHERE restaurant_id = $1 AND created_at >= date_trunc('month', CURRENT_DATE) */
    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() {
  await initDatabase();
  /* raw: SELECT * FROM plans WHERE is_active = true ORDER BY price_monthly ASC */
  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();

  /* raw: SELECT id, COALESCE(calls_this_month, 0)::text as calls_this_month FROM api_keys WHERE restaurant_id = $1 */
  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;
}
