video-workflow/cloud-functions/05-remixManager.js

/**
 * 云函数：remixManager（视频重塑记录）
 * 替代：GET/POST/DELETE /api/remixes[/:videoId[/:remixId]]
 * actions:
 *   listAll       → 所有视频的重塑汇总 { [videoId]: Remix[] }
 *   listByVideo   → 某视频的所有重塑
 *   upsert        → 创建或更新一条
 *   delete        → 删除一条
 */
async function handler(request, response) {
  try {
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "Remix" (
        "objectId"  VARCHAR(50) PRIMARY KEY,
        "videoId"   VARCHAR(255) NOT NULL,
        "remixId"   VARCHAR(255) NOT NULL,
        "data"      JSONB NOT NULL DEFAULT '{}',
        "userId"    VARCHAR(255) DEFAULT '',
        "createdAt" TIMESTAMPTZ  DEFAULT NOW(),
        "updatedAt" TIMESTAMPTZ  DEFAULT NOW()
      )
    `);
    await Psql.query(`CREATE UNIQUE INDEX IF NOT EXISTS idx_remix_pair ON "Remix" ("videoId","remixId")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_remix_video ON "Remix" ("videoId")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_remix_user ON "Remix" ("userId")`);

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

    if (action === 'listAll') {
      const conds = [], params = [];
      if (userId) { params.push(userId); conds.push(`"userId" = $${params.length}`); }
      const where = conds.length ? `WHERE ${conds.join(' AND ')}` : '';
      const rows = await Psql.query(
        `SELECT * FROM "Remix" ${where} ORDER BY "createdAt" DESC LIMIT 5000`,
        params
      );
      const grouped = {};
      for (const r of rows) {
        if (!grouped[r.videoId]) grouped[r.videoId] = [];
        grouped[r.videoId].push(rowToObj(r));
      }
      response.json({ code: 200, success: true, data: grouped });
      return;
    }

    if (action === 'listByVideo') {
      const videoId = pickParam(request, 'videoId');
      if (!videoId) return response.json({ code: 400, success: false, error: '缺少 videoId' });
      const rows = userId
        ? await Psql.query(`SELECT * FROM "Remix" WHERE "videoId" = $1 AND "userId" = $2 ORDER BY "createdAt" DESC`, [videoId, userId])
        : await Psql.query(`SELECT * FROM "Remix" WHERE "videoId" = $1 ORDER BY "createdAt" DESC`, [videoId]);
      response.json({ code: 200, success: true, data: rows.map(rowToObj) });
      return;
    }

    if (action === 'upsert') {
      const videoId = pickParam(request, 'videoId');
      const body = pickParam(request, 'remix', 'data') || request.body || {};
      const remixId = body.remixId || pickParam(request, 'remixId');
      if (!videoId || !remixId) {
        return response.json({ code: 400, success: false, error: '缺少 videoId 或 remixId' });
      }
      const now = new Date().toISOString();
      const merged = { ...body, remixId, videoId, updated_at: now };
      const exists = userId
        ? await Psql.query(`SELECT "objectId" FROM "Remix" WHERE "videoId"=$1 AND "remixId"=$2 AND "userId"=$3`, [videoId, remixId, userId])
        : await Psql.query(`SELECT "objectId" FROM "Remix" WHERE "videoId"=$1 AND "remixId"=$2`, [videoId, remixId]);
      if (exists.length) {
        await Psql.query(
          userId ? `UPDATE "Remix" SET "data"=$1, "updatedAt"=NOW() WHERE "videoId"=$2 AND "remixId"=$3 AND "userId"=$4` : `UPDATE "Remix" SET "data"=$1, "updatedAt"=NOW() WHERE "videoId"=$2 AND "remixId"=$3`,
          userId ? [JSON.stringify(merged), videoId, remixId, userId] : [JSON.stringify(merged), videoId, remixId]
        );
      } else {
        merged.created_at = now;
        const objectId = generateId();
        await Psql.query(
          `INSERT INTO "Remix" ("objectId","videoId","remixId","data","userId") VALUES ($1,$2,$3,$4,$5)`,
          [objectId, videoId, remixId, JSON.stringify(merged), userId]
        );
      }
      response.json({ code: 200, success: true, data: merged });
      return;
    }

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

    response.json({ code: 400, success: false, error: `未知 action: ${action}` });
  } catch (error) {
    console.error('❌ remixManager 失败:', 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;
}