`audits/designtex-uom-fix/backfill-mirror-metafields.mjs`

82 lines
#!/usr/bin/env node
/**
 * backfill-mirror-metafields.mjs
 * Backfills the dw_unified.shopify_products.metafields jsonb column for Designtex
 * products from their LIVE Shopify metafields (the source of truth).
 *
 * Why: sync-shopify-mirror.js freshens core fields only (title/status/tags/etc.) and
 * never carries metafields, so after the Designtex UOM/spec fix the mirror's
 * metafields->'global'->'unit_of_measure' was still blank. This closes that gap.
 *
 * Reads ALL metafields per product, rebuilds the {namespace:{key:value}} shape, and
 * merges it into the existing jsonb (shallow top-level merge — fresh namespaces win,
 * untouched namespaces preserved). Customer-facing Shopify is unchanged (read-only there).
 *
 * Run ON Kamatera (canonical dw_unified + SHOPIFY_ADMIN_TOKEN in /root/.env):
 *   set -a && . /root/.env && set +a && node backfill-mirror-metafields.mjs [--dry-run]
 */
import https from 'https';
import pg from 'pg';

const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
const SHOP = process.env.SHOPIFY_SHOP || 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const DRY = process.argv.includes('--dry-run');
const DB_URL = 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';

if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN undefined — source /root/.env first'); process.exit(1); }

const pool = new pg.Pool({ connectionString: DB_URL, max: 4 });
const sleep = ms => new Promise(r => setTimeout(r, ms));
const ts = () => new Date().toLocaleString('en-US', { timeZone: 'America/Los_Angeles', hour12: false });
const log = m => console.log(`[${ts()} PT] ${m}`);

function getJSON(path, retries = 4) {
  return new Promise((resolve, reject) => {
    const attempt = n => {
      https.get({ host: SHOP, path, headers: { 'X-Shopify-Access-Token': TOKEN } }, res => {
        let data = '';
        res.on('data', c => (data += c));
        res.on('end', () => {
          if (res.statusCode === 429 && n < retries) return setTimeout(() => attempt(n + 1), 2000 * n);
          if (res.statusCode >= 400) return reject(new Error(`HTTP ${res.statusCode}`));
          try { resolve(JSON.parse(data)); } catch (e) { reject(e); }
        });
      }).on('error', e => (n < retries ? setTimeout(() => attempt(n + 1), 1500 * n) : reject(e)));
    };
    attempt(1);
  });
}

(async () => {
  const { rows } = await pool.query(
    "SELECT shopify_id, title FROM shopify_products WHERE vendor ILIKE '%designtex%' AND shopify_id IS NOT NULL ORDER BY shopify_id"
  );
  log(`Designtex products in mirror: ${rows.length}${DRY ? '  (DRY RUN — no writes)' : ''}`);
  let updated = 0, errors = 0, withUom = 0;
  for (const r of rows) {
    try {
      const numericId = String(r.shopify_id).replace(/^gid:\/\/shopify\/Product\//, '');
      const res = await getJSON(`/admin/api/${API}/products/${numericId}/metafields.json?limit=250`);
      const mfs = res.metafields || [];
      const obj = {};
      for (const m of mfs) (obj[m.namespace] ||= {})[m.key] = m.value;
      if (obj?.global?.unit_of_measure) withUom++;
      if (!DRY) {
        await pool.query(
          "UPDATE shopify_products SET metafields = COALESCE(metafields,'{}'::jsonb) || $2::jsonb, synced_at = NOW() WHERE shopify_id = $1",
          [r.shopify_id, JSON.stringify(obj)]
        );
      }
      updated++;
      if (updated % 50 === 0) log(`  ${updated}/${rows.length} (uom-present: ${withUom})`);
      await sleep(300);
    } catch (e) {
      errors++;
      log(`  ERR ${r.shopify_id} (${r.title?.slice(0, 40)}): ${e.message}`);
    }
  }
  log(`DONE: ${updated}${DRY ? ' would-update' : ' updated'}, ${withUom} carry global.unit_of_measure, ${errors} errors of ${rows.length}`);
  await pool.end();
})();