`build-rows-17.mjs`

46 lines
#!/usr/bin/env node
// build-rows-17.mjs — pull the 17 clean onboard candidates from brewster_york_master (+ image) into
// data/onboard-rows-17.json (exact phase1-enriched shape build-onboard-payloads expects). $0 local.
import fs from 'node:fs'; import { execSync } from 'node:child_process';
// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
let PGPW; try{ PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim(); }catch(e){}
if(!PGPW){console.warn('WARN: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env — proceeding with empty PGPASSWORD (dw_admin on 127.0.0.1 is TRUST auth); the psql call will fail loudly if a password is truly required on this host.'); PGPW=''; }
const cands = JSON.parse(fs.readFileSync('data-onboard17-candidates.json','utf8'));
const skus = cands.map(c=>`'${String(c.mfr_sku).replace(/'/g,"''")}'`).join(',');
const SEP=String.fromCharCode(31);
const sql=`select concat_ws(chr(31),
  upper(trim(bym.mfr_sku)), coalesce(bym.parent_brand,''), coalesce(bym.brand,''), coalesce(bym.pattern_name,''),
  coalesce(bym.colorway,''), coalesce(bym.product_name,''), coalesce(bym.book_collection_name,''),
  coalesce(bym.us_msrp::text,''), coalesce(bym.us_map::text,''), coalesce(bym.uom,''),
  coalesce(bym.width_inches::text,''), coalesce(bym.length_feet::text,''), coalesce(bym.pattern_repeat,''),
  coalesce(bym.match_type,''), coalesce(bym.technical_material,''), coalesce(bym.backing,''), coalesce(bym.coverage,''),
  coalesce(bym.marketing_style,''), coalesce(bym.marketing_pattern,''), coalesce(bym.color_family,''),
  coalesce(bym.wholesale::text,''), coalesce(bym.dynamic_description,''), coalesce(bym.roll_price_single::text,''),
  coalesce(nullif(bc.image_url,''), nullif(yc.image_url,''), ''))
 from brewster_york_master bym
 left join brewster_catalog bc on upper(trim(bc.mfr_sku))=upper(trim(bym.mfr_sku))
 left join york_catalog yc on upper(trim(yc.mfr_sku))=upper(trim(bym.mfr_sku))
 where upper(trim(bym.mfr_sku)) in (${skus})`;
fs.writeFileSync('/tmp/_rows17.sql',sql);
const out=execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -f /tmp/_rows17.sql`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();
const num=x=>(x==null||x==='')?null:(isNaN(+x)?null:+x);
const seen=new Set(); const rows=[];
for(const line of out.split('\n').filter(Boolean)){
  const c=line.split(SEP);
  const sku=c[0]; if(seen.has(sku))continue; seen.add(sku);   // brewster_york_master may have dup rows per SKU
  rows.push({ export_sku:sku, parent_brand:c[1], brand:c[2], pattern_name:c[3], colorway:c[4], product_name:c[5],
    book_name:c[6], us_msrp:num(c[7]), us_map:num(c[8]), uom:(c[9]||'').toUpperCase()||null,
    width_inches:num(c[10]), length_feet:num(c[11]), pattern_repeat:c[12]||null, match_type:c[13]||null,
    technical_material:c[14]||null, backing:c[15]||null, coverage:c[16]||null, marketing_style:c[17]||null,
    marketing_pattern:c[18]||null, color_family:c[19]||null, wholesale:num(c[20]),
    dynamic_description:c[21]||'', roll_price_single:num(c[22]), image_url:c[23]||null });
}
fs.writeFileSync('data/onboard-rows-17.json',JSON.stringify(rows,null,1));
// completeness report
const miss=k=>rows.filter(r=>!r[k]||(typeof r[k]==='string'&&!r[k].trim())).length;
console.log(`rows: ${rows.length}/${cands.length}`);
console.log(`  missing image_url: ${miss('image_url')} | missing description: ${miss('dynamic_description')} | missing color_family: ${miss('color_family')}`);
console.log(`  missing price (no us_map AND no roll_single): ${rows.filter(r=>!(r.us_map>0)&&!(r.roll_price_single>0)).length}`);
console.log(`  missing width/length: ${rows.filter(r=>!r.width_inches||!r.length_feet).length} | missing uom: ${miss('uom')}`);
console.log('$0 (local)');