`set_weights.mjs`

91 lines
// set_weights.mjs — backfill non-zero shipping weight on ACTIVE Shopify variants so Google
// stops throwing missing_shipping_weight. Idempotent (skips variants that already have grams>0),
// resumable (paginates by since_id, records a cursor), reversible (logs prior grams to a table).
// Variant-aware: a "Sample"/memo variant gets the sample weight; the sellable variant gets the
// product_type weight. Scope with --vendor "<name>" (pilot) or --all.
//
// Modes: --dry-run (default)  --apply
import { execFileSync } from 'node:child_process';

const SHOP = process.env.DW_SHOP || 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const TOKEN = execFileSync('bash', ['-lc',
  `grep -m1 '^SHOPIFY_ADMIN_TOKEN=' "$HOME/Projects/secrets-manager/.env" | cut -d= -f2- | tr -d '"'"'"'\\r'`],
  { encoding: 'utf8' }).trim();
const DBURL = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
const args = process.argv.slice(2);
const DRY = !args.includes('--apply');
const VENDOR = (() => { const a = args.find(x => x.startsWith('--vendor')); return a ? a.split('=')[1] : null; })();
const DELAY = 320;

// grams by product_type (sellable variant); sample variants always SAMPLE_G
const SAMPLE_G = 113;                     // ~0.25 lb memo sample
const TYPE_G = {
  'wallcovering': 1814, 'wallcoverings': 1814, 'wallpaper': 1814, 'metallic wallcovering': 1814,
  'commercial wallcovering': 1814, 'mural': 2268, 'fabric': 454, 'commercial fabric': 454,
  'commercial drapery': 454, 'pillow': 680, 'trim': 227, 'acoustic panel': 907,
  'tin ceiling tile': 907, 'memo sample': SAMPLE_G, 'sample': SAMPLE_G,
};
const DEFAULT_G = 454;
function weightFor(productType, variant) {
  const t = (productType || '').toLowerCase().trim();
  if (/sample/i.test(variant.title || '') || /sample/i.test(variant.sku || '')) return SAMPLE_G;
  return TYPE_G[t] ?? DEFAULT_G;
}

const sleep = ms => new Promise(r => setTimeout(r, ms));
function qx(sql) { execFileSync('psql', [DBURL, '-q', '-c', sql], { stdio: 'ignore' }); }
function esc(s) { return String(s).replace(/'/g, "''"); }
async function api(method, path, body) {
  const url = `https://${SHOP}/admin/api/${API}/${path}`;
  for (let i = 0; i < 6; i++) {
    const res = await fetch(url, { method,
      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
      body: body ? JSON.stringify(body) : undefined });
    if (res.status === 429) { await sleep(2000 * (i + 1)); continue; }
    const txt = await res.text();
    if (!res.ok) throw new Error(`${method} ${path} -> HTTP ${res.status}: ${txt.slice(0,150)}`);
    return { headers: res.headers, json: txt ? JSON.parse(txt) : {} };
  }
  throw new Error(`${method} ${path} -> 429 exhausted`);
}

// reversibility log
qx(`CREATE TABLE IF NOT EXISTS shopify_weight_backfill (
  variant_id bigint PRIMARY KEY, product_id bigint, sku text, product_type text,
  prior_grams int, new_grams int, set_at timestamptz DEFAULT now());`);

console.log(`[weights] mode=${DRY ? 'DRY-RUN' : 'APPLY'} scope=${VENDOR ? `vendor="${VENDOR}"` : 'ALL ACTIVE'} token=...${TOKEN.slice(-4)}`);
let sinceId = 0, scanned = 0, set = 0, skipped = 0, errors = 0, pages = 0;
const vendorQ = VENDOR ? `&vendor=${encodeURIComponent(VENDOR)}` : '';

while (true) {
  let resp;
  try { resp = await api('GET', `products.json?status=active&limit=250&since_id=${sinceId}&fields=id,product_type,variants${vendorQ}`); }
  catch (e) { console.log('  GET page error:', e.message); break; }
  const prods = resp.json.products || [];
  if (!prods.length) break;
  pages++;
  for (const p of prods) {
    sinceId = Math.max(sinceId, p.id);
    for (const v of p.variants) {
      scanned++;
      const cur = +v.grams || 0;
      if (cur > 0) { skipped++; continue; }         // idempotent
      const g = weightFor(p.product_type, v);
      if (DRY) { set++; continue; }
      try {
        await api('PUT', `variants/${v.id}.json`, { variant: { id: v.id, grams: g } });
        qx(`INSERT INTO shopify_weight_backfill(variant_id,product_id,sku,product_type,prior_grams,new_grams)
            VALUES (${v.id},${p.id},'${esc(v.sku||'')}','${esc(p.product_type||'')}',${cur},${g})
            ON CONFLICT (variant_id) DO UPDATE SET new_grams=${g}, set_at=now()`);
        set++;
        await sleep(DELAY);
      } catch (e) { errors++; if (errors <= 20) console.log(`  ✗ variant ${v.id}: ${String(e.message).slice(0,120)}`); }
    }
  }
  if (pages % 5 === 0) console.log(`  …scanned=${scanned} set=${set} skipped=${skipped} err=${errors} (since_id=${sinceId})`);
}
console.log(`\n[weights] done · scanned=${scanned} set=${set} skipped(had weight)=${skipped} errors=${errors}`);
if (DRY) console.log('  (DRY-RUN — no writes. Re-run with --apply, optionally --vendor="Los Angeles Fabrics" to pilot.)');