`audits/designtex-uom-fix/build-load-sql.mjs`

127 lines
#!/usr/bin/env node
// Generate load.sql for the Designtex fill-in from designtex-fillin-plan.json.
// Step 1 of the approved plan. Idempotent UPDATE-by-id. PostgreSQL-first (canonical dw_unified).
//
// - 5 missing columns ADDED first (unit_of_measure, our_price, net_cost, price_updated_at, roll_length).
// - 2,701 FILL rows: pattern_repeat, maintenance, fire_rating, material, composition, finish, width,
//   unit_of_measure='YARD', all_images (JSON text array), + 440 color_name corrections (DTD-B sanitized).
// - 130 ARCHIVE rows: discontinued=true.
// - 105 parent rows: mfr_sku backfill to the first real colorway SKU.
// Color-name sanitize (DTD 3/3 verdict B): decode HTML entities + strip embedded/trailing SKU-like tokens.
import fs from 'fs';
import path from 'path';

const HERE = path.dirname(new URL(import.meta.url).pathname);
const plan = JSON.parse(fs.readFileSync(path.join(HERE, 'designtex-fillin-plan.json'), 'utf8'));

const sqlStr = (v) => {
  if (v === null || v === undefined || v === '') return 'NULL';
  return "'" + String(v).replace(/'/g, "''") + "'";
};

// Decode the handful of HTML entities seen in scraped swatch labels.
const decodeEntities = (s) => s
  .replace(/&#x27;|&#39;|&apos;/g, "'")
  .replace(/&amp;/g, '&')
  .replace(/&quot;/g, '"')
  .replace(/&lt;/g, '<')
  .replace(/&gt;/g, '>')
  .replace(/&#(\d+);/g, (_, n) => String.fromCharCode(parseInt(n, 10)));

// Strip SKU-like tokens from a human color name: alnum runs >=5 chars that contain a digit,
// or [A-Z]?digit{2,}[A-Z0-9]* style codes. Collapse whitespace. Keep clean color words.
const sanitizeColor = (raw) => {
  let s = decodeEntities(raw);
  s = s
    .replace(/\b[A-Za-z]?\d{2,}[A-Za-z0-9-]*\b/g, '')      // J278-810, 6117V101, AM001101 (starts w/ <=1 letter + digits)
    .replace(/\b[A-Za-z]{0,3}\d+[A-Za-z]+\d+[A-Za-z0-9-]*\b/g, '') // mixed alnum codes
    .replace(/\b(?=[A-Za-z0-9-]*\d)[A-Za-z0-9-]{5,}\b/g, '') // any >=5-char alnum run containing a digit
    // strip swatch-availability noise that isn't part of a color name
    .replace(/\b(samples?\s+back\s+soon|back\s+soon|out\s+of\s+stock|coming\s+soon|discontinued)\b/gi, '')
    .replace(/\s{2,}/g, ' ')
    .replace(/\s+-\s*$/,'')
    .trim();
  return s;
};

const out = [];
out.push('-- Designtex fill-in load.sql — generated from designtex-fillin-plan.json');
out.push('-- Step 1: PostgreSQL-first canonical write. Idempotent UPDATE-by-id.');
out.push('BEGIN;');
out.push('');
out.push('-- 1a. Add the 5 columns canonical is missing (idempotent).');
out.push("ALTER TABLE designtex_catalog ADD COLUMN IF NOT EXISTS unit_of_measure text;");
out.push("ALTER TABLE designtex_catalog ADD COLUMN IF NOT EXISTS our_price numeric;");
out.push("ALTER TABLE designtex_catalog ADD COLUMN IF NOT EXISTS net_cost numeric;");
out.push("ALTER TABLE designtex_catalog ADD COLUMN IF NOT EXISTS price_updated_at timestamptz;");
out.push("ALTER TABLE designtex_catalog ADD COLUMN IF NOT EXISTS roll_length text;");
out.push('');

const sanitizeLog = [];
let fillCount = 0, archCount = 0, colorCount = 0, backfillCount = 0, imgCount = 0;

for (const r of plan.rows) {
  if (r.action === 'FILL') {
    fillCount++;
    const sets = [];
    const s = r.set || {};
    // Spec fields → canonical columns. material AND composition both exist canonically; the plan
    // carries both (same value) so write both.
    if (s.pattern_repeat != null) sets.push(`pattern_repeat = ${sqlStr(s.pattern_repeat)}`);
    if (s.maintenance != null) sets.push(`maintenance = ${sqlStr(s.maintenance)}`);
    if (s.fire_rating != null) sets.push(`fire_rating = ${sqlStr(s.fire_rating)}`);
    if (s.material != null) sets.push(`material = ${sqlStr(s.material)}`);
    if (s.composition != null) sets.push(`composition = ${sqlStr(s.composition)}`);
    if (s.finish != null) sets.push(`finish = ${sqlStr(s.finish)}`);
    if (s.width != null) sets.push(`width = ${sqlStr(s.width)}`);
    sets.push(`unit_of_measure = 'YARD'`);
    // all_images stored as JSON text (canonical all_images is text column holding JSON)
    if (Array.isArray(r.all_images) && r.all_images.length) {
      imgCount++;
      sets.push(`all_images = ${sqlStr(JSON.stringify(r.all_images))}`);
    }
    // color correction (DTD-B sanitize)
    if (r.color_correction && r.color_correction.to) {
      const clean = sanitizeColor(r.color_correction.to);
      if (clean && clean.length) {
        colorCount++;
        sets.push(`color_name = ${sqlStr(clean)}`);
        if (clean !== r.color_correction.to) sanitizeLog.push({ id: r.id, dw_sku: r.dw_sku, from: r.color_correction.to, to: clean });
      }
    }
    // mfr_sku backfill DROPPED for this load (DTD 2/1 verdict A, 2026-06-23):
    // designtex_catalog has UNIQUE(mfr_sku) and 85/105 targets already exist on the
    // colorway row that owns them; backfilling duplicates a SKU into a uniquely-
    // constrained column. The slug mfr_sku ('betwixt') is a benign placeholder; these
    // parent rows get full specs+color regardless. mfr_sku hygiene → separate task
    // (proper fix = parent/colorway link, not a synthetic SKU). Counted but not written.
    if (r.mfr_sku_backfill && r.mfr_sku_backfill.to) {
      backfillCount++;
    }
    sets.push(`updated_at = now()`);
    out.push(`UPDATE designtex_catalog SET ${sets.join(', ')} WHERE id = ${r.id};`);
  } else if (r.action === 'ARCHIVE') {
    archCount++;
    out.push(`UPDATE designtex_catalog SET discontinued = true, updated_at = now() WHERE id = ${r.id};`);
  }
}

out.push('');
out.push('COMMIT;');
out.push('');
// Post-load verification (printed by psql)
out.push("\\echo '=== VERIFY ==='");
out.push(`SELECT count(*) AS yard_rows FROM designtex_catalog WHERE unit_of_measure = 'YARD';`);
out.push(`SELECT count(*) AS all_images_nonempty FROM designtex_catalog WHERE all_images IS NOT NULL AND all_images <> '' AND all_images <> '[]';`);
out.push(`SELECT count(*) AS maintenance_set FROM designtex_catalog WHERE maintenance IS NOT NULL AND maintenance <> '';`);
out.push(`SELECT count(*) AS fire_rating_set FROM designtex_catalog WHERE fire_rating IS NOT NULL AND fire_rating <> '';`);
out.push(`SELECT count(*) AS pattern_repeat_set FROM designtex_catalog WHERE pattern_repeat IS NOT NULL AND pattern_repeat <> '';`);
out.push(`SELECT count(*) AS discontinued_flagged FROM designtex_catalog WHERE discontinued = true;`);

fs.writeFileSync(path.join(HERE, 'load.sql'), out.join('\n'));
fs.writeFileSync(path.join(HERE, 'color-sanitize-log.json'), JSON.stringify(sanitizeLog, null, 2));

console.log(`load.sql written. FILL=${fillCount} ARCHIVE=${archCount} color_corr=${colorCount} backfill=${backfillCount} images=${imgCount}`);
console.log(`SKU-token sanitized color names: ${sanitizeLog.length}`);
sanitizeLog.forEach((x) => console.log(`  ${x.dw_sku}: "${x.from}" -> "${x.to}"`));