`scripts/final-split.js`

103 lines
#!/usr/bin/env node
/**
 * Step-0 DEFINITIVE priceable-vs-hold split (DTD verdict B). READ-ONLY.
 * Writes dw_unified.active_five_field_gaps (per-SKU) + a per-vendor JSON.
 *
 * Per-SKU fix + priceability classification:
 *   fix_type: 'add-sample'  (has_product, !has_sample)         -> always priceable ($4.25)
 *             'build-roll'   (!has_product)                     -> needs real cost
 *             'reprice-zero' (has_product, has_sample, any_zero)-> reprice from cost
 *   roll_cost_source / roll_price: from Kravet MAP (kravet fam) or vendor catalog costExpr.
 *   priceable: add-sample always true; build-roll/reprice true iff a trusted cost/price found.
 */
const fs = require('fs');
const { execSync } = require('child_process');
delete process.env.PGPASSWORD; delete process.env.DATABASE_URL;
const V = require(process.env.HOME + '/Projects/Designer-Wallcoverings/shopify/scripts/cadence/vendors.js');
const psql = s => execSync('psql -d dw_unified -tAF"\t" -f -', { input: s, encoding: 'utf8', maxBuffer: 1 << 28 });

const KF = ['Kravet','Kravet Couture','Kravet Design','Kravet Contract','Kravet Basics','Lee Jofa','Lee Jofa Modern','Groundworks','Brunschwig & Fils','Cole & Son','GP & J Baker','Colefax And Fowler','Clarke And Clarke','Clarke & Clarke','Mulberry','Threads','Baker Lifestyle','Andrew Martin','Nicolette Mayer','Aerin','Barclay Butera','Thom Filicia','Gaston Y Daniela','Donghia'];
const kfVals = KF.map(v => `('${v.replace(/'/g, "''")}')`).join(',');

// Non-kravet catalog cost expressions we trust (from cadence vendors.js).
const CAT = {
  'Osborne & Little':['osborne_catalog','price_trade'], 'Schumacher':['schumacher_catalog','cost'],
  'Thibaut':['thibaut_catalog','your_cost'], 'Romo':['romo_catalog','cost'],
  'Designtex':['designtex_catalog','price_retail'], 'Ralph Lauren':['rl_catalog','cost'],
  'Graham & Brown':['graham_brown_catalog','cost_price'], 'WallQuest':['wallquest_catalog','price_retail'],
};

// Build the per-SKU gaps table.
let catJoins = '', catSel = [];
for (const [v, [t, col]] of Object.entries(CAT)) {
  const al = t.replace(/[^a-z0-9]/g, '');
  catJoins += `\n  LEFT JOIN ${t} ${al} ON a.vendor='${v.replace(/'/g, "''")}' AND upper(${al}.mfr_sku)=upper(sp.mfr_sku)`;
  catSel.push(`CASE WHEN a.vendor='${v.replace(/'/g, "''")}' THEN NULLIF(${al}.${col}::numeric,0) END`);
}

const sql = `
DROP TABLE IF EXISTS active_five_field_gaps;
CREATE TABLE active_five_field_gaps AS
WITH kf(vendor) AS (VALUES ${kfVals}),
base AS (
  SELECT DISTINCT ON (a.shopify_id)
    a.shopify_id, a.vendor, sp.dw_sku, sp.mfr_sku,
    a.has_sample, a.has_product, a.any_zero, a.product_price, a.sample_price,
    (kf.vendor IS NOT NULL) AS is_kravet_fam,
    -- Kravet MAP price
    COALESCE(NULLIF(vm.price,0), NULLIF(pm.map_price,0), NULLIF(kmp.map_price,0)) AS kravet_price,
    -- non-kravet catalog cost (raw cost; retail computed downstream)
    COALESCE(${catSel.join(',\n            ')}) AS catalog_cost
  FROM active_five_field_status a
  JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
  LEFT JOIN kf ON kf.vendor=a.vendor
  LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku=sp.dw_sku
  LEFT JOIN product_map pm ON pm.dw_sku=sp.dw_sku
  LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku)=upper(sp.mfr_sku)${catJoins}
  WHERE (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
)
SELECT *,
  CASE WHEN NOT has_product THEN 'build-roll'
       WHEN has_product AND NOT has_sample THEN 'add-sample'
       WHEN any_zero THEN 'reprice-zero' END AS fix_type,
  -- computed roll price: kravet MAP as-is; non-kravet cost/0.65/0.85
  CASE WHEN is_kravet_fam THEN kravet_price
       WHEN catalog_cost IS NOT NULL THEN round((catalog_cost/0.65/0.85)::numeric,2) END AS computed_roll_price,
  -- priceable: add-sample always true; otherwise need a trusted roll price/cost
  CASE
    WHEN has_product AND NOT has_sample THEN true                          -- add-sample, $4.25
    WHEN is_kravet_fam AND kravet_price>0 THEN true                        -- kravet MAP
    WHEN (NOT is_kravet_fam) AND catalog_cost>0 THEN true                  -- real catalog cost
    ELSE false                                                            -- no-cost HOLD
  END AS priceable
FROM base;
CREATE INDEX ON active_five_field_gaps(vendor);
CREATE INDEX ON active_five_field_gaps(fix_type);
`;
psql(sql);

// Per-vendor split.
const rows = psql(`
  SELECT vendor,
    count(*) need_fix,
    count(*) FILTER (WHERE fix_type='build-roll') build_roll,
    count(*) FILTER (WHERE fix_type='add-sample') add_sample,
    count(*) FILTER (WHERE fix_type='reprice-zero') reprice,
    count(*) FILTER (WHERE priceable) priceable,
    count(*) FILTER (WHERE NOT priceable) hold,
    bool_or(is_kravet_fam) kravet_fam
  FROM active_five_field_gaps GROUP BY vendor ORDER BY need_fix DESC;
`).trim().split('\n').filter(Boolean).map(l => {
  const [vendor, need_fix, build_roll, add_sample, reprice, priceable, hold, kf] = l.split('\t');
  return { vendor, need_fix:+need_fix, build_roll:+build_roll, add_sample:+add_sample, reprice:+reprice, priceable:+priceable, hold:+hold, kravet_fam: kf === 't' };
});
const totals = rows.reduce((a, r) => ({
  need_fix:a.need_fix+r.need_fix, build_roll:a.build_roll+r.build_roll, add_sample:a.add_sample+r.add_sample,
  reprice:a.reprice+r.reprice, priceable:a.priceable+r.priceable, hold:a.hold+r.hold,
}), { need_fix:0, build_roll:0, add_sample:0, reprice:0, priceable:0, hold:0 });

const out = { generatedAt: new Date().toISOString(), source: 'live-scan (DTD-B) + trusted-cost join, product_pricing stubs excluded', totals, vendors: rows };
fs.writeFileSync(__dirname + '/../out/step0-priceable-vs-hold.json', JSON.stringify(out, null, 2));
console.log('TOTALS', JSON.stringify(totals, null, 0));
console.table(rows.slice(0, 35).map(r => ({ vendor: r.vendor.slice(0,26), need: r.need_fix, roll: r.build_roll, sample: r.add_sample, priceable: r.priceable, hold: r.hold, kf: r.kravet_fam ? 'Y' : '' })));