-- One-shot tokens used by the forgot-password and email-verification flows.
--
-- We never store the raw token; only the SHA-256 hash. The plaintext lives
-- only in the email link. This means a DB leak can't be replayed against the
-- live flow.
--
-- `purpose` discriminates the two flows so we can keep one narrow table:
--   'password_reset'      → expires in ~1h
--   'email_verification'  → expires in ~24h
--
-- `used_at` is set the first time a token is consumed. The handler must check
-- both `used_at IS NULL` AND `expires_at > now()` before honouring a token.
CREATE TABLE IF NOT EXISTS auth_tokens (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    purpose     VARCHAR(32)  NOT NULL,
    token_hash  CHAR(64)     NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ  NOT NULL,
    used_at     TIMESTAMPTZ,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CHECK (purpose IN ('password_reset', 'email_verification'))
);

CREATE INDEX IF NOT EXISTS auth_tokens_user_purpose_idx
    ON auth_tokens (user_id, purpose);

CREATE INDEX IF NOT EXISTS auth_tokens_expires_at_idx
    ON auth_tokens (expires_at);