`apply_shopify.mjs`

113 lines
// apply_shopify.mjs — onboard Justin David / Los Angeles Fabrics per-yard pricing to the
// LIVE DW Shopify store. Idempotent + resumable, driven by dw_unified.justin_david_pricing_2026.
//
// Per priced ACTIVE product:
//   • ensure a "Sold per Yard" variant exists (sku <base>-Yard, taxable) priced at retail_yd,
//     relabeling the lone "Default Title" sample variant to "Sample" when adding it;
//   • if it already exists, reprice it to retail_yd when off by >$0.01;
//   • set the Yard variant inventory-item unit cost = our_cost_yd;
//   • PG-first: update the local mirror's rollup, then mark the staging row applied.
//
// Modes:  --dry-run (default: no writes)   --pilot=N (apply first N only)   --apply (full run)
// Resumes automatically: rows already apply_status='ok' are skipped unless --force.
import { execFileSync } from 'node:child_process';

const SHOP = process.env.DW_SHOP || 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const TOKEN = (execFileSync('bash', ['-lc',
  `grep -m1 '^SHOPIFY_ADMIN_TOKEN=' "$HOME/Projects/secrets-manager/.env" | cut -d= -f2- | tr -d '"'"'"'\\r'`],
  { encoding: 'utf8' }).trim());
const DBURL = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
const PSQL = 'psql';
const args = process.argv.slice(2);
const DRY = !args.includes('--apply') && !args.some(a => a.startsWith('--pilot'));
const PILOT = (() => { const a = args.find(x => x.startsWith('--pilot')); return a ? +a.split('=')[1] : 0; })();
const FORCE = args.includes('--force');
const DELAY = 350;

const sleep = ms => new Promise(r => setTimeout(r, ms));
function q(sql) {
  const out = execFileSync(PSQL, [DBURL, '-At', '-F', '\t', '-c', sql], { encoding: 'utf8', maxBuffer: 128 * 1024 * 1024 });
  return out.trim() ? out.trim().split('\n').map(r => r.split('\t')) : [];
}
function qx(sql) { execFileSync(PSQL, [DBURL, '-q', '-c', sql], { stdio: 'ignore' }); }
function esc(s) { return String(s).replace(/'/g, "''"); }

async function api(method, path, body) {
  const url = `https://${SHOP}/admin/api/${API}/${path}`;
  for (let i = 0; i < 5; i++) {
    const res = await fetch(url, { method,
      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
      body: body ? JSON.stringify(body) : undefined });
    if (res.status === 429) { await sleep(2000 * (i + 1)); continue; }
    const txt = await res.text();
    if (!res.ok) throw new Error(`${method} ${path} -> HTTP ${res.status}: ${txt.slice(0, 200)}`);
    await sleep(DELAY);
    return txt ? JSON.parse(txt) : {};
  }
  throw new Error(`${method} ${path} -> 429 exhausted`);
}

// work queue: priced ACTIVE rows not yet applied (unless --force)
let rows = q(`select sp.handle, j.dw_sku, j.retail_yd, j.our_cost_yd, j.needs_peryard_variant, j.jd_pattern
  from shopify_products sp join justin_david_pricing_2026 j on j.dw_sku=sp.sku
  where sp.status='ACTIVE' and j.match_status='priced' and sp.handle is not null
  ${FORCE ? '' : "and (j.apply_status is distinct from 'ok')"}
  order by sp.handle`)
  .map(([handle, sku, retail, cost, needs, pat]) => ({ handle, base: sku.replace(/-Sample$/i, ''),
    retail: +retail, cost: +cost, needs: needs === 't', pat }));
if (PILOT) rows = rows.slice(0, PILOT);

console.log(`[apply] mode=${DRY ? 'DRY-RUN' : PILOT ? `PILOT(${PILOT})` : 'APPLY'} · store=${SHOP} · token=...${TOKEN.slice(-4)} · queue=${rows.length}`);
let added = 0, repriced = 0, costset = 0, noop = 0, errors = 0;

for (const r of rows) {
  try {
    const pj = await api('GET', `products.json?handle=${encodeURIComponent(r.handle)}`);
    const p = pj.products?.[0];
    if (!p) throw new Error('product not found by handle');
    const yard = p.variants.find(v => /yard/i.test(v.option1 || '') || /-yard$/i.test(v.sku || ''));
    const sample = p.variants.find(v => v !== yard);
    let action = 'noop', yardVariant = yard;

    if (!yard) {
      // add "Sold per Yard" + relabel the sole sample variant "Default Title" -> "Sample"
      if (!DRY) {
        const put = { product: { id: p.id, options: [{ id: p.options[0].id, name: 'Title' }],
          variants: [
            { id: sample.id, option1: 'Sample', price: String(sample.price), sku: sample.sku, taxable: true },
            { option1: 'Sold per Yard', price: r.retail.toFixed(2), sku: `${r.base}-Yard`, taxable: true, inventory_management: null },
          ] } };
        const resp = await api('PUT', `products/${p.id}.json`, put);
        yardVariant = resp.product.variants.find(v => /yard/i.test(v.option1 || ''));
      }
      action = 'added'; added++;
    } else if (Math.abs(+yard.price - r.retail) > 0.01) {
      if (!DRY) await api('PUT', `variants/${yard.id}.json`, { variant: { id: yard.id, price: r.retail.toFixed(2) } });
      action = `repriced ${yard.price}->${r.retail.toFixed(2)}`; repriced++;
    } else { noop++; }

    // set unit cost on the yard variant's inventory item
    if (!DRY && yardVariant?.inventory_item_id && r.cost > 0) {
      await api('PUT', `inventory_items/${yardVariant.inventory_item_id}.json`,
        { inventory_item: { id: yardVariant.inventory_item_id, cost: r.cost.toFixed(2) } });
      costset++;
    }

    if (!DRY) {
      // PG-first mirror rollup + staging mark
      qx(`update shopify_products set has_product_variant=true, retail_price=${r.retail}, min_variant_price=least(coalesce(min_variant_price,4.25),4.25)
          where sku='${esc(r.base)}-Sample' or sku='${esc(r.base)}-Yard'`);
      qx(`update justin_david_pricing_2026 set apply_status='ok', applied_at=now(), apply_note='${esc(action)}' where dw_sku='${esc(r.base)}-Sample' or dw_sku='${esc(r.base)}-Yard'`);
    }
    console.log(`  ${DRY ? 'would' : 'did'} ${action.padEnd(26)} ${r.handle}  ($${r.retail.toFixed(2)}/yd)`);
  } catch (e) {
    errors++;
    const msg = String(e.message || e).slice(0, 160);
    if (!DRY) qx(`update justin_david_pricing_2026 set apply_status='err', apply_note='${esc(msg)}' where dw_sku='${esc(r.base)}-Sample'`);
    console.log(`  ✗ ERROR ${r.handle}: ${msg}`);
  }
}
console.log(`\n[apply] done · added=${added} repriced=${repriced} cost_set=${costset} noop=${noop} errors=${errors}`);
if (DRY) console.log('  (DRY-RUN — no writes. Re-run with --pilot=3 then --apply.)');