`reprice.mjs`

130 lines
// carnegie-reprice — reprice the Carnegie contract-fabric line from vendor-cost to DW retail.
// BUG (2026-08-18, TK-10671): the Carnegie scraper posted carnegiefabrics.com's own published
// per-yard price verbatim as the DW SELL price, so the whole line went live AT COST. Steve
// confirms that scraped price IS our net cost. Fix: retail = round(cost/0.65/0.85), whole dollar.
//
// SOURCE OF TRUTH: the LIVE Shopify store (Admin REST). cost = carnegie_catalog.price by sku.
// SAFE: dry-run by default (writes plan + before-snapshot, touches nothing). --apply does the
// gated live write. --rollback <snapshot.jsonl> restores. Sample ($4.25) variants NEVER touched.
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const DIR = new URL('.', import.meta.url).pathname;
const ENV = `${process.env.HOME}/Projects/secrets-manager/.env`;
function 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');
let SHOP = env('SHOPIFY_STORE_DOMAIN') || env('SHOPIFY_STORE');
if (SHOP && !SHOP.includes('.')) SHOP += '.myshopify.com';
const API = `https://${SHOP}/admin/api/2024-10`;
const H = { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' };
const MODE = process.argv[2] || 'dry';                 // dry | apply | rollback
const ROLLBACK_FILE = process.argv[3];
const sleep = ms => new Promise(r => setTimeout(r, ms));

const PSQL = ['/opt/homebrew/opt/postgresql@14/bin/psql', '/usr/local/opt/postgresql@14/bin/psql', 'psql']
  .find(p => { try { execFileSync(p, ['--version'], { stdio: 'ignore' }); return true; } catch { return false; } }) || 'psql';
function q(sql) {
  const out = execFileSync(PSQL, ['postgresql:///dw_unified?host=/tmp', '-At', '-F', '|', '-c', sql], { encoding: 'utf8', maxBuffer: 128 * 1024 * 1024 });
  return out.trim() ? out.trim().split('\n').map(r => r.split('|')) : [];
}
const round = n => Math.round(n);
const retailFromCost = c => round(c / 0.65 / 0.85);

async function shop(path, opts = {}, tries = 5) {
  for (let i = 0; i < tries; i++) {
    const res = await fetch(`${API}${path}`, { headers: H, ...opts });
    if (res.status === 429) { await sleep(2000 * (i + 1)); continue; }
    if (!res.ok) throw new Error(`HTTP ${res.status} ${path} :: ${(await res.text()).slice(0, 200)}`);
    return res;
  }
  throw new Error(`exhausted retries ${path}`);
}
async function allCarnegie() {              // paginate live Carnegie products via cursor Link header
  const out = []; let url = `/products.json?vendor=Carnegie&limit=250`;
  while (url) {
    const res = await shop(url);
    const j = await res.json(); out.push(...(j.products || []));
    const link = res.headers.get('link') || '';
    const m = link.match(/<[^>]*[?&]page_info=([^>&]+)[^>]*>;\s*rel="next"/);
    url = m ? `/products.json?limit=250&page_info=${m[1]}` : null;
    await sleep(600);
  }
  return out;
}

// ---- ROLLBACK ----
if (MODE === 'rollback') {
  if (!ROLLBACK_FILE || !fs.existsSync(ROLLBACK_FILE)) throw new Error('rollback needs a snapshot.jsonl path');
  const snap = fs.readFileSync(ROLLBACK_FILE, 'utf8').trim().split('\n').map(JSON.parse);
  console.log(`[rollback] restoring ${snap.length} variants…`);
  let n = 0;
  for (const s of snap) {
    await shop(`/variants/${s.variant_id}.json`, { method: 'PUT', body: JSON.stringify({ variant: { id: s.variant_id, price: s.old_price } }) });
    if (++n % 50 === 0) console.log(`  ${n}/${snap.length}`);
    await sleep(550);
  }
  console.log(`[rollback] done (${n})`);
  process.exit(0);
}

// ---- BUILD PLAN ----
const cost = new Map();
for (const [sku, p] of q(`select dw_sku, price from carnegie_catalog where price is not null`)) cost.set(sku, +p);
console.log(`[plan] cost basis: ${cost.size} Carnegie catalog skus`);

const products = await allCarnegie();
console.log(`[plan] live Carnegie products: ${products.length}`);

const plan = [], snapshot = [], noCost = [], sampleSkipped = [], unchanged = [];
for (const p of products) {
  for (const v of (p.variants || [])) {
    const sku = v.sku || '', price = +v.price;
    const isSample = /sample/i.test(v.title || '') || /sample/i.test(sku) || price <= 5;
    if (isSample) { sampleSkipped.push(sku); continue; }
    const c = cost.get(sku);
    if (!(c > 0)) { noCost.push({ handle: p.handle, sku, price }); continue; }
    const nu = retailFromCost(c);
    if (nu === price) { unchanged.push(sku); continue; }
    plan.push({ variant_id: v.id, product_id: p.id, handle: p.handle, title: `${p.title} / ${v.title}`, sku, old_price: price.toFixed(2), cost: c, new_price: nu.toFixed(2) });
    snapshot.push({ variant_id: v.id, sku, old_price: price.toFixed(2) });
  }
}

const stamp = q(`select to_char(now(),'YYYYMMDD-HH24MISS')`)[0][0];
fs.writeFileSync(`${DIR}plan-${stamp}.jsonl`, plan.map(r => JSON.stringify(r)).join('\n'));
fs.writeFileSync(`${DIR}snapshot-${stamp}.jsonl`, snapshot.map(r => JSON.stringify(r)).join('\n'));
fs.writeFileSync(`${DIR}plan-latest.jsonl`, plan.map(r => JSON.stringify(r)).join('\n'));
fs.writeFileSync(`${DIR}snapshot-latest.jsonl`, snapshot.map(r => JSON.stringify(r)).join('\n'));

const sumOld = plan.reduce((a, r) => a + +r.old_price, 0), sumNew = plan.reduce((a, r) => a + +r.new_price, 0);
console.log(`\n=== CARNEGIE REPRICE PLAN (${MODE.toUpperCase()}) ===`);
console.log(`  variants to reprice : ${plan.length}`);
console.log(`  unchanged (already right): ${unchanged.length}`);
console.log(`  samples skipped     : ${sampleSkipped.length}`);
console.log(`  NO-COST (skipped, review): ${noCost.length}`);
console.log(`  avg old (cost)      : $${(sumOld / plan.length || 0).toFixed(2)}`);
console.log(`  avg new (retail)    : $${(sumNew / plan.length || 0).toFixed(2)}`);
console.log(`  sample rows:`);
for (const r of plan.slice(0, 8)) console.log(`    ${r.sku.padEnd(16)} $${r.old_price} → $${r.new_price}   ${r.title.slice(0, 40)}`);
if (noCost.length) console.log(`  ⚠ ${noCost.length} live variants have NO catalog cost — NOT repriced (e.g. ${noCost.slice(0,3).map(x=>x.sku).join(', ')})`);
console.log(`  files: plan-${stamp}.jsonl + snapshot-${stamp}.jsonl (rollback with:  node reprice.mjs rollback snapshot-${stamp}.jsonl)`);

if (MODE !== 'apply') { console.log(`\n[dry-run] nothing written to Shopify. Re-run with 'apply' to fire the gated live write.`); process.exit(0); }

// ---- APPLY (gated) ----
console.log(`\n[apply] writing ${plan.length} live variant prices…`);
let ok = 0, err = 0; const errors = [];
for (const r of plan) {
  try {
    await shop(`/variants/${r.variant_id}.json`, { method: 'PUT', body: JSON.stringify({ variant: { id: r.variant_id, price: r.new_price } }) });
    ok++;
  } catch (e) { err++; errors.push({ sku: r.sku, err: String(e.message || e).slice(0, 120) }); }
  if ((ok + err) % 50 === 0) console.log(`  ${ok + err}/${plan.length} (ok ${ok}, err ${err})`);
  await sleep(550);
}
fs.writeFileSync(`${DIR}apply-result-${stamp}.json`, JSON.stringify({ stamp, ok, err, errors }, null, 2));
console.log(`[apply] done: ${ok} ok, ${err} err. Rollback: node reprice.mjs rollback snapshot-${stamp}.jsonl`);