`reprice-tk11434.mjs`

160 lines
// TK-11434 — Carnegie at-cost reprice fixer.  GATED: --apply is a customer-facing LIVE
// Shopify money-write. DRY-RUN by default (touches nothing).
//
// Bug: the live Carnegie v2 product set was CREATED at vendor cost on 2026-08-18 (rollout2.mjs
// create path: price:r.price||'0.00', no markup) and PUBLISHED live by the Sep-10 cutover. The
// TK-10686 reprice.mjs only reprices LIVE variants, so it never reached this set (draft/archived
// at the time); the cutover then archived the previously-repriced v1 set. Result: 5,914 ACTIVE
// non-sample variants selling at cost (ratio ~1.0) vs the 1.5x floor.
//
// This fixer mirrors the Steve-approved TK-11403 pattern:
//   * plan = carnegie_reprice_staging_tk11434 (needs_reprice rows), staged + join-verified 2026-09-10.
//   * LIVE RE-READ + COMPARE-AND-SET before every write: only writes proposed_retail IF the current
//     live price still equals the staged old_price (i.e. still at cost). If the live price has moved
//     off cost since staging, SKIP — never clobber a value we didn't stage. If already == proposed, NOOP.
//   * proposed_retail = round(cost/0.65/0.85, 2)  (TK-10686 precedent, ~1.81x).
//   * LEDGER: every intent/applied/skip/noop/failed row appended with the exact pre-price (reversible).
//   * RESUME-SAFE: variants already 'applied' in the ledger are skipped (survives interruption).
//   * BATCHED: --limit=N caps variants per run (e.g. ≤1000/day if the Shopify daily variant limit bites).
//   * SAMPLE ($4.25) variants are never in the plan and never touched.
//   * UNDO: node undo-tk11434.mjs        (restores old_price via compare-and-set; see that file)
//
// Usage:
//   node reprice-tk11434.mjs                 # DRY-RUN — prints the plan, writes nothing
//   node reprice-tk11434.mjs --apply         # GATED live write (Steve-approved only)
//   node reprice-tk11434.mjs --apply --limit=1000   # first daily batch
//   node reprice-tk11434.mjs verify          # read-only: re-sweep live, report ratio<1.5x remaining
import fs from 'node:fs';
import { execFileSync } from 'node:child_process';

const HOME = process.env.HOME;
const ENV = `${HOME}/Projects/secrets-manager/.env`;
const env = k => { const m = fs.readFileSync(ENV,'utf8').split('\n').find(l=>l.startsWith(k+'=')); return m ? m.slice(k.length+1).trim().replace(/^["']|["']$/g,'') : ''; };
const TOKEN = env('SHOPIFY_ADMIN_TOKEN');
const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';   // the LIVE DW store (legacy "sandbox" name)
const API = `https://${DOMAIN}/admin/api/2024-10/graphql.json`;
const sleep = ms => new Promise(r=>setTimeout(r,ms));

const APPLY = process.argv.includes('--apply');
const VERIFY = process.argv[2] === 'verify';
const LIMIT = Number((process.argv.find(a=>a.startsWith('--limit='))||'=0').split('=')[1]) || 0;

const DIR = new URL('.', import.meta.url).pathname;
const LED = `${DIR}reprice-tk11434-ledger.jsonl`;
const log = o => fs.appendFileSync(LED, JSON.stringify({ ts:new Date().toISOString(), tk:'TK-11434', ...o }) + '\n');

async function gql(query, variables={}) {
  for (let a=0;a<6;a++){
    const r = await fetch(API,{ method:'POST', headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'},
      body: JSON.stringify({query,variables}), signal: AbortSignal.timeout(45000) });
    if (r.status===429 || r.status>=500){ await sleep(1500*(a+1)); continue; }
    const j = await r.json();
    if (j.errors && JSON.stringify(j.errors).includes('THROTTLED')){ await sleep(3000*(a+1)); continue; }
    return j;
  }
  throw new Error('gql retries exhausted');
}
function psql(sql){
  const out = execFileSync('psql', ['postgresql:///dw_unified?host=/tmp','-At','-F','|','-c',sql], { encoding:'utf8', maxBuffer:256*1024*1024 });
  return out.trim() ? out.trim().split('\n').map(r=>r.split('|')) : [];
}

// ---- plan from the staged, join-verified table ----
const rows = psql(
  `SELECT product_id, variant_id, dw_sku, handle, current_live_price, cost, proposed_retail
     FROM carnegie_reprice_staging_tk11434
    WHERE needs_reprice
    ORDER BY product_id, variant_id`
).map(([product_id,variant_id,dw_sku,handle,old,cost,prop]) => ({
  product_id, variant_id, dw_sku, handle, old:+old, cost:+cost, prop:+prop
}));
console.log(`[plan] ${rows.length} needs_reprice variants staged (formula round(cost/0.65/0.85,2), ~1.81x)`);

// ---- VERIFY mode: read-only re-sweep of the whole plan against live ----
if (VERIFY) {
  let atCost=0, fixed=0, other=0, missing=0, checked=0;
  const ids = rows.map(r=>`gid://shopify/ProductVariant/${r.variant_id}`);
  for (let i=0;i<ids.length;i+=100){
    const j = await gql(`query($ids:[ID!]!){ nodes(ids:$ids){ ... on ProductVariant { id price } } }`, { ids: ids.slice(i,i+100) });
    const map = Object.fromEntries((j.data?.nodes||[]).filter(Boolean).map(n=>[n.id.split('/').pop(), +n.price]));
    for (const r of rows.slice(i,i+100)){
      checked++; const live = map[r.variant_id];
      if (live===undefined){ missing++; continue; }
      if (r.cost>0 && live/r.cost < 1.5) atCost++;
      else if (Math.abs(live - r.prop) < 0.01) fixed++;
      else other++;
    }
    await sleep(300);
  }
  console.log(`[verify] checked=${checked} still<1.5x(at-cost)=${atCost} at-proposed(fixed)=${fixed} other=${other} missing=${missing}`);
  process.exit(atCost>0 ? 1 : 0);
}

// ---- resume: skip vids already applied ----
const done = new Set();
if (fs.existsSync(LED)) for (const l of fs.readFileSync(LED,'utf8').split('\n')){
  if(!l.trim())continue; try{ const r=JSON.parse(l); if(r.phase==='applied')done.add(String(r.vid)); if(r.phase==='undone')done.delete(String(r.vid)); }catch{}
}

// ---- group plan by product for bulk update ----
const byProduct = new Map();
for (const r of rows){ if(!byProduct.has(r.product_id)) byProduct.set(r.product_id,[]); byProduct.get(r.product_id).push(r); }

const M = `mutation($pid:ID!,$vars:[ProductVariantsBulkInput!]!){
  productVariantsBulkUpdate(productId:$pid, variants:$vars){
    productVariants{ id sku price } userErrors{ field message } } }`;

let applied=0, noop=0, skip=0, failed=0, variantsSeen=0;
for (const [pid, plan] of byProduct){
  if (LIMIT && variantsSeen >= LIMIT) break;
  const gpid = `gid://shopify/Product/${pid}`;
  // LIVE RE-READ every variant on this product
  const j = await gql(`query($id:ID!){ product(id:$id){ id status variants(first:100){ nodes{ id sku price } } } }`, { id: gpid });
  const p = j.data?.product;
  if (!p){ for(const r of plan){ log({phase:'skip',pid,vid:r.variant_id,sku:r.dw_sku,why:'product not found'}); skip++; } continue; }
  if (p.status !== 'ACTIVE'){ for(const r of plan){ log({phase:'skip',pid,vid:r.variant_id,sku:r.dw_sku,why:'status '+p.status}); skip++; } continue; }
  const liveById = Object.fromEntries(p.variants.nodes.map(n=>[n.id.split('/').pop(), n]));

  const vars = [];
  for (const r of plan){
    if (LIMIT && variantsSeen >= LIMIT) break;
    variantsSeen++;
    const vid = String(r.variant_id);
    if (done.has(vid)){ skip++; continue; }
    const lv = liveById[vid];
    if (!lv){ log({phase:'skip',pid,vid,sku:r.dw_sku,why:'variant not on product'}); skip++; continue; }
    const cur = +lv.price;
    // COMPARE-AND-SET safety: only reprice if still sitting at the staged old (cost) price.
    if (Math.abs(cur - r.prop) < 0.005){ log({phase:'noop',pid,vid,sku:r.dw_sku,price:cur,why:'already at proposed'}); noop++; continue; }
    if (Math.abs(cur - r.old) >= 0.005){ log({phase:'skip',pid,vid,sku:r.dw_sku,liveNow:cur,stagedOld:r.old,why:'live moved off staged baseline — not clobbering'}); skip++; continue; }
    vars.push({ id: lv.id, price: String(r.prop.toFixed(2)), _r:r, _pre:cur });
  }
  if (!vars.length) continue;

  for (const v of vars) log({ phase:'intent', pid, vid:v._r.variant_id, sku:v._r.dw_sku, prePrice:v._pre, toPrice:v.price, cost:v._r.cost, undo:'node undo-tk11434.mjs '+v._r.variant_id });

  if (!APPLY){
    for (const v of vars) console.log(`DRY  ${v._r.dw_sku}  ${v._pre} -> ${v.price}  (cost ${v._r.cost})`);
    applied += vars.length;   // count as "would-apply" in dry mode
    continue;
  }

  const r = await gql(M, { pid: gpid, vars: vars.map(v=>({ id:v.id, price:v.price })) });
  const ue = r.data?.productVariantsBulkUpdate?.userErrors || [];
  if (ue.length || r.errors){
    for (const v of vars){ log({phase:'failed',pid,vid:v._r.variant_id,sku:v._r.dw_sku,err:ue.length?ue:r.errors}); failed++; }
    console.log(`FAIL product ${pid}: ${JSON.stringify(ue.length?ue:r.errors).slice(0,200)}`);
    await sleep(600); continue;
  }
  const got = Object.fromEntries((r.data.productVariantsBulkUpdate.productVariants||[]).map(n=>[n.id.split('/').pop(), n.price]));
  for (const v of vars){
    const np = got[v._r.variant_id];
    log({ phase:'applied', pid, vid:v._r.variant_id, sku:v._r.dw_sku, prePrice:v._pre, newPrice:np });
    console.log(`OK   ${v._r.dw_sku}  ${v._pre} -> ${np}`);
    applied++;
  }
  await sleep(500);
}
console.log(`\n== ${APPLY?'APPLIED':'DRY-RUN would-apply'}=${applied} noop=${noop} skip=${skip} failed=${failed} (variants scanned ${variantsSeen}${LIMIT?`/limit ${LIMIT}`:''}) ==`);
if (!APPLY) console.log('DRY-RUN — nothing written to Shopify. Add --apply (Steve-approved) to write.');