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 WHERE restaurant_id = $1 */
    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,
  };
}
