`modules/engine/candidates.js`

198 lines
// Engine candidates — pick the day's candidate products from the local dw_unified
// Postgres mirror (READ-ONLY). Feeds generate.js. Everything here degrades
// gracefully: a missing pg module, an unreachable DB, or an empty result all
// resolve to [] rather than throwing, so the daily engine never crashes the MCC.
//
// Pipeline: SQL pull (recent ACTIVE, published, imaged products) → JS filters
//   (a) leak-deny drop, (b) Kravet-family drop, (c) no-repeat-window drop,
//   (d) variety pick (prefer unseen vendor + unseen product_type) → N products.
const store = require('./store');
const config = require('./config');
const { isLeaky } = require('../../lib/leak-deny');
const { isKravet } = require('../../lib/kravet.js');

// ── Postgres (lazy; dw_unified is canonical on Kamatera, /tmp mirror locally) ──
// Mirrors modules/calendars/lib.js: env-first, DATABASE_URL wins, else /tmp
// socket to the dw_unified mirror. require('pg') is inside a try/catch so a host
// without the pg module simply yields no candidates instead of crashing mount.
let _pool = null;
let _pgUnavailable = false;
function pool() {
  if (_pool) return _pool;
  if (_pgUnavailable) return null;
  let Pool;
  try { ({ Pool } = require('pg')); }
  catch (e) { console.error('[engine candidates] pg module missing —', e.message); _pgUnavailable = true; return null; }
  try {
    _pool = process.env.DATABASE_URL
      ? new Pool({ connectionString: process.env.DATABASE_URL, max: 3 })
      : new Pool({
          host: process.env.PGHOST || '/tmp',
          database: process.env.PGDATABASE || 'dw_unified',
          user: process.env.PGUSER || process.env.USER || 'stevestudio2',
          max: 3,
        });
    _pool.on('error', (e) => console.error('[engine candidates pool]', e.message)); // log, never crash the MCC
    return _pool;
  } catch (e) {
    console.error('[engine candidates] pool init failed —', e.message);
    _pgUnavailable = true;
    return null;
  }
}

// Base query — most-recent ACTIVE, online-store-published, imaged products within
// a lookback window. The interval days are a sanitized integer interpolated into
// the SQL (pg can't parameterize an interval literal); we hard-coerce to a small
// positive integer so nothing user-derived reaches the query text.
function buildSql(lookbackDays) {
  const days = Math.min(3650, Math.max(1, parseInt(lookbackDays, 10) || 14));
  return `
    SELECT shopify_id, dw_sku, sku, variant_sku, mfr_sku,
           title, vendor, product_type, image_url, handle, tags,
           created_at_shopify
      FROM shopify_products
     WHERE status = 'ACTIVE'
       AND online_store_published = true
       AND image_url IS NOT NULL
       AND created_at_shopify >= now() - interval '${days} days'
     ORDER BY created_at_shopify DESC
     LIMIT 200`;
}

// Escalation ladder — config.lookbackDays is the PREFERENCE/floor. If the primary
// window yields nothing (recent products simply haven't landed — the newest
// published+imaged SKU can be weeks old), widen the window so the daily engine
// never silently produces zero posts. DTD verdict (2026-07-21): resilient widen.
function lookbackLadder(base) {
  const b = Math.max(1, parseInt(base, 10) || 14);
  const ladder = [b, 30, 60, 90, 180];
  // de-dupe + keep strictly increasing from the base
  return [...new Set(ladder.filter(d => d >= b))].sort((a, c) => a - c);
}

// Parse a PG text[] / CSV tags column into a clean string[] (same handling as
// modules/calendars/lib.js parseTags).
function parseTags(raw) {
  if (!raw) return [];
  if (Array.isArray(raw)) return raw.map(t => String(t).trim()).filter(Boolean);
  let s = String(raw).trim();
  if (s.startsWith('{') && s.endsWith('}')) s = s.slice(1, -1);
  const out = []; let cur = '', inq = false;
  for (const ch of s) {
    if (ch === '"') { inq = !inq; continue; }
    if (ch === ',' && !inq) { out.push(cur); cur = ''; continue; }
    cur += ch;
  }
  if (cur) out.push(cur);
  return out.map(t => t.trim()).filter(Boolean);
}

// (c) Build the set of product identifiers already queued within noRepeatDays so
// we never re-feature the same product. Scans the existing queue's item.product.
function recentlyFeaturedKeys(noRepeatDays) {
  const days = Math.max(0, parseInt(noRepeatDays, 10) || 0);
  const cutoff = Date.now() - days * 24 * 60 * 60 * 1000;
  const keys = new Set();
  for (const it of store.load()) {
    const t = new Date(it.createdAt || `${it.date}T00:00:00`).getTime();
    if (!Number.isFinite(t) || t < cutoff) continue;
    const p = it.product || {};
    if (p.dwSku) keys.add(`sku:${String(p.dwSku).toLowerCase()}`);
    if (p.shopifyId) keys.add(`sid:${String(p.shopifyId)}`);
  }
  return keys;
}

// Resolve the customer-facing DW SKU. dw_sku is often empty on the mirror; the
// real SKU lives in sku/variant_sku carrying a '-Sample' suffix (the first variant
// is the memo sample). Prefer dw_sku, fall back to sku → variant_sku, strip the
// trailing sample suffix, and trim. Returns '' if nothing usable is present.
function resolveSku(row) {
  const raw = String(row.dw_sku || row.sku || row.variant_sku || '').trim();
  if (!raw) return '';
  return raw.replace(/[-_\s]*sample\s*$/i, '').trim();
}

function seenKeyOf(row) {
  const out = [];
  if (row.dw_sku) out.push(`sku:${String(row.dw_sku).toLowerCase()}`);
  if (row.shopify_id) out.push(`sid:${String(row.shopify_id)}`);
  return out;
}

// (d) Variety pick — greedy: at each step prefer a row whose vendor AND
// product_type are both still unseen, then unseen vendor, then unseen type, then
// anything. Preserves the recency ORDER within each preference tier.
function pickVariety(rows, n) {
  const chosen = [];
  const seenVendor = new Set();
  const seenType = new Set();
  const remaining = rows.slice();
  const vendorOf = r => String(r.vendor || '').trim().toLowerCase();
  const typeOf = r => String(r.product_type || '').trim().toLowerCase();

  while (chosen.length < n && remaining.length) {
    let idx = remaining.findIndex(r => !seenVendor.has(vendorOf(r)) && !seenType.has(typeOf(r)));
    if (idx < 0) idx = remaining.findIndex(r => !seenVendor.has(vendorOf(r)));
    if (idx < 0) idx = remaining.findIndex(r => !seenType.has(typeOf(r)));
    if (idx < 0) idx = 0;
    const [r] = remaining.splice(idx, 1);
    seenVendor.add(vendorOf(r));
    seenType.add(typeOf(r));
    chosen.push(r);
  }
  return chosen;
}

// pickCandidates() — the day's products. Never throws; [] on any failure.
async function pickCandidates() {
  const cfg = config.getConfig();
  const p = pool();
  if (!p) return []; // pg module / DB unavailable — degrade gracefully

  let rows = [];
  try {
    // Walk the lookback ladder — stop at the first window that yields rows that
    // survive the leak + Kravet + no-repeat filters.
    const noRepeat = recentlyFeaturedKeys(cfg.noRepeatDays);
    for (const days of lookbackLadder(cfg.lookbackDays)) {
      const res = await p.query(buildSql(days));
      const filtered = res.rows.filter(r => {
        // (a) leak-deny — never surface an upstream private-label name
        if (isLeaky(r.vendor) || isLeaky(r.title)) return false;
        // (b) Kravet-family — brand-controlled imagery is hard-blocked from reposting
        if (isKravet(r.vendor, r.title)) return false;
        // (c) no-repeat window — skip products already queued recently
        if (seenKeyOf(r).some(k => noRepeat.has(k))) return false;
        return true;
      });
      if (filtered.length) { rows = filtered; break; }
    }
  } catch (e) {
    console.error('[engine candidates] query failed —', e.message);
    return [];
  }

  if (!rows.length) return [];

  // (d) variety pick down to productsPerDay
  const n = Math.max(1, parseInt(cfg.productsPerDay, 10) || 3);
  const picked = pickVariety(rows, n);

  return picked.map(r => ({
    shopifyId: r.shopify_id != null ? String(r.shopify_id) : '',
    dwSku: resolveSku(r),
    mfrSku: r.mfr_sku || '',
    handle: r.handle || '',
    title: r.title || '',
    vendor: r.vendor || '',
    productType: r.product_type || '',
    imageUrl: r.image_url || '',
    productUrl: `https://designerwallcoverings.com/products/${r.handle || ''}`,
    tags: parseTags(r.tags),
  }));
}

module.exports = { pickCandidates };