`staging-link-gen.mjs`

101 lines
#!/usr/bin/env node
// staging-link-gen.mjs — READ-ONLY generator of GATED staging-link apply plans.
//
// Recovers canonical dw_sku for rows that have a real mfr_sku but no sku, by
// LINKING mfr_sku -> the vendor staging catalog's already-assigned dw_sku (the
// code the scraper gave it). Recovers an EXISTING code — never mints.
//
// Cross-machine: reads the blank rows from Kamatera (canonical shopify_products)
// and the mfr->dw_sku map from the Mac2 staging catalog (staging is Mac2-canonical).
// Emits apply-plans-staging-link/<vendor>/{apply.sql,undo.sql,restore-map.json}
// keyed on shopify_id + blank-guarded. NEVER writes a DB. Parent: TK-10896.
//
// Usage: node staging-link-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 { baseCode } from './match-helpers.mjs';
import { vendorAllowsMfrSelfCopy, 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');

// HARD MACHINE GATE (DTD verdict A, TK-10900): this generator recovers dw_sku by
// keying on mfr_sku, so it may run ONLY for a verified-real-mfr vendor. Refuse
// outright for anyone else — a fabricated mfr_sku (Maharam MH-*, CMO_*) must be
// re-scraped, never linked into a canonical dw_sku.
if (!vendorAllowsMfrSelfCopy(VENDOR)) {
  console.error(
    `[staging-link-gen] REFUSING vendor '${VENDOR}': not on MFR_SKU_REAL_ALLOWLIST ` +
    `(${[...MFR_SKU_REAL_ALLOWLIST].join(', ')}). mfr_sku is potentially fabricated — re-scrape instead.`,
  );
  process.exit(2);
}
const CATALOG = arg('--catalog', `${VENDOR}_catalog`);
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-staging-link'));
const CODE_SHAPE = /^[A-Za-z0-9][A-Za-z0-9._/-]{0,39}$/;
const sqlEscape = (v) => String(v).replace(/'/g, "''");

function run(cmd, sql) {
  // SQL via stdin (never -c) so it works over ssh; -F/-R separators.
  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));
}

// 1. Kamatera: blank-dw_sku active rows for this vendor that have a real mfr_sku.
const kamRows = run(KAM,
  `SELECT coalesce(shopify_id,''), btrim(mfr_sku) FROM shopify_products ` +
  `WHERE lower(coalesce(status,''))='active' AND (dw_sku IS NULL OR btrim(dw_sku)='') ` +
  `AND vendor ILIKE '${sqlEscape(VENDOR)}%' AND mfr_sku IS NOT NULL AND btrim(mfr_sku)<>'';`);

// 2. Mac2 staging catalog: mfr_sku -> dw_sku map.
const map = new Map();
for (const [mfr, dw] of run(['psql', '-h', '/tmp', '-d', 'dw_unified'],
  `SELECT btrim(mfr_sku), btrim(dw_sku) FROM ${CATALOG} WHERE dw_sku IS NOT NULL AND btrim(dw_sku)<>'';`)) {
  // Canonical dw_sku is the shared base identity; staging catalogs can carry
  // sell-unit suffixes such as -Sample. Normalize before planning so verify and
  // undo agree with canonical storage.
  if (!map.has(mfr)) map.set(mfr, baseCode(dw));
}

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

const entries = [];
const skipped = { no_map: 0, bad_shape: 0, minted_residue: 0, no_shopify_id: 0 };
for (const [sid, mfr] of kamRows) {
  if (!sid) { skipped.no_shopify_id++; continue; }
  const cand = map.get(mfr);
  if (!cand) { skipped.no_map++; continue; }
  if (!CODE_SHAPE.test(cand)) { skipped.bad_shape++; continue; }
  if (minted.has(cand.toUpperCase())) { skipped.minted_residue++; continue; } // never re-instate a reverted mint
  entries.push({ shopify_id: sid, mfr_sku: mfr, candidate: cand });
}

const HEADER =
  '-- GATED -- canonical Kamatera dw_unified write. Do NOT run automatically.\n' +
  '-- Staging-link recovery: mfr_sku -> catalog dw_sku (existing code, no mint). TK-10896.\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, mfr_sku: e.mfr_sku, 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');

const summary = { ticket: 'TK-10896', vendor: VENDOR, catalog: CATALOG, kam_blank_mfr_rows: kamRows.length, linked: entries.length, statements: entries.length, skipped, note: 'NOTHING executed. apply.sql is a GATED draft.' };
writeFileSync(join(OUT, `SUMMARY-${VENDOR}.json`), JSON.stringify(summary, null, 2) + '\n');
console.log(JSON.stringify(summary, null, 2));