`scripts/link_locations_to_orgs.js`

94 lines
#!/usr/bin/env node
/**
 * Backfill professional_locations.organization_id.
 *
 * The NPI bulk importer writes a professional_locations row per Type-1 NPI
 * but never sets organization_id (Type-1 rows don't carry an org NPI). This
 * leaves every ZIP/neighborhood query returning 0 doctors per org.
 *
 * Strategy — two passes, conservative first:
 *   1. addr_norm + phone match (single best org per location)
 *   2. addr_norm only, but only when exactly one org sits at that addr_norm
 *
 * Re-runnable: only touches rows where organization_id IS NULL.
 */
const { pool, query } = require('../agents/shared/db');

async function pass1Strict() {
  console.log('[link] pass 1: strict (addr_norm + phone) match…');
  const r = await query(`
    WITH cand AS (
      SELECT pl.id AS pl_id, o.id AS org_id,
             ROW_NUMBER() OVER (
               PARTITION BY pl.id
               ORDER BY o.id
             ) AS rn
        FROM professional_locations pl
        JOIN organizations o
          ON lower(regexp_replace(pl.address, '[^a-zA-Z0-9]', '', 'g')) =
             lower(regexp_replace(o.address,  '[^a-zA-Z0-9]', '', 'g'))
         AND pl.phone = o.phone
       WHERE pl.organization_id IS NULL
         AND pl.address IS NOT NULL
         AND o.address IS NOT NULL
         AND o.opted_out = FALSE
    )
    UPDATE professional_locations pl
       SET organization_id = cand.org_id
      FROM cand
     WHERE pl.id = cand.pl_id AND cand.rn = 1
    RETURNING pl.id
  `, []);
  console.log(`[link] pass 1 linked ${r.rowCount} locations`);
  return r.rowCount;
}

async function pass2AddrUnique() {
  console.log('[link] pass 2: addr_norm match where exactly one org at that addr_norm…');
  const r = await query(`
    WITH org_singletons AS (
      SELECT lower(regexp_replace(address, '[^a-zA-Z0-9]', '', 'g')) AS a_norm,
             MIN(id) AS org_id,
             COUNT(*) AS n
        FROM organizations
       WHERE address IS NOT NULL AND opted_out = FALSE
       GROUP BY 1
      HAVING COUNT(*) = 1
    )
    UPDATE professional_locations pl
       SET organization_id = s.org_id
      FROM org_singletons s
     WHERE pl.organization_id IS NULL
       AND pl.address IS NOT NULL
       AND lower(regexp_replace(pl.address, '[^a-zA-Z0-9]', '', 'g')) = s.a_norm
    RETURNING pl.id
  `, []);
  console.log(`[link] pass 2 linked ${r.rowCount} locations`);
  return r.rowCount;
}

async function summary() {
  const r = await query(`
    SELECT
      (SELECT COUNT(*) FROM professional_locations) AS total,
      (SELECT COUNT(*) FROM professional_locations WHERE organization_id IS NOT NULL) AS linked,
      (SELECT COUNT(*) FROM professional_locations WHERE organization_id IS NULL) AS unlinked
  `, []);
  const row = r.rows[0];
  const pct = ((Number(row.linked) / Number(row.total)) * 100).toFixed(1);
  console.log(`[link] total=${row.total}  linked=${row.linked} (${pct}%)  unlinked=${row.unlinked}`);
}

async function main() {
  await pass1Strict();
  await pass2AddrUnique();
  await summary();
  await pool.end();
}

main().catch(async (err) => {
  console.error('[link] fatal:', err);
  try { await pool.end(); } catch (_) {}
  process.exit(1);
});