spb/immbot-ai

1import { NextResponse } from "next/server";2import { apiError } from "@/lib/api.ts";3import { requireRole } from "@/lib/auth/session.ts";4import { all, get } from "@/lib/db/index.ts";5import { getBudgets } from "@/lib/usage.ts";67export async function GET() {8  try {9    await requireRole("instructor");10    const n = (sql: string, ...p: unknown[]) => get<{ n: number }>(sql, ...p)?.n ?? 0;11    const overview = {12      users: n("SELECT COUNT(*) as n FROM users"),13      students: n("SELECT COUNT(*) as n FROM users WHERE role = 'student'"),14      activeWeek: n("SELECT COUNT(DISTINCT user_id) as n FROM activity_log WHERE created_at >= datetime('now','-7 days')"),15      conversations: n("SELECT COUNT(*) as n FROM conversations"),16      messages: n("SELECT COUNT(*) as n FROM messages"),17      documents: n("SELECT COUNT(*) as n FROM documents"),18      chunks: n("SELECT COUNT(*) as n FROM chunks"),19      flagsOpen: n("SELECT COUNT(*) as n FROM report_flags WHERE resolved = 0"),20      apiErrors7d: n("SELECT COUNT(*) as n FROM usage_log WHERE ok = 0 AND created_at >= datetime('now','-7 days')"),21      costMonth: get<{ c: number }>("SELECT COALESCE(SUM(cost),0) as c FROM usage_log WHERE created_at >= datetime('now','start of month')")?.c ?? 0,22      costToday: get<{ c: number }>("SELECT COALESCE(SUM(cost),0) as c FROM usage_log WHERE created_at >= datetime('now','start of day')")?.c ?? 0,23      budgets: getBudgets(),24      lastIngestion: get("SELECT id, started_at, finished_at, status, chunks_created, files_ingested FROM ingestion_runs ORDER BY id DESC LIMIT 1"),25      costByDay: all(26        `SELECT date(created_at) as day, ROUND(SUM(cost), 4) as cost, COUNT(*) as calls27         FROM usage_log WHERE created_at >= datetime('now','-14 days') GROUP BY day ORDER BY day`28      ),29      topModels: all(30        `SELECT model, COUNT(*) as calls, ROUND(SUM(cost),4) as cost, SUM(tokens_in) as tin, SUM(tokens_out) as tout31         FROM usage_log WHERE created_at >= datetime('now','-30 days') GROUP BY model ORDER BY calls DESC LIMIT 10`32      ),33      invalidCitations7d: n(34        `SELECT COUNT(*) as n FROM activity_log WHERE kind='chat' AND created_at >= datetime('now','-7 days') AND json_extract(meta,'$.invalidCitations') > 0`35      ),36    };37    return NextResponse.json(overview);38  } catch (e) {39    return apiError(e);40  }41}42