import { NextResponse } from "next/server";
import { apiError } from "@/lib/api.ts";
import { requireRole } from "@/lib/auth/session.ts";
import { all, get } from "@/lib/db/index.ts";
import { getBudgets } from "@/lib/usage.ts";

export async function GET() {
  try {
    await requireRole("instructor");
    const n = (sql: string, ...p: unknown[]) => get<{ n: number }>(sql, ...p)?.n ?? 0;
    const overview = {
      users: n("SELECT COUNT(*) as n FROM users"),
      students: n("SELECT COUNT(*) as n FROM users WHERE role = 'student'"),
      activeWeek: n("SELECT COUNT(DISTINCT user_id) as n FROM activity_log WHERE created_at >= datetime('now','-7 days')"),
      conversations: n("SELECT COUNT(*) as n FROM conversations"),
      messages: n("SELECT COUNT(*) as n FROM messages"),
      documents: n("SELECT COUNT(*) as n FROM documents"),
      chunks: n("SELECT COUNT(*) as n FROM chunks"),
      flagsOpen: n("SELECT COUNT(*) as n FROM report_flags WHERE resolved = 0"),
      apiErrors7d: n("SELECT COUNT(*) as n FROM usage_log WHERE ok = 0 AND created_at >= datetime('now','-7 days')"),
      costMonth: get<{ c: number }>("SELECT COALESCE(SUM(cost),0) as c FROM usage_log WHERE created_at >= datetime('now','start of month')")?.c ?? 0,
      costToday: get<{ c: number }>("SELECT COALESCE(SUM(cost),0) as c FROM usage_log WHERE created_at >= datetime('now','start of day')")?.c ?? 0,
      budgets: getBudgets(),
      lastIngestion: get("SELECT id, started_at, finished_at, status, chunks_created, files_ingested FROM ingestion_runs ORDER BY id DESC LIMIT 1"),
      costByDay: all(
        `SELECT date(created_at) as day, ROUND(SUM(cost), 4) as cost, COUNT(*) as calls
         FROM usage_log WHERE created_at >= datetime('now','-14 days') GROUP BY day ORDER BY day`
      ),
      topModels: all(
        `SELECT model, COUNT(*) as calls, ROUND(SUM(cost),4) as cost, SUM(tokens_in) as tin, SUM(tokens_out) as tout
         FROM usage_log WHERE created_at >= datetime('now','-30 days') GROUP BY model ORDER BY calls DESC LIMIT 10`
      ),
      invalidCitations7d: n(
        `SELECT COUNT(*) as n FROM activity_log WHERE kind='chat' AND created_at >= datetime('now','-7 days') AND json_extract(meta,'$.invalidCitations') > 0`
      ),
    };
    return NextResponse.json(overview);
  } catch (e) {
    return apiError(e);
  }
}