memind/plaza-ops.mjs

import crypto from 'node:crypto';

const REPORT_REASONS = new Set(['spam', 'violence', 'porn', 'political', 'privacy', 'other']);
const FEATURED_POSITIONS = new Set(['homepage_banner', 'category_top', 'trending']);
const OPS_ROLE_RANK = { none: 0, reviewer: 1, editor: 2, ops_admin: 3 };

function opsError(message, code, details) {
  return Object.assign(new Error(message), { code, details });
}

function clampLimit(value, fallback = 20, max = 100) {
  const limit = Number(value ?? fallback);
  if (!Number.isFinite(limit) || limit < 1) return fallback;
  return Math.min(Math.floor(limit), max);
}

export function hasOpsRole(role, minimum) {
  return (OPS_ROLE_RANK[role] ?? 0) >= (OPS_ROLE_RANK[minimum] ?? 0);
}

export const opsInternals = {
  REPORT_REASONS,
  FEATURED_POSITIONS,
  hasOpsRole,
};

export function createPlazaOpsService(
  pool,
  {
    idFactory = () => crypto.randomUUID(),
    formatPostRow,
    reviewPost,
    invalidateFeedCaches = null,
  } = {},
) {
  const loadOperatorRole = async (userId) => {
    const [rows] = await pool.query(`SELECT ops_role FROM h5_users WHERE id = ? LIMIT 1`, [userId]);
    return rows[0]?.ops_role ?? 'none';
  };

  const writeAuditLog = async (operatorId, action, targetType, targetId, detail) => {
    await pool.query(
      `INSERT INTO ops_audit_log (id, operator_id, action, target_type, target_id, detail, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [idFactory(), operatorId, action, targetType, targetId, JSON.stringify(detail ?? {}), Date.now()],
    );
  };

  const listCategories = async () => {
    const [rows] = await pool.query(
      `SELECT id, name, slug, icon
       FROM plaza_categories
       WHERE is_active = 1
       ORDER BY sort_order ASC, name ASC`,
    );
    return rows.map((row) => ({
      id: row.id,
      name: row.name,
      slug: row.slug,
      icon: row.icon ?? '',
    }));
  };

  const listReviewQueue = async ({
    status = 'pending_review',
    cursor = null,
    limit = 20,
    keyword = null,
    category = null,
  } = {}) => {
    const pageLimit = clampLimit(limit, 20, 100);
    const params = [status];
    let cursorClause = '';
    if (cursor) {
      cursorClause = 'AND pp.published_at < (SELECT published_at FROM plaza_posts WHERE id = ? LIMIT 1)';
      params.push(cursor);
    }
    let keywordClause = '';
    if (keyword) {
      keywordClause = 'AND (pp.title LIKE ? OR pp.user_display_name LIKE ? OR pp.user_slug LIKE ?)';
      const pattern = `%${String(keyword).trim()}%`;
      params.push(pattern, pattern, pattern);
    }
    let categoryClause = '';
    const categorySlug = String(category ?? '').trim();
    if (categorySlug) {
      categoryClause = 'AND c.slug = ?';
      params.push(categorySlug);
    }
    params.push(pageLimit + 1);
    const [rows] = await pool.query(
      `SELECT pp.id, pp.title, pp.summary, pp.cover_url, pp.status, pp.user_display_name, pp.user_slug,
              pp.published_at, pp.created_at, c.name AS category_name, c.slug AS category_slug, c.icon AS category_icon
       FROM plaza_posts pp
       JOIN plaza_categories c ON c.id = pp.category_id
       WHERE pp.status = ? ${cursorClause} ${keywordClause} ${categoryClause}
       ORDER BY pp.published_at DESC, pp.id DESC
       LIMIT ?`,
      params,
    );
    const hasMore = rows.length > pageLimit;
    const pageRows = hasMore ? rows.slice(0, pageLimit) : rows;
    const now = Date.now();
    const posts = pageRows.map((row) => ({
      id: row.id,
      title: row.title,
      summary: row.summary ?? '',
      cover_url: row.cover_url ?? '',
      status: row.status,
      author: { display_name: row.user_display_name, slug: row.user_slug },
      category: { name: row.category_name, slug: row.category_slug, icon: row.category_icon ?? '' },
      published_at: new Date(Number(row.published_at)).toISOString(),
      sla_warning: status === 'pending_review' && now - Number(row.published_at) > 2 * 3_600_000,
    }));
    return {
      posts,
      next_cursor: hasMore ? pageRows[pageRows.length - 1].id : null,
      has_more: hasMore,
    };
  };

  const reviewPostAsOps = async (operatorId, postId, action, { reason = null } = {}) => {
    const [beforeRows] = await pool.query(`SELECT status FROM plaza_posts WHERE id = ? LIMIT 1`, [postId]);
    const before = beforeRows[0];
    if (!before) throw opsError('帖子不存在', 'POST_NOT_FOUND');
    const result = await reviewPost(postId, action, { reason });
    await writeAuditLog(operatorId, `${action}_post`, 'post', postId, {
      before_status: before.status,
      after_status: result.status,
      reason: reason ?? null,
    });
    return result;
  };

  const batchReviewPosts = async (operatorId, { post_ids: postIds, action, reason = null } = {}) => {
    const ids = Array.isArray(postIds) ? postIds.map((id) => String(id).trim()).filter(Boolean) : [];
    if (ids.length === 0) throw opsError('post_ids 不能为空', 'invalid_input');
    if (ids.length > 50) throw opsError('单次最多审核 50 条', 'invalid_input');
    const posts = [];
    for (const postId of ids) {
      posts.push(await reviewPostAsOps(operatorId, postId, action, { reason }));
    }
    return { posts };
  };

  const createReport = async (reporterId, { target_type: targetType, target_id: targetId, reason, detail }) => {
    const type = String(targetType ?? '').trim();
    if (type !== 'post' && type !== 'comment') {
      throw opsError('不支持的举报类型', 'invalid_input');
    }
    const reasonValue = String(reason ?? '').trim();
    if (!REPORT_REASONS.has(reasonValue)) throw opsError('不支持的举报原因', 'invalid_input');
    if (type === 'post') {
      const [rows] = await pool.query(
        `SELECT id FROM plaza_posts WHERE id = ? AND status = 'published' LIMIT 1`,
        [targetId],
      );
      if (!rows[0]) throw opsError('帖子不存在', 'POST_NOT_FOUND');
    } else {
      const [rows] = await pool.query(
        `SELECT id FROM plaza_comments WHERE id = ? AND status = 'visible' LIMIT 1`,
        [targetId],
      );
      if (!rows[0]) throw opsError('评论不存在', 'COMMENT_NOT_FOUND');
    }
    const id = idFactory();
    const now = Date.now();
    await pool.query(
      `INSERT INTO plaza_reports
       (id, target_type, target_id, reporter_id, reason, detail, status, created_at)
       VALUES (?, ?, ?, ?, ?, ?, 'pending', ?)`,
      [id, type, targetId, reporterId, reasonValue, String(detail ?? '').trim().slice(0, 500), now],
    );
    return { id, status: 'pending' };
  };

  const listReports = async ({ status = 'pending', limit = 50 } = {}) => {
    const pageLimit = clampLimit(limit, 50, 100);
    const [rows] = await pool.query(
      `SELECT r.*,
              (SELECT COUNT(*) FROM plaza_reports r2
               WHERE r2.target_type = r.target_type AND r2.target_id = r.target_id AND r2.status = 'pending') AS target_report_count
       FROM plaza_reports r
       WHERE r.status = ?
       ORDER BY r.created_at DESC
       LIMIT ?`,
      [status, pageLimit],
    );
    return {
      reports: rows.map((row) => ({
        id: row.id,
        target_type: row.target_type,
        target_id: row.target_id,
        reason: row.reason,
        detail: row.detail,
        status: row.status,
        target_report_count: Number(row.target_report_count ?? 1),
        created_at: new Date(Number(row.created_at)).toISOString(),
      })),
    };
  };

  const processReport = async (operatorId, reportId, { action, action_taken: actionTaken = '' } = {}) => {
    const [rows] = await pool.query(`SELECT * FROM plaza_reports WHERE id = ? LIMIT 1`, [reportId]);
    const report = rows[0];
    if (!report) throw opsError('举报不存在', 'report_not_found');
    const now = Date.now();
    const status = action === 'dismiss' ? 'dismissed' : 'processed';
    await pool.query(
      `UPDATE plaza_reports
       SET status = ?, processed_by = ?, processed_at = ?, action_taken = ?
       WHERE id = ?`,
      [status, operatorId, now, String(actionTaken).slice(0, 200), reportId],
    );
    if (action === 'hide_post' && report.target_type === 'post') {
      await pool.query(
        `UPDATE plaza_posts SET status = 'hidden', updated_at = ? WHERE id = ?`,
        [now, report.target_id],
      );
      await invalidateFeedCaches?.();
    }
    await writeAuditLog(operatorId, 'process_report', 'report', reportId, {
      action,
      target_type: report.target_type,
      target_id: report.target_id,
    });
    return { id: reportId, status };
  };

  const setFeatured = async (operatorId, { post_id: postId, position, sort_order: sortOrder = 0, expires_at: expiresAt = null }) => {
    const pos = String(position ?? '').trim();
    if (!FEATURED_POSITIONS.has(pos)) throw opsError('不支持的精选位', 'invalid_input');
    const [postRows] = await pool.query(
      `SELECT id FROM plaza_posts WHERE id = ? AND status = 'published' LIMIT 1`,
      [postId],
    );
    if (!postRows[0]) throw opsError('帖子不存在或未发布', 'POST_NOT_FOUND');
    const now = Date.now();
    const id = idFactory();
    const expiresMs = expiresAt ? new Date(expiresAt).getTime() : null;
    await pool.query(
      `INSERT INTO plaza_featured (id, post_id, position, sort_order, starts_at, expires_at, created_by, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [id, postId, pos, Number(sortOrder) || 0, now, expiresMs, operatorId, now],
    );
    await invalidateFeedCaches?.();
    await writeAuditLog(operatorId, 'set_featured', 'featured', id, { post_id: postId, position: pos });
    return { id, post_id: postId, position: pos };
  };

  const listFeatured = async () => {
    const now = Date.now();
    const [rows] = await pool.query(
      `SELECT f.*, pp.title, pp.cover_url, pp.user_display_name
       FROM plaza_featured f
       JOIN plaza_posts pp ON pp.id = f.post_id
       WHERE f.starts_at <= ? AND (f.expires_at IS NULL OR f.expires_at > ?)
       ORDER BY f.position ASC, f.sort_order ASC, f.created_at DESC`,
      [now, now],
    );
    return {
      items: rows.map((row) => ({
        id: row.id,
        post_id: row.post_id,
        position: row.position,
        sort_order: row.sort_order,
        title: row.title,
        cover_url: row.cover_url,
        author: row.user_display_name,
        expires_at: row.expires_at ? new Date(Number(row.expires_at)).toISOString() : null,
      })),
    };
  };

  const removeFeatured = async (operatorId, featuredId) => {
    const [result] = await pool.query(`DELETE FROM plaza_featured WHERE id = ?`, [featuredId]);
    if ((result.affectedRows ?? 0) === 0) throw opsError('精选不存在', 'featured_not_found');
    await invalidateFeedCaches?.();
    await writeAuditLog(operatorId, 'remove_featured', 'featured', featuredId, {});
    return { id: featuredId };
  };

  const loadActiveFeaturedPosts = async (viewerId = null) => {
    const now = Date.now();
    const [rows] = await pool.query(
      `SELECT f.position, f.sort_order, pp.*, c.name AS category_name, c.slug AS category_slug, c.icon AS category_icon
       FROM plaza_featured f
       JOIN plaza_posts pp ON pp.id = f.post_id AND pp.status = 'published'
       JOIN plaza_categories c ON c.id = pp.category_id
       WHERE f.starts_at <= ? AND (f.expires_at IS NULL OR f.expires_at > ?)
       ORDER BY f.position ASC, f.sort_order ASC`,
      [now, now],
    );
    const grouped = { homepage_banner: [], trending: [], category_top: {} };
    for (const row of rows) {
      const post = formatPostRow ? formatPostRow(row, { viewerReacted: null }) : row;
      if (row.position === 'homepage_banner') grouped.homepage_banner.push(post);
      if (row.position === 'trending') grouped.trending.push(post);
      if (row.position === 'category_top') {
        const slug = row.category_slug;
        if (!grouped.category_top[slug]) grouped.category_top[slug] = [];
        grouped.category_top[slug].push(post);
      }
    }
    return grouped;
  };

  const getAnalyticsOverview = async () => {
    const now = Date.now();
    const dayMs = 86_400_000;
    const todayStart = now - (now % dayMs);
    const yesterdayStart = todayStart - dayMs;

    const [[todayPosts]] = await pool.query(
      `SELECT COUNT(*) AS count FROM plaza_posts WHERE status = 'published' AND published_at >= ?`,
      [todayStart],
    );
    const [[yesterdayPosts]] = await pool.query(
      `SELECT COUNT(*) AS count FROM plaza_posts WHERE status = 'published' AND published_at >= ? AND published_at < ?`,
      [yesterdayStart, todayStart],
    );
    const [[pendingReview]] = await pool.query(
      `SELECT COUNT(*) AS count FROM plaza_posts WHERE status = 'pending_review'`,
    );
    const [[signupFromPlaza]] = await pool.query(
      `SELECT COUNT(*) AS count FROM plaza_attribution_events
       WHERE event_type = 'signup' AND utm_source = 'plaza' AND created_at >= ?`,
      [todayStart],
    );
    const [categoryRows] = await pool.query(
      `SELECT c.name, COUNT(p.id) AS count
       FROM plaza_categories c
       LEFT JOIN plaza_posts p ON p.category_id = c.id AND p.status = 'published'
       GROUP BY c.id, c.name
       ORDER BY count DESC`,
    );
    const [topCreators] = await pool.query(
      `SELECT user_slug AS slug, user_display_name AS display_name, COUNT(*) AS post_count,
              SUM(like_count) AS total_likes
       FROM plaza_posts
       WHERE status = 'published'
       GROUP BY user_id, user_slug, user_display_name
       ORDER BY total_likes DESC
       LIMIT 10`,
    );
    const [dailyPosts] = await pool.query(
      `SELECT DATE(FROM_UNIXTIME(published_at / 1000)) AS day, COUNT(*) AS count
       FROM plaza_posts
       WHERE status = 'published' AND published_at >= ?
       GROUP BY day
       ORDER BY day ASC`,
      [now - 14 * dayMs],
    );

    return {
      today: {
        new_posts: Number(todayPosts?.count ?? 0),
        plaza_signups: Number(signupFromPlaza?.count ?? 0),
        pending_review: Number(pendingReview?.count ?? 0),
      },
      yesterday: {
        new_posts: Number(yesterdayPosts?.count ?? 0),
      },
      categories: categoryRows.map((row) => ({
        name: row.name,
        count: Number(row.count ?? 0),
      })),
      top_creators: topCreators.map((row) => ({
        slug: row.slug,
        display_name: row.display_name,
        post_count: Number(row.post_count ?? 0),
        total_likes: Number(row.total_likes ?? 0),
      })),
      daily_posts: dailyPosts.map((row) => ({
        day: row.day,
        count: Number(row.count ?? 0),
      })),
    };
  };

  const listCreators = async ({ keyword = null, limit = 50 } = {}) => {
    const pageLimit = clampLimit(limit, 50, 100);
    const params = [];
    let keywordClause = '';
    if (keyword) {
      keywordClause = 'AND (u.username LIKE ? OR u.display_name LIKE ? OR u.slug LIKE ?)';
      const pattern = `%${String(keyword).trim()}%`;
      params.push(pattern, pattern, pattern);
    }
    params.push(pageLimit);
    const [rows] = await pool.query(
      `SELECT u.id, u.slug, u.username, u.display_name, u.plaza_post_count, u.plaza_follower_count,
              u.plaza_verified, u.plaza_post_banned, u.plaza_comment_banned, u.ops_role
       FROM h5_users u
       WHERE u.plaza_post_count > 0 ${keywordClause}
       ORDER BY u.plaza_post_count DESC
       LIMIT ?`,
      params,
    );
    return {
      creators: rows.map((row) => ({
        user_id: row.id,
        slug: row.slug || row.username,
        display_name: row.display_name || row.username,
        post_count: Number(row.plaza_post_count ?? 0),
        follower_count: Number(row.plaza_follower_count ?? 0),
        verified: Boolean(row.plaza_verified),
        post_banned: Boolean(row.plaza_post_banned),
        comment_banned: Boolean(row.plaza_comment_banned),
      })),
    };
  };

  const updateCreator = async (operatorId, userId, patch) => {
    const updates = [];
    const params = [];
    if (patch?.verified != null) {
      updates.push('plaza_verified = ?');
      params.push(patch.verified ? 1 : 0);
    }
    if (patch?.post_banned != null) {
      updates.push('plaza_post_banned = ?');
      params.push(patch.post_banned ? 1 : 0);
    }
    if (patch?.comment_banned != null) {
      updates.push('plaza_comment_banned = ?');
      params.push(patch.comment_banned ? 1 : 0);
    }
    if (updates.length === 0) throw opsError('没有可更新的字段', 'invalid_input');
    updates.push('updated_at = ?');
    params.push(Date.now(), userId);
    const [result] = await pool.query(
      `UPDATE h5_users SET ${updates.join(', ')} WHERE id = ?`,
      params,
    );
    if ((result.affectedRows ?? 0) === 0) throw opsError('用户不存在', 'user_not_found');
    await writeAuditLog(operatorId, 'update_creator', 'user', userId, patch);
    return { user_id: userId };
  };

  return {
    loadOperatorRole,
    hasOpsRole,
    listCategories,
    listReviewQueue,
    reviewPostAsOps,
    batchReviewPosts,
    createReport,
    listReports,
    processReport,
    setFeatured,
    listFeatured,
    removeFeatured,
    loadActiveFeaturedPosts,
    getAnalyticsOverview,
    listCreators,
    updateCreator,
  };
}