yourpart3/backend/services/moderationService.js

import BaseService from './BaseService.js';
import { sequelize } from '../utils/sequelize.js';
import { notifyUser } from '../utils/socket.js';

const ALLOWED_TARGET_TYPES = new Set([
  'forum_message',
  'gallery_image',
  'guestbook_entry',
  'one_to_one_message',
  'diary_entry',
  'user_profile',
  'blog',
  'blog_post'
]);
const ALLOWED_STATUS = new Set(['open', 'in_review', 'resolved', 'rejected']);

class ModerationService extends BaseService {
  constructor() {
    super();
    this._tableEnsured = false;
  }

  async _ensureTable() {
    if (this._tableEnsured) return;
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS community.moderation_report (
        id bigserial PRIMARY KEY,
        target_type varchar(64) NOT NULL,
        target_id bigint NULL,
        target_ref varchar(255) NULL,
        reason varchar(120) NOT NULL,
        details text NULL,
        status varchar(32) NOT NULL DEFAULT 'open',
        reporter_user_id bigint NOT NULL REFERENCES community.user(id) ON DELETE CASCADE,
        reviewer_user_id bigint NULL REFERENCES community.user(id) ON DELETE SET NULL,
        reviewer_note text NULL,
        created_at timestamptz NOT NULL DEFAULT NOW(),
        updated_at timestamptz NOT NULL DEFAULT NOW()
      );
    `);
    await sequelize.query(`
      CREATE INDEX IF NOT EXISTS moderation_report_target_idx
      ON community.moderation_report (target_type, target_id);
    `);
    await sequelize.query(`
      ALTER TABLE community.moderation_report
      ADD COLUMN IF NOT EXISTS target_ref varchar(255) NULL;
    `);
    await sequelize.query(`
      ALTER TABLE community.moderation_report
      ALTER COLUMN target_id DROP NOT NULL;
    `);
    await sequelize.query(`
      CREATE INDEX IF NOT EXISTS moderation_report_status_idx
      ON community.moderation_report (status, created_at DESC);
    `);
    this._tableEnsured = true;
  }

  async createReport(hashedUserId, { targetType, targetId, targetRef, reason, details }) {
    await this._ensureTable();
    const user = await this.getUserByHashedId(hashedUserId);
    const normalizedTargetType = String(targetType || '').trim();
    if (!ALLOWED_TARGET_TYPES.has(normalizedTargetType)) {
      throw new Error('Unsupported target type');
    }
    const numericTargetId = Number(targetId);
    const hasTargetId = Number.isFinite(numericTargetId) && numericTargetId >= 1;
    const normalizedTargetRef = String(targetRef || '').trim().slice(0, 255);
    if (!hasTargetId && !normalizedTargetRef) {
      throw new Error('Invalid target');
    }
    const normalizedReason = String(reason || '').trim().slice(0, 120);
    if (!normalizedReason) {
      throw new Error('Missing reason');
    }
    const normalizedDetails = String(details || '').trim().slice(0, 2000);

    const rows = await sequelize.query(
      `
      INSERT INTO community.moderation_report
        (target_type, target_id, target_ref, reason, details, status, reporter_user_id)
      VALUES
        (:targetType, :targetId, :targetRef, :reason, :details, 'open', :reporterUserId)
      RETURNING id, target_type AS "targetType", target_id AS "targetId", target_ref AS "targetRef", reason, details, status, created_at AS "createdAt"
      `,
      {
        replacements: {
          targetType: normalizedTargetType,
          targetId: hasTargetId ? numericTargetId : null,
          targetRef: normalizedTargetRef || null,
          reason: normalizedReason,
          details: normalizedDetails || null,
          reporterUserId: user.id
        },
        type: sequelize.QueryTypes.SELECT
      }
    );

    const created = rows[0];
    await this._notifyModeratorsAboutChange('created', created.id);
    return created;
  }

  async listReports(hashedUserId, { status = 'open', limit = 50 } = {}) {
    await this._ensureTable();
    const user = await this.getUserByHashedId(hashedUserId);
    const isAdmin = await this.hasUserRight(user.id, ['mainadmin', 'forum']);
    if (!isAdmin) {
      throw new Error('Access denied');
    }
    const normalizedStatus = String(status || 'open').trim();
    const effectiveStatus = ALLOWED_STATUS.has(normalizedStatus) ? normalizedStatus : 'open';
    const effectiveLimit = Math.min(Math.max(Number(limit) || 50, 1), 200);

    return sequelize.query(
      `
      SELECT
        r.id,
        r.target_type AS "targetType",
        r.target_id AS "targetId",
        r.target_ref AS "targetRef",
        fm.title_id AS "topicId",
        ft.forum_id AS "forumId",
        r.reason,
        r.details,
        r.status,
        r.created_at AS "createdAt",
        r.updated_at AS "updatedAt",
        reporter.username AS "reporterUsername",
        reviewer.username AS "reviewerUsername",
        r.reviewer_note AS "reviewerNote"
      FROM community.moderation_report r
      JOIN community.user reporter ON reporter.id = r.reporter_user_id
      LEFT JOIN community.user reviewer ON reviewer.id = r.reviewer_user_id
      LEFT JOIN forum.message fm
        ON r.target_type = 'forum_message'
       AND fm.id = r.target_id
      LEFT JOIN forum.title ft ON ft.id = fm.title_id
      WHERE r.status = :status
      ORDER BY r.created_at DESC
      LIMIT :limit
      `,
      {
        replacements: {
          status: effectiveStatus,
          limit: effectiveLimit
        },
        type: sequelize.QueryTypes.SELECT
      }
    );
  }

  async updateReportStatus(hashedUserId, reportId, { status, reviewerNote } = {}) {
    await this._ensureTable();
    const user = await this.getUserByHashedId(hashedUserId);
    const isAdmin = await this.hasUserRight(user.id, ['mainadmin', 'forum']);
    if (!isAdmin) {
      throw new Error('Access denied');
    }
    const numericReportId = Number(reportId);
    if (!Number.isFinite(numericReportId) || numericReportId < 1) {
      throw new Error('Invalid report id');
    }
    const normalizedStatus = String(status || '').trim();
    if (!ALLOWED_STATUS.has(normalizedStatus)) {
      throw new Error('Invalid status');
    }
    const note = String(reviewerNote || '').trim().slice(0, 2000);

    const rows = await sequelize.query(
      `
      UPDATE community.moderation_report
      SET
        status = :status,
        reviewer_user_id = :reviewerUserId,
        reviewer_note = :reviewerNote,
        updated_at = NOW()
      WHERE id = :reportId
      RETURNING id, status, reviewer_note AS "reviewerNote", updated_at AS "updatedAt"
      `,
      {
        replacements: {
          status: normalizedStatus,
          reviewerUserId: user.id,
          reviewerNote: note || null,
          reportId: numericReportId
        },
        type: sequelize.QueryTypes.SELECT
      }
    );
    if (!rows.length) {
      throw new Error('Report not found');
    }
    const updated = rows[0];
    await this._notifyModeratorsAboutChange('status_changed', numericReportId);
    return updated;
  }

  async getOpenReportCount(hashedUserId) {
    await this._ensureTable();
    const user = await this.getUserByHashedId(hashedUserId);
    const isAdmin = await this.hasUserRight(user.id, ['mainadmin', 'forum']);
    if (!isAdmin) {
      throw new Error('Access denied');
    }
    const rows = await sequelize.query(
      `
      SELECT COUNT(*)::int AS count
      FROM community.moderation_report
      WHERE status = 'open'
      `,
      { type: sequelize.QueryTypes.SELECT }
    );
    return { openCount: Number(rows?.[0]?.count || 0) };
  }

  async _notifyModeratorsAboutChange(kind, reportId) {
    try {
      const moderatorRows = await sequelize.query(
        `
        SELECT DISTINCT u.hashed_id AS "hashedId"
        FROM community.user u
        JOIN community.user_right ur ON ur.user_id = u.id
        JOIN type.user_right urt ON urt.id = ur.right_type_id
        WHERE u.active = true
          AND urt.title IN ('mainadmin', 'forum')
        `,
        { type: sequelize.QueryTypes.SELECT }
      );
      const countRows = await sequelize.query(
        `
        SELECT COUNT(*)::int AS count
        FROM community.moderation_report
        WHERE status = 'open'
        `,
        { type: sequelize.QueryTypes.SELECT }
      );
      const openCount = Number(countRows?.[0]?.count || 0);
      await Promise.all(
        (moderatorRows || [])
          .map((row) => row?.hashedId)
          .filter(Boolean)
          .map((hashedId) =>
            notifyUser(hashedId, 'moderationReportChanged', {
              kind,
              reportId,
              openCount
            })
          )
      );
    } catch (error) {
      console.error('Failed to notify moderators about moderation report change:', error);
    }
  }
}

export default new ModerationService();