← back to Dw Pairs Well
scripts/pipe-title-backfill/audit.mjs
185 lines
#!/usr/bin/env node
// Audit + backfill-proposal generator for malformed "| Vendor" titles in dw_unified.shopify_products.
// READ-ONLY: emits a before->after JSON, applies nothing. (Steve-gated writes handled separately.)
// Author: steve@designerwallcoverings.com
import pg from 'pg';
import fs from 'node:fs';
const DB = process.env.DATABASE_URL || 'postgresql://stevestudio2@/dw_unified?host=/tmp';
const client = new pg.Client({ connectionString: DB });
// --- helpers -------------------------------------------------------------
const BANNED = /\bwall\s*papers?\b/gi; // "wallpaper"/"wallpapers" banned in output
function titleCase(s) {
const SMALL = new Set(['a','an','and','as','at','but','by','for','in','of','on','or','the','to','with','on','ª']);
return s.split(/\s+/).filter(Boolean).map((w, i, arr) => {
const lower = w.toLowerCase();
// keep already-caps tokens with digits (e.g. "1910S", "24\"") intact-ish
if (/\d/.test(w)) return w;
if (i !== 0 && i !== arr.length - 1 && SMALL.has(lower)) return lower;
return lower.charAt(0).toUpperCase() + lower.slice(1);
}).join(' ');
}
// Humanize a descriptive handle into a pattern-name segment, stripping DW-boilerplate + BANNED word.
// Returns null when the handle has NO real pattern name (pure boilerplate/DIG-code handle).
function patternFromHandle(handle) {
if (!handle) return null;
// Reject boilerplate-only handles: dig_NNNNN_vintage_wallpaper_designer_wallcoverings (underscore form)
// and any handle that is JUST a code + boilerplate with no descriptive word.
const norm = handle.replace(/[_-]/g, ' ').toLowerCase();
const stripped = norm
.replace(/\b(dig|vin|dwh|eur)\b/g,' ')
.replace(/\b\d+\b/g,' ')
.replace(/\bvintage\b/g,' ').replace(/\bwall\s*papers?\b/g,' ')
.replace(/\bdesigner\b/g,' ').replace(/\bwallcoverings?\b/g,' ')
.replace(/\blos angeles\b/g,' ').replace(/\breproduction\b/g,' ')
.replace(/\bauthentic\b/g,' ').replace(/\bcopy of\b/g,' ')
.replace(/\s+/g,' ').trim();
if (!stripped) return null; // nothing descriptive left -> unrecoverable
let s = handle.replace(/[_-]/g, ' ').trim();
s = s.replace(/\bdesigner wallcoverings( los angeles)?\b/gi, ' ');
s = s.replace(/\bvintage wall\s*papers?\b/gi, 'Vintage'); // "vintage wallpaper(s)" -> "Vintage"
s = s.replace(/\bwall\s*papers?\b/gi, ' '); // any remaining banned word
s = s.replace(BANNED, ' ');
s = s.replace(/\bcopy of\b/gi, ' ');
s = s.replace(/\s+\d{1,3}\s*$/,' '); // trailing bare numeric counter e.g. "...wallcoverings 49"
s = s.replace(/\s+/g, ' ').trim();
if (!s) return null;
BANNED.lastIndex = 0;
if (BANNED.test(s)) { BANNED.lastIndex=0; return null; } // safety: banned word survived
BANNED.lastIndex = 0;
return titleCase(s);
}
// Recover a clean DIG/VIN code from dw_sku (strip variant suffixes).
function digCodeFromSku(dwSku) {
if (!dwSku) return null;
const m = dwSku.match(/^((?:DIG|Dig|VIN|Vin|DWH|EUR)[- ]?\d+)/i);
if (!m) return null;
return m[1].toUpperCase().replace(/\s/,'-').replace(/^DIG/,'DIG').replace(/^VIN/,'VIN');
}
async function main() {
await client.connect();
const { rows } = await client.query(`
SELECT id, shopify_id, dw_sku, vendor, mfr_sku, handle, title, image_url, pattern_name
FROM shopify_products
WHERE title LIKE '| %' AND status='ACTIVE'
ORDER BY vendor, dw_sku`);
// --- sibling lookups for name recovery (Osborne W-code, WM mfr twin) ---
// Osborne: recover pattern from any sibling sharing the W#### root with a real name.
// WM: recover pattern_name from a correctly-titled twin sharing mfr_sku.
const apply = []; // mechanically unambiguous
const draft = []; // needs Steve eyes
const flags = []; // private-label / other
for (const r of rows) {
const rec = { id: r.id, shopify_id: r.shopify_id, dw_sku: r.dw_sku, vendor: r.vendor,
before: { title: r.title, mfr_sku: r.mfr_sku }, after: {}, source: null, bucket: null };
if (r.vendor === 'Boråstapeter') {
// pattern_name already populated ("Sailboats Grey"); mfr_sku 8855 is a real Boråstapeter article number -> keep.
if (r.pattern_name && !/^\|/.test(r.pattern_name)) {
rec.after.title = `${titleCase(r.pattern_name)} Wallcovering | Boråstapeter`;
rec.after.mfr_sku = r.mfr_sku; // real article number
rec.source = 'pattern_name column';
rec.bucket = 'APPLY';
apply.push(rec);
} else { rec.bucket = 'DRAFT'; rec.source='no pattern_name'; draft.push(rec); }
continue;
}
if (r.vendor === 'Osborne & Little') {
// EUR-71203: mfr_sku junk "-03--"; image w7903-03 -> W7903-03; siblings W7903-01/02 = "Shalimar Coconut"
const imgW = (r.image_url||'').match(/w(\d{4})-(\d{2})/i);
let wcode = imgW ? `W${imgW[1]}-${imgW[2]}` : null;
let patt = null;
if (imgW) {
const sib = await client.query(
`SELECT pattern_name FROM shopify_products
WHERE vendor='Osborne & Little' AND mfr_sku ILIKE $1 AND pattern_name IS NOT NULL
AND pattern_name NOT LIKE '|%' AND pattern_name <> '' LIMIT 1`,
[`W${imgW[1]}-%`]);
if (sib.rows[0]) patt = sib.rows[0].pattern_name;
}
if (wcode && patt) {
rec.after.title = `${titleCase(patt)} | Osborne & Little Europe`;
rec.after.mfr_sku = wcode;
rec.source = `image filename ${imgW[0]} + sibling W${imgW[1]}-* pattern_name`;
rec.bucket = 'APPLY';
apply.push(rec);
} else { rec.bucket='DRAFT'; rec.source='could not recover W-code/pattern'; draft.push(rec); }
continue;
}
if (r.vendor === 'William Morris') {
// mfr_sku already real; recover pattern_name from correctly-titled twin sharing mfr_sku
const twin = await client.query(
`SELECT pattern_name FROM shopify_products
WHERE vendor='William Morris' AND mfr_sku=$1 AND title NOT LIKE '| %'
AND pattern_name IS NOT NULL AND pattern_name <> '' AND pattern_name NOT LIKE '|%'
LIMIT 1`, [r.mfr_sku]);
let patt = twin.rows[0]?.pattern_name;
if (!patt && /fruit/i.test(r.image_url||'')) patt = 'Fruit'; // image confirms Fruit colorway
if (patt) {
rec.after.title = `${titleCase(patt)} Wallcovering | William Morris`;
rec.after.mfr_sku = r.mfr_sku; // already correct
rec.source = twin.rows[0] ? `twin mfr_sku ${r.mfr_sku} pattern_name` : 'image filename (fruit)';
rec.bucket = 'APPLY';
apply.push(rec);
} else { rec.bucket='DRAFT'; rec.source='no twin/pattern'; draft.push(rec); }
continue;
}
if (r.vendor === 'Designer Wallcoverings') { // DW Bespoke Studios cluster
const patt = patternFromHandle(r.handle);
const dig = digCodeFromSku(r.dw_sku);
if (patt) {
// Quality guards: reject stutter ("Wallcovering(s) Wallcovering"), stray SKU tokens,
// and generic-boilerplate-only names -> DRAFT (valid but not blind-publish clean).
let cleanPatt = patt
.replace(/\bwallcoverings?\b/gi,'').replace(/\bvin\s*\d+\b/gi,'')
.replace(/(\d)\s+s\b/gi,"$1's") // "1940 S" -> "1940's"
.replace(/\s+/g,' ').trim();
const genericOnly = /^(authentic\s+)?\d{4}('?s)?\s+reproduction\s+vintage$/i.test(cleanPatt)
|| /^(authentic\s+)?(vintage\s+)?reproduction$/i.test(cleanPatt) || cleanPatt.length < 3;
rec.after.title = `${cleanPatt} Wallcovering | DW Bespoke Studios`;
rec.after.mfr_sku = dig || r.mfr_sku;
rec.source = `humanized handle${dig?` + DIG code ${dig}`:''}`;
BANNED.lastIndex = 0;
const stutter = /wallcovering.*wallcovering/i.test(rec.after.title);
if (BANNED.test(rec.after.title) || stutter || genericOnly) {
BANNED.lastIndex=0; rec.bucket='DRAFT';
rec.source += ` | DEMOTED: ${BANNED.test(rec.after.title)?'banned-word ':''}${stutter?'stutter ':''}${genericOnly?'generic-boilerplate-name':''}`.trim();
BANNED.lastIndex=0; draft.push(rec);
} else { BANNED.lastIndex=0; rec.bucket='APPLY'; apply.push(rec); }
} else {
// Handle has NO real pattern name (pure dig_NNNNN_vintage_wallpaper boilerplate).
// NEVER "Unknown"/banned word. Fall back to the DIG catalog code as the pattern token.
// Real pattern name unknown -> DRAFT for Steve/vendor lookup (do not blind-publish a code as a name).
rec.after.title = dig ? `${dig} Wallcovering | DW Bespoke Studios` : null;
rec.after.mfr_sku = dig || r.mfr_sku;
rec.source = 'handle is boilerplate-only (no pattern name); DIG-code fallback title, real name needs lookup';
rec.bucket = 'DRAFT';
draft.push(rec);
}
continue;
}
rec.bucket='DRAFT'; rec.source='unhandled vendor'; draft.push(rec);
}
const out = { generated_at: new Date().toISOString(), total: rows.length,
apply_count: apply.length, draft_count: draft.length, flags, apply, draft };
const path = new URL('./before-after.json', import.meta.url).pathname;
fs.writeFileSync(path, JSON.stringify(out, null, 2));
console.log(`total=${rows.length} apply=${apply.length} draft=${draft.length} flags=${flags.length}`);
console.log('wrote', path);
await client.end();
}
main().catch(e => { console.error(e); process.exit(1); });