`cadence/york-cadence.mjs`

125 lines
#!/usr/bin/env node
/**
 * york-cadence.mjs — daily York line maintenance (READ-ONLY on Shopify; propose-not-auto).
 * Diffs the loaded York master (dw_unified.york_master_aug2026) against LIVE Shopify (vendor
 * "Jeffrey Stevens", keyed by dwc/custom.manufacturer_sku metafield) and surfaces 3 action buckets:
 *   A. reprice-drift  — live ACTIVE + master Active/New + roll price != MAP (title-verified)
 *   B. onboard-ready  — master 'New', not live, has a local image, settlement-keyword-clear
 *   C. disco          — live ACTIVE + master Dead/Phasing
 * Writes data/latest.json; on any actionable count, writes a pending-approval memo + CNCP card.
 * NEVER writes to Shopify. Gated actions stay Steve's (existing york-reprice/create/disco tooling).
 */
import fs from 'node:fs'; import { execSync } from 'node:child_process';
import { autoOnboard } from './york-auto-onboard.mjs';  // the ONE Shopify write: New→DRAFT, cap 100/run
const ROOT=process.env.HOME+'/Projects/york-reprice-2026-08';
const NO_WRITE=process.env.YORK_CADENCE_NO_WRITE==='1'; // read-only test mode: no latest.json/memo/CNCP/auto-onboard writes
const SHOP='designer-laboratory-sandbox.myshopify.com',VER='2024-10';
const TOKEN=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1]?.trim();
const URL=`https://${SHOP}/admin/api/${VER}/graphql.json`;
const PG='PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F\'\x1f\' -c';
// multi-line SQL breaks `psql -c "<sql>"` ("invalid command \n") — the onboardCand query is
// multi-line and was silently throwing into a catch{}, falsely returning onboardReady=0 for
// MONTHS. Route every query through a temp .sql file + `-f` so multi-line SQL is safe.
// (All cadence queries concat_ws(chr(31),…) into ONE column, so -F is unnecessary.)
const q=sql=>{fs.writeFileSync('/tmp/_yc_q.sql',sql);return execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -f /tmp/_yc_q.sql`,{encoding:'utf8'}).trim();};
const sleep=ms=>new Promise(r=>setTimeout(r,ms));
const gql=async(query,v)=>{for(let a=0;a<8;a++){let j;try{const r=await fetch(URL,{method:'POST',headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'},body:JSON.stringify({query,variables:v})});j=await r.json();}catch(e){await sleep(1500*(a+1));continue;}if(j.errors){if(JSON.stringify(j.errors).includes('THROTTLED')){await sleep(2000*(a+1));continue;}throw new Error(JSON.stringify(j.errors));}const t=j.extensions?.cost?.throttleStatus;if(t&&t.currentlyAvailable<300)await sleep(1500);return j.data;}throw new Error('retries');};
const norm=s=>String(s||'').toUpperCase().trim();
const words=s=>new Set(String(s||'').toLowerCase().replace(/[^a-z0-9 ]/g,' ').split(/\s+/).filter(w=>w.length>2));
const share=(a,b)=>{const A=words(a),B=words(b);for(const w of A)if(B.has(w))return true;return false;};
const cents=x=>Math.round(Number(x)*100);
const priceOff=(a,b)=>Math.abs(cents(a)-cents(b))>1; // "off" = more than a 1-cent rounding gap

// 1. master: mfr_sku -> {price,status,pattern,color}
// york_master_aug2026 has MULTIPLE rows per export_sku (two roll sizes → conflicting us_map, e.g.
// NW3500 carries both $102 and $123.25; 39 SKUs fleet-wide). First-wins on price false-flagged a
// correctly-priced live roll as "drift" whenever it matched the OTHER row's MAP. Collect ALL valid
// MAP values per SKU into a set; a live price matching ANY of them is not drift (mirrors the
// single-roll RPS carve-out below). status/pattern/color stay first-wins.
const M=new Map();
for(const line of q(`select concat_ws(chr(31), upper(trim(export_sku)), coalesce(us_map,round(us_msrp*0.75,2)), sku_status, pattern_name, colorway) from york_master_aug2026 where export_sku is not null`).split('\n').filter(Boolean)){
  const [s,p,st,pat,col]=line.split(String.fromCharCode(31)); const price=parseFloat(p);
  if(!M.has(s)) M.set(s,{price,prices:new Set([price]),status:st,pattern:pat,color:col});
  else M.get(s).prices.add(price);
}
// naturals sell at a SINGLE-ROLL price (brewster_york_master.roll_price_single), NOT MAP — a second
// valid price target. Without this the MAP-only drift check false-flags every correctly-priced
// natural (grasscloth/sisal/paperweave/cork) as "drift" and would propose re-breaking it daily.
const RPS=new Map();
try{ for(const line of q(`select concat_ws(chr(31), upper(trim(mfr_sku)), roll_price_single) from brewster_york_master where roll_price_single is not null and roll_price_single>0`).split('\n').filter(Boolean)){
  const [s,p]=line.split(String.fromCharCode(31)); if(!RPS.has(s)) RPS.set(s,parseFloat(p)); } }catch(e){}
// onboard-ready CANDIDATES from DB (master 'New', settlement-keyword-clear, has a local image in
// york_catalog OR brewster_catalog). york_catalog holds art for the archived York-branded line;
// brewster_catalog holds it for the live Brewster/A-Street->Malibu line — every 'New' SKU is the
// latter, so BOTH image drawers must be checked (checking only york_catalog is why this read 0).
// 'Already live' is subtracted AFTER the live pagination below (liveMfr) so we never re-propose live SKUs.
let onboardCand=[]; try{ onboardCand=q(`
 select concat_ws(chr(31), n.s, n.pattern_name) from (select distinct on (upper(trim(export_sku))) upper(trim(export_sku)) s, pattern_name,product_name,book_name from york_master_aug2026 where sku_status='New' and export_sku is not null) n
 left join york_catalog c on upper(trim(c.mfr_sku))=n.s
 left join brewster_catalog b on upper(trim(b.mfr_sku))=n.s
 where coalesce(nullif(c.image_url,''), nullif(b.image_url,'')) is not null
   and lower(concat_ws(' ',n.pattern_name,n.product_name,n.book_name)) !~ 'banana|grape|bird|butterfly|palm|frond|leaf|leaves|foliage|botanical|floral|flower|tropical|jungle|vine|fern|bloom|garden|blossom|deciduous|egret|flamingo|crane|heron|peacock|feather'`)
   .split('\n').map(x=>x.trim()).filter(Boolean)
   .map(l=>{const [s,pat]=l.split(String.fromCharCode(31)); return {s,pat:pat||''};}); }catch(e){}

// 2. live Jeffrey Stevens
let cur=null, reprice=[], disco=[], liveMfr=new Set(), liveTitles=new Map();
// PAGE CAP: the JS catalog is ~6,193 products; the old 90×60=5,400 cap silently TRUNCATED the scan
// (~793 late-page products unseen), so their live SKUs weren't recognized → inflated onboard-ready
// AND could mask real reprice/disco in the tail. 120×100=12,000 headroom; hasNextPage breaks early.
for(let pg=0;pg<120;pg++){
  const d=(await gql(`query($c:String){products(first:100,after:$c,query:"vendor:'Jeffrey Stevens'"){pageInfo{hasNextPage endCursor} nodes{status title dwc:metafield(namespace:"dwc",key:"manufacturer_sku"){value} cust:metafield(namespace:"custom",key:"manufacturer_sku"){value} variants(first:20){nodes{id sku price title}}}}}`,{c:cur}))?.products; if(!d)break;
  for(const p of d.nodes){const mfr=norm(p.dwc?.value||p.cust?.value); if(!mfr)continue; liveMfr.add(mfr);
    { const a=liveTitles.get(mfr)||[]; a.push(p.title||''); liveTitles.set(mfr,a); } // for collision-aware onboard 2-factor
    const m=M.get(mfr); if(!m)continue;
    const rolls=p.variants.nodes.filter(v=>!/sample|memo|-sample$/i.test((v.sku||'')+(v.title||''))); if(!rolls.length)continue;
    const roll=rolls.reduce((a,b)=>parseFloat(b.price)>parseFloat(a.price)?b:a); const price=parseFloat(roll.price);
    if(p.status==='ACTIVE'){
      if(['Dead','Phasing'].includes(m.status)) disco.push({mfr,title:p.title,status:m.status});
      else if(['Active','New'].includes(m.status) && m.price>0 && share(p.title,m.pattern)){
        const rps=RPS.get(mfr);                                  // single-roll target (naturals)
        const offMap=![...m.prices].some(vp=>!priceOff(price,vp)); // off from EVERY valid master MAP row (dup rows = 2 roll sizes)
        const offRps=rps?priceOff(price,rps):true;               // off from single-roll (or none)
        if(offMap && offRps) reprice.push({mfr,title:p.title,cur:price,map:m.price,rps:rps||null});
      }
    }
  }
  if(!d.pageInfo.hasNextPage)break; cur=d.pageInfo.endCursor;
}
// collision-aware onboard-ready: a candidate is truly un-onboarded only if NO live product carrying its
// code shares a title word with its master pattern. Bare code-match masks cross-brand collisions
// (a New York code coinciding with an unrelated live Anna French/Thibaut SKU) as false 'already live'.
// exclude items DELIBERATELY HELD at build time (settlement flora/fauna via richer marketing-field
// analysis, dead-CDN images, color-extraction failures). The cadence's keyword filter is weaker than
// the real settlement/image gate, so without this it re-proposes held items daily (66 of 87 on 2026-07-28).
const HELD=new Set();
for(const f of ['onboard-settlement-held','onboard-image-held','onboard-color-fail-list']){
  try{ for(const r of JSON.parse(fs.readFileSync(ROOT+'/data/'+f+'.json','utf8'))) HELD.add(norm(r.mfr_sku)); }catch(e){}
}
const onboardObjs=onboardCand.filter(c=>{ if(HELD.has(norm(c.s))) return false; const titles=liveTitles.get(c.s)||[]; return !titles.some(t=>share(t,c.pat)); });
const onboardMfrs=onboardObjs.map(c=>c.s);
const onboardReady=onboardMfrs.length;
const out={ts:new Date().toISOString(),live_js:liveMfr.size,
  reprice_drift:reprice.length, onboard_ready:onboardReady, disco:disco.length,
  reprice_sample:reprice.slice(0,10), onboard_sample:onboardMfrs.slice(0,12), disco_sample:disco.slice(0,10)};
const actionable=reprice.length+onboardReady+disco.length;
console.log(`york-cadence ${out.ts} | live JS ${liveMfr.size} | reprice-drift ${reprice.length} | onboard-ready ${onboardReady} | disco ${disco.length}${NO_WRITE?' | NO_WRITE (read-only test)':''}`);
if(NO_WRITE){ console.log('  reprice_sample:',JSON.stringify(out.reprice_sample)); console.log('  onboard_sample:',JSON.stringify(out.onboard_sample.slice(0,6))); process.exit(0); }
fs.mkdirSync(ROOT+'/cadence/data',{recursive:true});
fs.writeFileSync(ROOT+'/cadence/data/latest.json',JSON.stringify(out,null,2));
if(actionable>0){
  const memo=`# York cadence — ${actionable} actionable (${out.ts.slice(0,10)})\n\n- **Reprice drift**: ${reprice.length} live ACTIVE below/above MAP → run york-pl-reprice / bridge-reprice (gated).\n- **Onboard-ready**: ${onboardReady} master 'New' with image + settlement-clear not yet live → run york-new-create (gated).\n- **Disco**: ${disco.length} live ACTIVE now Dead/Phasing → archive (gated).\n\nData: cadence/data/latest.json. All customer-facing writes stay gated — this is a surface, not an auto-apply.\n\n- [ ] APPROVE which action to run\n`;
  fs.writeFileSync(process.env.HOME+'/.claude/yolo-queue/pending-approval/york-cadence-actions.md',memo);
  try{execSync(`curl -s -X POST http://127.0.0.1:3333/api/parking-lot -H 'Content-Type: application/json' -d ${JSON.stringify(JSON.stringify({project:'designerwallcoverings',title:`York cadence: ${actionable} actionable`,note:`reprice ${reprice.length} / onboard ${onboardReady} / disco ${disco.length}`}))}`,{stdio:'ignore'});}catch(e){}
}

// ---- AUTO-ONBOARD — the ONLY Shopify write this cadence performs (Steve-authorized 2026-07-13) ----
// Drips the pre-vetted onboard queue (New York items) into Shopify as DRAFT, cap 100/run, idempotent.
// Reprice + disco above stay strictly PROPOSE-ONLY (memo only). Publish DRAFT→ACTIVE stays gated.
const ob=autoOnboard();
console.log(`york-auto-onboard | ${ob.ran?'RAN':'skipped'} | ${ob.note}`);
if(ob.created>0){
  try{execSync(`curl -s -X POST http://127.0.0.1:3333/api/wins -H 'Content-Type: application/json' -d ${JSON.stringify(JSON.stringify({project:'york-reprice-2026-08',title:`York cadence auto-onboarded ${ob.created} DRAFT`,summary:`${ob.note}; queue ${ob.queue_size}. DRAFT-only, cap 100/run, idempotent.`}))}`,{stdio:'ignore'});}catch(e){}
  try{fs.appendFileSync(process.env.HOME+'/.claude/yolo-queue/pending-approval/york-cadence-actions.md',`\n\n### Auto-onboard ${out.ts.slice(0,10)}\n- Created **${ob.created}** New York items as DRAFT (cap 100/run). ${ob.note}. Queue size ${ob.queue_size}.\n- DRAFT only — publish to ACTIVE remains a separate gated step.\n`);}catch(e){}
}