`rolls-ar-deeplink/backfill-pattern-repeat.js`

176 lines
#!/usr/bin/env node
/**
 * PATH A backfill — custom.pattern_repeat_number  (Council idea #5, 2026-08-09)
 * Steve approved Path A + Path B: "A and b approved / Dust2026".
 *
 * Source : dw_unified vendor *_catalog staging tables (Mac2-canonical, $0),
 *          which carry free-text repeat_v / repeat_h / pattern_repeat.
 * Target : a NEW numeric Shopify metafield custom.pattern_repeat_number (inches)
 *          on active products that lack a numeric repeat today. Additive +
 *          reversible (new field; never overwrites existing catalog data).
 *
 * Join   : vendor mfr SKU (catalog) -> Shopify custom.manufacturer_sku metafield
 *          (per the dw-mirror-mfrsku-blank rule: read the live metafield, not
 *          the blank mirror column).
 *
 * SAFETY : dry-run by default. --apply performs the metafield writes in batches
 *          via metafieldsSet, skipping any product that already has a numeric
 *          repeat, and writes a reversibility ledger (runs/<ts>.json) of every
 *          {productId, before, after} so the write can be undone.
 *
 * Usage:
 *   node backfill-pattern-repeat.js                 # dry-run, whole catalog
 *   node backfill-pattern-repeat.js --vendor thibaut_catalog   # scope one vendor
 *   node backfill-pattern-repeat.js --apply         # GATED live write
 */
const fs = require('fs');
const { execFileSync } = require('child_process');
const env = fs.readFileSync(require('os').homedir() + '/Projects/secrets-manager/.env', 'utf8');
const g = (k) => (env.match(new RegExp('^' + k + '=(.*)$', 'm')) || [])[1];
const TOKEN = g('SHOPIFY_ADMIN_TOKEN'), STORE = g('SHOPIFY_STORE');
const API = `https://${STORE}/admin/api/2024-10/graphql.json`;
const APPLY = process.argv.includes('--apply');
const _vi = process.argv.indexOf('--vendor');
const VENDOR = (process.argv.find((a) => a.startsWith('--vendor=')) || '').split('=')[1]
            || (_vi >= 0 ? (process.argv[_vi + 1] || '') : '');

/** Normalize a free-text repeat/width to a positive number of inches, else null.
 *  Handles: '20.5', '21 (53 cm)', '3.5  (8.89 cm)', '36" Wide', 'N/A', '0'. */
function toInches(v) {
  if (v == null) return null;
  const s = String(v);
  const first = s.match(/[0-9]+(\.[0-9]+)?/);
  if (!first) return null;
  let n = parseFloat(first[0]);
  const hasIn = /"|″|in\b|inch/i.test(s);
  // Leading number implausible as inches (>40) and NOT marked inches → treat as cm and
  // convert (many vendors store cm here; fixes mindthegap/as_creation 83-100% out-of-range).
  if (n > 40 && !hasIn) {
    const cmM = s.match(/([0-9]+(\.[0-9]+)?)\s*cm/i);
    n = (cmM ? parseFloat(cmM[1]) : n) / 2.54;
  }
  if (!Number.isFinite(n) || n <= 0) return null;
  n = Math.round(n * 100) / 100;
  // Range clamp: a real wallpaper vertical repeat is ~0-40"; past that it's a unit/format/
  // code error (osborne 15102, schumacher 192487), NOT inches → reject, never write garbage.
  return n > 0 && n <= 40 ? n : null;
}

function psql(sql) {
  return execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAF\t', '-X', '-c', sql], { encoding: 'utf8', maxBuffer: 1 << 28 });
}

// Build { mfrSku(upper) -> {rv, hr} } supply map from vendor catalogs.
function buildSupply() {
  const tables = psql(
    "select table_name from information_schema.columns where table_schema='public' " +
    "and column_name='repeat_v' and table_name like '%_catalog' and table_name not like '%_bak%' and table_name not like '_bak%'"
  ).trim().split('\n').filter(Boolean).map((r) => r.trim());
  const use = VENDOR ? tables.filter((t) => t === VENDOR || t === VENDOR + '_catalog') : tables;
  const raw = new Map(); // key -> [{rv,hr,src}] across ALL catalogs, resolved after
  for (const t of use) {
    // mfr sku column name varies; try common ones
    const cols = psql(`select column_name from information_schema.columns where table_schema='public' and table_name='${t}'`).trim().split('\n').map((s) => s.trim());
    const skuCol = ['mfr_sku', 'manufacturer_sku', 'sku', 'pattern_number', 'product_code', 'style_number'].find((c) => cols.includes(c));
    const hasHR = cols.includes('repeat_h');
    if (!skuCol) continue;
    let rows;
    try {
      rows = psql(`select ${skuCol}::text, repeat_v::text${hasHR ? ', repeat_h::text' : ''} from ${t} where repeat_v is not null`).trim().split('\n');
    } catch { continue; }
    for (const line of rows) {
      if (!line) continue;
      const [sku, rv, hr] = line.split('\t');
      const rvN = toInches(rv), hrN = hasHR ? toInches(hr) : null;
      if (!sku || (!rvN && !hrN)) continue;
      const key = sku.trim().toUpperCase();
      if (!raw.has(key)) raw.set(key, []);
      raw.get(key).push({ rv: rvN, hr: hrN, src: t });
    }
  }
  // Resolve cross-vendor collisions: a SKU shared across vendors with MATERIALLY divergent
  // vertical repeats (>1.5x or >3") is ambiguous — SKIP it rather than first-writer-wins
  // (which silently mis-prefilled the losing vendor's products). Consistent → keep one.
  const supply = new Map();
  for (const [key, arr] of raw) {
    const rvs = arr.map((a) => a.rv).filter((x) => x != null);
    if (rvs.length > 1) {
      const mn = Math.min(...rvs), mx = Math.max(...rvs);
      if (mx / mn > 1.5 || mx - mn > 3) continue; // ambiguous collision → skip, don't guess
    }
    supply.set(key, arr[0]);
  }
  return supply;
}

const Q = `query($c:String){ products(first:200, after:$c, query:"status:active product_type:Wallcovering"){
  pageInfo{hasNextPage endCursor}
  nodes{ id
    mfr: metafield(namespace:"custom", key:"manufacturer_sku"){value}
    prnum: metafield(namespace:"custom", key:"pattern_repeat_number"){value}
    prep: metafield(namespace:"custom", key:"pattern_repeat"){value}
  }}}`;

async function gql(query, variables) {
  const r = await fetch(API, { method: 'POST', headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' }, body: JSON.stringify({ query, variables }) });
  return r.json();
}

(async () => {
  console.log(`PATH A backfill — mode=${APPLY ? 'APPLY (live write)' : 'DRY-RUN'}${VENDOR ? ' vendor=' + VENDOR : ''}`);
  const supply = buildSupply();
  console.log(`vendor-catalog numeric-repeat supply: ${supply.size} distinct mfr SKUs`);

  let cur = null, more = true, page = 0;
  const stat = { active_wc: 0, has_mfr: 0, already_numeric: 0, matched: 0, would_write: 0 };
  const ledger = [];
  const CAP = parseInt(process.env.CAP || '250', 10);
  while (more && page < CAP) {
    const j = await gql(Q, { c: cur });
    if (j.errors) { console.error(JSON.stringify(j.errors).slice(0, 300)); break; }
    const d = j.data.products;
    for (const n of d.nodes) {
      stat.active_wc++;
      const alreadyNum = toInches(n.prnum && n.prnum.value) || toInches(n.prep && n.prep.value);
      if (alreadyNum) stat.already_numeric++;
      const mfr = n.mfr && n.mfr.value ? n.mfr.value.trim().toUpperCase() : null;
      if (mfr) stat.has_mfr++;
      if (!mfr || alreadyNum) continue;
      const s = supply.get(mfr);
      if (!s) continue;
      stat.matched++;
      const val = s.rv || s.hr;
      if (!val) continue;
      stat.would_write++;
      ledger.push({ id: n.id, mfr, before: n.prnum ? n.prnum.value : null, after: String(val), src: s.src });
    }
    cur = d.pageInfo.endCursor; more = d.pageInfo.hasNextPage; page++;
    if (page % 10 === 0) console.error(`...page ${page}, active WC scanned ${stat.active_wc}, would_write ${stat.would_write}`);
    await new Promise((x) => setTimeout(x, 120));
  }
  console.log(JSON.stringify({ ...stat, pages: page, completed: !more }, null, 2));

  // READ-ONLY audit dump (no live write) — used by the Path A dry-run gate.
  if (process.env.AUDIT_DUMP) {
    fs.writeFileSync(process.env.AUDIT_DUMP, JSON.stringify({ stat: { ...stat, pages: page, completed: !more }, ledger }, null, 2));
    console.error(`AUDIT_DUMP written: ${process.env.AUDIT_DUMP} (${ledger.length} would-write rows)`);
  }

  if (APPLY && ledger.length) {
    const ts = new Date().toISOString().replace(/[:.]/g, '-'); // stamp OUTSIDE, ok here (not a workflow)
    fs.mkdirSync(__dirname + '/runs', { recursive: true });
    fs.writeFileSync(`${__dirname}/runs/${ts}.json`, JSON.stringify(ledger, null, 2));
    const M = `mutation($m:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$m){ userErrors{field message} } }`;
    for (let i = 0; i < ledger.length; i += 25) {
      const chunk = ledger.slice(i, i + 25).map((e) => ({ ownerId: e.id, namespace: 'custom', key: 'pattern_repeat_number', type: 'number_decimal', value: e.after }));
      const j = await gql(M, { m: chunk });
      const errs = j.data && j.data.metafieldsSet && j.data.metafieldsSet.userErrors;
      if (errs && errs.length) console.error('userErrors', JSON.stringify(errs).slice(0, 300));
      await new Promise((x) => setTimeout(x, 200));
    }
    console.log(`APPLIED ${ledger.length} writes; ledger runs/${ts}.json`);
  } else if (!APPLY) {
    console.log(`DRY-RUN: ${ledger.length} products WOULD gain custom.pattern_repeat_number. Re-run with --apply to write.`);
  }
})().catch((e) => { console.error(e.message); process.exit(1); });