-- Refresh tokens for long-lived sessions. Each row represents one refresh
-- token, identified by the SHA-256 hash of the random token value (we never
-- store the raw token — same defence-in-depth as a password hash table).
--
-- On rotation, the row that was just used is marked `revoked_at = NOW()` and
-- a new row is inserted. Logout marks the active row revoked as well.
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT        NOT NULL UNIQUE,
    issued_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ NOT NULL,
    revoked_at  TIMESTAMPTZ
);

-- Lookups during /refresh and /logout hit token_hash with a non-null filter;
-- the partial index keeps it small (revoked rows are tombstones).
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_active_hash
    ON refresh_tokens(token_hash)
    WHERE revoked_at IS NULL;
