BEGIN;

SET ROLE epistemics_role;

ALTER TABLE epistemics.reflection_candidates
  ADD COLUMN IF NOT EXISTS claimed_by TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS claim_expires_at_unix_millis BIGINT NOT NULL DEFAULT 0;

ALTER TABLE epistemics.reflection_candidates
  DROP CONSTRAINT IF EXISTS reflection_candidates_claim_expiry_check;
ALTER TABLE epistemics.reflection_candidates
  ADD CONSTRAINT reflection_candidates_claim_expiry_check
  CHECK (claim_expires_at_unix_millis >= 0);

CREATE INDEX IF NOT EXISTS reflection_candidates_claimable_idx
  ON epistemics.reflection_candidates (state, claim_expires_at_unix_millis, recorded_at_unix_millis, reflection_candidate_id);

RESET ROLE;
COMMIT;