`scripts/db/agent-residential-schema.sql`

42 lines
-- agent-residential-schema.sql — adds RESIDENTIAL real-estate agents to the unified person model.
-- Reuses the existing broker/firm graph: a residential listing agent is a broker row with
-- agent_type='residential', their brokerage is a firm row, and broker_condo links them to the
-- cre.condo they listed (mirrors broker_listing for the commercial side). This keeps the mind-map,
-- /api/broker, and the broker/agent views working unchanged for both commercial + residential.
--
-- CCPA posture (unchanged): business-contact fields only (agent name / brokerage / business phone /
-- business email / license #). Per-field provenance recorded in broker_field_source (tier
-- 'redfin-detail'). NO consumer/family data. NO send (pitches stay draft, George-gated).

ALTER TABLE broker ADD COLUMN IF NOT EXISTS agent_type text DEFAULT 'commercial';
ALTER TABLE broker ADD COLUMN IF NOT EXISTS license text;   -- DRE/MLS license # when public

-- Backfill existing rows explicitly (DEFAULT only applies to new rows on some PG versions).
UPDATE broker SET agent_type = 'commercial' WHERE agent_type IS NULL;

CREATE INDEX IF NOT EXISTS idx_broker_agent_type ON broker(agent_type);

-- The residential edge: an agent listed a condo. Mirrors broker_listing for cre.condo.
CREATE TABLE IF NOT EXISTS broker_condo (
  broker_id   integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
  condo_id    text    NOT NULL REFERENCES condo(id)  ON DELETE CASCADE,
  role        text DEFAULT 'listing',     -- listing | co | team
  PRIMARY KEY (broker_id, condo_id)
);
CREATE INDEX IF NOT EXISTS idx_bc_condo  ON broker_condo(condo_id);
CREATE INDEX IF NOT EXISTS idx_bc_broker ON broker_condo(broker_id);

-- broker_node powers the mind-map node weight. Extend it so a node's listing count includes BOTH
-- commercial listings (broker_listing) AND residential condos (broker_condo), and expose agent_type.
-- DROP+recreate (not CREATE OR REPLACE): adding agent_type changes column order, which REPLACE
-- forbids. Keeps total_assets so scripts/export-to-gsheet.js + brokers-db topBrokers stay intact.
DROP VIEW IF EXISTS broker_node;
CREATE VIEW broker_node AS
  SELECT b.id, b.name, f.name AS firm, b.phone, b.email, b.title, b.total_assets, b.agent_type,
         (count(DISTINCT bl.listing_id) + count(DISTINCT bc.condo_id)) AS listings
    FROM broker b
    LEFT JOIN firm f ON f.id = b.firm_id
    LEFT JOIN broker_listing bl ON bl.broker_id = b.id
    LEFT JOIN broker_condo   bc ON bc.broker_id = b.id
   GROUP BY b.id, f.name;