`tmp-kravet-finish-collection-clean.mjs`

88 lines
#!/usr/bin/env node
// Kravet metafield cleanup — normalize global.Finish (strip trailing ';' artifacts, split internal ';' -> ', ', Title Case)
// and Title-Case global.Collection (preserving Roman numerals). Overwrite-authoritative, ledger-resumable.
// Touches ONLY global.Finish + global.Collection on active Kravet-family products. Prices/other fields untouched.
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 DRY = !process.argv.includes('--apply');
const LIMIT = (()=>{const i=process.argv.indexOf('--limit'); return i>=0?Number(process.argv[i+1]):null;})();

// --- transforms ---
const SMALL=new Set(["and","or","the","of","in","a","an","for","to","with","by"]);
const ROMAN=/^(?:I{1,3}|IV|VI{0,3}|IX|XI{0,3})$/;      // I..XIII, the realistic collection numerals
const capWord=p=>!p?p:(/^\d/.test(p)?p:p[0].toUpperCase()+p.slice(1).toLowerCase());
function titleCase(s){
  return String(s).split(/\s+/).map((w,i)=>{
    if(!w) return w;
    const low=w.toLowerCase();
    if(i>0 && SMALL.has(low)) return low;
    if(ROMAN.test(w)) return w.toUpperCase();          // standalone Roman numeral (Garden II)
    if(w.includes('&')){                                // &-token: preserve short all-caps initialism parts
      return w.split(/([&\-])/).map(part =>
        (part==='&'||part==='-') ? part
        : /^[A-Z0-9]{1,3}$/.test(part) ? part           // B, GP, J
        : capWord(part)).join('');
    }
    return w.split('-').map(capWord).join('-');          // normal token, hyphen-aware
  }).join(" ").replace(/\s+/g," ").trim();
}
const cleanFinish=v=>String(v).split(";").map(t=>t.trim()).filter(Boolean).map(titleCase).join(", ");

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;
}

// same target set as the backfill
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};});

const MF_LEDGER='/Users/macstudio3/kravet-feed-pull/finish-collection-done.txt';
const done=new Set(fs.existsSync(MF_LEDGER)?fs.readFileSync(MF_LEDGER,'utf8').trim().split('\n').filter(Boolean):[]);
const rows2=rows.filter(r=>!done.has(r.sku));

// bulk-load feed c14 (Finish) + c17 (Collection) keyed by mfr
const wanted=[...new Set(rows2.map(r=>r.mfr))];
const feed={};
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), c14, c17 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 [k,c14,c17]=line.split('\t'); feed[k]=[c14,c17]; }
}
console.log(`targets:${rows2.length} (of ${rows.length}, ${done.size} done)  feed:${Object.keys(feed).length}  ${DRY?'[DRY RUN]':''}`);

let ok=0,fail=0,fSet=0,cSet=0,shown=0;
for(const {sku,id,mfr} of rows2){
  try{
    const vals=feed[mfr]; if(!vals){fail++; if(!DRY)fs.appendFileSync(MF_LEDGER,sku+'\n'); continue;}
    const [rawFin,rawColl]=vals;
    const mfs=[];
    let fClean,cClean;
    if(rawFin!=null && String(rawFin).trim() && String(rawFin).toUpperCase()!=='NULL'){ fClean=cleanFinish(rawFin);
      if(fClean) mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Finish',type:'single_line_text_field',value:fClean.slice(0,255)}); }
    if(rawColl!=null && String(rawColl).trim() && String(rawColl).toUpperCase()!=='NULL'){ cClean=titleCase(rawColl);
      if(cClean) mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Collection',type:'single_line_text_field',value:cClean.slice(0,255)}); }
    if(DRY){ if(shown<12 && (fClean||cClean)){ console.log(`  ${sku}: Finish "${rawFin}"->"${fClean}"  Collection "${rawColl}"->"${cClean}"`); shown++; } if(fClean)fSet++; if(cClean)cSet++; ok++; continue; }
    if(mfs.length){ const d=await gql(`mutation($m:[MetafieldsSetInput!]!){metafieldsSet(metafields:$m){userErrors{message}}}`,{m:mfs});
      const errs=d.metafieldsSet.userErrors; if(errs.length) throw new Error(JSON.stringify(errs).slice(0,160)); }
    if(fClean)fSet++; if(cClean)cSet++; ok++; fs.appendFileSync(MF_LEDGER,sku+'\n');
    if(ok%300===0) console.log(`  ...${ok}/${rows2.length}  (Finish:${fSet} Collection:${cSet} fail:${fail})`);
    await sleep(90);
  }catch(e){ fail++; if(fail<=20)console.error(`  FAIL ${sku}: ${e.message}`); }
}
console.log(`\n${DRY?'DRY':'FINISH/COLLECTION'} DONE. products:${ok}  Finish set:${fSet}  Collection set:${cSet}  failures:${fail}`);