`build-hwc-map.mjs`

178 lines
#!/usr/bin/env node
// TK-10633 Option C — REVERSIBLE PREP. Build the HWC-#### restore-map for the ~646
// ACTIVE Momentum-real Hollywood/DWHD-* products, enumerating BOTH variants from LIVE
// Shopify (the mirror only holds the -Sample row and understates). READ-ONLY: reads the
// mirror to enumerate the target product-ids, then GETs each product LIVE for its actual
// variants + metafields, and writes ONLY a local restore file. Fires ZERO writes at
// Shopify or dw_unified.
//
// Steve's decision: identity = the real HWC-#### code WHERE IT EXISTS (in a dw_sku
// metafield). Products that HAVE one -> remap customer-facing SKU (both variants) to it.
// Products that LACK one -> HELD code-recovery bucket, stay DWHD for now (never fabricate,
// never hash).
import { createRequire } from 'module';
import { readFileSync, writeFileSync } from 'node:fs';
const require = createRequire(import.meta.url);
const { Pool } = require('pg');

const SHOP = 'designer-laboratory-sandbox.myshopify.com';
const VER = '2024-10';
const env = readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
const TOKEN = (env.split('\n').find(l => l.startsWith('SHOPIFY_ADMIN_TOKEN=')) || '')
  .replace('SHOPIFY_ADMIN_TOKEN=', '').replace(/["'\r]/g, '').trim();
if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(1); }

const pool = new Pool({ connectionString: (process.env.PGCONNSTRING || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified?host=/tmp') });
const BASE = `status='ACTIVE' AND variant_sku ~ '^DWHD' AND (metafields->'dwc'->'real_vendor'->>'value')='Momentum'`;

async function getJSON(url, tries = 0) {
  const res = await fetch(url, { headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' } });
  if (res.status === 429) { await new Promise(r => setTimeout(r, 2000)); return getJSON(url, tries); }
  if ((res.status === 502 || res.status === 503) && tries < 3) { await new Promise(r => setTimeout(r, 1500)); return getJSON(url, tries + 1); }
  if (!res.ok) throw new Error(`${res.status} ${url} ${await res.text()}`);
  return res.json();
}

function extractHWC(metafields) {
  // real HWC-#### lives in a dw_sku metafield (custom / dwc / global). Return the first
  // HWC-\d+ value found; null if none.
  for (const m of metafields) {
    if (m.key === 'dw_sku' && /^HWC-\d+/i.test(String(m.value || ''))) return String(m.value).trim().toUpperCase();
  }
  return null;
}
function dwSkuNamespaces(metafields) {
  // which dw_sku metafields currently hold a DWH* fabricated code (must be rewritten to HWC)
  const out = [];
  for (const m of metafields) {
    if (m.key === 'dw_sku' && /^DWH/i.test(String(m.value || ''))) out.push({ namespace: m.namespace, value: m.value, metafield_id: m.id });
  }
  return out;
}

async function main() {
  // Enumerate target product-ids from the mirror (product-level metafields are reliable there).
  const { rows: prods } = await pool.query(
    `SELECT DISTINCT shopify_id, handle FROM shopify_products WHERE ${BASE} ORDER BY shopify_id`);
  console.error(`target products from mirror: ${prods.length}`);

  const have = [];   // remappable now (real HWC-####)
  const held = [];   // no HWC -> held recovery bucket
  const problems = [];

  let i = 0;
  for (const p of prods) {
    i++;
    const gid = p.shopify_id;                          // gid://shopify/Product/#####
    const numId = gid.replace(/^.*\/Product\//, '');
    let product, metafields;
    try {
      const pr = await getJSON(`https://${SHOP}/admin/api/${VER}/products/${numId}.json?fields=id,handle,title,variants`);
      product = pr.product;
      const mr = await getJSON(`https://${SHOP}/admin/api/${VER}/products/${numId}/metafields.json`);
      metafields = mr.metafields || [];
    } catch (e) {
      problems.push({ product_id: numId, handle: p.handle, why: 'fetch_failed', err: String(e).slice(0, 200) });
      continue;
    }
    if (!product) { problems.push({ product_id: numId, handle: p.handle, why: 'no_product' }); continue; }

    const variants = (product.variants || []).map(v => ({
      variant_id: v.id, sku: v.sku, title: v.title, price: v.price,
    }));
    // classify variants — base "Sold Per Yard" (sellable) + sample
    const sampleV = variants.find(v => /-sample$/i.test(v.sku || '') || /sample/i.test(v.title || ''));
    const baseV = variants.find(v => v !== sampleV && /^DWHD/i.test(v.sku || ''));

    const hwc = extractHWC(metafields);
    const mfr = (metafields.find(m => m.key === 'manufacturer_sku') || {}).value || null;

    const rec = {
      product_id: numId, gid, handle: product.handle, title: product.title,
      variants,
      base_variant: baseV ? { variant_id: baseV.variant_id, old_sku: baseV.sku, title: baseV.title, price: baseV.price } : null,
      sample_variant: sampleV ? { variant_id: sampleV.variant_id, old_sku: sampleV.sku, title: sampleV.title, price: sampleV.price } : null,
      manufacturer_sku: mfr,                            // INTERNAL — never customer-facing, untouched
      hwc,                                              // real HWC-#### or null
      polluted_dw_sku_metafields: dwSkuNamespaces(metafields),
    };

    // structural sanity: both variants must be present + base sku is DWHD
    if (!rec.base_variant || !rec.sample_variant) {
      problems.push({ product_id: numId, handle: product.handle, why: 'missing_variant', variants });
    }

    if (hwc) {
      // build the new SKUs from HWC — both variants
      rec.new_base_sku = hwc;                           // HWC-####
      rec.new_sample_sku = `${hwc}-Sample`;             // HWC-####-Sample
      // does the observed base sku equal (sample sku minus -Sample)? (verify assumption, don't rely on it)
      rec.base_matches_sample_stem = !!(rec.base_variant && rec.sample_variant &&
        rec.base_variant.old_sku.toUpperCase() === rec.sample_variant.old_sku.replace(/-sample$/i, '').toUpperCase());
      have.push(rec);
    } else {
      held.push(rec);
    }
    if (i % 50 === 0) console.error(`  ...${i}/${prods.length} (have=${have.length} held=${held.length})`);
  }

  // ---- VERIFY the HAVE bucket ----
  // (1) zero within-set HWC collisions (two live products -> same HWC = must NOT collapse)
  const codeCount = {};
  for (const r of have) codeCount[r.new_base_sku] = (codeCount[r.new_base_sku] || 0) + 1;
  const dupCodes = Object.entries(codeCount).filter(([, n]) => n > 1).map(([c]) => c);
  const collidingWithin = have.filter(r => dupCodes.includes(r.new_base_sku));
  const applySet = have.filter(r => !dupCodes.includes(r.new_base_sku));

  // (2) zero collision vs EXISTING live SKUs (any product NOT in our target set already using HWC-####)
  const targetVids = new Set();
  for (const r of have) for (const v of r.variants) targetVids.add(String(v.variant_id));
  // pull all live HWC-* skus from the mirror (fast) — any HWC not belonging to our targets is a collision
  const { rows: liveHwc } = await pool.query(
    `SELECT variant_id, variant_sku FROM shopify_products WHERE variant_sku ~* '^HWC-'`);
  const existingHW = new Set(
    liveHwc.filter(e => !targetVids.has(String(e.variant_id)))
      .map(e => e.variant_sku.replace(/-sample$/i, '').toUpperCase()));
  const collisionsVsExisting = [...new Set(applySet.map(r => r.new_base_sku.toUpperCase()).filter(c => existingHW.has(c)))];

  // (3) zero bare-number / Momentum-number leakage in the result (new sku must never be all-digits or == mfr#)
  const bareLeak = applySet.filter(r => /^\d+$/.test(r.new_base_sku.replace(/^HWC-/i, '')) === false ? false : false);
  const equalsMfr = applySet.filter(r => r.manufacturer_sku && r.new_base_sku === String(r.manufacturer_sku));
  const dwhResidue = applySet.filter(r => /^DWH/i.test(r.new_base_sku) || /DWH/i.test(r.new_sample_sku));
  // any new sku that is a pure momentum bare number (all digits)
  const anyBareDigits = applySet.filter(r => /^\d+$/.test(r.new_base_sku));

  const summary = {
    generated_at: new Date().toISOString(),
    ticket: 'TK-10633',
    source: 'LIVE Shopify (both variants) + mirror metafields for HWC extraction',
    identity_scheme: 'real HWC-#### where it exists (dw_sku metafield); else HELD (stay DWHD)',
    total_products: prods.length,
    bucket_HAVE_hwc: have.length,
    bucket_HELD_no_hwc: held.length,
    apply_set_count: applySet.length,
    held_within_collision_rows: collidingWithin.length,
    problems: problems.length,
    base_stem_mismatches: have.filter(r => r.base_matches_sample_stem === false).length,
    verify: {
      within_set_hwc_collisions: dupCodes.length,       // held out of apply-set
      collisions_vs_existing_live_HW: collisionsVsExisting.length, // MUST be 0
      dwh_residue_in_new_skus: dwhResidue.length,        // MUST be 0
      new_sku_all_digits_bareleak: anyBareDigits.length, // MUST be 0
      new_sku_equals_mfr_number: equalsMfr.length,       // MUST be 0
      colliding_codes: dupCodes,
      collisions_vs_existing: collisionsVsExisting,
    },
  };

  writeFileSync('data/hwc-restore-map.json', JSON.stringify({
    summary, problems,
    apply_set: applySet,             // HAVE-bucket, clean, both variants -> HWC-####
    held_collision_rows: collidingWithin,
    held_recovery_bucket: held,      // LACK-HWC, stay DWHD (LBI Boyd recovery)
  }, null, 2));
  console.log(JSON.stringify(summary, null, 2));
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });