`scripts/recover-versa.mjs`

161 lines
#!/usr/bin/env node
// recover-versa.mjs — TK-11048 feed-first ($0) image recovery for the Versa
// (versadesignedsurfaces.com) dead-image rows in versa_catalog.
//
// Root cause of the dead rows: the original scraper captured a base64 data-URI
// lazy-load PLACEHOLDER and prepended the domain, e.g.
//   https://www.versadesignedsurfaces.com/data:image/jpeg;base64,....
// The REAL image URLs are present in the STATIC product-page HTML under srcset /
// /fileadmin/products/.../Thumbnails/ — the site is live and current, so a plain
// fetch of each product_url recovers a fresh, live image URL. NO browser, NO
// Gemini, NO paid API — $0 (local + public GET).
//
// SAFETY: DEFAULT = DRY-RUN. Writes NOTHING to the DB. Produces:
//   data/versa-recovery-map.jsonl   — {mfr_sku, new_image_url, verified}
//   data/versa-restore-map.jsonl    — {mfr_sku, old_image_url, old_all_images}
//   a printed recovery-rate summary
// Only with --apply (GATED — do not run without Steve's go) does it UPDATE
// versa_catalog.image_url. The restore map is the reversibility record.
//
// Every new URL is LIVENESS-VERIFIED (200 + Content-Type image/*) before it is
// counted recovered or written — the whole bug class is trusting a 200 that is
// really a 404-HTML page, so status alone is never enough.
//
// Usage:
//   node recover-versa.mjs                 # dry-run, ALL dead versa rows
//   node recover-versa.mjs --limit 60      # dry-run, bounded sample
//   node recover-versa.mjs --apply         # GATED write (Steve-approved only)
import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync, appendFileSync, readFileSync } from 'node:fs';
import { fileURLToPath, pathToFileURL } from 'node:url';
import { dirname, join } from 'node:path';

const HERE = dirname(fileURLToPath(import.meta.url));
const DATA = join(HERE, '..', 'data');
mkdirSync(DATA, { recursive: true });
const APPLY = process.argv.includes('--apply');
const LIMIT = (() => { const i = process.argv.indexOf('--limit'); return i > -1 ? Number(process.argv[i + 1]) : 0; })();
const HOST = 'https://www.versadesignedsurfaces.com';
const UA = { 'User-Agent': 'Mozilla/5.0 (dw-image-recovery)' };
const TIMEOUT = 25000;

// CRASH FIX (TK-11048): versa dead rows store a base64 data-URI in image_url
// (~5-27 KB each); the full-set SELECT returns ~2.9 MB, which OVERFLOWS
// execFileSync's DEFAULT 1 MB maxBuffer and the process is SIGTERM-killed.
// (--limit 60 stayed under 1 MB, which is why the bounded dry-run passed but
// the unbounded --apply crashed.) Raise maxBuffer generously.
const MAXBUF = 512 * 1024 * 1024;
function psql(sql) {
  return execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-Atc', sql],
    { encoding: 'utf8', timeout: 120000, maxBuffer: MAXBUF }).trim();
}
export const isDataUri = (u) => /^\s*data:|;base64,/i.test(String(u || ''));
async function withTimeout(fn) {
  const ac = new AbortController(); const t = setTimeout(() => ac.abort(), TIMEOUT);
  try { return await fn(ac.signal); } finally { clearTimeout(t); }
}
async function getText(url) {
  const res = await withTimeout((s) => fetch(url, { redirect: 'follow', signal: s, headers: UA }));
  if (!res.ok) return null;
  return res.text();
}
// verify a candidate is a real, live image
export async function isLiveImage(url) {
  try {
    let res = await withTimeout((s) => fetch(url, { method: 'HEAD', redirect: 'follow', signal: s, headers: UA }));
    let ct = (res.headers.get('content-type') || '').toLowerCase();
    if (res.ok && ct.startsWith('image/')) return true;
    if (res.ok && !ct) {
      res = await withTimeout((s) => fetch(url, { redirect: 'follow', signal: s, headers: { ...UA, Range: 'bytes=0-0' } }));
      ct = (res.headers.get('content-type') || '').toLowerCase();
      try { await res.arrayBuffer(); } catch {}
      return res.ok && ct.startsWith('image/');
    }
    return false;
  } catch { return false; }
}
// Extract the best real image URL for a given SKU from the product-page HTML.
// Strategy: prefer a processed (larger) srcset entry whose filename contains the
// SKU core (AVFnn-nnn); else the /Thumbnails/ path; else the largest srcset img.
export function extractImage(html, sku) {
  const core = (sku.match(/^[A-Za-z]+\d+-\d+/) || [sku])[0]; // AVF25-834
  const abs = (p) => (p.startsWith('http') ? p : HOST + p);
  const allImgs = [...html.matchAll(/\/fileadmin\/[^"'\s]+?\.(?:jpg|jpeg|png|webp)/gi)].map((m) => m[0]);
  const matches = allImgs.filter((u) => u.toLowerCase().includes(core.toLowerCase()));
  const pool = matches.length ? matches : [];
  // prefer processed (bigger) then thumbnails, avoid tiny 80w duplicates by picking longest hash variant
  const processed = pool.filter((u) => u.includes('/_processed_/'));
  const thumbs = pool.filter((u) => u.toLowerCase().includes('/thumbnails/'));
  const pick = processed[0] || thumbs[0] || pool[0] || null;
  if (!pick) return null;
  const url = abs(pick);
  // HARD REJECT: a data:/base64 URI must NEVER be persisted (this is the versa
  // scraper's original bug — it stored the base64 placeholder). Belt-and-braces
  // even though the /fileadmin/ filter above can't match a data: URI.
  if (isDataUri(url)) return null;
  return url;
}

function sqlLit(s) { return "'" + String(s).replace(/'/g, "''") + "'"; }

async function main() {
  const where = `et.catalog_table='versa_catalog' AND et.phase3_ai_at IS NULL AND et.last_error='Gemini returned no data' AND coalesce(c.product_url,'')<>''`;
  const rows = psql(
    `SELECT c.mfr_sku, c.product_url, coalesce(c.image_url,''), coalesce(c.all_images,'')
       FROM enrichment_tracking et JOIN versa_catalog c ON c.mfr_sku=et.mfr_sku
      WHERE ${where} ORDER BY c.mfr_sku ${LIMIT ? 'LIMIT ' + LIMIT : ''}`)
    .split('\n').filter(Boolean)
    .map((l) => { const [mfr_sku, product_url, image_url, all_images] = l.split('|'); return { mfr_sku, product_url, image_url, all_images }; });

  console.log(`[recover-versa] ${APPLY ? 'APPLY' : 'DRY-RUN'} — ${rows.length} dead versa rows${LIMIT ? ` (limit ${LIMIT})` : ''}`);
  const recMap = join(DATA, 'versa-recovery-map.jsonl');
  const resMap = join(DATA, 'versa-restore-map.jsonl');
  writeFileSync(recMap, ''); writeFileSync(resMap, '');

  let recovered = 0, nopdp = 0, noimg = 0, deadnew = 0;
  const pageCache = new Map();
  const CONC = 6;
  for (let i = 0; i < rows.length; i += CONC) {
    const batch = rows.slice(i, i + CONC);
    await Promise.all(batch.map(async (r) => {
      let html = pageCache.get(r.product_url);
      if (html === undefined) { html = await getText(r.product_url); pageCache.set(r.product_url, html); }
      if (!html) { nopdp++; return; }
      const url = extractImage(html, r.mfr_sku);
      if (!url) { noimg++; return; }
      if (isDataUri(url)) { noimg++; return; }          // never persist a data: URI
      const live = await isLiveImage(url);
      if (!live) { deadnew++; return; }
      recovered++;
      appendFileSync(recMap, JSON.stringify({ mfr_sku: r.mfr_sku, new_image_url: url, verified: true }) + '\n');
      appendFileSync(resMap, JSON.stringify({ mfr_sku: r.mfr_sku, old_image_url: r.image_url, old_all_images: r.all_images }) + '\n');
    }));
    process.stdout.write(`\r  progress ${Math.min(i + CONC, rows.length)}/${rows.length}  recovered=${recovered} noimg=${noimg} nopdp=${nopdp} deadnew=${deadnew}   `);
  }
  console.log('');
  const rate = rows.length ? ((recovered / rows.length) * 100).toFixed(1) : '0';
  console.log(`[recover-versa] recovered ${recovered}/${rows.length} = ${rate}%  (noimg=${noimg} nopdp=${nopdp} new-url-not-live=${deadnew})`);
  console.log(`  recovery map: ${recMap}`);
  console.log(`  restore map:  ${resMap}`);

  if (APPLY) {
    // GATED: write fresh image_url into versa_catalog (Mac2 canonical *_catalog).
    // NOTE: phase-3 enrichment reads the KAMATERA catalog — after this Mac2 write,
    // the Mac2->Kamatera *_catalog sync must carry it over for the loop to clear.
    const lines = readFileSync(recMap, 'utf8').split('\n').filter(Boolean);
    let wrote = 0;
    for (const ln of lines) {
      const { mfr_sku, new_image_url } = JSON.parse(ln);
      psql(`UPDATE versa_catalog SET image_url=${sqlLit(new_image_url)} WHERE mfr_sku=${sqlLit(mfr_sku)}`);
      wrote++;
    }
    console.log(`[recover-versa] APPLIED ${wrote} image_url updates to versa_catalog (Mac2).`);
  }
}

// Only run the DB-driven recovery when executed directly; importing the module
// (e.g. from tests) exposes the pure helpers without touching the database.
if (process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href) {
  await main();
}