24.07-用量统计

-- 用量明细表（原始记录）
CREATE TABLE usage_records (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  feature TEXT NOT NULL,              -- chat/rag/embedding
  model TEXT,
  input_tokens INTEGER DEFAULT 0,
  output_tokens INTEGER DEFAULT 0,
  total_tokens INTEGER DEFAULT 0,
  request_count INTEGER DEFAULT 1,
  cost INTEGER DEFAULT 0,             -- 成本（美分）
  status TEXT DEFAULT 'success',      -- success/failed
  latency_ms INTEGER,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
)
 
-- 索引
CREATE INDEX idx_usage_tenant_time ON usage_records(tenant_id, created_at)
CREATE INDEX idx_usage_user_time ON usage_records(user_id, created_at)
CREATE INDEX idx_usage_model_time ON usage_records(model, created_at)

-- 小时聚合表
CREATE TABLE usage_hourly (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  hour INTEGER NOT NULL,              -- 小时开始时间（时间戳）
  feature TEXT,
  model TEXT,
  total_tokens INTEGER DEFAULT 0,
  input_tokens INTEGER DEFAULT 0,
  output_tokens INTEGER DEFAULT 0,
  request_count INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  total_cost INTEGER DEFAULT 0,
  total_latency_ms INTEGER DEFAULT 0,
  UNIQUE(tenant_id, hour, feature, model)
)
 
-- 日聚合表
CREATE TABLE usage_daily (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  date TEXT NOT NULL,                 -- YYYY-MM-DD
  feature TEXT,
  model TEXT,
  total_tokens INTEGER DEFAULT 0,
  input_tokens INTEGER DEFAULT 0,
  output_tokens INTEGER DEFAULT 0,
  request_count INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  total_cost INTEGER DEFAULT 0,
  total_latency_ms INTEGER DEFAULT 0,
  UNIQUE(tenant_id, date, feature, model)
)
 
-- 月聚合表
CREATE TABLE usage_monthly (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  month TEXT NOT NULL,                -- YYYY-MM
  feature TEXT,
  model TEXT,
  total_tokens INTEGER DEFAULT 0,
  input_tokens INTEGER DEFAULT 0,
  output_tokens INTEGER DEFAULT 0,
  request_count INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  total_cost INTEGER DEFAULT 0,
  total_latency_ms INTEGER DEFAULT 0,
  UNIQUE(tenant_id, month, feature, model)
)

// src/lib/usage.ts
export class UsageRecorder {
  constructor(private db: D1Database, private env: Env) {}
 
  /**
   * 记录一次 AI 调用
   */
  async record(params: {
    tenantId: string
    userId: string
    feature: string
    model?: string
    inputTokens?: number
    outputTokens?: number
    cost?: number
    status?: 'success' | 'failed'
    latencyMs?: number
  }) {
    const {
      tenantId,
      userId,
      feature,
      model,
      inputTokens = 0,
      outputTokens = 0,
      cost = 0,
      status = 'success',
      latencyMs,
    } = params
 
    const totalTokens = inputTokens + outputTokens
    const now = Date.now()
    const id = crypto.randomUUID()
 
    // 1. 记录原始数据
    await this.db.prepare(`
      INSERT INTO usage_records (id, tenant_id, user_id, feature, model, input_tokens, output_tokens, total_tokens, cost, status, latency_ms, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).bind(
      id,
      tenantId,
      userId,
      feature,
      model || null,
      inputTokens,
      outputTokens,
      totalTokens,
      cost,
      status,
      latencyMs || null,
      now
    ).run()
 
    // 2. 更新聚合表（异步，不阻塞请求）
    this.env.executionCtx.waitUntil(
      this.updateAggregations({
        tenantId,
        feature,
        model,
        inputTokens,
        outputTokens,
        totalTokens,
        cost,
        status,
        latencyMs: latencyMs || 0,
        timestamp: now,
      })
    )
  }
 
  /**
   * 更新聚合表
   */
  private async updateAggregations(data: {
    tenantId: string
    feature: string
    model?: string
    inputTokens: number
    outputTokens: number
    totalTokens: number
    cost: number
    status: string
    latencyMs: number
    timestamp: number
  }) {
    const { tenantId, feature, model, inputTokens, outputTokens, totalTokens, cost, status, latencyMs, timestamp } = data
 
    const date = new Date(timestamp)
    const hourStart = new Date(date.getFullYear(), date.getMonth(), date.getDate(), date.getHours()).getTime()
    const dateStr = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}-${String(date.getDate()).padStart(2, '0')}`
    const monthStr = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}`
 
    const successCount = status === 'success' ? 1 : 0
    const failedCount = status === 'failed' ? 1 : 0
 
    // 更新小时聚合
    await this.upsertAggregation('usage_hourly', {
      tenantId,
      timeKey: hourStart.toString(),
      feature,
      model,
      inputTokens,
      outputTokens,
      totalTokens,
      cost,
      requestCount: 1,
      successCount,
      failedCount,
      latencyMs,
    })
 
    // 更新日聚合
    await this.upsertAggregation('usage_daily', {
      tenantId,
      timeKey: dateStr,
      feature,
      model,
      inputTokens,
      outputTokens,
      totalTokens,
      cost,
      requestCount: 1,
      successCount,
      failedCount,
      latencyMs,
    })
 
    // 更新月聚合
    await this.upsertAggregation('usage_monthly', {
      tenantId,
      timeKey: monthStr,
      feature,
      model,
      inputTokens,
      outputTokens,
      totalTokens,
      cost,
      requestCount: 1,
      successCount,
      failedCount,
      latencyMs,
    })
  }
 
  private async upsertAggregation(
    table: string,
    data: {
      tenantId: string
      timeKey: string
      feature: string
      model?: string
      inputTokens: number
      outputTokens: number
      totalTokens: number
      cost: number
      requestCount: number
      successCount: number
      failedCount: number
      latencyMs: number
    }
  ) {
    const id = crypto.randomUUID()
    const { tenantId, timeKey, feature, model, inputTokens, outputTokens, totalTokens, cost, requestCount, successCount, failedCount, latencyMs } = data
 
    const timeColumn = table === 'usage_hourly' ? 'hour' : table === 'usage_daily' ? 'date' : 'month'
 
    await this.db.prepare(`
      INSERT INTO ${table} (id, tenant_id, ${timeColumn}, feature, model, total_tokens, input_tokens, output_tokens, request_count, success_count, failed_count, total_cost, total_latency_ms)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(tenant_id, ${timeColumn}, feature, model)
      DO UPDATE SET
        total_tokens = total_tokens + ?,
        input_tokens = input_tokens + ?,
        output_tokens = output_tokens + ?,
        request_count = request_count + ?,
        success_count = success_count + ?,
        failed_count = failed_count + ?,
        total_cost = total_cost + ?,
        total_latency_ms = total_latency_ms + ?
    `).bind(
      id,
      tenantId,
      timeKey,
      feature,
      model || null,
      totalTokens,
      inputTokens,
      outputTokens,
      requestCount,
      successCount,
      failedCount,
      cost,
      latencyMs,
      totalTokens,
      inputTokens,
      outputTokens,
      requestCount,
      successCount,
      failedCount,
      cost,
      latencyMs
    ).run()
  }
}

// src/routes/usage.ts
import { Hono } from 'hono'
 
const app = new Hono()
 
// 获取租户用量概览
app.get('/overview', async (c) => {
  const tenantId = c.get('tenantId')
 
  // 本月用量
  const now = new Date()
  const monthStr = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`
 
  const monthlyUsage = await c.env.DB.prepare(`
    SELECT
      SUM(total_tokens) as tokens,
      SUM(request_count) as requests,
      SUM(total_cost) as cost,
      SUM(success_count) as successCount,
      SUM(failed_count) as failedCount
    FROM usage_monthly
    WHERE tenant_id = ? AND month = ?
  `).bind(tenantId, monthStr).first()
 
  // 今日用量
  const dateStr = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}-${String(now.getDate()).padStart(2, '0')}`
 
  const dailyUsage = await c.env.DB.prepare(`
    SELECT
      SUM(total_tokens) as tokens,
      SUM(request_count) as requests,
      SUM(total_cost) as cost
    FROM usage_daily
    WHERE tenant_id = ? AND date = ?
  `).bind(tenantId, dateStr).first()
 
  // 按模型分布
  const modelUsage = await c.env.DB.prepare(`
    SELECT
      model,
      SUM(total_tokens) as tokens,
      SUM(request_count) as requests,
      SUM(total_cost) as cost
    FROM usage_monthly
    WHERE tenant_id = ? AND month = ?
    GROUP BY model
    ORDER BY tokens DESC
  `).bind(tenantId, monthStr).all()
 
  return c.json({
    monthly: {
      tokens: monthlyUsage?.tokens || 0,
      requests: monthlyUsage?.requests || 0,
      cost: monthlyUsage?.cost || 0,
      successCount: monthlyUsage?.successCount || 0,
      failedCount: monthlyUsage?.failedCount || 0,
    },
    daily: {
      tokens: dailyUsage?.tokens || 0,
      requests: dailyUsage?.requests || 0,
      cost: dailyUsage?.cost || 0,
    },
    byModel: modelUsage.results,
  })
})
 
// 获取用量趋势
app.get('/trend', async (c) => {
  const tenantId = c.get('tenantId')
  const days = parseInt(c.req.query('days') || '30')
  const groupBy = c.req.query('groupBy') || 'day'  // hour/day/month
 
  const table = groupBy === 'hour' ? 'usage_hourly' : groupBy === 'month' ? 'usage_monthly' : 'usage_daily'
  const timeColumn = groupBy === 'hour' ? 'hour' : groupBy === 'month' ? 'month' : 'date'
 
  // 计算开始时间
  const now = new Date()
  let startTime: string
 
  if (groupBy === 'hour') {
    const start = new Date(now.getTime() - days * 24 * 60 * 60 * 1000)
    startTime = Math.floor(start.getTime() / (60 * 60 * 1000)) * (60 * 60 * 1000)).toString()
  } else if (groupBy === 'month') {
    const start = new Date(now.getFullYear(), now.getMonth() - days, 1)
    startTime = `${start.getFullYear()}-${String(start.getMonth() + 1).padStart(2, '0')}`
  } else {
    const start = new Date(now.getTime() - days * 24 * 60 * 60 * 1000)
    startTime = `${start.getFullYear()}-${String(start.getMonth() + 1).padStart(2, '0')}-${String(start.getDate()).padStart(2, '0')}`
  }
 
  const trend = await c.env.DB.prepare(`
    SELECT
      ${timeColumn} as time,
      SUM(total_tokens) as tokens,
      SUM(request_count) as requests,
      SUM(total_cost) as cost,
      SUM(success_count) as successCount,
      SUM(failed_count) as failedCount,
      AVG(CASE WHEN request_count > 0 THEN total_latency_ms * 1.0 / request_count ELSE 0 END) as avgLatency
    FROM ${table}
    WHERE tenant_id = ? AND ${timeColumn} >= ?
    GROUP BY ${timeColumn}
    ORDER BY ${timeColumn} ASC
  `).bind(tenantId, startTime).all()
 
  return c.json({ trend: trend.results })
})
 
// 按用户统计
app.get('/by-user', async (c) => {
  const tenantId = c.get('tenantId')
  const days = parseInt(c.req.query('days') || '30')
 
  const startTime = Date.now() - days * 24 * 60 * 60 * 1000
 
  const userUsage = await c.env.DB.prepare(`
    SELECT
      u.id as userId,
      u.email,
      u.name,
      SUM(ur.total_tokens) as tokens,
      SUM(ur.request_count) as requests,
      SUM(ur.cost) as cost
    FROM usage_records ur
    JOIN users u ON ur.user_id = u.id
    WHERE ur.tenant_id = ? AND ur.created_at > ?
    GROUP BY ur.user_id
    ORDER BY tokens DESC
  `).bind(tenantId, startTime).all()
 
  return c.json({ users: userUsage.results })
})
 
// 按功能统计
app.get('/by-feature', async (c) => {
  const tenantId = c.get('tenantId')
  const days = parseInt(c.req.query('days') || '30')
 
  const startTime = Date.now() - days * 24 * 60 * 60 * 1000
 
  const featureUsage = await c.env.DB.prepare(`
    SELECT
      feature,
      SUM(total_tokens) as tokens,
      SUM(request_count) as requests,
      SUM(cost) as cost,
      COUNT(*) as callCount
    FROM usage_records
    WHERE tenant_id = ? AND created_at > ?
    GROUP BY feature
    ORDER BY tokens DESC
  `).bind(tenantId, startTime).all()
 
  return c.json({ features: featureUsage.results })
})

// src/routes/usage.ts（补充）
app.get('/export', async (c) => {
  const tenantId = c.get('tenantId')
  const format = c.req.query('format') || 'csv'  // csv/json
  const startDate = c.req.query('startDate')
  const endDate = c.req.query('endDate')
 
  let query = `
    SELECT
      ur.created_at,
      u.email as userEmail,
      u.name as userName,
      ur.feature,
      ur.model,
      ur.input_tokens,
      ur.output_tokens,
      ur.total_tokens,
      ur.cost,
      ur.status,
      ur.latency_ms
    FROM usage_records ur
    JOIN users u ON ur.user_id = u.id
    WHERE ur.tenant_id = ?
  `
 
  const params: any[] = [tenantId]
 
  if (startDate) {
    query += ` AND ur.created_at >= ?`
    params.push(parseInt(startDate))
  }
 
  if (endDate) {
    query += ` AND ur.created_at <= ?`
    params.push(parseInt(endDate))
  }
 
  query += ` ORDER BY ur.created_at DESC`
 
  const records = await c.env.DB.prepare(query).bind(...params).all()
 
  if (format === 'csv') {
    const headers = ['时间', '用户邮箱', '用户姓名', '功能', '模型', '输入Token', '输出Token', '总Token', '成本(美分)', '状态', '延迟(ms)']
    const rows = records.results.map(r => [
      new Date(r.created_at).toISOString(),
      r.userEmail,
      r.userName || '',
      r.feature,
      r.model || '',
      r.input_tokens,
      r.output_tokens,
      r.total_tokens,
      r.cost,
      r.status,
      r.latency_ms || '',
    ])
 
    const csv = [
      headers.join(','),
      ...rows.map(row => row.map(v => `"${v}"`).join(',')),
    ].join('\n')
 
    return new Response(csv, {
      headers: {
        'Content-Type': 'text/csv',
        'Content-Disposition': `attachment; filename="usage-${Date.now()}.csv"`,
      },
    })
  }
 
  return c.json({ records: records.results })
})

// src/routes/admin/usage.ts
import { Hono } from 'hono'
import { requireRole } from '../../middleware/permission'
 
const app = new Hono()
 
// 全局用量统计（管理员）
app.get('/global', requireRole('admin'), async (c) => {
  const days = parseInt(c.req.query('days') || '30')
  const startTime = Date.now() - days * 24 * 60 * 60 * 1000
 
  // 全局汇总
  const globalStats = await c.env.DB.prepare(`
    SELECT
      COUNT(DISTINCT tenant_id) as activeTenants,
      SUM(total_tokens) as totalTokens,
      SUM(request_count) as totalRequests,
      SUM(cost) as totalCost,
      AVG(latency_ms) as avgLatency
    FROM usage_records
    WHERE created_at > ?
  `).bind(startTime).first()
 
  // 按租户排名
  const topTenants = await c.env.DB.prepare(`
    SELECT
      t.id as tenantId,
      t.name as tenantName,
      SUM(ur.total_tokens) as tokens,
      SUM(ur.cost) as cost
    FROM usage_records ur
    JOIN tenants t ON ur.tenant_id = t.id
    WHERE ur.created_at > ?
    GROUP BY ur.tenant_id
    ORDER BY tokens DESC
    LIMIT 10
  `).bind(startTime).all()
 
  // 按模型分布
  const modelStats = await c.env.DB.prepare(`
    SELECT
      model,
      COUNT(*) as requestCount,
      SUM(total_tokens) as tokens,
      SUM(cost) as cost,
      AVG(latency_ms) as avgLatency
    FROM usage_records
    WHERE created_at > ?
    GROUP BY model
    ORDER BY tokens DESC
  `).bind(startTime).all()
 
  return c.json({
    global: globalStats,
    topTenants: topTenants.results,
    modelStats: modelStats.results,
  })
})