`scripts/build-viewer-snapshot.mjs`

175 lines
#!/usr/bin/env node
// build-viewer-snapshot.mjs — merge greenland_catalog + enriched-cork.json + shopify mirror
// into ONE internal-viewer payload (public/viewer.json). $0 local. No hotlinks (images local).
//
//   node scripts/build-viewer-snapshot.mjs
//
// Sources:
//   - dw_unified.greenland_catalog     : canonical specs, gl_id (live-stock key), price, status, created
//   - data/enriched-cork.json          : hex, topColors[], colorFamily (hue bucket), city (style), tags
//   - dw_unified.shopify_products      : handle, shopify_id (Shopify links), live status  [keyed Cork-<n>-Sample]
//
// Pricing reality (per greenland-scraper-manager skill + vendor_registry):
//   RETAIL is canonical $59.50/yd, 6-yd increments. NET COST is NOT yet known — Greenland
//   wholesale cost has not been supplied and vendor_registry.vendor_discount_pct is blank/unconfirmed.
//   So net cost / discount / margin are surfaced HONESTLY as "unconfirmed" — never invented.

import { execFileSync } from 'node:child_process';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'public', 'viewer.json');
const ENRICHED = path.join(ROOT, 'data', 'enriched-cork.json');

const DB = (process.env.DATABASE_URL ||
  'postgresql:///dw_unified?host=/tmp&user=stevestudio2');

function psql(sql) {
  const out = execFileSync('psql', [DB, '-tAc', sql], { encoding: 'utf8', maxBuffer: 64 * 1024 * 1024 });
  return out.trim();
}
// JSON aggregate straight out of PG for the catalog + shopify mirror.
function psqlJson(sql) {
  const raw = psql(sql);
  return raw ? JSON.parse(raw) : [];
}

// ── 1) canonical catalog rows ──────────────────────────────────────────────
const catalog = psqlJson(`
  SELECT COALESCE(json_agg(row_to_json(t)), '[]')
  FROM (
    SELECT
      dw_sku, dw_number, gl_id,
      (raw->>'parent_id')::int AS gl_product_id,   -- parent product id = live-stock key
      mfr_sku, pattern_name, color_name, collection,
      material, use, width, width_inches, backing, composition, pattern_repeat,
      fire_rating_us, weight, minimum_order, category_name, subcategory_names,
      product_type, product_url, description,
      price_per_yard, min_increment_yards, sold_per, status,
      first_seen_at, is_publish
    FROM greenland_catalog
    WHERE dw_sku LIKE 'Cork-%'
    ORDER BY dw_number
  ) t`);

// ── 2) shopify mirror (handle / shopify_id / live status) keyed by base Cork- SKU ──
const shopRows = psqlJson(`
  SELECT COALESCE(json_agg(row_to_json(t)), '[]')
  FROM (
    SELECT
      regexp_replace(sku,'-Sample$','') AS base_sku,
      handle,
      split_part(shopify_id,'/',5) AS shopify_pid,
      status AS shop_status,
      online_store_published,
      min_variant_price AS sample_price
    FROM shopify_products
    WHERE sku LIKE 'Cork-5%-Sample'
  ) t`);
const shopBy = new Map(shopRows.map(r => [r.base_sku, r]));

// ── 3) local enrichment (hex, palette, colorFamily/hue, city/style, tags) ──
const enrichedArr = JSON.parse(fs.readFileSync(ENRICHED, 'utf8'));
const enrBy = new Map(enrichedArr.map(e => [e.dwSku, e]));

// Vendor/pricing facts (single source of truth for the pricing card).
const RETAIL_PER_YARD = 59.50;
const MIN_INCREMENT = 6;
const SAMPLE_PRICE = 4.25;

function localImage(dwSku) {
  const rel = `img/${dwSku}.jpg`;
  return fs.existsSync(path.join(ROOT, 'public', rel)) ? '/' + rel : null;
}

const products = catalog.map(c => {
  const enr = enrBy.get(c.dw_sku) || {};
  const shop = shopBy.get(c.dw_sku) || {};
  const retail = c.price_per_yard != null ? Number(c.price_per_yard) : RETAIL_PER_YARD;
  const step = c.min_increment_yards || MIN_INCREMENT;

  return {
    // identity
    dw_sku: c.dw_sku,
    dw_number: c.dw_number,
    gl_id: c.gl_id,                       // SKU-level id (reference)
    gl_product_id: c.gl_product_id,       // parent product id → live-stock re-check key
    mfr_sku: (c.mfr_sku || enr.mfrSku || '').trim(),
    mfr_model: enr.mfrModel || null,
    // naming (customer-facing = Phillipe Romano; city = style)
    pattern: c.pattern_name || enr.rawColor || null,
    color: enr.cleanColor || c.color_name || null,
    city: enr.city || null,              // coastal-city STYLE
    collection: c.collection || enr.collectionName || null,
    collection_code: enr.collectionCode || null,
    title: enr.title || null,
    // specs
    material: c.material || enr.material || 'Cork',
    use: c.use || enr.use || null,
    width: c.width || enr.width || null,
    width_inches: c.width_inches != null ? Number(c.width_inches) : null,
    backing: c.backing || enr.backing || null,
    composition: c.composition || null,
    repeat: c.pattern_repeat || enr.repeat || null,
    fire_rating: c.fire_rating_us || enr.fireRating || null,
    weight: c.weight || enr.weight || null,
    minimum_order: c.minimum_order || enr.minimumOrder || null,
    country: c.category_name ? null : null, // country-of-origin not in feed; left null
    category: c.category_name || null,
    subcategory: c.subcategory_names || null,
    product_type: c.product_type || 'Wallcovering',
    description: enr.description || c.description || null,
    // color / hue
    hex: enr.hex || null,
    top_colors: enr.topColors || null,
    color_family: enr.colorFamily || null,   // hue bucket
    tags: enr.tags || null,
    // image (LOCAL ONLY — never hotlink)
    image: localImage(c.dw_sku),
    // pricing (internal): retail canonical, cost/discount honestly unconfirmed
    retail_per_yard: retail,
    min_increment_yards: step,
    sold_per: c.sold_per || 'Yard',
    sample_price: SAMPLE_PRICE,
    net_cost: null,                        // Greenland wholesale not supplied
    trade_discount_pct: null,              // vendor_registry unconfirmed
    cost_status: 'unconfirmed',            // drives the pricing card badge
    // stock (snapshot from feed's isPublish; live re-check via /api/stock)
    in_catalog: c.is_publish === 1 || c.is_publish === true,
    stock_snapshot: (c.is_publish === 1 || c.is_publish === true) ? 'in_catalog' : 'unlisted',
    // status + links
    status: c.status || 'active',
    shop_status: shop.shop_status || null,
    online_published: shop.online_store_published === true || shop.online_store_published === 't',
    handle: shop.handle || null,
    shopify_pid: shop.shopify_pid || null,
    vendor_url: c.product_url || enr.productUrl || null,
    created_at: c.first_seen_at || null,
  };
});

const facetFields = ['collection', 'color_family', 'material', 'use', 'width', 'fire_rating', 'backing', 'composition', 'city'];
const snapshot = {
  generated_at: new Date().toISOString(),
  vendor_internal: 'Greenland',           // internal-only label
  brand_public: 'Phillipe Romano',
  line: 'Cork',
  retail_per_yard: RETAIL_PER_YARD,
  min_increment_yards: MIN_INCREMENT,
  sample_price: SAMPLE_PRICE,
  count: products.length,
  facet_fields: facetFields,
  products,
};

fs.writeFileSync(OUT, JSON.stringify(snapshot));
const withImg = products.filter(p => p.image).length;
const withHex = products.filter(p => p.hex).length;
const withShop = products.filter(p => p.handle).length;
console.log(`[snapshot] ${products.length} cork SKUs → ${OUT}`);
console.log(`[snapshot] local images ${withImg}/${products.length} · hue/hex ${withHex} · shopify-linked ${withShop}`);
console.log(`[snapshot] pricing: retail $${RETAIL_PER_YARD}/yd (min ${MIN_INCREMENT}yd) · net cost=unconfirmed · discount=unconfirmed`);