#!/usr/bin/env node
/**
 * 删除 Plaza 演示/种子内容（seed-plaza-demo.mjs 写入的数据），保留真实用户发布。
 * 识别规则：发布 slug 以 demo- 开头，或内容资产 checksum = demo-seed。
 *
 * 用法：node scripts/purge-plaza-demo.mjs [--dry-run]
 */
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { createDbPool } from '../db.mjs';

const root = path.join(path.dirname(fileURLToPath(import.meta.url)), '..');
const dryRun = process.argv.includes('--dry-run');

function loadEnvFile(filePath) {
  if (!fs.existsSync(filePath)) return;
  for (const line of fs.readFileSync(filePath, 'utf8').split('\n')) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const eq = trimmed.indexOf('=');
    if (eq < 0) continue;
    const key = trimmed.slice(0, eq).trim();
    const value = trimmed.slice(eq + 1).trim();
    if (!process.env[key]) process.env[key] = value;
  }
}

loadEnvFile(path.join(root, '../../.env.local'));
loadEnvFile(path.join(root, '.env'));

const pool = createDbPool();
const conn = await pool.getConnection();

try {
  const [rows] = await conn.query(
    `SELECT pp.id AS post_id, pp.title, pp.status AS post_status,
            pr.id AS publication_id, pr.page_id, pr.page_version_id, pr.url_slug, pr.security_scan_id,
            u.username
     FROM plaza_posts pp
     JOIN h5_publish_records pr ON pr.id = pp.publication_id
     JOIN h5_page_versions pv ON pv.id = pr.page_version_id
     JOIN h5_assets a ON a.id = pv.content_asset_id
     JOIN h5_users u ON u.id = pp.user_id
     WHERE pr.url_slug LIKE 'demo-%' OR a.checksum = 'demo-seed'
     ORDER BY pp.published_at DESC`,
  );

  if (rows.length === 0) {
    console.log('未发现演示内容，无需清理。');
    process.exit(0);
  }

  console.log(`${dryRun ? '[dry-run] ' : ''}将删除 ${rows.length} 条演示广场帖：`);
  for (const row of rows) {
    console.log(`  - [${row.username}] ${row.title} (${row.url_slug})`);
  }

  if (dryRun) {
    process.exit(0);
  }

  await conn.beginTransaction();

  const publicationIds = [...new Set(rows.map((row) => row.publication_id))];
  const pageIds = [...new Set(rows.map((row) => row.page_id))];
  const scanIds = [...new Set(rows.map((row) => row.security_scan_id).filter(Boolean))];
  const postIds = rows.map((row) => row.post_id);

  const [postResult] = await conn.query(
    `DELETE FROM plaza_posts WHERE id IN (${postIds.map(() => '?').join(',')})`,
    postIds,
  );

  const [pubResult] = await conn.query(
    `DELETE FROM h5_publish_records WHERE id IN (${publicationIds.map(() => '?').join(',')})`,
    publicationIds,
  );

  if (pageIds.length > 0) {
    await conn.query(
      `UPDATE h5_page_records SET current_publish_id = NULL WHERE id IN (${pageIds.map(() => '?').join(',')})`,
      pageIds,
    );
    await conn.query(
      `DELETE FROM h5_page_records WHERE id IN (${pageIds.map(() => '?').join(',')})`,
      pageIds,
    );
  }

  if (scanIds.length > 0) {
    await conn.query(
      `DELETE FROM h5_security_scans WHERE id IN (${scanIds.map(() => '?').join(',')})`,
      scanIds,
    );
  }

  const [assetResult] = await conn.query(
    `DELETE FROM h5_assets WHERE checksum = 'demo-seed'`,
  );

  await conn.commit();

  const [remaining] = await pool.query(`SELECT COUNT(*) AS cnt FROM plaza_posts`);
  console.log(`\n完成：删除 ${postResult.affectedRows} 条广场帖、${pubResult.affectedRows} 条发布记录、${assetResult.affectedRows} 个演示资产。`);
  console.log(`剩余广场帖：${remaining[0]?.cnt ?? 0} 条。`);
} catch (error) {
  await conn.rollback();
  console.error('清理失败：', error);
  process.exitCode = 1;
} finally {
  conn.release();
  await pool.end();
}