`gen.mjs`

199 lines
#!/usr/bin/env node
// Generator for the dw-catalog-3d Three.js drill-down.
// READ-ONLY against dw_unified (SELECT only). Writes local JSON into public/.
//   public/scene.json          -> one row per vendor with >=1 ACTIVE product
//   public/v/<slug>.json       -> drill data (collections -> products), ACTIVE+STAGED+DRAFT, each tagged with `st`
//   public/v/_index.json       -> manifest
//
// Usage: node gen.mjs
import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';

const ROOT = dirname(fileURLToPath(import.meta.url));
const PUB = join(ROOT, 'public');
const VDIR = join(PUB, 'v');
mkdirSync(VDIR, { recursive: true });

// caps (per brief)
const CAP_PROD_PER_COLL = 80;
const CAP_COLL_PER_VENDOR = 36;
const CAP_PROD_PER_VENDOR = 1400;

const STAGED_STATUSES = ['scheduled', 'drafted', 'manual_hold', 'blocked_metafields'];

function q(sql) {
  // returns array of rows, each an array of column strings. Uses a unit-separator to avoid delim collisions.
  const out = execFileSync('psql', ['-d', 'dw_unified', '-tAF', '\x1f', '-c', sql], {
    encoding: 'utf8', maxBuffer: 1024 * 1024 * 512,
  });
  return out.split('\n').filter(l => l.length).map(l => l.split('\x1f'));
}

function slugify(s) {
  return s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
}

// Cosmetic only: strip U+FFFD replacement chars left by upstream double-encoding
// (e.g. a corrupted "Coordonné" row whose original bytes are already lost) so the
// local viewer renders a clean string instead of mojibake boxes. Display-side only.
function cleanName(s) {
  return s.replace(/�/g, '').replace(/\s+/g, ' ').trim() || s;
}

function priceStr(retail, price) {
  const v = Number(retail) > 0 ? Number(retail) : (Number(price) > 0 ? Number(price) : 0);
  if (v <= 0) return '';
  return '$' + (Number.isInteger(v) ? v.toString() : v.toFixed(2));
}

function collFromTags(tags) {
  if (!tags) return 'Other / Uncollected';
  // tags is comma-separated; match "Collection: <name>" case-insensitively
  for (const raw of tags.split(',')) {
    const t = raw.trim();
    const m = t.match(/^collection:\s*(.+)$/i);
    if (m) return m[1].trim();
  }
  return 'Other / Uncollected';
}

// ---------------------------------------------------------------------------
// 1. staged dw_skus set (vendor-level staged COUNTS come from product_schedule)
// ---------------------------------------------------------------------------
console.error('querying staged set...');
const stagedRows = q(`
  SELECT lower(coalesce(dw_sku,'')) FROM product_schedule
  WHERE status IN (${STAGED_STATUSES.map(s => `'${s}'`).join(',')})
    AND dw_sku IS NOT NULL AND dw_sku<>''`);
const stagedSkus = new Set(stagedRows.map(r => r[0]).filter(Boolean));

// vendor-level staged counts (by vendor_name from the schedule)
const stagedByVendor = {};
for (const [vn, c] of q(`
  SELECT vendor_name, count(*) FROM product_schedule
  WHERE status IN (${STAGED_STATUSES.map(s => `'${s}'`).join(',')})
    AND vendor_name IS NOT NULL AND vendor_name<>''
  GROUP BY 1`)) {
  stagedByVendor[vn] = Number(c) || 0;
}

// ---------------------------------------------------------------------------
// 2. scene.json — per-vendor status rollup, vendors with >=1 ACTIVE
// ---------------------------------------------------------------------------
console.error('querying vendor rollup...');
const rollup = q(`
  SELECT vendor,
    count(*) FILTER (WHERE upper(status)='ACTIVE'),
    count(*) FILTER (WHERE upper(status)='DRAFT'),
    count(*) FILTER (WHERE upper(status)='ARCHIVED'),
    count(*) FILTER (WHERE upper(status)='DELETED_FROM_SHOPIFY')
  FROM shopify_products
  WHERE vendor IS NOT NULL AND vendor<>''
  GROUP BY vendor
  HAVING count(*) FILTER (WHERE upper(status)='ACTIVE') >= 1
  ORDER BY count(*) FILTER (WHERE upper(status)='ACTIVE') DESC`);

// representative active image per vendor (first non-empty)
console.error('querying vendor rep images...');
const repImg = {};
for (const [vendor, img] of q(`
  SELECT DISTINCT ON (vendor) vendor, image_url
  FROM shopify_products
  WHERE upper(status)='ACTIVE' AND image_url IS NOT NULL AND image_url<>''
  ORDER BY vendor, id`)) {
  repImg[vendor] = img;
}

const slugSeen = new Map();
function uniqueSlug(vendor) {
  let base = slugify(vendor) || 'vendor';
  let s = base, n = 1;
  while (slugSeen.has(s)) { s = `${base}-${++n}`; }
  slugSeen.set(s, true);
  return s;
}

const scene = rollup.map(([vendor, active, draft, archived, deleted]) => {
  const slug = uniqueSlug(vendor);
  return {
    vendor: cleanName(vendor),
    _rawVendor: vendor,
    active: Number(active) || 0,
    staged: stagedByVendor[vendor] || 0,
    draft: Number(draft) || 0,
    archived: Number(archived) || 0,
    deleted: Number(deleted) || 0,
    img: repImg[vendor] || '',
    slug,
  };
});
writeFileSync(join(PUB, 'scene.json'), JSON.stringify(scene.map(({ _rawVendor, ...rest }) => rest)));
console.error(`scene.json: ${scene.length} vendors`);

// ---------------------------------------------------------------------------
// 3. drill files — ACTIVE+STAGED+DRAFT products per vendor, tagged `st`
// ---------------------------------------------------------------------------
const index = {};
let totalDrillProducts = 0;
let drillFiles = 0;

for (const v of scene) {
  const vendor = v.vendor;          // cleaned (display)
  const rawVendor = v._rawVendor;   // raw (for SQL match)
  const vsafe = rawVendor.replace(/'/g, "''");
  // pull ACTIVE+DRAFT products (prefer rows WITH images), capped at CAP_PROD_PER_VENDOR
  const rows = q(`
    SELECT title, coalesce(variant_sku, dw_sku, sku, ''), retail_price, price, handle, image_url, tags,
           upper(status), lower(coalesce(dw_sku, ''))
    FROM shopify_products
    WHERE vendor='${vsafe}' AND upper(status) IN ('ACTIVE','DRAFT')
    ORDER BY (CASE WHEN image_url IS NOT NULL AND image_url<>'' THEN 0 ELSE 1 END),
             (CASE WHEN upper(status)='ACTIVE' THEN 0 ELSE 1 END), id
    LIMIT ${CAP_PROD_PER_VENDOR}`);

  // bucket into collections
  const collMap = new Map(); // name -> {name,count,products:[]}
  for (const [title, sku, retail, price, handle, img, tags, status, dwsku] of rows) {
    if (!title) continue;
    const cname = collFromTags(tags);
    let st = status === 'ACTIVE' ? 'active' : 'draft';
    if (dwsku && stagedSkus.has(dwsku)) st = 'staged';
    if (!collMap.has(cname)) collMap.set(cname, { name: cname, count: 0, products: [] });
    const c = collMap.get(cname);
    c.count++;
    // Preventive 404-guard (catalog-integrity audit 2026-06-26): a product is only a
    // CLICKABLE tile if its handle resolves to a real PDP. Empty / "-handle" / "-sample"
    // placeholder handles are known to 404 on the storefront, so skip them as tiles
    // (still counted above so collection sizes stay truthful). Keeps the panel 404-clean
    // as the daily refresh pulls in new rows.
    const hh = (handle || '').trim();
    const handleOk = hh && !hh.endsWith('-handle') && !hh.endsWith('-sample');
    if (handleOk && c.products.length < CAP_PROD_PER_COLL) {
      c.products.push({
        t: title,
        sku: sku || '',
        p: priceStr(retail, price),
        h: handle || '',
        img: img || '',
        st,
      });
    }
  }

  // collections: cap count, sort by count desc
  let collections = [...collMap.values()].sort((a, b) => b.count - a.count).slice(0, CAP_COLL_PER_VENDOR);
  const total = rows.length;
  const shown = collections.reduce((s, c) => s + c.products.length, 0);
  totalDrillProducts += shown;

  writeFileSync(join(VDIR, `${v.slug}.json`), JSON.stringify({ vendor, total, collections }));
  drillFiles++;
  index[vendor] = { slug: v.slug, collections: collections.length, shown };
}

writeFileSync(join(VDIR, '_index.json'), JSON.stringify(index, null, 2));
console.error(`drill files: ${drillFiles}  total drill products shown: ${totalDrillProducts}`);
console.error('DONE');