`tk10978-coordonne/build-plan.mjs`

66 lines
#!/usr/bin/env node
// TK-10978 — Coordonné ADD-ROLL recovery plan builder (READ-ONLY).
//
// Coordonné "unbuyable" = ACTIVE product with a single $4.25 Sample variant and
// no sellable variant. Recovery = ADD a "Sold Per Roll" variant priced by the
// DW-STANDARD formula price_trade/0.65/0.85 (identical to the approved WG +
// Scalamandre batches), keeping the $4.25 Sample untouched.
//
// IDENTITY: direct mfr_sku join to coordonne_catalog (price_trade>0). This is an
// EXACT identity match (not Scalamandre's content-match), so matchOk=true by
// construction. rollSku = variant_sku with the trailing '-Sample' stripped.
//
// HARD EXCLUSIONS: no price_trade>0 catalog row; missing/!-Sample variant_sku;
// retail out of sane range (10 < r < 10000). No writes — psql read-only.
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,
    c.dw_sku AS catalog_dw_sku, c.pattern_name, c.color_name,
    
    c.price_retail AS roll_retail
  FROM shopify_products sp
  JOIN coordonne_catalog c ON upper(c.mfr_sku)=upper(sp.mfr_sku)
  WHERE sp.vendor='Coordonné' AND sp.status='ACTIVE'
    AND NOT coalesce(sp.has_product_variant,false)
    AND sp.variant_sku ILIKE '%-Sample'
    AND c.price_retail > 0
  ORDER BY sp.shopify_id, c.price_retail 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 rollRetail = Number(r.roll_retail);
  if (!(rollRetail > 10 && rollRetail < 10000)) { excluded.push({ pid:r.pid, title:r.title, reason:`retail_range ${rollRetail}` }); continue; }
  const rollSku = String(r.sample_sku).replace(/-Sample$/i, '');
  plan.push({
    pid: r.pid, title: r.title,
    sampleSku: r.sample_sku, rollSku,
    catalog_dw_sku: r.catalog_dw_sku, mfr_sku: r.mfr_sku,
    price_trade: null, rollRetail,
    cat_pattern: r.pattern_name, cat_color: r.color_name,
    matchOk: true,            // direct mfr_sku identity join
  });
}
const report = { ticket:'TK-10978', vendor:'Coordonné', generated_at:new Date().toISOString(),
  mode:'READ-ONLY plan builder', formula:'price_retail (sell at MSRP — empirically matches live buyable $169=max MSRP)',
  raw_rows:rows.length, clean_actionable:plan.length, excluded:excluded.length,
  retail_min: plan.length?Math.min(...plan.map(p=>p.rollRetail)):null,
  retail_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}`);