`scripts/build-products-json.js`

157 lines
#!/usr/bin/env node
/**
 * Build data/products.json from the internal dw_unified.muralsource_catalog.
 * Mural Source is a scenic-mural line kept OFF the public Shopify store (DTD verdict C,
 * 2026-07-26 — internal-only, staff browse/quote). The viewer serves its OWN internal
 * PDPs from the staging table; no Shopify URL, no vendor URL is emitted. $0 (local PG read).
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const OUT = path.join(__dirname, '..', 'data', 'products.json');
const pool = new Pool({ host: '/tmp', database: 'dw_unified' });   // Mac2-local mirror, peer auth

const BUCKET = {
  white: 'white', ivory: 'white', cream: 'white', beige: 'white', oatmeal: 'white', alabaster: 'white',
  grey: 'grey', gray: 'grey', silver: 'grey', neutral: 'grey', taupe: 'grey', greige: 'grey', slate: 'grey',
  black: 'black', charcoal: 'black', ebony: 'black',
  red: 'red', burgundy: 'red', crimson: 'red',
  orange: 'orange', rust: 'orange', copper: 'orange', terracotta: 'orange', peach: 'orange', coral: 'orange',
  brown: 'brown', tan: 'brown', bronze: 'brown', chocolate: 'brown', sienna: 'brown', mahogany: 'brown',
  gold: 'gold', yellow: 'gold', mustard: 'gold', champagne: 'gold', canary: 'gold',
  green: 'green', olive: 'green', sage: 'green', emerald: 'green', celadon: 'green',
  teal: 'teal', turquoise: 'teal', aqua: 'teal',
  blue: 'blue', navy: 'blue', indigo: 'blue', dusty: 'blue',
  purple: 'purple', violet: 'purple', lavender: 'purple', plum: 'purple',
  pink: 'pink', rose: 'pink', blush: 'pink', magenta: 'pink',
};
const BUCKET_ORDER = ['white','grey','black','pink','red','orange','brown','gold','green','teal','blue','purple'];
const BUCKET_HUE   = { red:0, orange:30, gold:50, green:120, teal:175, blue:215, purple:280, pink:330, brown:25, white:null, grey:null, black:null };

const clean = s => (s == null ? s : String(s).replace(/\bWallpapers\b/gi, 'Wallcoverings').replace(/\bWallpaper\b/gi, 'Wallcovering'));
const cap = s => String(s || '').replace(/[-_]/g, ' ').replace(/\b\w/g, c => c.toUpperCase()).trim();
const jarr = v => { if (Array.isArray(v)) return v; if (v == null) return []; try { const p = JSON.parse(v); return Array.isArray(p) ? p : []; } catch { return []; } };
const slug = s => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');

function bucketFrom(words) {
  for (const w of words) {
    const t = String(w || '').toLowerCase();
    for (const key of Object.keys(BUCKET)) if (t.includes(key)) return BUCKET[key];
  }
  return null;
}
function repeatStr(r) {
  const v = (r.repeat_v && r.repeat_v !== '0') ? r.repeat_v : '';
  const h = (r.repeat_h && r.repeat_h !== '0') ? r.repeat_h : '';
  if (!v && !h) return null;
  return [h && `H ${h}`, v && `V ${v}`].filter(Boolean).join(' · ');
}

async function main() {
  const { rows } = await pool.query(`
    SELECT dw_sku, mfr_sku, pattern_name, color_name, collection, product_type,
           width, length, repeat_v, repeat_h, material, match_type, price_retail, price_trade,
           image_url, all_images, ai_colors, ai_styles, ai_tags, ai_description,
           color_hex, dominant_color_hex, discontinued, created_at
    FROM muralsource_catalog
    ORDER BY pattern_name, color_name, dw_sku
  `);

  const vr = (await pool.query(
    `SELECT vendor_name, vendor_discount_pct, pricing_unit, pricing_model, pricing_notes, sample_price
     FROM vendor_registry WHERE vendor_code = $1`, ['muralsource'])).rows[0] || {};
  const fm = (await pool.query(
    `SELECT phone, email_1, account_num FROM fmpro
     WHERE vendor_name = $1 AND account_num IS NOT NULL LIMIT 1`, ['Mural Source'])).rows[0] || {};
  const vendorMeta = {
    name: vr.vendor_name || 'Mural Source',
    phone: fm.phone || null, email: fm.email_1 || null, account_number: fm.account_num || null,
    discount_pct: vr.vendor_discount_pct != null ? Number(vr.vendor_discount_pct) : null,
    pricing_unit: vr.pricing_unit || 'Per Single Roll', pricing_model: vr.pricing_model || null,
    pricing_notes: vr.pricing_notes || null, sample_price: vr.sample_price != null ? Number(vr.sample_price) : 4.25,
  };

  const products = [];
  let dropped = 0;
  for (const r of rows) {
    if (r.discontinued) { dropped++; continue; }
    const imgs = [...new Set([r.image_url, ...jarr(r.all_images)].filter(Boolean))];
    if (imgs.length === 0) { dropped++; continue; }
    const aiColors = jarr(r.ai_colors);
    const colorWords = [r.color_name, ...aiColors.map(c => c && c.name)].filter(Boolean);
    const bucket = bucketFrom(colorWords);
    const styles = jarr(r.ai_styles).map(cap);
    const pat = cap(clean(r.pattern_name)) || cap(r.mfr_sku);
    const col = clean(r.color_name) && r.color_name.toLowerCase() !== (r.pattern_name || '').toLowerCase() ? cap(r.color_name) : '';
    const title = `${pat}${col ? ' ' + col : ''}`.trim();
    const hex = r.dominant_color_hex || r.color_hex || (aiColors[0] && aiColors[0].hex) || null;
    products.push({
      handle: `${slug(pat)}-${slug(r.color_name) || 'x'}--${(r.dw_sku || '').toLowerCase()}`,
      dw_sku: r.dw_sku,
      sku: r.mfr_sku || null,
      title: clean(title),
      display_eyebrow: pat,
      display_name: col || pat,
      series: pat || null,
      color: clean(r.color_name) || null,
      book: clean(r.collection) || 'Mural Source',
      style: styles[0] || null,
      styles,
      tags: jarr(r.ai_tags),
      color_bucket: bucket,
      hue: bucket ? BUCKET_HUE[bucket] : null,
      hex,
      sat: null, val: bucket === 'white' ? 1 : bucket === 'black' ? 0 : 0.5,
      width: r.width || null,
      length: r.length || null,
      repeat: repeatStr(r),
      material: r.material || null,
      match: r.match_type || null,
      price: r.price_retail != null && Number(r.price_retail) > 0 ? Number(r.price_retail) : null,
      cost: r.price_trade != null && Number(r.price_trade) > 0 ? Number(r.price_trade) : null,
      price_code: null,
      body_html: clean(r.ai_description || `${pat} — a made-to-order scenic wallcovering mural from Mural Source.`),
      swatch: imgs[0] || null,
      room: imgs[1] || imgs[0] || null,
      images: imgs.slice(0, 12),
      published_at: r.created_at,
      inquiry_sku: r.dw_sku,
    });
  }

  const facets = { books: {}, series: {}, colors: {}, styles: {}, materials: {} };
  for (const p of products) {
    if (p.book) facets.books[p.book] = (facets.books[p.book] || 0) + 1;
    if (p.series) facets.series[p.series] = (facets.series[p.series] || 0) + 1;
    if (p.color_bucket) facets.colors[p.color_bucket] = (facets.colors[p.color_bucket] || 0) + 1;
    for (const s of p.styles) facets.styles[s] = (facets.styles[s] || 0) + 1;
    if (p.material) facets.materials[p.material] = (facets.materials[p.material] || 0) + 1;
  }
  const orderedColors = BUCKET_ORDER.filter(b => facets.colors[b]).map(b => [b, facets.colors[b]]);

  const snapshot = {
    built_at: new Date().toISOString(),
    source: 'dw_unified.muralsource_catalog (INTERNAL — not on Shopify · DTD verdict C 2026-07-26)',
    count: products.length,
    dropped_no_image: dropped,
    vendor: vendorMeta,
    facets: {
      total: products.length,
      books: Object.entries(facets.books).sort((a, b) => b[1] - a[1]),
      series: Object.entries(facets.series).sort((a, b) => b[1] - a[1]).slice(0, 400),
      colors: orderedColors,
      styles: Object.entries(facets.styles).sort((a, b) => b[1] - a[1]),
      materials: Object.entries(facets.materials).sort((a, b) => b[1] - a[1]),
    },
    products,
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot));
  console.log(`products.json -> ${OUT}`);
  console.log(`  products: ${products.length} | dropped (disc/no-image): ${dropped}`);
  console.log(`  color buckets: ${orderedColors.map(c => c[0] + ':' + c[1]).join(', ')}`);
  console.log(`  patterns: ${snapshot.facets.series.length} | styles: ${snapshot.facets.styles.length}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });