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> = {
  created_at: 'o.created_at',
  placed_at: 'o.created_at',
  total: 'o.total',
  status: 'o.status',
  customer_name: 'o.customer_name',
  restaurant: 'r.name',
};

function isAdmin(role: string) {
  return role === 'superadmin' || role === 'support';
}

export const GET = withErrorHandler(
  withAuth(async (req: AuthedRequest) => {
    if (!isAdmin(req.session.role)) 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 deliveryType = p.get('delivery_type') ?? 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') ?? 'created_at';
    const sortDir = p.get('sort_dir') === 'asc' ? 'ASC' : 'DESC';

    const conditions: SQL[] = [];

    if (restaurantId) conditions.push(sql`o.restaurant_id = ${restaurantId}`);
    if (branchId) conditions.push(sql`o.branch_id = ${branchId}`);
    if (statuses.length === 1) {
      conditions.push(sql`o.status = ${statuses[0]}`);
    } else if (statuses.length > 1) {
      conditions.push(sql`o.status::text IN (${sql.join(statuses.map(s => sql`${s}`), sql`, `)})`);
    }
    if (channel) conditions.push(sql`o.channel::text = ${channel}`);
    if (deliveryType) conditions.push(sql`o.delivery_type = ${deliveryType}`);
    if (dateFrom) conditions.push(sql`o.created_at >= ${dateFrom}`);
    if (dateTo) conditions.push(sql`o.created_at <= ${dateTo} + INTERVAL '1 day'`);
    if (search) {
      conditions.push(sql`(o.customer_name ILIKE ${`%${search}%`} OR o.customer_phone ILIKE ${`%${search}%`} OR o.order_number::text ILIKE ${`%${search}%`})`);
    }

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

    /* raw: SELECT COUNT(*), stats with FILTER, and paginated rows with JOINs */
    const [countResult, statsResult] = await Promise.all([
      db.execute(sql`SELECT COUNT(*) FROM orders o ${where}`),
      db.execute(sql`
        SELECT
          COUNT(*) as total,
          COUNT(*) FILTER (WHERE o.status = 'pending') as pending,
          COUNT(*) FILTER (WHERE o.status IN ('confirmed','kitchen','ready','processing','rider')) as active,
          COUNT(*) FILTER (WHERE o.status = 'completed') as completed,
          COUNT(*) FILTER (WHERE o.status = 'cancelled') as cancelled,
          COALESCE(SUM(o.total) FILTER (WHERE o.status = 'completed'), 0) as revenue
        FROM orders o ${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] ?? 'o.created_at';

    const { rows } = await db.execute(sql`
      SELECT
        o.id, o.order_number, o.status, o.channel, o.delivery_type,
        o.customer_name, o.customer_phone, o.customer_email,
        o.items, o.subtotal, o.tax, o.total,
        o.discount_amount, o.coupon_code,
        o.gift_card_applied, o.gift_card_remaining_balance,
        o.table_number, o.delivery_address, o.special_instructions,
        o.modification_note, o.is_modified, o.agent_id, o.conversation_id,
        o.ai_confidence, o.created_at, o.updated_at,
        o.restaurant_id, o.branch_id, o.customer_id,
        r.name AS restaurant_name,
        b.name AS branch_name
      FROM orders o
      LEFT JOIN restaurants r ON o.restaurant_id = r.id
      LEFT JOIN branches b ON o.branch_id = b.id
      ${where}
      ORDER BY ${sql.raw(orderCol)} ${sql.raw(sortDir)}
      LIMIT ${limit} OFFSET ${offset}
    `);

    return NextResponse.json({
      orders: rows,
      total,
      page,
      limit,
      pages: Math.ceil(total / limit),
      stats: {
        total: Number(statsRow?.total ?? 0),
        pending: Number(statsRow?.pending ?? 0),
        active: Number(statsRow?.active ?? 0),
        completed: Number(statsRow?.completed ?? 0),
        cancelled: Number(statsRow?.cancelled ?? 0),
        revenue: Number(statsRow?.revenue ?? 0),
      },
    });
  })
);