← back to Mfr Review Viewer Corruption
lib/candidates.mjs
305 lines
// candidates.mjs — READ-ONLY per-SKU candidate resolver for the mfr-review-viewer.
//
// For ONE dw_sku, returns EVERY matching record in:
// • FileMaker (FmPro) db 'WALLPAPER', layout '*List Wallpapers - Full View'
// • the unified dw_unified Postgres DB (shopify_products, vendor_catalog, dw_sku_registry)
// so a reviewer can SEE the duplicate/wrong masters and pick the correct real mfr code.
//
// PURELY READ-ONLY: FileMaker _find calls + psql SELECTs only. Never writes anywhere.
// Imports findRecords from the filemaker-mcp project (does NOT modify that project),
// loading its .env for the FM Cloud creds. FM auth/timeout failures degrade gracefully
// to { filemaker:[], unified:[...], fmError:'...' } so the unified side still renders.
//
// SKU-match logic mirrors filemaker-mcp/scripts/fuzzy-sku-check.mjs + lib/wallpaper.js
// (parseCombo / splitCandidates / findExistingMaster).
import { readFileSync, existsSync } from 'node:fs';
import { execFile } from 'node:child_process';
import path from 'node:path';
import os from 'node:os';
const FM_PROJECT = path.join(os.homedir(), 'Projects', 'filemaker-mcp');
const FM_ENV = path.join(FM_PROJECT, '.env');
const FM_CLIENT = path.join(FM_PROJECT, 'src', 'fm-client.js');
const FM_DB = 'WALLPAPER';
const FM_LAYOUT = '*List Wallpapers - Full View';
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
// ---- load the filemaker-mcp .env (read-only) so FM Cloud creds are present ----
function loadFmEnv() {
if (!existsSync(FM_ENV)) return;
for (const line of readFileSync(FM_ENV, 'utf8').split('\n')) {
const m = line.match(/^([A-Z_]+)=(.*)$/);
if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
}
}
loadFmEnv();
// lazy-import findRecords (ES module) once, cached
let _findRecords = null;
async function getFindRecords() {
if (_findRecords) return _findRecords;
const mod = await import('file://' + FM_CLIENT);
_findRecords = mod.findRecords;
return _findRecords;
}
// ---------------- SKU normalization (mirrors wallpaper.js) ----------------
const normSku = (s) => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
// Strip a trailing -Sample / unit suffix, collapse whitespace.
function cleanSku(raw) {
return String(raw || '').trim().replace(/[-_ ]?sample$/i, '');
}
// Enumerate every {Series, JS Pattern} split at each separator boundary.
function splitCandidates(dashForm) {
const s = String(dashForm || '').trim();
const out = [];
for (let i = 0; i < s.length; i++) {
if (/[-_ ]/.test(s[i])) {
const series = s.slice(0, i);
const pattern = s.slice(i + 1);
if (series && pattern) out.push([series, pattern]);
}
}
return out;
}
// The numeric tail of a SKU (the "pattern number") — e.g. HSW-51526 -> 51526.
function numericTail(clean) {
const m = String(clean || '').match(/(\d[\d]*)\s*$/);
return m ? m[1] : '';
}
// Parse the real mfr code out of the Mfr Pattern / note text.
// "#gz127 - $44.10 Net - 1/14" -> "gz127"; "AMW10042-6; Library-Leather" -> "AMW10042-6";
// "15712" -> "15712". Token after an optional leading '#', up to the first space / ' - ' / ';'.
function parseNoteMfr(note) {
let t = String(note || '').trim();
if (!t) return '';
// take first line only
t = t.split(/[\r\n]/)[0].trim();
// if there's an embedded "#code" anywhere, prefer that token
const hash = t.match(/#\s*([^\s;,-][^\s;,]*)/);
if (hash) return hash[1].replace(/[.,]$/, '').trim();
// otherwise the leading token before a separator that starts a cost/date note
// "AMW10042-6; Library-Leather" -> AMW10042-6 (stop at ';')
// "p622_17 - $89 Net" -> p622_17 (stop at ' - ')
let m = t.match(/^([^\s;]+?)(?:\s*;|\s+-\s+|\s+\$|\s+net\b|$)/i);
if (m) return m[1].replace(/[.,]$/, '').trim();
return t;
}
// FileMaker codes meaning "no match / field not on layout" (skip) vs a real error.
const FM_SKIP = new Set(['401', '102', '105', '106']);
// ---------------- FileMaker candidates ----------------
async function fmCandidates(dwSku) {
let findRecords;
try {
findRecords = await getFindRecords();
} catch (e) {
return { filemaker: [], fmError: 'fm-client load failed: ' + e.message };
}
const clean = cleanSku(dwSku);
const dashless = clean.replace(/[-_ ]/g, '');
const dashed = clean.includes('-') ? clean : (dashless.match(/^([A-Za-z]+)(\d.*)$/) ? dashless.replace(/^([A-Za-z]+)(\d.*)$/, '$1-$2') : clean);
const tail = numericTail(clean);
// OR-array of every field a SKU can live in + every component split.
const query = [];
const pushEq = (field, val) => { if (val) query.push({ [field]: '==' + val }); };
pushEq('combo sku', dashless);
pushEq('comboskuwithdash', dashed);
pushEq('mfr pattern number', tail || clean);
pushEq('Mfr Pattern', tail || clean);
// component splits (Series==X, JS Pattern==Y) over both dashed + raw forms
const seenSplit = new Set();
for (const src of [dashed, clean, dashless]) {
for (const [series, pattern] of splitCandidates(src)) {
const k = series.toUpperCase() + '|' + pattern.toUpperCase();
if (seenSplit.has(k)) continue;
seenSplit.add(k);
query.push({ Series: '==' + series, 'JS Pattern': '==' + pattern });
}
}
if (!query.length) return { filemaker: [], fmError: null };
let records = [];
try {
const r = await findRecords(FM_DB, FM_LAYOUT, query, { limit: 50 });
records = r.records || [];
} catch (e) {
const code = String(e.fmCode || '');
if (FM_SKIP.has(code)) return { filemaker: [], fmError: null }; // genuinely no match
return { filemaker: [], fmError: `FileMaker ${code || ''} ${e.message}`.trim() };
}
// Dedupe by recordId; project to the fields the UI needs.
const targetKey = normSku(clean);
const byId = new Map();
for (const rec of records) {
const fd = rec.fieldData || {};
// Accept the record if its Series+JS Pattern OR its combo sku normalizes to the target,
// OR it matched via an mfr/tail probe (keep it — reviewer decides). We include all
// returned records so the reviewer can SEE duplicates; but flag the confident matches.
const storedKey = normSku(String(fd.Series || '') + String(fd['JS Pattern'] || ''));
const comboKey = normSku(fd['combo sku']);
const skuMatch = storedKey === targetKey || comboKey === targetKey;
const mfrPattern = String(fd['Mfr Pattern'] || '').trim();
// The "note" that holds the real code: Mfr Pattern is the primary carrier (it holds
// "#gz127" for HSW-51526). We ALSO surface the sample-chase memo field if it carries a
// "#code" (fallback). Both are read-only.
const chaseMemo = String(fd['Vendor Sample - Where is Memo Send 2nd day'] || '').trim();
const mfrNote = mfrPattern || (/#/.test(chaseMemo) ? chaseMemo : '');
const noteMfr = parseNoteMfr(mfrNote) || parseNoteMfr(chaseMemo);
// ---- sample-order history (Steve's rule: a "master" = a record where a client
// sample was NEVER ordered). Both fields live on the Full View layout. Non-empty
// in EITHER = a client sample was ordered against this record -> a KEEP record. ----
const sampleReq = String(fd['today for client'] || '').trim();
const sampleSent = String(fd['Date WP Sample Sent'] || '').trim();
const sampleOrdered = !!(sampleReq || sampleSent);
const sampleDate = sampleSent || sampleReq || '';
// ---- placeholder mfr = the parsed code is purely the DW# numeric tail (no real
// alpha code) i.e. the "DW# == Mfr SKU" corruption, not a real manufacturer code. ----
const codeHasAlpha = /[A-Za-z]/.test(noteMfr || '');
const codeDigits = String(noteMfr || mfrPattern || '').replace(/[^0-9]/g, '');
const mfrPlaceholder = !codeHasAlpha && !!tail && codeDigits === tail;
const cand = {
recordId: rec.recordId,
comboSku: String(fd['combo sku'] || '').trim(),
comboSkuWithDash: String(fd['comboskuwithdash'] || fd['seriesdashnumber'] || '').trim(),
series: String(fd.Series || '').trim(),
jsPattern: String(fd['JS Pattern'] || '').trim(),
mfrPattern, // STRUCTURED mfr (may be the real code or a DW#)
name: String(fd['Name of Pattern'] || '').trim(),
color: String(fd['Color of Pattern'] || '').trim(),
vid: String(fd.vid || '').trim(),
supplier: String(fd.Supplier || '').trim(),
width: String(fd.Width || '').trim(),
cost: String(fd.Cost || fd['Updated Vendor Cost'] || fd['DW New Net'] || '').trim(),
mfrNote, // free-text carrier of the real code
noteMfr, // best-guess real mfr parsed from the note
skuMatch, // true = Series/combo normalizes to this dw_sku
sampleOrdered, // true = a client sample WAS ordered (a KEEP record)
sampleDate, // the sample sent/request date for display
mfrPlaceholder, // true = mfr is just the DW# placeholder (no real code)
};
byId.set(rec.recordId, cand);
}
// Confident sku-matches first, then the rest.
const out = [...byId.values()].sort((a, b) => (b.skuMatch - a.skuMatch));
return { filemaker: out, fmError: null };
}
// ---------------- unified DB candidates ----------------
function psqlJson(sql) {
return new Promise((resolve) => {
execFile(PSQL, ['dw_unified', '-tAc', sql], { maxBuffer: 1024 * 1024 * 64 }, (err, stdout) => {
if (err) return resolve(null);
const txt = (stdout || '').trim();
if (!txt) return resolve([]);
try { return resolve(JSON.parse(txt)); } catch { return resolve([]); }
});
});
}
async function unifiedCandidates(dwSku) {
const clean = cleanSku(dwSku);
const norm = normSku(clean); // HSW51526
const tail = numericTail(clean); // 51526
const esc = (s) => String(s).replace(/'/g, "''");
const nq = `'${esc(norm)}'`;
const tailPred = tail
? `OR regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g') = '${esc(tail)}'
OR regexp_replace(coalesce(dw_sku,''),'[^0-9]','','g') = '${esc(tail)}'`
: '';
// shopify_products
const shopSql = `SELECT coalesce(json_agg(row_to_json(t)),'[]') FROM (
SELECT 'shopify_products' AS source_table, dw_sku, sku, variant_sku, mfr_sku, vendor,
supplier_name, pattern_name, status,
metafields->'custom'->'manufacturer_sku'->>'value' AS manufacturer_sku
FROM shopify_products
WHERE upper(regexp_replace(coalesce(dw_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
OR upper(regexp_replace(coalesce(sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
OR upper(regexp_replace(coalesce(variant_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
${tail ? `OR regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g') = '${esc(tail)}'` : ''}
LIMIT 50 ) t`;
// vendor_catalog (vendor_code carries the vendor; no supplier_name/vendor cols)
const vcSql = `SELECT coalesce(json_agg(row_to_json(t)),'[]') FROM (
SELECT 'vendor_catalog' AS source_table, dw_sku, NULL::text AS sku, NULL::text AS variant_sku,
mfr_sku, vendor_code AS vendor, NULL::text AS supplier_name, pattern_name,
NULL::text AS status, NULL::text AS manufacturer_sku
FROM vendor_catalog
WHERE upper(regexp_replace(coalesce(dw_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
OR upper(regexp_replace(coalesce(mfr_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
${tail ? `OR regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g') = '${esc(tail)}'` : ''}
LIMIT 50 ) t`;
// dw_sku_registry
const regSql = `SELECT coalesce(json_agg(row_to_json(t)),'[]') FROM (
SELECT 'dw_sku_registry' AS source_table, dw_sku, NULL::text AS sku, NULL::text AS variant_sku,
mfr_sku, vendor_name AS vendor, NULL::text AS supplier_name, NULL::text AS pattern_name,
status, NULL::text AS manufacturer_sku
FROM dw_sku_registry
WHERE upper(regexp_replace(coalesce(dw_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
OR upper(regexp_replace(coalesce(mfr_sku,''),'[^A-Za-z0-9]','','g')) = ${nq}
${tail ? `OR regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g') = '${esc(tail)}'` : ''}
LIMIT 50 ) t`;
const [shop, vc, reg] = await Promise.all([psqlJson(shopSql), psqlJson(vcSql), psqlJson(regSql)]);
return [...(shop || []), ...(vc || []), ...(reg || [])];
}
// ---------------- public entry ----------------
export async function candidatesForSku(dwSku) {
const [fm, unified] = await Promise.all([
fmCandidates(dwSku).catch((e) => ({ filemaker: [], fmError: e.message })),
unifiedCandidates(dwSku).catch(() => []),
]);
const recs = fm.filemaker || [];
// ---- apply Steve's rule (2026-08-27): DELETE the broken no-sample "masters",
// KEEP the real sample-order records.
//
// SKU-SCOPING GUARD (2026-08-27, critical): classify ONLY records that ACTUALLY
// belong to this SKU (skuMatch === true — Series+JS Pattern normalizes to this
// dw_sku). The candidate OR-find deliberately over-fetches (numeric-tail probes),
// so foreign records that merely share a number leak in (e.g. Schumacher SCH|51526
// under HSW-51526). Those must NEVER be keep/delete/suggest targets — deleting one
// would destroy a DIFFERENT SKU's record. Foreign records stay in the list for
// context but are excluded from every decision here.
const own = recs.filter((r) => r.skuMatch);
const isKeepWorthy = (r) => r.sampleOrdered || (!r.mfrPlaceholder && /[A-Za-z]/.test(r.noteMfr || r.mfrPattern || ''));
const keepers = own.filter(isKeepWorthy);
// A REAL mfr code (Steve wants a mfr NUMBER, not a pattern name / description / image
// filename): non-empty, no spaces, not an image filename, <=24 chars, and CONTAINS a
// digit. Keeps gz127 / AD10002 / GLM51317 / SG3-Gold / SBR1-Chocolate; rejects
// "Lumberjack", "allstar fabric candy", "allstardetail1gunmetal.jpg".
const isRealCode = (s) => {
s = String(s || '').trim();
return !!s && !/\s/.test(s) && !/\.(jpe?g|png|gif|tiff?)$/i.test(s) && s.length <= 24 && /\d/.test(s);
};
// the correct real mfr to SUGGEST into the field: prefer a sample-ordered own record's
// real code, else any own record's real code. Only ever from THIS SKU's own records,
// and only when it actually LOOKS like a mfr number (else leave blank = needs lookup).
const realSrc = keepers.find((r) => r.sampleOrdered && isRealCode(r.noteMfr)) || keepers.find((r) => isRealCode(r.noteMfr));
const realMfr = realSrc ? String(realSrc.noteMfr).trim() : '';
const keepRid = realSrc ? String(realSrc.recordId) : (keepers[0] ? String(keepers[0].recordId) : '');
const canPrune = keepers.length > 0; // guard: only suggest deletes if a keeper survives
for (const r of recs) {
// delete-suggest ONLY an own-SKU, no-sample, placeholder record (never a foreign leak).
r.deleteSuggested = canPrune && r.skuMatch && !r.sampleOrdered && r.mfrPlaceholder && String(r.recordId) !== keepRid;
}
return { filemaker: recs, unified: unified || [], fmError: fm.fmError || null,
suggestion: { realMfr, keepRid, deleteRids: recs.filter((r) => r.deleteSuggested).map((r) => String(r.recordId)) } };
}