`server.js`

205 lines
// new.engine.designerwallcoverings.com — "New Arrivals" engine.
// Modeled on the '67 Corvette Console (127.0.0.1:9797): a zero-dependency Node http
// server + a LIVE data endpoint that pulls the newest products straight from the local
// dw_unified mirror. We shell out to psql and let the DB shape the JSON via json_agg,
// so no `pg` module is needed. Unlike the corvette build (which read the transient
// TK-135 apply-job logs), "newest" here is driven by the catalog itself —
// shopify_products.created_at_shopify desc — so it stays correct forever.
const http = require('http'), https = require('https'), fs = require('fs'), path = require('path'), { execFile } = require('child_process');
const PORT = process.env.PORT || 9870;
const ROOT = path.join(__dirname, 'public');
const PGDB = process.env.PGDATABASE || 'dw_unified';
const PGHOST = process.env.PGHOST || '/tmp';
const MIME = { '.html': 'text/html', '.json': 'application/json', '.css': 'text/css', '.js': 'text/javascript', '.png': 'image/png', '.jpg': 'image/jpeg', '.svg': 'image/svg+xml' };

// SQL string-literal escape: double single-quotes. PG runs with standard_conforming_strings
// on by default, so backslashes are literal and doubling quotes fully neutralizes injection.
const sq = s => "'" + String(s).replace(/'/g, "''") + "'";

// Read-only live pull of the newest ACTIVE, imaged products, with optional filters.
// Only these dynamic values ever touch the SQL, and each is neutralized:
//   • limit          → clamped integer
//   • q / vendor     → sq()-escaped string literals
//   • sort / since   → whitelisted (raw user value is NEVER interpolated)
// Returns { records:[…], total } where total is the count matching the SAME filters.
// color_enrichment is LEFT-joined — a product without a palette still shows.
function fetchItems(params, cb) {
  const n = Math.max(1, Math.min(parseInt(params.get('limit'), 10) || 60, 200));
  const q = (params.get('q') || '').trim().slice(0, 80);
  const vendor = (params.get('vendor') || '').trim().slice(0, 120);
  const sortKey = params.get('sort') || 'newest';
  const since = params.get('since') || 'all';

  const conds = [`p.status = 'ACTIVE'`, `p.created_at_shopify is not null`, `p.image_url is not null`];
  if (q) conds.push(`(p.title ilike ${sq('%' + q + '%')} or p.vendor ilike ${sq('%' + q + '%')}
      or coalesce(p.dw_sku,p.mfr_sku,p.variant_sku,'') ilike ${sq('%' + q + '%')})`);
  if (vendor) conds.push(`p.vendor = ${sq(vendor)}`);
  const sinceMap = {
    today: `p.created_at_shopify >= date_trunc('day', now())`,
    '7d': `p.created_at_shopify >= now() - interval '7 days'`,
    '30d': `p.created_at_shopify >= now() - interval '30 days'`,
  };
  if (sinceMap[since]) conds.push(sinceMap[since]);
  const where = conds.join(' and ');

  const orderMap = {
    newest: 'p.created_at_shopify desc',
    vendor: `p.vendor asc nulls last, p.created_at_shopify desc`,
    sku: `coalesce(nullif(p.dw_sku,''),nullif(p.mfr_sku,''),nullif(p.variant_sku,'')) asc nulls last`,
    title: 'lower(p.title) asc nulls last',
    color: `coalesce(nullif(e.color_family,''),nullif(e.dominant_color,''),'zzzz') asc, p.created_at_shopify desc`,
  };
  const orderBy = orderMap[sortKey] || orderMap.newest;

  const sql = `select json_build_object(
    'records', (select coalesce(json_agg(row_to_json(t)),'[]') from (
        select regexp_replace(p.shopify_id,'.*/','') as gid, p.title, p.vendor, p.handle, p.image_url as image,
               coalesce(nullif(p.dw_sku,''),nullif(p.mfr_sku,''),nullif(p.variant_sku,'')) as sku,
               to_char(p.created_at_shopify,'YYYY-MM-DD"T"HH24:MI:SS') as created,
               (select json_agg(json_build_object('hex',c->>'hex','pct',(c->>'percentage')::numeric))
                  from jsonb_array_elements(e.colors) c) as palette,
               (select json_agg(v) from (select distinct trim(x) v
                  from unnest(string_to_array(p.tags,',')) x
                  where trim(x) !~ '[:/.{}"]' and trim(x) !~ '^[0-9]' and length(trim(x)) between 2 and 22
                  limit 8) s) as tags
        from shopify_products p
        left join shopify_color_enrichment e on e.shopify_id = p.shopify_id
        where ${where}
        order by ${orderBy}
        limit ${n}) t),
    'total', (select count(*) from shopify_products p where ${where})
  )`;
  execFile('psql', ['-h', PGHOST, '-d', PGDB, '-tAc', sql], { maxBuffer: 64 * 1024 * 1024 }, (err, stdout) => {
    if (err) return cb(err);
    const out = (stdout || '').trim();
    if (!out) return cb(new Error('empty'));
    cb(null, out);
  });
}

// Distinct vendors (with counts) among the eligible set — powers the vendor filter dropdown.
function fetchFacets(cb) {
  const sql = `select json_build_object(
    'vendors', (select coalesce(json_agg(row_to_json(t)),'[]') from (
        select vendor, count(*) as n
        from shopify_products
        where status='ACTIVE' and image_url is not null and vendor is not null and vendor <> ''
        group by vendor order by count(*) desc, vendor asc limit 250) t))`;
  execFile('psql', ['-h', PGHOST, '-d', PGDB, '-tAc', sql], { maxBuffer: 16 * 1024 * 1024 }, (err, stdout) => {
    if (err) return cb(err);
    const out = (stdout || '').trim();
    if (!out) return cb(new Error('empty'));
    cb(null, out);
  });
}

// HTTP Basic Auth gate. Credentials come from BASIC_AUTH ("user:pass"), default admin:DW2024!.
// /healthz is intentionally left OPEN so the deploy smoke-test + uptime probes work unauthed.
const [AUTH_USER, AUTH_PASS] = (process.env.BASIC_AUTH || 'admin:DW2024!').split(':');
function authed(req) {
  const h = req.headers.authorization || '';
  if (!h.startsWith('Basic ')) return false;
  const [u, ...rest] = Buffer.from(h.slice(6), 'base64').toString('utf8').split(':');
  return u === AUTH_USER && rest.join(':') === AUTH_PASS;
}

// ---- response cache (single-flight + serve-stale) --------------------------
// The catalog query is a full seq-scan + sort of ~81k rows on the SHARED canonical
// dw_unified (no created_at index), so it costs ~5-8s. Without a cache, the 6s
// auto-refresh × every viewer would hammer that DB relentlessly. This cache makes
// repeat/auto-refresh hits instant and collapses concurrent identical requests into
// ONE db call (single-flight). On a stale hit it serves the stale copy immediately
// and refreshes in the background — the UI never blocks after the first load.
function makeCache(ttlMs, producer) {
  const store = new Map();   // key -> { json, ts }
  const inflight = new Map(); // key -> Promise<json>
  return function get(key, arg, cb) {
    const hit = store.get(key);
    const fresh = hit && (Date.now() - hit.ts) < ttlMs;
    if (fresh) return cb(null, hit.json, 'cache');
    if (!inflight.has(key)) {
      const pr = new Promise((resolve, reject) =>
        producer(arg, (err, json) => err ? reject(err) : resolve(json)));
      pr.then(json => store.set(key, { json, ts: Date.now() }), () => {});
      pr.finally(() => inflight.delete(key));
      inflight.set(key, pr);
    }
    if (hit) return cb(null, hit.json, 'stale'); // serve stale now; refresh continues
    inflight.get(key).then(json => cb(null, json, 'live'), err => cb(err));
  };
}
const itemsCache = makeCache(30000, fetchItems);
const facetsCache = makeCache(300000, (_arg, cb) => fetchFacets(cb));

http.createServer((req, res) => {
  const u = new URL(req.url, 'http://x');
  let p = decodeURIComponent(u.pathname);

  if (p === '/healthz') { res.writeHead(200); return res.end('ok'); }

  if (!authed(req)) {
    res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="new-engine"' });
    return res.end('auth required');
  }

  // Vendor facets for the filter dropdown (cached 5 min).
  if (p === '/api/facets') {
    facetsCache('vendors', null, (err, json) => {
      if (!err && json) { res.writeHead(200, { 'Content-Type': 'application/json', 'Cache-Control': 'max-age=120' }); return res.end(json); }
      res.writeHead(200, { 'Content-Type': 'application/json' });
      res.end('{"vendors":[]}');
    });
    return;
  }

  // Live newest feed (with filters), cached 30s per distinct filter. On any DB failure,
  // fall back to the frozen snapshot (matching the {records,total} contract).
  if (p === '/api/new-items') {
    const sp = u.searchParams;
    const key = ['items', sp.get('limit') || '60', sp.get('q') || '', sp.get('vendor') || '',
      sp.get('sort') || 'newest', sp.get('since') || 'all'].join('');
    itemsCache(key, sp, (err, json, src) => {
      if (!err && json) { res.writeHead(200, { 'Content-Type': 'application/json', 'X-Data-Source': src || 'live', 'Cache-Control': 'no-store' }); return res.end(json); }
      fs.readFile(path.join(ROOT, 'data.json'), (e, buf) => {
        if (e) { res.writeHead(502); return res.end('no live db and no snapshot'); }
        // Guard the parse: a corrupt snapshot must not throw in this async callback and
        // crash the process — that would turn the safety net into an outage.
        let obj;
        try { obj = JSON.parse(buf); } catch (pe) { res.writeHead(502); return res.end('snapshot unreadable: ' + pe.message); }
        if (Array.isArray(obj)) obj = { records: obj, total: obj.length };
        res.writeHead(200, { 'Content-Type': 'application/json', 'X-Data-Source': 'snapshot' });
        res.end(JSON.stringify(obj));
      });
    });
    return;
  }

  // ---- image proxy ----
  // Fetch the product image server-side so a dead CDN asset degrades to a transparent
  // placeholder (same-origin 200) instead of a browser-console 404. SSRF-guarded:
  // only https shopify.com CDN hosts are fetched; anything else → placeholder.
  if (p === '/img') {
    const PLACEHOLDER = Buffer.from('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNk+A8AAQUBAScY42YAAAAASUVORK5CYII=', 'base64');
    const placeholder = () => { if (res.writableEnded) return; res.writeHead(200, { 'Content-Type': 'image/png', 'Cache-Control': 'no-store' }); res.end(PLACEHOLDER); };
    let tu; try { tu = new URL(u.searchParams.get('u') || ''); } catch { return placeholder(); }
    if (tu.protocol !== 'https:' || !/(^|\.)shopify\.com$/.test(tu.hostname)) return placeholder();
    const ir = https.get(tu.href, (pres) => {
      if (pres.statusCode !== 200) { pres.resume(); return placeholder(); }
      res.writeHead(200, { 'Content-Type': pres.headers['content-type'] || 'image/jpeg', 'Cache-Control': 'public,max-age=3600' });
      pres.pipe(res);
    });
    ir.on('error', placeholder);
    ir.setTimeout(6000, () => { ir.destroy(); placeholder(); });
    return;
  }

  if (p === '/') p = '/index.html';
  const fp = path.join(ROOT, path.normalize(p));
  if (!fp.startsWith(ROOT)) { res.writeHead(403); return res.end('forbidden'); }
  fs.readFile(fp, (e, buf) => {
    if (e) { res.writeHead(404); return res.end('not found'); }
    res.writeHead(200, { 'Content-Type': MIME[path.extname(fp)] || 'application/octet-stream' });
    res.end(buf);
  });
}).listen(PORT, () => console.log(`New Arrivals engine → http://127.0.0.1:${PORT} (live /api/new-items ⋈ ${PGDB})`));