`scripts/anna-french-rolls/af-reprice-live.js`

106 lines
'use strict';
/* Anna French (DWAA) single-roll LIVE reprice — Steve-authorized 2026-06-17 (option 3:
   accept Mar last-known-good retail). For each active Anna French ROLL product (title !~ fabric)
   joined to anna_french_catalog.price_retail:
     newPrice = round(retail * 0.45 / 0.65 / 0.85, 2)   (cost=retail×0.45, sell=cost/0.65/0.85)
   - If a real ROLL variant exists (price > $4.255, the non-sample variant): UPDATE its price.
   - Set product metafield custom.packaging = single-roll/double-pack note (if absent).
   - If NO roll variant yet (still sample-only): log to out/af-reprice-noroll.jsonl, DO NOT create
     (variant creation is daily-capped — handled by af-build with the new plan).
   NEVER touches the $4.25 sample variant. DRY by default; set COMMIT=1 to write.
   REPRICE_LIMIT=N caps the run (canary). Resumable via out/af-reprice-done.txt. */
const fs = require('fs');
const { execSync } = require('child_process');
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 DIR = __dirname + '/out';
const DONE = DIR + '/af-reprice-done.txt';
const LOG = DIR + '/af-reprice-log.jsonl';
const NOROLL = DIR + '/af-reprice-noroll.jsonl';
const COMMIT = process.env.COMMIT === '1';
const LIMIT = parseInt(process.env.REPRICE_LIMIT || '0', 10) || Infinity;
const PRICE_CAP = 2000; // safety: never push a roll price above this without review
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; }
    const j = await r.json();
    if (j.errors && JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000 * (i + 1)); continue; }
    return j;
  }
  throw new Error('exhausted');
}

const Q = `query($id:ID!){ product(id:$id){ id title status
  options{name}
  variants(first:20){ nodes{ id sku price selectedOptions{name value} } }
  packaging: metafield(namespace:"custom", key:"packaging"){ id value } } }`;
const UPD = `mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){
  productVariantsBulkUpdate(productId:$pid, variants:$variants){ productVariants{ id price } userErrors{ message } } }`;
const META = `mutation($mf:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$mf){ userErrors{ message } } }`;
const PKG = 'Sold by the single roll; packaged in double roll.';

function rows() {
  const raw = execSync(`psql -tAc "
    select a.shopify_id, a.mfr_sku, round(c.price_retail*0.45/0.65/0.85,2) newprice
    from shopify_products a join anna_french_catalog c on upper(c.mfr_sku)=upper(a.mfr_sku)
    where a.vendor ilike '%anna%french%' and a.status='ACTIVE' and c.price_retail>0 and a.title !~* 'fabric'
    order by a.shopify_id"`, { env: PGENV, encoding: 'utf8' });
  return raw.split('\n').filter(Boolean).map(l => { const [id, mfr, np] = l.split('|'); return { sid: id, mfr, newPrice: parseFloat(np) }; });
}

(async () => {
  const all = rows();
  const done = new Set(fs.existsSync(DONE) ? fs.readFileSync(DONE, 'utf8').split('\n').filter(Boolean) : []);
  const todo = all.filter(r => !done.has(r.sid)).slice(0, LIMIT);
  console.log(`mode=${COMMIT ? 'COMMIT' : 'DRY'} total=${all.length} done=${done.size} todo=${todo.length}${LIMIT !== Infinity ? ' (limit ' + LIMIT + ')' : ''}`);
  const doneS = fs.createWriteStream(DONE, { flags: 'a' }), logS = fs.createWriteStream(LOG, { flags: 'a' }), norollS = fs.createWriteStream(NOROLL, { flags: 'a' });
  const t = { repriced: 0, alreadyOk: 0, pkgSet: 0, pkgOk: 0, noRoll: 0, skipCap: 0, err: 0, gone: 0 };
  for (const r of todo) {
    const gid = r.sid.startsWith('gid://') ? r.sid : `gid://shopify/Product/${r.sid}`;
    const lk = await g(Q, { id: gid });
    const p = lk.data && lk.data.product;
    if (!p || p.status !== 'ACTIVE') { t.gone++; if (COMMIT) doneS.write(r.sid + '\n'); continue; }
    // roll variant = the non-sample variant (price > 4.255). Prefer a "Roll"-named option value.
    const vs = p.variants.nodes;
    const nonSample = vs.filter(v => parseFloat(v.price) > 4.255);
    let roll = nonSample.find(v => v.selectedOptions.some(o => /roll/i.test(o.value))) || (nonSample.length === 1 ? nonSample[0] : null);
    const rec = { sid: r.sid, mfr: r.mfr, title: p.title.slice(0, 50), newPrice: r.newPrice };
    if (r.newPrice <= 0 || r.newPrice >= PRICE_CAP) { t.skipCap++; rec.action = 'skip-price-cap'; logS.write(JSON.stringify(rec) + '\n'); if (COMMIT) doneS.write(r.sid + '\n'); continue; }
    if (!roll) { t.noRoll++; rec.action = 'no-roll-variant'; rec.variants = vs.map(v => ({ sku: v.sku, price: v.price })); norollS.write(JSON.stringify(rec) + '\n'); logS.write(JSON.stringify(rec) + '\n'); if (COMMIT) doneS.write(r.sid + '\n'); continue; }
    rec.oldPrice = roll.price; rec.variantId = roll.id;
    // 1) reprice if differs
    const differs = Math.abs(parseFloat(roll.price) - r.newPrice) >= 0.005;
    if (differs) {
      if (COMMIT) {
        const rr = await g(UPD, { pid: gid, variants: [{ id: roll.id, price: String(r.newPrice) }] });
        const e = (rr.data && rr.data.productVariantsBulkUpdate && rr.data.productVariantsBulkUpdate.userErrors) || [];
        if (e.length) { t.err++; rec.action = 'err:' + JSON.stringify(e); logS.write(JSON.stringify(rec) + '\n'); continue; }
      }
      t.repriced++; rec.action = 'repriced';
    } else { t.alreadyOk++; rec.action = 'price-ok'; }
    // 2) packaging metafield
    if (!(p.packaging && p.packaging.value)) {
      if (COMMIT) {
        const mr = await g(META, { mf: [{ ownerId: gid, namespace: 'custom', key: 'packaging', type: 'single_line_text_field', value: PKG }] });
        const me = (mr.data && mr.data.metafieldsSet && mr.data.metafieldsSet.userErrors) || [];
        if (me.length) { rec.pkgErr = JSON.stringify(me); } else t.pkgSet++;
      } else t.pkgSet++;
      rec.pkg = 'set';
    } else { t.pkgOk++; rec.pkg = 'ok'; }
    logS.write(JSON.stringify(rec) + '\n');
    if (COMMIT) doneS.write(r.sid + '\n');
    console.log(`  ${rec.action}${differs ? ` $${roll.price}->$${r.newPrice}` : ` $${roll.price}`}  pkg=${rec.pkg}  ${rec.title}`);
    await sleep(280);
  }
  doneS.end(); logS.end(); norollS.end();
  console.log('\n' + JSON.stringify(t, null, 2));
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });