-- 011: Unified identity & assignment lifecycle (owner implementation order 2026-10-28)
--  - Decision 8: role permissions follow validity periods; expired assignments stop
--    granting at permission-load time (no orphan grants between sweeps).
--  - Decision 1: one person, one account — a member file can be linked to at most
--    one account (partial unique index).

-- +up
ALTER TABLE user_roles ADD COLUMN valid_from TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE user_roles ADD COLUMN valid_until TIMESTAMPTZ NULL;
ALTER TABLE user_roles ADD CONSTRAINT ck_user_roles_validity
  CHECK (valid_until IS NULL OR valid_until > valid_from);
CREATE INDEX idx_user_roles_expiry
  ON user_roles(valid_until) WHERE valid_until IS NOT NULL AND revoked_at IS NULL;

CREATE UNIQUE INDEX uq_users_member_link ON users(member_id) WHERE member_id IS NOT NULL;

-- +down
DROP INDEX IF EXISTS uq_users_member_link;
DROP INDEX IF EXISTS idx_user_roles_expiry;
ALTER TABLE user_roles DROP CONSTRAINT IF EXISTS ck_user_roles_validity;
ALTER TABLE user_roles DROP COLUMN IF EXISTS valid_until;
ALTER TABLE user_roles DROP COLUMN IF EXISTS valid_from;