`scripts/build-color-index.cjs`

100 lines
#!/usr/bin/env node
/**
 * build-color-index.cjs — generate the catalog-wide color index for the PDP
 * color-dot "index of many products within 10% tolerance" feature.
 *
 * Source of truth: dw_unified.product_colors (one dominant color per product,
 * with a PRECOMPUTED CIELAB lab_l/a/b) for the COLOR, JOINED to dw_unified.
 * shopify_products for LIVE TRUTH (current status + current featured image).
 * product_colors.status/image_url are populated by manual per-vendor extracts
 * and go stale (an archived product can still read status='active' with a dead
 * CDN image_url), which used to leak broken cards + 404 links into the PDP color
 * grid. We therefore gate on shopify_products.status='active' (synced from live
 * Shopify) and prefer its current image_url. We emit ONE row per active product
 * with a real hex + LAB + handle + image, so the /apps/color-index endpoint can
 * do a pure perceptual ΔE filter with no DB dependency at request time.
 *
 * Output: data/color-index.json  → { generated_at, count, items:[
 *   { h:"handle", t:"title", v:"vendor", x:"#rrggbb", l:LAB_L, a:LAB_A, b:LAB_B, i:"image_url", p:"product_type" } ] }
 * Short keys keep the file small (~60k rows).
 *
 * Run where pg resolves (dwphoto's symlinked node_modules has it):
 *   cd ~/Projects/dw-photo-capture && node scripts/build-color-index.cjs
 * Refreshes are cheap + idempotent; safe to schedule.  $0 (local PG).
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

// Same connection convention as the DW cadence scripts (Unix socket, peer auth).
const pool = new Pool(
  process.env.DATABASE_URL
    ? { connectionString: process.env.DATABASE_URL }
    : { host: '/tmp', database: 'dw_unified' }
);

const OUT = path.join(__dirname, '..', 'data', 'color-index.json');

(async () => {
  const t0 = Date.now();
  // Gate on LIVE truth: only handles that are active in shopify_products (synced
  // from live Shopify) survive, so archived/deleted products can't leak a dead
  // handle (404 link) or a stale image into the index. status is case-mixed
  // ('ACTIVE' + 'active'); match case-insensitively. shopify_products is one row
  // per variant, so DISTINCT ON (pc.handle) collapses to one row per product,
  // keeping the most-recently-synced variant's featured image. The displayed
  // image prefers the fresh shopify_products.image_url, falling back to the
  // color-extract's image_url only if the mirror lacks one.
  const q = `
    SELECT DISTINCT ON (pc.handle)
      pc.handle, pc.title, pc.vendor, pc.hex, pc.lab_l, pc.lab_a, pc.lab_b,
      sp.product_type,
      COALESCE(NULLIF(sp.image_url, ''), pc.image_url) AS image_url
    FROM product_colors pc
    JOIN shopify_products sp ON sp.handle = pc.handle
    WHERE lower(sp.status) = 'active'
      -- Must be PUBLISHED to the Online Store, not merely active in admin: an
      -- active-but-unpublished product 404s on the storefront, so its /products/
      -- <handle> link would be dead. online_store_published = live-visible.
      AND sp.online_store_published = true
      -- Harm-reduction: only surface products with a REAL roll variant to buy.
      -- ~23k products are sample-only (whole-vendor gaps — Koroseal, Phillipe
      -- Romano, Phillip Jeffries …) with no roll variant, so their PDP shows the
      -- $4.25 memo Sample as the "Full Roll" price. Shop-by-color must not lead a
      -- shopper to a $4.25 sample-only page. (Real fix = source vendor costs +
      -- add roll variants; until then, exclude them here.)
      AND sp.has_product_variant IS TRUE
      AND pc.lab_l IS NOT NULL AND pc.lab_a IS NOT NULL AND pc.lab_b IS NOT NULL
      AND pc.handle IS NOT NULL AND pc.handle <> ''
      AND COALESCE(NULLIF(sp.image_url, ''), pc.image_url) IS NOT NULL
      AND COALESCE(NULLIF(sp.image_url, ''), pc.image_url) <> ''
      AND pc.hex ~* '^#?[0-9a-f]{6}$'
    ORDER BY pc.handle, sp.updated_at_shopify DESC NULLS LAST
  `;
  const { rows } = await pool.query(q);
  await pool.end();

  const items = rows.map(r => ({
    h: r.handle,
    t: (r.title || '').slice(0, 120),
    v: (r.vendor || '').slice(0, 60),
    x: r.hex.startsWith('#') ? r.hex.toLowerCase() : ('#' + r.hex.toLowerCase()),
    l: Math.round(r.lab_l * 10) / 10,
    a: Math.round(r.lab_a * 10) / 10,
    b: Math.round(r.lab_b * 10) / 10,
    i: r.image_url,
    p: (r.product_type || '').slice(0, 40)
  }));

  const payload = {
    generated_at: new Date().toISOString(),
    count: items.length,
    // NAMED TUNABLE tolerance the endpoint reads too (kept here for provenance).
    tolerance_pct: 0.10,
    items
  };
  fs.writeFileSync(OUT, JSON.stringify(payload));
  const kb = Math.round(fs.statSync(OUT).size / 1024);
  console.log(`color-index: ${items.length} products → ${OUT} (${kb} KB) in ${Date.now() - t0}ms`);
})().catch(e => { console.error('build-color-index FAILED:', e.message); process.exit(1); });