-- V3 — refresh tokens with rotation + reuse detection (§4). Only the token hash
-- is stored; replaced_by_id links a rotated token to its successor for chain revocation.

CREATE TABLE refresh_tokens (
    id             uuid        NOT NULL,
    user_id        uuid        NOT NULL,
    token_hash     varchar(64) NOT NULL,
    expires_at     timestamptz NOT NULL,
    revoked_at     timestamptz,
    replaced_by_id uuid,
    user_agent     varchar(512),
    ip             varchar(45),
    created_at     timestamptz NOT NULL,
    CONSTRAINT pk_refresh_tokens PRIMARY KEY (id),
    CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_rt_replaced FOREIGN KEY (replaced_by_id) REFERENCES refresh_tokens (id),
    CONSTRAINT ux_rt_token_hash UNIQUE (token_hash)
);

CREATE INDEX ix_rt_user ON refresh_tokens (user_id);