`scripts/adapters/resolve.js`

92 lines
// adapters/resolve.js — shared grid-SKU → vendor-catalog product_url resolver.
//
// The grid passes a DISPLAY sku (e.g. "ROP-73347-Sample"). The vendor catalog is keyed by its own
// dw_sku (e.g. "DWRO-29318") and mfr_sku (e.g. "W472/04") — the display string alone often matches
// neither. So we bridge through shopify_products: look up the display row to recover its dw_sku +
// mfr_sku, then match the catalog on ANY of {display sku, its dw_sku, its mfr_sku}. This is exactly
// the join the coverage resolvability gate measures, so "wired & enabled" == "actually resolves".
async function catCols(pool, table) {
  const { rows } = await pool.query(
    `SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name=$1`, [table]);
  return new Set(rows.map((r) => r.column_name));
}

async function resolveCatalogUrl(pool, table, gridSku) {
  if (!table || !/^[a-z0-9_]+$/.test(table)) return { error: 'no catalog table for this vendor' };
  const cols = await catCols(pool, table);
  if (!cols.has('product_url')) return { error: 'catalog has no product_url' };

  // recover the display row's dw_sku + mfr_sku from the grid sku
  const keys = new Set([String(gridSku).toUpperCase()]);
  let recoveredMfr = null; // the SKU's own mfr code, kept for the template-learning fallback below
  try {
    const { rows } = await pool.query(
      `SELECT dw_sku, mfr_sku FROM shopify_products
        WHERE upper(coalesce(dw_sku,''))=upper($1) OR upper(coalesce(variant_sku,''))=upper($1) OR upper(coalesce(sku,''))=upper($1)
        LIMIT 1`, [gridSku]);
    if (rows[0]) {
      if (rows[0].dw_sku) keys.add(String(rows[0].dw_sku).toUpperCase());
      if (rows[0].mfr_sku) {
        const raw = String(rows[0].mfr_sku).toUpperCase();
        recoveredMfr = String(rows[0].mfr_sku).trim();
        keys.add(raw);
        // Some vendors carry a COMPOUND display mfr_sku that prepends the real mfr code to the
        // pattern+color (e.g. Osborne & Little "W7813-02-Villa Como-Aqua"), while the vendor catalog
        // keys on the bare code ("W7813-02"). Extract the leading <letters><digits>-<digits> code as
        // an additional candidate key. It equals the whole string for already-clean SKUs (a no-op),
        // so this only ADDS matches — it never breaks a vendor whose SKUs already align.
        const lead = raw.match(/^[A-Z]+[0-9]+-[0-9]+/);
        if (lead && lead[0] !== raw) keys.add(lead[0]);
      }
    }
  } catch { /* shopify_products lookup optional */ }

  const keyArr = [...keys];
  const wh = [];
  if (cols.has('dw_sku')) wh.push('upper(dw_sku)=ANY($1)');
  if (cols.has('mfr_sku')) wh.push('upper(mfr_sku)=ANY($1)');
  if (!wh.length) return { error: 'catalog has no matchable sku column' };
  const sel = ['product_url', cols.has('mfr_sku') ? 'mfr_sku' : "'' AS mfr_sku", cols.has('handle') ? 'handle' : "'' AS handle"].join(', ');
  const { rows } = await pool.query(
    `SELECT ${sel} FROM "${table}" WHERE (${wh.join(' OR ')}) AND product_url IS NOT NULL AND product_url<>'' LIMIT 1`, [keyArr]);
  if (rows.length) return { product_url: rows[0].product_url, mfr_sku: rows[0].mfr_sku || null, handle: rows[0].handle || null };

  // TEMPLATE-LEARNING FALLBACK (shape-general, additive) — reached ONLY when the direct join above
  // found no catalog row carrying a product_url for this SKU. Some vendor catalogs populate
  // product_url on only SOME rows while the URL shape embeds each row's own mfr_sku (e.g. a Magento
  // /products/<code> path). When this SKU's own row has an empty/missing product_url, we LEARN that
  // shape from any sibling row whose product_url contains its own mfr_sku, then substitute this SKU's
  // recovered mfr code. Because it runs only after a miss and needs a real recovered mfr_sku, it can
  // ONLY ADD a match — it never changes a vendor whose SKUs already resolve. Requires the grid row to
  // carry an mfr_sku (vendors whose grid rows have no SKU key are still correctly unresolved).
  if (recoveredMfr && cols.has('mfr_sku')) {
    try {
      const { rows: tpl } = await pool.query(
        `SELECT mfr_sku, product_url FROM "${table}"
          WHERE product_url IS NOT NULL AND product_url<>'' AND mfr_sku IS NOT NULL AND mfr_sku<>''
            AND position(mfr_sku in product_url) > 0 LIMIT 50`);
      for (const t of tpl) {
        const code = String(t.mfr_sku).trim();
        if (code && String(t.product_url).includes(code)) {
          return { product_url: String(t.product_url).split(code).join(recoveredMfr), mfr_sku: recoveredMfr, handle: null, synthesized: true };
        }
      }
    } catch { /* template sample optional — fall through to the honest not-found */ }
  }
  return { error: 'SKU not found in this vendor catalog' };
}

// nav-menu strip regex — vendor PDPs often embed "Discontinued List" / "Sale List" /
// "Check Stock" links in global nav; strip before reading stock state to avoid false reads.
// Used with .replace() only (not .test()/.exec()), so the /g flag is safe as a shared export.
const NAV_RE = /(discontinued|sale|stock)\s+list|check\s+stock|stock\s+list/ig;

// Shared Browserbase session teardown — identical across every portal/public adapter.
// Swallows all errors so a partial close never masks the real scrape result.
async function releaseSession({ browser, bb, session }) {
  try { if (browser) await browser.close(); } catch {}
  try { if (bb && session) await bb.sessions.update(session.id, { projectId: session.projectId, status: 'REQUEST_RELEASE' }); } catch {}
}

module.exports = { resolveCatalogUrl, NAV_RE, releaseSession };