`d3b_heldreview_emit_sql.mjs`

43 lines
#!/usr/bin/env node
// TK-11331 D3b-heldreview — READ-ONLY SQL emitter. Reads data/d3b-held-rows.json (+ optional
// data/d3b-held-images.tsv) and writes data/d3b-held-insert.sql. No DB, no network.
// Mirrors d3c_stage.mjs field semantics: cost=list*0.80, hw_price=list*1.448, sub_category for acoustic,
// settlement-review tag where flagged, dw_sku omitted (NULL — assign-sku FROZEN), uom -> uom+price_unit.
// Insert guarded by ON CONFLICT (pattern_name,color_name) DO NOTHING; RETURNING prints inserted ids.
import fs from 'fs';
import path from 'path';
const HERE = path.dirname(new URL(import.meta.url).pathname);
const rows = JSON.parse(fs.readFileSync(path.join(HERE, 'data', 'd3b-held-rows.json'), 'utf8'));
const imgPath = path.join(HERE, 'data', 'd3b-held-images.tsv');
const imgMap = new Map();
if (fs.existsSync(imgPath)) {
  for (const l of fs.readFileSync(imgPath, 'utf8').split('\n').filter(Boolean)) {
    const [num, img, series] = l.split('\t');
    imgMap.set(num, { img: img || null, series: series || null });
  }
}
const COST_MULT = 0.80, HW_MULT = 1.448;
const money = n => { const x = Number(n); return (!n || !(x > 0)) ? null : Number(x.toFixed(2)); };
const q = s => s == null ? 'NULL' : `'${String(s).replace(/'/g, "''")}'`;
const num = n => n == null ? 'NULL' : String(n);

const out = [];
out.push('\\set ON_ERROR_STOP on');
out.push('BEGIN;');
for (const r of rows) {
  const list = money(r.list_price);
  const im = imgMap.get(String(r.number)) || {};
  const tags = r.settlement ? JSON.stringify(['settlement-review']) : null;
  const subcat = r.category === 'Acoustic' ? 'Acoustic Panel' : null;
  const cost = list == null ? null : money(list * COST_MULT);
  const hw = list == null ? null : money(list * HW_MULT);
  out.push(
    `INSERT INTO momentum_colorways (pattern_name,color_name,color_number,momentum_sku,image_url,list_price,width,category,product_line,collection_name,uom,price_unit,cost,hw_price,sub_category,tags,created_at,updated_at) VALUES (` +
    `${q(r.stage_pattern)},${q(r.color_name)},${q(r.color_number)},${q(r.number)},${q(im.img || null)},${num(list)},${q(r.width)},${q(r.category)},${q(r.product_line)},${q(im.series || null)},${q(r.uom)},${q(r.uom)},${num(cost)},${num(hw)},${q(subcat)},${q(tags)},now(),now()) ` +
    `ON CONFLICT (pattern_name,color_name) DO NOTHING RETURNING id||E'\\t'||pattern_name||E'\\t'||color_name;`
  );
}
out.push('COMMIT;');
fs.writeFileSync(path.join(HERE, 'data', 'd3b-held-insert.sql'), out.join('\n') + '\n');
console.log(`emitted ${rows.length} INSERTs -> data/d3b-held-insert.sql  (images: ${imgMap.size})`);