`lib/entity-resolution.js`

267 lines
'use strict';
/**
 * Entity resolution — deterministic + scored candidate matching (spec §12).
 *
 * Rules (in order of priority):
 *  1. Exact normalized domain
 *  2. Exact public email domain
 *  3. Exact normalized name + city + state
 *  4. Exact LinkedIn company URL (from permitted source only)
 *
 * Candidate scoring: 0-1 float.
 * Thresholds: >=0.97 = auto; 0.82-0.9699 = review; <0.82 = no.
 * Merges are REVERSIBLE via merge_audit snapshot.
 */

const { normalizeName } = require('./types');

/**
 * Run deterministic matching rules against an existing org record.
 *
 * @param {{ domain?: string, emailDomain?: string, normalizedName?: string, city?: string, state?: string, linkedinUrl?: string }} candidate
 * @param {{ domain?: string, emailDomain?: string, normalized_name?: string, headquarters_city?: string, headquarters_state?: string, linkedin_url?: string }} existing  - one row from organizations table
 * @returns {{ matched: boolean, rule: string | null, confidence: number }}
 */
function deterministicMatch(candidate, existing) {
  // Rule 1: exact normalized domain (both non-null)
  if (candidate.domain && existing.domain) {
    const cd = String(candidate.domain).toLowerCase().replace(/^www\./, '');
    const ed = String(existing.domain).toLowerCase().replace(/^www\./, '');
    if (cd === ed) {
      return { matched: true, rule: 'EXACT_DOMAIN', confidence: 1.0 };
    }
  }

  // Rule 2: exact public email domain
  if (candidate.emailDomain && existing.emailDomain) {
    const ce = String(candidate.emailDomain).toLowerCase();
    const ee = String(existing.emailDomain).toLowerCase();
    if (ce === ee) {
      return { matched: true, rule: 'EXACT_EMAIL_DOMAIN', confidence: 0.98 };
    }
  }

  // Rule 3: exact normalized name + city + state
  if (candidate.normalizedName && existing.normalized_name) {
    const cn = normalizeName(candidate.normalizedName);
    const en = String(existing.normalized_name).trim();
    const cityMatch =
      !candidate.city ||
      !existing.headquarters_city ||
      String(candidate.city).toLowerCase() === String(existing.headquarters_city).toLowerCase();
    const stateMatch =
      !candidate.state ||
      !existing.headquarters_state ||
      String(candidate.state).toUpperCase() === String(existing.headquarters_state).toUpperCase();
    if (cn === en && cityMatch && stateMatch) {
      return { matched: true, rule: 'EXACT_NAME_CITY_STATE', confidence: 0.97 };
    }
  }

  // Rule 4: exact LinkedIn company URL (must come from a permitted discovery source)
  if (candidate.linkedinUrl && existing.linkedin_url) {
    const normalize = (u) =>
      String(u)
        .toLowerCase()
        .replace(/\/$/, '')
        .replace(/^https?:\/\/(www\.)?/, '');
    if (normalize(candidate.linkedinUrl) === normalize(existing.linkedin_url)) {
      return { matched: true, rule: 'EXACT_LINKEDIN_URL', confidence: 0.99 };
    }
  }

  return { matched: false, rule: null, confidence: 0 };
}

/**
 * Score a candidate pair (a, b) on a 0-1 scale using overlapping signals.
 * Intended for fuzzy / probabilistic candidate generation (not deterministic).
 *
 * @param {Object} a
 * @param {Object} b
 * @returns {number} 0-1 float
 */
function scoreCandidatePair(a, b) {
  let score = 0;
  let signals = 0;

  // Name similarity (Jaccard on trigrams, simplified)
  const na = normalizeName(a.display_name || a.legal_name || '');
  const nb = normalizeName(b.display_name || b.legal_name || '');
  if (na && nb) {
    const nameSim = jaccardTrigram(na, nb);
    score += nameSim * 0.40;
    signals++;
  }

  // Domain match
  if (a.domain && b.domain) {
    const da = String(a.domain).toLowerCase().replace(/^www\./, '');
    const db = String(b.domain).toLowerCase().replace(/^www\./, '');
    score += (da === db ? 1.0 : 0) * 0.30;
    signals++;
  }

  // State match
  if (a.headquarters_state && b.headquarters_state) {
    const sa = String(a.headquarters_state).toUpperCase();
    const sb = String(b.headquarters_state).toUpperCase();
    score += (sa === sb ? 1.0 : 0) * 0.10;
    signals++;
  }

  // City match
  if (a.headquarters_city && b.headquarters_city) {
    const ca = String(a.headquarters_city).toLowerCase().trim();
    const cb = String(b.headquarters_city).toLowerCase().trim();
    score += (ca === cb ? 1.0 : 0) * 0.10;
    signals++;
  }

  // Category overlap
  if (Array.isArray(a.advertiser_categories) && Array.isArray(b.advertiser_categories)) {
    const setA = new Set(a.advertiser_categories);
    const intersection = (b.advertiser_categories || []).filter((c) => setA.has(c));
    const union = new Set([...a.advertiser_categories, ...(b.advertiser_categories || [])]);
    const catSim = union.size > 0 ? intersection.length / union.size : 0;
    score += catSim * 0.10;
    signals++;
  }

  return signals > 0 ? Math.min(1, score) : 0;
}

/** Jaccard similarity on character trigrams of two strings. */
function jaccardTrigram(a, b) {
  const trigrams = (s) => {
    const set = new Set();
    for (let i = 0; i < s.length - 2; i++) set.add(s.slice(i, i + 3));
    return set;
  };
  const ta = trigrams(a);
  const tb = trigrams(b);
  if (ta.size === 0 && tb.size === 0) return 1;
  if (ta.size === 0 || tb.size === 0) return 0;
  let inter = 0;
  for (const t of ta) if (tb.has(t)) inter++;
  return inter / (ta.size + tb.size - inter);
}

/**
 * Classify a candidate pair score into a merge decision.
 *
 * @param {number} score 0-1
 * @returns {'auto' | 'review' | 'no'}
 */
function classifyMerge(score) {
  if (score >= 0.97) return 'auto';
  if (score >= 0.82) return 'review';
  return 'no';
}

/**
 * Perform a reversible merge of mergedId into keptId.
 *
 * Steps:
 *  1. Snapshot the merged org row into merge_audit.
 *  2. Update FK references (organization_id) in child tables.
 *  3. Delete the merged org row.
 *
 * The merge is fully reversible by reading merge_audit.snapshot.
 *
 * @param {Object} pool  - pg Pool instance
 * @param {string} keptId    - UUID of the org to keep
 * @param {string} mergedId  - UUID of the org to merge (will be deleted)
 * @returns {Promise<{merge_audit_id: string}>}
 */
async function reversibleMerge(pool, keptId, mergedId) {
  if (keptId === mergedId) throw new Error('Cannot merge an org into itself.');

  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // 1. Snapshot the merged row
    const snap = await client.query(
      `SELECT row_to_json(o) AS snapshot FROM organizations o WHERE id=$1`,
      [mergedId]
    );
    if (snap.rows.length === 0) {
      throw new Error(`Organization to merge (id=${mergedId}) not found.`);
    }
    const snapshot = snap.rows[0].snapshot;

    const auditRes = await client.query(
      `INSERT INTO merge_audit (kept_id, merged_id, entity_table, snapshot)
       VALUES ($1,$2,'organizations',$3::jsonb)
       RETURNING id`,
      [keptId, mergedId, JSON.stringify(snapshot)]
    );
    const merge_audit_id = auditRes.rows[0].id;

    // 2. Re-parent child rows — tables with organization_id FK
    const CHILD_TABLES = [
      'ad_sightings',
      'campaigns',
      'contact_points',
      'creative_assets',
      'event_relationships',
      'notes',
      'opportunities',
      'opportunity_scores',
      'organization_markets',
      'organization_tags',
      'suppression_requests',
      'tasks',
    ];

    for (const tbl of CHILD_TABLES) {
      // organization_tags and organization_markets have composite PKs — handle ON CONFLICT
      if (tbl === 'organization_tags') {
        await client.query(
          `UPDATE organization_tags SET organization_id=$1 WHERE organization_id=$2`,
          [keptId, mergedId]
        );
        // Remove any duplicates that arose from the re-parent
        await client.query(
          `DELETE FROM organization_tags a USING organization_tags b
           WHERE a.ctid > b.ctid AND a.organization_id=b.organization_id AND a.tag_id=b.tag_id`
        );
      } else if (tbl === 'organization_markets') {
        await client.query(
          `UPDATE organization_markets SET organization_id=$1 WHERE organization_id=$2`,
          [keptId, mergedId]
        );
        await client.query(
          `DELETE FROM organization_markets a USING organization_markets b
           WHERE a.ctid > b.ctid AND a.organization_id=b.organization_id
             AND a.market_id=b.market_id AND a.relationship_type=b.relationship_type`
        );
      } else {
        await client.query(
          `UPDATE ${tbl} SET organization_id=$1 WHERE organization_id=$2`,
          [keptId, mergedId]
        );
      }
    }

    // 3. Delete the merged org (all remaining FKs should be cleared now)
    await client.query(`DELETE FROM organizations WHERE id=$1`, [mergedId]);

    await client.query('COMMIT');
    return { merge_audit_id };
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

module.exports = {
  deterministicMatch,
  scoreCandidatePair,
  classifyMerge,
  reversibleMerge,
};