`tk10978-rebelwalls/build-plan.mjs`

97 lines
#!/usr/bin/env node
// TK-10978 — Rebel Walls "unbuyable" recovery plan builder (READ-ONLY, dry-run).
//
// CORRECTION to the 2026-09-01 progress note: the 494 live unbuyable RW products
// use an mfr_sku "RS####" series (e.g. RS12851 "Tin Plates, Ontario") that is
// ABSENT from rebel_walls_catalog (which is entirely an "R####" series). So the
// "cost already staged, pure compute" premise is FALSE for these 494 — there is
// no identity join and no staged price for the RS-series. Recovery is therefore
// a Scalamandre-class CONTENT-MATCH (pattern -> catalog pattern_name), which
// "fails plausibly" and is PILOT-GATED per the methodology.
//
// SAFE cohort  = live pattern (text before first comma) matches EXACTLY ONE
//   catalog pattern_name that carries a SINGLE price_retail (n_prices=1) ->
//   unambiguous price. Everything else is excluded:
//     - multi_price_ambiguous : pattern maps to >1 catalog price (wrong-price risk)
//     - no_catalog_match      : pattern absent from catalog -> needs re-scrape
//
// SELL-PRICE CONVENTION (per the 09-01 vp-dw-commerce note, PILOT-VERIFY before batch):
//   sellable variant = base $191 flat + custom.mural_price_per_sqm metafield,
//   where per_sqm = round(catalog price_retail * 1.0909, 2). Keep $4.25 Sample.
//
// NO WRITES. psql read-only. Emits data/plan.csv + data/report.json.
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 BASE_PRICE = 191;      // flat sellable-variant base, per 09-01 note
const PER_SQM_MARKUP = 1.0909;

const SQL = `
BEGIN READ ONLY;
SELECT json_agg(t) FROM (
  WITH live AS (
    SELECT regexp_replace(sp.shopify_id,'.*/','') AS pid, sp.title, sp.variant_sku AS sample_sku,
           sp.mfr_sku AS live_mfr,
           lower(trim(split_part(replace(sp.title,' | Rebel Walls',''), ',', 1))) AS pat
    FROM shopify_products sp
    WHERE sp.vendor='Rebel Walls' AND sp.status='ACTIVE'
      AND NOT coalesce(sp.has_product_variant,false)
      AND sp.variant_sku ILIKE '%-Sample'
  ),
  catp AS (
    SELECT lower(trim(pattern_name)) AS pat,
           count(DISTINCT price_retail) AS n_prices,
           min(price_retail) AS price_retail,
           (array_agg(dw_sku ORDER BY dw_sku))[1] AS a_dw_sku
    FROM rebel_walls_catalog WHERE price_retail>0 GROUP BY 1
  )
  SELECT live.pid, live.title, live.sample_sku, live.live_mfr, live.pat,
         catp.n_prices, catp.price_retail, catp.a_dw_sku
  FROM live LEFT JOIN catp ON catp.pat=live.pat
) 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 = [], multi = [], nomatch = [];
for (const r of rows) {
  if (r.n_prices == null)      { nomatch.push({ pid:r.pid, title:r.title, pat:r.pat, reason:'no_catalog_match_needs_rescrape' }); continue; }
  if (Number(r.n_prices) > 1)  { multi.push({ pid:r.pid, title:r.title, pat:r.pat, reason:`multi_price_ambiguous(${r.n_prices})` }); continue; }
  const pr = Number(r.price_retail);
  if (!(pr > 10 && pr < 10000)) { multi.push({ pid:r.pid, title:r.title, pat:r.pat, reason:`retail_range ${pr}` }); continue; }
  const perSqm = Math.round(pr * PER_SQM_MARKUP * 100) / 100;
  const muralSku = String(r.sample_sku).replace(/-Sample$/i, '');
  plan.push({
    pid:r.pid, title:r.title, sampleSku:r.sample_sku, muralSku,
    live_mfr:r.live_mfr, matched_pattern:r.pat, catalog_dw_sku:r.a_dw_sku,
    catalog_price_retail:pr, base_price:BASE_PRICE, per_sqm:perSqm,
    matchType:'content-match(pattern,unique-price)', pilotRequired:true,
  });
}

const report = {
  ticket:'TK-10978', vendor:'Rebel Walls', child:'RW-494 corrected', generated_at:new Date().toISOString(),
  mode:'READ-ONLY dry-run — NO writes fired',
  premise_correction:'RS-series live SKUs absent from rebel_walls_catalog (R-series); NOT an identity join / staged-cost clean win',
  sell_convention:`base $${BASE_PRICE} + custom.mural_price_per_sqm = price_retail*${PER_SQM_MARKUP} (09-01 note — PILOT-VERIFY)`,
  raw_unbuyable: rows.length,
  content_match_safe: plan.length,
  multi_price_ambiguous_excluded: multi.length,
  no_catalog_match_needs_rescrape: nomatch.length,
  per_sqm_min: plan.length?Math.min(...plan.map(p=>p.per_sqm)):null,
  per_sqm_max: plan.length?Math.max(...plan.map(p=>p.per_sqm)):null,
  gate:'customer-facing Shopify write -> HARD-GATED; pilot-first; needs Steve on-ticket go',
};

const cols = ['pid','title','sampleSku','muralSku','live_mfr','matched_pattern','catalog_dw_sku','catalog_price_retail','base_price','per_sqm','matchType','pilotRequired'];
const esc = v => `"${String(v??'').replace(/"/g,'""')}"`;
const csv = [cols.join(',')].concat(plan.map(p=>cols.map(c=>esc(p[c])).join(','))).join('\n');
writeFileSync(`${HERE}data/plan.csv`, csv);
writeFileSync(`${HERE}data/report.json`, JSON.stringify({ report, excluded_multi:multi.slice(0,20), excluded_nomatch:nomatch.slice(0,20) }, null, 2));
console.log(JSON.stringify(report, null, 2));