← back to Dw Sku Integrity

content-match-gen.mjs

170 lines

#!/usr/bin/env node
// content-match-gen.mjs — READ-ONLY generator of GATED content-match apply plans (TK-10900, Program A).
//
// Recovers canonical dw_sku for blank Shopify rows that have NO sku and NO mfr_sku, by CONTENT-MATCHing
// the Shopify title -> a vendor staging-catalog row -> that row's EXISTING real code. Recovers an
// existing code — never mints. Cross-machine: blank rows from Kamatera (canonical shopify_products),
// the map from the Mac2 <vendor>_catalog (staging is Mac2-canonical).
//
// UNIFIED CONCAT MATCHER (v2): the match key is the whole product-identity string, NOT a parsed
// pattern/color split (which was fragile — it only worked for numeric colors and 100%-missed
// color-name vendors). Both sides build a comparable key:
//   * catalog key = norm(pattern_name + ' ' + color)   [color = color_number|color_name],
//                   or norm(title) when the catalog has only a title column.
//   * shopify key = norm(title with the vendor name stripped off either end).
// This reproduces Carnegie (numeric) AND recovers Maharam/Knoll/etc (color-name) with one mode.
//
// SAFETY RAILS (unchanged): mint-catalog vendors (carnegie/maharam/cmo paris/stout) have greenfield-mint
// dw_sku -> use mfr_sku instead; base-code collapse strips only trailing '-<alpha>' type-suffixes so a
// numeric-hyphenated real code survives; greenfield-mint + reverted-ledger codes excluded; a write is
// emitted ONLY when the key resolves to exactly ONE base code whose product_type bucket is compatible
// with the Shopify row's bucket (the Abbey-61 cross-class guard). Everything else -> review-queue, never
// a best guess. Emits apply-plans-content-match/<vendor>/{apply,undo}.sql + restore-map + review-queue,
// shopify_id-keyed + blank-guarded. NEVER writes a DB. Parent: TK-10900 (under TK-10896).
//
// Usage: node content-match-gen.mjs --vendor Carnegie --catalog carnegie_catalog \
//          [--kam 'ssh root@45.61.58.125 psql dw_unified'] [--ledger /tmp/_ledcodes.txt]

import { execFileSync } from 'node:child_process';
import { readFileSync, writeFileSync, mkdirSync, existsSync, rmSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { CODE_SHAPE, GREENFIELD_MINT, MINT_CATALOG, norm, scrubTitle, baseCode, bucket, stripVendor, isCrossClass } from './match-helpers.mjs';

const HERE = dirname(fileURLToPath(import.meta.url));
const US = '\x1f', RS = '\x1e';
const arg = (n, d = null) => { const i = process.argv.indexOf(n); return i >= 0 ? process.argv[i + 1] : d; };
const VENDOR = arg('--vendor', 'Carnegie');
const CATALOG = arg('--catalog', null);
const KAM = (arg('--kam', 'ssh root@45.61.58.125 psql dw_unified')).split(/\s+/);
const LEDGER = arg('--ledger', '/tmp/_ledcodes.txt');
const OUT = arg('--out', join(HERE, 'apply-plans-content-match'));
const sqlEscape = (v) => String(v).replace(/'/g, "''"); // SQL-literal escape (tool-specific; stays here)

function runPsql(cmd, sql) {
  const out = execFileSync(cmd[0], [...cmd.slice(1), '-tA', '-F', US, '-R', RS], { input: sql, maxBuffer: 1 << 30, encoding: 'utf8' });
  return out.split(RS).map((r) => r.replace(/\n$/, '')).filter(Boolean).map((r) => r.split(US));
}
const LOCAL = ['psql', '-h', '/tmp', '-d', 'dw_unified'];

// 0. Resolve the catalog + detect columns (schema-adaptive).
const catalog = CATALOG || `${VENDOR.toLowerCase().replace(/\s+/g, '_')}_catalog`;
const cols = new Set(runPsql(LOCAL,
  `SELECT column_name FROM information_schema.columns WHERE table_name='${sqlEscape(catalog)}';`).map((r) => r[0]));
const useMfr = MINT_CATALOG.has(VENDOR.toLowerCase());
const colColumn = cols.has('color_number') ? 'color_number' : (cols.has('color_name') ? 'color_name' : null);
const hasProductType = cols.has('product_type');
const hasPatternColor = cols.has('pattern_name') && colColumn;
const keyMode = hasPatternColor ? 'concat' : (cols.has('title') ? 'title' : null);

function deferUnsupported(reason, detail) {
  mkdirSync(OUT, { recursive: true });
  const s = { ticket: 'TK-10900', program: 'A-content-match', vendor: VENDOR, catalog, status: 'deferred_unsupported_schema', reason, detail, columns: [...cols], matched: 0, statements: 0, note: 'NOTHING executed. No usable match key in this catalog.' };
  writeFileSync(join(OUT, `SUMMARY-${VENDOR}.json`), JSON.stringify(s, null, 2) + '\n');
  console.log(JSON.stringify(s, null, 2)); process.exit(0);
}
mkdirSync(OUT, { recursive: true });
const codeCol = useMfr ? 'mfr_sku' : 'dw_sku';
if (!cols.has(codeCol)) deferUnsupported('no_code_source', `needs '${codeCol}' (${useMfr ? 'mint-catalog vendor' : 'clean-catalog vendor'})`);
if (!keyMode) deferUnsupported('no_match_key', 'needs pattern_name+color OR a title column');

// 1. Reverted-mint exclude list.
const minted = new Set(existsSync(LEDGER) ? readFileSync(LEDGER, 'utf8').split('\n').map((s) => s.trim().toUpperCase()).filter(Boolean) : []);

// 2. Build the catalog map: identity key -> Map(baseCode -> Set(bucket)).
const selCols = keyMode === 'concat'
  ? `coalesce(pattern_name,''), coalesce(${colColumn},''), ${hasProductType ? "coalesce(product_type,'')" : "''"}, coalesce(dw_sku,''), coalesce(mfr_sku,'')`
  : `coalesce(title,''), '', ${hasProductType ? "coalesce(product_type,'')" : "''"}, coalesce(dw_sku,''), coalesce(mfr_sku,'')`;
const catRows = runPsql(LOCAL, `SELECT ${selCols} FROM ${catalog};`);
const map = new Map();
let catUsable = 0, catExcluded = 0;
for (const [a, b, ptype, dw, mfr] of catRows) {
  const raw = useMfr ? mfr : dw;
  if (!raw || !raw.trim()) continue;
  // Catalog dw_sku can itself carry a sell-unit suffix (for example
  // DWKE-41415-Sample). Canonical dw_sku is the shared base identity for the
  // sellable and sample rows, so normalize both code sources. The previous
  // verbatim dw_sku path produced a restore-map candidate that disagreed with
  // the canonical DB normalization trigger and made post-apply verification
  // fail even though the stored value was correct.
  const code = baseCode(raw);
  if (!CODE_SHAPE.test(code) || GREENFIELD_MINT.test(code) || minted.has(code.toUpperCase())) { catExcluded++; continue; }
  const key = keyMode === 'concat' ? scrubTitle(norm(a) + ' ' + norm(b)) : scrubTitle(a);
  if (!key) continue;
  if (!map.has(key)) map.set(key, new Map());
  const codes = map.get(key);
  if (!codes.has(code)) codes.set(code, new Set());
  codes.get(code).add(bucket(ptype));
  catUsable++;
}

// 3. Shopify blank rows (canonical). Key = vendor-stripped title.
const shopRows = runPsql(KAM,
  `SELECT coalesce(shopify_id,''), coalesce(title,''), coalesce(product_type,'') FROM shopify_products ` +
  `WHERE lower(coalesce(status,''))='active' AND (dw_sku IS NULL OR btrim(dw_sku)='') ` +
  `AND vendor ILIKE '${sqlEscape(VENDOR)}%';`);

// Contrarian pass-2 guard: title-mode catalogs have no product_type, so the cross-class bucket guard
// (isCrossClass) can't run. That is only class-SAFE when the vendor's blank rows are a single product
// class (no fabric-vs-wall ambiguity possible — e.g. Vahallan, 100% Wallcovering). A MULTI-class
// title-mode vendor could silently take a wrong-class code, so defer it to a fitted/manual path.
if (keyMode === 'title' && !hasProductType) {
  const shopBuckets = new Set(shopRows.map(([, , pt]) => bucket(pt)).filter((b) => b !== 'other'));
  if (shopBuckets.size > 1) deferUnsupported('title_mode_multiclass_no_bucket_guard',
    `title-mode w/o catalog product_type but Shopify rows span ${shopBuckets.size} classes (${[...shopBuckets].join(',')}) — cross-class guard cannot run; not safe to auto-emit`);
}

const entries = [], review = [];
const stat = { shopify_blank: shopRows.length, no_shopify_id: 0, no_map: 0, ambiguous: 0, cross_class: 0, matched: 0 };
for (const [sid, title, ptype] of shopRows) {
  if (!sid) { stat.no_shopify_id++; continue; }
  const key = scrubTitle(stripVendor(title, VENDOR));
  const codes = map.get(key);
  if (!codes) { stat.no_map++; review.push({ shopify_id: sid, title, key, reason: 'no_catalog_match' }); continue; }
  let cand = [...codes.keys()];
  if (cand.length > 1) { // multi base code -> disambiguate by product_type bucket
    const want = bucket(ptype);
    const filtered = cand.filter((c) => codes.get(c).has(want));
    if (filtered.length === 1) cand = filtered;
  }
  if (cand.length !== 1) { stat.ambiguous++; review.push({ shopify_id: sid, title, key, candidates: [...codes.keys()], reason: 'ambiguous_multi_code' }); continue; }
  // Bucket-compatibility precondition (single-candidate keys too): never write wall onto fabric / vice versa.
  const shopBucket = bucket(ptype);
  const catBuckets = codes.get(cand[0]);
  if (isCrossClass(shopBucket, catBuckets)) {
    stat.cross_class++;
    review.push({ shopify_id: sid, title, key, candidate: cand[0], catalog_buckets: [...catBuckets], shopify_bucket: shopBucket, reason: 'cross_class_mismatch' });
    continue;
  }
  entries.push({ shopify_id: sid, title, candidate: cand[0] });
  stat.matched++;
}

// 4. Emit GATED artifacts.
const HEADER =
  '-- GATED -- canonical Kamatera dw_unified write. Do NOT run automatically.\n' +
  `-- Content-match recovery (${keyMode}-key -> ${catalog} ${useMfr ? 'mfr_sku(base)' : 'dw_sku'}, existing code, no mint). TK-10900.\n`;
const applySql = HEADER + entries.map((e) =>
  `UPDATE shopify_products SET dw_sku='${sqlEscape(e.candidate)}' WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND (dw_sku IS NULL OR btrim(dw_sku)='');`).join('\n') + '\n';
const undoSql = HEADER + entries.map((e) =>
  `UPDATE shopify_products SET dw_sku=NULL WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND dw_sku='${sqlEscape(e.candidate)}';`).join('\n') + '\n';
const restoreMap = entries.map((e) => ({ shopify_id: e.shopify_id, title: e.title, column: 'dw_sku', old: null, new: e.candidate }));

const dir = join(OUT, VENDOR);
if (existsSync(dir)) rmSync(dir, { recursive: true, force: true });
mkdirSync(dir, { recursive: true });
writeFileSync(join(dir, 'apply.sql'), applySql);
writeFileSync(join(dir, 'undo.sql'), undoSql);
writeFileSync(join(dir, 'restore-map.json'), JSON.stringify(restoreMap, null, 2) + '\n');
writeFileSync(join(dir, 'review-queue.json'), JSON.stringify(review, null, 2) + '\n');

const summary = {
  ticket: 'TK-10900', program: 'A-content-match', vendor: VENDOR, catalog, status: 'supported', key_mode: keyMode,
  color_column: colColumn, code_source: useMfr ? 'mfr_sku(base-stripped)' : 'dw_sku(base-stripped)', bucket_guard: hasProductType,
  catalog_usable: catUsable, catalog_excluded_mint: catExcluded, ...stat, review_queue: review.length, statements: entries.length,
  match_pct: stat.shopify_blank ? (100 * stat.matched / stat.shopify_blank).toFixed(1) + '%' : '0%',
  note: 'NOTHING executed. apply.sql is a GATED draft; review-queue.json is NOT written.',
};
writeFileSync(join(OUT, `SUMMARY-${VENDOR}.json`), JSON.stringify(summary, null, 2) + '\n');
console.log(JSON.stringify(summary, null, 2));