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

/**
 * 云函数：resultManager（结果库）
 * 替代：GET/POST/PUT/DELETE /api/results[/:id]
 * 支持 action：list | get | create | update | delete
 */
const { requireSession, assertRequestedUserMatchesSession } = require('./_session');

async function handler(request, response) {
  try {
    await ensureTable();

    const action = pickParam(request, 'action') || 'list';
    const session = await requireSession(request, Psql);
    assertRequestedUserMatchesSession(request, session);
    const userId = session.userId;

    if (action === 'list') return listResults(request, response, userId);
    if (action === 'get') return getResult(request, response, userId);
    if (action === 'create') return upsertResult(request, response, userId, 'create');
    if (action === 'update') return upsertResult(request, response, userId, 'update');
    if (action === 'delete') return deleteResult(request, response, userId);

    return response.json({ code: 400, success: false, error: `未知 action: ${action}` });
  } catch (error) {
    console.error('resultManager failed:', error && error.message);
    return response.json({ code: error.status || 500, success: false, error: error.message });
  }
}

async function ensureTable() {
  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(`DROP INDEX IF EXISTS idx_videoflow_result_biz`);
  await Psql.query(`CREATE UNIQUE INDEX IF NOT EXISTS idx_videoflow_result_user_biz ON "VideoflowResult" ("userId", "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")`);
}

async function listResults(request, response, userId) {
  const conds = ['"userId" = $1'];
  const params = [userId];
  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 = safeLimit(pickParam(request, 'limit'), 500, 2000);
  params.push(limit);
  const rows = await Psql.query(
    `SELECT * FROM "VideoflowResult" WHERE ${conds.join(' AND ')} ORDER BY "createdAt" DESC LIMIT $${params.length}`,
    params
  );
  return response.json({ code: 200, success: true, data: rows.map(rowToObj) });
}

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

async function upsertResult(request, response, userId, mode) {
  const body = pickParam(request, 'result', 'data', 'patch') || request.body || {};
  const requestedId = pickParam(request, 'id', 'bizId');
  const bizId = body.id || body.bizId || requestedId || generateId();
  const now = new Date().toISOString();
  const existing = await Psql.query(`SELECT * FROM "VideoflowResult" WHERE "bizId" = $1 AND "userId" = $2 LIMIT 1`, [bizId, userId]);
  const previous = existing.length ? rowToObj(existing[0]) : {};
  if (mode === 'update' && requestedId && requestedId !== bizId && existing.length) {
    return response.json({ code: 400, success: false, error: '结果 id 不一致' });
  }
  const merged = {
    ...previous,
    ...body,
    id: bizId,
    bizId,
    userId,
    created_at: previous.created_at || body.created_at || now,
    updated_at: now,
  };

  await Psql.query(
    `INSERT INTO "VideoflowResult" ("objectId","bizId","data","userId","type","status")
     VALUES ($1,$2,$3,$4,$5,$6)
     ON CONFLICT ("userId","bizId") DO UPDATE SET "data"=$3, "type"=$5, "status"=$6, "updatedAt"=NOW()`,
    [existing[0]?.objectId || generateId(), bizId, JSON.stringify(merged), userId, merged.type || '', merged.status || '']
  );
  return response.json({ code: 200, success: true, data: merged });
}

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

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.query, request];
  for (const src of sources) {
    if (!src || typeof src !== 'object') continue;
    for (const name of names) {
      const value = src[name];
      if (value !== undefined && value !== null && value !== '') return value;
    }
  }
  return null;
}

function safeLimit(value, fallback, max) {
  const parsed = parseInt(value || fallback, 10);
  return Number.isFinite(parsed) ? Math.min(Math.max(parsed, 1), max) : fallback;
}

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