`scripts/load_feed_pricing.mjs`

30 lines
#!/usr/bin/env node
// Load a brand's feed-harvest JSONL checkpoint -> <brand>_feed_pricing (UPSERT on base_code). Staging only.
// USAGE: node load_feed_pricing.mjs --brand sanderson
import fs from 'fs';
import { execFileSync } from 'child_process';
const args = Object.fromEntries(process.argv.slice(2).reduce((a,v,i,arr)=>{ if(v.startsWith('--')) a.push([v.slice(2), arr[i+1]&&!arr[i+1].startsWith('--')?arr[i+1]:true]); return a; }, []));
const BRAND = args.brand; if (!BRAND) { console.error('need --brand'); process.exit(1); }
const DIR = new URL('..', import.meta.url).pathname;
const CKPT = `${DIR}pilot/${BRAND}_feed_harvest.jsonl`;
const TABLE = `${BRAND}_feed_pricing`;
if (!fs.existsSync(CKPT)) { console.error('no checkpoint '+CKPT); process.exit(1); }
const rows = fs.readFileSync(CKPT,'utf8').split('\n').filter(Boolean).map(JSON.parse);
// dedupe by base_code (last wins)
const byCode = new Map(); for (const r of rows) byCode.set(r.base_code, r);
const q = v => v==null ? 'NULL' : `'${String(v).replace(/'/g,"''")}'`;
const n = v => (v==null || v==='') ? 'NULL' : Number(v);
let vals = [];
for (const r of byCode.values()) {
  vals.push(`(${q(r.base_code)},${q(r.mfr_sku)},${q(r.product_type)},${q(r.pattern)},${q(r.color)},${q(r.collection)},${q(r.width)},${q(r.length)},${n(r.ssp_usd)},${n(r.trade_usd)},${n(r.retail_usd)},${q(r.image)})`);
}
const sql = `INSERT INTO ${TABLE} (base_code,mfr_sku,product_type,pattern,color,collection,width,length,ssp_usd,trade_usd,retail_usd,image) VALUES\n${vals.join(',\n')}\nON CONFLICT (base_code) DO UPDATE SET mfr_sku=EXCLUDED.mfr_sku,product_type=EXCLUDED.product_type,pattern=EXCLUDED.pattern,color=EXCLUDED.color,collection=EXCLUDED.collection,width=EXCLUDED.width,length=EXCLUDED.length,ssp_usd=EXCLUDED.ssp_usd,trade_usd=EXCLUDED.trade_usd,retail_usd=EXCLUDED.retail_usd,image=EXCLUDED.image,harvested_at=now();`;
const tmp = `${DIR}pilot/.load_${BRAND}.sql`;
fs.writeFileSync(tmp, sql);
execFileSync('psql', ['-h','/tmp','-d','dw_unified','-f',tmp], { stdio:'inherit' });
fs.unlinkSync(tmp);
const out = execFileSync('psql',['-h','/tmp','-d','dw_unified','-tAc',
  `SELECT count(*),count(trade_usd),count(*) FILTER(WHERE product_type='wallcovering'),count(*) FILTER(WHERE product_type='fabric') FROM ${TABLE};`],{encoding:'utf8'}).trim();
const [tot,priced,wp,fab]=out.split('|');
console.log(`[${BRAND}] loaded ${TABLE}: total=${tot} priced=${priced} wallcovering=${wp} fabric=${fab}`);