`src/enrich/btrc_match.ts`

84 lines
/**
 * btrc_match — populate has_btrc_match for every non-la_btrc business on the corridor.
 * A business is "matched" if any la_btrc row exists at the same street_number ± 5
 * on Ventura Blvd. Otherwise → "POSSIBLY NO BUSINESS LICENSE".
 */
import { query, pool } from '../../db/pool.ts';

async function main() {
  const t0 = Date.now();

  // Reset state
  await query(`UPDATE businesses
    SET has_btrc_match = NULL, btrc_match_score = NULL, btrc_matched_id = NULL, btrc_match_at = NULL
    WHERE on_corridor AND source != 'la_btrc'`);

  // For each non-btrc corridor business, find best btrc match at same street_number (±5).
  // Match score = (street_num_exact ? 1 : 0.7) + name-similarity (trigram, scaled 0..1).
  const sql = `
    WITH non_btrc AS (
      SELECT id, name, street_number, zip,
             NULLIF(regexp_replace(street_number,'[^0-9]','','g'),'')::INT AS sn_int
      FROM businesses
      WHERE on_corridor AND source != 'la_btrc' AND street_number ~ '[0-9]'
    ),
    btrc AS (
      SELECT id, name, street_number, zip,
             NULLIF(regexp_replace(street_number,'[^0-9]','','g'),'')::INT AS sn_int
      FROM businesses
      WHERE on_corridor AND source = 'la_btrc' AND street_number ~ '[0-9]'
    ),
    pairs AS (
      SELECT n.id AS n_id,
             b.id AS b_id,
             CASE WHEN n.sn_int = b.sn_int THEN 1.0
                  WHEN ABS(n.sn_int - b.sn_int) <= 5 THEN 0.7
                  ELSE 0
             END AS addr_score,
             similarity(LOWER(n.name), LOWER(b.name)) AS name_score
      FROM non_btrc n
      JOIN btrc b
        ON ABS(n.sn_int - b.sn_int) <= 5
       AND (n.zip = b.zip OR n.zip IS NULL OR b.zip IS NULL)
    ),
    best AS (
      SELECT n_id, b_id, addr_score, name_score,
             (addr_score * 0.4 + name_score * 0.6) AS score,
             ROW_NUMBER() OVER (PARTITION BY n_id ORDER BY (addr_score * 0.4 + name_score * 0.6) DESC) AS rn
      FROM pairs
      WHERE addr_score > 0
    )
    UPDATE businesses b
       SET has_btrc_match   = (best.score >= 0.5),
           btrc_match_score = best.score,
           btrc_matched_id  = best.b_id,
           btrc_match_at    = NOW()
      FROM best
     WHERE b.id = best.n_id AND best.rn = 1
    RETURNING b.id;
  `;
  const r = await query(sql);
  console.log(`[btrc-match] joined ${r.rowCount} businesses against btrc`);

  // Anything still NULL after the join = no btrc candidate at all → flag false
  const r2 = await query(`
    UPDATE businesses
       SET has_btrc_match = FALSE, btrc_match_at = NOW()
     WHERE on_corridor AND source != 'la_btrc' AND has_btrc_match IS NULL
    RETURNING id`);
  console.log(`[btrc-match] no-btrc-candidate flagged: ${r2.rowCount}`);

  const counts = await query(`
    SELECT
      COUNT(*) FILTER (WHERE on_corridor AND source != 'la_btrc') AS non_btrc_total,
      COUNT(*) FILTER (WHERE on_corridor AND source != 'la_btrc' AND has_btrc_match) AS matched,
      COUNT(*) FILTER (WHERE on_corridor AND source != 'la_btrc' AND has_btrc_match = FALSE) AS flagged
    FROM businesses
  `);
  console.log(`[btrc-match] result:`, counts.rows[0]);
  console.log(`[btrc-match] done in ${Date.now() - t0}ms`);
  await pool.end();
}

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