`db/migrations/013_building_roster.sql`

65 lines
-- Migration 013 — building roster view + multi-tenant grouping
-- Powers /buildings.html — one card per address showing every tenant + walked/pitched status.
-- Strips SUITE/STE/UNIT/# tokens from address to derive a canonical building key
-- (street_name in businesses table has the suite concatenated — see iter 53 notes).

CREATE OR REPLACE VIEW v_building_canonical AS
SELECT
  b.id,
  b.name,
  b.address,
  b.city,
  b.zip,
  b.lat,
  b.lng,
  b.phone,
  b.website,
  b.category,
  b.category_naics,
  b.on_corridor,
  -- Canonical building address: strip everything from "SUITE"/"STE"/"UNIT"/"#" onward
  TRIM(regexp_replace(b.address, '\s*(SUITE|STE|UNIT|#).*$', '', 'i')) AS bldg_address,
  -- Suite token alone, or '' if none. substring(...from'pattern') is scalar, not set-returning.
  COALESCE(
    substring(b.address from '(?:SUITE|STE|UNIT|#)\s*[#]?\s*([A-Z0-9-]+)'),
    ''
  ) AS suite,
  b.raw->>'primary_naics_description' AS naics
FROM businesses b
WHERE b.on_corridor
  AND b.address IS NOT NULL;

CREATE OR REPLACE VIEW v_building_roster AS
SELECT
  bc.bldg_address,
  bc.city,
  bc.zip,
  -- Only average geocoded points that fall in the LA basin
  -- (BTRC has bad lat/lng for ~5% of rows — some ended up in AZ/NM)
  AVG(bc.lat) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) AS lat,
  AVG(bc.lng) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) AS lng,
  count(*)                                                                          AS tenants_total,
  count(*) FILTER (WHERE p.id IS NOT NULL)                                          AS pitched,
  count(*) FILTER (WHERE p.outreach_channel = 'walk-in' AND p.sent_at IS NOT NULL)  AS walked,
  count(*) FILTER (WHERE p.replied_at IS NOT NULL)                                  AS replied,
  count(*) FILTER (WHERE p.status = 'won')                                          AS won,
  count(*) FILTER (WHERE p.status = 'skip')                                         AS skipped,
  count(*) FILTER (WHERE p.status = 'draft' AND p.sent_at IS NULL)                  AS draft,
  count(*) FILTER (WHERE p.id IS NULL)                                              AS unpitched,
  -- Distance from DW (15442 Ventura — 34.1542, -118.4703).
  -- Equirectangular approximation; 1° lat = 69.1mi, 1° lng @ 34°N ≈ 57.3mi.
  ROUND(
    (SQRT(
      POW((AVG(bc.lat) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) - 34.1542) * 69.1, 2) +
      POW((AVG(bc.lng) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) - -118.4703) * 57.3, 2)
    ))::numeric, 2
  ) AS dw_miles,
  -- Most-common pitch_type for this building
  MODE() WITHIN GROUP (ORDER BY p.pitch_type) FILTER (WHERE p.pitch_type IS NOT NULL) AS dominant_pitch_type
FROM v_building_canonical bc
LEFT JOIN pitches p ON p.business_id = bc.id
WHERE bc.bldg_address IS NOT NULL AND bc.bldg_address <> ''
GROUP BY bc.bldg_address, bc.city, bc.zip
HAVING count(*) >= 2
ORDER BY count(*) DESC;