`scripts/adapters/kravet-db.js`

92 lines
// adapters/kravet-db.js — Kravet-family AUTHORITATIVE-PRICE adapter ($0 DB read, tier DB_AUTHORITATIVE).
//
// The Kravet umbrella (Kravet / Lee Jofa / Brunschwig & Fils / Cole & Son / GP & J Baker /
// Gaston y Daniela / Threads / Baker Lifestyle / Kravet Couture / Kravet Design / Lee Jofa Modern)
// publishes NO live portal stock — but Kravet emails DW an authoritative price-adjustment file,
// ingested daily by the kravet-price-watch skill into dw_unified.kravet_authoritative_pricing
// (mfr_sku → new_whls / new_map). So the honest "live" signal for these lines is the AUTHORITATIVE
// MAP price: instant, $0, no Browserbase session (session_opened:false → the server bills nothing).
//
// PUBLIC-SAFE CONTRACT: this adapter emits ONLY the MAP-derived RETAIL price. new_whls (wholesale)
// is read solely to derive MAP when new_map is null (standing Kravet rule: MAP = wholesale × 1.5)
// and NEVER included in the returned object.
//
// SKU resolution: the grid passes a display sku (dw_sku / variant_sku / mfr code). Bridge through
// shopify_products to recover the row's mfr_sku (Kravet format e.g. "34971.13.0"), then match
// kravet_authoritative_pricing on upper() with cheap format fallbacks (±trailing ".0").

// Candidate pricing-table keys for one raw sku string (Kravet dot format: "34971.13.0").
function candidateKeys(raw) {
  const keys = new Set();
  const s = String(raw || '').trim().toUpperCase();
  if (!s) return keys;
  keys.add(s);
  // ±trailing ".0" — shopify rows usually carry it, but tolerate either side missing it.
  if (s.endsWith('.0')) keys.add(s.slice(0, -2)); else keys.add(s + '.0');
  // DASH→DOT: Kravet WALLCOVERING codes are stored dash-form in Shopify ("W3909-50") but the
  // price file carries them dot-form with a ".0" tail ("W3909.50.0"). Without this, ~1,247 of
  // the ~1,382 unmatched Kravet actives (the whole wallcovering line) silently miss the file.
  if (s.includes('-')) { const dot = s.replace(/-/g, '.'); keys.add(dot); keys.add(dot + '.0'); }
  // leading pattern code (mirrors resolve.js's compound-SKU extraction — dash AND dot shapes).
  const lead = s.match(/^[A-Z]*[0-9]+[-.][0-9A-Z]+/);
  if (lead && lead[0] !== s) { keys.add(lead[0]); keys.add(lead[0] + '.0'); }
  return keys;
}

async function run({ sku, pool, log }) {
  try {
    // 1. Recover the display row's dw_sku + mfr_sku (the pricing table keys on the mfr code).
    const keys = candidateKeys(sku);
    try {
      const { rows } = await pool.query(
        `SELECT dw_sku, mfr_sku, vendor 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`, [sku]);
      if (rows[0]) {
        for (const k of candidateKeys(rows[0].dw_sku)) keys.add(k);
        for (const k of candidateKeys(rows[0].mfr_sku)) keys.add(k);
      }
    } catch { /* shopify_products bridge optional — raw-sku keys still tried */ }
    // Curated backfill bridge — rows whose mirror mfr_sku is empty but were resolved by
    // scripts/backfill-kravet-mfr.js (title / price-consensus / spec / local-model tiers).
    try {
      const { rows } = await pool.query(
        `SELECT mfr_sku FROM kravet_mfr_backfill
          WHERE upper(sku)=upper($1) OR upper(base_sku)=upper($1) LIMIT 1`, [sku]);
      if (rows[0]) for (const k of candidateKeys(rows[0].mfr_sku)) keys.add(k);
    } catch { /* backfill table optional */ }
    if (!keys.size) return { available: false, session_opened: false, reason: 'sku required' };

    // 2. Authoritative price row — exact upper() match on any candidate key.
    const { rows: hits } = await pool.query(
      `SELECT mfr_sku, new_map, new_whls, loaded_at FROM kravet_authoritative_pricing
        WHERE upper(mfr_sku)=ANY($1) LIMIT 1`, [[...keys]]);
    if (!hits.length) {
      return { available: false, session_opened: false,
        reason: 'SKU not in the Kravet authoritative price file' };
    }
    const r = hits[0];
    // MAP retail only — new_map when present, else derive wholesale × 1.5 (standing Kravet MAP rule).
    const price = r.new_map != null ? +(+r.new_map).toFixed(2)
      : (r.new_whls != null ? Math.round(+r.new_whls * 1.5 * 100) / 100 : null);
    if (price == null) {
      return { available: false, session_opened: false,
        reason: 'price file row has no MAP or wholesale to derive from' };
    }
    if (log) log('kravet-db hit', r.mfr_sku);
    // PUBLIC-SAFE: MAP-derived retail ONLY — new_whls/wholesale is never emitted.
    return {
      available: true, session_opened: false, vendor: null,
      in_stock: null, lead_time: null,
      price,
      raw_stock_label: 'authoritative price (Kravet price file)',
      as_of: r.loaded_at ? new Date(r.loaded_at).toISOString() : new Date().toISOString(),
      source: 'kravet_authoritative_pricing',
    };
  } catch (e) {
    return { available: false, session_opened: false, reason: 'kravet-db lookup error: ' + (e.message || e) };
  }
}

module.exports = { run };