`tk11041-innovations-reconcile/rescrape/tk11041-dryrun-reprice-plan.mjs`

214 lines
#!/usr/bin/env node
/**
 * tk11041-dryrun-reprice-plan.mjs — READ-ONLY dry-run reprice PLAN for the 39 live
 * PR-Innovations items (prefixes CSO/ENS/FLN/PRO/SKL/TTM/VCO). WHL/Whistler EXCLUDED.
 *
 * Emits a JSON + human-readable .md that wire the DW reprice formula end-to-end with the
 * per-pattern LIST price as the ONE free variable. Writes NOTHING to dw_unified or Shopify.
 *
 *   net_cost = LIST * 0.90            (discount SETTLED 10%, HIGH conf — Sales Order O-111475)
 *   retail   = net_cost / 0.65 / 0.85 (DW standard markup)
 *   round    = Math.round(x*100)/100  (nearest cent) — the SAME convention as the existing
 *              add-yard/reprice script scripts/innovations-reprice/stage-innov-reprice.mjs
 *              (RETAIL = c => Math.round((c/0.65/0.85)*100)/100; "same formula as cadence-import").
 *
 * The gated staging write (price_trade / price_unit / our_price / price_source / price_updated_at)
 * fires ONLY after portal creds land (innovationsusa.com acct 58315) AND per-pattern LIST verified.
 */
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';
import path from 'node:path';

const OUTDIR = path.dirname(new URL(import.meta.url).pathname);
const PREFIXES = ['CSO', 'ENS', 'FLN', 'PRO', 'SKL', 'TTM', 'VCO'];

// ---- settled inputs (from prior TK-11041 sessions; not re-litigated here) -------------------
const DISCOUNT = 0.90;                 // net_cost = LIST * 0.90  (10% off)
const M1 = 0.65, M2 = 0.85;            // retail = net_cost / 0.65 / 0.85
const round2 = x => Math.round(x * 100) / 100;   // DW convention (nearest cent)
const netCost = list => round2(list * DISCOUNT);
const retail  = list => round2(netCost(list) / M1 / M2);

// ---- read the cohort from the Mac2-local mirror (READ-ONLY) ---------------------------------
const psql = sql => execFileSync('psql',
  ['-h', '/tmp', '-At', '-F', '\t', '-d', 'dw_unified', '-c', sql], { encoding: 'utf8' }).trim();

const rows = psql(`
  SELECT product_url, pattern_name, mfr_sku, color_name,
         coalesce(dw_sku,'') , coalesce(width,'') , coalesce(roll_length,'') ,
         coalesce(price_unit,'') ,
         (price_trade IS NOT NULL OR our_price IS NOT NULL OR price_retail IS NOT NULL) AS has_price
  FROM innovations_catalog
  WHERE substring(mfr_sku from '^[A-Za-z]+') IN (${PREFIXES.map(p => `'${p}'`).join(',')})
  ORDER BY product_url, mfr_sku;`)
  .split('\n').filter(Boolean).map(l => {
    const [product_url, pattern_name, mfr_sku, color_name, dw_sku, width, roll_length, price_unit, has_price] = l.split('\t');
    return { product_url, pattern_name, mfr_sku, color_name, dw_sku, width, roll_length, price_unit,
             has_price: has_price === 't' };
  });

// ---- integrity checks (flag anything that contradicts the brief) ----------------------------
const flags = [];
if (rows.length !== 39) flags.push(`COHORT COUNT IS ${rows.length}, EXPECTED 39`);
const priced = rows.filter(r => r.has_price);
if (priced.length !== 0) flags.push(`${priced.length}/39 ALREADY CARRY A PRICE: ${priced.map(r => r.mfr_sku).join(', ')}`);

// ---- group by the unique pattern page -------------------------------------------------------
const pagesMap = new Map();
for (const r of rows) {
  if (!pagesMap.has(r.product_url)) pagesMap.set(r.product_url, []);
  pagesMap.get(r.product_url).push(r);
}
const pages = [...pagesMap.entries()].map(([product_url, items]) => ({
  product_url,
  pattern_name: items[0].pattern_name,
  prefix: items[0].mfr_sku.match(/^[A-Za-z]+/)[0],
  width: [...new Set(items.map(i => i.width))].join(' | '),
  roll_length: [...new Set(items.map(i => i.roll_length))].join(' | '),
  item_count: items.length,
  colorways: items.map(i => ({ mfr_sku: i.mfr_sku, color_name: i.color_name })),
}));

// ---- worked PLACEHOLDER example -------------------------------------------------------------
const PLACEHOLDER_LIST = 84.50;   // NOT a real Innovations price — illustrative only
const example = {
  label: 'PLACEHOLDER — arithmetic demonstration only; NOT a real Innovations price',
  applies_to: 'e.g. CSO-001 Radiant (Costine) — any of the 39, formula is identical',
  LIST_per_yard: PLACEHOLDER_LIST,
  step1_net_cost: { formula: 'round(LIST * 0.90, 2)', value: netCost(PLACEHOLDER_LIST), writes_to: 'price_trade' },
  step2_retail:   { formula: 'round(net_cost / 0.65 / 0.85, 2)', value: retail(PLACEHOLDER_LIST), writes_to: 'our_price' },
  price_unit: 'yard',
};

// ---- assemble the plan ----------------------------------------------------------------------
const now = new Date().toISOString();
const plan = {
  ticket: 'TK-11041',
  kind: 'dry-run-reprice-plan',
  generated_at: now,
  read_only: true, no_db_write: true, no_shopify_write: true, no_email: true, no_deploy: true,
  source_of_truth: 'Mac2-local dw_unified.innovations_catalog (host=/tmp socket)',
  cohort: {
    n: rows.length, expected: 39, pattern_pages: pages.length, priced_now: priced.length,
    prefixes: PREFIXES,
    excluded: '12 WHL/Whistler items (404, no portal page, priced separately)',
  },
  integrity_flags: flags.length ? flags : ['NONE — 39 items, 7 pages, 0 priced (matches brief)'],
  formula: {
    settled_inputs: {
      discount_pct: 10,
      discount_confidence: 'HIGH — Sales Order O-111475',
      portal_basis: 'LIST (portal shows list price; discount applies on top)',
    },
    chain: 'net_cost = LIST * 0.90 ; retail = net_cost / 0.65 / 0.85',
    rounding: 'Math.round(x * 100) / 100 (nearest cent, 2 decimals)',
    rounding_source: 'scripts/innovations-reprice/stage-innov-reprice.mjs RETAIL() — "same formula as cadence-import"',
    free_variable: 'LIST — per-pattern list price, per linear yard, from innovationsusa.com trade portal (acct 58315)',
    price_unit: 'yard  (Innovations sold PER LINEAR YARD)',
  },
  one_remaining_input: 'per-pattern LIST price from the innovationsusa.com trade portal (acct 58315). '
    + 'Creds NOT yet pasted, so LIST is unknown — this plan is fully parameterized on it.',
  gated_write_fires_only_after: [
    'portal creds land (Steve pastes innovationsusa.com acct 58315 login)',
    'per-pattern LIST verified on the authenticated page (basis=LIST confirmed, not NET)',
    'then: stage price cols, then run the add-yard/reprice (stage-innov-reprice.mjs) — Steve-gated (customer-facing money op)',
  ],
  target_columns: {
    price_trade:      'net_cost = round(LIST * 0.90, 2)',
    price_unit:       "'yard'",
    our_price:        'retail = round(price_trade / 0.65 / 0.85, 2)',
    price_source:     "'innovations portal <YYYY-MM-DD>'",
    price_updated_at: 'now()',
  },
  retail_computed: false,
  retail_blocked_reason:
    'Per-pattern LIST price not yet available (portal creds unpasted). The two unknowns the pull '
    + 'script guards (DISCOUNT + BASIS) are SETTLED for this plan — discount 10% (O-111475), basis LIST '
    + '— so the ONLY remaining input is the LIST number itself. Once LIST lands per pattern: '
    + 'price_trade = round(LIST*0.90,2); our_price = round(price_trade/0.65/0.85,2).',
  worked_example: example,
  pattern_pages: pages,
  items: rows.map(r => ({
    mfr_sku: r.mfr_sku,
    pattern_name: r.pattern_name,
    color_name: r.color_name,
    product_url: r.product_url,
    dw_sku: r.dw_sku || null,
    width: r.width,
    roll_length: r.roll_length,
    price_unit_now: r.price_unit || null,
    // formula wired symbolically — LIST is the free variable, filled in only after the portal pull
    LIST_per_yard: null,
    net_cost_formula: 'round(LIST * 0.90, 2)  -> price_trade',
    retail_formula:  'round(net_cost / 0.65 / 0.85, 2)  -> our_price',
    retail_computed: false,
    retail_blocked_reason: 'awaiting per-pattern LIST from portal (creds unpasted)',
  })),
};

fs.writeFileSync(path.join(OUTDIR, 'tk11041-dryrun-reprice-plan.json'), JSON.stringify(plan, null, 2));

// ---- human-readable .md ---------------------------------------------------------------------
const money = v => '$' + v.toFixed(2);
let md = `# TK-11041 — Innovations dry-run reprice plan (READ-ONLY)

_Generated ${now} · Mac2-local dw_unified.innovations_catalog · NO db write · NO Shopify write · NO email · NO deploy_

## Cohort
- **${plan.cohort.n} items** across **${plan.cohort.pattern_pages} unique pattern pages** (prefixes ${PREFIXES.join('/')}).
- **${plan.cohort.priced_now}/39 currently carry a price** (price_trade / our_price / price_retail all NULL).
- Excluded: ${plan.cohort.excluded}.
- Integrity: ${plan.integrity_flags.join('; ')}.

## Formula (LIST is the one free variable)
\`\`\`
net_cost = LIST * 0.90               # discount SETTLED 10%, HIGH conf — Sales Order O-111475
retail   = net_cost / 0.65 / 0.85    # DW standard markup
round    = Math.round(x*100)/100     # nearest cent (2dp) — matches stage-innov-reprice.mjs / cadence-import
\`\`\`
- Portal basis = **LIST** (discount applies on top). Price unit = **yard** (sold per linear yard).
- Rounding source: \`scripts/innovations-reprice/stage-innov-reprice.mjs\` → \`RETAIL = c => Math.round((c/0.65/0.85)*100)/100\`.

## The ONE remaining input
**Per-pattern LIST price** from the innovationsusa.com trade portal (acct 58315). Creds not yet pasted.
The gated write (stage price cols → then the add-yard/reprice) fires **only after** creds land **and** LIST is verified per pattern.

## Worked EXAMPLE (PLACEHOLDER — not a real Innovations price)
LIST = ${money(PLACEHOLDER_LIST)}/yard →
net_cost = round(${money(PLACEHOLDER_LIST)} × 0.90) = **${money(example.step1_net_cost.value)}** (→ price_trade) →
retail = round(${money(example.step1_net_cost.value)} / 0.65 / 0.85) = **${money(example.step2_retail.value)}** (→ our_price)

## Target columns (staged only after LIST verified — GATED)
| column | value |
|---|---|
| price_trade | round(LIST × 0.90, 2) |
| price_unit | 'yard' |
| our_price | round(price_trade / 0.65 / 0.85, 2) |
| price_source | 'innovations portal <YYYY-MM-DD>' |
| price_updated_at | now() |

\`retail_computed: false\` carried on every row until LIST lands (matches the pull script's safety contract).

## The 7 pattern pages
| # | pattern | prefix | items | width | roll_length | product_url |
|---|---|---|---|---|---|---|
`;
pages.forEach((p, i) => {
  md += `| ${i + 1} | ${p.pattern_name} | ${p.prefix} | ${p.item_count} | ${p.width} | ${p.roll_length} | ${p.product_url} |\n`;
});

md += `\n## All 39 items (LIST column blank — filled only after the portal pull)\n`;
md += `| mfr_sku | pattern | colorway | width | roll_length | LIST | net_cost=LIST×0.90 | retail=net/0.65/0.85 |\n`;
md += `|---|---|---|---|---|---|---|---|\n`;
for (const r of rows) {
  md += `| ${r.mfr_sku} | ${r.pattern_name} | ${r.color_name} | ${r.width} | ${r.roll_length} | _(pending)_ | round(LIST×0.90) | round(net/0.65/0.85) |\n`;
}
md += `\n_39 rows. dw_sku is NULL in the local mirror for all 39 (identity/spec present; dw_sku not yet stamped here) — noted, not blocking the reprice math._\n`;

fs.writeFileSync(path.join(OUTDIR, 'tk11041-dryrun-reprice-plan.md'), md);

console.log(`cohort=${rows.length} pages=${pages.length} priced=${priced.length}`);
console.log(`flags: ${plan.integrity_flags.join('; ')}`);
console.log(`example: LIST ${money(PLACEHOLDER_LIST)} -> net ${money(example.step1_net_cost.value)} -> retail ${money(example.step2_retail.value)}`);
console.log(`wrote tk11041-dryrun-reprice-plan.json + .md`);