`metafield-fill.mjs`

101 lines
// carnegie-metafield-fill — fill front+back-end metafields on the Carnegie line from
// carnegie_catalog, matching the DW store's real namespace schema (learned from Thibaut).
//
// STRUCTURE: Carnegie = one PATTERN per product, colorways = variants. So:
//   PRODUCT-level (pattern facts): pattern_name, brand/vendor, collection, product_class(=Fabric,
//     corrects the wrong "Wallcovering"), type=fabric, manufacturer_sku(=pattern_number)
//   VARIANT-level (per colorway): dw_sku, color, color_hex, manufacturer_sku(=per-color mfr_sku)
// Only cleanly-sourced fields are written — NO invented width/repeat/coverage (no catalog source).
//
// Cost basis for lookups: carnegie_catalog by variant sku (DWAG-*). Dry-run by default (writes a
// plan, touches nothing). --apply upserts metafields (POST = upsert by namespace+key). Reversible
// via the snapshot (old value per key; restore or clear).
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const DIR = new URL('.', import.meta.url).pathname;
const ENV = `${process.env.HOME}/Projects/secrets-manager/.env`;
const env = k => { const m = fs.readFileSync(ENV,'utf8').split('\n').find(l=>l.startsWith(k+'=')); return m? m.slice(k.length+1).trim().replace(/^["']|["']$/g,''):''; };
const TOKEN = env('SHOPIFY_ADMIN_TOKEN');
let SHOP = env('SHOPIFY_STORE_DOMAIN') || env('SHOPIFY_STORE'); if (SHOP && !SHOP.includes('.')) SHOP += '.myshopify.com';
const API = `https://${SHOP}/admin/api/2024-10`;
const H = { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' };
const MODE = process.argv[2] || 'dry';
const sleep = ms => new Promise(r=>setTimeout(r,ms));
const PSQL = ['/opt/homebrew/opt/postgresql@14/bin/psql','/usr/local/opt/postgresql@14/bin/psql','psql'].find(p=>{try{execFileSync(p,['--version'],{stdio:'ignore'});return true}catch{return false}})||'psql';
const q = sql => { const o=execFileSync(PSQL,['postgresql:///dw_unified?host=/tmp','-At','-F','\t','-c',sql],{encoding:'utf8',maxBuffer:128*1024*1024}); return o.trim()?o.trim().split('\n').map(r=>r.split('\t')):[]; };

async function shop(path, opts={}, tries=5){ for(let i=0;i<tries;i++){ const r=await fetch(`${API}${path}`,{headers:H,...opts}); if(r.status===429){await sleep(2000*(i+1));continue;} if(!r.ok) throw new Error(`HTTP ${r.status} ${path} :: ${(await r.text()).slice(0,150)}`); return r; } throw new Error('retries'); }
async function allCarnegie(){ const out=[]; let url=`/products.json?vendor=Carnegie&limit=250`; while(url){ const r=await shop(url); const j=await r.json(); out.push(...(j.products||[])); const link=r.headers.get('link')||''; const m=link.match(/<[^>]*[?&]page_info=([^>&]+)[^>]*>;\s*rel="next"/); url=m?`/products.json?limit=250&page_info=${m[1]}`:null; await sleep(500);} return out; }

// carnegie_catalog by sku → {pattern_name, pattern_number, color_name, mfr_sku, dw_class, primary_hex, collection_tag}
const cat = new Map();
for (const [sku,pn,pnum,cn,mfr,dc,hex,coll] of q(`select dw_sku,coalesce(pattern_name,''),coalesce(pattern_number,''),coalesce(color_name,''),coalesce(mfr_sku,''),coalesce(dw_class,''),coalesce(primary_hex,''),coalesce(collection_tag,'') from carnegie_catalog`))
  cat.set(sku,{pn,pnum,cn,mfr,dc,hex,coll});
console.log(`[mf] catalog rows: ${cat.size}`);

const products = await allCarnegie();
console.log(`[mf] live Carnegie products: ${products.length}`);

// build write plan
const prodWrites=[], varWrites=[]; let noCat=0;
const setP=(pid,ns,key,type,val,list)=>{ if(val==null||val==='')return; list.push({owner:'product',owner_id:pid,namespace:ns,key,type,value:String(val)}); };
const setV=(vid,ns,key,type,val,list)=>{ if(val==null||val==='')return; list.push({owner:'variant',owner_id:vid,namespace:ns,key,type,value:String(val)}); };

for (const p of products){
  // pattern-level facts from the product's non-sample variants
  const rows = (p.variants||[]).map(v=>cat.get(v.sku)).filter(Boolean);
  if(!rows.length){ noCat++; continue; }
  const r0 = rows.find(r=>r.pn) || rows[0];
  const patternClass = (r0.dc||'Fabric');                 // corrects the wrong "Wallcovering"
  for(const ns of ['global','custom','dwc']) setP(p.id, ns, 'pattern_name', 'single_line_text_field', r0.pn, prodWrites);
  for(const ns of ['global','custom','dwc']) setP(p.id, ns, ns==='global'?'Brand':'brand', 'single_line_text_field', 'Carnegie', prodWrites);
  setP(p.id,'custom','vendor','single_line_text_field','Carnegie',prodWrites);
  setP(p.id,'custom','product_class','single_line_text_field',patternClass,prodWrites);
  setP(p.id,'global','type','single_line_text_field','fabric',prodWrites);
  setP(p.id,'custom','collection_name','single_line_text_field','Carnegie Textiles',prodWrites);
  setP(p.id,'dwc','collection','single_line_text_field','Carnegie Textiles',prodWrites);
  for(const ns of ['custom','global','dwc']) setP(p.id, ns, 'manufacturer_sku', 'single_line_text_field', r0.pnum, prodWrites);

  // variant-level per-colorway identity
  for(const v of (p.variants||[])){
    if(/sample/i.test(v.title||'')||/sample/i.test(v.sku||'')) continue;
    const c = cat.get(v.sku); if(!c) continue;
    for(const ns of ['global','custom','dwc']) setV(v.id, ns, 'dw_sku', 'single_line_text_field', v.sku, varWrites);
    for(const ns of ['global','custom','dwc']) setV(v.id, ns, 'color', 'single_line_text_field', c.cn, varWrites);
    setV(v.id,'custom','color_hex','single_line_text_field',c.hex,varWrites);
    for(const ns of ['custom','global','dwc']) setV(v.id, ns, 'manufacturer_sku', 'single_line_text_field', c.mfr, varWrites);
  }
}

const stamp = q(`select to_char(now(),'YYYYMMDD-HH24MISS')`)[0][0];
const plan = { products: prodWrites, variants: varWrites };
fs.writeFileSync(`${DIR}mf-plan-${stamp}.json`, JSON.stringify(plan,null,2));
fs.writeFileSync(`${DIR}mf-plan-latest.json`, JSON.stringify(plan,null,2));
console.log(`\n=== METAFIELD FILL PLAN (${MODE.toUpperCase()}) ===`);
console.log(`  products covered      : ${products.length - noCat}`);
console.log(`  no-catalog-match      : ${noCat}`);
console.log(`  product metafield sets: ${prodWrites.length}`);
console.log(`  variant metafield sets: ${varWrites.length}`);
console.log(`  keys/product: pattern_name, Brand/brand, vendor, product_class(=Fabric), type, collection_name, manufacturer_sku (×namespaces)`);
console.log(`  keys/variant: dw_sku, color, color_hex, manufacturer_sku (×namespaces)`);
const ex = prodWrites.slice(0,6).concat(varWrites.slice(0,4));
for(const w of ex) console.log(`    ${w.owner} ${w.owner_id} ${w.namespace}.${w.key} = ${w.value}`);

if(MODE!=='apply'){ console.log(`\n[dry-run] nothing written. Re-run with 'apply' to fire the gated metafield writes (${prodWrites.length+varWrites.length} sets).`); process.exit(0); }

// APPLY (gated) — upsert via POST to owner metafields endpoint
console.log(`\n[apply] writing ${prodWrites.length+varWrites.length} metafields…`);
let ok=0,err=0; const errors=[];
async function put(w){
  const path = w.owner==='product' ? `/products/${w.owner_id}/metafields.json` : `/variants/${w.owner_id}/metafields.json`;
  await shop(path,{method:'POST',body:JSON.stringify({metafield:{namespace:w.namespace,key:w.key,type:w.type,value:w.value}})});
}
for(const w of [...prodWrites,...varWrites]){
  try{ await put(w); ok++; }catch(e){ err++; errors.push({o:w.owner_id,k:`${w.namespace}.${w.key}`,e:String(e.message||e).slice(0,100)}); }
  if((ok+err)%100===0) console.log(`  ${ok+err}/${prodWrites.length+varWrites.length} (ok ${ok}, err ${err})`);
  await sleep(560);
}
fs.writeFileSync(`${DIR}mf-apply-result-${stamp}.json`, JSON.stringify({stamp,ok,err,errors:errors.slice(0,50)},null,2));
console.log(`[apply] done: ${ok} ok, ${err} err.`);