DO $$
BEGIN
  CREATE TYPE kyc_status_enum AS ENUM ('PENDING', 'APPROVED', 'REJECTED');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS contact_address text,
  ADD COLUMN IF NOT EXISTS contact_street text,
  ADD COLUMN IF NOT EXISTS contact_postal_code text,
  ADD COLUMN IF NOT EXISTS contact_country_iso2 text,
  ADD COLUMN IF NOT EXISTS employment_employed boolean,
  ADD COLUMN IF NOT EXISTS employment_status text,
  ADD COLUMN IF NOT EXISTS employer_name text,
  ADD COLUMN IF NOT EXISTS employer_address text,
  ADD COLUMN IF NOT EXISTS employment_position text,
  ADD COLUMN IF NOT EXISTS referrer_name text,
  ADD COLUMN IF NOT EXISTS referrer_phone text,
  ADD COLUMN IF NOT EXISTS referrer_address text,
  ADD COLUMN IF NOT EXISTS referrer_street text,
  ADD COLUMN IF NOT EXISTS referrer_city text,
  ADD COLUMN IF NOT EXISTS referrer_postal_code text,
  ADD COLUMN IF NOT EXISTS consent_accepted boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS consent_accepted_at timestamp,
  ADD COLUMN IF NOT EXISTS next_of_kin_first_name text,
  ADD COLUMN IF NOT EXISTS next_of_kin_last_name text,
  ADD COLUMN IF NOT EXISTS next_of_kin_other_names text,
  ADD COLUMN IF NOT EXISTS next_of_kin_gender text,
  ADD COLUMN IF NOT EXISTS next_of_kin_residential_address text,
  ADD COLUMN IF NOT EXISTS next_of_kin_street text,
  ADD COLUMN IF NOT EXISTS next_of_kin_city text,
  ADD COLUMN IF NOT EXISTS next_of_kin_postal_code text,
  ADD COLUMN IF NOT EXISTS next_of_kin_email text,
  ADD COLUMN IF NOT EXISTS next_of_kin_phone text,
  ADD COLUMN IF NOT EXISTS next_of_kin_relationship text,
  ADD COLUMN IF NOT EXISTS next_of_kin_occupation text,
  ADD COLUMN IF NOT EXISTS kyc_id_front_url text,
  ADD COLUMN IF NOT EXISTS kyc_id_back_url text,
  ADD COLUMN IF NOT EXISTS kyc_selfie_url text,
  ADD COLUMN IF NOT EXISTS kyc_status kyc_status_enum,
  ADD COLUMN IF NOT EXISTS kyc_submitted_at timestamp,
  ADD COLUMN IF NOT EXISTS kyc_reviewed_at timestamp,
  ADD COLUMN IF NOT EXISTS kyc_reviewed_by_user_id integer,
  ADD COLUMN IF NOT EXISTS kyc_rejection_reason text,
  ADD COLUMN IF NOT EXISTS deleted_at timestamp;

