`scripts/availability-drift/availability-drift-canary.mjs`

89 lines
// ⚠️ INVALID-AS-BUILT FOR DW (2026-06-16) — DO NOT WIRE NIGHTLY.
// Empirically, ~97% of mirror-ACTIVE DW products report variant.available=false,
// because DW is a TRADE / memo-sample / inquiry storefront: most products are sold
// via sample-request, not direct add-to-cart, so available=false is BY DESIGN — not
// a stockout. This metric conflates by-design inquiry products with real stockouts
// and would cry wolf catastrophically (same lesson dw-scraper-canary learned by
// deferring price). A valid version must key on the inventory=2026 sentinel or the
// inquiry-CTA render, NOT variant.available. Kept for reference / future redesign.
//
// availability-drift-canary — sample top-vendor PDPs and compare what SHOPPERS see
// (storefront product JSON variant availability) against the dw_unified mirror's
// ACTIVE status. Catches conversion-killing drift the count/field canaries miss:
//   MISSING    — mirror ACTIVE but storefront /products/<handle>.json 404s (not published)
//   UNBUYABLE  — storefront product exists but EVERY variant available=false (shopper can't buy)
//   OK         — at least one variant purchasable
// READ-ONLY (public storefront JSON + local psql). $0. No writes.
//   node availability-drift-canary.mjs [--per N] [--vendors "A,B,..."]
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const PSQL = [ '/opt/homebrew/opt/postgresql@14/bin/psql', '/usr/local/opt/postgresql@14/bin/psql', 'psql' ]
  .find(p => { try { execFileSync(p, ['--version'], { stdio: 'ignore' }); return true; } catch { return false; } }) || 'psql';
const DB = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
const BASE = 'https://www.designerwallcoverings.com';
const OUT = `${process.env.HOME}/.claude/yolo-queue/availability-drift-2026-06-16.json`;
const MD = `${process.env.HOME}/.claude/yolo-queue/availability-drift-2026-06-16.md`;

const args = process.argv.slice(2);
const PER = parseInt(args.find((_,i,a)=>a[i-1]==='--per') || '5', 10) || 5;
const VENDORS = (args.find((_,i,a)=>a[i-1]==='--vendors') || 'Thibaut,Schumacher,Rebel Walls,Kravet,Brunschwig & Fils,Phillipe Romano,Hollywood Wallcoverings,China Seas').split(',');

function q(sql) {
  const out = execFileSync(PSQL, [DB, '-At', '-F', '|', '-c', sql], { encoding: 'utf8', maxBuffer: 64*1024*1024 });
  return out.trim() ? out.trim().split('\n').map(r => r.split('|')) : [];
}
const sleep = (ms) => new Promise(r => setTimeout(r, ms));

const vlist = VENDORS.map(v => `'${v.replace(/'/g,"''")}'`).join(',');
const rows = q(`
  with ranked as (
    select vendor, handle, mfr_sku, row_number() over (partition by vendor order by mfr_sku) rn
    from shopify_products where status='ACTIVE' and handle is not null and handle<>'' and vendor in (${vlist})
  ) select vendor, handle, mfr_sku from ranked where rn <= ${PER} order by vendor, handle`)
  .map(r => ({ vendor: r[0], handle: r[1], sku: r[2] }));

async function check(p) {
  try {
    const res = await fetch(`${BASE}/products/${p.handle}.json`, { headers: { 'User-Agent': 'dw-availability-canary' }, signal: AbortSignal.timeout(15000) });
    if (res.status === 404) return { ...p, state: 'MISSING', detail: '404 on storefront (mirror ACTIVE)' };
    if (!res.ok) return { ...p, state: 'UNKNOWN', detail: `http ${res.status}` };
    const j = await res.json();
    const variants = j.product?.variants || [];
    if (!variants.length) return { ...p, state: 'UNKNOWN', detail: 'no variants in JSON' };
    const anyAvail = variants.some(v => v.available === true);
    if (!anyAvail) return { ...p, state: 'UNBUYABLE', detail: `${variants.length} variants, none available` };
    return { ...p, state: 'OK', detail: `${variants.filter(v=>v.available).length}/${variants.length} variants buyable` };
  } catch (e) { return { ...p, state: 'UNKNOWN', detail: `fetch error: ${String(e.message).slice(0,40)}` }; }
}

(async () => {
  const results = [];
  for (const p of rows) { results.push(await check(p)); await sleep(150); }
  const byState = results.reduce((m,r)=>(m[r.state]=(m[r.state]||0)+1,m),{});
  const drift = results.filter(r => r.state === 'MISSING' || r.state === 'UNBUYABLE');
  const byVendor = {};
  for (const r of results) { (byVendor[r.vendor] ||= { total:0, drift:0 }); byVendor[r.vendor].total++; if (r.state==='MISSING'||r.state==='UNBUYABLE') byVendor[r.vendor].drift++; }

  const report = { generated_at: new Date().toISOString(), sampled: results.length, per_vendor_sample: PER, by_state: byState,
    drift_count: drift.length, by_vendor: byVendor, drift, ok_sample: results.filter(r=>r.state==='OK').slice(0,3) };
  fs.writeFileSync(OUT, JSON.stringify(report, null, 2));

  let md = `# Availability-drift canary — ${new Date().toISOString().slice(0,16)}\n\n`;
  md += `Sampled **${results.length}** PDPs (${PER}/vendor × ${VENDORS.length} vendors), storefront variant availability vs mirror ACTIVE. **READ-ONLY, $0.**\n\n`;
  md += `State: ${Object.entries(byState).map(([k,v])=>`${k}=${v}`).join(' · ')}\n\n`;
  if (!drift.length) md += `🟢 **No availability drift in the sample** — every sampled ACTIVE product is live and purchasable on the storefront.\n`;
  else {
    md += `🟠 **${drift.length} drift item(s)** (mirror ACTIVE but shopper can't buy):\n\n| Vendor | SKU | Handle | State | Detail |\n|---|---|---|---|---|\n`;
    for (const d of drift) md += `| ${d.vendor} | ${d.sku} | ${d.handle} | ${d.state} | ${d.detail} |\n`;
  }
  md += `\n## Per-vendor\n| Vendor | Sampled | Drift |\n|---|---:|---:|\n`;
  for (const [v,s] of Object.entries(byVendor)) md += `| ${v} | ${s.total} | ${s.drift} |\n`;
  md += `\n_Sample-based canary (${PER}/vendor); scale --per for fuller coverage. Drift = MISSING (404) or UNBUYABLE (no available variant). UNKNOWN = transient fetch, not drift._\n`;
  fs.writeFileSync(MD, md);

  console.log(`[availability-drift] sampled ${results.length} · ${Object.entries(byState).map(([k,v])=>`${k}=${v}`).join(' ')} · drift=${drift.length}`);
  for (const d of drift) console.log(`  ⚠️  ${d.vendor} ${d.sku} (${d.handle}): ${d.state} — ${d.detail}`);
  console.log(`Report: ${MD}`);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });