`db/migrations/014_building_dupes.sql`

70 lines
-- Migration 014 — same-tenant detection within a building
-- Surfaces business pairs that are likely the same entity in different suites:
--   1. Name similarity > 0.5 via pg_trgm (e.g. "ABRAMS LAW LLC" vs "ABRAMS & ASSOC LAW")
--   2. Shared phone number (different suite, same line)
--   3. Shared website domain
-- Materialized so /api/buildings/:bldg/dupes is fast even on big buildings (575 tenants at 15821).

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE OR REPLACE VIEW v_business_normalized AS
SELECT
  bc.id, bc.name, bc.address, bc.suite, bc.bldg_address, bc.city, bc.zip,
  bc.phone, bc.website,
  -- Normalize name: strip LLC/INC/CORP/CO/LTD/LP suffixes; collapse whitespace; lowercase
  LOWER(TRIM(regexp_replace(
    bc.name,
    '\s+(LLC|L\.L\.C\.|INC|INC\.|INCORPORATED|CORP|CORP\.|CORPORATION|CO|CO\.|COMPANY|LTD|LTD\.|LP|LLP|PROFESSIONAL CORPORATION|PC)\.?$',
    '', 'i'
  ))) AS name_norm,
  -- Strip phone formatting: "(818) 555-0123" → "8185550123"
  regexp_replace(COALESCE(bc.phone, ''), '\D', '', 'g') AS phone_digits,
  -- Strip website to bare domain: "https://www.abramslaw.com/about" → "abramslaw.com"
  LOWER(regexp_replace(
    regexp_replace(COALESCE(bc.website, ''), '^https?://(www\.)?', '', 'i'),
    '/.*$', ''
  )) AS website_domain
FROM v_building_canonical bc;

DROP MATERIALIZED VIEW IF EXISTS mv_building_dupes;
CREATE MATERIALIZED VIEW mv_building_dupes AS
SELECT DISTINCT
  bn1.bldg_address,
  bn1.id    AS id_a,
  bn2.id    AS id_b,
  bn1.name  AS name_a,
  bn2.name  AS name_b,
  bn1.suite AS suite_a,
  bn2.suite AS suite_b,
  CASE
    WHEN bn1.phone_digits   = bn2.phone_digits   AND bn1.phone_digits   <> '' THEN 'phone'
    WHEN bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '' THEN 'website'
    ELSE 'name'
  END AS match_type,
  GREATEST(
    similarity(bn1.name_norm, bn2.name_norm),
    CASE WHEN bn1.phone_digits   = bn2.phone_digits   AND bn1.phone_digits   <> '' THEN 1.0 ELSE 0 END,
    CASE WHEN bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '' THEN 1.0 ELSE 0 END
  )::numeric(3,2) AS confidence
FROM v_business_normalized bn1
JOIN v_business_normalized bn2
  ON bn1.bldg_address = bn2.bldg_address
 AND bn1.id < bn2.id   -- avoid pairing a row with itself; produce each pair once
 AND bn1.bldg_address IS NOT NULL
 AND (
       similarity(bn1.name_norm, bn2.name_norm) > 0.5
    OR (bn1.phone_digits   = bn2.phone_digits   AND bn1.phone_digits   <> '')
    OR (bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '')
 );

CREATE INDEX IF NOT EXISTS idx_mv_dupes_bldg ON mv_building_dupes (bldg_address);
CREATE INDEX IF NOT EXISTS idx_mv_dupes_a    ON mv_building_dupes (id_a);
CREATE INDEX IF NOT EXISTS idx_mv_dupes_b    ON mv_building_dupes (id_b);

-- Trigger-style refresh helper (call via /api/buildings/refresh-dupes)
CREATE OR REPLACE FUNCTION refresh_building_dupes() RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW mv_building_dupes;
END;
$$ LANGUAGE plpgsql;