`scripts/harvest_width.js`

59 lines
#!/usr/bin/env node
// Harvest Sanderson product WIDTH from the PUBLIC retail API ($0, no auth) into
// sanderson_catalog.width. The trade portal carries price but NOT width; width
// lives on www.sanderson.design's product record (sdb_usable_width_inches / sdb_useable_width).
//
// Endpoint (proven via network capture): one call per SKU, keyed by sku —
//   GET /api/n/find?type=product&verbosity=3&filter={"sku":"<SKU>"}&limit=1
//   → .catalog[] entry where .sku===SKU has sdb_usable_width_inches ("20.47 in")
//     and sdb_useable_width ("52 cm").
// Stored in the DW sibling convention: "20.47 in  (52 cm)".
//
// Usage: node scripts/harvest_width.js            (all null-width rows)
//        node scripts/harvest_width.js --all      (re-harvest every row)
const fs = require('fs');
const https = require('https');
const { execFileSync } = require('child_process');

const ALL = process.argv.includes('--all');
const sleep = ms => new Promise(r => setTimeout(r, ms));
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36';

function get(url) {
  return new Promise((resolve) => {
    https.get(url, { headers: { 'User-Agent': UA, 'Accept': 'application/json' } }, res => {
      let d = ''; res.on('data', c => d += c); res.on('end', () => { try { resolve(JSON.parse(d)); } catch { resolve(null); } });
    }).on('error', () => resolve(null));
  });
}
const psql = sql => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAc', sql], { encoding: 'utf8' }).trim();
const psqlFile = f => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-q', '-v', 'ON_ERROR_STOP=1', '-f', f], { encoding: 'utf8' });
const q = s => "'" + String(s).replace(/'/g, "''") + "'";
const numFrom = s => { const m = String(s == null ? '' : s).match(/-?\d+(\.\d+)?/); return m ? m[0] : null; };

(async () => {
  const where = ALL ? "mfr_sku is not null" : "mfr_sku is not null and width is null";
  const skus = psql(`select mfr_sku from sanderson_catalog where ${where} order by mfr_sku`).split('\n').filter(Boolean);
  console.log(`[width] ${skus.length} SKUs to harvest (${ALL ? 'all' : 'null-width only'})`);
  const updates = []; let hit = 0, miss = 0;
  for (let i = 0; i < skus.length; i++) {
    const sku = skus[i];
    const filter = encodeURIComponent(JSON.stringify({ sku }));
    const url = `https://www.sanderson.design/api/n/find?type=product&verbosity=3&filter=${filter}&limit=1`;
    const d = await get(url);
    const cat = (d && Array.isArray(d.catalog)) ? d.catalog.find(c => c.sku === sku) || d.catalog[0] : null;
    const inch = cat && numFrom(cat.sdb_usable_width_inches);
    const cm = cat && numFrom(cat.sdb_useable_width);
    if (inch) { const w = cm ? `${inch} in  (${cm} cm)` : `${inch} in`; updates.push(`update sanderson_catalog set width=${q(w)} where mfr_sku=${q(sku)};`); hit++; }
    else miss++;
    if ((i + 1) % 25 === 0 || i === skus.length - 1) console.log(`[width] ${i + 1}/${skus.length}  hit=${hit} miss=${miss}`);
    await sleep(150);
  }
  if (updates.length) {
    const tmp = '/tmp/sanderson_width_updates.sql';
    fs.writeFileSync(tmp, 'begin;\n' + updates.join('\n') + '\ncommit;\n');
    psqlFile(tmp);
  }
  console.log(`[width] DONE: wrote ${hit} widths, ${miss} misses. Fill now: ${psql("select count(width)||'/'||count(*) from sanderson_catalog")}`);
})();