`scripts/mfr-review-queue.mjs`

175 lines
// TK-10906 — READ-ONLY mfr review-queue generator.
//
// For a given vendor list, emit a per-item review queue of the BROKEN (DW#==mfr) rows with a
// BEST-GUESS real mfr + its source + a confidence, for STAFF to confirm before any write.
// The actual mfr repair is Steve-gated and runs SEPARATELY from this queue after he confirms
// the vendor set. This tool makes ZERO writes to dw_unified, FileMaker, or Shopify — it only
// SELECTs from the local dw_unified mirror and (optionally) does READ-ONLY FileMaker finds.
//
//   node scripts/mfr-review-queue.mjs                 # default Tier-3 vendor set, ~50 rows
//   node scripts/mfr-review-queue.mjs --limit 50      # cap total rows
//   node scripts/mfr-review-queue.mjs --no-fm         # skip the FileMaker-note lookups (pure PG)
//   node scripts/mfr-review-queue.mjs --vendors "Glitter Walls,Wolf Gordon"
//
// Output: data/mfr-review-queue.jsonl  (one JSON object per broken SKU)
//   { dw_sku, bad_mfr, vendor, pattern, best_guess_real_mfr, source, confidence }
//   source ∈ { dwsku-alpha-prefix, filemaker-note, vendor-feed, none }
import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync, readFileSync, existsSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
const OUT = join(ROOT, 'data', 'mfr-review-queue.jsonl');

// ---- args -------------------------------------------------------------------------------
const args = process.argv.slice(2);
const flag = (n) => args.includes(n);
const opt = (n, d) => { const i = args.indexOf(n); return i >= 0 && args[i + 1] ? args[i + 1] : d; };
const LIMIT = parseInt(opt('--limit', '50'), 10);
const USE_FM = !flag('--no-fm');

// DEFAULT Tier-3 vendor set — EXCLUDING the Schumacher family (DW# IS their real mfr) and the
// pure-numeric-legit vendors. These are confirmed to carry the DW#==mfr signature.
const DEFAULT_VENDORS = [
  'Glitter Walls', 'Hollywood Wallcoverings', 'LA Walls', 'DW Bespoke Studio',
  'Jeffrey Stevens', 'Graham & Brown', 'Ralph Lauren', 'Anna French',
  'Wolf Gordon', 'Missoni', 'IKSEL', 'Versace',
];
const VENDORS = opt('--vendors', '') ? opt('--vendors', '').split(',').map((v) => v.trim()).filter(Boolean) : DEFAULT_VENDORS;

// ---- READ-ONLY psql helpers -------------------------------------------------------------
const sqlEsc = (v) => String(v ?? '').replace(/'/g, "''");
function sqlRows(q) {
  // -F $'\t' tab-separated, -A unaligned, -t tuples-only. Purely a SELECT — read-only.
  try {
    const out = execFileSync(PSQL, ['dw_unified', '-tAF', '\t', '-c', q], { encoding: 'utf8' });
    return out.split('\n').filter(Boolean).map((l) => l.split('\t'));
  } catch (e) { console.error('psql read failed:', e.message); return []; }
}

// ---- guard helpers (same semantics as lib/wallpaper.js — pure, no writes) ----------------
const dwTail = (dwSku) => (String(dwSku || '').match(/(\d+)(?:[-_ ]?sample)?$/i) || [])[1] || '';
// LEGIT-NUMERIC allowlist — DW# IS the real mfr for these (Schumacher DWSW). NEVER queue them
// for "repair": their DW#==mfr rows are correct, not corrupt. Belt-and-suspenders even if a
// user explicitly passes --vendors "Schumacher" (the guard in lib/wallpaper.js already
// protects the live importer; this keeps the queue from proposing a wrong repair).
const LEGIT_NUMERIC_MFR_PREFIXES = ['DWSW', 'SCH']; // Schumacher family — DW# IS the real mfr
const isLegitNumericLine = (dwSku, vendor) => {
  const up = String(dwSku || '').toUpperCase();
  if (/schumacher/i.test(String(vendor || ''))) return true;               // any Schumacher vendor variant
  return LEGIT_NUMERIC_MFR_PREFIXES.some((pre) => up === pre || up.startsWith(pre + '-') || new RegExp(`^${pre}\\d`).test(up));
};
function recoverAlphaMfr(dwSku, badMfr) {
  const bad = String(badMfr || '').trim();
  if (!bad || !/^\d+$/.test(bad)) return '';
  const seg = String(dwSku || '').replace(/[-_ ]?sample$/i, '').split(/[-_ ]/).pop() || '';
  const m = seg.match(/^([A-Za-z]+)(\d+)$/);
  if (m && m[2] === bad) return (m[1] + m[2]).toUpperCase();
  return '';
}

// ---- optional READ-ONLY FileMaker note lookup -------------------------------------------
// Some real codes live only on the FileMaker WALLPAPER master (the "Mfr #<code>" note /
// Mfr Pattern field) and never made it back into dw_unified. We do a READ-ONLY find for the
// master and read its Mfr Pattern / MetDataSearchWord — but ONLY report it as a guess when
// it differs from the DW# placeholder. NEVER writes.
let fm = null;
if (USE_FM) {
  try {
    // load .env for FM creds (same pattern as the repo's other scripts)
    if (existsSync(join(ROOT, '.env'))) {
      for (const l of readFileSync(join(ROOT, '.env'), 'utf8').split('\n')) {
        const m = l.match(/^([A-Z0-9_]+)=(.*)$/); if (m && !process.env[m[1]]) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
      }
    }
    fm = await import('../src/fm-client.js');
    await fm.ping();
  } catch (e) {
    console.error(`FileMaker unavailable (${e.message.split('\n')[0]}) — continuing with PG-only sources.`);
    fm = null;
  }
}

const FULL = '*List Wallpapers - Full View';
const normSku = (s) => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
async function fmNoteMfr(dwSku) {
  if (!fm) return '';
  // Split dw_sku into Series + pattern-segment (best-effort) and find the master READ-ONLY.
  const clean = String(dwSku).replace(/[-_ ]?sample$/i, '');
  const parts = clean.split(/[-_ ]/);
  const series = parts.length > 1 ? parts[0] : '';
  const pattern = parts.length > 1 ? parts.slice(1).join('-') : clean;
  const q = series ? [{ Series: '==' + series, 'JS Pattern': '==' + pattern }] : [{ 'JS Pattern': '==' + pattern }];
  try {
    const r = await fm.findRecords('WALLPAPER', FULL, q, { limit: 3 });
    for (const rec of (r.records || [])) {
      const fd = rec.fieldData || {};
      // confirm same SKU
      if (normSku(String(fd.Series || '') + String(fd['JS Pattern'] || '')) !== normSku(clean)) continue;
      const real = String(fd['Mfr Pattern'] || fd['Detail 1 Mfr Number'] || fd.MetDataSearchWord || '').trim();
      // Only a guess if it's a REAL code that differs from the DW# numeric tail.
      if (real && real !== dwTail(dwSku)) return real;
    }
  } catch { /* no-match / auth blip -> no guess */ }
  return '';
}

// ---- gather the broken rows per vendor --------------------------------------------------
// Broken (DW#==mfr) = mfr_sku equals the DW SKU's numeric tail. Read-only SELECT.
mkdirSync(join(ROOT, 'data'), { recursive: true });
const rows = [];
const perVendor = {};
// Distribute the row budget across vendors so the sample spans the whole set (round up),
// rather than exhausting the cap on the first vendor.
const perVendorCap = Math.max(1, Math.ceil(LIMIT / VENDORS.length));
for (const vendor of VENDORS) {
  if (rows.length >= LIMIT) break;
  const cap = Math.min(perVendorCap, LIMIT - rows.length);
  const q = `SELECT dw_sku, mfr_sku, vendor, COALESCE(pattern_name,'') FROM shopify_products
    WHERE vendor ILIKE '%${sqlEsc(vendor)}%'
      AND mfr_sku IS NOT NULL AND mfr_sku <> ''
      AND mfr_sku = (regexp_match(dw_sku, '([0-9]+)(-[Ss]ample)?$'))[1]
    ORDER BY dw_sku
    LIMIT ${cap}`;
  const found = sqlRows(q);
  perVendor[vendor] = { total: found.length, alpha: 0, fmnote: 0, none: 0 };
  for (const [dw_sku, bad_mfr, vend, pattern] of found) {
    if (rows.length >= LIMIT) break;
    // Skip legit-numeric lines (Schumacher family): DW#==mfr is correct there, not corrupt.
    if (isLegitNumericLine(dw_sku, vend || vendor)) { perVendor[vendor].total--; continue; }
    // Source 1 (Tier-2, HIGH conf): dw_sku alpha-prefix recovery.
    let best = recoverAlphaMfr(dw_sku, bad_mfr), source = best ? 'dwsku-alpha-prefix' : '', confidence = best ? 'high' : '';
    // Source 2 (MEDIUM conf): READ-ONLY FileMaker Mfr# note on the master.
    if (!best) {
      const note = await fmNoteMfr(dw_sku);
      if (note) { best = note; source = 'filemaker-note'; confidence = 'medium'; }
    }
    // else: none — needs a vendor re-scrape.
    if (!best) { source = 'none'; confidence = 'none'; }
    perVendor[vend || vendor] = perVendor[vendor];
    perVendor[vendor][source === 'dwsku-alpha-prefix' ? 'alpha' : source === 'filemaker-note' ? 'fmnote' : 'none']++;
    rows.push({ dw_sku, bad_mfr, vendor: vend || vendor, pattern, best_guess_real_mfr: best || null, source, confidence });
  }
}

// ---- write the queue (the ONLY write this tool makes — to its own data file) -------------
writeFileSync(OUT, rows.map((r) => JSON.stringify(r)).join('\n') + (rows.length ? '\n' : ''), 'utf8');

// ---- report -----------------------------------------------------------------------------
console.log(`mfr-review-queue: ${rows.length} broken rows across ${VENDORS.length} vendors -> ${OUT}`);
console.log(`FileMaker note lookups: ${fm ? 'ON' : 'OFF (PG-only)'}\n`);
console.log('per-vendor quality:');
for (const v of VENDORS) {
  const s = perVendor[v]; if (!s) continue;
  console.log(`  ${v.padEnd(24)}  rows=${String(s.total).padStart(3)}  alpha=${s.alpha}  fm-note=${s.fmnote}  none=${s.none}`);
}
const bySource = rows.reduce((a, r) => { a[r.source] = (a[r.source] || 0) + 1; return a; }, {});
console.log('\nsample (first 12):');
for (const r of rows.slice(0, 12)) {
  console.log(`  ${r.dw_sku.padEnd(18)} bad=${String(r.bad_mfr).padEnd(8)} -> ${String(r.best_guess_real_mfr || '(none)').padEnd(12)} [${r.source}/${r.confidence}] "${r.pattern.slice(0, 30)}"`);
}
console.log('\nby source:', JSON.stringify(bySource));
console.log('\nZERO writes to dw_unified / FileMaker / Shopify. Confirm the vendor set + guesses, then run the (Steve-gated) repair separately.');