`scripts/build-products-json.js`

207 lines
#!/usr/bin/env node
/**
 * Build data/products.json from the internal dw_unified momentum_colorways table.
 * The ENTIRE table IS the Hollywood Wallcoverings line (Momentum→Hollywood private
 * label — 10,985 rows, Wallcovering + Acoustic). These are NOT on Shopify; this
 * internal viewer serves its OWN grid. $0 (local PG read).
 *
 * PUBLIC-facing private-label brand = "Hollywood Wallcoverings". The real upstream
 * vendor ("Momentum" / "Versa" / "Versa Designed Surfaces") must NEVER appear on
 * any customer-facing surface. This is an INTERNAL gated tool, so we DO show the
 * momentum_sku on the card (like astek shows the real vendor sku) — it stays behind
 * basic-auth and never leaks to the public.
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const OUT = path.join(__dirname, '..', 'data', 'products.json');
const PW = (() => {
  try {
    const env = fs.readFileSync(require('os').homedir() + '/Projects/secrets-manager/.env', 'utf8');
    const m = env.match(/^DW_ADMIN_DB_PASSWORD=(.*)$/m);
    if (m) return m[1].replace(/^["']|["']$/g, '').trim();
  } catch { /* fall through */ }
  return process.env.PGPASSWORD || 'DW2024!';
})();
const pool = new Pool({ host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PW });

// Map an ai_color_name / color_name -> canonical color bucket (drives color filter + color sort).
const BUCKET = {
  white: 'white', ivory: 'white', cream: 'white', beige: 'white', alabaster: 'white', oatmeal: 'white', linen: 'white', snow: 'white', pearl: 'white',
  grey: 'grey', gray: 'grey', silver: 'grey', neutral: 'grey', taupe: 'grey', greige: 'grey', stone: 'grey', slate: 'grey', pewter: 'grey', dove: 'grey', ash: 'grey', fog: 'grey',
  black: 'black', charcoal: 'black', ebony: 'black', onyx: 'black', noir: 'black',
  red: 'red', burgundy: 'red', crimson: 'red', scarlet: 'red', ruby: 'red', wine: 'red', brick: 'red',
  orange: 'orange', rust: 'orange', copper: 'orange', terracotta: 'orange', apricot: 'orange', amber: 'orange', tangerine: 'orange',
  brown: 'brown', tan: 'brown', bronze: 'brown', chocolate: 'brown', mocha: 'brown', coffee: 'brown', walnut: 'brown', cocoa: 'brown', khaki: 'brown', camel: 'brown', harvest: 'brown',
  gold: 'gold', yellow: 'gold', mustard: 'gold', honey: 'gold', wheat: 'gold', flax: 'gold', maize: 'gold',
  green: 'green', olive: 'green', sage: 'green', emerald: 'green', moss: 'green', fern: 'green', forest: 'green', celadon: 'green', mint: 'green',
  teal: 'teal', turquoise: 'teal', aqua: 'teal', seafoam: 'teal', peacock: 'teal',
  blue: 'blue', navy: 'blue', indigo: 'blue', cobalt: 'blue', denim: 'blue', sky: 'blue', azure: 'blue', ocean: 'blue', marine: 'blue',
  purple: 'purple', violet: 'purple', lavender: 'purple', plum: 'purple', mauve: 'purple', lilac: 'purple', aubergine: 'purple', eggplant: 'purple',
  pink: 'pink', rose: 'pink', blush: 'pink', magenta: 'pink', coral: 'pink', fuchsia: 'pink', salmon: '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 };

// banned-word scrub — applies to EVERY string that could surface on a public deploy.
// Strips the real upstream vendor names (Momentum / Versa) plus the wallpaper→wallcovering rule.
const clean = s => (s == null ? s : String(s)
  .replace(/\bVersa\s+Designed\s+Surfaces\b/gi, 'Hollywood Wallcoverings')
  .replace(/\bMomentum\s+Textiles?(?:\s*&?\s*Walls?)?\b/gi, 'Hollywood Wallcoverings')
  .replace(/\bMomentum\b/gi, 'Hollywood Wallcoverings')
  .replace(/\bVersa\b/gi, 'Hollywood')
  .replace(/\bWallpapers\b/gi, 'Wallcoverings')
  .replace(/\bWallpaper\b/gi, 'Wallcovering'));

const slug = s => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');

function bucketFor(...names) {
  for (const name of names) {
    if (!name) continue;
    const words = String(name).toLowerCase().split(/[^a-z]+/).filter(Boolean);
    for (const w of words) if (BUCKET[w]) return BUCKET[w];
  }
  return null;
}

// bucket list_price into a coarse price group for the left-panel facet table
function priceGroup(v) {
  if (v == null) return null;
  v = Number(v);
  if (!(v > 0)) return null;
  if (v < 25) return 'Under $25';
  if (v < 50) return '$25 - $50';
  if (v < 75) return '$50 - $75';
  if (v < 100) return '$75 - $100';
  if (v < 150) return '$100 - $150';
  if (v < 250) return '$150 - $250';
  return '$250+';
}
// fixed order so the price-group facet reads low→high, not alphabetical
const PRICE_GROUP_ORDER = ['Under $25', '$25 - $50', '$50 - $75', '$75 - $100', '$100 - $150', '$150 - $250', '$250+'];

async function main() {
  const { rows } = await pool.query(`
    SELECT dw_sku, momentum_sku, pattern_name, color_name, ai_color_name, ai_color_hex,
           category, pl_city_name, product_line, width, list_price,
           is_new, is_closeout, image_url, created_at
    FROM momentum_colorways
    ORDER BY pl_city_name, pattern_name, color_name, dw_sku
  `);

  // Vendor ops meta for the internal PDP (Call Vendor / Our Account # / pricing basis).
  // Look the line up in vendor_registry / fmpro under its REAL vendor name (internal only).
  const VENDOR_CODE_REG = 'momentum';
  const FM_VENDOR_NAME = 'Momentum';
  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`, [VENDOR_CODE_REG])).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 AND phone IS NOT NULL LIMIT 1`,
    [FM_VENDOR_NAME])).rows[0] || {};
  const vendorMeta = {
    name: 'Hollywood Wallcoverings',                 // public-safe display name
    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 || null,
    pricing_model: vr.pricing_model || null,
    pricing_notes: vr.pricing_notes || null,
    sample_price: vr.sample_price != null ? Number(vr.sample_price) : null,
  };

  const products = [];
  let dropped = 0;
  for (const r of rows) {
    const img = r.image_url || null;
    if (!img) { dropped++; continue; }                      // no image → not shown on the grid
    const dwsku = r.dw_sku || '';
    const displayColor = r.ai_color_name || r.color_name || null;   // prefer AI color name
    const bucket = bucketFor(r.ai_color_name, r.color_name);
    const status = r.is_closeout ? 'Closeout' : (r.is_new ? 'New' : null);
    const price = r.list_price != null && Number(r.list_price) > 0 ? Number(r.list_price) : null;
    products.push({
      handle: slug(dwsku),                                  // e.g. dwhd-508083
      dw_sku: r.dw_sku,
      momentum_sku: r.momentum_sku || null,                 // internal only (behind basic-auth)
      sku: r.momentum_sku || null,                          // alias for generic card machinery
      title: clean([r.pattern_name, displayColor].filter(Boolean).join(' - ')) || dwsku,
      display_eyebrow: clean(r.pattern_name || ''),
      display_name: clean(displayColor || r.pattern_name || dwsku),
      pattern_name: clean(r.pattern_name) || null,
      series: clean(r.pattern_name) || null,                // pattern = "series" for pairing/sort
      color_name: clean(r.color_name) || null,
      ai_color_name: clean(r.ai_color_name) || null,
      color: clean(displayColor) || null,
      category: r.category || null,                          // Wallcovering | Acoustic
      collection: clean(r.pl_city_name) || null,             // pl_city_name = the collection
      book: r.category || 'Wallcovering',                    // category drives the "book" chip
      product_line: r.product_line || null,
      style: null, styles: [],
      color_bucket: bucket,
      hue: bucket ? BUCKET_HUE[bucket] : null,
      hex: r.ai_color_hex || null,
      sat: null, val: bucket === 'white' ? 1 : bucket === 'black' ? 0 : 0.5,
      width: r.width ? String(r.width) : null,
      is_new: !!r.is_new,
      is_closeout: !!r.is_closeout,
      status,
      price,
      price_group: priceGroup(price),
      swatch: img,
      room: img,
      images: [img],
      created_at: r.created_at || null,
      published_at: r.created_at || null,
      inquiry_sku: r.dw_sku,
    });
  }

  // Facet counts for the left-panel tables. FIELDS list mirrors the frontend.
  const FACET_FIELDS = ['category', 'collection', 'series', 'color', 'product_line', 'width', 'status', 'price_group', 'color_bucket'];
  const facets = {};
  for (const f of FACET_FIELDS) facets[f] = {};
  for (const p of products) {
    for (const f of FACET_FIELDS) {
      const v = p[f];
      if (v == null || v === '') continue;
      facets[f][v] = (facets[f][v] || 0) + 1;
    }
  }
  const orderedColors = BUCKET_ORDER.filter(b => facets.color_bucket[b]).map(b => [b, facets.color_bucket[b]]);
  const orderedPrice = PRICE_GROUP_ORDER.filter(g => facets.price_group[g]).map(g => [g, facets.price_group[g]]);

  const snapshot = {
    built_at: new Date().toISOString(),
    source: 'dw_unified.momentum_colorways (INTERNAL - Hollywood Wallcoverings private label, not on Shopify)',
    count: products.length,
    dropped_no_image: dropped,
    vendor: vendorMeta,
    facets: {
      total: products.length,
      category: Object.entries(facets.category).sort((a, b) => b[1] - a[1]),
      collection: Object.entries(facets.collection).sort((a, b) => b[1] - a[1]),
      series: Object.entries(facets.series).sort((a, b) => b[1] - a[1]).slice(0, 400),
      color: Object.entries(facets.color).sort((a, b) => b[1] - a[1]).slice(0, 400),
      product_line: Object.entries(facets.product_line).sort((a, b) => b[1] - a[1]),
      width: Object.entries(facets.width).sort((a, b) => b[1] - a[1]),
      status: Object.entries(facets.status).sort((a, b) => b[1] - a[1]),
      price_group: orderedPrice,
      colors: orderedColors,          // legacy alias
      color_bucket: orderedColors,
    },
    products,
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot));
  console.log(`products.json -> ${OUT}`);
  console.log(`  products: ${products.length} | dropped (no image): ${dropped}`);
  console.log(`  categories: ${snapshot.facets.category.map(c => c[0] + ':' + c[1]).join(', ')}`);
  console.log(`  color buckets: ${orderedColors.map(c => c[0] + ':' + c[1]).join(', ')}`);
  console.log(`  collections: ${snapshot.facets.collection.length} | patterns: ${snapshot.facets.series.length}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });