`scripts/metafield-coverage/metafield-coverage-audit.mjs`

171 lines
// metafield-coverage-audit — read-only Shopify GraphQL audit of enrichment
// metafield coverage per vendor, for the standing rule "discover price+specs+
// images per SKU and ALWAYS write to Shopify metafields" (repped vendors).
//
// For every ACTIVE product it records, per vendor:
//   • images:  has >=1 image, has >=3 images   (the "ALL images" half of the rule)
//   • specs:   has custom.material AND custom.width   (the core spec metafields)
//   • color:   has custom.color_hex OR custom.color_details (the AI color-enrichment)
//   • pattern: has custom.pattern_name
// Price coverage is intentionally NOT re-pulled here — the GMC dry-run already
// maps which products are sample-only ($4.25) vs have a real roll; pulling 60
// variants/product would blow the GraphQL cost cap. This audit is specs+images.
//
// Vendors are tagged from dw_unified.vendor_registry: repped = is_active AND
// NOT is_private_label AND discount_confirmed (the confirmed trade lines DW reps).
//
//   node metafield-coverage-audit.mjs [--min 60] [--page 50]
// READ-ONLY (GraphQL queries + metafield reads only, no mutations). $0.
import fs from 'node:fs';
import { execFileSync } from 'node:child_process';

const args = process.argv.slice(2);
const MIN = parseInt(args.find((_,i,a)=>a[i-1]==='--min')||'60',10)||60; // WARN threshold % per axis
const PAGE = parseInt(args.find((_,i,a)=>a[i-1]==='--page')||'50',10)||50;
const SHOP='designer-laboratory-sandbox.myshopify.com', VERSION='2024-10';
const ENV=fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env','utf8');
const TOKEN=(ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1];
if(!TOKEN){console.error('no SHOPIFY_ADMIN_TOKEN');process.exit(1);}
const URL=`https://${SHOP}/admin/api/${VERSION}/graphql.json`;
const OUT=`${process.env.HOME}/.claude/yolo-queue/metafield-coverage-2026-06-16.json`;
const MD =`${process.env.HOME}/.claude/yolo-queue/metafield-coverage-2026-06-16.md`;
const sleep=ms=>new Promise(r=>setTimeout(r,ms));

// --- repped tags from vendor_registry (case-insensitive vendor_name map) ---
function reppedTags(){
  const PSQL='/opt/homebrew/opt/postgresql@14/bin/psql';
  const DB='postgresql:///dw_unified?host=/tmp';
  let rows=[];
  try{
    const out=execFileSync(PSQL,[DB,'-At','-F','\t','-c',
      "select lower(vendor_name), is_active, is_private_label, coalesce(discount_confirmed,false) from vendor_registry;"],{encoding:'utf8'});
    rows=out.trim().split('\n').filter(Boolean).map(l=>l.split('\t'));
  }catch(e){ console.error('warn: vendor_registry read failed ('+e.message.slice(0,40)+') — tags will be blank'); }
  const m=new Map();
  for(const [name,act,pl,disc] of rows){
    const repped = act==='t' && pl!=='t' && disc==='t';
    m.set(name,{is_active:act==='t',private_label:pl==='t',discount_confirmed:disc==='t',repped});
  }
  return m;
}

async function gql(q,v){
  for(let a=0;a<8;a++){
    let res;
    try{ res=await fetch(URL,{method:'POST',headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'},body:JSON.stringify({query:q,variables:v})}); }
    catch(e){ await sleep(2000*(a+1)); continue; }
    if(res.status===429){ await sleep(2000*(a+1)); continue; }
    const j=await res.json();
    if(j.errors){ if(JSON.stringify(j.errors).match(/THROTTLED|exceeded/i)){ await sleep(2500*(a+1)); continue;} throw new Error(JSON.stringify(j.errors).slice(0,200)); }
    const avail=j.extensions?.cost?.throttleStatus?.currentlyAvailable??2000;
    if(avail<300) await sleep(1500);
    return j.data;
  }
  throw new Error('exhausted retries');
}

const QUERY=`query($cursor:String){
  products(first:${PAGE}, after:$cursor, query:"status:active"){
    pageInfo{ hasNextPage endCursor }
    nodes{
      vendor
      images(first:3){ nodes{ id } }
      material: metafield(namespace:"custom", key:"material"){ value }
      width: metafield(namespace:"custom", key:"width"){ value }
      colorHex: metafield(namespace:"custom", key:"color_hex"){ value }
      colorDetails: metafield(namespace:"custom", key:"color_details"){ value }
      patternName: metafield(namespace:"custom", key:"pattern_name"){ value }
      wGlobal: metafield(namespace:"global", key:"width"){ value }
      wPillow: metafield(namespace:"pillow", key:"width_inches"){ value }
      dimPillow: metafield(namespace:"pillow", key:"dimensions"){ value }
      mContents: metafield(namespace:"global", key:"Contents"){ value }
      mPillow: metafield(namespace:"pillow", key:"face_content"){ value }
    }
  }
}`;

// has SOME width signal across known namespaces
const hasWidth = p => !!(p.width?.value || p.wGlobal?.value || p.wPillow?.value || p.dimPillow?.value);
// has SOME material/content signal across known namespaces
const hasMaterial = p => !!(p.material?.value || p.mContents?.value || p.mPillow?.value);

function blank(){return {n:0, img1:0, img3:0, specs:0, specsStrict:0, color:0, pattern:0};}

(async()=>{
  const tags=reppedTags();
  const byVendor=new Map();
  let cursor=null, total=0, pages=0; const t0=Date.now();
  while(true){
    const d=await gql(QUERY,{cursor});
    const c=d.products;
    for(const p of c.nodes){
      total++;
      const v=p.vendor||'(none)';
      if(!byVendor.has(v)) byVendor.set(v,blank());
      const s=byVendor.get(v); s.n++;
      const imgs=p.images.nodes.length;
      if(imgs>=1) s.img1++;
      if(imgs>=3) s.img3++;
      if(p.material?.value && p.width?.value) s.specsStrict++;       // c26 definition (custom.* only)
      if(hasMaterial(p) && hasWidth(p)) s.specs++;                   // c28 hardened (any namespace)
      if(p.colorHex?.value || p.colorDetails?.value) s.color++;
      if(p.patternName?.value) s.pattern++;
    }
    pages++;
    if(pages%50===0) process.stderr.write(`  ...${pages} pages, ${total} products, ${byVendor.size} vendors\n`);
    if(!c.pageInfo.hasNextPage) break;
    cursor=c.pageInfo.endCursor;
  }
  const elapsed=((Date.now()-t0)/1000).toFixed(1);
  const pct=(x,n)=>n?Math.round(1000*x/n)/10:0;

  const vendors=[...byVendor.entries()].map(([vendor,s])=>{
    const t=tags.get(vendor.toLowerCase())||null;
    return { vendor, repped: t?.repped??null, private_label: t?.private_label??null,
      active_products:s.n,
      img1_pct:pct(s.img1,s.n), img3_pct:pct(s.img3,s.n),
      specs_pct:pct(s.specs,s.n), specs_strict_pct:pct(s.specsStrict,s.n),
      color_pct:pct(s.color,s.n), pattern_pct:pct(s.pattern,s.n) };
  }).sort((a,b)=>b.active_products-a.active_products);

  // repped vendors below MIN on any axis = the gap list
  const reppedV = vendors.filter(v=>v.repped===true);
  const gaps = reppedV.filter(v=>v.active_products>=50 &&
    (v.img1_pct<MIN || v.specs_pct<MIN || v.color_pct<MIN));

  const report={ generated_at:new Date().toISOString(), scanned_active:total, pages, elapsed_s:+elapsed,
    warn_below_pct:MIN, vendor_count:vendors.length,
    repped_vendor_count:reppedV.length, repped_gap_count:gaps.length,
    repped_gaps:gaps, vendors };
  fs.writeFileSync(OUT, JSON.stringify(report,null,2));

  // overall coverage (repped only, weighted by product count)
  const sum=(arr,k)=>arr.reduce((a,v)=>a+v[k]*v.active_products,0);
  const totR=reppedV.reduce((a,v)=>a+v.active_products,0)||1;
  const wimg1=Math.round(10*sum(reppedV,'img1_pct')/totR)/10;
  const wspecs=Math.round(10*sum(reppedV,'specs_pct')/totR)/10;
  const wspecsStrict=Math.round(10*sum(reppedV,'specs_strict_pct')/totR)/10;
  const wcolor=Math.round(10*sum(reppedV,'color_pct')/totR)/10;

  let md=`# Shopify metafield-coverage audit (repped vendors) — ${new Date().toISOString().slice(0,16)}\n\n`;
  md+=`**READ-ONLY (GraphQL metafield reads, no writes), \$0.** Scanned **${total}** ACTIVE products in ${elapsed}s (${pages} pages). `;
  md+=`Standing rule: repped vendors must have price+specs+images discovered → written to Shopify metafields. Price coverage tracked separately (GMC \$4.25 scan); this audit = **specs + images + color-enrichment** metafields.\n\n`;
  md+=`Repped = vendor_registry is_active AND NOT private_label AND discount_confirmed. Repped vendors: **${reppedV.length}** (${totR} active products).\n\n`;
  md+=`**c28 hardening:** \"specs\" now accepts width from custom.width / global.width / pillow.width_inches / pillow.dimensions AND material from custom.material / global.Contents / pillow.face_content (was custom-only in c26 → caused Scalamandre pillow false-0%). \"specs-strict\" = the old custom-only number, shown alongside so the namespace-artifact delta is visible.\n\n`;
  md+=`## Weighted repped coverage\n`;
  md+=`| Axis | Coverage |\n|---|---:|\n| ≥1 image | ${wimg1}% |\n| **specs (any namespace)** | **${wspecs}%** |\n| specs-strict (custom.* only, c26) | ${wspecsStrict}% |\n| color-enrichment (hex/details) | ${wcolor}% |\n\n`;
  md+=`## 🔴 Repped vendors below ${MIN}% on image / specs / color (≥50 active products) — ${gaps.length}\n`;
  md+=`| Vendor | Active | ≥1img% | ≥3img% | specs% | specs-strict% | color% | pattern% |\n|---|---:|---:|---:|---:|---:|---:|---:|\n`;
  for(const v of gaps) md+=`| ${v.vendor} | ${v.active_products} | ${v.img1_pct} | ${v.img3_pct} | **${v.specs_pct}** | ${v.specs_strict_pct} | ${v.color_pct} | ${v.pattern_pct} |\n`;
  md+=`\n## All vendors (top 50 by active count)\n`;
  md+=`| Vendor | Repped | Active | ≥1img% | ≥3img% | specs% | specs-strict% | color% | pattern% |\n|---|:--:|---:|---:|---:|---:|---:|---:|---:|\n`;
  for(const v of vendors.slice(0,50)) md+=`| ${v.vendor} | ${v.repped===true?'✓':v.repped===false?'·':'?'} | ${v.active_products} | ${v.img1_pct} | ${v.img3_pct} | ${v.specs_pct} | ${v.specs_strict_pct} | ${v.color_pct} | ${v.pattern_pct} |\n`;
  md+=`\n_Repped ✓ = confirmed trade line · · = active non-repped · ? = not matched in vendor_registry. \"specs\" = (any width namespace) AND (any material namespace). A vendor where specs >> specs-strict was a namespace artifact, not a true gap._\n`;
  fs.writeFileSync(MD,md);

  console.log(`[metafield-coverage] scanned=${total} vendors=${vendors.length} repped=${reppedV.length} gaps=${gaps.length}`);
  console.log(`  weighted repped: img>=1 ${wimg1}% · specs ${wspecs}% (strict ${wspecsStrict}%) · color ${wcolor}%`);
  console.log(`Report: ${MD}`);
  process.exit(0);
})().catch(e=>{console.error('FATAL',e.message);process.exit(1);});