`scripts/resync-images.mjs`

101 lines
// resync-images.mjs — safe, verify-before-write re-sync of stale microsite_products
// image_url for a microsite slug (default naturaltextilewallpaper).
//
// Steve-approved (TK-10371, yoloforever). Canonical dw_unified write on Kamatera.
//
// SAFE by construction:
//   - Only UPDATEs a row when the CURRENT microsite image is genuinely broken (non-200
//     at the raw CDN) AND the shopify_products replacement (same handle) resolves 200
//     AND differs. Never touches a working image (no regressions).
//   - DRY_RUN by default (reads + probes only). Pass --apply to write.
//   - Every applied change (handle, sku, before, after) is written to
//     data/resync-images-<ts>.json for reversibility BEFORE the transaction commits.
//
// Run ON Kamatera from the app dir (peer-auth socket, app's own pg):
//   node scripts/resync-images.mjs            # dry-run report
//   node scripts/resync-images.mjs --apply    # apply the safe subset
import pg from 'pg';
import fs from 'fs';
import path from 'path';

const SLUG = process.env.SLUG || 'naturaltextilewallpaper';
const APPLY = process.argv.includes('--apply');
const CONC = 24;
const TS = new Date().toISOString().replace(/[:.]/g, '-');

const pool = new pg.Pool(process.env.MICROSITE_DB_URL
  ? { connectionString: process.env.MICROSITE_DB_URL }
  : { host: process.env.PGHOST || '/var/run/postgresql', database: 'dw_unified' });

async function resolves(url) {
  if (!url || !/^https?:\/\//.test(url)) return false;
  try {
    const r = await fetch(url, { method: 'GET', redirect: 'follow', signal: AbortSignal.timeout(15000) });
    return r.ok; // 2xx
  } catch { return false; }
}

async function mapLimit(items, limit, fn) {
  const out = new Array(items.length); let i = 0;
  await Promise.all(Array.from({ length: Math.min(limit, items.length) }, async () => {
    while (i < items.length) { const idx = i++; out[idx] = await fn(items[idx], idx); }
  }));
  return out;
}

(async () => {
  const { rows } = await pool.query(
    `SELECT m.sku, m.handle, m.image_url AS micro, s.image_url AS shop
       FROM microsite_products m
       LEFT JOIN shopify_products s ON s.handle = m.handle
      WHERE m.site_slug = $1`, [SLUG]);
  console.log(`[resync] ${SLUG}: ${rows.length} rows loaded`);

  // 1) find rows whose CURRENT microsite image is broken
  const probed = await mapLimit(rows, CONC, async (r) => ({ ...r, microOk: await resolves(r.micro) }));
  const broken = probed.filter(r => !r.microOk);
  console.log(`[resync] broken microsite images: ${broken.length} / ${rows.length}`);

  // 2) of broken, keep those where the shopify replacement RESOLVES and differs
  const withCand = await mapLimit(broken, CONC, async (r) => ({ ...r, shopOk: r.shop && r.shop !== r.micro ? await resolves(r.shop) : false }));
  const fixable = withCand.filter(r => r.shopOk);
  const unfixable = withCand.filter(r => !r.shopOk);
  console.log(`[resync] FIXABLE (broken→working replacement): ${fixable.length}`);
  console.log(`[resync] UNFIXABLE (no working shopify image, needs upstream refresh): ${unfixable.length}`);

  // report a few examples
  for (const r of fixable.slice(0, 5)) console.log(`   FIX  ${r.handle}`);
  for (const r of unfixable.slice(0, 5)) console.log(`   SKIP ${r.handle} (shop also broken/missing)`);

  const outDir = path.join(process.cwd(), 'data');
  const report = {
    ts: TS, slug: SLUG, apply: APPLY, total: rows.length,
    broken: broken.length, fixable: fixable.length, unfixable: unfixable.length,
    changes: fixable.map(r => ({ handle: r.handle, sku: r.sku, before: r.micro, after: r.shop })),
    unfixable_handles: unfixable.map(r => r.handle),
  };
  fs.writeFileSync(path.join(outDir, `resync-images-${TS}.json`), JSON.stringify(report, null, 2));
  console.log(`[resync] report → data/resync-images-${TS}.json`);

  if (!APPLY) { console.log('[resync] DRY-RUN — no writes. Re-run with --apply to write the FIXABLE subset.'); await pool.end(); return; }
  if (!fixable.length) { console.log('[resync] nothing to apply.'); await pool.end(); return; }

  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    let n = 0;
    for (const r of fixable) {
      const res = await client.query(
        `UPDATE microsite_products SET image_url=$1 WHERE site_slug=$2 AND sku=$3 AND image_url=$4`,
        [r.shop, SLUG, r.sku, r.micro]);
      n += res.rowCount;
    }
    await client.query('COMMIT');
    console.log(`[resync] APPLIED ${n} image_url updates (transaction committed).`);
  } catch (e) {
    await client.query('ROLLBACK');
    console.error(`[resync] ROLLBACK — ${e.message}`);
    process.exitCode = 1;
  } finally { client.release(); await pool.end(); }
})();