`scripts/build-queue.mjs`

170 lines
#!/usr/bin/env node
// build-queue.mjs — READ-ONLY loader for the broken "DW# == Mfr SKU" corruption.
// Runs the corruption-signature query against dw_unified.shopify_products,
// classifies each row, prefers the other agent's richer output when present,
// and writes data/queue.jsonl. NEVER writes to dw_unified / FileMaker / Shopify.

import { execFileSync } from 'node:child_process';
import { readFileSync, writeFileSync, existsSync, mkdirSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..');
const DATA_DIR = join(ROOT, 'data');
const OUT = join(DATA_DIR, 'queue.jsonl');
const PSQL = '/opt/homebrew/opt/postgresql@14/bin/psql';
const DB = 'dw_unified';
// The other agent's richer output — consumed as source-of-truth when present.
const PEER_FILE = process.env.PEER_FILE ||
  '/Users/macstudio3/Projects/filemaker-mcp/data/mfr-review-queue.jsonl';

if (!existsSync(DATA_DIR)) mkdirSync(DATA_DIR, { recursive: true });

// Does shopify_products have a usable created/updated timestamp column?
function detectDateCol() {
  const cols = execFileSync(PSQL, [DB, '-Atc',
    `SELECT column_name FROM information_schema.columns
     WHERE table_name='shopify_products'
       AND column_name IN ('created_at','created_at_shopify','updated_at','updated_at_shopify','synced_at')`
  ], { encoding: 'utf8' }).trim().split('\n').filter(Boolean);
  // Prefer a real creation timestamp, then updated, then synced.
  for (const c of ['created_at','created_at_shopify','updated_at','updated_at_shopify','synced_at']) {
    if (cols.includes(c)) return c;
  }
  return null;
}

const dateCol = detectDateCol();
console.log(`[build-queue] date column: ${dateCol || 'none'}`);

// Corruption signature: mfr_sku <> '' AND mfr_sku = numeric tail of dw_sku.
// We pull the raw fields + the derived pattern token so classification runs in JS.
//
// SCOPE (Steve 2026-08-27, "only look at active patterns"): DEFAULT to status='ACTIVE'
// only — the ~18k DELETED_FROM_SHOPIFY/ARCHIVED ghosts (and ALL Schumacher rows, which
// are already all deleted/archived) are noise for review. ACTIVE-only = ~2,432 rows.
// Set STATUS_FILTER=all to see every status again.
const STATUS_FILTER = (process.env.STATUS_FILTER || 'ACTIVE').trim().toUpperCase();
const STATUS_CLAUSE = STATUS_FILTER === 'ALL' ? '' : `status='${STATUS_FILTER.replace(/'/g, "''")}'\n  AND `;
console.log(`[build-queue] status filter: ${STATUS_FILTER}`);
const SIG = `${STATUS_CLAUSE}mfr_sku IS NOT NULL AND mfr_sku <> ''
  AND mfr_sku = (regexp_match(dw_sku,'(\\d+)(-SAMPLE)?$','i'))[1]`;

const selDate = dateCol ? `to_char(${dateCol} AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"')` : `NULL`;

// Use a control char (\t) delimiter; fields are simple text so this is safe.
const SQL = `
COPY (
  SELECT
    dw_sku,
    mfr_sku,
    COALESCE(vendor,''),
    COALESCE(pattern_name,''),
    ${selDate},
    COALESCE((regexp_match(dw_sku, '^[A-Za-z0-9]+?-(.+)$'))[1], '')
  FROM shopify_products
  WHERE ${SIG}
  ORDER BY vendor, dw_sku
) TO STDOUT WITH (FORMAT text, DELIMITER E'\\t', NULL '');
`;

console.log('[build-queue] querying dw_unified (read-only)...');
const raw = execFileSync(PSQL, [DB, '-v', 'ON_ERROR_STOP=1', '-Atc', SQL], {
  encoding: 'utf8', maxBuffer: 1024 * 1024 * 256,
});

// Load peer (other agent) output keyed by dw_sku, if it exists.
const peer = new Map();
if (existsSync(PEER_FILE)) {
  try {
    for (const line of readFileSync(PEER_FILE, 'utf8').split('\n')) {
      if (!line.trim()) continue;
      const o = JSON.parse(line);
      const k = o.dw_sku || o.dwSku || o.sku;
      if (k) peer.set(k, o);
    }
    console.log(`[build-queue] loaded ${peer.size} peer rows from ${PEER_FILE}`);
  } catch (e) {
    console.warn(`[build-queue] peer file unreadable, ignoring: ${e.message}`);
  }
} else {
  console.log(`[build-queue] no peer file at ${PEER_FILE} (using computed guesses)`);
}

function classify(dw_sku, bad_mfr, vendor, patternToken) {
  const token = (patternToken || '').replace(/-SAMPLE$/i, '');

  // LIKELY-LEGIT: Schumacher family — DW# IS their real mfr.
  if (/schumacher/i.test(vendor)) {
    return { best_guess_real_mfr: bad_mfr, source: 'likely-legit-numeric',
             confidence: 'exclude', tier: 'LIKELY-LEGIT' };
  }

  // TIER-4: corrupt SKU — no proper alpha series prefix, or a huge bare number.
  const noAlphaPrefix = !/^[A-Za-z]{2,}-/.test(dw_sku);
  const hugeNumber = /^[0-9]{13,}$/.test(dw_sku);
  const leadingDash = /^-/.test(dw_sku);
  if (leadingDash || hugeNumber || noAlphaPrefix) {
    return { best_guess_real_mfr: '', source: 'bad-sku',
             confidence: 'n/a', tier: 'TIER-4' };
  }

  // TIER-2: alpha-prefix recovery. Token is ALPHA+digits, and stripping the
  // leading alpha equals bad_mfr → the token IS the real mfr code.
  if (/^[A-Za-z]+[0-9]+$/.test(token)) {
    const digitTail = token.replace(/^[A-Za-z]+/, '');
    if (digitTail === bad_mfr) {
      return { best_guess_real_mfr: token, source: 'dwsku-alpha-prefix',
               confidence: 'high', tier: 'TIER-2' };
    }
  }

  // TIER-3: real mfr not recoverable from dw_unified — needs review queue.
  return { best_guess_real_mfr: '', source: 'none',
           confidence: 'needs-review', tier: 'TIER-3' };
}

const lines = raw.split('\n').filter((l) => l.length);
const out = [];
let peerUsed = 0;
for (const line of lines) {
  const [dw_sku, bad_mfr, vendor, pattern_name, created_at, patternToken] =
    line.split('\t');
  const c = classify(dw_sku, bad_mfr, vendor, patternToken);

  const rec = {
    dw_sku,
    bad_mfr,
    vendor: vendor || '',
    pattern_name: pattern_name || '',
    created_at: created_at || null,
    best_guess_real_mfr: c.best_guess_real_mfr,
    source: c.source,
    confidence: c.confidence,
    tier: c.tier,
  };

  // Prefer the peer's richer best_guess/source when it covers this dw_sku.
  const p = peer.get(dw_sku);
  if (p && (p.best_guess_real_mfr || p.best_guess || p.real_mfr)) {
    rec.best_guess_real_mfr = p.best_guess_real_mfr || p.best_guess || p.real_mfr;
    rec.source = p.source || rec.source;
    if (p.confidence) rec.confidence = p.confidence;
    if (p.tier) rec.tier = p.tier;
    rec.peer = true;
    peerUsed++;
  }

  out.push(JSON.stringify(rec));
}

writeFileSync(OUT, out.join('\n') + (out.length ? '\n' : ''));

// Per-tier tally for the report.
const tally = {};
for (const l of out) { const t = JSON.parse(l).tier; tally[t] = (tally[t]||0)+1; }
console.log(`[build-queue] wrote ${out.length} rows -> ${OUT}`);
console.log(`[build-queue] peer overrides applied: ${peerUsed}`);
console.log(`[build-queue] tiers:`, JSON.stringify(tally));