`tk10978-scalamandre/build-plan.mjs`

138 lines
#!/usr/bin/env node
// TK-10978 — Scalamandre ADD-ROLL recovery plan builder (READ-ONLY).
//
// Reads data/scalamandre-exact-recovery.csv (156 rows). Builds a CLEAN,
// deduped, match-sanity-annotated plan of products to ADD a "Sold Per Roll"
// variant to. Recomputes roll retail LIVE from scalamandre_catalog by
// catalog_dw_sku (never trusts the CSV/mirror number).
//
// HARD EXCLUSIONS (skip, never price):
//   - price-conflict products: any shopify_num_id that appears on >1 CSV row
//     (the CSV carries 2 candidate catalog matches at different prices for 6
//      products → cost is ambiguous). These 6 (12 rows) are dropped.
//   - a row whose catalog_dw_sku is blank.
//   - a catalog_dw_sku not found in scalamandre_catalog with price_trade>0.
//
// MATCH SANITY: carries the live-title pattern/color AND the catalog
// pattern_name/color_name so the executor (and this builder) can confirm the
// content match is real before pricing. Builder flags mismatches.
//
// Output: data/plan.json (clean actionable set) + data/excluded.json.
// This script performs NO Shopify or DB writes.
import { readFileSync, writeFileSync } from 'node:fs';
import { queryRows } from '../lib/db.mjs';

const HERE = new URL('.', import.meta.url).pathname;
const CSV = `${HERE}../data/scalamandre-exact-recovery.csv`;

// --- parse CSV (simple, fields have no embedded commas in the id/sku/dwsku cols) ---
const lines = readFileSync(CSV, 'utf8').trim().split('\n');
const header = lines[0].split(',');
const rows = lines.slice(1).map(line => {
  // title can't contain a comma in this data (verified: " - Color | Scalamandre"), so a plain split is safe.
  const parts = line.split(',');
  // header: shopify_num_id,shopify_sku,title,catalog_dw_sku,price_trade,computed_roll_retail
  return {
    shopify_num_id: parts[0],
    shopify_sku: parts[1],
    title: parts[2],
    catalog_dw_sku: parts[3],
    price_trade_csv: parts[4],
    computed_roll_retail_csv: parts[5],
  };
});

// --- identify price-conflict products (id appears on >1 row) ---
const byId = new Map();
for (const r of rows) { byId.set(r.shopify_num_id, (byId.get(r.shopify_num_id) || 0) + 1); }
const conflictIds = new Set([...byId.entries()].filter(([, n]) => n > 1).map(([id]) => id));

// --- normalize helpers for match sanity ---
const norm = s => (s || '').toUpperCase().replace(/[^A-Z0-9 ]/g, ' ').replace(/\s+/g, ' ').trim();
// live title -> pattern (before first ' - ') + color (last segment before ' | ')
function parseTitle(title) {
  const beforePipe = title.split('|')[0].trim();           // "Adelaide Beaded Sisal - Burnished Gold"
  const segs = beforePipe.split(' - ').map(s => s.trim());
  const color = segs[segs.length - 1];
  const pattern = segs.slice(0, -1).join(' - ');            // handles "Abyssal - Mural"
  return { pattern, color };
}

const excluded = [];
const candidateRows = rows.filter(r => {
  if (conflictIds.has(r.shopify_num_id)) { return false; }  // handled below in bulk
  if (!r.catalog_dw_sku || r.catalog_dw_sku.trim() === '') {
    excluded.push({ ...r, reason: 'blank catalog_dw_sku' }); return false;
  }
  return true;
});
// record the conflict exclusions once per id
for (const id of conflictIds) {
  const rs = rows.filter(r => r.shopify_num_id === id);
  excluded.push({ shopify_num_id: id, title: rs[0].title, reason: 'price-conflict (multiple candidate catalog matches at different prices)',
    candidates: rs.map(r => ({ catalog_dw_sku: r.catalog_dw_sku || '(blank)', price_trade: r.price_trade_csv })) });
}

// --- pull LIVE catalog truth for the candidate DWSC skus ---
const dwscList = [...new Set(candidateRows.map(r => r.catalog_dw_sku))];
const inClause = dwscList.map(s => `'${s.replace(/'/g, "''")}'`).join(',');
const catRows = queryRows(`
  SELECT dw_sku, pattern_name, color_name, price_trade,
         round(price_trade/0.65/0.85, 2) AS roll_retail
  FROM scalamandre_catalog
  WHERE dw_sku IN (${inClause}) AND price_trade > 0`);
const catBy = new Map(catRows.map(c => [c.dw_sku, c]));

const plan = [];
for (const r of candidateRows) {
  const cat = catBy.get(r.catalog_dw_sku);
  if (!cat) { excluded.push({ ...r, reason: 'catalog_dw_sku not found with price_trade>0' }); continue; }

  const rollSku = r.shopify_sku.replace(/-Sample$/i, '');   // DWA-89268-Sample -> DWA-89268
  const { pattern: tPat, color: tColor } = parseTitle(r.title);
  const patMatch = norm(cat.pattern_name).includes(norm(tPat)) || norm(tPat).includes(norm(cat.pattern_name));
  const colMatch = norm(cat.color_name) === norm(tColor) || norm(cat.color_name).includes(norm(tColor)) || norm(tColor).includes(norm(cat.color_name));

  plan.push({
    pid: r.shopify_num_id,
    title: r.title,
    sampleSku: r.shopify_sku,             // existing $4.25 Sample variant SKU (untouched)
    rollSku,                              // numeric DW-SKU — NO mint
    catalog_dw_sku: r.catalog_dw_sku,
    price_trade: Number(cat.price_trade),
    rollRetail: Number(cat.roll_retail), // LIVE-recomputed, authoritative
    csv_retail: Number(r.computed_roll_retail_csv),
    title_pattern: tPat, title_color: tColor,
    cat_pattern: cat.pattern_name, cat_color: cat.color_name,
    patMatch, colMatch,
    matchOk: patMatch && colMatch,
  });
}

// sanity: retail recompute must equal the CSV (proves no drift) — flag if not
for (const p of plan) {
  p.retail_matches_csv = Math.abs(p.rollRetail - p.csv_retail) < 0.02;
}

const mismatches = plan.filter(p => !p.matchOk);
const retailDrift = plan.filter(p => !p.retail_matches_csv);

const report = {
  ticket: 'TK-10978',
  generated_at: new Date().toISOString(),
  mode: 'READ-ONLY plan builder — no writes',
  csv_rows: rows.length,
  distinct_products: byId.size,
  conflict_products_excluded: conflictIds.size,
  clean_actionable: plan.length,
  match_mismatches: mismatches.length,
  retail_drift_vs_csv: retailDrift.length,
};
console.log(JSON.stringify(report, null, 2));
if (mismatches.length) { console.log('\nMATCH MISMATCHES (will be skipped at execute):'); mismatches.forEach(m => console.log(`  ${m.pid} "${m.title}"  title(${m.title_pattern}/${m.title_color}) vs cat(${m.cat_pattern}/${m.cat_color})`)); }
if (retailDrift.length) { console.log('\nRETAIL DRIFT vs CSV:'); retailDrift.forEach(m => console.log(`  ${m.pid} live=${m.rollRetail} csv=${m.csv_retail}`)); }

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} clean products; excluded.json: ${excluded.length} rows`);