-- 0008_refresh_tokens: opaque refresh tokens backing POST /api/v1/auth/refresh
-- (B2). The long-lived credential that renews the 24h access token, so a client
-- can stay signed in without storing the account password.
--
-- Only the SHA-256 hash of a token is stored (token_hash); the plaintext is
-- returned once at login/refresh and never persisted or logged. revoked_at is
-- NULL while a grant is live; expiry is validated in Go after the hash lookup.
-- The canonical schema is also re-issued idempotently by auth.NewRefreshStore so
-- an in-memory store used by tests stays self-contained; this migration is the
-- authoritative path for an existing database.
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL,
    role       TEXT NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at TEXT NOT NULL,
    revoked_at TEXT,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id
    ON refresh_tokens (user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires_at
    ON refresh_tokens (expires_at);