`audits/designtex-uom-fix/build-fillin-dataset.mjs`

126 lines
#!/usr/bin/env node
// Build the Designtex fill-in dataset by joining crawled sidecars to dw_unified.designtex_catalog rows.
// READ-ONLY (reads PG + sidecars, writes a JSON plan). $0 (local).
//
// Produces designtex-fillin-plan.json:
//   - per DB row: the spec values + image set the crawl recovered, keyed by mfr_sku,
//     with UOM forced to YARD, pattern_repeat from "Repeat", care from "Cleaning Code",
//     all_images from per-colorway gallery, plus corrected color_name where DB drifted.
//   - dedupe: rows whose mfr_sku does NOT match any live colorway SKU on its pattern page
//     (e.g. mfr_sku='gilded') => ARCHIVE candidate.
//   - discontinued: rows on the 14 confirmed-404 patterns => ARCHIVE candidate.
import fs from 'fs';
import path from 'path';
import { execSync } from 'child_process';

const HERE = path.dirname(new URL(import.meta.url).pathname);
const SIDE = path.join(HERE, '..', '..', 'enrich-cache', 'designtex');

const DISCONTINUED_SLUGS = new Set(['burnish','imprint','kith','layer','plexus','tacit','talula','twinkle','zipper','circulate','zip-code','zip-line','silicone-element-celliant','spark']);
const RENAMES = { 'bocce-plaid': 'bocce', 'kin': 'kindred' }; // old DB slug -> new live slug

const slugOf = (url) => (url || '').replace(/^https?:\/\/shop\.designtex\.com\//, '').replace(/\/$/, '');
const normSku = (s) => (s || '').replace(/[^0-9A-Za-z]/g, '').toUpperCase();

// load sidecars into a map: slug -> sidecar, and a colorway index: mfr_sku -> {slug, colorway}
const sidecars = {};
const cwIndex = {};
for (const f of fs.readdirSync(SIDE)) {
  if (!f.endsWith('.json') || f === '_manifest.json') continue;
  const j = JSON.parse(fs.readFileSync(path.join(SIDE, f), 'utf8'));
  sidecars[j.slug] = j;
  for (const c of j.colorways || []) {
    if (c.mfr_sku) cwIndex[normSku(c.mfr_sku)] = { slug: j.slug, colorway: c, specs: j.specs };
  }
}

// Pull DB rows via psql (peer auth, no password)
const sql = `COPY (SELECT row_to_json(t) FROM (SELECT id, mfr_sku, dw_sku, pattern_name, color_name, product_url, shopify_product_id, unit_of_measure, pattern_repeat, maintenance, fire_rating, material FROM designtex_catalog ORDER BY id) t) TO STDOUT;`;
const raw = execSync(`psql -d dw_unified -tAc ${JSON.stringify(sql)}`, { maxBuffer: 1 << 28 }).toString();
const rows = raw.trim().split('\n').filter(Boolean).map((l) => JSON.parse(l));

// spec field mapping
const SPEC_MAP = {
  pattern_repeat: (s) => s['Repeat'] || null,
  maintenance: (s) => s['Cleaning Code'] ? `Cleaning Code: ${s['Cleaning Code']}` + (s['Approved Disinfectants'] ? `; Approved Disinfectants: ${s['Approved Disinfectants']}` : '') : null,
  fire_rating: (s) => s['Flammability'] || null,
  material: (s) => s['Content'] || null,
  finish: (s) => s['Finish'] || null,
  width: (s) => s['Width'] || null,
  composition: (s) => s['Content'] || null,
};

const plan = { generated_at: new Date().toISOString(), source: 'enrich-cache/designtex sidecars', rows: [], stats: {} };
let matched = 0, archiveDiscontinued = 0, archiveOrphan = 0, noMatch = 0;

for (const r of rows) {
  const sku = normSku(r.mfr_sku);
  let slug = slugOf(r.product_url);
  if (RENAMES[slug]) slug = RENAMES[slug];

  if (DISCONTINUED_SLUGS.has(slugOf(r.product_url))) {
    plan.rows.push({ id: r.id, dw_sku: r.dw_sku, mfr_sku: r.mfr_sku, pattern_name: r.pattern_name,
      action: 'ARCHIVE', reason: 'pattern page 404 + not in vendor sitemap (discontinued)',
      shopify_product_id: r.shopify_product_id });
    archiveDiscontinued++;
    continue;
  }

  let hit = cwIndex[sku];
  let mfrBackfill = null;
  if (!hit) {
    // mfr_sku is the slug (placeholder) not a colorway SKU (e.g. 'gilded','betwixt').
    // DTD 3/3 (2026-06-23): these are REAL products (esp. the 85 live ACTIVE parents).
    // FILL them using the pattern page's base specs + first/representative colorway image,
    // and backfill mfr_sku to the pattern's first real colorway SKU. NEVER archive over a
    // mfr_sku-hygiene issue. Only genuinely-404 patterns (handled above) get ARCHIVE.
    const sc = sidecars[slug];
    if (!sc) {
      plan.rows.push({ id: r.id, dw_sku: r.dw_sku, mfr_sku: r.mfr_sku, pattern_name: r.pattern_name,
        action: 'REVIEW', reason: `no sidecar for slug '${slug}' (not 404, but uncrawled)`,
        shopify_product_id: r.shopify_product_id });
      noMatch++;
      continue;
    }
    const firstCw = (sc.colorways || [])[0];
    hit = { slug: sc.slug, colorway: firstCw || { color_name: r.color_name, images: [], swatch_image: null }, specs: sc.specs };
    mfrBackfill = firstCw && firstCw.mfr_sku ? firstCw.mfr_sku : null;
  }

  const s = hit.specs || {};
  const fill = {};
  for (const [col, fn] of Object.entries(SPEC_MAP)) { const v = fn(s); if (v) fill[col] = v; }
  // always force YARD
  fill.unit_of_measure = 'YARD';
  // corrected color name from live swatch label — ONLY for per-colorway rows whose mfr_sku
  // matched a live colorway directly. For parent rows (mfrBackfill set) the parent's existing
  // color_name is authoritative; do NOT overwrite it with the pattern's first colorway.
  const liveColor = hit.colorway.color_name;
  const colorCorrection = (!mfrBackfill && liveColor && liveColor.toLowerCase() !== (r.color_name || '').toLowerCase())
    ? { from: r.color_name, to: liveColor } : null;
  // images: per-colorway gallery (flat + detail) + swatch
  const imgs = [...(hit.colorway.images || [])];
  if (hit.colorway.swatch_image) imgs.push(hit.colorway.swatch_image);

  const row = {
    id: r.id, dw_sku: r.dw_sku, mfr_sku: r.mfr_sku, pattern_name: r.pattern_name,
    action: 'FILL',
    matched_slug: hit.slug,
    set: fill,
    color_correction: colorCorrection,
    all_images: [...new Set(imgs)],
    uom_was: r.unit_of_measure,
    shopify_product_id: r.shopify_product_id,
  };
  if (mfrBackfill) { row.mfr_sku_backfill = { from: r.mfr_sku, to: mfrBackfill }; row.is_parent_row = true; }
  plan.rows.push(row);
  matched++;
}

plan.stats = { total: rows.length, fill: matched, archive_discontinued: archiveDiscontinued, archive_orphan: archiveOrphan, review_no_sidecar: noMatch, parent_rows_filled: plan.rows.filter(x=>x.is_parent_row).length };
fs.writeFileSync(path.join(HERE, 'designtex-fillin-plan.json'), JSON.stringify(plan, null, 2));
console.log(JSON.stringify(plan.stats, null, 2));
// sample
console.log('\nSample FILL row:'); console.log(JSON.stringify(plan.rows.find((r) => r.action === 'FILL'), null, 2).slice(0, 900));
console.log('\nArchive-orphan rows:'); plan.rows.filter((r) => r.action === 'ARCHIVE' && r.reason.includes('orphan')).slice(0, 5).forEach((r) => console.log(' ', r.dw_sku, r.mfr_sku, r.pattern_name, '|', r.reason));