`scripts/backfill-metafields.mjs`

201 lines
#!/usr/bin/env node
// TK-11346 — Newmor metafield spec-backfill executor (FILL-ONLY, NEVER CLOBBER)
// DRY-RUN by default. Live writes ONLY behind explicit --apply.
// Scope: the 116 ACTIVE Newmor colorways in artifacts/LIVE-NEWMOR-2026-09-09.csv
// Source: dw_unified.newmor_catalog (local mirror, host=/tmp)
// Rules (from artifacts/METAFIELD-MAP.md):
//   1. Write a metafield ONLY when LIVE value is empty/absent AND source value is non-empty.
//   2. Never fork keys — lock write type to the existing live type when the key already exists.
//   3. Never touch body_html / price / variant / channel. Online Store only, unchanged.
import {execFileSync} from 'node:child_process';
import fs from 'node:fs';
import {gql, ROOT} from './shopify.mjs';

const APPLY = process.argv.includes('--apply');
const CSV = `${ROOT}/artifacts/LIVE-NEWMOR-2026-09-09.csv`;
const OUT_DIR = `${ROOT}/data`;
const STAMP = new Date().toISOString().replace(/[:.]/g, '-');
const nowIso = () => new Date().toISOString();

// ---- 1. Load the 116 live products (manufacturer_code -> GID) from the manifest ----
const csvRows = fs.readFileSync(CSV, 'utf8').trim().split('\n').slice(1)
  .map(l => l.split(','))
  .map(c => ({ mfr: c[0].trim(), gid: c[1].trim() }));
if (csvRows.length !== 116) throw new Error(`Expected 116 manifest rows, got ${csvRows.length}`);
const gidSet = new Set(csvRows.map(r => r.gid));
if (gidSet.size !== 116) throw new Error(`Duplicate GIDs in manifest: ${csvRows.length} rows / ${gidSet.size} unique`);

// ---- 2. Fresh catalog read from dw_unified for exactly these mfr codes ----
const codes = csvRows.map(r => r.mfr);
const inList = codes.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
const sql = `COPY (
  SELECT json_agg(row_to_json(t)) FROM (
    SELECT mfr_sku,
           NULLIF(width,'')        AS width,
           NULLIF(length,'')       AS length,
           NULLIF(material,'')      AS material,
           NULLIF(color_hex,'')     AS color_hex,
           NULLIF(product_url,'')   AS product_url,
           NULLIF(fire_rating,'')   AS fire_rating,
           NULLIF(application,'')   AS application,
           NULLIF(match_type,'')    AS match_type,
           NULLIF(coverage,'')      AS coverage,
           NULLIF(repeat_v,'')      AS repeat_v
    FROM newmor_catalog WHERE mfr_sku IN (${inList})
  ) t
) TO STDOUT;`;
const catalogJson = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-c', sql], {encoding: 'utf8', maxBuffer: 64 * 1024 * 1024});
const catalog = JSON.parse(catalogJson);
const catByMfr = new Map(catalog.map(r => [r.mfr_sku, r]));
for (const r of csvRows) if (!catByMfr.has(r.mfr)) throw new Error(`No newmor_catalog row for ${r.mfr}`);

// ---- 3. Candidate key definitions (fill-only; catalog-derived unless flagged constant) ----
// roll_length / vendor_url are catalog-derived; us_distributor is a Steve-confirmed CONSTANT
// (map row 27 = "LBI Boyd"), still gated by fill-only (write only where live is empty).
// width/material/color_hex are DELIBERATELY listed so the dry-run PROVES they are skipped
// by the clobber-guard (live already populated) — they will produce 0 writes.
// Set NEWMOR_SKIP_DISTRIBUTOR=1 to drop the constant us_distributor fill (strict catalog-only mode).
const SKIP_DISTRIBUTOR = process.env.NEWMOR_SKIP_DISTRIBUTOR === '1';

// Parse a free-text repeat like "11mm" / "13.25cm" into Shopify's dimension JSON shape.
// Returns null (never written) if it can't be parsed cleanly — never guess a unit.
const UNIT_MAP = { mm: 'MILLIMETERS', cm: 'CENTIMETERS', in: 'INCHES' };
function toDimension(v) {
  const m = String(v).trim().match(/^(\d+(?:\.\d+)?)\s*(mm|cm|in)$/i);
  if (!m) return null;
  return JSON.stringify({ value: parseFloat(m[1]), unit: UNIT_MAP[m[2].toLowerCase()] });
}

const CANDIDATES = [
  { key: 'roll_length',    defaultType: 'single_line_text_field', source: c => c.length,      origin: 'catalog:length' },
  { key: 'vendor_url',     defaultType: 'url',                    source: c => c.product_url, origin: 'catalog:product_url' },
  ...(SKIP_DISTRIBUTOR ? [] : [{ key: 'us_distributor', defaultType: 'single_line_text_field', source: () => 'LBI Boyd', origin: 'constant:LBI Boyd (Steve-confirmed, map row 27)' }]),
  // clobber-guard proof keys (expected 0 writes — live already populated):
  { key: 'width',          defaultType: 'single_line_text_field', source: c => c.width,       origin: 'catalog:width' },
  { key: 'material',       defaultType: 'multi_line_text_field',  source: c => c.material,     origin: 'catalog:material' },
  { key: 'color_hex',      defaultType: 'single_line_text_field', source: c => c.color_hex,    origin: 'catalog:color_hex' },
  // TK-11424 — Norman scraper spec-refresh keys (METAFIELD-MAP.md). Steve's ruling 2026-09-13:
  // STRICT fill-only-never-clobber, no placeholder exception (a live value like
  // custom.fire_rating="Inquire for more Information" counts as populated and is skipped).
  { key: 'fire_rating',    defaultType: 'single_line_text_field', source: c => c.fire_rating,  origin: 'catalog:fire_rating (TK-11424 PDF spec-sheet extraction)' },
  { key: 'application',    defaultType: 'single_line_text_field', source: c => c.application,  origin: 'catalog:application (TK-11424 PDF filename/body)' },
  { key: 'pattern_match',  defaultType: 'single_line_text_field', source: c => c.match_type,   origin: 'catalog:match_type (TK-11424 PDF/HTML)' },
  { key: 'coverage',       defaultType: 'single_line_text_field', source: c => c.coverage,     origin: 'catalog:coverage (TK-11424 computed roll sqm/sqyd)' },
  { key: 'pattern_repeat_vertical', defaultType: 'dimension',     source: c => c.repeat_v,     origin: 'catalog:repeat_v (TK-11424 PDF/HTML)', format: toDimension },
];
const NS = 'custom';
const empty = v => v === null || v === undefined || String(v).trim() === '';

// ---- 4. Fetch live metafields for all 116, compute fill candidates ----
const perProduct = [];     // full record per product
const jsonl = [];          // one line per proposed write
const restoreMap = [];     // reversibility record
const keyTotals = {};      // key -> proposed write count
const skipReasons = {};    // key -> {live_populated, source_empty, would_fork_type}
for (const cand of CANDIDATES) skipReasons[cand.key] = { live_populated: 0, source_empty: 0, unparseable_source: 0 };

// First pass: discover existing live type per candidate key across all 116 (to lock type, never fork)
const liveByGid = new Map();
for (const {mfr, gid} of csvRows) {
  const d = await gql(
    `query($id:ID!){product(id:$id){id title vendor status metafields(first:250){nodes{id namespace key type value}}}}`,
    {id: gid});
  const p = d.product;
  if (!p) throw new Error(`Product not found: ${gid} (${mfr})`);
  if (p.vendor !== 'Newmor Wallcoverings') throw new Error(`Vendor mismatch on ${gid}: ${p.vendor}`);
  if (p.status !== 'ACTIVE') console.error(`WARN ${mfr} ${gid} status=${p.status} (manifest = 116 ACTIVE)`);
  liveByGid.set(gid, p);
}
// Lock type per key from existing live usage
const lockedType = {};
for (const cand of CANDIDATES) {
  let found = null;
  for (const p of liveByGid.values()) {
    const m = p.metafields.nodes.find(n => n.namespace === NS && n.key === cand.key);
    if (m) { found = m.type; break; }
  }
  lockedType[cand.key] = found || cand.defaultType;
}

// Second pass: compute fills
for (const {mfr, gid} of csvRows) {
  const p = liveByGid.get(gid);
  const cat = catByMfr.get(mfr);
  const liveMf = new Map(p.metafields.nodes.filter(n => n.namespace === NS).map(n => [n.key, n]));
  const rec = { mfr, gid, title: p.title, writes: [], skips: [] };
  for (const cand of CANDIDATES) {
    const src = cand.source(cat);
    const liveM = liveMf.get(cand.key);
    const liveVal = liveM ? liveM.value : null;
    if (empty(src)) { rec.skips.push({key: cand.key, reason: 'source_empty'}); skipReasons[cand.key].source_empty++; continue; }
    if (!empty(liveVal)) { rec.skips.push({key: cand.key, reason: 'live_populated', liveVal, liveType: liveM.type}); skipReasons[cand.key].live_populated++; continue; }
    // FILL: live empty/absent AND source non-empty
    const type = lockedType[cand.key];
    const value = cand.format ? cand.format(src) : String(src).trim();
    if (value === null) { rec.skips.push({key: cand.key, reason: 'unparseable_source', src}); skipReasons[cand.key].unparseable_source++; continue; }
    const write = { key: `${NS}.${cand.key}`, type, value, origin: cand.origin, existingMetafieldId: liveM ? liveM.id : null, action: liveM ? 'update-empty' : 'create' };
    rec.writes.push(write);
    keyTotals[cand.key] = (keyTotals[cand.key] || 0) + 1;
    jsonl.push(JSON.stringify({mfr, gid, title: p.title, ...write}));
    restoreMap.push({
      gid, mfr, namespace: NS, key: cand.key,
      restore: liveM ? { action: 'set-old-value', oldValue: liveM.value, metafieldId: liveM.id, oldType: liveM.type }
                     : { action: 'delete-if-created' },
      newValue: value, newType: type, origin: cand.origin, applied: false, appliedMetafieldId: null, appliedAt: null,
    });
  }
  perProduct.push(rec);
}

const grandTotal = jsonl.length;
const productsAffected = perProduct.filter(r => r.writes.length).length;

// ---- 5. Emit artifacts ----
fs.mkdirSync(`${OUT_DIR}/tk11346`, {recursive: true});
const jsonlPath = `${OUT_DIR}/tk11346/dryrun-writes-${STAMP}.jsonl`;
const perProdPath = `${OUT_DIR}/tk11346/dryrun-per-product-${STAMP}.json`;
const restorePath = `${OUT_DIR}/tk11346/restore-map-${STAMP}.json`;
fs.writeFileSync(jsonlPath, jsonl.join('\n') + (jsonl.length ? '\n' : ''));
fs.writeFileSync(perProdPath, JSON.stringify({generated: nowIso(), mode: APPLY ? 'apply' : 'dry-run', perProduct}, null, 2) + '\n');

const summary = {
  ticket: 'TK-11346', generated: nowIso(), mode: APPLY ? 'apply' : 'dry-run',
  scope: { live_products: 116, source: 'dw_unified.newmor_catalog', rule: 'fill-only (live-empty AND source-non-empty), never clobber, never fork type' },
  locked_types: lockedType,
  key_totals: keyTotals,
  grand_total_writes: grandTotal,
  products_affected: productsAffected,
  skip_reasons: skipReasons,
};

// ---- 6. Live writes (ONLY with --apply) ----
if (APPLY) {
  console.error('!!! --apply: firing LIVE metafieldsSet writes !!!');
  const applied = [];
  for (const r of perProduct) {
    for (const w of r.writes) {
      const [ns, key] = w.key.split('.');
      const d = await gql(
        `mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){metafields{id namespace key type value}userErrors{field message}}}`,
        {mf: [{ownerId: r.gid, namespace: ns, key, type: w.type, value: w.value}]});
      const ue = d.metafieldsSet.userErrors;
      if (ue.length) throw new Error(`metafieldsSet ${r.gid} ${w.key}: ${JSON.stringify(ue)}`);
      const setId = d.metafieldsSet.metafields[0].id;
      const rm = restoreMap.find(x => x.gid === r.gid && x.key === key);
      rm.applied = true; rm.appliedMetafieldId = setId; rm.appliedAt = nowIso();
      applied.push({gid: r.gid, key: w.key, id: setId});
      await new Promise(res => setTimeout(res, 250)); // gentle pacing
    }
  }
  summary.applied_count = applied.length;
  console.error(`Applied ${applied.length} live writes.`);
}

fs.writeFileSync(restorePath, JSON.stringify({ticket: 'TK-11346', generated: nowIso(), mode: APPLY ? 'apply' : 'dry-run', restore_map: restoreMap}, null, 2) + '\n');

// ---- 7. Report ----
console.log(JSON.stringify(summary, null, 2));
console.log(`\nArtifacts:\n  writes JSONL : ${jsonlPath}\n  per-product  : ${perProdPath}\n  restore map  : ${restorePath}`);
console.log(`\nGRAND TOTAL proposed writes: ${grandTotal}  |  products affected: ${productsAffected}/116`);
if (!APPLY) console.log('\nMODE = DRY-RUN. Zero live writes fired. Re-run with --apply (GATED) to write.');