`mfr-selfcopy-gen.mjs`

140 lines
#!/usr/bin/env node
// mfr-selfcopy-gen.mjs — READ-ONLY generator of GATED dw_sku recovery plans for
// VERIFIED-REAL-MFR vendors (Carnegie, Stout). DTD verdict A, 2026-09-02, TK-10900.
//
// Recovers the canonical dw_sku from the NORMALIZED mfr_sku (strip the
// "-windows"-class product-type suffix). Two modes:
//   --mode blank         : fill rows whose dw_sku is blank            (blank-guarded)
//   --mode mint-correct  : overwrite rows whose dw_sku is a greenfield MINT
//                          (e.g. Carnegie DWAG-*) with the real code  (old-value-guarded)
//
// HARD RAILS (do not relax):
//   * Refuses any vendor NOT on classify.mjs MFR_SKU_REAL_ALLOWLIST (exit 2).
//   * COLLAPSE GUARD: if two DISTINCT mfr_sku normalize to the same candidate,
//     every row for that candidate is EXCLUDED (a colorway-collapse defect).
//   * COLLISION GUARD: a candidate already owned by a DIFFERENT active product's
//     dw_sku is EXCLUDED (routes to dedup, TK-10649), never written.
//   * NO database writes. Emits apply.sql / undo.sql / restore-map.json DRAFTS a
//     human fires later — the dw_sku write is Kamatera-canonical + HARD-GATED.
//
// Usage:
//   DWSKU_PSQL='ssh root@45.61.58.125 psql dw_unified' \
//     node mfr-selfcopy-gen.mjs --vendor carnegie --mode blank --out apply-plans-mfr
//   ... --mode mint-correct --mint-prefix DWAG ...

import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync, rmSync, existsSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { vendorAllowsMfrSelfCopy, normalizeMfrSku, MFR_SKU_REAL_ALLOWLIST } from './classify.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 MODE = arg('--mode', 'blank'); // blank | mint-correct
const MINT_PREFIX = arg('--mint-prefix', 'DWAG');
const OUT = arg('--out', join(HERE, 'apply-plans-mfr'));
const PSQL = (process.env.DWSKU_PSQL || 'psql -h /tmp -d dw_unified').split(/\s+/);
const CODE_SHAPE = /^[A-Za-z0-9][A-Za-z0-9._/-]{0,39}$/;
const sqlEscape = (v) => String(v).replace(/'/g, "''");

if (!vendorAllowsMfrSelfCopy(VENDOR)) {
  console.error(`[mfr-selfcopy-gen] REFUSING '${VENDOR}': not on MFR_SKU_REAL_ALLOWLIST (${[...MFR_SKU_REAL_ALLOWLIST].join(', ')}). mfr_sku fabricated — re-scrape instead.`);
  process.exit(2);
}
if (!['blank', 'mint-correct'].includes(MODE)) { console.error(`[mfr-selfcopy-gen] bad --mode ${MODE}`); process.exit(2); }

function run(sql) {
  const out = execFileSync(PSQL[0], [...PSQL.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));
}

// Active dw_sku code set (for the foreign-collision guard).
const activeCodes = new Set(run(
  `select distinct dw_sku from shopify_products where lower(coalesce(status,''))='active' and dw_sku is not null and btrim(dw_sku)<>''`,
).map((r) => r[0]));

// Target rows for this vendor + mode.
const where = MODE === 'blank'
  ? `(dw_sku is null or btrim(dw_sku)='')`
  : `dw_sku ~ '^${MINT_PREFIX}-'`;
const rows = run(
  `select coalesce(shopify_id,''), btrim(mfr_sku), coalesce(dw_sku,'') from shopify_products ` +
  `where lower(coalesce(status,''))='active' and vendor ilike '${sqlEscape(VENDOR)}%' and ${where} ` +
  `and mfr_sku is not null and btrim(mfr_sku)<>''`,
);

// Pass 1 — normalize + shape check; build candidate -> distinct source mfr set.
const candToMfrs = new Map();
const staged = [];
const skipped = { no_shopify_id: 0, bad_shape: 0 };
for (const [sid, mfr, dw] of rows) {
  if (!sid) { skipped.no_shopify_id++; continue; }
  const cand = normalizeMfrSku(mfr);
  if (!cand || !CODE_SHAPE.test(cand)) { skipped.bad_shape++; continue; }
  staged.push({ shopify_id: sid, mfr_sku: mfr, old_dw: dw, candidate: cand });
  if (!candToMfrs.has(cand)) candToMfrs.set(cand, new Set());
  candToMfrs.get(cand).add(mfr);
}

// Pass 2 — COLLAPSE + COLLISION guards.
//
// COLLAPSE: when the stripped base is reached by 2+ DISTINCT mfr_sku, stripping
// the product-type suffix would force two DISTINCT active products (e.g. the
// same Carnegie fabric offered as "-panels" AND "-windows") to SHARE one dw_sku
// — a cross-product uniqueness collision. The fix is NOT to drop them: fall back
// to the FULL mfr_sku (unstripped) as the candidate, which is still a real code
// and stays unique per application. We only truly EXCLUDE if even the full
// mfr_sku collides with a foreign active dw_sku (-> dedup, TK-10649).
const entries = [];
const excluded = { collision: [] };
let keptViaFullMfr = 0;
for (const e of staged) {
  let cand = e.candidate;
  if (candToMfrs.get(cand).size > 1) {
    // ambiguous base -> preserve uniqueness by keeping the full mfr_sku code.
    if (CODE_SHAPE.test(e.mfr_sku)) { cand = e.mfr_sku; keptViaFullMfr++; }
  }
  if (activeCodes.has(cand) && cand !== e.old_dw) { excluded.collision.push({ ...e, candidate: cand }); continue; }
  entries.push({ ...e, candidate: cand });
}

const HEADER =
  `-- GATED -- canonical Kamatera dw_unified write. Do NOT run automatically.\n` +
  `-- mfr_sku recovery (mode=${MODE}) for verified-real vendor '${VENDOR}'. Recovers real code, no mint. TK-10900.\n`;

let applySql, undoSql, restoreMap;
if (MODE === 'blank') {
  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';
  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';
  restoreMap = entries.map((e) => ({ shopify_id: e.shopify_id, mfr_sku: e.mfr_sku, column: 'dw_sku', old: null, new: e.candidate }));
} else { // mint-correct: guard on the EXACT old mint value so a concurrent change can't be clobbered.
  applySql = HEADER + entries.map((e) =>
    `UPDATE shopify_products SET dw_sku='${sqlEscape(e.candidate)}' WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND dw_sku='${sqlEscape(e.old_dw)}';`).join('\n') + '\n';
  undoSql = HEADER + entries.map((e) =>
    `UPDATE shopify_products SET dw_sku='${sqlEscape(e.old_dw)}' WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND dw_sku='${sqlEscape(e.candidate)}';`).join('\n') + '\n';
  restoreMap = entries.map((e) => ({ shopify_id: e.shopify_id, mfr_sku: e.mfr_sku, column: 'dw_sku', old: e.old_dw, new: e.candidate }));
}

const dir = join(OUT, `${VENDOR}-${MODE}`);
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');
if (excluded.collision.length) writeFileSync(join(dir, 'EXCLUDED-collision.json'), JSON.stringify(excluded.collision, null, 2) + '\n');

const summary = {
  ticket: 'TK-10900', vendor: VENDOR, mode: MODE, mint_prefix: MODE === 'mint-correct' ? MINT_PREFIX : undefined,
  target_rows: rows.length, staged: staged.length, planned_statements: entries.length,
  kept_via_full_mfr_uniqueness: keptViaFullMfr,
  excluded_collision: excluded.collision.length,
  skipped, active_code_count: activeCodes.size,
  note: 'NOTHING executed. apply.sql is a GATED draft; dw_sku is Kamatera-canonical + hard-gated.',
};
writeFileSync(join(OUT, `SUMMARY-${VENDOR}-${MODE}.json`), JSON.stringify(summary, null, 2) + '\n');
console.log(JSON.stringify(summary, null, 2));