`sync-state.js`

59 lines
'use strict';
/**
 * Sync price_sourcing to current Shopify reality: any showroom SKU that NOW has a real
 * roll variant (no longer single $4.25 sample) → has_price=true, so it drops off the
 * letter queue. Reflects "some products updated". Letters sent are already tracked
 * (letter_status='sent' set by the web page on send). Resumable per 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));
function psql(s) { return execSync(`psql -tAc ${JSON.stringify(s)}`, { env: PGENV, encoding: 'utf8' }); }

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');
}

(async () => {
  // vendors still in the letter queue (have unpriced rows)
  const vendors = psql(`SELECT DISTINCT vendor FROM price_sourcing WHERE COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY vendor`).split('\n').filter(Boolean);
  let updated = 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{ variants(first:3){nodes{price sku}} } } }`;
    let c = null, priced = [];
    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 stillSampleOnly = vs.length === 1 && parseFloat(vs[0].price) <= 4.255;
        if (!stillSampleOnly) { const s = vs.find(v => parseFloat(v.price) <= 4.255) || vs[0]; const dw = (s.sku || '').replace(/-sample$/i, ''); if (dw) priced.push(dw); }
      }
      c = conn.pageInfo.hasNextPage ? conn.pageInfo.endCursor : null;
    } while (c);
    if (priced.length) {
      // batch update has_price=true in chunks
      for (let i = 0; i < priced.length; i += 200) {
        const chunk = priced.slice(i, i + 200).map(s => `'${s.replace(/'/g, "''")}'`).join(',');
        psql(`UPDATE price_sourcing SET has_price=true, crawl_status='found', updated_at=now() WHERE dw_sku IN (${chunk})`);
      }
      updated += priced.length;
      console.log(`  ${vendor}: ${priced.length} now priced`);
    }
  }
  console.log(`\nSync done: ${updated} products marked priced (dropped from letter queue).`);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });