`scripts/stats.js`

69 lines
#!/usr/bin/env node
/**
 * Quick console stats — run any time to see what's in the DB.
 *   node scripts/stats.js
 */
const { pool, query } = require('../agents/shared/db');

async function main() {
  const counts = await query(`
    SELECT
      (SELECT COUNT(*) FROM professionals WHERE opted_out=false) AS professionals,
      (SELECT COUNT(*) FROM organizations WHERE opted_out=false) AS organizations,
      (SELECT COUNT(*) FROM organizations WHERE type='hospital' AND opted_out=false) AS hospitals,
      (SELECT COUNT(*) FROM organizations WHERE type='medical_group' AND opted_out=false) AS medical_groups,
      (SELECT COUNT(*) FROM organizations WHERE type='clinic' AND opted_out=false) AS clinics,
      (SELECT COUNT(*) FROM organizations WHERE type='surgery_center' AND opted_out=false) AS surgery_centers,
      (SELECT COUNT(*) FROM organizations WHERE type='urgent_care' AND opted_out=false) AS urgent_cares,
      (SELECT COUNT(*) FROM organizations WHERE type='fqhc' AND opted_out=false) AS fqhcs,
      (SELECT COUNT(*) FROM organizations WHERE lat IS NOT NULL) AS geocoded_orgs,
      (SELECT COUNT(*) FROM professionals WHERE license_number IS NOT NULL) AS with_license,
      (SELECT COUNT(*) FROM professionals WHERE license_status='Active') AS active_license,
      (SELECT COUNT(*) FROM professionals WHERE medical_school IS NOT NULL) AS with_school,
      (SELECT COUNT(*) FROM raw_records) AS raw_records,
      (SELECT COUNT(*) FROM specialties) AS specialties
  `, []);
  console.table(counts.rows[0]);

  console.log('\nTop specialties:');
  const top = await query(`
    SELECT s.name, COUNT(*) AS n
      FROM professional_specialties ps
      JOIN specialties s ON s.id = ps.specialty_id
      JOIN professionals p ON p.id = ps.professional_id
     WHERE p.opted_out = false
     GROUP BY s.name ORDER BY n DESC LIMIT 15
  `, []);
  console.table(top.rows);

  console.log('\nDoctors per top LA-area ZIP:');
  const byZip = await query(`
    SELECT o.zip, z.city, COUNT(DISTINCT p.id) AS doctors,
           COUNT(DISTINCT o.id) FILTER (WHERE o.type='hospital')   AS hospitals,
           COUNT(DISTINCT o.id)                                    AS orgs
      FROM la_zips z
      LEFT JOIN organizations o ON o.zip = z.zip AND o.opted_out=false
      LEFT JOIN professional_locations pl ON pl.organization_id = o.id
      LEFT JOIN professionals p ON p.id = pl.professional_id AND p.opted_out=false
     GROUP BY o.zip, z.city
     HAVING COUNT(DISTINCT p.id) + COUNT(DISTINCT o.id) > 0
     ORDER BY doctors DESC NULLS LAST LIMIT 20
  `, []);
  console.table(byZip.rows);

  console.log('\nBeverly Hills (90210/90211/90212):');
  const bh = await query(`
    SELECT o.zip, COUNT(DISTINCT o.id) AS orgs,
           COUNT(DISTINCT pl.professional_id) AS doctors
      FROM organizations o
      LEFT JOIN professional_locations pl ON pl.organization_id = o.id
     WHERE o.zip IN ('90210','90211','90212') AND o.opted_out=false
     GROUP BY o.zip ORDER BY o.zip
  `, []);
  console.table(bh.rows);

  await pool.end();
}

main().catch((e) => { console.error(e); process.exit(1); });