`src/match.ts`

175 lines
// directory-core / match
//
// Geographic-anchor + bounding-box primitives shared by every directory's
// "match-providers-near-this-lead" feature.
//
// Source: extracted from `lawyer-directory-builder/src/lib/match_firms.ts` 2026-05-04.
// The original file fused these primitives with lawyer-specific column choices
// (organizations.type='law_firm', attorney_count, marketing_score, site_audits
// join). This module exports JUST the vertical-neutral pieces; each consumer
// composes its own matching SQL on top.
//
// What's IN this module:
//   - BoundingBox + the canonical CA / LA bounds Steve uses
//   - EmptyReason discriminator for honest empty-state UX
//   - resolveZipAnchor(query, zip, opts) — exact-zip → 3-digit-prefix fallback
//
// What's NOT in this module:
//   - The actual provider-ranking SQL (every vertical has different score columns)
//   - Per-vertical defaults (LA centroid, lawyer's attorney_count tiebreaker)
//
// The architect's review (2026-05-04) flagged the original `matchFirmsForLead`
// as too schema-coupled to extract. We're extracting only the generic geographic
// resolution layer beneath it. Each vertical writes ~15 lines of its own ranking
// SQL on top.

import type { LatLng } from './geo.js';
import { assertSafeIdent } from './_ident.js';

export interface BoundingBox {
  latMin: number;
  latMax: number;
  lngMin: number;
  lngMax: number;
}

/** California bounding box. Used as a hard filter against bad state-column data. */
export const BBOX_CA: BoundingBox = {
  latMin: 32.5, latMax: 42.05, lngMin: -124.5, lngMax: -114.0,
};

/** Continental US bounding box (excludes AK/HI/territories). */
export const BBOX_US_CONTINENTAL: BoundingBox = {
  latMin: 24.5, latMax: 49.5, lngMin: -125.0, lngMax: -66.5,
};

/** Default fallback centroid for CA-only verticals when a ZIP doesn't resolve. */
export const CENTROID_LA: LatLng = { lat: 34.0522, lng: -118.2437 };

/**
 * Why is the match result empty? Front-end uses this to render an honest
 * empty state instead of misleading "widen your search radius" copy.
 */
export type EmptyReason =
  | 'ok'                      // results returned, no error
  | 'no_zip'                  // caller didn't supply a ZIP
  | 'no_anchor'               // ZIP given but no records exist in that ZIP or 3-digit prefix
  | 'no_providers_in_radius'; // anchor exists but radius cap excluded everyone

/** Generic query function shape — matches pg's `pool.query` and directory-core/db's `query`. */
export type QueryFn = <T = unknown>(text: string, params: unknown[]) => Promise<{ rows: T[] }>;

export interface ResolveZipOpts {
  /** Table to compute the centroid from. Must have `lat`, `lng`, and `zip` columns. */
  table: string;
  /** Optional additional WHERE clause appended after `zip = $1`. Example: `"type='law_firm'"`. */
  whereExtra?: string;
  /** Bounding box hard-filter to defend against bad lat/lng data. Default: BBOX_CA. */
  bbox?: BoundingBox;
}

/**
 * Resolve a ZIP code to a geographic centroid by averaging the lat/lng of
 * existing records in that ZIP. Two-step fallback:
 *   1. Exact ZIP match → centroid of all records in that ZIP
 *   2. First-3-digit ZIP prefix (same SCF region) → centroid of all records in that prefix
 *   3. null → caller decides (fallback centroid, return empty, etc.)
 *
 * This is preferable to a ZIP-centroids API for established directories: if
 * you already have N records geocoded in that ZIP, their centroid is more
 * accurate than the postal-service centroid (which can be miles off in
 * sprawling rural ZIPs).
 *
 * Returns null if neither exact nor prefix match yields any records.
 */
export async function resolveZipAnchor(
  query: QueryFn,
  zip: string | null | undefined,
  opts: ResolveZipOpts,
): Promise<LatLng | null> {
  if (!zip || typeof zip !== 'string') return null;
  const z = zip.trim();
  if (!/^[0-9]{3,5}$/.test(z)) return null;

  // Audit P1-c 2026-05-04: opts.table is interpolated into SQL — gate it.
  // opts.whereExtra remains documented as caller-supplied SQL (its whole point
  // is to inject a vertical-specific predicate); callers are expected to keep
  // it as a hard-coded literal, never thread request data through it.
  assertSafeIdent(opts.table, 'resolveZipAnchor.table');

  const bbox = opts.bbox ?? BBOX_CA;
  const extra = opts.whereExtra ? `AND (${opts.whereExtra})` : '';

  // Step 1: exact ZIP match
  if (z.length === 5) {
    const r1 = await query<{ lat: number | null; lng: number | null; n: number }>(
      `SELECT AVG(lat)::float8 AS lat, AVG(lng)::float8 AS lng, COUNT(*)::int AS n
       FROM ${opts.table}
       WHERE zip = $1
         AND lat BETWEEN $2 AND $3 AND lng BETWEEN $4 AND $5
         ${extra}`,
      [z, bbox.latMin, bbox.latMax, bbox.lngMin, bbox.lngMax],
    );
    const row = r1.rows[0];
    if (row && row.n > 0 && row.lat !== null && row.lng !== null) {
      return { lat: row.lat, lng: row.lng };
    }
  }

  // Step 2: 3-digit ZIP prefix
  const prefix = z.slice(0, 3);
  if (prefix.length === 3) {
    const r2 = await query<{ lat: number | null; lng: number | null; n: number }>(
      `SELECT AVG(lat)::float8 AS lat, AVG(lng)::float8 AS lng, COUNT(*)::int AS n
       FROM ${opts.table}
       WHERE zip LIKE $1
         AND lat BETWEEN $2 AND $3 AND lng BETWEEN $4 AND $5
         ${extra}`,
      [prefix + '%', bbox.latMin, bbox.latMax, bbox.lngMin, bbox.lngMax],
    );
    const row = r2.rows[0];
    if (row && row.n > 0 && row.lat !== null && row.lng !== null) {
      return { lat: row.lat, lng: row.lng };
    }
  }

  return null;
}

/**
 * SQL fragment that computes great-circle distance in km between a fixed
 * (lat, lng) pair (passed as $latParam, $lngParam) and a row's (lat, lng)
 * columns (named by `latCol` and `lngCol`).
 *
 * Use as a SELECT expression to add a distance_km column without needing
 * the PostGIS or earthdistance extensions:
 *
 *   const sql = `SELECT id, ${haversineKmSql('o.lat', 'o.lng', '$1', '$2')} AS distance_km
 *                FROM organizations o ORDER BY distance_km LIMIT $3`;
 *   await query(sql, [centroid.lat, centroid.lng, 5]);
 */
export function haversineKmSql(latCol: string, lngCol: string, latParam: string, lngParam: string): string {
  return `(2 * 6371 * ASIN(SQRT(
    POW(SIN(RADIANS((${latCol} - ${latParam}) / 2)), 2) +
    COS(RADIANS(${latParam})) * COS(RADIANS(${latCol})) *
    POW(SIN(RADIANS((${lngCol} - ${lngParam}) / 2)), 2)
  )))::float8`;
}

/**
 * SQL fragment that asserts a row's (lat, lng) falls within a bounding box.
 * Use as a WHERE-clause predicate. Caller binds the four bounds as the
 * specified positional params.
 *
 *   `WHERE ${withinBoundingBoxSql('o.lat', 'o.lng', '$1', '$2', '$3', '$4')}`
 *   query.params: [bbox.latMin, bbox.latMax, bbox.lngMin, bbox.lngMax]
 */
export function withinBoundingBoxSql(
  latCol: string, lngCol: string,
  latMinParam: string, latMaxParam: string,
  lngMinParam: string, lngMaxParam: string,
): string {
  return `${latCol} BETWEEN ${latMinParam} AND ${latMaxParam} AND ` +
         `${lngCol} BETWEEN ${lngMinParam} AND ${lngMaxParam}`;
}