-- AUTO TESTER - Security Tables Migration
-- Created: 2026-04-22
-- Description: Create tables for login attempts, rate limiting, and security events

-- ============================================
-- 1. Login Attempts Table
-- ============================================

CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL COMMENT 'User email address',
    `ip_address` VARCHAR(45) COMMENT 'IP address of the request',
    `user_agent` TEXT COMMENT 'User agent string',
    `attempt_type` ENUM('login', 'password_reset', 'api_auth') DEFAULT 'login' COMMENT 'Type of attempt',
    `success` BOOLEAN DEFAULT FALSE COMMENT 'Whether the attempt was successful',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_email` (`email`),
    INDEX `idx_ip_address` (`ip_address`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_email_created` (`email`, `created_at`),
    INDEX `idx_ip_created` (`ip_address`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Track login attempts for security monitoring';

-- ============================================
-- 2. API Rate Limits Table
-- ============================================

CREATE TABLE IF NOT EXISTS `api_rate_limits` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `identifier` VARCHAR(255) NOT NULL COMMENT 'User ID or IP address',
    `endpoint` VARCHAR(255) NOT NULL COMMENT 'API endpoint',
    `method` VARCHAR(10) DEFAULT 'GET' COMMENT 'HTTP method',
    `request_count` INT DEFAULT 1 COMMENT 'Number of requests in window',
    `window_start` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Start of rate limit window',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_identifier` (`identifier`),
    INDEX `idx_endpoint` (`endpoint`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_identifier_endpoint` (`identifier`, `endpoint`),
    INDEX `idx_identifier_created` (`identifier`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API rate limiting tracking';

-- ============================================
-- 3. Security Events Table
-- ============================================

CREATE TABLE IF NOT EXISTS `security_events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `event_type` VARCHAR(50) NOT NULL COMMENT 'Type of security event',
    `severity` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium' COMMENT 'Event severity',
    `email` VARCHAR(255) COMMENT 'Related user email',
    `ip_address` VARCHAR(45) COMMENT 'IP address of the request',
    `user_agent` TEXT COMMENT 'User agent string',
    `details` TEXT COMMENT 'Event details in JSON format',
    `resolved` BOOLEAN DEFAULT FALSE COMMENT 'Whether the event has been resolved',
    `resolved_by` INT COMMENT 'ID of user who resolved the event',
    `resolved_at` DATETIME COMMENT 'When the event was resolved',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_event_type` (`event_type`),
    INDEX `idx_severity` (`severity`),
    INDEX `idx_email` (`email`),
    INDEX `idx_ip_address` (`ip_address`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_resolved` (`resolved`),
    INDEX `idx_severity_created` (`severity`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Security event logging';

-- ============================================
-- 4. Blocked IPs Table
-- ============================================

CREATE TABLE IF NOT EXISTS `blocked_ips` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(45) NOT NULL UNIQUE COMMENT 'Blocked IP address',
    `reason` VARCHAR(255) NOT NULL COMMENT 'Reason for blocking',
    `blocked_by` INT COMMENT 'ID of user who blocked the IP',
    `blocked_until` DATETIME COMMENT 'When the block expires (NULL for permanent)',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT 'Whether the block is currently active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`blocked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_ip_address` (`ip_address`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_blocked_until` (`blocked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blocked IP addresses management';

-- ============================================
-- 5. Security Settings Table
-- ============================================

CREATE TABLE IF NOT EXISTS `security_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Setting key',
    `setting_value` TEXT NOT NULL COMMENT 'Setting value',
    `description` TEXT COMMENT 'Setting description',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT 'Whether the setting is active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_setting_key` (`setting_key`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Security configuration settings';

-- ============================================
-- 6. Insert Default Security Settings
-- ============================================

INSERT INTO `security_settings` (`setting_key`, `setting_value`, `description`) VALUES
('max_login_attempts', '5', 'Maximum number of failed login attempts before lockout'),
('lockout_duration', '900', 'Lockout duration in seconds (15 minutes)'),
('rate_limit_window', '3600', 'Rate limit window in seconds (1 hour)'),
('max_requests_per_window', '1000', 'Maximum requests per rate limit window'),
('password_min_length', '8', 'Minimum password length'),
('password_require_uppercase', '1', 'Require uppercase letters in passwords'),
('password_require_lowercase', '1', 'Require lowercase letters in passwords'),
('password_require_numbers', '1', 'Require numbers in passwords'),
('password_require_special', '1', 'Require special characters in passwords'),
('session_timeout', '3600', 'Session timeout in seconds (1 hour)'),
('enable_captcha', '0', 'Enable CAPTCHA for login'),
('enable_two_factor', '0', 'Enable two-factor authentication'),
('backup_encryption', '1', 'Encrypt database backups'),
('audit_log_retention', '90', 'Audit log retention period in days')
ON DUPLICATE KEY UPDATE 
    `setting_value` = VALUES(`setting_value`),
    `description` = VALUES(`description`),
    `updated_at` = CURRENT_TIMESTAMP;

-- ============================================
-- 7. Create Triggers for Cleanup
-- ============================================

-- Trigger to clean old login attempts
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS `cleanup_old_login_attempts`
AFTER INSERT ON `login_attempts`
FOR EACH ROW
BEGIN
    -- Delete attempts older than 7 days
    DELETE FROM login_attempts 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
    LIMIT 1000;
END$$
DELIMITER ;

-- Trigger to clean old rate limit records
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS `cleanup_old_rate_limits`
AFTER INSERT ON `api_rate_limits`
FOR EACH ROW
BEGIN
    -- Delete rate limit records older than 1 hour
    DELETE FROM api_rate_limits 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
    LIMIT 1000;
END$$
DELIMITER ;

-- ============================================
-- 8. Create Views for Security Monitoring
-- ============================================

-- View for recent security events
CREATE OR REPLACE VIEW `v_recent_security_events` AS
SELECT 
    se.event_type,
    se.severity,
    se.email,
    se.ip_address,
    se.details,
    se.created_at,
    CASE 
        WHEN se.severity = 'critical' THEN 1
        WHEN se.severity = 'high' THEN 2
        WHEN se.severity = 'medium' THEN 3
        WHEN se.severity = 'low' THEN 4
        ELSE 5
    END as priority_level
FROM security_events se
WHERE se.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY priority_level ASC, se.created_at DESC;

-- View for login attempt statistics
CREATE OR REPLACE VIEW `v_login_attempt_stats` AS
SELECT 
    DATE(created_at) as attempt_date,
    COUNT(*) as total_attempts,
    COUNT(DISTINCT email) as unique_emails,
    COUNT(DISTINCT ip_address) as unique_ips,
    SUM(CASE WHEN success = TRUE THEN 1 ELSE 0 END) as successful_attempts,
    SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END) as failed_attempts
FROM login_attempts
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY attempt_date DESC;

-- View for API usage statistics
CREATE OR REPLACE VIEW `v_api_usage_stats` AS
SELECT 
    endpoint,
    method,
    COUNT(*) as total_requests,
    COUNT(DISTINCT identifier) as unique_users,
    MAX(created_at) as last_request,
    DATE(created_at) as request_date
FROM api_rate_limits
WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY endpoint, method, DATE(created_at)
ORDER BY total_requests DESC;

-- ============================================
-- 9. Create Stored Procedures for Security Management
-- ============================================

DELIMITER $$
-- Procedure to get security statistics
CREATE PROCEDURE IF NOT EXISTS `GetSecurityStats`()
BEGIN
    SELECT 
        'Failed Logins (24h)' as metric,
        COUNT(*) as value
    FROM login_attempts 
    WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND success = FALSE
    
    UNION ALL
    
    SELECT 
        'Blocked Requests (24h)' as metric,
        COUNT(*) as value
    FROM security_events 
    WHERE event_type = 'blocked_request' 
    AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    
    UNION ALL
    
    SELECT 
        'Locked Users' as metric,
        COUNT(DISTINCT email) as value
    FROM login_attempts 
    WHERE created_at > DATE_SUB(NOW(), INTERVAL 900 SECOND)
    GROUP BY email 
    HAVING COUNT(*) >= 5;
END$$
DELIMITER ;

DELIMITER $$
-- Procedure to cleanup old security data
CREATE PROCEDURE IF NOT EXISTS `CleanupSecurityData`()
BEGIN
    -- Delete old login attempts (older than 30 days)
    DELETE FROM login_attempts 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    -- Delete old security events (older than 90 days)
    DELETE FROM security_events 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- Delete inactive blocked IPs
    DELETE FROM blocked_ips 
    WHERE is_active = FALSE 
    AND updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    
    -- Select cleanup statistics
    SELECT 
        'Security data cleanup completed' as message,
        ROW_COUNT() as rows_affected;
END$$
DELIMITER ;

DELIMITER $$
-- Procedure to block IP address
CREATE PROCEDURE IF NOT EXISTS `BlockIPAddress`(
    IN p_ip_address VARCHAR(45),
    IN p_reason VARCHAR(255),
    IN p_blocked_by INT,
    IN p_blocked_until DATETIME
)
BEGIN
    INSERT INTO blocked_ips (ip_address, reason, blocked_by, blocked_until)
    VALUES (p_ip_address, p_reason, p_blocked_by, p_blocked_until)
    ON DUPLICATE KEY UPDATE 
        reason = p_reason,
        blocked_by = p_blocked_by,
        blocked_until = p_blocked_until,
        is_active = TRUE,
        updated_at = CURRENT_TIMESTAMP;
    
    SELECT 
        'IP address blocked successfully' as message,
        p_ip_address as ip_address,
        p_reason as reason;
END$$
DELIMITER ;

-- ============================================
-- 10. Add Indexes for Performance
-- ============================================

-- Composite indexes for common queries
ALTER TABLE login_attempts 
ADD INDEX `idx_email_success_created` (`email`, `success`, `created_at`);

ALTER TABLE security_events 
ADD INDEX `idx_type_severity_created` (`event_type`, `severity`, `created_at`);

ALTER TABLE api_rate_limits 
ADD INDEX `idx_identifier_endpoint_created` (`identifier`, `endpoint`, `created_at`);

-- ============================================
-- 11. Grant Permissions (if needed)
-- ============================================

-- Note: Add specific user permissions here if required
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.login_attempts TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.api_rate_limits TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.security_events TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.blocked_ips TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON auto_tester.security_settings TO 'autotester_app'@'localhost';

-- ============================================
-- Migration Complete
-- ============================================

-- Log migration completion
INSERT INTO system_logs (log_type, message, created_at) 
VALUES ('migration', 'Security tables migration completed successfully', NOW())
ON DUPLICATE KEY UPDATE message = message;
