// CRITICALimport type { Hono } from "hono";import Database from "bun:sqlite";import type { AppContext } from "../types/context"; /** * Register usage analytics routes. * Uses SQLite spend_logs table as single source of truth. * Falls back to empty data if table doesn't exist. * @param app - Hono app. * @param context - App context. */export const registerUsageRoutes = (app: Hono, context: AppContext): void => {  /**   * Calculate percentage change.   * @param current - Current value.   * @param previous - Previous value.   * @returns Change percent or null.   */  const calcChange = (current: number, previous: number): number | null => {    if (!previous || previous === 0) {      return null;    }    return Math.round(((current - previous) / previous) * 1000) / 10;  };   /**   * Create empty usage stats response.   * @returns Empty usage response object.   */  const emptyResponse = (): Record<string, unknown> => ({    totals: {      total_tokens: 0,      prompt_tokens: 0,      completion_tokens: 0,      total_requests: 0,      successful_requests: 0,      failed_requests: 0,      success_rate: 0,      unique_sessions: 0,      unique_users: 0,    },    latency: {      avg_ms: 0,      p50_ms: 0,      p95_ms: 0,      p99_ms: 0,      min_ms: 0,      max_ms: 0,    },    ttft: {      avg_ms: 0,      p50_ms: 0,      p95_ms: 0,      p99_ms: 0,    },    tokens_per_request: {      avg: 0,      avg_prompt: 0,      avg_completion: 0,      max: 0,      p50: 0,      p95: 0,    },    cache: {      hits: 0,      misses: 0,      hit_tokens: 0,      miss_tokens: 0,      hit_rate: 0,    },    week_over_week: {      this_week: { requests: 0, tokens: 0, successful: 0 },      last_week: { requests: 0, tokens: 0, successful: 0 },      change_pct: { requests: null, tokens: null },    },    recent_activity: {      last_hour_requests: 0,      last_24h_requests: 0,      prev_24h_requests: 0,      last_24h_tokens: 0,      change_24h_pct: null,    },    peak_days: [],    peak_hours: [],    by_model: [],    daily: [],    hourly_pattern: [],  });   app.get("/usage", async (ctx) => {    let db: Database | null = null;    try {      db = new Database(context.config.db_path, { readonly: true });       // Check if spend_logs table exists      const tableCheck = db.query<{ name: string }, []>(        `SELECT name FROM sqlite_master WHERE type='table' AND name='spend_logs'`      ).get();       if (!tableCheck) {        // Table doesn't exist, return empty data        return ctx.json(emptyResponse());      }       // Totals      const totals = db.query<{        total_tokens: number;        prompt_tokens: number;        completion_tokens: number;        total_requests: number;        successful_requests: number;        failed_requests: number;        unique_sessions: number;      }, []>(`        SELECT          COALESCE(SUM(total_tokens), 0) as total_tokens,          COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,          COALESCE(SUM(completion_tokens), 0) as completion_tokens,          COUNT(*) as total_requests,          SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_requests,          SUM(CASE WHEN status != 'success' OR status IS NULL THEN 1 ELSE 0 END) as failed_requests,          COUNT(DISTINCT session_id) as unique_sessions        FROM spend_logs      `).get() ?? { total_tokens: 0, prompt_tokens: 0, completion_tokens: 0, total_requests: 0, successful_requests: 0, failed_requests: 0, unique_sessions: 0 };       // If no data at all, return empty response      if (totals.total_requests === 0) {        return ctx.json(emptyResponse());      }       // Latency stats (using julianday for time calculations)      const latency = db.query<{        avg_ms: number;        min_ms: number;        max_ms: number;      }, []>(`        SELECT          AVG((julianday(end_time) - julianday(start_time)) * 86400000) as avg_ms,          MIN((julianday(end_time) - julianday(start_time)) * 86400000) as min_ms,          MAX((julianday(end_time) - julianday(start_time)) * 86400000) as max_ms        FROM spend_logs        WHERE status = 'success' AND end_time IS NOT NULL AND start_time IS NOT NULL      `).get() ?? { avg_ms: 0, min_ms: 0, max_ms: 0 };       // Percentiles need to be calculated differently in SQLite      const latencyPercentiles = db.query<{ latency_ms: number }, []>(`        SELECT (julianday(end_time) - julianday(start_time)) * 86400000 as latency_ms        FROM spend_logs        WHERE status = 'success' AND end_time IS NOT NULL AND start_time IS NOT NULL        ORDER BY latency_ms      `).all();       const getPercentile = (sorted: { latency_ms: number }[], p: number): number => {        if (sorted.length === 0) return 0;        const index = Math.floor(sorted.length * p);        return Math.round(sorted[Math.min(index, sorted.length - 1)]?.latency_ms ?? 0);      };       // TTFT stats      const ttft = db.query<{        avg_ms: number;      }, []>(`        SELECT          AVG((julianday(completion_start_time) - julianday(start_time)) * 86400000) as avg_ms        FROM spend_logs        WHERE status = 'success'          AND completion_start_time IS NOT NULL          AND completion_start_time != ''          AND start_time IS NOT NULL      `).get() ?? { avg_ms: 0 };       const ttftPercentiles = db.query<{ ttft_ms: number }, []>(`        SELECT (julianday(completion_start_time) - julianday(start_time)) * 86400000 as ttft_ms        FROM spend_logs        WHERE status = 'success'          AND completion_start_time IS NOT NULL          AND completion_start_time != ''          AND start_time IS NOT NULL        ORDER BY ttft_ms      `).all();       // Token stats      const tokenStats = db.query<{        avg_tokens: number;        avg_prompt: number;        avg_completion: number;        max_tokens: number;      }, []>(`        SELECT          AVG(total_tokens) as avg_tokens,          AVG(prompt_tokens) as avg_prompt,          AVG(completion_tokens) as avg_completion,          MAX(total_tokens) as max_tokens        FROM spend_logs        WHERE status = 'success' AND total_tokens IS NOT NULL      `).get() ?? { avg_tokens: 0, avg_prompt: 0, avg_completion: 0, max_tokens: 0 };       const tokenPercentiles = db.query<{ tokens: number }, []>(`        SELECT total_tokens as tokens        FROM spend_logs        WHERE status = 'success' AND total_tokens IS NOT NULL        ORDER BY tokens      `).all();       // Cache stats      const cacheRows = db.query<{        cache_hit: string | null;        count: number;        tokens: number;      }, []>(`        SELECT          cache_hit,          COUNT(*) as count,          COALESCE(SUM(total_tokens), 0) as tokens        FROM spend_logs        GROUP BY cache_hit      `).all();       const cache = { hits: 0, misses: 0, hit_tokens: 0, miss_tokens: 0, hit_rate: 0 };      for (const row of cacheRows) {        if (row.cache_hit === "True") {          cache.hits = row.count;          cache.hit_tokens = row.tokens;        } else if (row.cache_hit === "False" || row.cache_hit === null) {          cache.misses += row.count;          cache.miss_tokens += row.tokens;        }      }      const totalCache = cache.hits + cache.misses;      if (totalCache > 0) {        cache.hit_rate = Math.round((cache.hits / totalCache) * 10000) / 100;      }       // By model      const byModel = db.query<{        model: string;        requests: number;        successful: number;        total_tokens: number;        prompt_tokens: number;        completion_tokens: number;        avg_tokens: number;        avg_latency_ms: number;        avg_ttft_ms: number;      }, []>(`        SELECT          model,          COUNT(*) as requests,          SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,          COALESCE(SUM(total_tokens), 0) as total_tokens,          COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,          COALESCE(SUM(completion_tokens), 0) as completion_tokens,          AVG(CASE WHEN status = 'success' THEN total_tokens END) as avg_tokens,          AVG(CASE WHEN status = 'success' THEN (julianday(end_time) - julianday(start_time)) * 86400000 END) as avg_latency_ms,          AVG(CASE WHEN status = 'success' AND completion_start_time IS NOT NULL AND completion_start_time != ''              THEN (julianday(completion_start_time) - julianday(start_time)) * 86400000 END) as avg_ttft_ms        FROM spend_logs        WHERE model != '' AND model IS NOT NULL        GROUP BY model        ORDER BY total_tokens DESC        LIMIT 25      `).all();       // Daily stats (last 14 days)      const daily = db.query<{        date: string;        requests: number;        successful: number;        total_tokens: number;        prompt_tokens: number;        completion_tokens: number;        avg_latency_ms: number;      }, []>(`        SELECT          DATE(start_time) as date,          COUNT(*) as requests,          SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,          COALESCE(SUM(total_tokens), 0) as total_tokens,          COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,          COALESCE(SUM(completion_tokens), 0) as completion_tokens,          AVG(CASE WHEN status = 'success' THEN (julianday(end_time) - julianday(start_time)) * 86400000 END) as avg_latency_ms        FROM spend_logs        WHERE DATE(start_time) >= DATE('now', '-14 days')        GROUP BY DATE(start_time)        ORDER BY date DESC      `).all();       // Hourly pattern      const hourly = db.query<{        hour: number;        requests: number;        successful: number;        tokens: number;      }, []>(`        SELECT          CAST(strftime('%H', start_time) AS INTEGER) as hour,          COUNT(*) as requests,          SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,          COALESCE(SUM(total_tokens), 0) as tokens        FROM spend_logs        WHERE start_time IS NOT NULL        GROUP BY strftime('%H', start_time)        ORDER BY hour      `).all();       // Week over week      const wow = db.query<{        this_week_requests: number;        last_week_requests: number;        this_week_tokens: number;        last_week_tokens: number;        this_week_successful: number;        last_week_successful: number;      }, []>(`        SELECT          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-7 days') THEN 1 ELSE 0 END) as this_week_requests,          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-14 days') AND DATE(start_time) < DATE('now', '-7 days') THEN 1 ELSE 0 END) as last_week_requests,          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-7 days') AND status = 'success' THEN total_tokens ELSE 0 END) as this_week_tokens,          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-14 days') AND DATE(start_time) < DATE('now', '-7 days') AND status = 'success' THEN total_tokens ELSE 0 END) as last_week_tokens,          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-7 days') AND status = 'success' THEN 1 ELSE 0 END) as this_week_successful,          SUM(CASE WHEN DATE(start_time) >= DATE('now', '-14 days') AND DATE(start_time) < DATE('now', '-7 days') AND status = 'success' THEN 1 ELSE 0 END) as last_week_successful        FROM spend_logs      `).get() ?? { this_week_requests: 0, last_week_requests: 0, this_week_tokens: 0, last_week_tokens: 0, this_week_successful: 0, last_week_successful: 0 };       // Peak days      const peakDays = db.query<{        date: string;        requests: number;        tokens: number;      }, []>(`        SELECT          DATE(start_time) as date,          COUNT(*) as requests,          COALESCE(SUM(total_tokens), 0) as tokens        FROM spend_logs        WHERE status = 'success'        GROUP BY DATE(start_time)        ORDER BY requests DESC        LIMIT 5      `).all();       // Peak hours      const peakHours = db.query<{        hour: number;        requests: number;      }, []>(`        SELECT          CAST(strftime('%H', start_time) AS INTEGER) as hour,          COUNT(*) as requests        FROM spend_logs        WHERE DATE(start_time) >= DATE('now', '-7 days')        GROUP BY strftime('%H', start_time)        ORDER BY requests DESC        LIMIT 5      `).all();       // Recent activity      const recent = db.query<{        last_24h_requests: number;        prev_24h_requests: number;        last_24h_tokens: number;        last_hour_requests: number;      }, []>(`        SELECT          SUM(CASE WHEN datetime(start_time) >= datetime('now', '-24 hours') THEN 1 ELSE 0 END) as last_24h_requests,          SUM(CASE WHEN datetime(start_time) >= datetime('now', '-48 hours') AND datetime(start_time) < datetime('now', '-24 hours') THEN 1 ELSE 0 END) as prev_24h_requests,          SUM(CASE WHEN datetime(start_time) >= datetime('now', '-24 hours') AND status = 'success' THEN total_tokens ELSE 0 END) as last_24h_tokens,          SUM(CASE WHEN datetime(start_time) >= datetime('now', '-1 hour') THEN 1 ELSE 0 END) as last_hour_requests        FROM spend_logs      `).get() ?? { last_24h_requests: 0, prev_24h_requests: 0, last_24h_tokens: 0, last_hour_requests: 0 };       // Format by_model with tokens_per_sec      const modelsFormatted = byModel.map((row) => {        const avgLatencyMs = row.avg_latency_ms ?? 0;        const completionTokens = row.completion_tokens;        let tokensPerSec: number | null = null;        if (avgLatencyMs > 0 && row.successful > 0) {          const avgCompletion = completionTokens / row.successful;          tokensPerSec = avgCompletion ? Math.round((avgCompletion / (avgLatencyMs / 1000)) * 10) / 10 : null;        }        return {          model: row.model,          requests: row.requests,          successful: row.successful,          success_rate: row.requests ? Math.round((row.successful / row.requests) * 1000) / 10 : 0,          total_tokens: row.total_tokens,          prompt_tokens: row.prompt_tokens,          completion_tokens: completionTokens,          avg_tokens: Math.round(row.avg_tokens ?? 0),          avg_latency_ms: Math.round(avgLatencyMs),          p50_latency_ms: Math.round(avgLatencyMs), // Approximation          avg_ttft_ms: Math.round(row.avg_ttft_ms ?? 0),          tokens_per_sec: tokensPerSec,        };      });       const successRate = totals.total_requests        ? Math.round((totals.successful_requests / totals.total_requests) * 10000) / 100        : 0;       return ctx.json({        totals: {          total_tokens: totals.total_tokens,          prompt_tokens: totals.prompt_tokens,          completion_tokens: totals.completion_tokens,          total_requests: totals.total_requests,          successful_requests: totals.successful_requests,          failed_requests: totals.failed_requests,          success_rate: successRate,          unique_sessions: totals.unique_sessions,          unique_users: 2, // Hardcoded for now        },        latency: {          avg_ms: Math.round(latency.avg_ms ?? 0),          p50_ms: getPercentile(latencyPercentiles, 0.5),          p95_ms: getPercentile(latencyPercentiles, 0.95),          p99_ms: getPercentile(latencyPercentiles, 0.99),          min_ms: Math.round(latency.min_ms ?? 0),          max_ms: Math.round(latency.max_ms ?? 0),        },        ttft: {          avg_ms: Math.round(ttft.avg_ms ?? 0),          p50_ms: getPercentile(ttftPercentiles.map(r => ({ latency_ms: r.ttft_ms })), 0.5),          p95_ms: getPercentile(ttftPercentiles.map(r => ({ latency_ms: r.ttft_ms })), 0.95),          p99_ms: getPercentile(ttftPercentiles.map(r => ({ latency_ms: r.ttft_ms })), 0.99),        },        tokens_per_request: {          avg: Math.round(tokenStats.avg_tokens ?? 0),          avg_prompt: Math.round(tokenStats.avg_prompt ?? 0),          avg_completion: Math.round(tokenStats.avg_completion ?? 0),          max: tokenStats.max_tokens ?? 0,          p50: getPercentile(tokenPercentiles.map(r => ({ latency_ms: r.tokens })), 0.5),          p95: getPercentile(tokenPercentiles.map(r => ({ latency_ms: r.tokens })), 0.95),        },        cache,        week_over_week: {          this_week: {            requests: wow.this_week_requests,            tokens: wow.this_week_tokens,            successful: wow.this_week_successful,          },          last_week: {            requests: wow.last_week_requests,            tokens: wow.last_week_tokens,            successful: wow.last_week_successful,          },          change_pct: {            requests: calcChange(wow.this_week_requests, wow.last_week_requests),            tokens: calcChange(wow.this_week_tokens, wow.last_week_tokens),          },        },        recent_activity: {          last_hour_requests: recent.last_hour_requests,          last_24h_requests: recent.last_24h_requests,          prev_24h_requests: recent.prev_24h_requests,          last_24h_tokens: recent.last_24h_tokens,          change_24h_pct: calcChange(recent.last_24h_requests, recent.prev_24h_requests),        },        peak_days: peakDays.map((row) => ({          date: row.date,          requests: row.requests,          tokens: row.tokens,        })),        peak_hours: peakHours.map((row) => ({          hour: row.hour,          requests: row.requests,        })),        by_model: modelsFormatted,        daily: daily.map((row) => ({          date: row.date,          requests: row.requests,          successful: row.successful,          success_rate: row.requests ? Math.round((row.successful / row.requests) * 1000) / 10 : 0,          total_tokens: row.total_tokens,          prompt_tokens: row.prompt_tokens,          completion_tokens: row.completion_tokens,          avg_latency_ms: Math.round(row.avg_latency_ms ?? 0),        })),        hourly_pattern: hourly.map((row) => ({          hour: row.hour,          requests: row.requests,          successful: row.successful,          tokens: row.tokens,        })),      });    } catch (error) {      // On any error, return empty data instead of throwing      console.error("[Usage] Error fetching usage stats:", error);      return ctx.json(emptyResponse());    } finally {      if (db) {        db.close();      }    }  });};