-- Migration: Add refresh_tokens table for refresh token mechanism
-- Version: V23
-- Description: Creates refresh_tokens table to store refresh tokens for JWT authentication

CREATE TABLE refresh_tokens
(
    id                UUID                     DEFAULT uuid_generate_v4() PRIMARY KEY,
    token             VARCHAR(500) NOT NULL UNIQUE,
    user_id           UUID         NOT NULL,
    expiry_date       TIMESTAMP    NOT NULL,
    created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at      TIMESTAMP,
    device_fingerprint VARCHAR(500),
    ip_address        VARCHAR(45),
    user_agent        VARCHAR(500),
    revoked           BOOLEAN      NOT NULL DEFAULT FALSE,
    revoked_at        TIMESTAMP,
    revoked_reason    VARCHAR(500),
    CONSTRAINT fk_refresh_token_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Create indexes for better query performance
CREATE INDEX idx_refresh_token_token ON refresh_tokens (token);
CREATE INDEX idx_refresh_token_user_id ON refresh_tokens (user_id);
CREATE INDEX idx_refresh_token_expiry ON refresh_tokens (expiry_date);
CREATE INDEX idx_refresh_token_user_revoked ON refresh_tokens (user_id, revoked, expiry_date);

-- Add comment to table
COMMENT ON TABLE refresh_tokens IS 'Stores refresh tokens for JWT authentication. Refresh tokens are long-lived tokens used to obtain new access tokens.';
COMMENT ON COLUMN refresh_tokens.token IS 'The refresh token value (JWT)';
COMMENT ON COLUMN refresh_tokens.user_id IS 'Reference to the user who owns this refresh token';
COMMENT ON COLUMN refresh_tokens.expiry_date IS 'When this refresh token expires';
COMMENT ON COLUMN refresh_tokens.revoked IS 'Whether this refresh token has been revoked';
COMMENT ON COLUMN refresh_tokens.device_fingerprint IS 'Device fingerprint for tracking which device the token was issued to';
COMMENT ON COLUMN refresh_tokens.ip_address IS 'IP address from which the token was created';
COMMENT ON COLUMN refresh_tokens.user_agent IS 'User agent string from which the token was created';

