`tk10978-momentum/build-plan.mjs`

45 lines
#!/usr/bin/env node
// TK-10978 — Phillipe Romano (Momentum-sourced) ADD-YARD recovery plan (READ-ONLY).
// PR products whose supplier_name ~ Momentum recover from momentum_colorways.hw_price
// (the DW-assigned "Hollywood price"; roll-type Hollywood twins sell at hw_price exactly).
// Per-yard line -> add a "Sold Per Yard" variant @ hw_price, keep $4.25 Sample.
// Join key: mfr_sku=alt_sku|momentum_sku, or dw_sku=dw_sku. matchOk=true (identity join).
import { writeFileSync, mkdirSync } from 'node:fs';
import { execSync } from 'node:child_process';
const HERE = new URL('.', import.meta.url).pathname; mkdirSync(`${HERE}data`, { recursive: true });
const SQL = `
BEGIN READ ONLY;
SELECT json_agg(t) FROM (
  SELECT DISTINCT ON (sp.shopify_id)
    regexp_replace(sp.shopify_id,'.*/','') AS pid, sp.title, sp.variant_sku AS sample_sku,
    sp.mfr_sku, m.momentum_sku, m.alt_sku, m.hw_price, m.retail_price, m.list_price, m.price_unit
  FROM shopify_products sp
  JOIN momentum_colorways m
    ON (upper(m.alt_sku)=upper(sp.mfr_sku) OR upper(m.momentum_sku)=upper(sp.mfr_sku) OR upper(m.dw_sku)=upper(sp.dw_sku))
  WHERE sp.vendor='Phillipe Romano' AND sp.status='ACTIVE'
    AND NOT coalesce(sp.has_product_variant,false)
    AND sp.supplier_name ~* 'mom'
    AND sp.variant_sku ILIKE '%-Sample'
    AND m.hw_price > 0
  ORDER BY sp.shopify_id, m.hw_price DESC
) t;
ROLLBACK;`;
const out = execSync('psql -h /tmp -d dw_unified -tA -v ON_ERROR_STOP=1',{input:SQL,encoding:'utf8',maxBuffer:64*1024*1024});
const _s=out.slice(out.indexOf('['),out.lastIndexOf(']')+1); const rows=_s?JSON.parse(_s):[];
const plan=[],excluded=[];
for(const r of rows){
  const sell=Number(r.hw_price);
  if(!(sell>2 && sell<10000)){excluded.push({pid:r.pid,title:r.title,reason:`hw_price ${sell}`});continue;}
  plan.push({pid:r.pid,title:r.title,sampleSku:r.sample_sku,rollSku:String(r.sample_sku).replace(/-Sample$/i,''),
    catalog_dw_sku:r.alt_sku||r.momentum_sku, price_trade:Number(r.list_price)||null,
    rollRetail:sell, retail_price:Number(r.retail_price)||null, price_unit:r.price_unit, matchOk:true});
}
const report={ticket:'TK-10978',vendor:'Phillipe Romano (Momentum)',generated_at:new Date().toISOString(),
  formula:'hw_price (Hollywood price) per-yard; retail_price carried as alt', raw_rows:rows.length,
  clean_actionable:plan.length, excluded:excluded.length,
  sell_min:plan.length?Math.min(...plan.map(p=>p.rollRetail)):null, sell_max:plan.length?Math.max(...plan.map(p=>p.rollRetail)):null};
console.log(JSON.stringify(report,null,2));
writeFileSync(`${HERE}data/plan.json`,JSON.stringify({report,plan},null,2));
writeFileSync(`${HERE}data/excluded.json`,JSON.stringify(excluded,null,2));
console.log(`\nplan.json: ${plan.length} products; excluded: ${excluded.length}`);