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

83 lines
#!/usr/bin/env node
// Catalog-wide Finish/Collection cosmetic cleanup (ALL vendors), driven from the fresh mirror.
// Title-Case all-caps global.Collection + normalize semicolon global.Finish. Writes Shopify AND the mirror.
// Ledger-resumable. Only touches products whose value actually changes.
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;})();

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})$/;
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();
    if(w.includes('&')) return w.split(/([&\-])/).map(part=>(part==='&'||part==='-')?part:/^[A-Z0-9]{1,3}$/.test(part)?part:capWord(part)).join('');
    return w.split('-').map(capWord).join('-');
  }).join(" ").replace(/\s+/g," ").trim();
}
const cleanFinish=v=>String(v).split(";").map(t=>t.trim()).filter(Boolean).map(titleCase).join(", ");
const isAllCaps=s=>/[A-Za-z]/.test(s)&&s===s.toUpperCase();

async function gql(q,v,t=0){
  const r=await fetch(GQL,{method:'POST',headers:H,body:JSON.stringify({query:q,variables:v})});
  if(r.status===429||r.status>=500){if(t<6){await sleep(1500*(t+1));return gql(q,v,t+1);}}
  const j=await r.json();
  if(j.errors){if(t<6&&JSON.stringify(j.errors).includes('Throttled')){await sleep(2000*(t+1));return gql(q,v,t+1);}throw new Error(JSON.stringify(j.errors).slice(0,180));}
  return j.data;
}
const psql=sql=>execFileSync('psql',['-h','/tmp','-d','dw_unified','-tA','-F','\t','-c',sql],{maxBuffer:1<<28}).toString();

// targets: active products where Collection is all-caps OR Finish has ';'
const rows=psql(`
  SELECT replace(shopify_id,'gid://shopify/Product/','') AS id, sku,
         coalesce(metafields->'global'->'Collection'->>'value',''),
         coalesce(metafields->'global'->'Finish'->>'value','')
  FROM shopify_products
  WHERE status='ACTIVE' AND metafields->'global' IS NOT NULL
    AND ( (metafields->'global'->'Collection'->>'value') ~ '[A-Za-z]' AND (metafields->'global'->'Collection'->>'value')=upper(metafields->'global'->'Collection'->>'value')
          OR (metafields->'global'->'Finish'->>'value') LIKE '%;%' )
  ORDER BY sku ${LIMIT?`LIMIT ${LIMIT}`:''};`).trim().split('\n').filter(Boolean).map(l=>{const[id,sku,coll,fin]=l.split('\t');return{id,sku,coll,fin};});

const LEDGER='/Users/macstudio3/kravet-feed-pull/catalog-fincoll-done.txt';
const done=new Set(fs.existsSync(LEDGER)?fs.readFileSync(LEDGER,'utf8').trim().split('\n').filter(Boolean):[]);
const todo=rows.filter(r=>!done.has(r.sku));
console.log(`candidates:${rows.length}  todo:${todo.length}  ${DRY?'[DRY]':''}`);

let ok=0,cSet=0,fSet=0,fail=0,shown=0;
for(const {id,sku,coll,fin} of todo){
  try{
    let newColl,newFin;
    if(coll && isAllCaps(coll)){ const t=titleCase(coll); if(t&&t!==coll) newColl=t.slice(0,255); }
    if(fin && fin.includes(';')){ const t=cleanFinish(fin); if(t&&t!==fin) newFin=t.slice(0,255); }
    if(!newColl && !newFin){ if(!DRY)fs.appendFileSync(LEDGER,sku+'\n'); ok++; continue; }
    if(DRY){ if(shown<15){console.log(`  ${sku}: ${newColl?`Coll "${coll}"->"${newColl}"`:''} ${newFin?`Fin "${fin}"->"${newFin}"`:''}`);shown++;} if(newColl)cSet++; if(newFin)fSet++; ok++; continue; }
    const mfs=[];
    if(newColl) mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Collection',type:'single_line_text_field',value:newColl});
    if(newFin)  mfs.push({ownerId:`gid://shopify/Product/${id}`,namespace:'global',key:'Finish',type:'single_line_text_field',value:newFin});
    const d=await gql(`mutation($m:[MetafieldsSetInput!]!){metafieldsSet(metafields:$m){userErrors{message}}}`,{m:mfs});
    if(d.metafieldsSet.userErrors.length) throw new Error(JSON.stringify(d.metafieldsSet.userErrors).slice(0,160));
    // keep the mirror consistent too
    const sets=[]; if(newColl)sets.push(`jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text))`);
    if(newColl&&newFin){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
      `UPDATE shopify_products SET metafields=jsonb_set(jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text)),'{global,Finish,value}',to_jsonb('${newFin.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
    else if(newColl){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
      `UPDATE shopify_products SET metafields=jsonb_set(metafields,'{global,Collection,value}',to_jsonb('${newColl.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
    else if(newFin){ execFileSync('psql',['-h','/tmp','-d','dw_unified','-c',
      `UPDATE shopify_products SET metafields=jsonb_set(metafields,'{global,Finish,value}',to_jsonb('${newFin.replace(/'/g,"''")}'::text)) WHERE shopify_id='gid://shopify/Product/${id}';`]); }
    if(newColl)cSet++; if(newFin)fSet++; ok++; fs.appendFileSync(LEDGER,sku+'\n');
    if(ok%200===0) console.log(`  ...${ok}/${todo.length}  (Collection:${cSet} Finish:${fSet} fail:${fail})`);
    await sleep(90);
  }catch(e){ fail++; if(fail<=20)console.error(`  FAIL ${sku}: ${e.message}`); }
}
console.log(`\n${DRY?'DRY':'CATALOG FIN/COLL'} DONE. products:${ok}  Collection:${cSet}  Finish:${fSet}  fail:${fail}`);