audio_codebuddy/deploy-package/scripts/clean-db-deadlinks.cjs

/**
 * DB 死链清理 (audioUrl 指向 OSS 但文件已删)
 *
 * 策略: 跟 OSS 上实际存在的文件做差集, 找出 DB 里指向死链的 audioUrl 字段
 *
 * 用法:
 *   node clean-db-deadlinks.cjs --dry-run          # 默认, 列出死链不更新
 *   node clean-db-deadlinks.cjs --apply           # 真清 (UPDATE audioUrl='', audioDuration=0)
 *   node clean-db-deadlinks.cjs --apply --out deadlinks.json  # 留档
 */
const fs = require('fs');
const path = require('path');
const envTxt = fs.readFileSync(path.join(__dirname, '..', '..', 'server', '.env.production'), 'utf8');
const env = {};
for (const line of envTxt.split(/\r?\n/)) {
  const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
  if (m) env[m[1]] = m[2].replace(/^["']|["']$/g, '');
}
const serverNM = path.join(__dirname, '..', '..', 'server', 'node_modules');
const OSS = require(path.join(serverNM, 'ali-oss'));
const mysql = require(path.join(serverNM, 'mysql2', 'promise'));

const args = process.argv.slice(2);
const opts = { apply: args.includes('--apply'), dryRun: !args.includes('--apply'), out: null };
for (let i = 0; i < args.length; i++) if (args[i] === '--out') opts.out = args[++i];

// 解析 URL 拿 key, 不管 host
function urlToKey(url) {
  if (!url) return null;
  const s = String(url).trim();
  if (!s || s.startsWith('/')) return null;
  try { return decodeURIComponent(new URL(s).pathname.replace(/^\/+/, '')); } catch { return null; }
}

(async () => {
  console.log(`\n=== DB 死链清理 (${opts.apply ? '🗑 真实更新' : '👀 dry-run'}) ===`);

  // 1. 列 OSS audio/ 所有 key
  const client = new OSS({ accessKeyId: env.OSS_ACCESS_KEY_ID, accessKeySecret: env.OSS_ACCESS_KEY_SECRET, bucket: 'rrbrr-book-audio', endpoint: 'oss-cn-shanghai.aliyuncs.com', secure: true });
  const liveKeys = new Set();
  let marker = null;
  do {
    const r = await client.list({ prefix: 'audio/', 'max-keys': 1000, marker });
    for (const o of (r.objects || [])) liveKeys.add(o.name);
    marker = r.nextMarker || null;
  } while (marker);
  console.log(`OSS 活文件数: ${liveKeys.size}`);

  // 2. 查 DB 所有 OSS audioUrl
  const conn = await mysql.createConnection({ uri: env.DATABASE_URL });
  const [bcRows] = await conn.query(`SELECT id, audioUrl, audioDuration FROM BookChapter WHERE audioUrl LIKE '%aliyuncs.com%'`);
  const [arRows] = await conn.query(`SELECT audioId, audioUrl, audioDuration FROM AudioRecord WHERE status='completed' AND audioUrl IS NOT NULL AND audioUrl<>'' AND audioUrl LIKE '%aliyuncs.com%'`);
  await conn.end();
  console.log(`DB  BookChapter 有 OSS URL: ${bcRows.length}`);
  console.log(`DB  AudioRecord 有 OSS URL:  ${arRows.length}`);

  // 3. 找死链
  const deadBC = [];
  for (const r of bcRows) {
    const k = urlToKey(r.audioUrl);
    if (k && !liveKeys.has(k)) deadBC.push(r);
  }
  const deadAR = [];
  for (const r of arRows) {
    const k = urlToKey(r.audioUrl);
    if (k && !liveKeys.has(k)) deadAR.push(r);
  }
  console.log(`\n  BookChapter 死链: ${deadBC.length}`);
  console.log(`  AudioRecord  死链: ${deadAR.length}`);
  console.log(`  总计:          ${deadBC.length + deadAR.length}`);

  if (deadBC.length) {
    console.log(`\n  BookChapter 死链前 5:`);
    for (const r of deadBC.slice(0, 5)) console.log(`    id=${r.id}  ${r.audioUrl}`);
  }
  if (deadAR.length) {
    console.log(`\n  AudioRecord 死链前 5:`);
    for (const r of deadAR.slice(0, 5)) console.log(`    audioId=${r.audioId}  ${r.audioUrl}`);
  }

  if (opts.out) {
    fs.writeFileSync(opts.out, JSON.stringify({ scannedAt: new Date().toISOString(), deadBookChapter: deadBC, deadAudioRecord: deadAR }, null, 2));
    console.log(`\n  📄 死链清单已写到 ${opts.out}`);
  }

  if (opts.dryRun) {
    console.log(`\n👀 DRY-RUN 模式, 没动任何数据`);
    console.log(`   确认后用 --apply 真清:`);
    console.log(`   UPDATE BookChapter SET audioUrl='', audioDuration=0 WHERE id IN (${deadBC.map(r=>r.id).join(',')})`);
    console.log(`   UPDATE AudioRecord SET audioUrl='', audioDuration=0 WHERE audioId IN (${deadAR.map(r=>`"${r.audioId}"`).join(',')})`);
    return;
  }

  // 4. 真清
  const conn2 = await mysql.createConnection({ uri: env.DATABASE_URL });
  let updatedBC = 0, updatedAR = 0;
  if (deadBC.length) {
    const ids = deadBC.map(r => r.id);
    // 一次最多 1000 个
    for (let i = 0; i < ids.length; i += 1000) {
      const batch = ids.slice(i, i + 1000);
      const [r] = await conn2.query(`UPDATE BookChapter SET audioUrl='', audioDuration=0 WHERE id IN (?)`, [batch]);
      updatedBC += r.affectedRows;
    }
  }
  if (deadAR.length) {
    const ids = deadAR.map(r => r.audioId);
    for (let i = 0; i < ids.length; i += 1000) {
      const batch = ids.slice(i, i + 1000);
      const [r] = await conn2.query(`UPDATE AudioRecord SET audioUrl='', audioDuration=0 WHERE audioId IN (?)`, [batch]);
      updatedAR += r.affectedRows;
    }
  }
  await conn2.end();
  console.log(`\n✅ BookChapter 更新: ${updatedBC} 行`);
  console.log(`✅ AudioRecord  更新: ${updatedAR} 行`);
})().catch(e => { console.error('异常:', e); process.exit(1); });