`pilot.mjs`

106 lines
#!/usr/bin/env node
// TK-10875 — BOUNDED RECOVERY PILOT (DRY-RUN ONLY), Wolf Gordon cohort.
//
// EMPIRICALLY-CORRECTED (verified against LIVE Shopify, 2026-08-30):
// The 224 cost-joined WG "unbuyable" products are NOT sample-only, and NOT
// already-sellable. Each has a SINGLE variant with option1='Sample' priced at
// the ROLL RETAIL (e.g. $54.75, $343.80) — not the $4.25 memo price. This is the
// "RR-defect" class already repaired for Ronald Redding + Cole & Son under
// TK-10875-130: a lone Sample-labeled variant mispriced at roll retail, with no
// real $4.25 sample and no clean "Sold Per Roll" variant.
//
// The dw_unified MIRROR cannot detect this: it carries no `option1`, and its
// price is stale (dead sync job — one product read $180.90 in the mirror vs
// $343.80 live). So this plan is ADVISORY: a real executor MUST read each
// product live and recompute retail from STAGING trade cost (not the mirror).
//
// Recovery action (GATED — NOT executed here): for each product,
//   - relabel the lone variant option1 'Sample' -> 'Sold Per Roll',
//     price = trade_cost/0.65/0.85 (DW markup), SKU = numeric DW-SKU (no mint);
//   - add a real Sample variant at $4.25 (SKU <dwsku>-Sample).
// This is the exact reversible, rollback-mapped, ledgered pattern from
// TK-10875-130 (dw-add-sellable-variant / dw-data-repair rollback-all.mjs).

import { writeFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
import { queryRows } from './lib/db.mjs';

const HERE = dirname(fileURLToPath(import.meta.url));
const COHORT = 'Wolf Gordon';
const SAMPLE_PRICE = 4.25;

const rows = queryRows(`
  SELECT
    wg.shopify_id,                          -- REAL Shopify product id (gid://…)
    wg.id                                   AS mirror_row_id,
    wg.sku                                  AS dw_numeric_sku,
    wg.mfr_sku,
    wg.title,
    wg.variant_count,
    wg.min_variant_price                    AS mirror_price_STALE,
    c.pattern_name,
    c.price_trade                           AS trade_cost,
    round(c.price_trade/0.65/0.85, 2)       AS computed_roll_retail
  FROM shopify_products wg
  JOIN wolf_gordon_catalog c ON c.dw_sku = wg.sku
  WHERE wg.vendor='${COHORT}'
    AND lower(wg.status)='active'
    AND (wg.has_product_variant IS DISTINCT FROM true)
    AND c.price_trade > 0
  ORDER BY wg.title`);

const plan = rows.map(r => ({
  shopify_id: r.shopify_id,
  title: r.title,
  pattern: r.pattern_name,
  mfr_sku: r.mfr_sku,
  proposed_roll_sku: r.dw_numeric_sku,               // reuse existing numeric DW-SKU (no mint)
  proposed_roll_retail: Number(r.computed_roll_retail), // from STAGING trade cost, not the stale mirror
  proposed_sample_sku: `${r.dw_numeric_sku}-Sample`,
  proposed_sample_price: SAMPLE_PRICE,
  mirror_price_stale: Number(r.mirror_price_STALE ?? 0),
  existing_variant_count: Number(r.variant_count ?? 0),
  action: 'RESTRUCTURE_LONE_SAMPLE_VARIANT', // GATED — customer-facing Shopify write
  live_confirm_required: true,               // executor must GET the live variant first
}));

const singleVariant = plan.filter(p => p.existing_variant_count === 1);

const report = {
  ticket: 'TK-10875',
  cohort: COHORT,
  mode: 'DRY-RUN — no Shopify or dw_unified writes performed',
  generated_at: new Date().toISOString(),
  empirical_verification: 'Live Shopify GET on 4 WG products (price range $54.75–$343.80) — ALL show a single option1=Sample variant priced at roll retail (RR-defect). Mirror lacks option1 and is price-stale (13 days).',
  retail_formula: 'trade_cost / 0.65 / 0.85 (recompute from staging at execution; do NOT trust mirror price)',
  recovery_pattern: 'TK-10875-130 variant-restructure (relabel lone Sample->Sold Per Roll + add $4.25 Sample); reversible via rollback-all.mjs, ledgered',
  gating: 'GATED — customer-facing Shopify variant write. Route to vp-dw-commerce. Draft only; NOT executed here.',
  counts: {
    recoverable_via_restructure: plan.length,
    single_variant_confirmed: singleVariant.length,
  },
  plan,
};

const jsonOut = join(HERE, 'data', 'wolf-gordon-dryrun.json');
const csvOut  = join(HERE, 'data', 'wolf-gordon-dryrun.csv');
writeFileSync(jsonOut, JSON.stringify(report, null, 2));

const cols = ['shopify_id','proposed_roll_sku','mfr_sku','title','pattern','trade_cost','proposed_roll_retail','proposed_sample_price','mirror_price_stale','existing_variant_count','action','live_confirm_required'];
// re-derive trade_cost onto plan rows for the CSV
plan.forEach((p,i)=>{ p.trade_cost = Number(rows[i].trade_cost); });
const esc = v => `"${String(v ?? '').replace(/"/g,'""')}"`;
writeFileSync(csvOut, [cols.join(','), ...plan.map(p => cols.map(c => esc(p[c])).join(','))].join('\n') + '\n');

console.log(`\n================ DRY-RUN — NO WRITES PERFORMED ================`);
console.log(`Cohort: ${COHORT}`);
console.log(`Recoverable via RR-restructure: ${plan.length}  (all single-variant: ${singleVariant.length})`);
console.log(`Verified: live Shopify shows lone option1=Sample @ roll retail (RR-defect).`);
console.log(`Plan written: ${jsonOut}`);
console.log(`             ${csvOut}`);
console.log(`\nGATED next step (drafted, NOT executed): route the ${plan.length}-row plan to`);
console.log(`vp-dw-commerce for a tested, reversible, ledgered variant restructure — executor`);
console.log(`MUST read each product live + recompute retail from staging (mirror price is stale).`);
console.log(`==============================================================\n`);