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

import { childLogger } from '@server/logger';
const log = childLogger('svc.admin');

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,
           u.id as owner_id,
           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;
    owner_id: string | null;
    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,
    ownerId: restaurant.owner_id ?? null,
    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 updateRestaurantProfile(
  restaurantId: string,
  data: { name?: string; phone?: string | null; cuisineType?: string | null; address?: string | null; seatingCapacity?: number | null }
) {
  await initDatabase();
  const parts: SQL[] = [];
  if (data.name !== undefined) parts.push(sql`name = ${data.name}`);
  if (data.phone !== undefined) parts.push(sql`phone = ${data.phone}`);
  if (data.cuisineType !== undefined) parts.push(sql`cuisine_type = ${data.cuisineType}`);
  if (data.address !== undefined) parts.push(sql`address = ${data.address}`);
  if (data.seatingCapacity !== undefined) parts.push(sql`seating_capacity = ${data.seatingCapacity}`);
  if (parts.length === 0) return;
  const setClause = sql.join(parts, sql`, `);
  const { rowCount } = await db.execute(sql`UPDATE restaurants SET ${setClause} WHERE id = ${restaurantId}`);
  if (!rowCount) throw new Error('Restaurant not found');
}

export async function setRestaurantStatus(
  restaurantId: string,
  status: 'active' | 'suspended'
) {
  await initDatabase();
  /* raw: UPDATE restaurants SET status = $1 WHERE id = $2 */
  const { rowCount } = await db.execute(sql`UPDATE restaurants SET status = ${status} WHERE id = ${restaurantId}`);
  if (!rowCount) throw new Error('Restaurant not found');
}

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,
           p.razorpay_plan_id_monthly, p.razorpay_plan_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 { getRazorpayCredentials } = await import('@server/lib/razorpay');
  const [stripeKey, razorpayCreds] = await Promise.all([getStripeSecretKey(), getRazorpayCredentials()]);
  const stripeConfigured = Boolean(stripeKey);
  const razorpayConfigured = Boolean(razorpayCreds);
  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;
    razorpay_plan_id_monthly: string | null; razorpay_plan_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 hasRazorpayPlan = Boolean(r.razorpay_plan_id_monthly || r.razorpay_plan_id_annual);
    const stripeStatus = !stripeConfigured ? 'not_configured'
      : hasStripeProduct ? 'synced'
      : 'not_synced';
    const razorpayStatus = !razorpayConfigured ? 'not_configured'
      : hasRazorpayPlan ? '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 ?? '0', 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,
      razorpayStatus,
    };
  });
}

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,
           razorpay_plan_id_monthly, razorpay_plan_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;
    razorpay_plan_id_monthly: string | null; razorpay_plan_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 ?? '0', 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,
    razorpayPlanIdMonthly: row.razorpay_plan_id_monthly ?? null,
    razorpayPlanIdAnnual: row.razorpay_plan_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 syncPlanWithRazorpay(planId: string, payload: PlanPayload & { razorpayPlanIdMonthly?: string | null; razorpayPlanIdAnnual?: string | null }) {
  const { getRazorpayClient } = await import('@server/lib/razorpay');
  const { getGatewayConfig } = await import('@server/services/gateway.service');
  const cfg = await getGatewayConfig();
  if (!cfg.razorpayActive || !cfg.razorpayKeyId || !cfg.razorpayKeySecret) {
    return { razorpayPlanIdMonthly: payload.razorpayPlanIdMonthly ?? null, razorpayPlanIdAnnual: payload.razorpayPlanIdAnnual ?? null, razorpaySkipped: true };
  }
  const razorpay = await getRazorpayClient();
  if (!razorpay) {
    return { razorpayPlanIdMonthly: payload.razorpayPlanIdMonthly ?? null, razorpayPlanIdAnnual: payload.razorpayPlanIdAnnual ?? null, razorpaySkipped: true };
  }

  const currency = cfg.razorpayCurrency.toUpperCase();
  let razorpayPlanIdMonthly = payload.razorpayPlanIdMonthly ?? null;
  let razorpayPlanIdAnnual = payload.razorpayPlanIdAnnual ?? null;

  const priceMonthly = payload.priceMonthly;
  const priceAnnual = payload.priceAnnual ?? 0;

  if (priceMonthly > 0) {
    let needNew = !razorpayPlanIdMonthly;
    if (!needNew && razorpayPlanIdMonthly) {
      try {
        const existing = await (razorpay.plans.fetch as (id: string) => Promise<{ item?: { amount?: number } }>)(razorpayPlanIdMonthly);
        if (existing?.item?.amount !== Math.round(priceMonthly * 100)) needNew = true;
      } catch { needNew = true; }
    }
    if (needNew) {
      const plan = await (razorpay.plans.create as unknown as (p: Record<string, unknown>) => Promise<{ id: string }>)({
        period: 'monthly',
        interval: 1,
        item: { name: payload.name, amount: Math.round(priceMonthly * 100), currency },
        notes: { restroagent_plan_id: planId, billing_cycle: 'monthly' },
      });
      razorpayPlanIdMonthly = plan.id;
    }
  }

  if (priceAnnual > 0) {
    let needNew = !razorpayPlanIdAnnual;
    if (!needNew && razorpayPlanIdAnnual) {
      try {
        const existing = await (razorpay.plans.fetch as (id: string) => Promise<{ item?: { amount?: number } }>)(razorpayPlanIdAnnual);
        if (existing?.item?.amount !== Math.round(priceAnnual * 100)) needNew = true;
      } catch { needNew = true; }
    }
    if (needNew) {
      const plan = await (razorpay.plans.create as unknown as (p: Record<string, unknown>) => Promise<{ id: string }>)({
        period: 'yearly',
        interval: 1,
        item: { name: `${payload.name} Annual`, amount: Math.round(priceAnnual * 100), currency },
        notes: { restroagent_plan_id: planId, billing_cycle: 'annual' },
      });
      razorpayPlanIdAnnual = plan.id;
    }
  }

  return { razorpayPlanIdMonthly, razorpayPlanIdAnnual, razorpaySkipped: false };
}

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 => {
    log.error({ err }, 'billing: Stripe sync failed on create');
    return { stripeProductId: null, stripePriceIdMonthly: null, stripePriceIdAnnual: null, stripeSkipped: true };
  });

  const razorpayResult = await syncPlanWithRazorpay(id, { ...payload, razorpayPlanIdMonthly: null, razorpayPlanIdAnnual: null }).catch(err => {
    log.error({ err }, 'billing: Razorpay sync failed on create');
    return { razorpayPlanIdMonthly: null, razorpayPlanIdAnnual: null, razorpaySkipped: true };
  });

  /* raw: UPDATE plans SET stripe_product_id = $1, stripe_price_id_monthly = $2, stripe_price_id_annual = $3, razorpay_plan_id_monthly = $4, razorpay_plan_id_annual = $5 WHERE id = $6 */
  if (!stripeResult.stripeSkipped || !razorpayResult.razorpaySkipped) {
    await db.execute(sql`
      UPDATE plans SET
        stripe_product_id = ${stripeResult.stripeProductId},
        stripe_price_id_monthly = ${stripeResult.stripePriceIdMonthly},
        stripe_price_id_annual = ${stripeResult.stripePriceIdAnnual},
        razorpay_plan_id_monthly = ${razorpayResult.razorpayPlanIdMonthly},
        razorpay_plan_id_annual = ${razorpayResult.razorpayPlanIdAnnual}
      WHERE id = ${id}
    `);
  }

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

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 => {
    log.error({ err }, 'billing: Stripe sync failed on update');
    return { stripeProductId: existing.stripeProductId, stripePriceIdMonthly: existing.stripePriceIdMonthly, stripePriceIdAnnual: existing.stripePriceIdAnnual, stripeSkipped: true };
  });

  const razorpayResult = await syncPlanWithRazorpay(planId, {
    ...payload,
    razorpayPlanIdMonthly: existing.razorpayPlanIdMonthly,
    razorpayPlanIdAnnual: existing.razorpayPlanIdAnnual,
  }).catch(err => {
    log.error({ err }, 'billing: Razorpay sync failed on update');
    return { razorpayPlanIdMonthly: existing.razorpayPlanIdMonthly, razorpayPlanIdAnnual: existing.razorpayPlanIdAnnual, razorpaySkipped: true };
  });

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

  return { stripeSkipped: stripeResult.stripeSkipped, razorpaySkipped: razorpayResult.razorpaySkipped };
}

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

  const syncPayload = {
    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,
  };

  const [stripeResult, razorpayResult] = await Promise.all([
    syncPlanWithStripe(planId, {
      ...syncPayload,
      stripeProductId: existing.stripeProductId,
      stripePriceIdMonthly: existing.stripePriceIdMonthly,
      stripePriceIdAnnual: existing.stripePriceIdAnnual,
    }),
    syncPlanWithRazorpay(planId, {
      ...syncPayload,
      razorpayPlanIdMonthly: existing.razorpayPlanIdMonthly,
      razorpayPlanIdAnnual: existing.razorpayPlanIdAnnual,
    }).catch(err => {
      log.error({ err }, 'billing: Razorpay sync failed on manual sync');
      return { razorpayPlanIdMonthly: existing.razorpayPlanIdMonthly, razorpayPlanIdAnnual: existing.razorpayPlanIdAnnual, razorpaySkipped: true };
    }),
  ]);

  if (!stripeResult.stripeSkipped || !razorpayResult.razorpaySkipped) {
    /* raw: UPDATE plans SET stripe_product_id = $1, stripe_price_id_monthly = $2, stripe_price_id_annual = $3, razorpay_plan_id_monthly = $4, razorpay_plan_id_annual = $5 WHERE id = $6 */
    await db.execute(sql`
      UPDATE plans SET
        stripe_product_id = ${stripeResult.stripeProductId},
        stripe_price_id_monthly = ${stripeResult.stripePriceIdMonthly},
        stripe_price_id_annual = ${stripeResult.stripePriceIdAnnual},
        razorpay_plan_id_monthly = ${razorpayResult.razorpayPlanIdMonthly},
        razorpay_plan_id_annual = ${razorpayResult.razorpayPlanIdAnnual}
      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, razorpaySkipped: razorpayResult.razorpaySkipped };
}

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) {
      log.error({ err }, 'billing: Stripe archive failed on delete');
    }
  }
}

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, s.stripe_customer_id, 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.id as restaurant_id, r.name as restaurant_name,
           s.stripe_customer_id,
           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
  `);

  type Row = {
    id: string; restaurant_id: string; restaurant_name: string;
    stripe_customer_id: string | null; plan_name: string;
    price_monthly: string; status: string; period_end: string;
  };
  const subs = rows as Row[];

  // Try to enrich with real Stripe invoice URLs (best-effort).
  const stripeUrlsByCustomer = new Map<string, { pdfUrl: string | null; hostedUrl: string | null }>();
  try {
    const { getStripeSecretKey, getStripeClientFromDb } = await import('@server/lib/stripe');
    const stripeKey = await getStripeSecretKey();
    if (stripeKey) {
      const stripe = await getStripeClientFromDb();
      const list = await stripe.invoices.list({ limit: 100 });
      for (const inv of list.data) {
        const cust = typeof inv.customer === 'string' ? inv.customer : inv.customer?.id;
        if (!cust || stripeUrlsByCustomer.has(cust)) continue;
        stripeUrlsByCustomer.set(cust, {
          pdfUrl: inv.invoice_pdf ?? null,
          hostedUrl: inv.hosted_invoice_url ?? null,
        });
      }
    }
  } catch { /* gateway not configured or stripe error – return without urls */ }

  // Razorpay enrichment (best-effort)
  let razorpayByRestaurant = new Map<string, { gateway: 'razorpay' }>();
  try {
    const { rows: rpRows } = await db.execute(sql`
      SELECT DISTINCT restaurant_id FROM razorpay_payments WHERE status = 'captured'
    `);
    razorpayByRestaurant = new Map(
      (rpRows as { restaurant_id: string }[]).map(r => [r.restaurant_id, { gateway: 'razorpay' as const }])
    );
  } catch { /* table may not exist */ }

  return subs.map(r => {
    const stripeUrls = r.stripe_customer_id ? stripeUrlsByCustomer.get(r.stripe_customer_id) : undefined;
    const isRazorpay = !r.stripe_customer_id && razorpayByRestaurant.has(r.restaurant_id);
    return {
      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,
      gateway: isRazorpay ? 'razorpay' : 'stripe',
      pdfUrl: stripeUrls?.pdfUrl ?? null,
      hostedUrl: stripeUrls?.hostedUrl ?? null,
    };
  });
}

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 {
  // Cryptographically random display id over a 32-char alphabet (no
  // ambiguous 0/O/1/I). 7 chars => 32^7 ≈ 34B combinations. Math.random
  // is biased and not collision-friendly under load, so use crypto.randomInt.
  const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
  let id = 'RST-';
  for (let i = 0; i < 7; i++) {
    id += chars[crypto.randomInt(chars.length)];
  }
  return id;
}

// Wrap a restaurant-creating operation in a small retry loop on the
// Postgres unique-violation (SQLSTATE 23505) that the
// `idx_restaurants_display_id` constraint can raise. The display_id space
// is huge (32^7 ≈ 34B), so a real collision is rare — but when it does
// happen we transparently retry instead of bubbling a raw "duplicate key"
// error up to the caller (admin create or OTP signup).
export async function retryOnDisplayIdCollision<T>(
  fn: () => Promise<T>,
  maxAttempts = 5,
): Promise<T> {
  let lastErr: unknown = null;
  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (err: unknown) {
      lastErr = err;
      const code = (err as { code?: string })?.code;
      const constraint = (err as { constraint?: string })?.constraint;
      const isDisplayIdDup = code === '23505' && (
        !constraint || constraint === 'idx_restaurants_display_id'
      );
      if (!isDisplayIdDup) throw err;
      // else: fall through and retry
    }
  }
  throw new Error(
    `Failed after ${maxAttempts} attempts (display_id collisions): ${
      (lastErr as Error)?.message ?? 'unknown error'
    }`,
  );
}

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;
  }

  // The display_id column has a UNIQUE index. The DB trigger
  // restaurants_auto_display_id has its own retry loop, but we still wrap
  // the whole creation in a small retry on Postgres unique-violation
  // (SQLSTATE 23505) so a synthetic burst (or a future caller that passes
  // an explicit display_id) never bubbles a raw "duplicate key" up to the
  // user. 5 attempts × 32^7 keyspace ≈ astronomically safe.
  const result = await retryOnDisplayIdCollision(() => 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');

    // Auto-generate a unique storefront slug for the new restaurant
    await tx.execute(sql`
      UPDATE restaurants r SET slug = (
        SELECT CASE
          WHEN NOT EXISTS (SELECT 1 FROM restaurants WHERE slug = base AND id <> r.id) THEN base
          ELSE base || '-' || substr(r.id::text, 1, 6)
        END
        FROM (SELECT public._storefront_slugify(${input.restaurantName.trim()}) AS base) s
      )
      WHERE r.id = ${restaurant.id} AND (r.slug IS NULL OR r.slug = '')
    `);

    /* 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');

    // Auto-generate a unique branch slug (unique within restaurant)
    await tx.execute(sql`
      UPDATE branches b SET slug = (
        SELECT CASE
          WHEN NOT EXISTS (SELECT 1 FROM branches WHERE slug = base AND restaurant_id = b.restaurant_id AND id <> b.id) THEN base
          ELSE base || '-' || substr(b.id::text, 1, 6)
        END
        FROM (SELECT public._storefront_slugify(${input.restaurantName.trim() + ' Main Branch'}) AS base) s
      )
      WHERE b.id = ${branch.id} AND (b.slug IS NULL OR b.slug = '')
    `);

    /* 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,
  };
}