`yb_qty_coverage_audit.js`

140 lines
#!/usr/bin/env node
// READ-ONLY York/Brewster double-roll MOQ coverage audit.
// Scope refinement (DTD Option C HYBRID): prefix-glob spine (sku:DWJS*, sku:DWQW*)
// UNIONed then FILTERED by actual York/Brewster VENDOR identity:
//   IN SCOPE  = vendor in ('Jeffrey Stevens','Malibu Wallpaper')
//   EXCLUDED  = Apartment Wallpaper, PS Removable Wallpaper, Fentucci (peel&stick / wrong vendor)
// Mural/fabric exemption (belt-and-suspenders OR-logic): EXEMPT if ANY of
//   global.unit_of_measure is a NON-ROLL unit (set AND does not contain 'roll')
//     -- NOTE: unit_of_measure stores phrases like "Priced Per Single Roll",
//        "Full Roll", "Sold Per Single Roll" (= ROLL goods, IN SCOPE) vs
//        "Priced Per Yard" / "Per Panel" (= non-roll, EXEMPT). A "Priced Per
//        Single Roll" good is precisely the double-roll-MOQ case (priced per
//        single, ordered in 2s) -- it is NOT exempt.
//   OR 'mural' in title / productType / tags
//   OR in a collection whose handle/title contains 'mural'
// Drift = in-scope ROLL product whose global.v_prods_quantity_order_min/units is missing or != 2.
// NOTHING is written. Pure audit. Output: drifted-SKU list + exclusion lists + counts.
const https = require('https');
const fs = require('fs');
const STORE='designer-laboratory-sandbox.myshopify.com';
const os=require('os');
function loadToken(){const k=process.env.SHOPIFY_ADMIN_TOKEN;if(k)return k;try{const e=fs.readFileSync(os.homedir()+'/Projects/secrets-manager/.env','utf8');const m=e.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m);if(m)return m[1].trim();}catch{}throw new Error('SHOPIFY_ADMIN_TOKEN not found (export it or set in secrets-manager/.env)');}
let TOKEN; // set below via loadToken()
TOKEN=loadToken();
const OUTDIR='/Users/macstudio3/Projects/dwjs-consolidation-2026-04-23/yb-audit';
fs.mkdirSync(OUTDIR,{recursive:true});

const IN_SCOPE_VENDORS = new Set(['Jeffrey Stevens','Malibu Wallpaper']);

function gql(b, retry=0){return new Promise((res,rej)=>{const d=JSON.stringify(b);const r=https.request({hostname:STORE,path:'/admin/api/2024-10/graphql.json',method:'POST',headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json','Content-Length':Buffer.byteLength(d)}},R=>{let c='';R.on('data',x=>c+=x);R.on('end',()=>{try{res(JSON.parse(c))}catch{if(retry<3)setTimeout(()=>res(gql(b,retry+1)),2000);else res({error:c.slice(0,300)})}})});r.on('error',err=>{if(retry<3)setTimeout(()=>res(gql(b,retry+1)),2000);else rej(err)});r.setTimeout(60000,()=>{r.destroy();if(retry<3)res(gql(b,retry+1));else rej(new Error('t'))});r.write(d);r.end()});}

async function bulkCheck(filter){
  const q=`{ products(query:"${filter}") { edges { node { id title vendor productType tags status
    m1: metafield(namespace:"global", key:"v_prods_quantity_order_min") { value }
    m2: metafield(namespace:"global", key:"v_prods_quantity_order_units") { value }
    m3: metafield(namespace:"global", key:"unit_of_measure") { value }
    collections { edges { node { handle title } } }
    variants { edges { node { id sku title
      v1: metafield(namespace:"custom", key:"v_prod_quantity_order_min") { value }
      v2: metafield(namespace:"custom", key:"v_prods_quantity_order_units") { value }
    } } }
  } } } }`;
  for(let i=0;i<10;i++){const r=await gql({query:'{ currentBulkOperation { status } }'});const st=r?.data?.currentBulkOperation?.status;if(!st||['COMPLETED','FAILED','CANCELED','EXPIRED'].includes(st))break;await new Promise(r=>setTimeout(r,3000));}
  const start=await gql({query:`mutation{ bulkOperationRunQuery(query: """${q}""") { bulkOperation{id} userErrors{message} } }`});
  const ue=start?.data?.bulkOperationRunQuery?.userErrors;
  if(ue&&ue.length) throw new Error('bulk start: '+JSON.stringify(ue));
  for(;;){
    await new Promise(r=>setTimeout(r,4000));
    const p=await gql({query:'{ currentBulkOperation { status objectCount url } }'});
    const op=p?.data?.currentBulkOperation;
    console.log(`  bulk ${op?.status} objects=${op?.objectCount||0}`);
    if(op?.status==='COMPLETED') return op.url;
    if(['FAILED','CANCELED','EXPIRED'].includes(op?.status)) throw new Error(op.status);
  }
}
function dl(url,file){return new Promise((r,j)=>{const o=fs.createWriteStream(file);https.get(url,res=>{res.pipe(o);o.on('finish',()=>o.close(()=>r(file)))}).on('error',j)})}

function isMural(p){
  const blob = `${p.title} ${p.productType} ${p.tags}`.toLowerCase();
  if (/mural/.test(blob)) return 'title/type/tags';
  for (const c of p.collections) { if (/mural/i.test(`${c.handle} ${c.title}`)) return 'collection:'+c.handle; }
  return null;
}

(async()=>{
  const products = new Map();
  for (const filter of ['sku:DWJS*','sku:DWQW*']) {
    console.log(`\n=== bulk pull ${filter} ===`);
    const url = await bulkCheck(filter);
    const file = `${OUTDIR}/raw_${filter.replace(/\W/g,'')}.jsonl`;
    await dl(url, file);
    for (const line of fs.readFileSync(file,'utf8').split('\n').filter(Boolean)) {
      const o = JSON.parse(line);
      if (o.id?.includes('/Product/')) products.set(o.id, {
        id:o.id, title:o.title||'', vendor:o.vendor||'', productType:o.productType||'',
        tags:o.tags||'', status:o.status||'',
        m1:o.m1?.value??null, m2:o.m2?.value??null, m3:o.m3?.value??null,
        collections:[], variants:[],
      });
      else if (o.id?.includes('/Collection/') && o.__parentId) { const p=products.get(o.__parentId); if(p)p.collections.push({handle:o.handle||'',title:o.title||''}); }
      else if (o.id?.includes('/ProductVariant/') && o.__parentId) {
        const p=products.get(o.__parentId);
        if(p)p.variants.push({id:o.id,sku:o.sku||'',title:o.title||'',v1:o.v1?.value??null,v2:o.v2?.value??null});
      }
    }
  }

  const stats = {
    pulled_products: products.size,
    excluded_vendor: {}, // vendor -> count of products dropped (not in-scope vendor)
    in_scope_total: 0,
    mural_exempt: [], fabric_exempt: [],
    ok_2_2: 0,
    drifted: [],         // {dw_sku, vendor, m1, m2, reason}
    drifted_active: 0, drifted_nonactive: 0,
  };

  for (const p of products.values()) {
    if (!IN_SCOPE_VENDORS.has(p.vendor)) {
      stats.excluded_vendor[p.vendor]=(stats.excluded_vendor[p.vendor]||0)+1;
      continue;
    }
    // representative dw_sku = main (non-sample) variant sku, else first
    const main = p.variants.find(v=>!/sample/i.test(v.sku+v.title)) || p.variants[0] || {sku:''};
    const dw_sku = main.sku;
    stats.in_scope_total++;

    const muralReason = isMural(p);
    const unit = (p.m3||'').toLowerCase();
    const nonRoll = unit && !/roll/.test(unit); // 'priced per yard', 'per panel' etc. ROLL phrases stay in scope.
    if (nonRoll) { stats.fabric_exempt.push({dw_sku,vendor:p.vendor,unit:p.m3,type:p.productType}); continue; }
    if (muralReason) { stats.mural_exempt.push({dw_sku,vendor:p.vendor,reason:muralReason,title:p.title}); continue; }

    // ROLL product (or unit unset, default ROLL) -> must be 2/2 at product level
    if (p.m1==='2' && p.m2==='2') { stats.ok_2_2++; }
    else {
      const reason = (p.m1==null&&p.m2==null)?'missing-both':(p.m1!=='2'?'min!=2':'units!=2');
      stats.drifted.push({dw_sku,vendor:p.vendor,status:p.status,m1:p.m1,m2:p.m2,reason,product_id:p.id});
      if (p.status==='ACTIVE') stats.drifted_active++; else stats.drifted_nonactive++;
    }
  }

  // Also write the gated-write payload (drifted product ids) for qty_backfill consumption
  fs.writeFileSync(`${OUTDIR}/drifted_skus.json`, JSON.stringify(stats.drifted,null,2));
  fs.writeFileSync(`${OUTDIR}/mural_exempt.json`, JSON.stringify(stats.mural_exempt,null,2));
  fs.writeFileSync(`${OUTDIR}/fabric_exempt.json`, JSON.stringify(stats.fabric_exempt,null,2));
  fs.writeFileSync(`${OUTDIR}/excluded_vendor.json`, JSON.stringify(stats.excluded_vendor,null,2));

  console.log('\n========== YORK/BREWSTER DOUBLE-ROLL MOQ AUDIT ==========');
  console.log('pulled products (DWJS* ∪ DWQW*):', stats.pulled_products);
  console.log('excluded by vendor identity:', JSON.stringify(stats.excluded_vendor));
  console.log('IN-SCOPE total (Jeffrey Stevens + Malibu Wallpaper):', stats.in_scope_total);
  console.log('  fabric/non-ROLL exempt (unit != ROLL):', stats.fabric_exempt.length);
  console.log('  mural exempt:', stats.mural_exempt.length);
  console.log('  OK already 2/2:', stats.ok_2_2);
  console.log('  DRIFTED (single-roll-orderable):', stats.drifted.length,
              `(ACTIVE=${stats.drifted_active}, non-active=${stats.drifted_nonactive})`);
  console.log('files ->', OUTDIR);
})().catch(e=>{console.error(e);process.exit(1)});