import { guarded, json } from "@/lib/api";
import { sql } from "@/lib/db";
import { getSpending } from "@/lib/usage";

export const GET = guarded(async (req) => {
  const p = req.nextUrl.searchParams;
  const from = p.get("from") ?? new Date(Date.now() - 30 * 86_400_000).toISOString();
  const to = p.get("to") ?? new Date(Date.now() + 60_000).toISOString();
  const model = p.get("model"); const provider = p.get("provider"); const project = p.get("project");
  const where = sql`WHERE at >= ${from}::timestamptz AND at <= ${to}::timestamptz AND (${!model} OR model_id = ${model ?? ""}) AND (${!provider} OR provider = ${provider ?? ""}) AND (${!project} OR project_id = ${project ?? ""})`;
  const totals = await sql`SELECT count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost, coalesce(sum(cost_confirmed),0)::float AS cost_confirmed, coalesce(sum(input_tokens),0)::bigint AS input_tokens, coalesce(sum(output_tokens),0)::bigint AS output_tokens, coalesce(sum(cached_tokens),0)::bigint AS cached_tokens, coalesce(sum(reasoning_tokens),0)::bigint AS reasoning_tokens, avg(latency_ms)::int AS avg_latency, avg(ttft_ms)::int AS avg_ttft, count(*) FILTER (WHERE status = 'error')::int AS errors FROM usage_events ${where}`;
  const daily = await sql`SELECT date_trunc('day', at)::date AS day, provider, coalesce(sum(cost_estimated),0)::float AS cost, count(*)::int AS requests, sum(input_tokens)::bigint AS input_tokens, sum(output_tokens)::bigint AS output_tokens FROM usage_events ${where} GROUP BY 1, 2 ORDER BY 1`;
  const byModel = await sql`SELECT model_id, provider, count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost, sum(input_tokens)::bigint AS input_tokens, sum(output_tokens)::bigint AS output_tokens, sum(cached_tokens)::bigint AS cached_tokens, avg(latency_ms)::int AS avg_latency, avg(ttft_ms)::int AS avg_ttft, count(*) FILTER (WHERE status = 'error')::int AS errors FROM usage_events ${where} GROUP BY 1, 2 ORDER BY cost DESC`;
  const byKind = await sql`SELECT kind, count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost FROM usage_events ${where} GROUP BY 1 ORDER BY cost DESC`;
  const byProject = await sql`SELECT coalesce(p.name, '—') AS project, u.project_id, count(*)::int AS requests, coalesce(sum(u.cost_estimated),0)::float AS cost FROM usage_events u LEFT JOIN projects p ON p.id = u.project_id ${where} GROUP BY 1, 2 ORDER BY cost DESC`;
  const monthly = await sql`SELECT date_trunc('month', at)::date AS month, coalesce(sum(cost_estimated),0)::float AS cost FROM usage_events GROUP BY 1 ORDER BY 1 DESC LIMIT 12`;
  const spending = await getSpending();
  return json({ totals: totals[0], daily, byModel, byKind, byProject, monthly, spending, range: { from, to } });
});