`lib/mfr-gate-resolve.js`

141 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.
// Cache table→vendor-scoping clause. A SHARED catalog table (>1 vendor registered to it)
// must be filtered to THIS vendor's rows: `vendor_catalog` alone holds 157 distinct
// vendor_codes, and 11,640 mfr_sku values in it are used by MORE THAN ONE vendor, so an
// unfiltered `WHERE mfr_sku = …` returns whichever row the seq scan reaches first.
// Demonstrated 2026-09-10: mfr 5016840 resolves to schumacher "sage green" AND backdrop
// "Seaglass Wallcovering" — for a Backdrop product the unfiltered query returned
// Schumacher's colour. Returns '' for a private (single-vendor) table, a filter clause
// when it can scope, or null when it CANNOT — and null makes the caller fail OPEN, which
// is this module's standing doctrine (never a false BLOCK on a resolution failure).
const _scopeCache = new Map();
function vendorScopeFor(vendor, table) {
  const key = `${table}::${String(vendor || '').toLowerCase()}`;
  if (_scopeCache.has(key)) return _scopeCache.get(key);
  let clause = '';
  try {
    const sharers = parseInt(
      psql(`SELECT count(*) FROM vendor_registry WHERE catalog_table = ${lit(table)};`), 10) || 0;
    if (sharers > 1) {
      const cols = new Set(
        (psql(`SELECT column_name FROM information_schema.columns
                WHERE table_name = ${lit(table)}
                  AND column_name IN ('vendor_code','brand','vendor');`) || '')
          .split('\n').map((s) => s.trim()).filter(Boolean));
      const col = cols.has('vendor_code') ? 'vendor_code'
        : cols.has('brand') ? 'brand'
        : cols.has('vendor') ? 'vendor' : null;
      if (!col) {
        clause = null; // shared and unscopable → caller fails open
      } else if (col === 'vendor_code') {
        // `vendor` here is a vendor_NAME; map it to its code via the registry.
        const code = (psql(
          `SELECT vendor_code FROM vendor_registry
            WHERE lower(vendor_name) = ${lit(String(vendor || '').toLowerCase())} LIMIT 1;`) || '')
          .split('\n')[0].trim();
        clause = code ? ` AND lower(vendor_code) = lower(${lit(code)})` : null;
      } else {
        clause = ` AND lower(${col}) = lower(${lit(vendor)})`;
      }
    }
  } catch (_) {
    clause = null; // resolution failed → fail open rather than read another vendor's row
  }
  _scopeCache.set(key, clause);
  return clause;
}

function stagingColorFor(vendor, mfr) {
  if (!mfr) return null;
  const table = catalogTableFor(vendor);
  if (!table) return null;
  const scope = vendorScopeFor(vendor, table);
  if (scope === null) return null; // shared table we cannot scope — fail open
  try {
    const raw = psql(
      `SELECT color_name FROM ${table}
       WHERE mfr_sku = ${lit(mfr)} AND color_name IS NOT NULL${scope}
       ORDER BY color_name
       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 };