CREATE TABLE IF NOT EXISTS auth_tokens (
    id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id            BIGINT UNSIGNED NOT NULL,
    token_hash         VARCHAR(64)     NOT NULL,
    refresh_hash       VARCHAR(64)     NOT NULL,
    device_id          VARCHAR(255)    NOT NULL,
    device_name        VARCHAR(100)    DEFAULT NULL,
    device_type        ENUM('ios','android','web') NOT NULL,
    ip_address         VARCHAR(45)     NOT NULL,
    user_agent         TEXT            DEFAULT NULL,
    expires_at         DATETIME        NOT NULL,
    refresh_expires_at DATETIME        NOT NULL,
    revoked_at         DATETIME        DEFAULT NULL,
    created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_token (token_hash),
    UNIQUE KEY uq_refresh (refresh_hash),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_device (user_id, device_id),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
