`scripts/firm-phone-coverage.mjs`

68 lines
#!/usr/bin/env node
// firm-phone-coverage.mjs — TK-10687 "every firm has a phone / every broker callable" tracker.
// READ-ONLY. Measures how close we are to Steve's bar and surfaces the shortfall = the work-queue
// the enrich-firm-contacts driver drives to zero. Emits PASS/WARN/FAIL (fleet-health vocabulary) so
// it can plug into fleet-health-rollup, and writes data/firm-phone-coverage.json each run.
//
// Two headline numbers (CA, active-license scope):
//   1. FIRM coverage  — % of firms-with-an-active-broker that now have a phone.
//   2. BROKER callable — % of active brokers with a resolvable phone (own OR firm fallback OR
//      firm_contacts) — the thing the RE directory actually shows.
//
// Usage: node scripts/firm-phone-coverage.mjs        (prints report + writes json)
'use strict';
import pg from 'pg';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const DB = process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp';
const ACTIVE = `(license_status ILIKE 'active%' OR license_status ILIKE 'licensed%' OR license_status ILIKE 'current / active%')`;
const __dir = path.dirname(fileURLToPath(import.meta.url));
const OUT = path.join(__dir, '..', 'data', 'firm-phone-coverage.json');

async function main() {
  const pool = new pg.Pool({ connectionString: DB });
  const q = (s) => pool.query(s).then(r => r.rows[0]);

  const firm = await q(`
    WITH af AS (SELECT DISTINCT firm_id FROM broker WHERE firm_id IS NOT NULL AND license_state='CA' AND ${ACTIVE})
    SELECT count(*)::int total,
           count(*) FILTER (WHERE coalesce(nullif(f.phone,''),'')<>'')::int with_phone
      FROM firm f JOIN af ON af.firm_id=f.id`);

  // A broker is "callable" if it has its own phone, OR its firm has a phone, OR the firm has a
  // crawled firm_contacts phone — exactly the /api/brokers COALESCE resolution.
  const broker = await q(`
    SELECT count(*)::int total,
           count(*) FILTER (WHERE
             coalesce(nullif(b.phone,''),'')<>''
             OR coalesce(nullif(f.phone,''),'')<>''
             OR EXISTS (SELECT 1 FROM firm_contacts fc WHERE fc.firm_id=f.id AND fc.kind='phone')
           )::int callable
      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
     WHERE b.license_state='CA' AND b.firm_id IS NOT NULL AND ${ACTIVE}`);

  const firmPct = firm.total ? +(100 * firm.with_phone / firm.total).toFixed(1) : 0;
  const brokerPct = broker.total ? +(100 * broker.callable / broker.total).toFixed(1) : 0;
  const firmMissing = firm.total - firm.with_phone;
  const brokerMissing = broker.total - broker.callable;

  // verdict on the BROKER-callable number (the customer-facing goal). PASS ≥99%, WARN ≥80%, else FAIL.
  const verdict = brokerPct >= 99 ? 'PASS' : brokerPct >= 80 ? 'WARN' : 'FAIL';

  const rec = {
    verdict, ts: new Date().toISOString(), scope: 'CA active-license',
    firms: { total: firm.total, with_phone: firm.with_phone, missing: firmMissing, pct: firmPct },
    brokers: { total: broker.total, callable: broker.callable, missing: brokerMissing, pct: brokerPct },
  };
  try { fs.mkdirSync(path.dirname(OUT), { recursive: true }); fs.writeFileSync(OUT, JSON.stringify(rec, null, 2)); } catch {}

  console.log(`[${verdict}] firm-phone coverage (CA active-license)`);
  console.log(`  FIRMS w/ active broker : ${firm.with_phone.toLocaleString()}/${firm.total.toLocaleString()} have a phone (${firmPct}%) · ${firmMissing.toLocaleString()} still missing`);
  console.log(`  ACTIVE BROKERS callable: ${broker.callable.toLocaleString()}/${broker.total.toLocaleString()} (${brokerPct}%) · ${brokerMissing.toLocaleString()} not yet callable`);
  console.log(`  → work-queue: enrich the ${firmMissing.toLocaleString()} phoneless firms (firm-phone-first) to drive both toward 100%.`);
  await pool.end();
}
main().catch(e => { console.error('FATAL', e); process.exit(1); });