`tmp-kravet-alldata.mjs`

79 lines
#!/usr/bin/env node
// Kravet ALL-DATA metafield backfill — every feed column -> global.<header>, overwrite-authoritative.
// Excludes nothing except pure image-src plumbing (images handled separately). Sellable variant PRICES are NOT touched.
import fs from 'node:fs';
import { execFileSync } from 'node:child_process';
const env = Object.fromEntries(fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env','utf8')
  .split('\n').filter(l=>l.includes('=')).map(l=>{const i=l.indexOf('='); return [l.slice(0,i).trim(), l.slice(i+1).trim()];}));
const DOMAIN=(env.SHOPIFY_STORE_DOMAIN||env.SHOPIFY_STORE).replace(/^https?:\/\//,'').replace(/\/$/,'');
const GQL=`https://${DOMAIN}/admin/api/2024-10/graphql.json`;
const H={'X-Shopify-Access-Token':env.SHOPIFY_ADMIN_TOKEN,'Content-Type':'application/json'};
const sleep=ms=>new Promise(r=>setTimeout(r,ms));
const LIMIT = process.argv[2]?Number(process.argv[2]):null;

// header -> c-index
const hmap={}, headers=[];
for(const ln of fs.readFileSync('/tmp/kravet_feed_header.txt','utf8').trim().split('\n')){
  const [c,h]=ln.split('\t'); const hh=h.trim(); hmap[c]=hh; headers.push([c,hh]);
}
// ALL columns as data EXCEPT identity/plumbing dupes that add no value as metafields
const SKIP=new Set(['Image Src','Image Position']); // (feed has no these; guard anyway)
const cols = headers.map(([c])=>c); // c1..c55

async function gql(query,variables,tries=0){
  const r=await fetch(GQL,{method:'POST',headers:H,body:JSON.stringify({query,variables})});
  if(r.status===429||r.status===502||r.status===503){ if(tries<6){await sleep(1500*(tries+1)); return gql(query,variables,tries+1);} }
  const j=await r.json();
  if(j.errors){ if(tries<6 && JSON.stringify(j.errors).includes('Throttled')){await sleep(2000*(tries+1)); return gql(query,variables,tries+1);} throw new Error(JSON.stringify(j.errors).slice(0,180)); }
  return j.data;
}

const rows=execFileSync('psql',['-h','/tmp','-d','dw_unified','-tA','-F','|','-c',`
  SELECT s.sku, replace(s.shopify_id,'gid://shopify/Product/',''), upper(s.mfr_sku)
  FROM shopify_products s
  WHERE s.status='ACTIVE' AND EXISTS(SELECT 1 FROM kravet_feed_stage f WHERE upper(f.c1)=upper(s.mfr_sku))
    AND s.vendor ILIKE ANY(ARRAY['%brunschwig%','%lee jofa%','%kravet%','%cole%','%baker%','%mulberry%','%clarke%','%groundworks%','%colefax%','%threads%','%andrew martin%','%aerin%','%barclay%','%thom filicia%'])
  ORDER BY s.sku ${LIMIT?`LIMIT ${LIMIT}`:''};`]).toString().trim().split('\n').map(l=>{const[sku,id,mfr]=l.split('|');return{sku,id,mfr};});

// resume ledger (so a watchdog restart continues instead of redoing everything)
const MF_LEDGER='/Users/macstudio3/kravet-feed-pull/alldata-done.txt';
const mfDone=new Set(fs.existsSync(MF_LEDGER)?fs.readFileSync(MF_LEDGER,'utf8').trim().split('\n').filter(Boolean):[]);
const rows2=rows.filter(r=>!mfDone.has(r.sku));

// bulk-load the feed rows we need into a JS map keyed by mfr, using a single psql dump
const wanted=[...new Set(rows2.map(r=>r.mfr))];
const sel=cols.join(',');
const feed={};
// chunk the IN() to keep query size sane
for(let i=0;i<wanted.length;i+=2000){
  const chunk=wanted.slice(i,i+2000).map(m=>`'${m.replace(/'/g,"''")}'`).join(',');
  const out=execFileSync('psql',['-h','/tmp','-d','dw_unified','-tA','-F','\t','-c',
    `SELECT upper(c1), ${sel} FROM kravet_feed_stage WHERE upper(c1) IN (${chunk});`],{maxBuffer:1<<28}).toString().trim();
  for(const line of out.split('\n')){ if(!line)continue; const parts=line.split('\t'); const key=parts[0]; feed[key]=parts.slice(1); }
}
console.log(`targets:${rows2.length} (of ${rows.length}, ${mfDone.size} already done)  feed rows loaded:${Object.keys(feed).length}`);

let ok=0,fail=0,totalMF=0;
for(const {sku,id,mfr} of rows2){
  try{
    const vals=feed[mfr]; if(!vals){fail++;fs.appendFileSync(MF_LEDGER,sku+'\n');continue;}
    const mfs=[];
    cols.forEach((c,idx)=>{
      const hdr=hmap[c]; if(SKIP.has(hdr)) return;
      let v=vals[idx]; if(v==null) return; v=String(v).trim();
      if(v===''||v.toUpperCase()==='NULL'||v==='0'||v==='0.0') return;
      mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:hdr,type:'single_line_text_field',value:v.slice(0,255)});
    });
    if(!mfs.length) continue;
    for(let i=0;i<mfs.length;i+=25){
      const d=await gql(`mutation($m:[MetafieldsSetInput!]!){metafieldsSet(metafields:$m){userErrors{message}}}`,{m:mfs.slice(i,i+25)});
      const errs=d.metafieldsSet.userErrors; if(errs.length) throw new Error(JSON.stringify(errs).slice(0,160));
      await sleep(120);
    }
    ok++; totalMF+=mfs.length; fs.appendFileSync(MF_LEDGER,sku+'\n');
    if(ok%200===0) console.log(`  ...${ok}/${rows2.length}  (${totalMF} metafields, ${fail} fail)`);
  }catch(e){ fail++; if(fail<=20)console.error(`  FAIL ${sku}: ${e.message}`); }
  await sleep(90);
}
console.log(`\nALL-DATA DONE. products:${ok}/${rows.length}  metafields:${totalMF}  avg:${ok?(totalMF/ok).toFixed(1):0}  failures:${fail}`);