video-workflow/cloud-functions/13-douyinInsightManager.js

/**
 * 云函数：douyinInsightManager（二阶段：爆款分析、选题池、日报）
 * actions:
 *   analysisCreate | analysisList | analysisGet | analysisUpdate
 *   topicCreate | topicList | topicUpdate | topicArchive
 *   dailyReportCreate | dailyReportList | dailyReportGet
 *
 * 说明：本函数只负责业务资产的持久化和账号隔离。抖音原始数据抓取仍由 12-douyinManager 负责。
 */
const PARSE_API_HOST = readEnv('PARSE_API_HOST') || 'https://server.fmode.cn';
const PARSE_APP_ID = readEnv('PARSE_APP_ID') || 'ncloudmaster';

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

    const action = pickParam(request, 'action') || '';
    const session = await requireSession(request);
    const requestedUserId = pickParam(request, 'userId') || '';
    if (requestedUserId && requestedUserId !== session.userId) {
      return response.json({ code: 403, success: false, error: '没有访问该账号数据的权限' });
    }
    const userId = session.userId;

    if (action === 'analysisCreate') return createRow(response, 'VideoflowViralAnalysis', userId, pickParam(request, 'analysis', 'data') || {});
    if (action === 'analysisList') return listRows(response, 'VideoflowViralAnalysis', userId, pickParam(request, 'limit') || 100);
    if (action === 'analysisGet') return getRow(response, 'VideoflowViralAnalysis', userId, pickParam(request, 'id'));
    if (action === 'analysisUpdate') return updateRow(response, 'VideoflowViralAnalysis', userId, pickParam(request, 'id'), pickParam(request, 'patch', 'analysis', 'data') || {});

    if (action === 'topicCreate') return createRow(response, 'VideoflowTopicIdea', userId, pickParam(request, 'topic', 'data') || {});
    if (action === 'topicList') return listRows(response, 'VideoflowTopicIdea', userId, pickParam(request, 'limit') || 500);
    if (action === 'topicUpdate') return updateRow(response, 'VideoflowTopicIdea', userId, pickParam(request, 'id'), pickParam(request, 'patch', 'topic', 'data') || {});
    if (action === 'topicArchive') return updateRow(response, 'VideoflowTopicIdea', userId, pickParam(request, 'id'), { status: 'archived' });

    if (action === 'dailyReportCreate') return createRow(response, 'VideoflowDailyReport', userId, pickParam(request, 'report', 'data') || {});
    if (action === 'dailyReportList') return listRows(response, 'VideoflowDailyReport', userId, pickParam(request, 'limit') || 100);
    if (action === 'dailyReportGet') return getRow(response, 'VideoflowDailyReport', userId, pickParam(request, 'id'));

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

async function ensureTables() {
  for (const table of ['VideoflowViralAnalysis', 'VideoflowTopicIdea', 'VideoflowDailyReport']) {
    await Psql.query(`
      CREATE TABLE IF NOT EXISTS "${table}" (
        "objectId"  VARCHAR(50) PRIMARY KEY,
        "bizId"     VARCHAR(255) NOT NULL,
        "userId"    VARCHAR(255) NOT NULL,
        "data"      JSONB NOT NULL DEFAULT '{}',
        "status"    VARCHAR(50) DEFAULT '',
        "createdAt" TIMESTAMPTZ DEFAULT NOW(),
        "updatedAt" TIMESTAMPTZ DEFAULT NOW()
      )
    `);
    await Psql.query(`DROP INDEX IF EXISTS idx_${table.toLowerCase()}_biz`);
    await Psql.query(`CREATE UNIQUE INDEX IF NOT EXISTS idx_${table.toLowerCase()}_user_biz ON "${table}" ("userId", "bizId")`);
    await Psql.query(`CREATE INDEX IF NOT EXISTS idx_${table.toLowerCase()}_user ON "${table}" ("userId")`);
  }
  await Psql.query(`
    CREATE TABLE IF NOT EXISTS "AppSession" (
      "token" VARCHAR(120) PRIMARY KEY,
      "userId" VARCHAR(50) NOT NULL,
      "expiresAt" TIMESTAMPTZ NOT NULL,
      "createdAt" TIMESTAMPTZ DEFAULT NOW()
    )
  `);
}

async function createRow(response, table, userId, data) {
  const now = new Date().toISOString();
  const bizId = data.id || data.bizId || generateId();
  const merged = { ...data, id: bizId, userId, createdAt: data.createdAt || now, updatedAt: now };
  const existing = await Psql.query(
    `SELECT * FROM "${table}" WHERE "bizId"=$1 AND "userId"=$2 LIMIT 1`,
    [bizId, userId]
  );
  if (existing.length) {
    await Psql.query(
      `UPDATE "${table}" SET "data"=$1, "status"=$2, "updatedAt"=NOW() WHERE "bizId"=$3 AND "userId"=$4`,
      [JSON.stringify(merged), merged.status || '', bizId, userId]
    );
    return response.json({ code: 200, success: true, data: merged });
  }

  await Psql.query(
    `INSERT INTO "${table}" ("objectId","bizId","userId","data","status")
     VALUES ($1,$2,$3,$4,$5)`,
    [generateId(), bizId, userId, JSON.stringify(merged), merged.status || '']
  );
  response.json({ code: 200, success: true, data: merged });
}

async function listRows(response, table, userId, limit) {
  const parsedLimit = parseInt(limit || '100', 10);
  const safeLimit = Number.isFinite(parsedLimit) ? Math.min(Math.max(parsedLimit, 1), 1000) : 100;
  const rows = await Psql.query(
    `SELECT * FROM "${table}" WHERE "userId"=$1 ORDER BY "updatedAt" DESC LIMIT $2`,
    [userId, safeLimit]
  );
  response.json({ code: 200, success: true, data: rows.map(rowToObj) });
}

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

async function updateRow(response, table, userId, id, patch) {
  if (!id) return response.json({ code: 400, success: false, error: '缺少 id' });
  const rows = await Psql.query(
    `SELECT * FROM "${table}" WHERE "bizId"=$1 AND "userId"=$2 LIMIT 1`,
    [id, userId]
  );
  if (!rows.length) return response.json({ code: 404, success: false, error: '未找到记录' });
  const merged = { ...rowToObj(rows[0]), ...patch, id, userId, updatedAt: new Date().toISOString() };
  await Psql.query(
    `UPDATE "${table}" SET "data"=$1, "status"=$2, "updatedAt"=NOW() WHERE "bizId"=$3 AND "userId"=$4`,
    [JSON.stringify(merged), merged.status || '', id, userId]
  );
  response.json({ code: 200, success: true, data: merged });
}

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 name of names) {
      const value = src[name];
      if (value !== undefined && value !== null && value !== '') return value;
    }
  }
  return null;
}

async function requireSession(request) {
  const token = clean(pickParam(request, 'sessionToken'));
  if (!token) throw new Error('请先登录');
  const rows = await Psql.query(
    `SELECT * FROM "AppSession" WHERE "token"=$1 AND "expiresAt" > NOW() LIMIT 1`,
    [token]
  );
  if (rows.length) return rows[0];

  const parseUser = await verifyParseSession(token);
  if (parseUser?.objectId) return { token, userId: parseUser.objectId, source: 'parse' };
  throw new Error('登录已过期，请重新登录');
}

async function verifyParseSession(sessionToken) {
  if (typeof fetch !== 'function') return null;
  const resp = await fetch(`${PARSE_API_HOST}/parse/users/me?include=company`, {
    method: 'GET',
    headers: {
      'X-Parse-Application-Id': PARSE_APP_ID,
      'X-Parse-Session-Token': sessionToken,
    },
  });
  const data = await resp.json().catch(() => ({}));
  return resp.ok && data.objectId ? data : null;
}

function clean(value) {
  return String(value || '').trim();
}

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;
}

function readEnv(name) {
  if (typeof process !== 'undefined' && process.env && process.env[name]) {
    return process.env[name];
  }
  return '';
}