`build-zoffany-durable.mjs`

70 lines
#!/usr/bin/env node
// Durable Zoffany mfr_sku recovery builder (TK-10677) — 2026-08-25
// Reads the verified CSV and emits:
//   1. zoffany_durable_apply.sql   — idempotent, guarded, transactional UPDATE-by-handle (runs on Kamatera canonical AND Mac2 mirror)
//   2. zoffany_restore.sql          — reversible undo (sets the 24 mfr_sku back to '')
//   3. prints the exact fire commands
// This script only WRITES LOCAL FILES — no DB write, no network. Safe/reversible/$0.
import fs from 'node:fs';
import path from 'node:path';

const DIR = path.dirname(new URL(import.meta.url).pathname);
const csv = fs.readFileSync(path.join(DIR, 'zoffany_24_verified_20260824.csv'), 'utf8').trim().split('\n');
const header = csv[0].split(',');
const iHandle = header.indexOf('handle'), iMfr = header.indexOf('mfr_sku'), iDw = header.indexOf('dw_sku');
const rows = csv.slice(1).map(l => { const c = l.split(','); return { handle: c[iHandle], mfr: c[iMfr], dw: c[iDw] }; });

// sanity: every row has a plausible mfr code + handle
const bad = rows.filter(r => !r.handle || !/^Z[A-Z]{2,4}\d{4,6}$/.test(r.mfr));
if (bad.length) { console.error('REFUSING — malformed rows:', bad); process.exit(1); }
if (rows.length !== 24) { console.error(`REFUSING — expected 24 rows, got ${rows.length}`); process.exit(1); }

const esc = s => "'" + String(s).replace(/'/g, "''") + "'";
const values = rows.map(r => `    (${esc(r.handle)}, ${esc(r.mfr)})`).join(',\n');
const mfrList = rows.map(r => esc(r.mfr)).join(',');

const applySql = `-- DURABLE Zoffany mfr_sku recovery — TK-10677 — generated 2026-08-25
-- Runs identically on Kamatera-canonical dw_unified AND the Mac2 mirror.
-- IDEMPOTENT + GUARDED: only fills rows that are still null/empty, only these 24 handles, only ACTIVE Zoffany.
-- A re-run after success updates 0 rows. Reversible via zoffany_restore.sql.
BEGIN;

WITH recover(handle, mfr_sku) AS (
  VALUES
${values}
)
UPDATE shopify_products sp
SET    mfr_sku = r.mfr_sku
FROM   recover r
WHERE  sp.handle = r.handle
  AND  sp.vendor = 'Zoffany'
  AND  sp.status = 'ACTIVE'
  AND  (sp.mfr_sku IS NULL OR sp.mfr_sku = '');

-- verify: how many of the 24 target handles now carry their mfr code
SELECT count(*) AS zoffany_with_mfr_now
FROM   shopify_products
WHERE  vendor='Zoffany' AND status='ACTIVE' AND mfr_sku IN (${mfrList});

COMMIT;
`;

const restoreSql = `-- REVERSIBLE UNDO for the durable Zoffany mfr recovery — TK-10677
-- Sets the 24 recovered mfr_sku back to '' (only rows that currently hold exactly these codes).
BEGIN;
UPDATE shopify_products
SET    mfr_sku = ''
WHERE  vendor='Zoffany' AND status='ACTIVE' AND mfr_sku IN (${mfrList});
COMMIT;
`;

fs.writeFileSync(path.join(DIR, 'zoffany_durable_apply.sql'), applySql);
fs.writeFileSync(path.join(DIR, 'zoffany_restore.sql'), restoreSql);
// restore-map JSON (handle -> recovered mfr, for the ledger)
fs.writeFileSync(path.join(DIR, 'zoffany_restore_map.json'),
  JSON.stringify({ ticket: 'TK-10677', generated: '2026-08-25', undo: "SET mfr_sku='' for the listed mfr codes", rows }, null, 2));

console.log(`Built durable apply for ${rows.length} Zoffany products.`);
console.log('Files: zoffany_durable_apply.sql, zoffany_restore.sql, zoffany_restore_map.json');
console.log('Sample:', rows[0].handle, '->', rows[0].mfr);