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

function requireAdmin(req: AuthedRequest) {
  if (req.session.role !== 'superadmin' && req.session.role !== 'support') throw new ForbiddenError();
}

export const GET = withErrorHandler(
  withAuth(async (req: AuthedRequest) => {
    requireAdmin(req);
    await initDatabase();
    const url = new URL(req.url);
    const status = url.searchParams.get('status');
    const search = url.searchParams.get('search');
    const restaurantId = url.searchParams.get('restaurant_id');
    const templateKey = url.searchParams.get('template_key');
    const safeStatus = status || null;
    const safeSearch = search ? `%${search}%` : null;
    const safeRestaurant = restaurantId || null;
    const safeTemplate = templateKey || null;
    const { rows } = await db.execute(sql`
      SELECT id, restaurant_id, recipient, template_key, channel, kind, status, scheduled_for, sent_at, attempts, last_error, created_at, payload
      FROM email_outbox
      WHERE (${safeStatus}::text IS NULL OR status = ${safeStatus})
        AND (${safeSearch}::text IS NULL OR recipient ILIKE ${safeSearch} OR template_key ILIKE ${safeSearch})
        AND (${safeRestaurant}::text IS NULL OR restaurant_id::text = ${safeRestaurant})
        AND (${safeTemplate}::text IS NULL OR template_key = ${safeTemplate})
      ORDER BY created_at DESC LIMIT 200
    `);
    return NextResponse.json({ items: rows });
  })
);