-- Blocked IPs Table for DDoS Protection
-- This table stores blocked IP addresses with audit trail and persistence

CREATE TABLE IF NOT EXISTS blocked_ips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,  -- Supports both IPv4 and IPv6
    blocked_until DATETIME NOT NULL,  -- When the block expires
    reason VARCHAR(100) DEFAULT 'ddos_protection',  -- Reason for blocking
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for better performance
    INDEX idx_ip_address (ip_address),
    INDEX idx_blocked_until (blocked_until),
    INDEX idx_reason (reason),
    INDEX idx_created_at (created_at),
    
    -- Unique constraint to prevent duplicate entries
    UNIQUE KEY unique_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Create a view for active blocks
CREATE OR REPLACE VIEW active_blocked_ips AS
SELECT 
    ip_address,
    blocked_until,
    reason,
    created_at,
    TIMESTAMPDIFF(MINUTE, NOW(), blocked_until) as minutes_remaining
FROM blocked_ips 
WHERE blocked_until > NOW()
ORDER BY created_at DESC;

-- Optional: Create a stored procedure to clean up expired blocks
DELIMITER //
CREATE PROCEDURE CleanupExpiredBlocks()
BEGIN
    DELETE FROM blocked_ips WHERE blocked_until < NOW();
    SELECT ROW_COUNT() as expired_blocks_removed;
END //
DELIMITER ;

-- Optional: Create an event to automatically clean up expired blocks
-- (Uncomment if you want automatic cleanup)
-- CREATE EVENT IF NOT EXISTS cleanup_expired_blocks
-- ON SCHEDULE EVERY 1 HOUR
-- DO
--   DELETE FROM blocked_ips WHERE blocked_until < NOW();
