`build-romo-rolls.mjs`

129 lines
'use strict';
/**
 * Romo-group "Sold Per Roll" builder — matches LIVE Shopify products to romo_catalog
 * cost by the custom.manufacturer_sku metafield (the W-code), NOT dw_sku.
 *
 * Why a dedicated script: romo_catalog.dw_sku is DWRM-* (a parallel Romo import not on
 * Shopify); the LIVE products are DWLA-* and carry the manufacturer code only in the
 * custom.manufacturer_sku metafield. So build-roll-scale.js (dw_sku-keyed, product_map-
 * driven) cannot reach them. This bridges live<->cost by manufacturer_sku.
 *
 * Price = romo_catalog.our_price (= (cost+tariff)/0.65/0.85; markup 1.90-1.99x cost,
 * never below cost — verified). Adds ONE "Sold Per Roll" variant. Guards mirror
 * build-roll-scale: only single-variant <=$4.255 sample-only products, never overwrites
 * the sample, never flips ACTIVE, shared daily variant budget, ROLL_CAP, resumable.
 *
 * Env: ROLL_VENDOR (brand, required), ROLL_CAP (default 3), DRY_RUN=1 (no writes).
 */
import fs from 'fs';
import { execFileSync } from 'child_process';
import { createRequire } from 'module';
const require = createRequire(import.meta.url);
const budget = require('./scripts/variant-budget/budget.cjs');

const ENV = fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env', 'utf8');
const TOKEN = (ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1];
const EP = 'https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json';
const PGENV = { PGHOST: '/tmp', PGPORT: '5432', PGUSER: 'stevestudio2', PGDATABASE: 'dw_unified', PATH: '/opt/homebrew/opt/postgresql@14/bin:/usr/bin:/bin' };

const VENDOR = process.env.ROLL_VENDOR;
const CAP = parseInt(process.env.ROLL_CAP || '3', 10);
const DRY = process.env.DRY_RUN === '1';
if (!VENDOR) { console.error('ROLL_VENDOR required'); process.exit(1); }
const slug = VENDOR.toLowerCase().replace(/[^a-z0-9]+/g, '-');
const DONE = `${process.cwd()}/romo-rolls-done-${slug}.txt`;
const CREATED = `${process.cwd()}/romo-rolls-created-${slug}.jsonl`;
const LIMIT_RE = /exceed|daily.*limit|limit.*reached|throttl|too many/i;
const sleep = ms => new Promise(r => setTimeout(r, ms));

async function g(q, v) {
  for (let i = 0; i < 7; i++) {
    let r; const ac = new AbortController(); const to = setTimeout(() => ac.abort(), 25000);
    try { r = await fetch(EP, { method: 'POST', signal: ac.signal, headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' }, body: JSON.stringify({ query: q, variables: v }) }); }
    catch (e) { clearTimeout(to); await sleep(1500 * (i + 1)); continue; }
    clearTimeout(to);
    if (r.status === 429 || r.status >= 500) { await sleep(2000 * (i + 1)); continue; }
    let j; try { j = await r.json(); } catch (e) { await sleep(2000 * (i + 1)); continue; }
    if (j.errors && JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000 * (i + 1)); continue; }
    return j;
  }
  throw new Error('exhausted retries');
}

// preload mfr_sku(upper,trim) -> our_price for this brand
function priceMap() {
  const raw = execFileSync('psql', ['-tAc',
    `SELECT upper(trim(mfr_sku))||'~~'||our_price FROM romo_catalog WHERE brand ILIKE '%${VENDOR.replace(/'/g, "''")}%' AND mfr_sku<>'' AND our_price>trade_price_per_roll AND our_price>4.26`],
    { env: PGENV, encoding: 'utf8' });
  const m = new Map();
  raw.split('\n').filter(Boolean).forEach(l => { const [k, p] = l.split('~~'); if (!m.has(k)) m.set(k, parseFloat(p)); });
  return m;
}

const LIST = `query($q:String!,$after:String){ products(first:30, query:$q, after:$after){ pageInfo{hasNextPage endCursor} nodes{ id title options{name} metafield(namespace:"custom",key:"manufacturer_sku"){value} variants(first:5){nodes{id sku price selectedOptions{value}}} } } }`;
const MUT = `mutation($pid: ID!, $variants: [ProductVariantsBulkInput!]!) {
  productVariantsBulkCreate(productId: $pid, variants: $variants) { productVariants { id price sku } userErrors { message } } }`;
const UPD = `mutation($pid: ID!, $variants: [ProductVariantsBulkInput!]!) {
  productVariantsBulkUpdate(productId: $pid, variants: $variants) { productVariants { id } userErrors { message } } }`;

const PM = priceMap();
console.log(`[${VENDOR}] ${DRY ? 'DRY-RUN' : 'LIVE'} | price-map mfr_skus=${PM.size} | cap=${CAP}`);
const done = new Set(fs.existsSync(DONE) ? fs.readFileSync(DONE, 'utf8').split('\n').filter(Boolean) : []);
let made = 0, skipped = 0, after = null, scanned = 0;

while (made < CAP) {
  const lk = await g(LIST, { q: `vendor:"${VENDOR}" status:active`, after });
  const conn = lk.data && lk.data.products;
  if (!conn) { console.log('no products / error', JSON.stringify(lk.errors || {})); break; }
  for (const p of conn.nodes) {
    if (made >= CAP) break;
    const baseSku = (p.variants.nodes[0] && p.variants.nodes[0].sku || '').replace(/-Sample$/i, '');
    if (!baseSku) { skipped++; continue; }   // no SKU → skip; never build a colliding "-Roll" variant
    if (done.has(baseSku)) continue;
    scanned++;
    const vs = p.variants.nodes;
    // sample-only guard: exactly one variant, <= $4.255
    if (!(vs.length === 1 && parseFloat(vs[0].price) <= 4.255)) { skipped++; continue; }
    const mfr = (p.metafield && p.metafield.value || '').toUpperCase().trim();
    const price = mfr && PM.get(mfr);
    if (!price) { skipped++; continue; }
    const optName = (p.options[0] && p.options[0].name) || 'Title';
    if (DRY) {
      console.log(`  WOULD BUILD  ${baseSku}  "${p.title.slice(0, 32)}"  mfr=${mfr}  roll=$${price.toFixed(2)}`);
      made++; continue;
    }
    if (budget.take('roll', 1) < 1) { console.log('Daily variant budget spent — stopping (resumable).'); after = null; break; }
    // CRITICAL: a product whose only variant is Shopify's implicit default ("Default Title")
    // cannot coexist with a named-option variant — bulkCreate would REPLACE the sample. So
    // first RENAME the default variant's option value to "Sample" (update, not create), which
    // preserves it; only then does creating "Sold Per Roll" ADD a 2nd variant.
    const v0 = vs[0];
    const v0opt = (v0.selectedOptions && v0.selectedOptions[0] && v0.selectedOptions[0].value) || '';
    if (/^default title$/i.test(v0opt)) {
      const ur = await g(UPD, { pid: p.id, variants: [{ id: v0.id, optionValues: [{ optionName: optName, name: 'Sample' }] }] });
      const ue = (ur.data && ur.data.productVariantsBulkUpdate && ur.data.productVariantsBulkUpdate.userErrors) || [];
      if (ue.length) { skipped++; console.log(`  ERR rename-default ${baseSku}: ${JSON.stringify(ue)}`); continue; }
    }
    const variants = [{ price: String(price), optionValues: [{ optionName: optName, name: 'Sold Per Roll' }], inventoryPolicy: 'CONTINUE', inventoryItem: { sku: `${baseSku}-Roll`, tracked: false } }];
    const rr = await g(MUT, { pid: p.id, variants });
    // safety net: if create failed but the rename already ran, the product is sample-only
    // (option value "Sample") — still valid, not roll-only. No sample is ever lost.
    const res = rr.data && rr.data.productVariantsBulkCreate;
    const e = (res && res.userErrors) || [];
    if (e.length) {
      if (LIMIT_RE.test(JSON.stringify(e))) { console.log('Variant daily-limit/throttle — aborting:', JSON.stringify(e)); after = null; break; }
      skipped++; console.log(`  ERR ${baseSku}: ${JSON.stringify(e)}`); continue;
    }
    const nv = res.productVariants[0];
    made++; done.add(baseSku);
    fs.appendFileSync(DONE, baseSku + '\n');
    fs.appendFileSync(CREATED, JSON.stringify({ pid: p.id, sku: nv.sku, price: nv.price, mfr, title: p.title }) + '\n');
    console.log(`  BUILT ${baseSku}  "${p.title.slice(0, 28)}"  roll=$${nv.price}`);
    await sleep(300);
  }
  if (!conn.pageInfo.hasNextPage || after === null && made >= CAP) break;
  if (!conn.pageInfo.hasNextPage) break;
  after = conn.pageInfo.endCursor;
}
console.log(`\n[${VENDOR}] ${DRY ? 'DRY-RUN' : 'LIVE'} done: ${made} ${DRY ? 'would-build' : 'built'}, ${skipped} skipped, scanned ${scanned}.`);