`scripts/bannedword-title-preview/bannedword-title-preview.mjs`

107 lines
// bannedword-title-preview — PREVIEW-ONLY remediation for the banned word
// 'Wallpaper' in live product titles. Generates the rewrite transform and its
// edge-case census so Steve can approve the (gated) bulk rewrite with eyes on
// real before->after pairs. WRITES NOTHING to Shopify.
//
// Core insight surfaced by the data: a NAIVE global Wallpaper->Wallcovering would
// corrupt brand names for vendors whose NAME contains "Wallpaper" (Malibu Wallpaper,
// Scalamandre Wallpaper, etc.) — there "Wallpaper" sits only in the "| <brand>"
// suffix and is the legitimate vendor name, NOT a banned-word violation.
//
// GUARDED transform: split title on " | " into [descriptor, brand-suffix]; replace
// 'Wallpaper'->'Wallcovering' ONLY in the descriptor; leave the brand suffix intact.
//   - descriptor has the word  -> CLEAN rewrite
//   - word only in brand suffix -> NO-CHANGE (false positive; brand name)
//   - doubled result / plural   -> NEEDS-REVIEW
//
// Source: dw_unified mirror (READ-ONLY). NB the mirror shows ~1,508 ACTIVE titles
// with the word vs the storefront audit's 25,131 — the mirror undercounts (stale/
// partial title sync), so the actual bulk rewrite must target LIVE Shopify. This is
// a representative transform+edge-case preview, not the final worklist.
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const PSQL = [ '/opt/homebrew/opt/postgresql@14/bin/psql', '/usr/local/opt/postgresql@14/bin/psql', 'psql' ]
  .find(p => { try { execFileSync(p, ['--version'], { stdio: 'ignore' }); return true; } catch { return false; } }) || 'psql';
const DB = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
const OUT = `${process.env.HOME}/.claude/yolo-queue/bannedword-title-preview-2026-06-16.json`;
const MD = `${process.env.HOME}/.claude/yolo-queue/bannedword-title-preview-2026-06-16.md`;

function q(sql) {
  const out = execFileSync(PSQL, [DB, '-At', '-F', '\t', '-c', sql], { encoding: 'utf8', maxBuffer: 128 * 1024 * 1024 });
  return out.trim() ? out.trim().split('\n').map(r => r.split('\t')) : [];
}

const rows = q(`select vendor, mfr_sku, title from shopify_products
  where status='ACTIVE' and title ~* 'wallpaper' order by vendor, mfr_sku`)
  .map(r => ({ vendor: r[0], sku: r[1], title: r[2] }));

const RE = /\bwallpaper\b/gi;
const repl = (s) => s.replace(RE, (m) => m[0] === 'W' ? 'Wallcovering' : 'wallcovering');

const cats = { clean: [], brand_only_nochange: [], needs_review: [] };
const byVendor = {};
for (const r of rows) {
  byVendor[r.vendor] = (byVendor[r.vendor] || 0) + 1;
  const parts = r.title.split(' | ');
  const brand = parts.length > 1 ? parts.slice(1).join(' | ') : '';
  const desc = parts[0];
  const descHas = RE.test(desc); RE.lastIndex = 0;
  let newTitle, cat;
  if (!descHas) { newTitle = r.title; cat = 'brand_only_nochange'; }
  else {
    const newDesc = repl(desc);
    newTitle = parts.length > 1 ? [newDesc, ...parts.slice(1)].join(' | ') : newDesc;
    // needs-review heuristics
    if (/wallcovering\s+wallcovering/i.test(newTitle) || /\bwallpapers\b/i.test(r.title) || /\bwallcovering\b.*\bwallpaper\b/i.test(r.title)) cat = 'needs_review';
    else cat = 'clean';
  }
  const rec = { vendor: r.vendor, sku: r.sku, before: r.title, after: newTitle, brand };
  cats[cat].push(rec);
}

const sampleAcross = (arr, n) => {
  const byV = {}; for (const x of arr) (byV[x.vendor] ||= []).push(x);
  const out = []; const keys = Object.keys(byV);
  let i = 0; while (out.length < n && keys.length) { const k = keys[i % keys.length]; if (byV[k].length) out.push(byV[k].shift()); else { keys.splice(i % keys.length, 1); continue; } i++; }
  return out;
};

const out = {
  generated_at: new Date().toISOString(),
  source: 'dw_unified mirror (READ-ONLY)',
  mirror_count: rows.length,
  storefront_audit_count: 25131,
  note: 'mirror undercounts live titles; bulk rewrite must target live Shopify, not this mirror',
  transform: "GUARDED: replace \\bWallpaper\\b -> Wallcovering ONLY in the descriptor (text before ' | '); brand suffix preserved",
  category_counts: { clean: cats.clean.length, brand_only_nochange: cats.brand_only_nochange.length, needs_review: cats.needs_review.length },
  by_vendor: Object.entries(byVendor).sort((a,b)=>b[1]-a[1]),
  samples_clean: sampleAcross(cats.clean, 30),
  samples_brand_only: sampleAcross(cats.brand_only_nochange, 12),
  samples_needs_review: cats.needs_review.slice(0, 20),
};
fs.writeFileSync(OUT, JSON.stringify(out, null, 2));

let md = `# Banned-word 'Wallpaper' title rewrite — PREVIEW (report-only)\n\n`;
md += `**$0, READ-ONLY.** No Shopify writes. Source: dw_unified mirror.\n\n`;
md += `⚠️ **Count caveat:** mirror shows **${rows.length}** ACTIVE titles with the word; the storefront audit found **25,131**. The mirror undercounts (stale/partial title sync) — the actual bulk rewrite MUST run against LIVE Shopify, not the mirror. This is a representative transform + edge-case preview.\n\n`;
md += `## Guarded transform\n\`${out.transform}\`\n\n`;
md += `## Category census (of ${rows.length} mirror rows)\n`;
md += `| Category | Count | Meaning |\n|---|---:|---|\n`;
md += `| ✅ CLEAN rewrite | ${cats.clean.length} | word is in the descriptor → safe Wallpaper→Wallcovering |\n`;
md += `| 🚫 BRAND-ONLY (no change) | ${cats.brand_only_nochange.length} | word ONLY in the brand suffix (e.g. \\| Malibu Wallpaper) → DO NOT rewrite (legit vendor name) |\n`;
md += `| 🟠 NEEDS REVIEW | ${cats.needs_review.length} | doubled 'Wallcovering Wallcovering', plural 'Wallpapers', or mixed |\n\n`;
md += `> **The critical guard:** a naive global replace would corrupt ${cats.brand_only_nochange.length} titles' brand names (Malibu Wallpaper → Malibu Wallcovering, etc.). The guarded transform leaves the brand suffix untouched.\n\n`;
md += `## Affected by vendor\n| Vendor | Count |\n|---|---:|\n`;
for (const [v, c] of out.by_vendor) md += `| ${v} | ${c} |\n`;
md += `\n## CLEAN before→after (sample)\n`;
for (const s of out.samples_clean.slice(0, 20)) md += `- \`${s.before}\`\n  → \`${s.after}\`\n`;
md += `\n## BRAND-ONLY — DO NOT rewrite (sample)\n`;
for (const s of out.samples_brand_only) md += `- \`${s.before}\` _(word is the brand)_\n`;
if (cats.needs_review.length) { md += `\n## NEEDS REVIEW (sample)\n`; for (const s of out.samples_needs_review) md += `- \`${s.before}\` → \`${s.after}\`\n`; }
md += `\n_Gated: the bulk rewrite (live Shopify, via shopify_api_queue {id,title}) needs Steve's approval. This preview + the brand-suffix guard is the spec._\n`;
fs.writeFileSync(MD, md);

console.log(`[bannedword-title-preview] mirror=${rows.length}: clean=${cats.clean.length} brand-only-nochange=${cats.brand_only_nochange.length} needs-review=${cats.needs_review.length}`);
console.log(`Report: ${MD}`);