-- Migration 057: Add missing contacts fields + UDF storage

ALTER TABLE contacts
  ADD COLUMN IF NOT EXISTS user_defined_fields         JSONB,
  ADD COLUMN IF NOT EXISTS middle_initial              VARCHAR(10),
  ADD COLUMN IF NOT EXISTS note                        TEXT,
  ADD COLUMN IF NOT EXISTS external_id                VARCHAR(50),
  ADD COLUMN IF NOT EXISTS country_id                  INTEGER,
  ADD COLUMN IF NOT EXISTS company_location_id         BIGINT,
  ADD COLUMN IF NOT EXISTS create_date                 TIMESTAMP WITHOUT TIME ZONE,
  ADD COLUMN IF NOT EXISTS is_opted_out_from_bulk_email BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS bulk_email_opt_out_time     TIMESTAMP WITHOUT TIME ZONE,
  ADD COLUMN IF NOT EXISTS solicitation_opt_out_time   TIMESTAMP WITHOUT TIME ZONE,
  ADD COLUMN IF NOT EXISTS survey_opt_out              BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS receives_email_notifications BOOLEAN DEFAULT true,
  ADD COLUMN IF NOT EXISTS billing_contact             BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS impersonator_creator_resource_id BIGINT;

-- GIN index for UDF queries (e.g. find by UserID, O365License)
CREATE INDEX IF NOT EXISTS idx_contacts_udfs ON contacts USING gin(user_defined_fields);