`tk11041-innovations-reconcile/rescrape/tk11041-authoritative-table.mjs`

98 lines
#!/usr/bin/env node
/**
 * TK-11041 — the AUTHORITATIVE per-SKU reprice table for the 39 live PR-Innovations items.
 * READ-ONLY. Writes NOTHING to dw_unified or Shopify. Emits JSON + markdown for the gated memo.
 *
 * Canonical chain — EXACTLY matches the production gated writer
 * scripts/innovations-reprice/stage-innov-reprice.mjs line 40:
 *     price_trade (net) = round(list * 0.90)          # 10% trade discount, Sales Order O-111475
 *     our_price  (retail) = round(price_trade / 0.65 / 0.85)   # canonical DW markup (NOT the 1.81 approx)
 * Net is rounded to cents FIRST because price_trade is a real 2dp stored cost column, then retail
 * is derived from that stored net — this is the DW cadence-import convention.
 *
 * SKYLARK is SOLD BY THE ROLL (vendor 2026-09-15). Its price_trade/our_price are computed at the
 * ROLL unit (list_roll = list_per_yd * 10), NOT per-yard-then-x10 — that avoids the 12-cent
 * rounding drift ($2198.28 roll-level vs $2198.40 per-yд-x10).
 */
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { execFileSync } from 'node:child_process';

const HERE = path.dirname(fileURLToPath(import.meta.url));
const TERMS = JSON.parse(fs.readFileSync(path.join(HERE, 'data/pattern-order-terms.json'), 'utf8'));
const r2 = n => Math.round(n * 100) / 100;
// DECIMAL-SAFE money math via integer cents — matches a PostgreSQL NUMERIC round(list*0.90,2)
// write. Plain JS `Math.round(list*0.90*100)/100` hits an IEEE-754 artifact on half-cent
// boundaries (132.95*0.90 = 119.655 reads as 119.6549999 and rounds DOWN to 119.65, a silent 1c
// underprice on Flanders). Integer cents keeps 119.655 -> 11965.5 -> half-up 11966 -> $119.66.
const cents = dollars => Math.round(dollars * 100);
const netOf = list => Math.round(cents(list) * 9 / 10) / 100;          // 10% off, exact
const retailOf = net => Math.round(cents(net) / 0.65 / 0.85) / 100;    // canonical DW markup

// live shopify_product_id from the Mac2 mirror (READ-ONLY) for the restore-map
const psql = sql => execFileSync('psql', ['-h', '/tmp', '-At', '-F', '\t', '-d', 'dw_unified', '-c', sql], { encoding: 'utf8' }).trim();
const spidRows = psql(`SELECT mfr_sku, coalesce(shopify_product_id::text,''), coalesce(price_trade::text,''),
  coalesce(our_price::text,''), coalesce(price_unit,'') FROM innovations_catalog
  WHERE substring(mfr_sku from '^[A-Za-z]+') IN ('CSO','ENS','FLN','PRO','SKL','TTM','VCO') ORDER BY mfr_sku;`);
const SPID = {};
for (const l of spidRows.split('\n').filter(Boolean)) {
  const [sku, spid, pt, op, pu] = l.split('\t');
  SPID[sku] = { spid, old_price_trade: pt || null, old_our_price: op || null, old_price_unit: pu || null };
}

const rows = [];
for (const [sku, t] of Object.entries(TERMS).sort()) {
  const listYd = t.current_list_per_yd;
  const base = {
    mfr_sku: sku, pattern: t.pattern, sold_by: t.sold_by,
    vendor_list_per_yd: listYd, min_order_yds: t.min_order_yds,
    cut_fee_per_yd: t.cut_fee_per_yd, cut_fee_below_yds: t.cut_fee_below_yds,
    width: t.width, warehouse: t.warehouse,
    shopify_product_id: SPID[sku]?.spid || null,
    restore: { old_price_trade: SPID[sku]?.old_price_trade ?? null,
               old_our_price: SPID[sku]?.old_our_price ?? null,
               old_price_unit: SPID[sku]?.old_price_unit ?? null },
  };
  if (t.sold_by === 'roll') {
    const listRoll = r2(listYd * t.roll_yds);          // 134.95 * 10 = 1349.50
    const netRoll = netOf(listRoll);                   // round(1349.50 * 0.90) = 1214.55
    const retailRoll = retailOf(netRoll);              // round(1214.55 / 0.65 / 0.85) = 2198.28
    rows.push({ ...base, order_unit: 'roll', roll_yds: t.roll_yds,
      list_roll: listRoll, net_cost_roll: netRoll, retail_roll: retailRoll,
      // per-yд shown for reference only; NOT the stored price for a by-roll item
      ref_net_per_yd: netOf(listYd), ref_retail_per_yd: retailOf(netOf(listYd)),
      price_unit: 'roll', price_trade: netRoll, our_price: retailRoll });
  } else {
    const net = netOf(listYd);
    rows.push({ ...base, order_unit: 'yard',
      net_cost_per_yd: net, retail_per_yd: retailOf(net),
      price_unit: 'yard', price_trade: net, our_price: retailOf(net) });
  }
}

const now = new Date().toISOString();
const doc = {
  ticket: 'TK-11041', kind: 'authoritative-reprice-table', generated_at: now,
  read_only: true, no_db_write: true, no_shopify_write: true,
  basis: 'LIST (Steve-confirmed; Hektor Martinez reply 2026-09-15)',
  formula: 'net = round(list*0.90); retail = round(net/0.65/0.85). Matches stage-innov-reprice.mjs.',
  skylark_note: 'SOLD BY THE ROLL (10yd/roll). price_trade/our_price computed at the ROLL unit.',
  cohort_n: rows.length, rows,
};
fs.writeFileSync(path.join(HERE, 'data/tk11041-authoritative-table.json'), JSON.stringify(doc, null, 2));

// pattern-level summary
const money = v => '$' + Number(v).toFixed(2);
const byPat = {};
for (const r of rows) if (!byPat[r.pattern]) byPat[r.pattern] = r;
console.log('PATTERN        UNIT   LIST       NET        RETAIL      min  cutfee');
for (const p of Object.keys(byPat)) {
  const r = byPat[p];
  if (r.order_unit === 'roll')
    console.log(`${p.padEnd(14)} roll   ${money(r.list_roll).padEnd(10)} ${money(r.net_cost_roll).padEnd(10)} ${money(r.retail_roll).padEnd(11)} ${String(r.min_order_yds).padEnd(3)}  n/a (roll)  [per-yd ref retail ${money(r.ref_retail_per_yd)}]`);
  else
    console.log(`${p.padEnd(14)} yard   ${money(r.vendor_list_per_yd).padEnd(10)} ${money(r.net_cost_per_yd).padEnd(10)} ${money(r.retail_per_yd).padEnd(11)} ${String(r.min_order_yds).padEnd(3)}  +$${r.cut_fee_per_yd}/yd <${r.cut_fee_below_yds}yd`);
}
console.log(`\nrows=${rows.length}  ->  data/tk11041-authoritative-table.json`);