import { NextResponse } from 'next/server';
import { withErrorHandler } from '@server/middleware/withErrorHandler';
import { withAuth, AuthedRequest } from '@server/middleware/withAuth';
import { ForbiddenError } from '@server/errors';
import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';

const SORT_COLS: Record<string, string> = {
  booking_date: 'b.booking_date',
  date: 'b.booking_date',
  created_at: 'b.created_at',
  status: 'b.status',
  guest_name: 'b.guest_name',
  restaurant: 'r.name',
};

export const GET = withErrorHandler(
  withAuth(async (req: AuthedRequest) => {
    if (req.session.role !== 'superadmin' && req.session.role !== 'support') {
      throw new ForbiddenError();
    }
    await initDatabase();

    const url = new URL(req.url);
    const p = url.searchParams;

    const restaurantId = p.get('restaurant_id') ?? undefined;
    const branchId = p.get('branch_id') ?? undefined;
    const statusRaw = p.get('status') ?? undefined;
    const statuses = statusRaw ? statusRaw.split(',').map(s => s.trim()).filter(Boolean) : [];
    const channel = p.get('channel') ?? undefined;
    const dateFrom = p.get('date_from') ?? undefined;
    const dateTo = p.get('date_to') ?? undefined;
    const search = p.get('search') ?? undefined;
    const page = Math.max(1, parseInt(p.get('page') ?? '1', 10));
    const limit = Math.min(100, Math.max(1, parseInt(p.get('limit') ?? '25', 10)));
    const offset = (page - 1) * limit;
    const sortField = p.get('sort_field') ?? 'booking_date';
    const sortDir = p.get('sort_dir') === 'asc' ? 'ASC' : 'DESC';

    const conditions: SQL[] = [];

    if (restaurantId) conditions.push(sql`b.restaurant_id = ${restaurantId}`);
    if (branchId) conditions.push(sql`b.branch_id = ${branchId}`);
    if (statuses.length === 1) {
      conditions.push(sql`b.status = ${statuses[0]}`);
    } else if (statuses.length > 1) {
      conditions.push(sql`b.status::text IN (${sql.join(statuses.map(s => sql`${s}`), sql`, `)})`);
    }
    if (channel) conditions.push(sql`b.channel::text = ${channel}`);
    if (dateFrom) conditions.push(sql`b.booking_date >= ${dateFrom}`);
    if (dateTo) conditions.push(sql`b.booking_date <= ${dateTo}`);
    if (search) {
      conditions.push(sql`(b.guest_name ILIKE ${`%${search}%`} OR b.guest_phone ILIKE ${`%${search}%`} OR b.guest_email ILIKE ${`%${search}%`})`);
    }

    const where = conditions.length > 0 ? sql`WHERE ${sql.join(conditions, sql` AND `)}` : sql``;
    const today = new Date().toISOString().slice(0, 10);

    /* raw: SELECT COUNT(*), stats with FILTER, and paginated rows with JOINs */
    const [countResult, statsResult] = await Promise.all([
      db.execute(sql`SELECT COUNT(*) FROM bookings b ${where}`),
      db.execute(sql`
        SELECT
          COUNT(*) as total,
          COUNT(*) FILTER (WHERE b.booking_date = ${today}) as today,
          COUNT(*) FILTER (WHERE b.status = 'confirmed') as confirmed,
          COUNT(*) FILTER (WHERE b.status = 'seated') as seated,
          COUNT(*) FILTER (WHERE b.status = 'cancelled') as cancelled,
          COUNT(*) FILTER (WHERE b.status = 'noshow') as noshow,
          COUNT(*) FILTER (WHERE b.status = 'pending') as pending
        FROM bookings b ${where}
      `),
    ]);

    const total = parseInt((countResult.rows[0] as Record<string, unknown>)?.count as string ?? '0', 10);
    const statsRow = statsResult.rows[0] as Record<string, string>;
    const orderCol = SORT_COLS[sortField] ?? 'b.booking_date';

    const { rows } = await db.execute(sql`
      SELECT
        b.id, b.guest_name, b.guest_phone, b.guest_email, b.is_vip,
        b.booking_date, b.booking_time, b.party_size, b.table_no, b.zone,
        b.status, b.channel, b.occasion, b.dietary_needs, b.special_requests,
        b.ai_notes, b.ai_confidence, b.reminder_sent, b.notes,
        b.agent_id, b.conversation_id, b.created_at, b.updated_at,
        b.restaurant_id, b.branch_id, b.customer_id,
        r.name AS restaurant_name,
        br.name AS branch_name
      FROM bookings b
      LEFT JOIN restaurants r ON b.restaurant_id = r.id
      LEFT JOIN branches br ON b.branch_id = br.id
      ${where}
      ORDER BY ${sql.raw(orderCol)} ${sql.raw(sortDir)}, b.booking_time ${sql.raw(sortDir)}
      LIMIT ${limit} OFFSET ${offset}
    `);

    return NextResponse.json({
      bookings: rows,
      total,
      page,
      limit,
      pages: Math.ceil(total / limit),
      stats: {
        total: Number(statsRow?.total ?? 0),
        today: Number(statsRow?.today ?? 0),
        confirmed: Number(statsRow?.confirmed ?? 0),
        seated: Number(statsRow?.seated ?? 0),
        cancelled: Number(statsRow?.cancelled ?? 0),
        noshow: Number(statsRow?.noshow ?? 0),
        pending: Number(statsRow?.pending ?? 0),
      },
    });
  })
);