-- Add security and tracking columns to users table
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `is_banned` TINYINT(1) DEFAULT 0;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `registration_ip` VARCHAR(45) DEFAULT NULL;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_ip` VARCHAR(45) DEFAULT NULL;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `login_attempts` INT DEFAULT 0;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_attempt_time` TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `registration_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Create a table for brute force tracking by IP (for non-existent users)
CREATE TABLE IF NOT EXISTS `login_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(45) NOT NULL,
    `attempts` INT DEFAULT 1,
    `last_attempt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (`ip_address`)
);