`scripts/reconcile-dead-images.js`

93 lines
// TK-10390 reconciliation — find (and optionally NULL) rooms.scene_image /
// guides.hero_image values that point to a local file which does NOT exist, so the
// affected pages fall back cleanly instead of emitting a dead <img> src (and a dead
// image in the OG / Article JSON-LD tags). Complementary to the client-side
// graceful-degrade (commit 63069ec): the degrade HIDES broken images in the browser;
// this removes the dead reference server-side (kills the 404 request + fixes OG/SEO).
//
// This is the INTERIM cleanup path. The PROPER fix is regenerating + persisting the
// missing scene PNGs (lib/roomgen, claude-email's auto-room lane) — NOT this script.
//
// SAFE BY DEFAULT: dry-run (read-only) unless --apply is passed. --apply is a GATED
// prod DB write; it snapshots every old value to a timestamped JSON first (reversible)
// and runs inside a single transaction (rollback on any error).
//
//   node scripts/reconcile-dead-images.js            # dry-run: report only ($0 local)
//   node scripts/reconcile-dead-images.js --apply     # GATED: NULL dead paths (snapshot-first)
//
// External (http/https) image URLs are left untouched — only our own local
// /img/... paths are checked against the filesystem.
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const db = require('../lib/db');

const APPLY = process.argv.includes('--apply');
const PUBLIC = path.join(__dirname, '..', 'public');

const isExternal = (v) => /^https?:\/\//i.test(v);
const fileMissing = (v) => !isExternal(v) && !fs.existsSync(path.join(PUBLIC, v.replace(/^\//, '')));

async function scan(table, idCol, imgCol) {
  const { rows } = await db.query(
    `SELECT ${idCol} AS id, ${imgCol} AS img FROM ${table} WHERE ${imgCol} IS NOT NULL`);
  const dead = rows.filter((r) => fileMissing(r.img));
  return { table, idCol, imgCol, total: rows.length, dead };
}

(async () => {
  const targets = [
    await scan('rooms', 'slug', 'scene_image'),
    await scan('guides', 'slug', 'hero_image'),
  ];

  let deadTotal = 0;
  for (const t of targets) {
    deadTotal += t.dead.length;
    console.log(`\n[${t.table}.${t.imgCol}] with-image=${t.total} dead-file=${t.dead.length}`);
    t.dead.slice(0, 12).forEach((r) => console.log(`   ${t.idCol}=${r.id}  img=${r.img}`));
    if (t.dead.length > 12) console.log(`   … +${t.dead.length - 12} more`);
  }

  console.log(`\nTOTAL dead-file image references: ${deadTotal}   cost: $0 (local DB)`);

  if (!deadTotal) { console.log('Nothing to reconcile — clean.'); process.exit(0); }
  if (!APPLY) {
    console.log('\nDRY-RUN (no writes). Re-run with --apply to NULL these (snapshot-first, GATED).');
    process.exit(0);
  }

  // --- GATED apply path: snapshot-first, single transaction ---
  const stamp = new Date().toISOString().replace(/[:.]/g, '-');
  const snapDir = path.join(__dirname, '..', 'tmp');
  fs.mkdirSync(snapDir, { recursive: true });
  const snapFile = path.join(snapDir, `reconcile-dead-images-${stamp}.json`);
  const snapshot = targets.flatMap((t) =>
    t.dead.map((r) => ({ table: t.table, idCol: t.idCol, id: r.id, col: t.imgCol, old: r.img })));
  fs.writeFileSync(snapFile, JSON.stringify(snapshot, null, 2));
  console.log(`\nsnapshot -> ${snapFile} (${snapshot.length} rows) — restore with the SQL printed at the end`);

  const client = await db.pool.connect();
  try {
    await client.query('BEGIN');
    let n = 0;
    for (const t of targets) {
      for (const r of t.dead) {
        const res = await client.query(
          `UPDATE ${t.table} SET ${t.imgCol}=NULL WHERE ${t.idCol}=$1 AND ${t.imgCol}=$2`, [r.id, r.img]);
        n += res.rowCount;
      }
    }
    await client.query('COMMIT');
    console.log(`APPLIED: NULLed ${n} dead image references.`);
    console.log(`Reversible: for each snapshot row -> UPDATE <table> SET <col>='<old>' WHERE <idCol>='<id>';`);
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('ROLLED BACK —', e.message);
    process.exitCode = 1;
  } finally {
    client.release();
  }
  process.exit(process.exitCode || 0);
})().catch((e) => { console.error('ERR', e.message); process.exit(1); });