`rolls-ar-deeplink/audit-pathA-pg.js`

67 lines
#!/usr/bin/env node
// READ-ONLY Path A audit (PG side only — no Shopify, no writes). Reuses the backfill's
// OWN toInches normalizer + supply logic, but instead of first-writer-wins it DETECTS
// cross-vendor SKU collisions + per-vendor value distribution + out-of-range (unit errors).
'use strict';
const { execFileSync } = require('child_process');
function psql(sql) { return execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAF\t', '-X', '-c', sql], { encoding: 'utf8', maxBuffer: 1 << 28 }); }
function toInches(v) { if (v == null) return null; const m = String(v).match(/[0-9]+(\.[0-9]+)?/); if (!m) return null; const n = parseFloat(m[0]); return Number.isFinite(n) && n > 0 ? Math.round(n * 100) / 100 : null; }
const median = (a) => { if (!a.length) return null; const s = [...a].sort((x, y) => x - y); const m = s.length >> 1; return s.length % 2 ? s[m] : Math.round((s[m - 1] + s[m]) / 2 * 100) / 100; };

const tables = psql("select table_name from information_schema.columns where table_schema='public' and column_name='repeat_v' and table_name like '%_catalog' and table_name not like '%_bak%' and table_name not like '_bak%'").trim().split('\n').filter(Boolean).map(s => s.trim());

const perVendor = [];
const keyMap = new Map(); // upper(sku) -> [{vendor, val}]
let totalRows = 0, totalNumeric = 0;

for (const t of tables) {
  const cols = psql(`select column_name from information_schema.columns where table_schema='public' and table_name='${t}'`).trim().split('\n').map(s => s.trim());
  const skuCol = ['mfr_sku', 'manufacturer_sku', 'sku', 'pattern_number', 'product_code', 'style_number'].find(c => cols.includes(c));
  if (!skuCol) { perVendor.push({ t, skuCol: null, rows: 0, numeric: 0, note: 'NO sku column — contributes nothing' }); continue; }
  let rows = [];
  try { rows = psql(`select ${skuCol}::text, repeat_v::text from ${t} where repeat_v is not null`).trim().split('\n').filter(Boolean); } catch { perVendor.push({ t, skuCol, rows: 0, numeric: 0, note: 'query failed' }); continue; }
  const vals = []; let oor = 0;
  for (const line of rows) {
    const [sku, rv] = line.split('\t');
    const n = toInches(rv);
    if (!sku || n == null) continue;
    vals.push(n);
    if (n > 36) oor++;                    // >36in = likely a cm value stored as-is / unit error
    const key = sku.trim().toUpperCase();
    if (!keyMap.has(key)) keyMap.set(key, []);
    keyMap.get(key).push({ vendor: t, val: n });
  }
  totalRows += rows.length; totalNumeric += vals.length;
  perVendor.push({ t, skuCol, rows: rows.length, numeric: vals.length,
    min: vals.length ? Math.min(...vals) : null, med: median(vals), max: vals.length ? Math.max(...vals) : null,
    out_of_range_gt36: oor });
}

// cross-vendor collisions: same key in >=2 DISTINCT vendors
let collisionKeys = 0, divergentKeys = 0; const divergentExamples = [];
for (const [key, arr] of keyMap) {
  const vendors = new Set(arr.map(a => a.vendor));
  if (vendors.size < 2) continue;
  collisionKeys++;
  const distinctVals = new Set(arr.map(a => a.val));
  if (distinctVals.size > 1) {
    divergentKeys++;
    if (divergentExamples.length < 15) divergentExamples.push({ key, spread: [...arr.map(a => `${a.vendor.replace('_catalog','')}=${a.val}"`)] });
  }
}

const worstOOR = perVendor.filter(v => v.out_of_range_gt36 > 0).sort((a, b) => b.out_of_range_gt36 - a.out_of_range_gt36).slice(0, 12);
const noSku = perVendor.filter(v => v.skuCol === null).map(v => v.t);

console.log(JSON.stringify({
  catalogs: tables.length,
  supply_rows_with_repeat: totalRows,
  supply_numeric_parseable: totalNumeric,
  distinct_supply_keys: keyMap.size,
  cross_vendor_collision_keys: collisionKeys,
  DIVERGENT_collision_keys: divergentKeys,   // ← the hazard: first-writer-wins mis-prefills the losers
  divergent_examples: divergentExamples,
  vendors_no_sku_col: noSku,
  worst_out_of_range_vendors: worstOOR.map(v => ({ vendor: v.t, gt36: v.out_of_range_gt36, of_numeric: v.numeric, max: v.max })),
}, null, 2));