`scripts/scan-live.js`

117 lines
#!/usr/bin/env node
/**
 * Step-0 live scanner (DTD verdict B, 2026-06-21).
 *
 * READ-ONLY. Scans every ACTIVE Shopify product, reads each variant's live
 * title + price + sku + the product descriptionHtml, classifies the 5-field
 * structure per SKU, and persists ONE row per active product into
 * dw_unified.active_five_field_status (truncate+reload, idempotent).
 *
 * Ground truth for has_sample / has_product (the mirror -Sample-suffix
 * heuristic misclassifies ~5k SKUs — Koroseal $4.25-leak, non-suffixed
 * samples). We classify a variant as a SAMPLE if its title says "sample" OR
 * its sku ends -sample OR (single-variant product whose only variant is
 * priced exactly 4.25 — the leak pattern). has_product = a non-sample variant.
 *
 * Cost: $0 (free Shopify GraphQL reads). No writes to Shopify.
 */
const fs = require('fs');
const { Pool } = require('pg');

const env = fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
const TOKEN = (env.match(/SHOPIFY_ADMIN_TOKEN=(\S+)/) || [])[1];
const DOMAIN = 'designer-laboratory-sandbox.myshopify.com', VER = '2024-10';
const URL = `https://${DOMAIN}/admin/api/${VER}/graphql.json`;
// Connect over the unix socket (peer auth, no password) exactly like the psql
// CLI does — TCP to 127.0.0.1 demands a password the local trust setup rejects.
delete process.env.PGPASSWORD;
delete process.env.DATABASE_URL;
const pool = new Pool({ host: '/tmp', database: 'dw_unified' });

const Q = `query($cursor:String){products(first:120,after:$cursor,query:"status:active"){pageInfo{hasNextPage endCursor} edges{node{id vendor descriptionHtml variants(first:30){edges{node{title sku price}}}}}}}`;

const isSampleByLabel = x => /sample/i.test(x.title || '') || /-sample$/i.test(x.sku || '');

async function gql(cursor) {
  for (let a = 0; a < 8; a++) {
    const r = await fetch(URL, { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN }, body: JSON.stringify({ query: Q, variables: { cursor } }) });
    const j = await r.json();
    if (j.errors && JSON.stringify(j.errors).includes('Throttled')) { await new Promise(s => setTimeout(s, 3000)); continue; }
    return j;
  }
  throw new Error('throttled-exhausted');
}

(async () => {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS active_five_field_status (
      shopify_id text PRIMARY KEY,
      vendor text,
      bare_sku text,        -- the non-sample variant sku (or stripped), our dw_sku join key
      has_sample boolean,
      has_product boolean,
      any_zero boolean,     -- any variant price 0/null
      product_price numeric, -- price of the (first) non-sample variant, if any
      sample_price numeric,
      variant_count int,
      no_desc boolean,
      scanned_at timestamptz default now()
    );`);
  await pool.query('TRUNCATE active_five_field_status;');

  let cursor = null, n = 0, pages = 0, batch = [];
  const flush = async () => {
    if (!batch.length) return;
    const vals = [], ph = [];
    batch.forEach((r, i) => {
      const b = i * 10;
      ph.push(`($${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10})`);
      vals.push(r.shopify_id, r.vendor, r.bare_sku, r.has_sample, r.has_product, r.any_zero, r.product_price, r.sample_price, r.variant_count, r.no_desc);
    });
    await pool.query(`INSERT INTO active_five_field_status
      (shopify_id,vendor,bare_sku,has_sample,has_product,any_zero,product_price,sample_price,variant_count,no_desc)
      VALUES ${ph.join(',')} ON CONFLICT (shopify_id) DO NOTHING`, vals);
    batch = [];
  };

  while (true) {
    const j = await gql(cursor);
    if (j.errors) { console.error('ERR', JSON.stringify(j.errors).slice(0, 300)); break; }
    for (const { node: p } of j.data.products.edges) {
      n++;
      const vs = p.variants.edges.map(e => e.node);
      const prices = vs.map(x => (x.price == null ? null : parseFloat(x.price)));
      const labelSample = vs.map(isSampleByLabel);
      // Leak detection: single variant, not labeled sample, priced exactly 4.25 -> it's a leaked sample (no real product).
      const leak = vs.length === 1 && !labelSample[0] && prices[0] === 4.25;
      const isSample = vs.map((x, i) => labelSample[i] || (leak && i === 0));
      const hasSample = isSample.some(Boolean);
      const hasProduct = vs.some((x, i) => !isSample[i]);
      const nonSampleIdx = vs.findIndex((x, i) => !isSample[i]);
      const sampleIdx = isSample.indexOf(true);
      const anyZero = prices.some(pp => pp == null || pp === 0);
      const txt = (p.descriptionHtml || '').replace(/<[^>]*>/g, '').trim();
      const rawSku = (nonSampleIdx >= 0 ? vs[nonSampleIdx].sku : vs[0]?.sku) || '';
      const bare = rawSku.replace(/-Sample$/i, '');
      batch.push({
        shopify_id: p.id, vendor: p.vendor, bare_sku: bare,
        has_sample: hasSample, has_product: hasProduct, any_zero: anyZero,
        product_price: nonSampleIdx >= 0 ? prices[nonSampleIdx] : null,
        sample_price: sampleIdx >= 0 ? prices[sampleIdx] : null,
        variant_count: vs.length, no_desc: txt.length < 5,
      });
    }
    if (batch.length >= 400) await flush();
    pages++;
    const pi = j.data.products.pageInfo;
    cursor = pi.endCursor;
    if (pages % 25 === 0) console.error(`...${n} scanned`);
    if (!pi.hasNextPage) break;
    await new Promise(s => setTimeout(s, 220));
  }
  await flush();
  const c = await pool.query('SELECT count(*) FROM active_five_field_status');
  console.log(`DONE scanned=${n} persisted=${c.rows[0].count}`);
  await pool.end();
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });