`scripts/migrate-crcp.sql`

31 lines
-- Evolve CRUnifiedDB to hold the CRCP full-CRE base alongside the sublease inventory.
ALTER TABLE listings ALTER COLUMN sponsor_id DROP NOT NULL;
ALTER TABLE listings ADD COLUMN IF NOT EXISTS listing_kind TEXT NOT NULL DEFAULT 'sublease'; -- sublease | lease | sale
ALTER TABLE listings ADD COLUMN IF NOT EXISTS asset_class  TEXT;      -- Multifamily | Retail | Office | Mixed-use
ALTER TABLE listings ADD COLUMN IF NOT EXISTS cap_rate     NUMERIC;
ALTER TABLE listings ADD COLUMN IF NOT EXISTS units        INTEGER;
ALTER TABLE listings ADD COLUMN IF NOT EXISTS year_built   INTEGER;
ALTER TABLE listings ADD COLUMN IF NOT EXISTS broker_id    INTEGER;
ALTER TABLE listings ADD COLUMN IF NOT EXISTS source_label TEXT;      -- 'crcp' for the imported base, NULL for sponsor-crawled
-- CRCP rows have no sponsor; dedupe them on (source_label, external_id)
CREATE UNIQUE INDEX IF NOT EXISTS ux_listings_source ON listings(source_label, external_id) WHERE source_label IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_listings_kind ON listings(listing_kind);

-- Real broker directory (from CRCP brokers-snapshot)
CREATE TABLE IF NOT EXISTS brokers (
  id           INTEGER PRIMARY KEY,      -- keep CRCP id
  name         TEXT NOT NULL,
  firm         TEXT,
  agent_type   TEXT,
  phone        TEXT,
  email        TEXT,
  website      TEXT,
  linkedin     TEXT,
  office_addr  TEXT,
  total_assets INTEGER,
  specialties  TEXT,
  source       TEXT DEFAULT 'crcp',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_brokers_firm ON brokers(firm);