`extract-mfr.js`

68 lines
'use strict';
/**
 * Step 1 of the price-sourcing pipeline: get mfr_sku on ALL unpriced showroom-line
 * products (the Tier-B set). For each in-scope vendor, tag-query its Showroom-Line
 * (sample-only) products and record dw_sku, mfr_sku (barcode || custom.manufacturer_sku),
 * pattern, color, title into price_sourcing. Resumable (skips dw_skus already present).
 *
 * Excludes vendors already handled or out of scope: Kravet-family (priced via 2026 MAP),
 * Phillip Jeffries (lookup-only), Phillipe Romano (DW own brand — direct price, no vendor).
 */
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 ENDPOINT = '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 sleep = ms => new Promise(r => setTimeout(r, ms));
const EXCLUDE = new Set(['kravet','brunschwig & fils','lee jofa','gp & j baker','mulberry','threads','baker lifestyle','clarke and clarke','gaston y daniela','cole & son','winfield thybony','donghia','phillip jeffries','phillipe romano','schumacher','thibaut']);

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(ENDPOINT, { 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');
}
function pgEsc(s) { return String(s == null ? '' : s).replace(/'/g, "''"); }
function upsert(rows) {
  // drop blank dw_sku and de-dup within the batch (ON CONFLICT can't hit the same key twice)
  const seen = new Set();
  rows = rows.filter(r => r.dw && !seen.has(r.dw) && seen.add(r.dw));
  if (!rows.length) return;
  const vals = rows.map(r => `('${pgEsc(r.dw)}','${pgEsc(r.vendor)}','${pgEsc(r.mfr)}','${pgEsc(r.pat)}','${pgEsc(r.col)}','${pgEsc(r.title)}')`).join(',');
  const sql = `INSERT INTO price_sourcing (dw_sku,vendor,mfr_sku,pattern,color,title) VALUES ${vals} ON CONFLICT (dw_sku) DO UPDATE SET mfr_sku=EXCLUDED.mfr_sku,pattern=EXCLUDED.pattern,color=EXCLUDED.color,title=EXCLUDED.title,updated_at=now();`;
  execSync(`psql -q -c ${JSON.stringify(sql)}`, { env: PGENV });
}

(async () => {
  const vendors = JSON.parse(fs.readFileSync('/Users/macstudio3/Projects/designerwallcoverings/showroom-vendors.json', 'utf8')).rows
    .filter(r => !EXCLUDE.has(r.vendor.toLowerCase())).sort((a, b) => b.sampleOnly - a.sampleOnly).map(r => r.vendor);
  console.log(`in-scope vendors: ${vendors.length}`);
  let totalNew = 0;
  for (const vendor of vendors) {
    const Q = `query($c:String){ products(first:80, after:$c, query:${JSON.stringify("status:active tag:'Showroom Line' vendor:\"" + vendor + "\"")}){ pageInfo{hasNextPage endCursor} nodes{ title variants(first:2){nodes{price sku barcode}} mf:metafield(namespace:"custom",key:"manufacturer_sku"){value} pn:metafield(namespace:"custom",key:"pattern_name"){value} cn:metafield(namespace:"custom",key:"color_1_name"){value} } } }`;
    let c = null, batch = [], n = 0;
    do {
      const r = await g(Q, { c }); const conn = r.data && r.data.products; if (!conn) break;
      for (const p of conn.nodes) {
        const vs = p.variants.nodes; const s = vs.find(v => parseFloat(v.price) <= 4.255);
        if (!(vs.length === 1 && s)) continue;
        batch.push({ dw: (s.sku || '').replace(/-sample$/i, ''), vendor, mfr: s.barcode || (p.mf && p.mf.value) || '', pat: (p.pn && p.pn.value) || p.title.split('|')[0].trim(), col: (p.cn && p.cn.value) || '', title: p.title.slice(0, 200) });
        n++;
        if (batch.length >= 100) { upsert(batch); totalNew += batch.length; batch = []; }
      }
      c = conn.pageInfo.hasNextPage ? conn.pageInfo.endCursor : null;
    } while (c);
    if (batch.length) { upsert(batch); totalNew += batch.length; }
    console.log(`  ${vendor}: ${n}`);
  }
  console.log(`\nDone. upserted ~${totalNew} rows into price_sourcing.`);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });