import bcrypt from 'bcryptjs';
import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';

export async function getAdminDashboardStats() {
  await initDatabase();

  const [mrrResult, restaurantResult, conversationResult, churnResult] = await Promise.all([
    /* raw: SELECT COALESCE(SUM(p.price_monthly), 0) as mrr FROM subscriptions s JOIN plans p ON s.plan_id = p.id WHERE s.status IN ('active', 'past_due') */
    db.execute(sql`SELECT COALESCE(SUM(p.price_monthly), 0) as mrr FROM subscriptions s JOIN plans p ON s.plan_id = p.id WHERE s.status IN ('active', 'past_due')`),
    /* raw: SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status = 'active') as active FROM restaurants */
    db.execute(sql`SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status = 'active') as active FROM restaurants`),
    /* raw: SELECT COUNT(*) as total FROM conversations */
    db.execute(sql`SELECT COUNT(*) as total FROM conversations`),
    /* raw: SELECT COUNT(*) FILTER (WHERE s.status = 'cancelled') as churned, COUNT(*) as total FROM subscriptions s */
    db.execute(sql`SELECT COUNT(*) FILTER (WHERE s.status = 'cancelled') as churned, COUNT(*) as total FROM subscriptions s`),
  ]);

  const mrrRow = mrrResult.rows[0] as { mrr: string } | undefined;
  const restaurantCount = restaurantResult.rows[0] as { total: string; active: string } | undefined;
  const conversationCount = conversationResult.rows[0] as { total: string } | undefined;
  const churnRow = churnResult.rows[0] as { churned: string; total: string } | undefined;

  const mrr = parseFloat(mrrRow?.mrr ?? '0');
  const activeRestaurants = parseInt(restaurantCount?.active ?? '0', 10);
  const totalConversations = parseInt(conversationCount?.total ?? '0', 10);
  const churned = parseInt(churnRow?.churned ?? '0', 10);
  const totalSubs = parseInt(churnRow?.total ?? '0', 10);
  const churnRate = totalSubs > 0 ? ((churned / totalSubs) * 100) : 0;

  return {
    mrr,
    activeRestaurants,
    totalConversations,
    churnRate: parseFloat(churnRate.toFixed(1)),
  };
}

export async function getAdminMrrHistory() {
  await initDatabase();
  /* raw: SELECT TO_CHAR(DATE_TRUNC('month', s.created_at), 'Mon') as month, COALESCE(SUM(p.price_monthly), 0) as mrr FROM subscriptions s JOIN plans p ON ... WHERE s.status IN (...) AND s.created_at >= ... GROUP BY ... ORDER BY ... */
  const { rows } = await db.execute(sql`
    SELECT
      TO_CHAR(DATE_TRUNC('month', s.created_at), 'Mon') as month,
      COALESCE(SUM(p.price_monthly), 0) as mrr
    FROM subscriptions s
    JOIN plans p ON s.plan_id = p.id
    WHERE s.status IN ('active', 'past_due', 'cancelled')
      AND s.created_at >= CURRENT_DATE - INTERVAL '7 months'
    GROUP BY DATE_TRUNC('month', s.created_at)
    ORDER BY DATE_TRUNC('month', s.created_at) ASC
  `);
  return (rows as { month: string; mrr: string }[]).map(r => ({ month: r.month, mrr: parseFloat(r.mrr) }));
}

export async function getAdminRecentRestaurants() {
  await initDatabase();
  /* raw: SELECT r.name, COALESCE(p.name, r.plan) as plan_name, r.status, r.created_at, COALESCE(p.price_monthly, 0) as price_monthly FROM restaurants r LEFT JOIN LATERAL (...) ls ON true LEFT JOIN plans p ON ... ORDER BY r.created_at DESC LIMIT 10 */
  const { rows } = await db.execute(sql`
    SELECT r.name, COALESCE(p.name, r.plan) as plan_name, r.status,
           r.created_at, COALESCE(p.price_monthly, 0) as price_monthly
    FROM restaurants r
    LEFT JOIN LATERAL (
      SELECT s.plan_id FROM subscriptions s
      WHERE s.restaurant_id = r.id
      ORDER BY s.created_at DESC LIMIT 1
    ) ls ON true
    LEFT JOIN plans p ON ls.plan_id = p.id
    ORDER BY r.created_at DESC
    LIMIT 10
  `);
  return (rows as { name: string; plan_name: string; status: string; created_at: string; price_monthly: string }[]).map(r => ({
    name: r.name,
    plan: r.plan_name ?? 'N/A',
    status: r.status ?? 'active',
    createdAt: r.created_at,
    mrr: parseFloat(r.price_monthly ?? '0'),
  }));
}

export async function getAdminPlatformAnalytics() {
  await initDatabase();
  const [convResult, apiCallsResult, latencyResult, escalationResult] = await Promise.all([
    /* raw: SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE DATE(created_at) = CURRENT_DATE) AS today FROM conversations */
    db.execute(sql`SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE DATE(created_at) = CURRENT_DATE) AS today FROM conversations`),
    /* raw: SELECT COALESCE(SUM(calls_this_month), 0) AS total_calls FROM api_keys */
    db.execute(sql`SELECT COALESCE(SUM(calls_this_month), 0) AS total_calls FROM api_keys`),
    /* raw: SELECT COALESCE(AVG(duration_seconds), 0) AS avg_duration, COUNT(*) AS total_calls FROM call_logs WHERE created_at >= CURRENT_DATE - INTERVAL '14 days' */
    db.execute(sql`SELECT COALESCE(AVG(duration_seconds), 0) AS avg_duration, COUNT(*) AS total_calls FROM call_logs WHERE created_at >= CURRENT_DATE - INTERVAL '14 days'`),
    /* raw: SELECT COUNT(*) FILTER (WHERE status = 'human') AS escalated, COUNT(*) AS total_convs FROM conversations WHERE created_at >= CURRENT_DATE - INTERVAL '14 days' */
    db.execute(sql`SELECT COUNT(*) FILTER (WHERE status = 'human') AS escalated, COUNT(*) AS total_convs FROM conversations WHERE created_at >= CURRENT_DATE - INTERVAL '14 days'`),
  ]);

  const convStats = convResult.rows[0] as { total: string; today: string } | undefined;
  const apiCallsRow = apiCallsResult.rows[0] as { total_calls: string } | undefined;
  const latencyRow = latencyResult.rows[0] as { avg_duration: string; total_calls: string } | undefined;
  const escalationRow = escalationResult.rows[0] as { escalated: string; total_convs: string } | undefined;

  const totalConvs14d = parseInt(escalationRow?.total_convs ?? '0', 10);
  const escalated14d = parseInt(escalationRow?.escalated ?? '0', 10);
  const escalationRate = totalConvs14d > 0
    ? parseFloat(((escalated14d / totalConvs14d) * 100).toFixed(2))
    : 0;

  return {
    totalConversations: parseInt(convStats?.total ?? '0', 10),
    todayConversations: parseInt(convStats?.today ?? '0', 10),
    totalApiCalls: parseInt(apiCallsRow?.total_calls ?? '0', 10),
    avgCallDuration: parseFloat(parseFloat(latencyRow?.avg_duration ?? '0').toFixed(1)),
    totalVoiceCalls: parseInt(latencyRow?.total_calls ?? '0', 10),
    escalationRate,
    escalatedCount: escalated14d,
  };
}

export async function getAdminConversationTrend() {
  await initDatabase();
  /* raw: SELECT TO_CHAR(DATE(created_at), 'Mon DD') as date, COUNT(*) as conversations FROM conversations WHERE created_at >= CURRENT_DATE - INTERVAL '14 days' GROUP BY DATE(created_at) ORDER BY ... */
  const { rows } = await db.execute(sql`
    SELECT TO_CHAR(DATE(created_at), 'Mon DD') as date,
           COUNT(*) as conversations
    FROM conversations
    WHERE created_at >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY DATE(created_at)
    ORDER BY DATE(created_at) ASC
  `);
  return (rows as { date: string; conversations: string }[]).map(r => ({
    date: r.date,
    conversations: parseInt(r.conversations, 10),
  }));
}

export async function getAdminCallLatencyTrend() {
  await initDatabase();
  /* raw: SELECT TO_CHAR(DATE(created_at), 'Mon DD') as date, COALESCE(AVG(duration_seconds), 0) as avg_duration, COUNT(*) as call_count FROM call_logs WHERE created_at >= CURRENT_DATE - INTERVAL '14 days' GROUP BY ... ORDER BY ... */
  const { rows } = await db.execute(sql`
    SELECT TO_CHAR(DATE(created_at), 'Mon DD') as date,
           COALESCE(AVG(duration_seconds), 0) as avg_duration,
           COUNT(*) as call_count
    FROM call_logs
    WHERE created_at >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY DATE(created_at)
    ORDER BY DATE(created_at) ASC
  `);
  return (rows as { date: string; avg_duration: string; call_count: string }[]).map(r => ({
    date: r.date,
    avgDuration: parseFloat(parseFloat(r.avg_duration).toFixed(1)),
    callCount: parseInt(r.call_count, 10),
  }));
}

export async function getAdminErrorBreakdown() {
  await initDatabase();
  /* raw: SELECT status, COUNT(*) as count FROM conversations WHERE created_at >= CURRENT_DATE - INTERVAL '14 days' GROUP BY status ORDER BY count DESC */
  const { rows } = await db.execute(sql`
    SELECT status, COUNT(*) as count
    From conversations
    WHERE created_at >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY status
    ORDER BY count DESC
  `);
  return (rows as { status: string; count: string }[]).map(r => ({
    type: r.status === 'human' ? 'Escalated to Human'
        : r.status === 'resolved' ? 'Resolved'
        : r.status === 'ai' ? 'AI Handled'
        : r.status,
    count: parseInt(r.count, 10),
  }));
}

export async function getAdminRestaurantList() {
  await initDatabase();
  /* raw: SELECT r.id, r.display_id, r.name, u.name as owner_name, u.email as owner_email, p.name as plan_name, s.status as sub_status, r.status as r_status, COUNT(DISTINCT b.id) as branch_count, p.price_monthly, s.trial_end, r.created_at FROM restaurants r LEFT JOIN users u ... LEFT JOIN subscriptions s ... LEFT JOIN plans p ... LEFT JOIN branches b ... GROUP BY ... ORDER BY r.created_at DESC */
  const { rows } = await db.execute(sql`
    SELECT r.id, r.display_id, r.name,
           COALESCE(u.name, '') as owner_name,
           COALESCE(u.email, '') as owner_email,
           COALESCE(p.name, r.plan, 'N/A') as plan_name,
           COALESCE(s.status, 'trial') as sub_status,
           COALESCE(r.status, 'active') as r_status,
           COUNT(DISTINCT b.id) as branch_count,
           COALESCE(p.price_monthly, 0) as price_monthly,
           s.trial_end,
           r.created_at
    FROM restaurants r
    LEFT JOIN users u ON u.id = r.owner_id
    LEFT JOIN subscriptions s ON s.restaurant_id = r.id
    LEFT JOIN plans p ON s.plan_id = p.id
    LEFT JOIN branches b ON b.restaurant_id = r.id
    GROUP BY r.id, r.display_id, r.name, u.name, u.email, p.name, r.plan, s.status, r.status, p.price_monthly, s.trial_end, r.created_at
    ORDER BY r.created_at DESC
  `);
  return (rows as {
    id: string; display_id: string | null; name: string; owner_name: string; owner_email: string;
    plan_name: string; sub_status: string; r_status: string;
    branch_count: string; price_monthly: string;
    trial_end: string | null; created_at: string;
  }[]).map(r => ({
    id: r.id,
    displayId: r.display_id ?? ('RST-' + r.id.replace(/-/g, '').slice(0, 7).toUpperCase()),
    name: r.name,
    owner: r.owner_name,
    email: r.owner_email,
    plan: r.plan_name,
    status: r.sub_status,
    restaurantStatus: r.r_status,
    branches: parseInt(r.branch_count ?? '0', 10),
    mrr: r.sub_status === 'active' ? parseFloat(r.price_monthly ?? '0') : 0,
    trialEnd: r.trial_end ?? null,
    joinedDate: r.created_at,
  }));
}

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

  /* raw: SELECT r.id, r.display_id, r.name, u.name as owner_name, u.email as owner_email, r.phone, r.cuisine_type, r.address, r.description, r.seating_capacity, r.status, r.created_at, s.id as sub_id, s.plan_id, p.name as plan_name, p.price_monthly, s.status as sub_status, s.trial_start, s.trial_end, s.current_period_end, s.stripe_subscription_id FROM restaurants r LEFT JOIN users u ... LEFT JOIN subscriptions s ... LEFT JOIN plans p ... WHERE r.id = $1 */
  const { rows: restaurantRows } = await db.execute(sql`
    SELECT r.id, r.display_id, r.name,
           COALESCE(u.name, '') as owner_name,
           COALESCE(u.email, '') as owner_email,
           r.phone, r.cuisine_type, r.address, r.description, r.seating_capacity,
           COALESCE(r.status, 'active') as status,
           r.created_at,
           s.id as sub_id, s.plan_id, COALESCE(p.name, r.plan) as plan_name,
           p.price_monthly,
           s.status as sub_status, s.trial_start, s.trial_end,
           s.current_period_end, s.stripe_subscription_id
    FROM restaurants r
    LEFT JOIN users u ON u.id = r.owner_id
    LEFT JOIN subscriptions s ON s.restaurant_id = r.id
    LEFT JOIN plans p ON s.plan_id = p.id
    WHERE r.id = ${restaurantId}
  `);

  const restaurant = restaurantRows[0] as {
    id: string; display_id: string | null; name: string; owner_name: string; owner_email: string;
    phone: string | null; cuisine_type: string | null; address: string | null;
    description: string | null; seating_capacity: number | null;
    status: string; created_at: string;
    plan_id: string | null; plan_name: string | null; price_monthly: string | null;
    sub_status: string | null; trial_start: string | null; trial_end: string | null;
    current_period_end: string | null; stripe_subscription_id: string | null;
    sub_id: string | null;
  } | undefined;

  if (!restaurant) return null;

  const [branchResult, usageResult, activityResult, plansResult] = await Promise.all([
    /* raw: SELECT id, name, is_active FROM branches WHERE restaurant_id = $1 ORDER BY name ASC */
    db.execute(sql`SELECT id, name, is_active FROM branches WHERE restaurant_id = ${restaurantId} ORDER BY name ASC`),
    /* raw: SELECT (SELECT COUNT(*) FROM conversations WHERE restaurant_id = $1) as conversations, (SELECT COALESCE(SUM(calls_this_month), 0) FROM api_keys WHERE restaurant_id = $1) as ai_queries, (SELECT COUNT(*) FROM orders WHERE restaurant_id = $1) as orders */
    db.execute(sql`
      SELECT
        (SELECT COUNT(*) FROM conversations WHERE restaurant_id = ${restaurantId}) as conversations,
        (SELECT COALESCE(SUM(calls_this_month), 0) FROM api_keys WHERE restaurant_id = ${restaurantId}) as ai_queries,
        (SELECT COUNT(*) FROM orders WHERE restaurant_id = ${restaurantId}) as orders
    `),
    /* raw: (SELECT 'conversation' as event_type, ... FROM conversations WHERE restaurant_id = $1 ...) UNION ALL (SELECT 'order' as event_type, ... FROM orders WHERE restaurant_id = $1 ...) ORDER BY created_at DESC LIMIT 10 */
    db.execute(sql`
      (
        SELECT 'conversation' as event_type,
               'New conversation started' as description,
               created_at,
               'Customer' as actor
        FROM conversations
        WHERE restaurant_id = ${restaurantId}
        ORDER BY created_at DESC LIMIT 5
      )
      UNION ALL
      (
        SELECT 'order' as event_type,
               'Order #' || COALESCE(order_number::text, id::text) || ' placed' as description,
               created_at,
               'Customer' as actor
        FROM orders
        WHERE restaurant_id = ${restaurantId}
        ORDER BY created_at DESC LIMIT 5
      )
      ORDER BY created_at DESC
      LIMIT 10
    `).catch(() => ({ rows: [] as { event_type: string; description: string; created_at: string; actor: string }[] })),
    /* raw: SELECT id, name, price_monthly FROM plans WHERE is_active = true ORDER BY price_monthly ASC */
    db.execute(sql`SELECT id, name, price_monthly FROM plans WHERE is_active = true ORDER BY price_monthly ASC`),
  ]);

  const branches = branchResult.rows as { id: string; name: string; is_active: boolean }[];
  const usage = usageResult.rows[0] as { ai_queries: string; conversations: string; orders: string } | undefined;
  const recentActivity = activityResult.rows as { event_type: string; description: string; created_at: string; actor: string }[];
  const plans = plansResult.rows as { id: string; name: string; price_monthly: string }[];

  return {
    id: restaurant.id,
    displayId: restaurant.display_id ?? ('RST-' + restaurant.id.replace(/-/g, '').slice(0, 7).toUpperCase()),
    name: restaurant.name,
    owner: restaurant.owner_name,
    email: restaurant.owner_email,
    phone: restaurant.phone,
    cuisineType: restaurant.cuisine_type,
    address: restaurant.address,
    description: restaurant.description,
    seatingCapacity: restaurant.seating_capacity,
    status: restaurant.status,
    joinedDate: restaurant.created_at,
    subscription: {
      id: restaurant.sub_id,
      planId: restaurant.plan_id,
      planName: restaurant.plan_name,
      priceMonthly: parseFloat(restaurant.price_monthly ?? '0'),
      status: restaurant.sub_status,
      trialStart: restaurant.trial_start,
      trialEnd: restaurant.trial_end,
      currentPeriodEnd: restaurant.current_period_end,
      stripeSubscriptionId: restaurant.stripe_subscription_id,
    },
    branches: branches.map(b => ({ id: b.id, name: b.name, isActive: b.is_active })),
    usage: {
      conversations: parseInt(usage?.conversations ?? '0', 10),
      aiQueries: parseInt(usage?.ai_queries ?? '0', 10),
      orders: parseInt(usage?.orders ?? '0', 10),
    },
    recentActivity: recentActivity.map(a => ({
      eventType: a.event_type,
      description: a.description,
      actor: a.actor,
      time: a.created_at,
    })),
    availablePlans: plans.map(p => ({ id: p.id, name: p.name, priceMonthly: parseFloat(p.price_monthly ?? '0') })),
  };
}

export async function updateRestaurantSubscription(
  restaurantId: string,
  payload: { action?: 'grant_trial' | 'revoke_trial'; trialDays?: number; planId?: string }
) {
  await initDatabase();

  if (payload.planId) {
    /* raw: SELECT id FROM plans WHERE id = $1 */
    const { rows: planRows } = await db.execute(sql`SELECT id FROM plans WHERE id = ${payload.planId}`);
    if (!planRows[0]) {
      throw new Error(`Plan not found: ${payload.planId}`);
    }
  }

  /* raw: SELECT id FROM subscriptions WHERE restaurant_id = $1 ORDER BY created_at DESC LIMIT 1 */
  const { rows: existingRows } = await db.execute(sql`SELECT id FROM subscriptions WHERE restaurant_id = ${restaurantId} ORDER BY created_at DESC LIMIT 1`);
  const existing = existingRows[0] as { id: string } | undefined;

  if (payload.action === 'grant_trial') {
    const days = Math.min(Math.max(1, Math.floor(payload.trialDays ?? 14)), 365);
    const planId = payload.planId ?? null;
    if (existing) {
      /* raw: UPDATE subscriptions SET status = 'trial', trial_start = NOW(), trial_end = NOW() + ($1 || ' days')::INTERVAL WHERE restaurant_id = $2 */
      await db.execute(sql`UPDATE subscriptions SET status = 'trial', trial_start = NOW(), trial_end = NOW() + (${String(days)} || ' days')::INTERVAL WHERE restaurant_id = ${restaurantId}`);
    } else {
      /* raw: INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_start, trial_end) VALUES ($1, $2, 'trial', NOW(), NOW() + ($3 || ' days')::INTERVAL) */
      await db.execute(sql`INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_start, trial_end) VALUES (${restaurantId}, ${planId}, 'trial', NOW(), NOW() + (${String(days)} || ' days')::INTERVAL)`);
      return;
    }
  } else if (payload.action === 'revoke_trial') {
    if (existing) {
      /* raw: UPDATE subscriptions SET status = 'inactive', trial_end = NOW() WHERE restaurant_id = $1 */
      await db.execute(sql`UPDATE subscriptions SET status = 'inactive', trial_end = NOW() WHERE restaurant_id = ${restaurantId}`);
    } else {
      /* raw: INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_end) VALUES ($1, $2, 'inactive', NOW()) */
      await db.execute(sql`INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_end) VALUES (${restaurantId}, ${payload.planId ?? null}, 'inactive', NOW())`);
      return;
    }
  }

  if (payload.planId) {
    if (existing) {
      if (payload.action) {
        /* raw: UPDATE subscriptions SET plan_id = $1 WHERE restaurant_id = $2 */
        await db.execute(sql`UPDATE subscriptions SET plan_id = ${payload.planId} WHERE restaurant_id = ${restaurantId}`);
      } else {
        /* raw: UPDATE subscriptions SET plan_id = $1, status = 'active' WHERE restaurant_id = $2 */
        await db.execute(sql`UPDATE subscriptions SET plan_id = ${payload.planId}, status = 'active' WHERE restaurant_id = ${restaurantId}`);
      }
    } else {
      const status = payload.action ? 'inactive' : 'active';
      /* raw: INSERT INTO subscriptions (restaurant_id, plan_id, status) VALUES ($1, $2, $3) */
      await db.execute(sql`INSERT INTO subscriptions (restaurant_id, plan_id, status) VALUES (${restaurantId}, ${payload.planId}, ${status})`);
    }
  }
}

export async function getAuditLogs(filters?: {
  search?: string; severity?: string;
}) {
  await initDatabase();
  const conditions: SQL[] = [];

  if (filters?.search) {
    const pattern = `%${filters.search}%`;
    conditions.push(sql`(actor_email ILIKE ${pattern} OR action ILIKE ${pattern} OR resource ILIKE ${pattern})`);
  }
  if (filters?.severity && filters.severity !== 'all') {
    conditions.push(sql`severity = ${filters.severity}`);
  }

  const where = conditions.length > 0
    ? sql`WHERE ${sql.join(conditions, sql` AND `)}`
    : sql``;

  /* raw: SELECT id, COALESCE(actor_email, 'System') as actor_email, actor_type, action, COALESCE(resource, '') as resource, COALESCE(ip_address, 'system') as ip_address, severity, created_at FROM audit_logs [WHERE ...] ORDER BY created_at DESC LIMIT 100 */
  const { rows } = await db.execute(sql`
    SELECT id, COALESCE(actor_email, 'System') as actor_email,
           actor_type, action, COALESCE(resource, '') as resource,
           COALESCE(ip_address, 'system') as ip_address,
           severity, created_at
    FROM audit_logs ${where}
    ORDER BY created_at DESC
    LIMIT 100
  `);

  return (rows as {
    id: string; actor_email: string; actor_type: string;
    action: string; resource: string; ip_address: string;
    severity: string; created_at: string;
  }[]).map(r => ({
    id: r.id,
    actor: r.actor_email,
    actorType: r.actor_type,
    action: r.action,
    resource: r.resource,
    timestamp: r.created_at,
    ip: r.ip_address,
    severity: r.severity,
  }));
}

export async function getAdminBillingPlans() {
  await initDatabase();
  /* raw: SELECT p.id, p.name, p.price_monthly, p.features, COUNT(s.id) FILTER (...) as subscribers, COALESCE(SUM(p.price_monthly) FILTER (...), 0) as mrr FROM plans p LEFT JOIN subscriptions s ON ... WHERE p.is_active = true GROUP BY p.id ORDER BY p.price_monthly ASC */
  const { rows } = await db.execute(sql`
    SELECT p.id, p.name, p.price_monthly,
           p.features,
           COUNT(s.id) FILTER (WHERE s.status IN ('active','trial','past_due')) as subscribers,
           COALESCE(SUM(p.price_monthly) FILTER (WHERE s.status IN ('active','past_due')), 0) as mrr
    FROM plans p
    LEFT JOIN subscriptions s ON s.plan_id = p.id
    WHERE p.is_active = true
    GROUP BY p.id
    ORDER BY p.price_monthly ASC
  `);
  return (rows as { id: string; name: string; price_monthly: string; features: string; subscribers: string; mrr: string }[]).map(r => ({
    id: r.id,
    name: r.name,
    price: parseFloat(r.price_monthly),
    features: typeof r.features === 'string' ? JSON.parse(r.features) : r.features,
    subscribers: parseInt(r.subscribers ?? '0', 10),
    mrr: parseFloat(r.mrr ?? '0'),
  }));
}

export async function getAdminBillingPlansAll() {
  await initDatabase();
  /* raw: SELECT p.id, p.name, p.description, p.price_monthly, p.price_annual, p.trial_days, p.features, p.limits, p.is_active, p.stripe_product_id, p.stripe_price_id_monthly, p.stripe_price_id_annual, COUNT(s.id) FILTER (...) as subscribers, COALESCE(SUM(p.price_monthly) FILTER (...), 0) as mrr FROM plans p LEFT JOIN subscriptions s ON ... GROUP BY p.id ORDER BY p.price_monthly ASC */
  const { rows } = await db.execute(sql`
    SELECT p.id, p.name, p.description, p.price_monthly, p.price_annual,
           p.trial_days, p.features, p.limits, p.is_active,
           p.stripe_product_id, p.stripe_price_id_monthly, p.stripe_price_id_annual,
           COUNT(s.id) FILTER (WHERE s.status IN ('active','trial','past_due')) as subscribers,
           COALESCE(SUM(p.price_monthly) FILTER (WHERE s.status IN ('active','past_due')), 0) as mrr
    FROM plans p
    LEFT JOIN subscriptions s ON s.plan_id = p.id
    GROUP BY p.id
    ORDER BY p.price_monthly ASC
  `);
  const { getStripeSecretKey } = await import('@server/lib/stripe');
  const stripeConfigured = Boolean(await getStripeSecretKey());
  return (rows as {
    id: string; name: string; description: string | null;
    price_monthly: string; price_annual: string;
    trial_days: string | null; features: string; limits: string;
    is_active: boolean; stripe_product_id: string | null;
    stripe_price_id_monthly: string | null; stripe_price_id_annual: string | null;
    subscribers: string; mrr: string;
  }[]).map(r => {
    const limits = typeof r.limits === 'string' ? (JSON.parse(r.limits) as Record<string, unknown>) : (r.limits as Record<string, unknown> || {});
    const features = typeof r.features === 'string' ? (JSON.parse(r.features) as unknown[]) : (r.features || []);
    const hasStripeProduct = Boolean(r.stripe_product_id);
    const stripeStatus = !stripeConfigured ? 'not_configured'
      : hasStripeProduct ? 'synced'
      : 'not_synced';
    return {
      id: r.id,
      name: r.name,
      description: r.description ?? '',
      priceMonthly: parseFloat(r.price_monthly),
      priceAnnual: parseFloat(r.price_annual ?? '0'),
      trialDays: parseInt(r.trial_days ?? '14', 10),
      features,
      limits,
      isActive: r.is_active,
      stripeProductId: r.stripe_product_id ?? null,
      stripePriceIdMonthly: r.stripe_price_id_monthly ?? null,
      stripePriceIdAnnual: r.stripe_price_id_annual ?? null,
      subscribers: parseInt(r.subscribers ?? '0', 10),
      mrr: parseFloat(r.mrr ?? '0'),
      stripeStatus,
    };
  });
}

export async function getAdminBillingPlanById(planId: string) {
  await initDatabase();
  /* raw: SELECT id, name, description, price_monthly, price_annual, trial_days, features, limits, is_active, stripe_product_id, stripe_price_id_monthly, stripe_price_id_annual FROM plans WHERE id = $1 */
  const { rows } = await db.execute(sql`
    SELECT id, name, description, price_monthly, price_annual, trial_days,
           features, limits, is_active,
           stripe_product_id, stripe_price_id_monthly, stripe_price_id_annual
    FROM plans WHERE id = ${planId}
  `);
  const row = rows[0] as {
    id: string; name: string; description: string | null;
    price_monthly: string; price_annual: string; trial_days: string | null;
    features: string; limits: string; is_active: boolean;
    stripe_product_id: string | null; stripe_price_id_monthly: string | null;
    stripe_price_id_annual: string | null;
  } | undefined;
  if (!row) return null;
  return {
    id: row.id,
    name: row.name,
    description: row.description ?? '',
    priceMonthly: parseFloat(row.price_monthly),
    priceAnnual: parseFloat(row.price_annual ?? '0'),
    trialDays: parseInt(row.trial_days ?? '14', 10),
    features: typeof row.features === 'string' ? JSON.parse(row.features) : (row.features ?? []),
    limits: typeof row.limits === 'string' ? JSON.parse(row.limits) : (row.limits ?? {}),
    isActive: row.is_active,
    stripeProductId: row.stripe_product_id ?? null,
    stripePriceIdMonthly: row.stripe_price_id_monthly ?? null,
    stripePriceIdAnnual: row.stripe_price_id_annual ?? null,
  };
}

interface PlanPayload {
  name: string;
  description?: string;
  priceMonthly: number;
  priceAnnual?: number;
  trialDays?: number;
  features?: string[];
  limits?: Record<string, unknown>;
  isActive?: boolean;
}

async function syncPlanWithStripe(planId: string, payload: PlanPayload & { stripeProductId?: string | null; stripePriceIdMonthly?: string | null; stripePriceIdAnnual?: string | null }) {
  const { getStripeSecretKey, getStripeClientFromDb } = await import('@server/lib/stripe');
  const key = await getStripeSecretKey();
  if (!key) return {
    stripeProductId: payload.stripeProductId ?? null,
    stripePriceIdMonthly: payload.stripePriceIdMonthly ?? null,
    stripePriceIdAnnual: payload.stripePriceIdAnnual ?? null,
    stripeSkipped: true,
  };

  const stripe = await getStripeClientFromDb();

  let stripeProductId = payload.stripeProductId ?? null;
  let stripePriceIdMonthly = payload.stripePriceIdMonthly ?? null;
  let stripePriceIdAnnual = payload.stripePriceIdAnnual ?? null;

  if (!stripeProductId) {
    const product = await stripe.products.create({
      name: payload.name,
      description: payload.description || undefined,
      metadata: { restroagent_plan_id: planId },
    });
    stripeProductId = product.id;
  } else {
    await stripe.products.update(stripeProductId, {
      name: payload.name,
      description: payload.description || undefined,
    });
  }

  const priceMonthly = payload.priceMonthly;
  if (priceMonthly > 0) {
    if (stripePriceIdMonthly && payload.stripeProductId) {
      const existingPrice = await stripe.prices.retrieve(stripePriceIdMonthly).catch(() => null);
      const existingUnitAmount = existingPrice?.unit_amount ?? null;
      if (existingUnitAmount !== Math.round(priceMonthly * 100)) {
        await stripe.prices.update(stripePriceIdMonthly, { active: false });
        const newPrice = await stripe.prices.create({
          product: stripeProductId,
          unit_amount: Math.round(priceMonthly * 100),
          currency: 'usd',
          recurring: { interval: 'month' },
          metadata: { billing_cycle: 'monthly' },
        });
        stripePriceIdMonthly = newPrice.id;
      }
    } else {
      const newPrice = await stripe.prices.create({
        product: stripeProductId,
        unit_amount: Math.round(priceMonthly * 100),
        currency: 'usd',
        recurring: { interval: 'month' },
        metadata: { billing_cycle: 'monthly' },
      });
      stripePriceIdMonthly = newPrice.id;
    }
  }

  const priceAnnual = payload.priceAnnual ?? 0;
  if (priceAnnual > 0) {
    if (stripePriceIdAnnual && payload.stripeProductId) {
      const existingPrice = await stripe.prices.retrieve(stripePriceIdAnnual).catch(() => null);
      const existingUnitAmount = existingPrice?.unit_amount ?? null;
      if (existingUnitAmount !== Math.round(priceAnnual * 100)) {
        await stripe.prices.update(stripePriceIdAnnual, { active: false });
        const newPrice = await stripe.prices.create({
          product: stripeProductId,
          unit_amount: Math.round(priceAnnual * 100),
          currency: 'usd',
          recurring: { interval: 'year' },
          metadata: { billing_cycle: 'annual' },
        });
        stripePriceIdAnnual = newPrice.id;
      }
    } else {
      const newPrice = await stripe.prices.create({
        product: stripeProductId,
        unit_amount: Math.round(priceAnnual * 100),
        currency: 'usd',
        recurring: { interval: 'year' },
        metadata: { billing_cycle: 'annual' },
      });
      stripePriceIdAnnual = newPrice.id;
    }
  }

  return { stripeProductId, stripePriceIdMonthly, stripePriceIdAnnual, stripeSkipped: false };
}

export async function createAdminBillingPlan(payload: PlanPayload) {
  await initDatabase();

  /* raw: INSERT INTO plans (name, description, price_monthly, price_annual, trial_days, features, limits, is_active) VALUES ($1, $2, $3, $4, $5, $6::jsonb, $7::jsonb, $8) RETURNING id */
  const { rows: insertRows } = await db.execute(sql`
    INSERT INTO plans (name, description, price_monthly, price_annual, trial_days, features, limits, is_active)
    VALUES (${payload.name}, ${payload.description ?? null}, ${payload.priceMonthly}, ${payload.priceAnnual ?? 0},
            ${payload.trialDays ?? 14}, ${JSON.stringify(payload.features ?? [])}::jsonb, ${JSON.stringify(payload.limits ?? {})}::jsonb, ${payload.isActive !== false})
    RETURNING id
  `);

  const id = (insertRows[0] as { id: string } | undefined)?.id;
  if (!id) throw new Error('Failed to create plan');

  const stripeResult = await syncPlanWithStripe(id, { ...payload, stripeProductId: null, stripePriceIdMonthly: null, stripePriceIdAnnual: null }).catch(err => {
    console.error('[billing] Stripe sync failed on create:', err);
    return { stripeProductId: null, stripePriceIdMonthly: null, stripePriceIdAnnual: null, stripeSkipped: true };
  });

  /* raw: UPDATE plans SET stripe_product_id = $1, stripe_price_id_monthly = $2, stripe_price_id_annual = $3 WHERE id = $4 */
  await db.execute(sql`UPDATE plans SET stripe_product_id = ${stripeResult.stripeProductId}, stripe_price_id_monthly = ${stripeResult.stripePriceIdMonthly}, stripe_price_id_annual = ${stripeResult.stripePriceIdAnnual} WHERE id = ${id}`);

  return { id, stripeSkipped: stripeResult.stripeSkipped };
}

export async function updateAdminBillingPlan(planId: string, payload: PlanPayload) {
  await initDatabase();

  const existing = await getAdminBillingPlanById(planId);
  if (!existing) throw new Error('Plan not found');

  /* raw: UPDATE plans SET name = $1, description = $2, price_monthly = $3, price_annual = $4, trial_days = $5, features = $6::jsonb, limits = $7::jsonb, is_active = $8 WHERE id = $9 */
  await db.execute(sql`
    UPDATE plans SET
      name = ${payload.name}, description = ${payload.description ?? null}, price_monthly = ${payload.priceMonthly}, price_annual = ${payload.priceAnnual ?? 0},
      trial_days = ${payload.trialDays ?? 14}, features = ${JSON.stringify(payload.features ?? [])}::jsonb, limits = ${JSON.stringify(payload.limits ?? {})}::jsonb, is_active = ${payload.isActive !== false}
    WHERE id = ${planId}
  `);

  const stripeResult = await syncPlanWithStripe(planId, {
    ...payload,
    stripeProductId: existing.stripeProductId,
    stripePriceIdMonthly: existing.stripePriceIdMonthly,
    stripePriceIdAnnual: existing.stripePriceIdAnnual,
  }).catch(err => {
    console.error('[billing] Stripe sync failed on update:', err);
    return { stripeProductId: existing.stripeProductId, stripePriceIdMonthly: existing.stripePriceIdMonthly, stripePriceIdAnnual: existing.stripePriceIdAnnual, stripeSkipped: true };
  });

  if (!stripeResult.stripeSkipped || stripeResult.stripeProductId !== existing.stripeProductId) {
    /* raw: UPDATE plans SET stripe_product_id = $1, stripe_price_id_monthly = $2, stripe_price_id_annual = $3 WHERE id = $4 */
    await db.execute(sql`UPDATE plans SET stripe_product_id = ${stripeResult.stripeProductId}, stripe_price_id_monthly = ${stripeResult.stripePriceIdMonthly}, stripe_price_id_annual = ${stripeResult.stripePriceIdAnnual} WHERE id = ${planId}`);
  }

  return { stripeSkipped: stripeResult.stripeSkipped };
}

export async function syncAdminBillingPlan(planId: string) {
  await initDatabase();
  const existing = await getAdminBillingPlanById(planId);
  if (!existing) return null;

  const stripeResult = await syncPlanWithStripe(planId, {
    name: existing.name,
    description: existing.description,
    priceMonthly: existing.priceMonthly,
    priceAnnual: existing.priceAnnual,
    trialDays: existing.trialDays,
    features: existing.features as string[],
    limits: existing.limits,
    isActive: existing.isActive,
    stripeProductId: existing.stripeProductId,
    stripePriceIdMonthly: existing.stripePriceIdMonthly,
    stripePriceIdAnnual: existing.stripePriceIdAnnual,
  });

  if (!stripeResult.stripeSkipped) {
    /* raw: UPDATE plans SET stripe_product_id = $1, stripe_price_id_monthly = $2, stripe_price_id_annual = $3 WHERE id = $4 */
    await db.execute(sql`UPDATE plans SET stripe_product_id = ${stripeResult.stripeProductId}, stripe_price_id_monthly = ${stripeResult.stripePriceIdMonthly}, stripe_price_id_annual = ${stripeResult.stripePriceIdAnnual} WHERE id = ${planId}`);
  }

  const { getStripeSecretKey } = await import('@server/lib/stripe');
  const stripeConfigured = Boolean(await getStripeSecretKey());
  const stripeStatus = !stripeConfigured ? 'not_configured'
    : stripeResult.stripeProductId ? 'synced'
    : 'not_synced';

  return { stripeSkipped: stripeResult.stripeSkipped, stripeStatus };
}

export async function deleteAdminBillingPlan(planId: string) {
  await initDatabase();

  const existing = await getAdminBillingPlanById(planId);
  if (!existing) throw new Error('Plan not found');

  /* raw: UPDATE plans SET is_active = false WHERE id = $1 */
  await db.execute(sql`UPDATE plans SET is_active = false WHERE id = ${planId}`);

  if (existing.stripeProductId) {
    try {
      const { getStripeSecretKey, getStripeClientFromDb } = await import('@server/lib/stripe');
      const key = await getStripeSecretKey();
      if (key) {
        const stripe = await getStripeClientFromDb();
        await stripe.products.update(existing.stripeProductId, { active: false });
      }
    } catch (err) {
      console.error('[billing] Stripe archive failed on delete:', err);
    }
  }
}

export async function getAdminBillingUsage() {
  await initDatabase();
  const [convResult, branchResult, apiCallsResult] = await Promise.all([
    /* raw: SELECT COUNT(*) as total FROM conversations */
    db.execute(sql`SELECT COUNT(*) as total FROM conversations`),
    /* raw: SELECT COUNT(*) as total FROM branches WHERE is_active = true */
    db.execute(sql`SELECT COUNT(*) as total FROM branches WHERE is_active = true`),
    /* raw: SELECT COALESCE(SUM(calls_this_month), 0) as total FROM api_keys */
    db.execute(sql`SELECT COALESCE(SUM(calls_this_month), 0) as total FROM api_keys`),
  ]);
  return {
    totalConversations: parseInt((convResult.rows[0] as { total: string })?.total ?? '0', 10),
    activeBranches: parseInt((branchResult.rows[0] as { total: string })?.total ?? '0', 10),
    apiCallsThisMonth: parseInt((apiCallsResult.rows[0] as { total: string })?.total ?? '0', 10),
  };
}

export async function getAdminInvoices() {
  await initDatabase();
  /* raw: SELECT s.id, r.name as restaurant_name, COALESCE(p.name, 'Unknown') as plan_name, COALESCE(p.price_monthly, 0) as price_monthly, s.status, COALESCE(s.current_period_end, s.created_at) as period_end FROM subscriptions s JOIN restaurants r ON ... LEFT JOIN plans p ON ... ORDER BY s.created_at DESC LIMIT 50 */
  const { rows } = await db.execute(sql`
    SELECT s.id, r.name as restaurant_name,
           COALESCE(p.name, 'Unknown') as plan_name,
           COALESCE(p.price_monthly, 0) as price_monthly,
           s.status,
           COALESCE(s.current_period_end, s.created_at) as period_end
    FROM subscriptions s
    JOIN restaurants r ON r.id = s.restaurant_id
    LEFT JOIN plans p ON s.plan_id = p.id
    ORDER BY s.created_at DESC
    LIMIT 50
  `);
  return (rows as {
    id: string; restaurant_name: string; plan_name: string;
    price_monthly: string; status: string; period_end: string;
  }[]).map(r => ({
    id: r.id,
    restaurant: r.restaurant_name,
    plan: r.plan_name,
    amount: parseFloat(r.price_monthly),
    status: r.status === 'active' || r.status === 'trial' ? 'paid'
          : r.status === 'past_due' ? 'pending'
          : r.status === 'cancelled' ? 'cancelled'
          : r.status,
    date: r.period_end,
  }));
}

export async function getPlatformSettings() {
  await initDatabase();
  /* raw: SELECT key, value FROM platform_settings ORDER BY key */
  const { rows } = await db.execute(sql`SELECT key, value FROM platform_settings ORDER BY key`);
  const settings: Record<string, unknown> = {};
  for (const row of rows as { key: string; value: string }[]) {
    if (typeof row.value === 'string' && row.value.trim() !== '') {
      try { settings[row.key] = JSON.parse(row.value); }
      catch { settings[row.key] = row.value; }
    } else {
      settings[row.key] = row.value;
    }
  }
  return settings;
}

export async function savePlatformSettings(
  settings: Record<string, unknown>,
  userId?: string
) {
  await initDatabase();
  for (const [key, value] of Object.entries(settings)) {
    const jsonValue = JSON.stringify(value);
    /* raw: INSERT INTO platform_settings (key, value, updated_by) VALUES ($1, $2::jsonb, $3) ON CONFLICT (key) DO UPDATE SET value = $2::jsonb, updated_by = $3, updated_at = now() */
    await db.execute(sql`
      INSERT INTO platform_settings (key, value, updated_by)
      VALUES (${key}, ${jsonValue}::jsonb, ${userId ?? null})
      ON CONFLICT (key) DO UPDATE SET value = ${jsonValue}::jsonb, updated_by = ${userId ?? null}, updated_at = now()
    `);
  }
}

export async function createAuditLog(entry: {
  actorId?: string;
  actorEmail?: string;
  actorType: string;
  action: string;
  resource?: string;
  resourceId?: string;
  ipAddress?: string;
  severity?: string;
  metadata?: Record<string, unknown>;
}) {
  await initDatabase();
  /* raw: INSERT INTO audit_logs (actor_id, actor_email, actor_type, action, resource, resource_id, ip_address, severity, metadata) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb) */
  await db.execute(sql`
    INSERT INTO audit_logs (actor_id, actor_email, actor_type, action, resource, resource_id, ip_address, severity, metadata)
    VALUES (${entry.actorId ?? null}, ${entry.actorEmail ?? null}, ${entry.actorType}, ${entry.action},
            ${entry.resource ?? null}, ${entry.resourceId ?? null}, ${entry.ipAddress ?? null},
            ${entry.severity ?? 'info'}, ${JSON.stringify(entry.metadata ?? {})}::jsonb)
  `);
}

export function generateDisplayId(): string {
  const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
  let id = 'RST-';
  for (let i = 0; i < 7; i++) {
    id += chars[Math.floor(Math.random() * chars.length)];
  }
  return id;
}

function generateRandomPassword(length = 16): string {
  const charset = 'abcdefghjkmnpqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ23456789!@#$%';
  const bytes = require('crypto').randomBytes(length);
  return Array.from(bytes as Uint8Array).map((b: number) => charset[b % charset.length]).join('');
}

export async function createAdminRestaurant(input: {
  restaurantName: string;
  ownerName: string;
  ownerEmail: string;
  ownerPassword?: string;
  plan: string;
  adminActorEmail?: string;
}) {
  await initDatabase();

  /* raw: SELECT id FROM users WHERE email = $1 */
  const { rows: existingRows } = await db.execute(sql`SELECT id FROM users WHERE email = ${input.ownerEmail.toLowerCase().trim()}`);
  if (existingRows[0]) throw new Error('An account with this email already exists');

  const wasPasswordProvided = !!(input.ownerPassword?.trim());
  const plainPassword = wasPasswordProvided ? input.ownerPassword!.trim() : generateRandomPassword();
  const passwordHash = await bcrypt.hash(plainPassword, 12);

  /* raw: SELECT id FROM plans WHERE LOWER(name) = LOWER($1) LIMIT 1 */
  const { rows: planRows } = await db.execute(sql`SELECT id FROM plans WHERE LOWER(name) = LOWER(${input.plan || 'starter'}) LIMIT 1`);
  let planRow = planRows[0] as { id: string } | undefined;
  if (!planRow) {
    /* raw: SELECT id FROM plans ORDER BY price_monthly ASC LIMIT 1 */
    const { rows: fallbackPlanRows } = await db.execute(sql`SELECT id FROM plans ORDER BY price_monthly ASC LIMIT 1`);
    planRow = fallbackPlanRows[0] as { id: string } | undefined;
  }

  const result = await db.transaction(async (tx) => {
    /* raw: INSERT INTO restaurants (name, owner_id, plan, status) VALUES ($1, gen_random_uuid(), $2, 'active') RETURNING id, name, display_id */
    const { rows: restaurantRows } = await tx.execute(sql`
      INSERT INTO restaurants (name, owner_id, plan, status)
      VALUES (${input.restaurantName.trim()}, gen_random_uuid(), ${input.plan || 'starter'}, 'active') RETURNING id, name, display_id
    `);
    const restaurant = restaurantRows[0] as { id: string; name: string; display_id: string } | undefined;
    if (!restaurant) throw new Error('Failed to create restaurant');

    /* raw: INSERT INTO branches (restaurant_id, name, is_active) VALUES ($1, $2, true) RETURNING id */
    const { rows: branchRows } = await tx.execute(sql`
      INSERT INTO branches (restaurant_id, name, is_active) VALUES (${restaurant.id}, ${input.restaurantName.trim() + ' — Main Branch'}, true) RETURNING id
    `);
    const branch = branchRows[0] as { id: string } | undefined;
    if (!branch) throw new Error('Failed to create branch');

    /* raw: INSERT INTO users (restaurant_id, branch_id, role, name, email, password_hash, is_active) VALUES ($1, $2, 'owner', $3, $4, $5, true) RETURNING id */
    const { rows: userRows } = await tx.execute(sql`
      INSERT INTO users (restaurant_id, branch_id, role, name, email, password_hash, is_active)
      VALUES (${restaurant.id}, ${branch.id}, 'owner', ${input.ownerName.trim() || input.ownerEmail.split('@')[0]},
              ${input.ownerEmail.toLowerCase().trim()}, ${passwordHash}, true) RETURNING id
    `);
    const user = userRows[0] as { id: string } | undefined;
    if (!user) throw new Error('Failed to create owner user');

    /* raw: UPDATE restaurants SET owner_id = $1 WHERE id = $2 */
    await tx.execute(sql`UPDATE restaurants SET owner_id = ${user.id} WHERE id = ${restaurant.id}`);
    /* raw: INSERT INTO ai_agent_configs (restaurant_id, branch_id, voice_enabled, chat_enabled, model) VALUES ($1, $2, true, true, 'gpt-4o') */
    await tx.execute(sql`INSERT INTO ai_agent_configs (restaurant_id, branch_id, voice_enabled, chat_enabled, model) VALUES (${restaurant.id}, ${branch.id}, true, true, 'gpt-4o')`);
    /* raw: INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_start, trial_end) VALUES ($1, $2, 'trial', NOW(), NOW() + INTERVAL '14 days') ON CONFLICT (restaurant_id) DO UPDATE SET ... */
    await tx.execute(sql`
      INSERT INTO subscriptions (restaurant_id, plan_id, status, trial_start, trial_end)
      VALUES (${restaurant.id}, ${planRow?.id ?? null}, 'trial', NOW(), NOW() + INTERVAL '14 days')
      ON CONFLICT (restaurant_id) DO UPDATE
        SET plan_id = EXCLUDED.plan_id, status = 'trial',
            trial_start = EXCLUDED.trial_start, trial_end = EXCLUDED.trial_end
    `);

    return { id: restaurant.id, displayId: restaurant.display_id, name: restaurant.name };
  });

  await createAuditLog({
    actorEmail: input.adminActorEmail,
    actorType: 'admin',
    action: 'restaurant.created',
    resource: 'restaurant',
    resourceId: result.id as unknown as string,
    severity: 'info',
    metadata: { restaurantName: result.name, ownerEmail: input.ownerEmail, plan: input.plan },
  });

  return {
    ...result,
    tempPassword: wasPasswordProvided ? undefined : plainPassword,
  };
}
