`scripts/harvest_harlequin_trade.mjs`

57 lines
#!/usr/bin/env node
// Harvest Harlequin US TRADE price ($0, no login) into harlequin_catalog.price_trade.
// Path: harlequin.design/us public JSON API returns US SSP (Suggested Selling Price) in USD.
// Group-wide convention (proven on 1948/1948 Sanderson trade rows): SSP = 2 x TRADE.
//   => trade = US_SSP / 2.
// Checkpoints continuously to pilot/harlequin_trade_harvest.jsonl so nothing is lost on a rate-limit/crash.
// NEVER fabricates a price. Leaves price_trade NULL where the API returns no US price.
import https from 'https';
import fs from 'fs';
import { execFileSync } from 'child_process';

const DIR = new URL('..', import.meta.url).pathname;
const CKPT = DIR + 'pilot/harlequin_trade_harvest.jsonl';
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36';
const sleep = ms => new Promise(r => setTimeout(r, ms));
const psql = sql => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAc', sql], { encoding: 'utf8' }).trim();

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 hawFrom = slug => { const m = String(slug).match(/haw[0-9]{4}-[0-9]{2}/i); return m ? m[0].toUpperCase() : null; };

(async () => {
  const rows = psql(`select mfr_sku from harlequin_catalog where mfr_sku is not null order by mfr_sku`).split('\n').filter(Boolean);
  // resume: skip already-checkpointed mfr_sku
  const done = new Set();
  if (fs.existsSync(CKPT)) for (const l of fs.readFileSync(CKPT, 'utf8').split('\n').filter(Boolean)) { try { done.add(JSON.parse(l).mfr_sku); } catch {} }
  const todo = rows.filter(r => !done.has(r));
  console.log(`[hq-trade] ${rows.length} total, ${done.size} already checkpointed, ${todo.length} to harvest`);

  let hit = 0, miss = 0, i = 0;
  const out = fs.createWriteStream(CKPT, { flags: 'a' });
  for (const mfr_sku of todo) {
    i++;
    const haw = hawFrom(mfr_sku);
    let ssp = null, trade = null, apiSku = null, name = null;
    if (haw) {
      const filter = encodeURIComponent(JSON.stringify({ sku: haw }));
      const d = await get(`https://www.harlequin.design/us/api/n/find?type=product&verbosity=3&filter=${filter}&limit=1`);
      const c = d && Array.isArray(d.catalog) ? (d.catalog.find(x => (x.sku || '').toUpperCase() === haw) || d.catalog[0]) : null;
      if (c && typeof c.price === 'number' && c.price > 0) {
        ssp = c.price; trade = Math.round((ssp / 2) * 100) / 100; apiSku = c.sku; name = c.name;
        hit++;
      } else miss++;
    } else miss++;
    out.write(JSON.stringify({ mfr_sku, haw, api_sku: apiSku, name, ssp_usd: ssp, trade_usd: trade }) + '\n');
    if (i % 25 === 0 || i === todo.length) console.log(`[hq-trade] ${i}/${todo.length}  hit=${hit} miss=${miss}`);
    await sleep(120);
  }
  out.end();
  console.log(`[hq-trade] harvest DONE: hit=${hit} miss=${miss}. Checkpoint: ${CKPT}`);
})();