`scripts/dedupe.js`

115 lines
#!/usr/bin/env node
/**
 * Dedupe + confidence scoring — Stage 8.
 *
 * Runs matchers in priority and writes each professional's final
 * source_confidence_score, plus collapses duplicate organizations.
 *
 * Confidence rubric:
 *   0.85+  NPI present AND license matched (MBC or DCA) AND has primary location
 *   0.75   NPI + license OR  NPI + hospital-affiliation match
 *   0.50   NPI only (single-source)
 *   0.30   No NPI, name-only match
 */
const { pool, query } = require('../agents/shared/db');

async function recomputeProfessionalScores() {
  console.log('[dedupe] recomputing professional confidence scores…');
  await query(`
    UPDATE professionals p SET source_confidence_score = sub.score, updated_at = NOW()
    FROM (
      SELECT
        p.id,
        CASE
          WHEN p.npi_number IS NOT NULL
               AND p.license_number IS NOT NULL
               AND p.license_status IS NOT NULL
               AND EXISTS(SELECT 1 FROM professional_locations pl WHERE pl.professional_id = p.id)
            THEN 0.92
          WHEN p.npi_number IS NOT NULL AND p.license_number IS NOT NULL
            THEN 0.80
          WHEN p.npi_number IS NOT NULL AND EXISTS(
                  SELECT 1 FROM professional_locations pl
                   WHERE pl.professional_id = p.id AND pl.organization_id IS NOT NULL)
            THEN 0.70
          WHEN p.npi_number IS NOT NULL
            THEN 0.55
          ELSE 0.30
        END AS score
      FROM professionals p
    ) sub
    WHERE p.id = sub.id
  `, []);

  const r = await query(`
    SELECT
      ROUND(AVG(source_confidence_score)::numeric, 2) AS avg_score,
      COUNT(*) FILTER (WHERE source_confidence_score >= 0.85) AS high,
      COUNT(*) FILTER (WHERE source_confidence_score >= 0.50 AND source_confidence_score < 0.85) AS medium,
      COUNT(*) FILTER (WHERE source_confidence_score <  0.50) AS low
    FROM professionals
  `, []);
  console.log('[dedupe] confidence buckets →', r.rows[0]);
}

async function dedupeOrgsByNpi() {
  console.log('[dedupe] collapsing duplicate organizations by NPI…');
  // Already enforced by UNIQUE(npi_number); just safety check counts.
  const r = await query(`
    SELECT npi_number, COUNT(*) AS n
    FROM organizations
    WHERE npi_number IS NOT NULL
    GROUP BY npi_number HAVING COUNT(*) > 1
  `, []);
  console.log(`[dedupe] orgs with duplicate NPI: ${r.rowCount}`);
}

async function dedupeOrgsByCcnHcai() {
  console.log('[dedupe] merging orgs that share an HCAI ID or CMS CCN…');
  // Pick canonical row (lowest id), reassign foreign keys, delete the others.
  for (const col of ['hcai_id', 'cdph_license']) {
    const dupes = await query(`
      SELECT ${col} AS key, MIN(id) AS keep_id, ARRAY_AGG(id) AS all_ids
      FROM organizations
      WHERE ${col} IS NOT NULL
      GROUP BY ${col} HAVING COUNT(*) > 1
    `, []);
    for (const dupe of dupes.rows) {
      const drop = dupe.all_ids.filter(x => x !== dupe.keep_id);
      if (drop.length === 0) continue;
      await query(`UPDATE professional_locations SET organization_id = $1 WHERE organization_id = ANY($2)`,
                  [dupe.keep_id, drop]);
      await query(`UPDATE phones                SET organization_id = $1 WHERE organization_id = ANY($2)`,
                  [dupe.keep_id, drop]);
      await query(`UPDATE emails                SET organization_id = $1 WHERE organization_id = ANY($2)`,
                  [dupe.keep_id, drop]);
      await query(`DELETE FROM organizations WHERE id = ANY($1)`, [drop]);
      console.log(`[dedupe] ${col}=${dupe.key} kept=${dupe.keep_id} dropped=${drop.length}`);
    }
  }
}

async function tagPrivatePractice() {
  console.log('[dedupe] tagging single-doctor orgs as private_practice…');
  await query(`
    UPDATE organizations o SET type = 'private_practice', updated_at = NOW()
    WHERE o.type = 'medical_group'
      AND o.id IN (
        SELECT organization_id FROM professional_locations
         WHERE organization_id IS NOT NULL
         GROUP BY organization_id
        HAVING COUNT(DISTINCT professional_id) = 1
      )
  `, []);
}

async function main() {
  await dedupeOrgsByCcnHcai();
  await dedupeOrgsByNpi();
  await tagPrivatePractice();
  await recomputeProfessionalScores();
  await pool.end();
}

main().catch((e) => { console.error('[dedupe] fatal:', e); process.exit(1); });