`verification/tk10836/verify-freeship-valence.js`

61 lines
#!/usr/bin/env node
// TK-11366 final question: was full-price "Free Shipping (No Tracking)" INTENDED or a LEAK?
// Hypothesis: pre-2026-09-09 that rate lived on the GENERAL profile gated by TOTAL WEIGHT <= 0.5 lb
// (per TK-11333's own baseline note). Any order whose products have NO weight set totals 0 lb,
// clears the gate, and ships free regardless of cart value -> a leak in DW's favour, not a
// customer benefit by design. Test: compare product weights on pre-window full-price orders that
// shipped FREE vs those that PAID. READ-ONLY.
const fs=require('fs'),os=require('os');
for (const line of fs.readFileSync(os.homedir()+'/Projects/secrets-manager/.env','utf8').split('\n')) {
  const m=line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)$/); if(!m) continue;
  let v=m[2].trim().replace(/^['"]|['"]$/g,''); if(!process.env[m[1]]) process.env[m[1]]=v;
}
const STORE=process.env.SHOPIFY_STORE_DOMAIN, TOKEN=process.env.SHOPIFY_FULL_ACCESS_TOKEN, API='2024-10';
async function gql(q,v={}){const r=await fetch(`https://${STORE}/admin/api/${API}/graphql.json`,{method:'POST',
  headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'},body:JSON.stringify({query:q,variables:v})});
  const j=await r.json(); if(j.errors)console.error('ERR',JSON.stringify(j.errors).slice(0,300)); return j.data;}

const Q=`query($c:String){ orders(first:25, after:$c, query:"created_at:>2026-07-01 created_at:<2026-08-18", sortKey:CREATED_AT){
  pageInfo{hasNextPage endCursor}
  nodes{ name createdAt
    subtotalPriceSet{shopMoney{amount}} totalShippingPriceSet{shopMoney{amount}}
    shippingLine{title} shippingAddress{countryCodeV2}
    lineItems(first:15){ nodes{ sku title quantity
      variant{ inventoryItem{ requiresShipping measurement{ weight{unit value} } } } } } } } }`;
(async()=>{
  let c=null, rows=[], pages=0;
  do{ const d=await gql(Q,{c}); if(!d) break; pages++;
    for(const o of d.orders.nodes){
      const lis=o.lineItems.nodes; if(!lis.length) continue;
      if(o.shippingAddress?.countryCodeV2!=='US') continue;
      const isS=li=>/(^|-)sample/i.test(li.sku||'')||/sample|memo|swatch/i.test(li.title||'');
      if(!lis.every(li=>!isS(li))) continue;                       // NON-sample only
      let wt=0, unweighted=0, tot=0;
      for(const li of lis){
        const w=li.variant?.inventoryItem?.measurement?.weight;
        const lbs = w? (w.unit==='POUNDS'?w.value : w.unit==='OUNCES'?w.value/16 : w.unit==='GRAMS'?w.value/453.592 : w.unit==='KILOGRAMS'?w.value*2.20462 : 0) : 0;
        wt += lbs*li.quantity; tot++;
        if(!w || !w.value) unweighted++;
      }
      rows.push({name:o.name, sub:Number(o.subtotalPriceSet?.shopMoney?.amount||0),
        ship:Number(o.totalShippingPriceSet?.shopMoney?.amount||0),
        rate:o.shippingLine?.title||'(none)', wt:+wt.toFixed(3), unweighted, lines:tot});
    } c=d.orders.pageInfo.hasNextPage?d.orders.pageInfo.endCursor:null; }while(c && pages<14);

  const free=rows.filter(r=>r.ship===0), paid=rows.filter(r=>r.ship>0);
  const stat=(a,f)=>a.length? (a.reduce((s,x)=>s+f(x),0)/a.length) : null;
  const med=a=>{a=[...a].sort((x,y)=>x-y);return a.length?a[Math.floor(a.length/2)]:null;};
  console.log(`PRE-WINDOW (Jul 1 - Aug 17) non-sample US orders: n=${rows.length}  free=${free.length}  paid=${paid.length}\n`);
  console.log('### THE TEST: does shipping FREE track a cart weight of <= 0.5 lb? ###');
  for(const [lab,a] of [['FREE',free],['PAID',paid]]){
    if(!a.length){console.log(`  ${lab}: none`);continue;}
    const under = a.filter(r=>r.wt<=0.5).length;
    console.log(`  ${lab.padEnd(5)} n=${String(a.length).padStart(3)}  median cart weight=${med(a.map(r=>r.wt))} lb  median subtotal=$${med(a.map(r=>r.sub))}`);
    console.log(`         carts weighing <= 0.5 lb: ${under}/${a.length} (${(100*under/a.length).toFixed(0)}%)`);
    console.log(`         avg unweighted line items per order: ${stat(a,r=>r.unweighted/r.lines*100).toFixed(0)}% of lines have NO weight set`);
  }
  console.log('\n### highest-value orders that shipped FREE (the ones that look like a leak) ###');
  for(const r of free.filter(x=>x.sub>=300).sort((a,b)=>b.sub-a.sub).slice(0,10))
    console.log(`  ${r.name}  $${r.sub.toFixed(2)}  weight=${r.wt} lb  unweighted lines=${r.unweighted}/${r.lines}  "${r.rate}"`);
})();