-- Revoked tokens table for JWT token blacklisting
-- Stores JWT tokens that have been invalidated (e.g., on logout)
-- Used to prevent replay attacks and enforce token invalidation

CREATE TABLE IF NOT EXISTS revoked_tokens (
    id TEXT PRIMARY KEY NOT NULL,

    -- JWT token (stored as hash for security, or truncated)
    -- Store token_jti (JWT ID) claim instead of full token
    token_jti TEXT NOT NULL UNIQUE,

    -- User who owned the token
    user_id TEXT,

    -- Reason for revocation (logout, password_change, admin_revoke, etc.)
    reason TEXT NOT NULL DEFAULT 'logout',

    -- When the token was revoked
    revoked_at TEXT NOT NULL,

    -- When the token would have expired (for cleanup)
    -- Revoked tokens older than their expiration can be deleted
    expires_at TEXT NOT NULL,

    -- Additional metadata for audit trail
    ip_address TEXT,
    user_agent TEXT,

    -- Indexes for fast lookups
    UNIQUE(token_jti)
);

-- Index for efficient cleanup of expired revoked tokens
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_expires_at ON revoked_tokens(expires_at);

-- Index for user-specific token lookups (e.g., revoke all user tokens)
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_user_id ON revoked_tokens(user_id) WHERE user_id IS NOT NULL;

-- Index for JTI lookup (most common query)
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_jti ON revoked_tokens(token_jti);

-- Enable foreign keys
PRAGMA foreign_keys = ON;
