CREATE TABLE auth_sessions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    refresh_token_hash text NOT NULL UNIQUE,
    user_agent text,
    remote_ip text,
    expires_at timestamptz NOT NULL,
    revoked_at timestamptz,
    revoke_reason text,
    replaced_by_session_id uuid REFERENCES auth_sessions(id) ON DELETE SET NULL,
    last_used_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (revoked_at IS NULL OR revoke_reason IS NOT NULL)
);

CREATE INDEX auth_sessions_user_created_at_idx
    ON auth_sessions (user_id, created_at DESC);

CREATE INDEX auth_sessions_active_user_idx
    ON auth_sessions (user_id, expires_at)
    WHERE revoked_at IS NULL;

CREATE INDEX auth_sessions_expires_at_idx
    ON auth_sessions (expires_at);

CREATE TRIGGER auth_sessions_set_updated_at
BEFORE UPDATE ON auth_sessions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();