`scripts/build-products-json.js`

207 lines
#!/usr/bin/env node
/**
 * INTERNAL Schumacher line viewer data — build data/products.json from dw_unified.shopify_products
 * (vendor ILIKE '%schumacher%'). Schumacher is INTERNAL-ONLY (Steve HARD RULE 2026-07-16): the
 * whole line is archived off the live storefront and lives ONLY here. Self-contained internal PDPs;
 * NO shopify.com / schumacher.com URLs emitted. $0 (local PG read). Facets derived from product_type
 * (book) + AI-Analyzed tags (color bucket + style). Includes the shopify `status` per row as an
 * internal signal (active-was / archived / draft). DELETED_FROM_SHOPIFY + image-less rows are dropped.
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const OUT = path.join(__dirname, '..', 'data', 'products.json');

// Facet slice limits
const MAX_SERIES = 300;
const MAX_STYLES =  60;

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', oatmeal: 'white',
  grey: 'grey', gray: 'grey', silver: 'grey', neutral: 'grey', taupe: 'grey', greige: 'grey', dove: 'grey',
  black: 'black', charcoal: 'black', ebony: 'black', 'midnight blue': 'blue', midnightblue: 'blue',
  red: 'red', burgundy: 'red', garnet: 'red', crimson: 'red', paprika: 'orange',
  orange: 'orange', rust: 'orange', copper: 'orange', terracotta: 'orange', coral: 'orange', peach: 'orange',
  brown: 'brown', tan: 'brown', bronze: 'brown', chocolate: 'brown', walnut: 'brown',
  gold: 'gold', yellow: 'gold', mustard: 'gold', ochre: 'gold', 'soft gold': 'gold', amber: 'gold',
  green: 'green', olive: 'green', sage: 'green', emerald: 'green', celadon: 'green', 'sage green': 'green', 'sea green': 'green', 'light green': 'green',
  teal: 'teal', turquoise: 'teal', aqua: 'teal',
  blue: 'blue', navy: 'blue', indigo: 'blue', "robin's egg": 'blue', cobalt: 'blue',
  purple: 'purple', violet: 'purple', lavender: 'purple', plum: 'purple', aubergine: 'purple',
  pink: 'pink', rose: 'pink', blush: 'pink', magenta: 'pink', 'pale peach': 'orange',
};
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 };

// Known interior-design style vocabulary (drives the Style facet + sort). Only these tags count as styles.
const STYLES = new Set(['traditional','contemporary','modern','transitional','geometric','floral','botanical',
  'chinoiserie','damask','stripe','striped','ikat','toile','trellis','arts & crafts','art deco','minimalist',
  'english country','scenic','abstract','animal','texture','grasscloth','moiré','plaid','paisley','ogee',
  'medallion','mid-century','bohemian','coastal','tropical','novelty','metallic','ombre','check','herringbone']);

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();

// normalize the messy product_type strings into one clean "book" (collection) chip
function bookFor(pt) {
  const t = (pt || '').toLowerCase();
  if (t.includes('memo') || t.includes('sample')) return 'Memo Sample';
  if (t.includes('trim'))    return 'Trim';
  if (t.includes('fabric'))  return 'Fabric';
  if (t.includes('pillow'))  return 'Pillow';
  return 'Wallcovering';
}

function parseTags(raw) {
  if (!raw) return [];
  const s = String(raw).trim();
  if (s.startsWith('{') && s.endsWith('}')) {           // pg array text form {"a","b"}
    return s.slice(1, -1).split(',').map(t => t.replace(/^"|"$/g, '').trim()).filter(Boolean);
  }
  return s.split(',').map(t => t.trim()).filter(Boolean); // csv form
}

function parseMetafields(raw) {
  try { return typeof raw === 'string' ? JSON.parse(raw) : (raw || {}); } catch { return {}; }
}

function colorFromTags(tags) {
  // prefer an explicit "color:Xyz" structured tag, else first bucket-matching tag
  let name = null, bucket = null;
  for (const t of tags) {
    const m = /^color:(.+)$/i.exec(t);
    if (m) { name = cap(m[1]); const b = BUCKET[m[1].toLowerCase().trim()]; if (b) bucket = b; }
  }
  for (const t of tags) {
    const key = t.toLowerCase().trim();
    if (BUCKET[key]) { bucket = bucket || BUCKET[key]; name = name || cap(t); if (name && bucket) break; }
  }
  return { name, bucket };
}

function styleFromTags(tags) {
  const out = [];
  for (const t of tags) { if (STYLES.has(t.toLowerCase().trim())) out.push(cap(t)); }
  return [...new Set(out)];
}

/** Run a query with a no-rows fallback on error (e.g. missing table in some envs). */
async function querySafe(q, params) {
  return (await pool.query(q, params).catch(() => ({ rows: [] }))).rows[0] || {};
}

/** Increment a facet counter. */
function tally(obj, key) { if (key) obj[key] = (obj[key] || 0) + 1; }

async function main() {
  const { rows } = await pool.query(`
    SELECT dw_sku, mfr_sku, handle, title, product_type, body_html, tags, metafields,
           image_url, status, created_at_shopify AS created_at
    FROM shopify_products
    WHERE vendor ILIKE '%schumacher%'
      AND image_url IS NOT NULL AND image_url <> ''
      AND COALESCE(upper(status),'') <> 'DELETED_FROM_SHOPIFY'
    ORDER BY title, dw_sku
  `);

  // vendor ops meta for the internal PDP (Call Vendor / Our Account #). Defaults are fine if absent.
  const vr = await querySafe(
    `SELECT vendor_name, vendor_discount_pct, pricing_unit, pricing_model, pricing_notes, sample_price
     FROM vendor_registry WHERE vendor_code = $1`, ['schumacher']);
  const fm = await querySafe(
    `SELECT phone, email_1, account_num FROM fmpro
     WHERE vendor_name ILIKE $1 AND account_num IS NOT NULL LIMIT 1`,
    ['%schumacher%']);
  const vendorMeta = {
    name: vr.vendor_name || 'Schumacher',
    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) : 15, // Schumacher trade = 15%
    pricing_unit: vr.pricing_unit || null, pricing_model: vr.pricing_model || 'quote',
    pricing_notes: vr.pricing_notes || 'Schumacher is internal-only; price/stock via vendor request.',
    sample_price: vr.sample_price != null ? Number(vr.sample_price) : 4.25,
  };

  const products = [];
  const facets = { books: {}, series: {}, colors: {}, styles: {}, statuses: {} };

  for (const r of rows) {
    const tags = parseTags(r.tags);
    const mf = parseMetafields(r.metafields);
    const patternName = mf?.custom?.pattern_name?.value
      || (r.title || '').split(/\s*[|—-]\s*/)[0].trim() || null;
    const { name: color, bucket } = colorFromTags(tags);
    const styles = styleFromTags(tags);
    const book = bookFor(r.product_type);

    products.push({
      handle: `${r.handle || 'schu'}--${(r.dw_sku || '').toLowerCase()}`,
      dw_sku: r.dw_sku,
      sku: (r.mfr_sku || '').split('__')[0] || null,
      title: clean(r.title),
      display_eyebrow: clean(patternName || ''),
      display_name: clean(r.title),
      series: clean(patternName) || null,
      color: color || null,
      book,
      style: styles[0] || null,
      styles,
      color_bucket: bucket,
      hue: bucket ? BUCKET_HUE[bucket] : null,
      hex: null, sat: null, val: bucket === 'white' ? 1 : bucket === 'black' ? 0 : 0.5,
      width: mf?.sec?.width?.value || mf?.custom?.width?.value || null,
      length: null,
      repeat: mf?.sec?.repeat?.value || null,
      material: mf?.custom?.material?.value || mf?.sec?.material?.value || null,
      match: null,
      price: null, cost: null, price_code: null,
      body_html: clean(r.body_html || ''),
      status: r.status || null,                         // internal signal: ACTIVE(was)/ARCHIVED/DRAFT
      swatch: r.image_url, room: r.image_url, images: [r.image_url],
      published_at: r.created_at,
      inquiry_sku: r.dw_sku,
    });

    tally(facets.books, book);
    tally(facets.series, clean(patternName) || null);
    tally(facets.colors, bucket);
    for (const s of styles) tally(facets.styles, s);
    tally(facets.statuses, r.status || null);
  }

  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.shopify_products vendor~Schumacher (INTERNAL-ONLY — archived off Shopify)',
    count: products.length,
    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, MAX_SERIES),
      colors:   orderedColors,
      styles:   Object.entries(facets.styles).sort((a, b) => b[1] - a[1]).slice(0, MAX_STYLES),
      statuses: Object.entries(facets.statuses).sort((a, b) => b[1] - a[1]),
    },
    products,
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot));
  console.log(`products.json -> ${OUT}`);
  console.log(`  products: ${products.length}`);
  console.log(`  books: ${snapshot.facets.books.map(b => b[0] + ':' + b[1]).join(', ')}`);
  console.log(`  colors: ${orderedColors.map(c => c[0] + ':' + c[1]).join(', ')}`);
  console.log(`  styles: ${snapshot.facets.styles.slice(0, 8).map(s => s[0] + ':' + s[1]).join(', ')}`);
  console.log(`  statuses: ${snapshot.facets.statuses.map(s => s[0] + ':' + s[1]).join(', ')}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });