`order-history-check.mjs`

93 lines
#!/usr/bin/env node
// TK-10633 Option C — Cody's resolving test. READ-ONLY.
// Pull the last 12 months of Shopify order line-items, count how many of the 646
// target DWHD-* base/sample SKUs appear in ANY order (matched by line-item SKU),
// and the total line-item count referencing them. High refs = "immutability of
// printed SKUs" risk is real; near-zero = Option C is clean.
import { createRequire } from 'module';
import { readFileSync } from 'node:fs';
const require = createRequire(import.meta.url);
const { Pool } = require('pg');

const SHOP = 'designer-laboratory-sandbox.myshopify.com';
const VER = '2024-10';
// Use the FULL_ACCESS token (…2ea5) which carries read_orders + read_all_orders (12-mo).
// GET-ONLY — never a write with this token.
const TOKEN = (readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8')
  .split('\n').find(l => l.startsWith('SHOPIFY_FULL_ACCESS_TOKEN=')) || '')
  .replace('SHOPIFY_FULL_ACCESS_TOKEN=', '').replace(/["'\r]/g, '').trim();
if (!TOKEN) { console.error('no SHOPIFY_FULL_ACCESS_TOKEN'); process.exit(1); }

const pool = new Pool({ connectionString: (process.env.PGCONNSTRING || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified?host=/tmp') });
const BASE = `status='ACTIVE' AND variant_sku ~ '^DWHD' AND (metafields->'dwc'->'real_vendor'->>'value')='Momentum'`;

async function targetSkus() {
  const { rows } = await pool.query(`SELECT variant_sku FROM shopify_products WHERE ${BASE}`);
  const set = new Set();
  for (const r of rows) {
    const s = r.variant_sku;             // DWHD-#####-Sample
    set.add(s.toUpperCase());
    set.add(s.replace(/-sample$/i, '').toUpperCase()); // DWHD-##### base
  }
  return set;
}

async function getJSON(url) {
  const res = await fetch(url, { headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' } });
  if (res.status === 429) { await new Promise(r => setTimeout(r, 2000)); return getJSON(url); }
  if (!res.ok) throw new Error(`${res.status} ${await res.text()}`);
  const link = res.headers.get('link') || '';
  let next = null;
  const m = link.match(/<([^>]+)>;\s*rel="next"/);
  if (m) next = m[1];
  return { body: await res.json(), next };
}

async function main() {
  const targets = await targetSkus();
  const since = new Date(Date.now() - 365 * 864e5).toISOString();
  let url = `https://${SHOP}/admin/api/${VER}/orders.json?status=any&created_at_min=${since}&limit=250&fields=id,created_at,line_items`;
  let orderCount = 0, liCount = 0, matchedLi = 0;
  const matchedSkuHits = new Map();      // sku -> line-item count
  const matchedOrders = new Set();
  let pages = 0;
  while (url) {
    const { body, next } = await getJSON(url);
    const orders = body.orders || [];
    orderCount += orders.length;
    for (const o of orders) {
      for (const li of (o.line_items || [])) {
        liCount++;
        const sku = (li.sku || '').toUpperCase();
        if (sku && targets.has(sku)) {
          matchedLi++;
          matchedOrders.add(o.id);
          matchedSkuHits.set(sku, (matchedSkuHits.get(sku) || 0) + 1);
        }
      }
    }
    pages++;
    url = next;
    if (pages % 10 === 0) console.error(`  ...scanned ${orderCount} orders / ${liCount} line-items so far`);
  }

  const result = {
    generated_at: new Date().toISOString(),
    ticket: 'TK-10633',
    window: { since, until: new Date().toISOString() },
    orders_scanned: orderCount,
    line_items_scanned: liCount,
    target_skus_tracked: targets.size,   // 646 base + 646 sample forms
    // --- THE ANSWER ---
    distinct_target_skus_appearing_in_any_order: matchedSkuHits.size,
    total_matched_line_items: matchedLi,
    matched_orders: matchedOrders.size,
    top_hit_skus: [...matchedSkuHits.entries()].sort((a,b)=>b[1]-a[1]).slice(0, 20).map(([sku,n])=>({sku,n})),
  };
  const { writeFileSync } = await import('node:fs');
  writeFileSync('data/order-history.json', JSON.stringify(result, null, 2));
  console.log(JSON.stringify(result, null, 2));
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });