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

101 lines
'use strict';
/* READ-ONLY: pull all live Anna French products from Shopify, classify sample-only,
   unit (fabric=Yard / wallcovering=Roll), and resolve price from thibaut_catalog
   (dw_retail_price) else anna_french_catalog (price_retail*0.8824), joined by mfr_sku.
   Writes scripts/anna-french-rolls/out/af-build-plan.jsonl. NO writes to Shopify. */
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 OUT = __dirname + '/out/af-build-plan.jsonl';
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');
}

// price map by mfr_sku: prefer thibaut_catalog.dw_retail_price, else anna_french_catalog.price_retail*0.8824
function priceMap() {
  const raw = execSync(`psql -tAc "
    SELECT mfr_sku, price, src FROM (
      SELECT mfr_sku, dw_retail_price::text price, 'thibaut_dw_retail' src FROM thibaut_catalog WHERE dw_retail_price>0
      UNION ALL
      SELECT mfr_sku, round(price_retail*0.8824,2)::text, 'af_retail_x0884' FROM anna_french_catalog WHERE price_retail>0
    ) u"`, { env: PGENV, encoding: 'utf8' });
  const m = {};
  for (const l of raw.split('\n').filter(Boolean)) {
    const [mfr, price, src] = l.split('|');
    if (!m[mfr] || src === 'thibaut_dw_retail') m[mfr] = { price: parseFloat(price), src }; // thibaut wins
  }
  return m;
}

const Q = `query($cur:String){ products(first:50, after:$cur, query:"vendor:'Anna French' status:active"){
  pageInfo{ hasNextPage endCursor }
  nodes{ id title productType tags options{name}
    mfrSku: metafield(namespace:"custom", key:"manufacturer_sku"){ value }
    curw: metafield(namespace:"custom", key:"width"){ value }
    variants(first:10){ nodes{ sku price barcode } } } } }`;

// dims map: mfr_sku -> {width_inches, length}  (AF catalog length = DOUBLE roll)
function dimsMap() {
  const raw = execSync(`psql -tAc "SELECT mfr_sku, COALESCE(width_inches::text,''), COALESCE(length,'') FROM anna_french_catalog WHERE mfr_sku<>''"`, { env: PGENV, encoding: 'utf8' });
  const m = {};
  for (const l of raw.split('\n').filter(Boolean)) { const [mfr, w, len] = l.split('|'); if (!m[mfr]) m[mfr] = { width_inches: w ? +w : null, length: len }; }
  return m;
}

(async () => {
  const { rollLabel, lengthMetafieldValue } = await import('../roll-length-label/roll-label.mjs');
  const pm = priceMap();
  const dm = dimsMap();
  const out = fs.createWriteStream(OUT);
  let cur = null, n = 0; const tally = { total: 0, sampleOnly: 0, hasRoll: 0, fabric: 0, wall: 0, priced: 0, noPrice: 0, buildable: 0 };
  do {
    const j = await g(Q, { cur });
    const conn = j.data.products;
    for (const p of conn.nodes) {
      tally.total++;
      const vs = p.variants.nodes;
      const sampleOnly = vs.length === 1 && parseFloat(vs[0].price || '0') <= 4.255;
      if (sampleOnly) tally.sampleOnly++; else tally.hasRoll++;
      const isFabric = /fabric/i.test(p.productType + ' ' + p.title);
      isFabric ? tally.fabric++ : tally.wall++;
      // mfr_sku: custom.manufacturer_sku metafield (canonical), else sample-variant barcode
      const mfr = (p.mfrSku && p.mfrSku.value) || (vs[0] && vs[0].barcode) || null;
      // unit label: fabric=Yard; wallcovering=single-roll dimensioned label (AF catalog=DOUBLE -> halve);
      // falls back to plain "Sold Per Roll" if dims missing. + length metafield value.
      const d = mfr && dm[mfr] ? dm[mfr] : null;
      let unit, lengthMf = null;
      if (isFabric) { unit = 'Sold Per Yard'; }
      else { unit = rollLabel({ width_inches: d && d.width_inches, widthText: p.curw && p.curw.value, lengthRaw: d && d.length, sourceIsDouble: true });
             lengthMf = lengthMetafieldValue(d && d.length, { sourceIsDouble: true }); }
      const pr = mfr && pm[mfr] ? pm[mfr] : null;
      if (pr) tally.priced++; else tally.noPrice++;
      const buildable = sampleOnly && pr;
      if (buildable) tally.buildable++;
      out.write(JSON.stringify({ id: p.id, title: p.title, sampleOnly, isFabric, unit, lengthMf,
        sampleSku: vs[0] && vs[0].sku, mfr, price: pr && pr.price, priceSrc: pr && pr.src,
        optName: (p.options[0] && p.options[0].name) || 'Size', buildable }) + '\n');
    }
    n += conn.nodes.length;
    cur = conn.pageInfo.hasNextPage ? conn.pageInfo.endCursor : null;
    process.stdout.write(`\r  pulled ${n}…`);
    await sleep(250);
  } while (cur);
  out.end();
  console.log('\n' + JSON.stringify(tally, null, 2));
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });