← back to Wolfgordon Crawl
reconcile.js
187 lines
#!/usr/bin/env node
// ============================================================================
// Wolf Gordon re-onboarding reconciliation (DRY-RUN, read-only).
//
// The wolfgordon.com site fully recoded its manufacturer SKUs, so the 1,068
// legacy Shopify products (DWWG-AD10336, DWWG-ZIG-8856, ...) no longer match
// the fresh catalog by SKU (~2 matches out of 1,068). The only durable join is
// PATTERN NAME + COLOR NAME.
//
// This script:
// 1. Pulls the fresh wolf_gordon_catalog (truth) keyed by normalized
// pattern|color -> sequential dw_sku.
// 2. Pulls the 1,068 legacy shopify_products (vendor='Wolf Gordon'),
// extracts each row's pattern + color (color from title after " - ",
// fallback to pattern_name field), normalizes, and tries to match.
// 3. Reports: matched (re-mappable to a sequential DW SKU), unmatched legacy
// (discontinue candidates), net-new catalog rows (no legacy peer).
//
// WRITES NOTHING. Pure analysis. Run: node reconcile.js
// ============================================================================
const { pool } = require('./scraper-utils');
// Normalize a name for matching: lowercase, strip ®/™, collapse whitespace,
// drop punctuation that varies between sources.
function norm(s) {
if (!s) return '';
return String(s)
.toLowerCase()
.replace(/[®™©]/g, '')
.replace(/&/g, '&')
.replace(/[^a-z0-9]+/g, ' ')
.replace(/\s+/g, ' ')
.trim();
}
// Normalize a MANUFACTURER SKU for matching. The site reformatted SKUs
// (space<->hyphen) and the legacy DW SKUs carried a trailing "_8" sample-roll
// suffix (e.g. "BOS-4399_8"). Strip ALL non-alphanumerics AND a trailing "8"
// that came from "_8" so "CCT-2670"==="CCT 2670" and "BOS-4399_8"==="BOS 4399".
function nsku(s) {
if (!s) return '';
let x = String(s).toLowerCase().replace(/_8$/, ''); // drop sample-roll suffix
return x.replace(/[^a-z0-9]+/g, ''); // strip space/hyphen
}
// Pull the color out of a legacy Shopify title.
// Titles look like: "Sparta - Oak | Wolf Gordon Wallcoverings"
// or "SM8008 | Wolf Gordon Wallcoverings" (no color) or
// "Ambient Design - AD10335 | Wolf Gordon Wallcoverings" (color is a code).
function legacyColorFromTitle(title) {
if (!title) return null;
const head = title.split('|')[0].trim(); // "Sparta - Oak"
const idx = head.indexOf(' - ');
if (idx === -1) return null;
return head.slice(idx + 3).trim();
}
async function main() {
// ---- 1. Fresh catalog index ----
const cat = await pool.query(`
SELECT mfr_sku, dw_sku, pattern_name, color_name, collection
FROM wolf_gordon_catalog
`);
// Map normalized "pattern|color" -> [rows]. Collisions are tracked.
const catByPC = new Map(); // pattern+color
const catByPattern = new Map(); // pattern only (for color-blind fallback diag)
for (const r of cat.rows) {
const p = norm(r.pattern_name);
const c = norm(r.color_name);
const keyPC = `${p}|${c}`;
if (!catByPC.has(keyPC)) catByPC.set(keyPC, []);
catByPC.get(keyPC).push(r);
if (!catByPattern.has(p)) catByPattern.set(p, []);
catByPattern.get(p).push(r);
}
// ---- 2. Legacy Shopify rows ----
const leg = await pool.query(`
SELECT sku, mfr_sku, pattern_name, title, status
FROM shopify_products
WHERE vendor = 'Wolf Gordon'
`);
// Normalized-SKU index of the fresh catalog (primary join).
const catByNsku = new Map();
for (const r of cat.rows) {
const k = nsku(r.mfr_sku);
if (k && !catByNsku.has(k)) catByNsku.set(k, r);
}
const matchedBySku = []; // normalized mfr_sku aligns (PRIMARY, strongest)
const matchedByPC = []; // pattern+color hit a unique catalog row
const matchedByPCMulti = [];// pattern+color hit MULTIPLE catalog rows (ambiguous)
const unmatched = []; // no catalog peer -> discontinue candidate
for (const r of leg.rows) {
const legColor = legacyColorFromTitle(r.title);
const p = norm(r.pattern_name);
const c = norm(legColor);
const keyPC = `${p}|${c}`;
// (1) PRIMARY: normalized manufacturer SKU survivor?
const skuHit = catByNsku.get(nsku(r.mfr_sku));
if (skuHit) {
matchedBySku.push({ legacy: r, legColor, target: skuHit });
continue;
}
// (2) FALLBACK: pattern + color
if (c && catByPC.has(keyPC)) {
const hits = catByPC.get(keyPC);
if (hits.length === 1) {
matchedByPC.push({ legacy: r, legColor, target: hits[0] });
} else {
matchedByPCMulti.push({ legacy: r, legColor, targets: hits });
}
continue;
}
unmatched.push({ legacy: r, legColor });
}
// ---- 3. Net-new catalog rows (no legacy peer matched into them) ----
// A catalog row is "covered" if some legacy row matched it (by PC or sku).
const coveredDwSku = new Set();
for (const m of matchedBySku) coveredDwSku.add(m.target.mfr_sku);
for (const m of matchedByPC) coveredDwSku.add(m.target.mfr_sku);
for (const m of matchedByPCMulti) for (const t of m.targets) coveredDwSku.add(t.mfr_sku);
let netNew = 0;
for (const r of cat.rows) {
if (!coveredDwSku.has(r.mfr_sku)) netNew++;
}
// ---- Report ----
const line = '='.repeat(74);
console.log(line);
console.log(' WOLF GORDON RECONCILIATION — DRY RUN (read-only, no writes)');
console.log(line);
console.log(` Fresh catalog rows: ${cat.rows.length}`);
console.log(` Legacy Shopify WG products: ${leg.rows.length}`);
console.log(line);
console.log(' PRIMARY KEY: normalized mfr_sku (strip space/hyphen + trailing _8)');
console.log(' FALLBACK: normalized(pattern_name)+"|"+normalized(title color)');
console.log(line);
console.log(` [A] Matched by normalized mfr_sku: ${matchedBySku.length} <- safely re-mappable (PRIMARY)`);
console.log(` [B] Matched by pattern+color (UNIQUE): ${matchedByPC.length} <- safely re-mappable (fallback)`);
console.log(` [C] Matched by pattern+color (AMBIGUOUS): ${matchedByPCMulti.length} <- need a tiebreak`);
console.log(` [D] Unmatched legacy (discontinue cand.): ${unmatched.length}`);
console.log(` --------------------------------------------------`);
console.log(` legacy total = A+B+C+D = ${matchedBySku.length + matchedByPC.length + matchedByPCMulti.length + unmatched.length}`);
console.log(line);
console.log(` [E] Net-new catalog rows (no legacy peer): ${netNew}`);
console.log(line);
console.log('\n --- SAMPLE [A] normalized-SKU match (legacy DW SKU -> new sequential DW SKU) ---');
for (const m of matchedBySku.slice(0, 12)) {
console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(34)} -> ${m.target.dw_sku || '(no dw_sku yet)'} [${m.target.mfr_sku}]`);
}
console.log('\n --- SAMPLE [B] pattern+color match (legacy SKU -> new sequential DW SKU) ---');
for (const m of matchedByPC.slice(0, 12)) {
console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(34)} -> ${m.target.dw_sku || '(no dw_sku yet)'} [${m.target.mfr_sku}]`);
}
console.log('\n --- SAMPLE [C] ambiguous pattern+color (>1 catalog hit) ---');
for (const m of matchedByPCMulti.slice(0, 8)) {
console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(30)} -> ${m.targets.map(t=>t.dw_sku||t.mfr_sku).join(', ')}`);
}
console.log('\n --- SAMPLE [D] unmatched legacy (discontinue candidates) ---');
for (const m of unmatched.slice(0, 15)) {
console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'(no color)')).padEnd(34)} status=${m.legacy.status}`);
}
// breakdown of unmatched by status (archived/deleted are expected to not re-map)
const ubs = {};
for (const m of unmatched) ubs[m.legacy.status] = (ubs[m.legacy.status]||0)+1;
console.log('\n --- [D] unmatched by Shopify status ---');
for (const k of Object.keys(ubs)) console.log(` ${k.padEnd(22)} ${ubs[k]}`);
await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });