import { prisma } from '@/lib/prisma' export interface TokenKpi { totalTokens: number totalCostUsd: number avgCostPerJob: number jobCount: number } export interface TokenJobRow { jobId: string taskTitle: string | null ideaCode: string | null modelId: string | null inputTokens: number | null outputTokens: number | null cacheReadTokens: number | null cacheWriteTokens: number | null thinkingTokens: number | null costUsd: number | null durationSeconds: number | null } export interface TokenStatsByKindRow { kind: string jobCount: number totalTokens: number totalCostUsd: number } export interface TokenStatsResult { kpi: TokenKpi jobs: TokenJobRow[] } type RawKpiRow = { total_tokens: bigint total_cost: number | null avg_cost: number | null job_count: bigint } type RawJobRow = { job_id: string task_title: string | null idea_code: string | null model_id: string | null input_tokens: number | null output_tokens: number | null cache_read_tokens: number | null cache_write_tokens: number | null actual_thinking_tokens: number | null cost_usd: number | null duration_seconds: number | null } type RawByKindRow = { kind: string job_count: bigint total_tokens: bigint total_cost: number | null } const EMPTY_KPI: TokenKpi = { totalTokens: 0, totalCostUsd: 0, avgCostPerJob: 0, jobCount: 0 } export async function getTokenStats(userId: string, sprintId: string): Promise { if (!sprintId) return { kpi: EMPTY_KPI, jobs: [] } const [kpiRows, jobRows] = await Promise.all([ prisma.$queryRaw` SELECT COALESCE(SUM(cj.input_tokens + cj.output_tokens + cj.cache_read_tokens + cj.cache_write_tokens + COALESCE(cj.actual_thinking_tokens, 0)), 0) AS total_tokens, SUM( cj.input_tokens * mp.input_price_per_1m / 1000000.0 + cj.output_tokens * mp.output_price_per_1m / 1000000.0 + cj.cache_read_tokens * mp.cache_read_price_per_1m / 1000000.0 + cj.cache_write_tokens * mp.cache_write_price_per_1m / 1000000.0 + COALESCE(cj.actual_thinking_tokens, 0) * mp.input_price_per_1m / 1000000.0 ) FILTER (WHERE cj.input_tokens IS NOT NULL) AS total_cost, AVG( cj.input_tokens * mp.input_price_per_1m / 1000000.0 + cj.output_tokens * mp.output_price_per_1m / 1000000.0 + cj.cache_read_tokens * mp.cache_read_price_per_1m / 1000000.0 + cj.cache_write_tokens * mp.cache_write_price_per_1m / 1000000.0 + COALESCE(cj.actual_thinking_tokens, 0) * mp.input_price_per_1m / 1000000.0 ) FILTER (WHERE cj.input_tokens IS NOT NULL) AS avg_cost, COUNT(*) FILTER (WHERE cj.input_tokens IS NOT NULL) AS job_count FROM claude_jobs cj JOIN tasks t ON cj.task_id = t.id JOIN stories s ON t.story_id = s.id LEFT JOIN model_prices mp ON mp.model_id = cj.model_id WHERE cj.user_id = ${userId} AND s.sprint_id = ${sprintId} AND cj.status = 'DONE' `, prisma.$queryRaw` SELECT cj.id AS job_id, t.title AS task_title, i.code AS idea_code, cj.model_id, cj.input_tokens, cj.output_tokens, cj.cache_read_tokens, cj.cache_write_tokens, cj.actual_thinking_tokens, CASE WHEN cj.input_tokens IS NOT NULL THEN cj.input_tokens * mp.input_price_per_1m / 1000000.0 + cj.output_tokens * mp.output_price_per_1m / 1000000.0 + cj.cache_read_tokens * mp.cache_read_price_per_1m / 1000000.0 + cj.cache_write_tokens * mp.cache_write_price_per_1m / 1000000.0 + COALESCE(cj.actual_thinking_tokens, 0) * mp.input_price_per_1m / 1000000.0 END AS cost_usd, EXTRACT(EPOCH FROM (cj.finished_at - cj.claimed_at)) AS duration_seconds FROM claude_jobs cj LEFT JOIN tasks t ON cj.task_id = t.id LEFT JOIN ideas i ON cj.idea_id = i.id LEFT JOIN stories s ON t.story_id = s.id LEFT JOIN model_prices mp ON mp.model_id = cj.model_id WHERE cj.user_id = ${userId} AND (s.sprint_id = ${sprintId} OR cj.idea_id IS NOT NULL) AND cj.status = 'DONE' ORDER BY cj.finished_at DESC `, ]) const kpi = kpiRows[0] return { kpi: { totalTokens: Number(kpi?.total_tokens ?? 0), totalCostUsd: Number(kpi?.total_cost ?? 0), avgCostPerJob: Number(kpi?.avg_cost ?? 0), jobCount: Number(kpi?.job_count ?? 0), }, jobs: jobRows.map(r => ({ jobId: r.job_id, taskTitle: r.task_title, ideaCode: r.idea_code, modelId: r.model_id, inputTokens: r.input_tokens, outputTokens: r.output_tokens, cacheReadTokens: r.cache_read_tokens, cacheWriteTokens: r.cache_write_tokens, thinkingTokens: r.actual_thinking_tokens, costUsd: r.cost_usd != null ? Number(r.cost_usd) : null, durationSeconds: r.duration_seconds != null ? Number(r.duration_seconds) : null, })), } } // PBI-67: per-kind aggregatie. Toont totaal tokens + kosten per ClaudeJob.kind // binnen één sprint zodat we de relatieve uitgaven van IDEA_GRILL vs // TASK_IMPLEMENTATION etc. kunnen zien. Voor jobs zonder sprint-koppeling // (idea-jobs) blijven we filteren op user_id + sprint_id; idea-jobs zonder // task vallen buiten deze view. export async function getTokenStatsByKind( userId: string, sprintId: string, ): Promise { if (!sprintId) return [] const rows = await prisma.$queryRaw` SELECT cj.kind::text AS kind, COUNT(*) FILTER (WHERE cj.input_tokens IS NOT NULL) AS job_count, COALESCE(SUM( cj.input_tokens + cj.output_tokens + cj.cache_read_tokens + cj.cache_write_tokens + COALESCE(cj.actual_thinking_tokens, 0) ), 0) AS total_tokens, SUM( cj.input_tokens * mp.input_price_per_1m / 1000000.0 + cj.output_tokens * mp.output_price_per_1m / 1000000.0 + cj.cache_read_tokens * mp.cache_read_price_per_1m / 1000000.0 + cj.cache_write_tokens * mp.cache_write_price_per_1m / 1000000.0 + COALESCE(cj.actual_thinking_tokens, 0) * mp.input_price_per_1m / 1000000.0 ) FILTER (WHERE cj.input_tokens IS NOT NULL) AS total_cost FROM claude_jobs cj JOIN tasks t ON cj.task_id = t.id JOIN stories s ON t.story_id = s.id LEFT JOIN model_prices mp ON mp.model_id = cj.model_id WHERE cj.user_id = ${userId} AND s.sprint_id = ${sprintId} AND cj.status = 'DONE' GROUP BY cj.kind ORDER BY total_cost DESC NULLS LAST ` return rows.map((r) => ({ kind: r.kind, jobCount: Number(r.job_count), totalTokens: Number(r.total_tokens), totalCostUsd: Number(r.total_cost ?? 0), })) }