`scripts/backfill-hotspots.js`

95 lines
// TK-11260 — hotspots-ONLY backfill for rooms that already have a scene_image but
// empty hotspots ('[]'). Follow-on to TK-10402's Flux backfill: the Flux scene path
// (SCENE_PROVIDER=replicate-flux) doesn't run vision-locate, so those 12 rooms
// rendered but weren't shoppable. Unlike scripts/backfill-room-scenes.js (which also
// regenerates the SCENE — paid, ~$0.04/room), this only re-runs lib/hotspots against
// the EXISTING scene_image, so it's ~10x cheaper.
//
//   node scripts/backfill-hotspots.js --dry-run              # $0: list targets + est cost
//   node scripts/backfill-hotspots.js --limit 1               # CANARY: one room, show cost
//   node scripts/backfill-hotspots.js --max-cost 1             # hard spend cap (default $1)
//   node scripts/backfill-hotspots.js                          # all empty-hotspots rooms w/ a scene
//   HOTSPOT_PROVIDER=openai node scripts/backfill-hotspots.js  # force the fallback provider
//
// Idempotent: only rooms with scene_image NOT NULL AND (hotspots IS NULL OR hotspots = '[]')
// unless --force. Whichever DATABASE_URL is active is the one updated. Per-room + running
// cost printed every step. Failures are per-room (logged, skipped) — never aborts the batch.
require('dotenv').config();
const path = require('path');
const db = require('../lib/db');
const hotspots = require('../lib/hotspots');
const { flag, num, MAX_FAILS } = require('../lib/run-guard');

const FORCE = flag('--force');
const DRY = flag('--dry-run');
const LIMIT = num('--limit', null);
const MAX_COST = num('--max-cost', Number(process.env.MAX_COST || 1));

// The provider contract silently returns {hotspots:[],cost:0} when its key is
// missing (intentional graceful-degrade for the LIVE server path — a room must
// still render without a configured vision key). That's dangerous for a batch
// backfill: a missing/unrouted key would look identical to "nothing found" and
// get written as a false "done". Fail loud here instead.
const REQUIRED_KEY = { gemini: 'GEMINI_API_KEY', openai: 'OPENAI_API_KEY' };
const needKey = REQUIRED_KEY[hotspots.PROVIDER];
if (needKey && !process.env[needKey]) {
  console.error(`[hotspots] ABORT: HOTSPOT_PROVIDER=${hotspots.PROVIDER} needs ${needKey}, which is not set. $0 spent.`);
  process.exit(2);
}

(async () => {
  const where = FORCE
    ? 'WHERE scene_image IS NOT NULL'
    : "WHERE scene_image IS NOT NULL AND (hotspots IS NULL OR hotspots::text = '[]')";
  const lim = LIMIT ? `LIMIT ${LIMIT}` : '';
  const { rows: targets } = await db.query(
    `SELECT slug, scene_image, product_ids FROM rooms ${where} ORDER BY updated_at ${lim}`);

  const perCall = hotspots.COST_PER_CALL || 0;
  const est = targets.length * perCall;
  console.log(`[hotspots] provider=${hotspots.PROVIDER} — ${targets.length} room(s) need hotspots. Est cost ~$${est.toFixed(3)} ($${perCall}/room). Cap: $${MAX_COST.toFixed(2)}.`);
  if (DRY) {
    for (const r of targets) console.log(`  · would locate ${r.slug} (${(r.product_ids || []).length} products)`);
    console.log('[hotspots] DRY RUN — nothing called, $0 spent.');
    process.exit(0);
  }
  if (est > MAX_COST) {
    console.error(`[hotspots] ABORT: estimated $${est.toFixed(3)} exceeds --max-cost $${MAX_COST.toFixed(2)}. Raise the cap or use --limit. $0 spent.`);
    process.exit(2);
  }

  let spent = 0, done = 0, skipped = 0, fails = 0;
  for (const r of targets) {
    if (spent + perCall > MAX_COST) {
      console.error(`[hotspots] STOP: next room would exceed the $${MAX_COST.toFixed(2)} cap (spent $${spent.toFixed(3)}).`);
      break;
    }
    try {
      const ids = r.product_ids || [];
      if (!ids.length) { console.log(`  · ${r.slug}: no products — skipped`); skipped++; continue; }
      const { rows: products } = await db.query(
        `SELECT id, title, image_url, price, sale_price, advertiser FROM products WHERE id = ANY($1) AND NOT suppressed`, [ids]);
      if (!products.length) { console.log(`  · ${r.slug}: no usable products — skipped`); skipped++; continue; }

      const imgPath = path.join(__dirname, '..', 'public', r.scene_image);
      const loc = await hotspots.locateProducts(imgPath, products);
      if (loc.error) throw new Error(loc.error);

      await db.query('UPDATE rooms SET hotspots=$1, updated_at=now() WHERE slug=$2',
        [JSON.stringify(loc.hotspots || []), r.slug]);

      spent += loc.cost || 0; done++; fails = 0;
      console.log(`  ✓ ${r.slug}: ${loc.hotspots.length}/${products.length} spots ($${(loc.cost || 0).toFixed(4)})  running: $${spent.toFixed(4)}`);
    } catch (e) {
      fails += 1;
      console.error(`  ✗ ${r.slug}: ${e.message}`);
      if (fails >= MAX_FAILS) {
        console.error(`[hotspots] ABORT: ${MAX_FAILS} consecutive failures — likely a dead/unfunded credential. Spent $${spent.toFixed(4)}.`);
        break;
      }
    }
  }
  console.log(`[hotspots] done. ${done} updated, ${skipped} skipped, spent $${spent.toFixed(4)}.`);
  await db.pool.end();
})();