`scripts/build-products-json.js`

164 lines
#!/usr/bin/env node
/**
 * Workstream B data — build data/products.json from the internal dw_unified astek_catalog.
 * These products are deliberately NOT on Shopify. The landing serves its OWN internal PDPs.
 * No vendor (astek.com) URLs, no Shopify URLs are 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 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 });

// Map Astek color_ tags -> our canonical color buckets (drives the color filter + color sort).
const BUCKET = {
  white: 'white', ivory: 'white', cream: 'white', beige: 'white',
  grey: 'grey', gray: 'grey', silver: 'grey', neutral: 'grey', taupe: 'grey',
  black: 'black', charcoal: 'black',
  red: 'red', burgundy: 'red',
  orange: 'orange', rust: 'orange', copper: 'orange', terracotta: 'orange',
  brown: 'brown', tan: 'brown', bronze: 'brown', chocolate: 'brown',
  gold: 'gold', yellow: 'gold', mustard: 'gold',
  green: 'green', olive: 'green', sage: 'green', emerald: 'green',
  teal: 'teal', turquoise: 'teal', aqua: 'teal',
  blue: 'blue', navy: 'blue', indigo: 'blue',
  purple: 'purple', violet: 'purple', lavender: 'purple', plum: 'purple',
  pink: 'pink', rose: 'pink', blush: 'pink', magenta: 'pink',
};
// order buckets so the color-wheel sort reads neutrals-first then chromatic
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 customer-facing string (titles, colors, descriptions)
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();

function bucketFor(tags) {
  for (const t of (tags || [])) {
    const key = (t || '').toLowerCase().trim();
    if (BUCKET[key]) return BUCKET[key];
  }
  return null;
}

async function main() {
  // one row per (dw_sku) colorway variant; drop the rare image-less rows from the public grid
  const { rows } = await pool.query(`
    SELECT dw_sku, mfr_sku, handle, pattern_name, color_name, title, product_type,
           description_text, width, length, repeat, material, match_type, price, cost,
           color_tags, style_tags, all_images, image_url, variant_image, created_at
    FROM astek_catalog
    ORDER BY pattern_name, color_name, dw_sku
  `);

  // Vendor ops meta — drives the internal PDP (Call Vendor, Our Account #, pricing
  // basis). vendor_registry holds the line's discount/pricing model; fmpro (the
  // FileMaker mirror) holds phone + our account number with the vendor.
  const VENDOR_CODE_REG = 'astek';
  const FM_VENDOR_NAME = 'Astek';
  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: vr.vendor_name || FM_VENDOR_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 imgs = (r.all_images || []).filter(Boolean);
    if (imgs.length === 0) { dropped++; continue; }               // no image → not shown on public grid
    const bucket = bucketFor(r.color_tags);
    // internal-only, self-contained: NO shopify url, NO astek.com url.
    products.push({
      handle: `${r.handle}--${(r.dw_sku || '').toLowerCase()}`,   // unique internal PDP handle per colorway
      dw_sku: r.dw_sku,
      // Astek jams color tags into variant skus ("AD752-1__black__gold…") — the
      // real vendor SKU is the part before the first "__". Never show the junk.
      sku: (r.mfr_sku || '').split('__')[0] || null,
      title: clean(r.title),
      display_eyebrow: clean(r.pattern_name || ''),
      display_name: clean(r.color_name || r.pattern_name || r.title),
      series: clean(r.pattern_name) || null,                       // pattern = "series" for pairing/sort
      color: clean(r.color_name) || null,
      book: r.product_type || 'Astek',                             // Digital / Traditional etc → collection chip
      // style facet + Style sort — primary tag is the scalar the generic filter/sort
      // machinery uses; the full tag list rides along for search.
      style: (r.style_tags || [])[0] ? cap(r.style_tags[0]) : null,
      styles: (r.style_tags || []).map(cap),
      color_bucket: bucket,
      hue: bucket ? BUCKET_HUE[bucket] : null,
      hex: null,                                                   // no per-sku hex extraction (tag-bucket only)
      sat: null, val: bucket === 'white' ? 1 : bucket === 'black' ? 0 : 0.5,
      width: r.width || null,
      length: r.length || null,
      repeat: r.repeat || null,
      material: r.material || null,
      match: r.match_type || null,
      // internal pricing — list/cost straight from the catalog row; the vendor's
      // letter price-code (e.g. "Price Code M") parsed from the spec prose.
      price: r.price != null && Number(r.price) > 0 ? Number(r.price) : null,
      cost: r.cost != null && Number(r.cost) > 0 ? Number(r.cost) : null,
      price_code: (() => { const m = (r.description_text || '').match(/Price\s*Code\s+([A-Z0-9]{1,3})\b/i); return m ? m[1].toUpperCase() : null; })(),
      body_html: clean(r.description_text || ''),
      swatch: imgs[0] || null,
      room: imgs[1] || imgs[0] || null,
      images: imgs.slice(0, 12),
      published_at: r.created_at,
      // memo-sample / inquiry — the internal CTA target (no checkout, no external store)
      inquiry_sku: r.dw_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.astek_catalog (INTERNAL — not on Shopify)',
    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(`  color buckets: ${orderedColors.map(c => c[0] + ':' + c[1]).join(', ')}`);
  console.log(`  patterns: ${snapshot.facets.series.length} | books: ${snapshot.facets.books.length}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });