`scripts/build-products-json.js`

158 lines
#!/usr/bin/env node
/**
 * Internal Quadrille HOUSE viewer data — build data/products.json from the local
 * dw_unified.quadrille_house_catalog (1,942 rows across 8 lines: Quadrille,
 * Alan Campbell, Suncloth, Home Couture, Plains, Charles Burger, Cloth and Paper,
 * Grasscloth). INTERNAL ONLY — never customer-facing. Quadrille shows zero prices
 * online, so pricing rides the Get Price active-request flow. $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 PW = (() => {
  const env = fs.readFileSync(require('os').homedir() + '/Projects/secrets-manager/.env', 'utf8');
  const m = env.match(/^DW_ADMIN_DB_PASSWORD=(.*)$/m);
  return m ? m[1].replace(/^["']|["']$/g, '').trim() : (process.env.PGPASSWORD || '');
})();
const pool = new Pool({ host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PW });

const BUCKET = {
  white: 'white', ivory: 'white', cream: 'white', beige: 'white', alabaster: 'white', oyster: 'white',
  grey: 'grey', gray: 'grey', silver: 'grey', neutral: 'grey', taupe: 'grey', greige: 'grey', stone: 'grey',
  black: 'black', charcoal: 'black', ebony: 'black',
  red: 'red', burgundy: 'red', crimson: 'red', brick: 'red',
  orange: 'orange', rust: 'orange', copper: 'orange', terracotta: 'orange', coral: 'orange',
  brown: 'brown', tan: 'brown', bronze: 'brown', chocolate: 'brown', mocha: 'brown', cognac: 'brown',
  gold: 'gold', yellow: 'gold', mustard: 'gold', honey: 'gold', amber: 'gold',
  green: 'green', olive: 'green', sage: 'green', emerald: 'green', moss: 'green', forest: 'green', lime: 'green',
  teal: 'teal', turquoise: 'teal', aqua: 'teal',
  blue: 'blue', navy: 'blue', indigo: 'blue', cobalt: 'blue', denim: 'blue',
  purple: 'purple', violet: 'purple', lavender: 'purple', plum: 'purple', mauve: '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 titleCase = s => (s || '').replace(/\w\S*/g, t => t.charAt(0).toUpperCase() + t.slice(1).toLowerCase());
const slug = s => (s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
const parseArr = v => { if (!v) return []; if (Array.isArray(v)) return v; try { const j = JSON.parse(v); return Array.isArray(j) ? j : []; } catch { return []; } };

function bucketFrom(candidates) {
  for (const c of candidates) {
    if (!c) continue;
    const key = String(c).toLowerCase().trim();
    if (BUCKET[key]) return BUCKET[key];
    for (const w of key.split(/[\s\-/]+/)) if (BUCKET[w]) return BUCKET[w];
  }
  return null;
}

// Vendor ops meta — registry (discount/pricing model) + fmpro (phone/email/our
// account #). Account values that are really notes are rejected by shape.
async function fetchVendorMeta(vendorCode, fmName) {
  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`, [vendorCode])).rows[0] || {};
  const fm = (await pool.query(`SELECT phone, email_1, account_num FROM fmpro WHERE vendor_name ILIKE $1 AND (phone IS NOT NULL OR email_1 IS NOT NULL) LIMIT 1`, [fmName])).rows[0] || {};
  const acct = (fm.account_num || '').trim();
  const acctOk = /^[A-Za-z0-9][A-Za-z0-9\-\/. ]{1,19}$/.test(acct) && !/discount|resale|on file|%/i.test(acct);
  return {
    name: vr.vendor_name || fmName,
    phone: fm.phone || null,
    email: fm.email_1 || null,
    account_number: acctOk ? acct : 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,
  };
}

async function main() {
  const { rows } = await pool.query(`
    SELECT mfr_sku, dw_sku, brand, pattern_name, color_name, colorway, collection, product_type,
           material, composition, width, pattern_repeat, match_type,
           description, ai_description, ai_colors, ai_tags, color_hex, dominant_color_hex,
           image_url, all_images, cost_price, our_price, crawled_at
    FROM quadrille_house_catalog
    WHERE COALESCE(image_rejected,false) = false AND COALESCE(dedup_skip,false) = false
    ORDER BY brand, pattern_name, color_name, mfr_sku
  `);
  const vendorMeta = await fetchVendorMeta('quadrille', 'Quadrille');

  const products = [];
  let dropped = 0;
  for (const r of rows) {
    let imgs = parseArr(r.all_images).filter(x => typeof x === 'string' && x.startsWith('http'));
    if (r.image_url && !imgs.includes(r.image_url)) imgs.unshift(r.image_url);
    imgs = [...new Set(imgs)];
    if (imgs.length === 0) { dropped++; continue; }

    const aiColors = parseArr(r.ai_colors);
    const pattern = titleCase(clean(r.pattern_name || ''));
    const colorway = titleCase(clean(r.color_name || r.colorway || ''));
    const bucket = bucketFrom([r.color_name, r.colorway, ...aiColors.map(c => c && c.name), ...parseArr(r.ai_tags)]);
    products.push({
      handle: `${slug(pattern || r.mfr_sku)}--${slug(r.dw_sku || r.mfr_sku)}`,
      dw_sku: r.dw_sku || null,
      sku: r.mfr_sku,
      title: clean(pattern ? `${pattern}${colorway ? ' ' + colorway : ''} | ${r.brand}` : `${r.mfr_sku} | ${r.brand}`),
      display_eyebrow: pattern || null,
      display_name: colorway || pattern || r.mfr_sku,
      series: pattern || null,
      color: colorway || null,
      book: r.brand || 'Quadrille',                                 // house line = collection chip
      color_bucket: bucket,
      hue: bucket ? BUCKET_HUE[bucket] : null,
      hex: r.color_hex || r.dominant_color_hex || (aiColors[0] && aiColors[0].hex) || null,
      sat: null, val: bucket === 'white' ? 1 : bucket === 'black' ? 0 : 0.5,
      width: clean(r.width) || null,
      length: null,
      repeat: clean(r.pattern_repeat) || null,
      material: clean(r.material || r.composition) || null,
      match: clean(r.match_type) || null,
      price: r.our_price != null && Number(r.our_price) > 0 ? Number(r.our_price) : null,
      cost: r.cost_price != null && Number(r.cost_price) > 0 ? Number(r.cost_price) : null,
      price_code: null,
      body_html: clean(r.ai_description || r.description || ''),
      swatch: imgs[0] || null,
      room: imgs[1] || imgs[0] || null,
      images: imgs.slice(0, 12),
      published_at: r.crawled_at,
      inquiry_sku: r.dw_sku || r.mfr_sku,
    });
  }

  const facets = { books: {}, series: {}, colors: {} };
  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;
  }
  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.quadrille_house_catalog (INTERNAL — Quadrille house, 8 lines)',
    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, 300),
      colors: orderedColors,
    },
    products,
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot));
  console.log(`products.json -> ${OUT}`);
  console.log(`  products: ${products.length} | dropped (no image): ${dropped}`);
  console.log(`  lines: ${snapshot.facets.books.map(b => b[0] + ':' + b[1]).join(', ')}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });