jianhen-voc/backend/cloud/voc-init.js

/**
 * VOC 鉴权 + 计费 建表云函数
 * 
 * 数据库：NovaCloud (ncloudmaster)
 * 
 * 支持 action:
 *   - init        : 建表 + 建索引（幂等，可重复执行）
 *   - addKey      : 添加 API Key
 *   - listKeys    : 列出所有 API Key
 *   - disableKey  : 禁用某个 API Key
 *   - enableKey   : 启用某个 API Key
 *   - stats       : 查询用量统计
 *   - debug       : 调试模式，返回接收到的所有参数
 */
async function handler(request, response) {
  console.log('🔑 [VOC-Init] 云函数被调用...');

  try {
    // ==================== 0. 建表（幂等） ====================
    // --- VocApiKey: API Key 鉴权表 ---
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "VocApiKey" (
        "objectId"     VARCHAR(10) PRIMARY KEY,
        "key"          VARCHAR(255) NOT NULL UNIQUE,
        "clientName"   VARCHAR(255) DEFAULT 'unknown',
        "isActive"     BOOLEAN DEFAULT true,
        "dailyQuota"   INTEGER DEFAULT 10000,
        "rateLimit"    INTEGER DEFAULT 60,
        "permissions"  JSONB DEFAULT '["*"]'::jsonb,
        "description"  TEXT DEFAULT '',
        "createdAt"    TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        "updatedAt"    TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_apikey_key ON "VocApiKey" ("key")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_apikey_client ON "VocApiKey" ("clientName")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_apikey_active ON "VocApiKey" ("isActive")`);

    // --- VocUsageLog: 调用计费日志表 ---
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "VocUsageLog" (
        "objectId"       VARCHAR(10) PRIMARY KEY,
        "apiKey"         VARCHAR(255) DEFAULT 'anonymous',
        "clientName"     VARCHAR(255) DEFAULT 'unknown',
        "skillName"      VARCHAR(100) DEFAULT '',
        "path"           VARCHAR(500) DEFAULT '',
        "method"         VARCHAR(10) DEFAULT 'POST',
        "requestParams"  JSONB,
        "responseStatus" INTEGER DEFAULT 200,
        "responseTime"   INTEGER DEFAULT 0,
        "dataCount"      INTEGER DEFAULT 0,
        "cost"           INTEGER DEFAULT 1,
        "ip"             VARCHAR(50) DEFAULT '',
        "userAgent"      VARCHAR(500) DEFAULT '',
        "date"           VARCHAR(10) DEFAULT '',
        "success"        BOOLEAN DEFAULT true,
        "createdAt"      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        "updatedAt"      TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_apikey ON "VocUsageLog" ("apiKey")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_client ON "VocUsageLog" ("clientName")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_skill ON "VocUsageLog" ("skillName")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_date ON "VocUsageLog" ("date")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_success ON "VocUsageLog" ("success")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_log_created ON "VocUsageLog" ("createdAt")`);

    // --- VocDailyStat: 按天聚合统计表（可选，加速统计查询） ---
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "VocDailyStat" (
        "objectId"     VARCHAR(10) PRIMARY KEY,
        "apiKey"       VARCHAR(255) NOT NULL,
        "clientName"   VARCHAR(255) DEFAULT 'unknown',
        "skillName"    VARCHAR(100) DEFAULT '',
        "date"         VARCHAR(10) NOT NULL,
        "totalCalls"   INTEGER DEFAULT 0,
        "totalCost"    INTEGER DEFAULT 0,
        "totalData"    INTEGER DEFAULT 0,
        "successCount" INTEGER DEFAULT 0,
        "failCount"    INTEGER DEFAULT 0,
        "avgTime"      FLOAT DEFAULT 0,
        "createdAt"    TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        "updatedAt"    TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        UNIQUE ("apiKey", "skillName", "date")
      )
    `);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_stat_date ON "VocDailyStat" ("date")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_voc_stat_client ON "VocDailyStat" ("clientName")`);

    console.log('✅ [VOC-Init] 建表完成');

    // ==================== 解析参数 ====================
    let action = '';
    const sources = [request.params, request.body, request];
    const getParam = (name) => {
      for (const src of sources) {
        if (src && typeof src === 'object' && src[name] !== undefined) return src[name];
      }
      return '';
    };
    action = getParam('action');

    console.log(`📌 [VOC-Init] action=${action}`);

    // ==================== 调试模式 ====================
    if (action === 'debug') {
      response.json({
        code: 200, success: true,
        debug: {
          requestKeys: Object.keys(request || {}),
          paramsKeys: Object.keys(request.params || {}),
          bodyKeys: Object.keys(request.body || {}),
          params: request.params,
          body: request.body
        }
      });
      return;
    }

    // ==================== init: 仅建表，返回表结构 ====================
    if (!action || action === 'init') {
      const keyCount = await Psql.query(`SELECT COUNT(*) as total FROM "VocApiKey"`);
      const logCount = await Psql.query(`SELECT COUNT(*) as total FROM "VocUsageLog"`);

      response.json({
        code: 200,
        success: true,
        message: 'VOC 表初始化完成',
        tables: {
          VocApiKey: {
            count: parseInt(keyCount[0]?.total || '0'),
            columns: ['objectId', 'key', 'clientName', 'isActive', 'dailyQuota', 'rateLimit', 'permissions', 'description', 'createdAt', 'updatedAt']
          },
          VocUsageLog: {
            count: parseInt(logCount[0]?.total || '0'),
            columns: ['objectId', 'apiKey', 'clientName', 'skillName', 'path', 'method', 'requestParams', 'responseStatus', 'responseTime', 'dataCount', 'cost', 'ip', 'userAgent', 'date', 'success', 'createdAt', 'updatedAt']
          },
          VocDailyStat: {
            columns: ['objectId', 'apiKey', 'clientName', 'skillName', 'date', 'totalCalls', 'totalCost', 'totalData', 'successCount', 'failCount', 'avgTime', 'createdAt', 'updatedAt']
          }
        }
      });
      return;
    }

    // ==================== addKey: 添加 API Key ====================
    if (action === 'addKey') {
      const clientName = getParam('clientName') || 'default';
      const dailyQuota = parseInt(getParam('dailyQuota')) || 10000;
      const rateLimit = parseInt(getParam('rateLimit')) || 60;
      const description = getParam('description') || '';
      let permissions = getParam('permissions');
      if (!permissions) permissions = ['*'];
      if (typeof permissions === 'string') {
        try { permissions = JSON.parse(permissions); } catch(e) { permissions = ['*']; }
      }

      // 生成 API Key: voc_ + 32位随机字符
      const keyChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
      let apiKey = 'voc_';
      for (let i = 0; i < 32; i++) {
        apiKey += keyChars.charAt(Math.floor(Math.random() * keyChars.length));
      }

      const objectId = generateId();

      await Psql.query(
        `INSERT INTO "VocApiKey" ("objectId", "key", "clientName", "isActive", "dailyQuota", "rateLimit", "permissions", "description")
         VALUES ($1, $2, $3, true, $4, $5, $6::jsonb, $7)`,
        [objectId, apiKey, clientName, dailyQuota, rateLimit, JSON.stringify(permissions), description]
      );

      console.log(`✅ [VOC-Init] API Key 已创建: ${clientName} -> ${apiKey}`);

      response.json({
        code: 200,
        success: true,
        message: 'API Key 已创建',
        data: {
          objectId,
          key: apiKey,
          clientName,
          dailyQuota,
          rateLimit,
          permissions,
          description
        }
      });
      return;
    }

    // ==================== listKeys: 列出所有 API Key ====================
    if (action === 'listKeys') {
      const results = await Psql.query(
        `SELECT * FROM "VocApiKey" ORDER BY "createdAt" DESC LIMIT 100`
      );

      response.json({
        code: 200,
        success: true,
        total: results.length,
        data: results.map(r => ({
          objectId: r.objectId,
          key: r.key,
          clientName: r.clientName,
          isActive: r.isActive,
          dailyQuota: r.dailyQuota,
          rateLimit: r.rateLimit,
          permissions: r.permissions,
          description: r.description,
          createdAt: r.createdAt
        }))
      });
      return;
    }

    // ==================== disableKey / enableKey ====================
    if (action === 'disableKey' || action === 'enableKey') {
      const targetKey = getParam('targetKey') || getParam('key');
      if (!targetKey) {
        response.json({ code: 400, success: false, error: '缺少 targetKey 或 key' });
        return;
      }

      const isActive = action === 'enableKey';
      await Psql.query(
        `UPDATE "VocApiKey" SET "isActive" = $1, "updatedAt" = NOW() WHERE "key" = $2`,
        [isActive, targetKey]
      );

      console.log(`🔧 [VOC-Init] API Key ${isActive ? '启用' : '禁用'}: ${targetKey}`);
      response.json({
        code: 200,
        success: true,
        message: `API Key 已${isActive ? '启用' : '禁用'}`
      });
      return;
    }

    // ==================== stats: 用量统计 ====================
    if (action === 'stats') {
      const filterApiKey = getParam('apiKey');
      const filterDate = getParam('date') || new Date().toISOString().slice(0, 10);
      const filterStartDate = getParam('startDate') || filterDate;
      const filterEndDate = getParam('endDate') || filterDate;

      let conditions = [];
      let params = [];
      let paramIdx = 1;

      if (filterApiKey) {
        conditions.push(`"apiKey" = $${paramIdx++}`);
        params.push(filterApiKey);
      }
      conditions.push(`"date" >= $${paramIdx++}`);
      params.push(filterStartDate);
      conditions.push(`"date" <= $${paramIdx++}`);
      params.push(filterEndDate);

      const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

      // 总体统计
      const summary = await Psql.query(
        `SELECT 
           COUNT(*) as "totalCalls",
           COALESCE(SUM("cost"), 0) as "totalCost",
           COALESCE(SUM("dataCount"), 0) as "totalData",
           COALESCE(AVG("responseTime"), 0) as "avgTime",
           COUNT(CASE WHEN "success" = true THEN 1 END) as "successCount",
           COUNT(CASE WHEN "success" = false THEN 1 END) as "failCount"
         FROM "VocUsageLog" ${whereClause}`,
        params
      );

      // 按技能分组
      const bySkill = await Psql.query(
        `SELECT 
           "skillName",
           COUNT(*) as calls,
           COALESCE(SUM("cost"), 0) as cost,
           COALESCE(SUM("dataCount"), 0) as data,
           COALESCE(AVG("responseTime"), 0) as "avgTime"
         FROM "VocUsageLog" ${whereClause}
         GROUP BY "skillName"
         ORDER BY calls DESC`,
        params
      );

      // 最近调用
      const recent = await Psql.query(
        `SELECT * FROM "VocUsageLog" ${whereClause} ORDER BY "createdAt" DESC LIMIT 20`,
        params
      );

      response.json({
        code: 200,
        success: true,
        period: { start: filterStartDate, end: filterEndDate },
        summary: summary[0] || {},
        bySkill: bySkill.map(r => ({
          skillName: r.skillName,
          calls: parseInt(r.calls),
          cost: parseInt(r.cost),
          data: parseInt(r.data),
          avgTime: Math.round(parseFloat(r.avgTime))
        })),
        recent: recent.map(r => ({
          objectId: r.objectId,
          skillName: r.skillName,
          clientName: r.clientName,
          cost: r.cost,
          dataCount: r.dataCount,
          responseTime: r.responseTime,
          success: r.success,
          date: r.date,
          createdAt: r.createdAt
        }))
      });
      return;
    }

    // ==================== 未知 action ====================
    response.json({
      code: 400,
      success: false,
      error: `未知 action: ${action}`,
      availableActions: ['init', 'addKey', 'listKeys', 'disableKey', 'enableKey', 'stats', 'debug']
    });

  } catch (error) {
    console.error('❌ [VOC-Init] 处理失败:', error.message);
    response.json({ code: 500, success: false, error: error.message });
  }
}

function generateId() {
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let result = '';
  for (let i = 0; i < 10; i++) {
    result += chars.charAt(Math.floor(Math.random() * chars.length));
  }
  return result;
}