spb/chat-spboucher Public

1// Author: Simon-Pierre Boucher2// Contact: contact@spboucher.ai3// Project: chat.spboucher.ai45import { NextResponse, type NextRequest } from "next/server";6import { requireSession } from "@/lib/auth/guard";7import { getDb } from "@/lib/db/database";89export const runtime = "nodejs";1011const PERIODS: Record<string, number | null> = {12  today: 1000 * 60 * 60 * 24,13  "7d": 1000 * 60 * 60 * 24 * 7,14  "30d": 1000 * 60 * 60 * 24 * 30,15  all: null,16};1718export async function GET(req: NextRequest) {19  const { unauthorized } = await requireSession();20  if (unauthorized) return unauthorized;2122  const period = req.nextUrl.searchParams.get("period") ?? "7d";23  const windowMs = PERIODS[period] ?? PERIODS["7d"];24  const since = windowMs === null ? 0 : Date.now() - windowMs;25  const db = getDb();2627  const totals = db28    .prepare(29      `SELECT COUNT(*) AS requests,30              COALESCE(SUM(prompt_tokens), 0) AS prompt_tokens,31              COALESCE(SUM(completion_tokens), 0) AS completion_tokens,32              COALESCE(SUM(reasoning_tokens), 0) AS reasoning_tokens,33              COALESCE(SUM(total_tokens), 0) AS total_tokens,34              COALESCE(SUM(COALESCE(reported_cost_usd, estimated_cost_usd)), 0) AS cost_usd35       FROM generation_usage WHERE created_at >= ?`36    )37    .get(since) as Record<string, number>;3839  const byModel = db40    .prepare(41      `SELECT gu.model_id,42              COALESCE(m.name, gu.model_id) AS model_name,43              m.provider,44              COUNT(*) AS requests,45              COALESCE(SUM(gu.total_tokens), 0) AS total_tokens,46              COALESCE(SUM(COALESCE(gu.reported_cost_usd, gu.estimated_cost_usd)), 0) AS cost_usd47       FROM generation_usage gu48       LEFT JOIN models m ON m.openrouter_model_id = gu.model_id49       WHERE gu.created_at >= ?50       GROUP BY gu.model_id51       ORDER BY cost_usd DESC, requests DESC52       LIMIT 25`53    )54    .all(since);5556  const byDay = db57    .prepare(58      `SELECT date(created_at / 1000, 'unixepoch', 'localtime') AS day,59              COUNT(*) AS requests,60              COALESCE(SUM(total_tokens), 0) AS total_tokens,61              COALESCE(SUM(COALESCE(reported_cost_usd, estimated_cost_usd)), 0) AS cost_usd62       FROM generation_usage WHERE created_at >= ?63       GROUP BY day ORDER BY day`64    )65    .all(since);6667  return NextResponse.json({ period, totals, byModel, byDay });68}69