-- Create audit trail table for login monitoring
CREATE TABLE IF NOT EXISTS audit_trail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    user_type ENUM('admin', 'company', 'candidate') NOT NULL,
    email VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    status ENUM('success', 'failed', 'blocked') NOT NULL,
    failure_reason VARCHAR(255),
    session_id VARCHAR(128),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_type_created (user_type, created_at),
    INDEX idx_email_created (email, created_at),
    INDEX idx_status_created (status, created_at),
    INDEX idx_ip_created (ip_address, created_at)
);