import { NextResponse } from "next/server";
import { getDbPool } from "@/db";
import { getSession } from "@/lib/auth";

export const runtime = "nodejs";
export const dynamic = "force-dynamic";

export async function GET() {
  try {
    const session = await getSession();
    if (!session) return NextResponse.json({ error: "Não autenticado." }, { status: 401 });
    if (session.role.startsWith("client_")) return NextResponse.json({ error: "A fila de exceções é exclusiva da operação Capital." }, { status: 403 });

    const db = getDbPool();
    const [rows] = await db.query(
      `SELECT i.external_id, i.tenant_id, i.vehicle_type, i.plate, i.vehicle_description, i.status, i.risk_score, i.created_at,
              t.name AS tenant_name,
              MAX(CASE WHEN a.chassis_match = 'mismatched' OR ev.rule_code IN ('CHASSIS_DIVERGENT','MANUAL_CHASSIS_DIVERGENT') THEN 1 ELSE 0 END) AS chassis_alert,
              MAX(CASE WHEN a.plate_match = 'mismatched' OR ev.rule_code IN ('PLATE_DIVERGENT','MANUAL_PLATE_DIVERGENT') THEN 1 ELSE 0 END) AS plate_alert,
              MAX(CASE WHEN a.requires_human = TRUE OR a.status = 'requires_human' THEN 1 ELSE 0 END) AS ai_requires_human,
              MAX(CASE WHEN a.status = 'failed' OR (a.confidence IS NOT NULL AND a.confidence < 80) THEN 1 ELSE 0 END) AS ai_low_confidence,
              MAX(CASE WHEN r.review_status = 'requested' THEN 1 ELSE 0 END) AS pending_review
       FROM inspections i
       INNER JOIN tenants t ON t.id = i.tenant_id
       LEFT JOIN inspection_photos p ON p.inspection_id = i.id AND p.inspection_cycle = i.inspection_cycle
       LEFT JOIN inspection_photo_analyses a ON a.inspection_photo_id = p.id
       LEFT JOIN inspection_reviews r ON r.inspection_id = i.id AND r.review_status = 'requested'
       LEFT JOIN inspection_rule_evaluations ev ON ev.inspection_id = i.id
       WHERE i.status IN ('retriage','human_review')
          OR EXISTS (SELECT 1 FROM inspection_photos ep INNER JOIN inspection_photo_analyses ea ON ea.inspection_photo_id = ep.id WHERE ep.inspection_id = i.id AND ep.inspection_cycle = i.inspection_cycle AND (ea.requires_human = TRUE OR ea.status IN ('requires_human','failed') OR ea.plate_match = 'mismatched' OR ea.chassis_match = 'mismatched' OR ea.confidence < 80))
       GROUP BY i.id, i.external_id, i.tenant_id, i.vehicle_type, i.plate, i.vehicle_description, i.status, i.risk_score, i.created_at, t.name
       ORDER BY chassis_alert DESC, i.status = 'human_review' DESC, plate_alert DESC, i.created_at ASC
       LIMIT 100`,
    );
    return NextResponse.json({ items: rows });
  } catch (error) {
    console.error("Erro ao carregar fila de exceções", error);
    return NextResponse.json({ error: "Não foi possível carregar a fila de exceções." }, { status: 500 });
  }
}