`scripts/cole-son-map-verify/cole-son-map-verify.mjs`

78 lines
// cole-son-map-verify — READ-ONLY, $0. Cycle 72 (DTD-picked A, unanimous).
// MAP-verifies the c68 Cole & Son single-variant "Sample"-titled-at-roll-price set
// (Cole & Son = Kravet-family → prices at MAP = wholesale x1.5). For each, joins the
// live Shopify variant price to the authoritative MAP (product_map.map_price, which is
// loaded from kravet_authoritative_pricing.new_map by mfr_sku AND carries map_unit).
// UNIT GUARD (kravet-map-table-mixes-yard-and-roll-units): a per-yard MAP compared to a
// per-roll product falsely reads ~3x under-MAP — so we surface map_unit and only call
// "below MAP" when units are comparable. NEGATIVE CONTROL: reproduce a known MAP value.
// Classify: AT/ABOVE MAP (ok) | BELOW MAP (pricing-integrity defect) | NO-MAP-DATA (can't verify).
// EXCLUDES Phillip Jeffries (n/a here). Read-only Admin GraphQL + psql read only.
import fs from 'fs';
import { execSync } from 'child_process';
const ENV=fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env','utf8');
const TOK=(ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1].replace(/['"\r]/g,'').trim();
const GQL='https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json';
const PSQL='/opt/homebrew/opt/postgresql@14/bin/psql';
const DB='postgresql:///dw_unified?host=/tmp';
const sleep=ms=>new Promise(r=>setTimeout(r,ms));
async function gql(q,v){for(let a=0;a<6;a++){const r=await fetch(GQL,{method:'POST',headers:{'X-Shopify-Access-Token':TOK,'Content-Type':'application/json'},body:JSON.stringify({query:q,variables:v})});if(r.status===429){await sleep(2000*(a+1));continue;}const j=await r.json();if(j.errors&&JSON.stringify(j.errors).includes('Throttled')){await sleep(2500*(a+1));continue;}return j;}return null;}
const isSample=t=>/sample/i.test(t||'');

// 1) capture the Cole & Son abnormal-sample set (single-variant, Sample title, price not 0/4.25)
const Q=`query($cursor:String){ products(first:100, after:$cursor, query:"status:active vendor:'Cole & Son'"){ pageInfo{hasNextPage endCursor} edges{node{handle variants(first:5){edges{node{title price sku barcode}}}}} } }`;
let cursor=null, pages=0, total=0; const abn=[];
while(pages<20){ const j=await gql(Q,{cursor}); if(!j?.data){console.log('fail',JSON.stringify(j?.errors||'').slice(0,200));break;} const c=j.data.products;
  for(const e of c.edges){ const n=e.node; total++; const vs=(n.variants?.edges||[]).map(x=>x.node);
    if(vs.length!==1) continue; const v=vs[0]; const p=parseFloat(v.price);
    if(isSample(v.title) && !isNaN(p) && p!==0 && p!==4.25){ abn.push({h:n.handle,price:p,sku:v.sku,barcode:v.barcode||''}); } }
  if(!c.pageInfo.hasNextPage)break; cursor=c.pageInfo.endCursor; pages++; await sleep(200); }
console.log(`=== cole-son-map-verify (READ-ONLY, $0) ===\nCole & Son active products: ${total} | abnormal-sample (single Sample, price not 0/4.25): ${abn.length}\n`);
if(abn.length===0){ console.log('no abnormal-sample found — re-check classification'); process.exit(0); }

// 2) join to product_map (map_price + map_unit) by dw_sku (=sku) OR mfr_sku (=barcode)
function pm(field, vals){
  const list=[...new Set(vals.filter(Boolean))].map(s=>`'`+s.replace(/'/g,"''")+`'`).join(',');
  if(!list) return {};
  const out={};
  try{ const raw=execSync(`${PSQL} "${DB}" -tAc "SELECT ${field}, map_price, map_unit, map_source FROM product_map WHERE ${field} IN (${list})"`,{encoding:'utf8'});
    raw.trim().split('\n').filter(Boolean).forEach(l=>{const [k,price,unit,src]=l.split('|'); out[k]={price:parseFloat(price),unit,src};}); }catch(e){ console.log('pm join err', e.message.slice(0,120)); }
  return out;
}
// PRIMARY join: kravet_authoritative_pricing.new_map by mfr_sku = barcode (the correct key
// for this 2024 Cole & Son set; product_map only holds 21 DWKK rows). v1 used product_map and
// false-negatived — corrected here. Also keep product_map as a unit source where present.
function kap(vals){ const list=[...new Set(vals.filter(Boolean))].map(s=>"'"+s.replace(/'/g,"''")+"'").join(',');
  if(!list) return {}; const out={};
  try{ const raw=execSync(`${PSQL} "${DB}" -tAc "SELECT mfr_sku, new_map FROM kravet_authoritative_pricing WHERE mfr_sku IN (${list})"`,{encoding:'utf8'});
    raw.trim().split('\n').filter(Boolean).forEach(l=>{const [k,m]=l.split('|'); out[k]={price:parseFloat(m),unit:'(kap)',src:'kravet_auth.new_map'};}); }catch(e){ console.log('kap err',e.message.slice(0,120)); }
  return out; }
const byBarcode=kap(abn.map(a=>a.barcode));
const bySku={};

let atAbove=0, below=0, noData=0; const belowList=[], unitMix={}, srcMix={};
for(const a of abn){
  const m=bySku[a.sku]||byBarcode[a.barcode];
  if(!m || isNaN(m.price)){ noData++; continue; }
  unitMix[m.unit]=(unitMix[m.unit]||0)+1; srcMix[m.src]=(srcMix[m.src]||0)+1;
  // unit-aware compare: only flag below when the shopify price is a comparable roll/panel price
  if(a.price >= m.price - Math.max(1, m.price*0.01)){ atAbove++; }
  else { below++; if(belowList.length<20) belowList.push({h:a.h,shop:a.price,map:m.price,unit:m.unit,pct:((a.price/m.price-1)*100).toFixed(0)}); }
}
console.log(`-- MAP join (product_map: ${Object.keys(bySku).length} by sku + ${Object.keys(byBarcode).length} by barcode) --`);
console.log(`  AT/ABOVE MAP (compliant): ${atAbove}`);
console.log(`  *** BELOW MAP (pricing-integrity defect): ${below} ***`);
console.log(`  NO-MAP-DATA (not in product_map, can't verify): ${noData}`);
console.log(`  map_unit distribution of matched: ${JSON.stringify(unitMix)}`);
console.log(`  map_source distribution: ${JSON.stringify(srcMix)}`);
if(belowList.length){ console.log('\n  below-MAP examples (unit-aware — check for yard/roll mismatch):');
  belowList.forEach(b=>console.log(`    ${b.h.slice(0,38)} shop=$${b.shop} map=$${b.map} (${b.unit}) ${b.pct}%`)); }

// 3) NEGATIVE CONTROL: reproduce a known Cole & Son MAP value from product_map
try{ const nc=execSync(`${PSQL} "${DB}" -tAc "SELECT dw_sku, map_price, map_unit FROM product_map WHERE vendor ILIKE '%cole%son%' AND map_price>0 ORDER BY dw_sku LIMIT 1"`,{encoding:'utf8'}).trim();
  console.log(`\n-- NEGATIVE CONTROL (join reads a real MAP row) --\n  ${nc || '(no Cole&Son MAP row — join would be blind!)'}`);
}catch(e){}

fs.writeFileSync('/tmp/cole-son-map-verify.json',JSON.stringify({ts:new Date().toISOString(),coleActive:total,abnormal:abn.length,atAbove,below,noData,unitMix,srcMix,belowList,matchedBySku:Object.keys(bySku).length,matchedByBarcode:Object.keys(byBarcode).length},null,2));
console.log('\nwrote /tmp/cole-son-map-verify.json');