-- BizMatch Phase 2 — module 4: the fields the buyer side actually collects
-- (NDA form + intake sheet). 001_init.sql stays untouched.

-- -------------------------------------------------------------- contact
ALTER TABLE contact ADD COLUMN cell text;

-- ---------------------------------------------------------------- buyer
ALTER TABLE buyer
  ADD COLUMN address               text,
  ADD COLUMN state                 text,
  ADD COLUMN background_experience text,
  ADD COLUMN how_heard             text,
  ADD COLUMN interested_in_updates boolean NOT NULL DEFAULT false;

-- ------------------------------------------------------------------ nda
-- Money stays text on purpose: the paper forms contain things like
-- "1.2M + inventory" or "TBD" that no numeric type survives.
ALTER TABLE nda
  ADD COLUMN total_purchase_price text,
  ADD COLUMN down_payment         text,
  ADD COLUMN intro_date           date;

-- ------------------------------------------- dedup anchors (duplicates)
-- The duplicate check compares names case-insensitively and phone numbers
-- digits-only, so the indexes have to match those expressions exactly.
CREATE INDEX contact_name_lower_idx ON contact (lower(btrim(name)));
CREATE INDEX contact_phone_digits_idx
  ON contact ((regexp_replace(phone, '[^0-9]', '', 'g'))) WHERE phone IS NOT NULL;
CREATE INDEX contact_cell_digits_idx
  ON contact ((regexp_replace(cell, '[^0-9]', '', 'g'))) WHERE cell IS NOT NULL;