`scripts/backfill-wallco-rooms.js`

74 lines
#!/usr/bin/env node
/**
 * backfill-wallco-rooms — the /admin/rooms gallery reads the wallco_rooms table,
 * but it's only populated by the interactive "save room render" endpoint
 * (server.js ~19435). The BULK room mockups generated by
 * scripts/generate_room_mockups.py land in data/rooms/design_<id>_<room>.png +
 * the spoon_all_designs.room_mockups JSONB — and were NEVER inserted into
 * wallco_rooms. So the gallery showed ~200 of the ~1,400+ renders that exist.
 *
 * This scans data/rooms/design_*.png and inserts a wallco_rooms row for any not
 * already present (matched by image_path = /designs/room/<basename>, which is
 * the public static route that serves data/rooms/). Idempotent — re-runnable.
 * Tagged notes='backfill:data/rooms' so it's trivially reversible:
 *   DELETE FROM wallco_rooms WHERE notes='backfill:data/rooms';
 *
 * Usage:  node scripts/backfill-wallco-rooms.js [--dry]
 */
'use strict';
const fs = require('fs');
const path = require('path');
const { execSync } = require('child_process');
const ROOT = path.resolve(__dirname, '..');
const ROOMS = path.join(ROOT, 'data', 'rooms');
const DRY = process.argv.includes('--dry');

// On prod (Linux) psql must run as the postgres role — root has no PG role
// (FATAL: role "root" does not exist). On Mac2 the local user owns dw_unified.
const PSQL = process.platform === 'linux' ? 'sudo -u postgres psql' : 'psql';
function psql(sql) {
  return execSync(`${PSQL} dw_unified -At -F'\x1f' -c ${JSON.stringify(sql)}`, {
    encoding: 'utf8', maxBuffer: 64 * 1024 * 1024,
  });
}
function esc(s) { return String(s).replace(/'/g, "''"); }

// 1) existing image_paths already in wallco_rooms (idempotency guard)
const existing = new Set(
  psql("SELECT image_path FROM wallco_rooms WHERE image_path IS NOT NULL;")
    .split('\n').map(s => s.trim()).filter(Boolean)
);

// 2) scan data/rooms/design_<id>_<room>.png — skip templates/masks
const files = fs.readdirSync(ROOMS).filter(f => /^design_\d+_.+\.png$/.test(f));
const rows = [];
let skipExisting = 0, skipParse = 0;
for (const f of files) {
  const m = f.match(/^design_(\d+)_(.+)\.png$/);
  if (!m) { skipParse++; continue; }
  const designId = parseInt(m[1], 10);
  const roomType = m[2];                       // e.g. living_room, bedroom, living_room_hero
  const imagePath = `/designs/room/${f}`;
  if (existing.has(imagePath)) { skipExisting++; continue; }
  let bytes = 0;
  try { bytes = fs.statSync(path.join(ROOMS, f)).size; } catch {}
  rows.push({ designId, roomType, filename: f, imagePath, bytes });
}

console.log(`data/rooms PNGs: ${files.length} · already in wallco_rooms: ${skipExisting} · unparseable: ${skipParse} · to insert: ${rows.length}`);
if (DRY) { console.log('--- DRY: no inserts. Sample:', rows.slice(0, 3)); process.exit(0); }
if (!rows.length) { console.log('nothing to backfill.'); process.exit(0); }

// 3) batched multi-row INSERT (chunks of 500)
let inserted = 0;
for (let i = 0; i < rows.length; i += 500) {
  const chunk = rows.slice(i, i + 500);
  const values = chunk.map(r =>
    `(${r.designId}, '${esc(r.roomType)}', '${esc(r.filename)}', '${esc(r.imagePath)}', ${r.bytes}, FALSE, 'backfill:data/rooms')`
  ).join(',');
  psql(`INSERT INTO wallco_rooms (design_id, room_type, filename, image_path, file_size_bytes, selected_for_marketing, notes) VALUES ${values};`);
  inserted += chunk.length;
  process.stdout.write(`  inserted ${inserted}/${rows.length}\r`);
}
console.log(`\n✓ backfilled ${inserted} room rows into wallco_rooms (tagged notes='backfill:data/rooms').`);