`lib/mfr-gate-resolve.js`

92 lines
'use strict';

/*
 * mfr-gate-resolve.js — the thin I/O layer that feeds lib/mfr-gate.js.
 *
 * Keeps mfr-gate.js pure (no DB) by doing the cheap dw_unified lookups here:
 *   - reusedMfrSet(): one query → the SET of mfr codes that appear under >1 vendor
 *     (the internal-counter bug class), loaded ONCE per activator run.
 *   - stagingColorFor(vendor, mfr): resolve the vendor's staging table via
 *     vendor_registry.catalog_table and read the color_name of the row whose
 *     mfr_sku === the resolved code (the fabricated-placeholder-color signal).
 *
 * All reads are host=/tmp local dw_unified. Fail-safe: any query error returns the
 * EMPTY/absent value so the gate fails OPEN on that dimension (never a false BLOCK
 * on a DB hiccup; the gate only BLOCKs on positive evidence).
 */
const { execFileSync } = require('child_process');

function psql(sql) {
  return execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tAF', '\t', '-c', sql],
    { encoding: 'utf8', maxBuffer: 1 << 28 }).trim();
}

// SQL string literal escaper (single-quote doubling). Codes/vendors are alnum+dash in
// practice, but never interpolate raw.
const lit = (s) => `'${String(s == null ? '' : s).replace(/'/g, "''")}'`;

// The set of mfr codes shared across >1 vendor in shopify_products — the reused
// internal-counter signature. Loaded once; O(1) membership after. Fail-safe → empty set.
function reusedMfrSet() {
  try {
    const raw = psql(`
      SELECT rm FROM (
        SELECT COALESCE(
          NULLIF(metafields->'dwc'->'manufacturer_sku'->>'value',''),
          NULLIF(metafields->'custom'->'manufacturer_sku'->>'value',''),
          NULLIF(metafields->'global'->'manufacturer_sku'->>'value',''),
          NULLIF(mfr_sku,'')
        ) AS rm, vendor
        FROM shopify_products
      ) q
      WHERE rm IS NOT NULL
      GROUP BY rm HAVING count(DISTINCT vendor) > 1;`);
    return new Set(raw ? raw.split('\n').map((l) => l.trim()).filter(Boolean) : []);
  } catch (_) {
    return new Set();
  }
}

// Cache vendor→catalog_table so we resolve vendor_registry once per vendor per run.
const _tableCache = new Map();
function catalogTableFor(vendor) {
  const key = String(vendor || '').toLowerCase();
  if (_tableCache.has(key)) return _tableCache.get(key);
  let table = null;
  try {
    // Exact-name first, then a contained match (vendor labels vary slightly). Only
    // accept a real, safe table identifier (letters/digits/underscore) — never a value
    // we'd interpolate blindly into the next query.
    const raw = psql(
      `SELECT catalog_table FROM vendor_registry
       WHERE catalog_table IS NOT NULL AND catalog_table <> ''
         AND (lower(vendor_name) = ${lit(key)} OR lower(vendor_name) LIKE ${lit('%' + key + '%')})
       ORDER BY (lower(vendor_name) = ${lit(key)}) DESC LIMIT 1;`);
    const t = (raw || '').split('\n')[0].trim();
    if (/^[a-z_][a-z0-9_]*$/i.test(t)) table = t;
  } catch (_) { /* fail-open */ }
  _tableCache.set(key, table);
  return table;
}

// The color_name of the staging row whose mfr_sku === the resolved code. Returns null
// when there is no staging table, no matching row, or on any error → gate fails open
// on the fabricated-sequence dimension. Guards that the table actually has the columns.
function stagingColorFor(vendor, mfr) {
  if (!mfr) return null;
  const table = catalogTableFor(vendor);
  if (!table) return null;
  try {
    const raw = psql(
      `SELECT color_name FROM ${table}
       WHERE mfr_sku = ${lit(mfr)} AND color_name IS NOT NULL
       LIMIT 1;`);
    const c = (raw || '').split('\n')[0];
    return c === '' ? null : c;
  } catch (_) {
    return null; // e.g. table lacks color_name/mfr_sku columns — fail open
  }
}

module.exports = { reusedMfrSet, catalogTableFor, stagingColorFor };