multi-platform-media-manage/server/src/scripts/clean-work-day-statistics-orphans.ts

/**
 * 清理 work_day_statistics 表中的孤儿数据（work_id 在 works 表中不存在的记录），
 * 执行后可运行 database/migrations/clean_work_day_statistics_orphans_and_add_fk.sql 添加外键。
 */
import { initDatabase, AppDataSource } from '../models/index.js';
import { logger } from '../utils/logger.js';

async function main() {
  try {
    await initDatabase();
    const qr = AppDataSource.createQueryRunner();
    await qr.connect();

    try {
      // 统计孤儿数据
      const countResult = await qr.query(
        `SELECT COUNT(*) AS cnt FROM work_day_statistics wds
         LEFT JOIN works w ON wds.work_id = w.id
         WHERE w.id IS NULL`
      );
      const orphanCount = Number(countResult?.[0]?.cnt ?? 0);

      if (orphanCount === 0) {
        logger.info('[CleanOrphans] work_day_statistics 中无孤儿数据。');
      } else {
        logger.info(`[CleanOrphans] 发现 ${orphanCount} 条孤儿数据，正在删除...`);
        const deleteResult = await qr.query(
          `DELETE wds FROM work_day_statistics wds
           LEFT JOIN works w ON wds.work_id = w.id
           WHERE w.id IS NULL`
        );
        const affected = (deleteResult as { affectedRows?: number })?.affectedRows ?? orphanCount;
        logger.info(`[CleanOrphans] 已删除 ${affected} 条孤儿数据。`);
      }

      // 提示添加外键
      logger.info(
        '[CleanOrphans] 若需为 work_day_statistics.work_id 添加外键（删除 works 时级联删除），请执行：database/migrations/clean_work_day_statistics_orphans_and_add_fk.sql'
      );
    } finally {
      await qr.release();
    }

    process.exit(0);
  } catch (e) {
    logger.error('[CleanOrphans] 执行失败:', e);
    process.exit(1);
  }
}

void main();