video-workflow/cloud-functions/04-resultManager.js

/**
 * 云函数：resultManager（结果库）
 * 替代：GET/POST/PUT/DELETE /api/results[/:id]
 * actions: list | get | create | update | delete
 */
async function handler(request, response) {
  try {
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "VideoflowResult" (
        "objectId"  VARCHAR(50) PRIMARY KEY,
        "bizId"     VARCHAR(255),
        "data"      JSONB NOT NULL DEFAULT '{}',
        "userId"    VARCHAR(255) DEFAULT '',
        "type"      VARCHAR(50)  DEFAULT '',
        "status"    VARCHAR(50)  DEFAULT '',
        "createdAt" TIMESTAMPTZ  DEFAULT NOW(),
        "updatedAt" TIMESTAMPTZ  DEFAULT NOW()
      )
    `);
    await Psql.query(`CREATE UNIQUE INDEX IF NOT EXISTS idx_videoflow_result_biz ON "VideoflowResult" ("bizId")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_videoflow_result_user ON "VideoflowResult" ("userId")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_videoflow_result_type ON "VideoflowResult" ("type")`);

    const action = pickParam(request, 'action') || 'list';
    const userId = pickParam(request, 'userId') || '';

    if (action === 'list') {
      const conds = [], params = [];
      if (userId) { params.push(userId); conds.push(`"userId" = $${params.length}`); }
      const type = pickParam(request, 'type');
      if (type) { params.push(type); conds.push(`"type" = $${params.length}`); }
      const status = pickParam(request, 'status');
      if (status) { params.push(status); conds.push(`"status" = $${params.length}`); }
      const limit = Math.min(parseInt(pickParam(request, 'limit') || '500'), 2000);
      const where = conds.length ? `WHERE ${conds.join(' AND ')}` : '';
      params.push(limit);
      const rows = await Psql.query(
        `SELECT * FROM "VideoflowResult" ${where} ORDER BY "createdAt" DESC LIMIT $${params.length}`,
        params
      );
      response.json({ code: 200, success: true, data: rows.map(rowToObj) });
      return;
    }

    if (action === 'get') {
      const id = pickParam(request, 'id', 'bizId');
      if (!id) return response.json({ code: 400, success: false, error: '缺少 id' });
      const rows = userId
        ? await Psql.query(`SELECT * FROM "VideoflowResult" WHERE "bizId" = $1 AND "userId" = $2 LIMIT 1`, [id, userId])
        : await Psql.query(`SELECT * FROM "VideoflowResult" WHERE "bizId" = $1 LIMIT 1`, [id]);
      if (!rows.length) return response.json({ code: 404, success: false, error: '未找到结果' });
      response.json({ code: 200, success: true, data: rowToObj(rows[0]) });
      return;
    }

    if (action === 'create') {
      const body = pickParam(request, 'result', 'data') || request.body || {};
      const bizId = body.id || body.bizId || generateId();
      const merged = { ...body, id: bizId, created_at: body.created_at || new Date().toISOString(), updated_at: new Date().toISOString() };
      const objectId = generateId();
      await Psql.query(
        `INSERT INTO "VideoflowResult" ("objectId","bizId","data","userId","type","status")
         VALUES ($1,$2,$3,$4,$5,$6)
         ON CONFLICT ("bizId") DO UPDATE SET "data"=$3, "type"=$5, "status"=$6, "updatedAt"=NOW()`,
        [objectId, bizId, JSON.stringify(merged), userId, merged.type || '', merged.status || '']
      );
      response.json({ code: 200, success: true, data: merged });
      return;
    }

    if (action === 'update') {
      const id = pickParam(request, 'id', 'bizId');
      if (!id) return response.json({ code: 400, success: false, error: '缺少 id' });
      const patch = pickParam(request, 'result', 'data', 'patch') || request.body || {};
      const cur = userId
        ? await Psql.query(`SELECT * FROM "VideoflowResult" WHERE "bizId" = $1 AND "userId" = $2 LIMIT 1`, [id, userId])
        : await Psql.query(`SELECT * FROM "VideoflowResult" WHERE "bizId" = $1 LIMIT 1`, [id]);
      if (!cur.length) return response.json({ code: 404, success: false, error: '未找到结果' });
      const merged = { ...rowToObj(cur[0]), ...patch, updated_at: new Date().toISOString() };
      await Psql.query(
        userId ? `UPDATE "VideoflowResult" SET "data"=$1, "type"=$2, "status"=$3, "updatedAt"=NOW() WHERE "bizId"=$4 AND "userId"=$5` : `UPDATE "VideoflowResult" SET "data"=$1, "type"=$2, "status"=$3, "updatedAt"=NOW() WHERE "bizId"=$4`,
        userId ? [JSON.stringify(merged), merged.type || '', merged.status || '', id, userId] : [JSON.stringify(merged), merged.type || '', merged.status || '', id]
      );
      response.json({ code: 200, success: true, data: merged });
      return;
    }

    if (action === 'delete') {
      const id = pickParam(request, 'id', 'bizId');
      if (!id) return response.json({ code: 400, success: false, error: '缺少 id' });
      const r = userId
        ? await Psql.query(`DELETE FROM "VideoflowResult" WHERE "bizId" = $1 AND "userId" = $2 RETURNING "bizId"`, [id, userId])
        : await Psql.query(`DELETE FROM "VideoflowResult" WHERE "bizId" = $1 RETURNING "bizId"`, [id]);
      response.json({ code: 200, success: true, data: { id, deleted: r.length > 0 } });
      return;
    }

    response.json({ code: 400, success: false, error: `未知 action: ${action}` });
  } catch (error) {
    console.error('❌ resultManager 失败:', error.message);
    response.json({ code: 500, success: false, error: error.message });
  }
}

function rowToObj(row) {
  const data = typeof row.data === 'string' ? JSON.parse(row.data) : (row.data || {});
  return { ...data, objectId: row.objectId, createdAt: row.createdAt, updatedAt: row.updatedAt };
}

function pickParam(request, ...names) {
  const sources = [request.params, request.body, request];
  for (const src of sources) {
    if (!src || typeof src !== 'object') continue;
    for (const n of names) {
      const v = src[n];
      if (v !== undefined && v !== null && v !== '') return v;
    }
  }
  return null;
}

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