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

export type DashboardPeriod = 'today' | '7days' | 'month' | 'lifetime';

function periodCond(field: string, period: DashboardPeriod): string {
  switch (period) {
    case 'today':    return `DATE(${field}) = CURRENT_DATE`;
    case '7days':    return `${field} >= CURRENT_DATE - INTERVAL '6 days'`;
    case 'month':    return `DATE_TRUNC('month', ${field}) = DATE_TRUNC('month', CURRENT_DATE)`;
    case 'lifetime': return 'TRUE';
  }
}

export async function getDashboardStats(restaurantId: string, branchId?: string, period: DashboardPeriod = 'today') {
  await initDatabase();
  const ocRaw = sql.raw(periodCond('created_at', period));
  const bcRaw = sql.raw(periodCond('booking_date', period));
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  const [orders, bookings, customers, conversations, revenue] = await Promise.all([
    /* raw: SELECT COUNT(*) FILTER (...) as pending_orders, ... FROM orders WHERE restaurant_id = $1 [AND branch_id = $2] */
    db.execute(sql`
      SELECT
        COUNT(*) FILTER (WHERE status = 'pending') as pending_orders,
        COUNT(*) FILTER (WHERE status IN ('confirmed','kitchen','ready')) as active_orders,
        COUNT(*) FILTER (WHERE ${ocRaw}) as period_orders,
        COUNT(*) FILTER (WHERE channel = 'voice' AND ${ocRaw}) as period_voice_orders,
        COUNT(*) FILTER (WHERE channel = 'chat' AND ${ocRaw}) as period_chat_orders,
        COUNT(*) FILTER (WHERE status = 'completed' AND ${ocRaw}) as period_completed_orders
      FROM orders WHERE restaurant_id = ${restaurantId}${branchCond}
    `),
    /* raw: SELECT COUNT(*) FILTER (...) as pending_bookings, ... FROM bookings WHERE restaurant_id = $1 [AND branch_id = $2] */
    db.execute(sql`
      SELECT
        COUNT(*) FILTER (WHERE status = 'pending') as pending_bookings,
        COUNT(*) FILTER (WHERE ${bcRaw}) as period_bookings,
        COUNT(*) FILTER (WHERE status = 'confirmed' AND ${bcRaw}) as period_confirmed_bookings,
        COUNT(*) FILTER (WHERE booking_date = CURRENT_DATE + 1) as tomorrow_bookings
      FROM bookings WHERE restaurant_id = ${restaurantId}${branchCond}
    `),
    /* raw: SELECT COUNT(*) as total_customers FROM customers c WHERE c.restaurant_id = $1
       [AND (EXISTS (orders for branch) OR EXISTS (bookings for branch))]
       The customers table is restaurant-scoped (no branch_id column), so when
       a specific branch is selected we count customers who have at least one
       order or booking at that branch. This mirrors the filter applied by
       listCustomers in customers.service.ts so the dashboard tile and the
       Customers page always agree. */
    branchId
      ? db.execute(sql`
          SELECT COUNT(*) as total_customers FROM customers c
          WHERE c.restaurant_id = ${restaurantId}
            AND (
              EXISTS (
                SELECT 1 FROM orders o
                 WHERE o.restaurant_id = ${restaurantId}
                   AND o.branch_id = ${branchId}
                   AND o.customer_id = c.id
              )
              OR EXISTS (
                SELECT 1 FROM bookings b
                 WHERE b.restaurant_id = ${restaurantId}
                   AND b.branch_id = ${branchId}
                   AND b.customer_id = c.id
              )
            )
        `)
      : db.execute(sql`SELECT COUNT(*) as total_customers FROM customers WHERE restaurant_id = ${restaurantId}`),
    /* raw: SELECT COUNT(*) FILTER (...) as human_conversations, ... FROM conversations WHERE restaurant_id = $1 [AND branch_id = $2] */
    db.execute(sql`
      SELECT
        COUNT(*) FILTER (WHERE status = 'human') as human_conversations,
        COUNT(*) FILTER (WHERE ${ocRaw}) as period_conversations,
        COUNT(*) FILTER (WHERE status = 'ai' AND ${ocRaw}) as period_ai_conversations,
        COUNT(*) FILTER (WHERE status = 'human' AND ${ocRaw}) as period_human_conversations,
        COUNT(*) FILTER (WHERE status = 'resolved' AND ${ocRaw}) as period_resolved_conversations,
        COALESCE(AVG(ai_confidence) FILTER (WHERE ${ocRaw}), 0) as period_avg_confidence,
        COUNT(*) FILTER (WHERE status = 'ai' AND channel = 'voice' AND ${ocRaw}) as period_ai_voice_conversations,
        COUNT(*) FILTER (WHERE status = 'ai' AND channel = 'chat' AND ${ocRaw}) as period_ai_chat_conversations,
        COUNT(*) FILTER (WHERE status = 'human' AND channel = 'voice' AND ${ocRaw}) as period_voice_escalations,
        COUNT(*) FILTER (WHERE status = 'human' AND channel = 'chat' AND ${ocRaw}) as period_chat_escalations,
        COUNT(*) FILTER (WHERE status = 'human' AND channel = 'voice' AND created_at >= NOW() - INTERVAL '24 hours') as recent_voice_escalations,
        COUNT(*) FILTER (WHERE status = 'human' AND channel = 'chat' AND created_at >= NOW() - INTERVAL '24 hours') as recent_chat_escalations
      FROM conversations WHERE restaurant_id = ${restaurantId}${branchCond}
    `),
    /* raw: SELECT COALESCE(SUM(total) FILTER (...), 0) as period_revenue FROM orders WHERE restaurant_id = $1 [AND branch_id = $2] */
    db.execute(sql`
      SELECT
        COALESCE(SUM(total) FILTER (WHERE status = 'completed' AND ${ocRaw}), 0) as period_revenue
      FROM orders WHERE restaurant_id = ${restaurantId}${branchCond}
    `),
  ]);

  return {
    orders: orders.rows[0] ?? {},
    bookings: bookings.rows[0] ?? {},
    customers: customers.rows[0] ?? {},
    conversations: conversations.rows[0] ?? {},
    revenue: revenue.rows[0] ?? {},
  };
}

export async function getHourlyActivity(restaurantId: string, branchId?: string) {
  await initDatabase();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  /* raw: SELECT EXTRACT(HOUR FROM created_at)::int as hour, COUNT(*) as count FROM orders WHERE restaurant_id = $1 [AND branch_id = $2] AND DATE(created_at) = CURRENT_DATE GROUP BY ... */
  const orderResult = await db.execute(sql`
    SELECT EXTRACT(HOUR FROM created_at)::int as hour, COUNT(*) as count
    FROM orders
    WHERE restaurant_id = ${restaurantId}${branchCond} AND DATE(created_at) = CURRENT_DATE
    GROUP BY EXTRACT(HOUR FROM created_at)
    ORDER BY hour
  `);

  /* raw: SELECT EXTRACT(HOUR FROM booking_time::time)::int as hour, COUNT(*) as count FROM bookings WHERE restaurant_id = $1 [AND branch_id = $2] AND booking_date = CURRENT_DATE GROUP BY ... */
  const bookingResult = await db.execute(sql`
    SELECT EXTRACT(HOUR FROM booking_time::time)::int as hour, COUNT(*) as count
    From bookings
    WHERE restaurant_id = ${restaurantId}${branchCond} AND booking_date = CURRENT_DATE
    GROUP BY EXTRACT(HOUR FROM booking_time::time)
    ORDER BY hour
  `);

  const orderMap: Record<number, number> = {};
  for (const r of orderResult.rows as { hour: string; count: string }[]) orderMap[parseInt(r.hour, 10)] = parseInt(r.count, 10);
  const bookingMap: Record<number, number> = {};
  for (const r of bookingResult.rows as { hour: string; count: string }[]) bookingMap[parseInt(r.hour, 10)] = parseInt(r.count, 10);

  const hours = [];
  for (let h = 6; h <= 23; h++) {
    const suffix = h >= 12 ? 'PM' : 'AM';
    const display = h > 12 ? h - 12 : h === 0 ? 12 : h;
    hours.push({
      hour: `${display} ${suffix}`,
      orders: orderMap[h] ?? 0,
      reservations: bookingMap[h] ?? 0,
    });
  }
  return hours;
}

export async function getChannelBreakdown(restaurantId: string, days = 7, branchId?: string) {
  await initDatabase();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  /* raw: SELECT EXTRACT(DOW FROM created_at)::int as dow, TO_CHAR(...) as day_name, COUNT(*) FILTER (...) as voice, ... FROM orders WHERE restaurant_id = $1 [AND branch_id = $3] AND created_at >= ... GROUP BY ... ORDER BY dow */
  const { rows } = await db.execute(sql`
    SELECT
      EXTRACT(DOW FROM created_at)::int as dow,
      TO_CHAR(created_at, 'Dy') as day_name,
      COUNT(*) FILTER (WHERE channel = 'voice') as voice,
      COUNT(*) FILTER (WHERE channel = 'chat') as chat
    FROM orders
    WHERE restaurant_id = ${restaurantId}${branchCond}
      AND created_at >= CURRENT_DATE - INTERVAL '1 day' * ${days}
    GROUP BY EXTRACT(DOW FROM created_at), TO_CHAR(created_at, 'Dy')
    ORDER BY dow
  `);

  return (rows as { day_name: string; voice: string; chat: string }[]).map(r => ({
    day: r.day_name,
    voice: parseInt(r.voice, 10),
    chat: parseInt(r.chat, 10),
  }));
}

export async function getOrderTrends(restaurantId: string, days = 30, branchId?: string) {
  await initDatabase();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  /* raw: SELECT DATE(created_at) as date, COUNT(*) as total_orders, ... FROM orders WHERE restaurant_id = $1 [AND branch_id = $3] AND created_at >= ... GROUP BY DATE(created_at) ORDER BY date */
  const { rows } = await db.execute(sql`
    SELECT
      DATE(created_at) as date,
      COUNT(*) as total_orders,
      COUNT(*) FILTER (WHERE status = 'completed') as completed,
      COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
      COALESCE(SUM(total) FILTER (WHERE status = 'completed'), 0) as revenue
    FROM orders
    WHERE restaurant_id = ${restaurantId}${branchCond}
      AND created_at >= CURRENT_DATE - INTERVAL '1 day' * ${days}
    GROUP BY DATE(created_at)
    ORDER BY date ASC
  `);
  return rows;
}

export async function getRevenueSummary(restaurantId: string, branchId?: string) {
  await initDatabase();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  const [monthly, channels, topItems] = await Promise.all([
    /* raw: SELECT TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM') as month, COALESCE(SUM(total), 0) as revenue, COUNT(*) as orders FROM orders WHERE restaurant_id = $1 [AND branch_id = $2] AND status = 'completed' ... GROUP BY ... ORDER BY month */
    db.execute(sql`
      SELECT
        TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM') as month,
        COALESCE(SUM(total), 0) as revenue,
        COUNT(*) as orders
      FROM orders
      WHERE restaurant_id = ${restaurantId}${branchCond} AND status = 'completed'
        AND created_at >= CURRENT_DATE - INTERVAL '6 months'
      GROUP BY DATE_TRUNC('month', created_at)
      ORDER BY month ASC
    `),
    /* raw: SELECT channel, COUNT(*) as orders, COALESCE(SUM(total), 0) as revenue FROM orders WHERE restaurant_id = $1 [AND branch_id = $2] AND status = 'completed' GROUP BY channel ORDER BY revenue DESC */
    db.execute(sql`
      SELECT channel, COUNT(*) as orders, COALESCE(SUM(total), 0) as revenue
      FROM orders
      WHERE restaurant_id = ${restaurantId}${branchCond} AND status = 'completed'
      GROUP BY channel ORDER BY revenue DESC
    `),
    /* raw: SELECT item->>'name' as name, COUNT(*) as order_count, ... FROM orders, jsonb_array_elements(items) as item WHERE restaurant_id = $1 [AND branch_id = $2] AND status = 'completed' GROUP BY item->>'name' ORDER BY order_count DESC LIMIT 10 */
    db.execute(sql`
      SELECT
        item->>'name' as name,
        COUNT(*) as order_count,
        COALESCE(SUM((item->>'price')::numeric * (item->>'quantity')::numeric), 0) as revenue
      FROM orders, jsonb_array_elements(items) as item
      WHERE restaurant_id = ${restaurantId}${branchCond} AND status = 'completed'
      GROUP BY item->>'name'
      ORDER BY order_count DESC
      LIMIT 10
    `),
  ]);

  return { monthly: monthly.rows, channels: channels.rows, topItems: topItems.rows };
}

export type AnalyticsPeriod = 'today' | 'week' | 'month' | '3months';

function pctChange(curr: number, prior: number): { change: string; positive: boolean } {
  if (prior === 0) return { change: curr > 0 ? '+100%' : '–', positive: curr >= prior };
  const diff = ((curr - prior) / prior) * 100;
  return { change: (diff >= 0 ? '+' : '') + diff.toFixed(1) + '%', positive: diff >= 0 };
}

export async function getFullAnalytics(
  restaurantId: string,
  period: AnalyticsPeriod,
  branchId?: string
) {
  await initDatabase();

  const now = new Date();
  let currentStart: Date, priorStart: Date, priorEnd: Date;

  if (period === 'today') {
    currentStart = new Date(now.getFullYear(), now.getMonth(), now.getDate());
    priorStart = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1);
    priorEnd = new Date(currentStart);
  } else if (period === 'week') {
    currentStart = new Date(now);
    currentStart.setDate(now.getDate() - 6);
    currentStart.setHours(0, 0, 0, 0);
    priorEnd = new Date(currentStart);
    priorStart = new Date(priorEnd);
    priorStart.setDate(priorStart.getDate() - 7);
  } else if (period === 'month') {
    currentStart = new Date(now.getFullYear(), now.getMonth(), 1);
    priorStart = new Date(now.getFullYear(), now.getMonth() - 1, 1);
    priorEnd = new Date(currentStart);
  } else {
    currentStart = new Date(now.getFullYear(), now.getMonth() - 2, 1);
    priorStart = new Date(now.getFullYear(), now.getMonth() - 5, 1);
    priorEnd = new Date(currentStart);
  }

  const cs = currentStart.toISOString();
  const ps = priorStart.toISOString();
  const pe = priorEnd.toISOString();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  const bookingTrendStart =
    period === 'month'
      ? new Date(now.getFullYear(), now.getMonth() - 5, 1).toISOString()
      : cs;

  const orderTrendBucket = period === 'today'
    ? sql.raw(`EXTRACT(HOUR FROM created_at)::int`)
    : period === '3months'
    ? sql.raw(`TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM')`)
    : sql.raw(`DATE(created_at)::text`);

  const bookingTrendBucket = (period === 'month' || period === '3months')
    ? sql.raw(`TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM')`)
    : sql.raw(`DATE(created_at)::text`);

  const orderTrendGroupBy = period === 'today'
    ? sql.raw(`EXTRACT(HOUR FROM created_at)`)
    : period === '3months'
    ? sql.raw(`DATE_TRUNC('month', created_at)`)
    : sql.raw(`DATE(created_at)`);

  const bookingTrendGroupBy = (period === 'month' || period === '3months')
    ? sql.raw(`DATE_TRUNC('month', created_at)`)
    : sql.raw(`DATE(created_at)`);

  const [
    curOrderKpi, priorOrderKpi,
    curBookingKpi, priorBookingKpi,
    curConvKpi, priorConvKpi,
    orderTrendResult, bookingTrendResult,
    aiByHourResult, channelSplitResult,
    revenueByChannelResult, topItemResult,
  ] = await Promise.all([
    /* raw: SELECT COUNT(*) as total_orders, COALESCE(SUM(total) FILTER (WHERE status='completed'), 0) as revenue FROM orders WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N::timestamptz */
    db.execute(sql`SELECT COUNT(*) as total_orders, COALESCE(SUM(total) FILTER (WHERE status='completed'), 0) as revenue
      FROM orders WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz`),
    /* raw: SELECT COUNT(*) as total_orders, ... FROM orders WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N AND created_at < $N */
    db.execute(sql`SELECT COUNT(*) as total_orders, COALESCE(SUM(total) FILTER (WHERE status='completed'), 0) as revenue
      FROM orders WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${ps}::timestamptz AND created_at < ${pe}::timestamptz`),
    /* raw: SELECT COUNT(*) as total_bookings FROM bookings WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N */
    db.execute(sql`SELECT COUNT(*) as total_bookings FROM bookings WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz`),
    /* raw: SELECT COUNT(*) as total_bookings FROM bookings WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N AND created_at < $N */
    db.execute(sql`SELECT COUNT(*) as total_bookings FROM bookings WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${ps}::timestamptz AND created_at < ${pe}::timestamptz`),
    /* raw: SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved FROM conversations WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N */
    db.execute(sql`SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved
      FROM conversations WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz`),
    /* raw: SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved FROM conversations WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N AND created_at < $N */
    db.execute(sql`SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved
      FROM conversations WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${ps}::timestamptz AND created_at < ${pe}::timestamptz`),
    /* raw: SELECT <dynamic_bucket> as bucket, COUNT(*) as total_orders, COALESCE(SUM(total) FILTER (...), 0) as revenue FROM orders WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N GROUP BY <dynamic> ORDER BY bucket */
    db.execute(sql`SELECT ${orderTrendBucket} as bucket,
      COUNT(*) as total_orders,
      COALESCE(SUM(total) FILTER (WHERE status='completed'), 0) as revenue
      FROM orders WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz
      GROUP BY ${orderTrendGroupBy} ORDER BY bucket`),
    /* raw: SELECT <dynamic_bucket> as bucket, COUNT(*) as total_bookings, COUNT(*) FILTER (WHERE status='cancelled') as cancellations FROM bookings WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N GROUP BY <dynamic> ORDER BY bucket */
    db.execute(sql`SELECT ${bookingTrendBucket} as bucket,
      COUNT(*) as total_bookings,
      COUNT(*) FILTER (WHERE status='cancelled') as cancellations
      FROM bookings WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${bookingTrendStart}::timestamptz
      GROUP BY ${bookingTrendGroupBy} ORDER BY bucket`),
    /* raw: SELECT EXTRACT(HOUR FROM created_at)::int as hour, COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved FROM conversations WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N GROUP BY ... ORDER BY hour */
    db.execute(sql`SELECT EXTRACT(HOUR FROM created_at)::int as hour,
      COUNT(*) as total, COUNT(*) FILTER (WHERE status='resolved') as resolved
      FROM conversations WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz
      GROUP BY EXTRACT(HOUR FROM created_at) ORDER BY hour`),
    /* raw: SELECT COALESCE(channel::text, 'unknown') as channel, COUNT(*) as count FROM conversations WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N GROUP BY channel ORDER BY count DESC */
    db.execute(sql`SELECT COALESCE(channel::text, 'unknown') as channel, COUNT(*) as count
      FROM conversations WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz
      GROUP BY channel ORDER BY count DESC`),
    /* raw: SELECT COALESCE(channel::text, 'unknown') as channel, COUNT(*) as orders, COALESCE(SUM(total) FILTER (...), 0) as revenue FROM orders WHERE restaurant_id=$1 [AND branch_id=$2] AND created_at >= $N GROUP BY channel ORDER BY revenue DESC */
    db.execute(sql`SELECT COALESCE(channel::text, 'unknown') as channel, COUNT(*) as orders,
      COALESCE(SUM(total) FILTER (WHERE status='completed'), 0) as revenue
      FROM orders WHERE restaurant_id=${restaurantId}${branchCond} AND created_at >= ${cs}::timestamptz
      GROUP BY channel ORDER BY revenue DESC`),
    /* raw: SELECT item->>'name' as name, COUNT(*) as order_count, ... FROM orders, jsonb_array_elements(...) as item WHERE restaurant_id=$1 [AND branch_id=$2] AND status='completed' AND created_at >= $N ... GROUP BY item->>'name' ORDER BY order_count DESC LIMIT 10 */
    db.execute(sql`SELECT item->>'name' as name, COUNT(*) as order_count,
      COALESCE(SUM((item->>'price')::numeric * (item->>'quantity')::numeric), 0) as revenue
      FROM orders, jsonb_array_elements(COALESCE(items, '[]'::jsonb)) as item
      WHERE restaurant_id=${restaurantId}${branchCond} AND status='completed' AND created_at >= ${cs}::timestamptz
        AND items IS NOT NULL AND jsonb_array_length(COALESCE(items, '[]'::jsonb)) > 0
        AND item->>'name' IS NOT NULL
      GROUP BY item->>'name' ORDER BY order_count DESC LIMIT 10`),
  ]);

  const curOrderKpiRow = curOrderKpi.rows[0] as Record<string, string> | undefined;
  const priorOrderKpiRow = priorOrderKpi.rows[0] as Record<string, string> | undefined;
  const curBookingKpiRow = curBookingKpi.rows[0] as Record<string, string> | undefined;
  const priorBookingKpiRow = priorBookingKpi.rows[0] as Record<string, string> | undefined;
  const curConvKpiRow = curConvKpi.rows[0] as Record<string, string> | undefined;
  const priorConvKpiRow = priorConvKpi.rows[0] as Record<string, string> | undefined;

  const curRevNum = parseFloat(curOrderKpiRow?.revenue ?? '0');
  const priorRevNum = parseFloat(priorOrderKpiRow?.revenue ?? '0');
  const curOrdersNum = parseInt(curOrderKpiRow?.total_orders ?? '0', 10);
  const priorOrdersNum = parseInt(priorOrderKpiRow?.total_orders ?? '0', 10);
  const curBookingsNum = parseInt(curBookingKpiRow?.total_bookings ?? '0', 10);
  const priorBookingsNum = parseInt(priorBookingKpiRow?.total_bookings ?? '0', 10);
  const curConvTotal = parseInt(curConvKpiRow?.total ?? '0', 10);
  const curConvResolved = parseInt(curConvKpiRow?.resolved ?? '0', 10);
  const priorConvTotal = parseInt(priorConvKpiRow?.total ?? '0', 10);
  const priorConvResolved = parseInt(priorConvKpiRow?.resolved ?? '0', 10);
  const curAiRate = curConvTotal > 0 ? (curConvResolved / curConvTotal) * 100 : 0;
  const priorAiRate = priorConvTotal > 0 ? (priorConvResolved / priorConvTotal) * 100 : 0;

  const aiByHourRows = aiByHourResult.rows as Record<string, string>[];
  const channelSplitRows = channelSplitResult.rows as Record<string, string>[];
  const orderTrendRows = orderTrendResult.rows as Record<string, string>[];
  const bookingTrendRows = bookingTrendResult.rows as Record<string, string>[];
  const revenueByChannelRows = revenueByChannelResult.rows as Record<string, string>[];
  const topItemRows = topItemResult.rows as Record<string, unknown>[];

  const hourBucketMap: Record<number, { total: number; resolved: number }> = {};
  for (let h = 0; h <= 22; h += 2) hourBucketMap[h] = { total: 0, resolved: 0 };
  (aiByHourRows ?? []).forEach(r => {
    const h = parseInt(r.hour, 10);
    const bucket = Math.floor(h / 2) * 2;
    if (hourBucketMap[bucket] !== undefined) {
      hourBucketMap[bucket].total += parseInt(r.total, 10);
      hourBucketMap[bucket].resolved += parseInt(r.resolved, 10);
    }
  });
  const HOUR_LABELS: Record<number, string> = {
    0: '12am', 2: '2am', 4: '4am', 6: '6am',
    8: '8am', 10: '10am', 12: '12pm', 14: '2pm',
    16: '4pm', 18: '6pm', 20: '8pm', 22: '10pm',
  };
  const aiByHour = Object.entries(hourBucketMap).map(([h, v]) => ({
    hour: HOUR_LABELS[Number(h)] ?? `${h}:00`,
    resolution: v.total > 0 ? Math.round((v.resolved / v.total) * 100) : 0,
    escalation: v.total > 0 ? Math.round(((v.total - v.resolved) / v.total) * 100) : 0,
    total: v.total,
  }));

  const channelTotal = (channelSplitRows ?? []).reduce((s, r) => s + parseInt(r.count, 10), 0);
  const channelSplit = (channelSplitRows ?? []).map(r => ({
    name: (r.channel as string).charAt(0).toUpperCase() + (r.channel as string).slice(1),
    value: channelTotal > 0 ? Math.round((parseInt(r.count, 10) / channelTotal) * 100) : 0,
    count: parseInt(r.count, 10),
  }));

  return {
    kpis: {
      revenue: { value: curRevNum, ...pctChange(curRevNum, priorRevNum) },
      orders: { value: curOrdersNum, ...pctChange(curOrdersNum, priorOrdersNum) },
      bookings: { value: curBookingsNum, ...pctChange(curBookingsNum, priorBookingsNum) },
      aiResolution: { value: Math.round(curAiRate * 10) / 10, ...pctChange(curAiRate, priorAiRate) },
    },
    orderTrend: (orderTrendRows ?? []).map(r => ({
      bucket: String(r.bucket),
      orders: parseInt(r.total_orders, 10),
      revenue: parseFloat(r.revenue),
    })),
    bookingTrend: (bookingTrendRows ?? []).map(r => ({
      bucket: String(r.bucket),
      bookings: parseInt(r.total_bookings, 10),
      cancellations: parseInt(r.cancellations, 10),
    })),
    aiByHour,
    channelSplit,
    revenueByChannel: (revenueByChannelRows ?? []).map(r => ({
      channel: r.channel as string,
      orders: parseInt(r.orders, 10),
      revenue: parseFloat(r.revenue),
    })),
    topItems: (topItemRows ?? []).map(r => ({
      name: String(r.name ?? ''),
      orderCount: parseInt(String(r.order_count), 10),
      revenue: parseFloat(String(r.revenue)),
    })),
    period,
    currentStart: cs,
  };
}