-- =====================================================
-- Security Violations Table
-- Run this in phpMyAdmin to add violation tracking
-- =====================================================

-- Create security_violations table
CREATE TABLE IF NOT EXISTS security_violations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(64) NOT NULL,
    violation_type VARCHAR(50) NOT NULL,
    description TEXT,
    recorded_at DATETIME NOT NULL,
    
    INDEX idx_session (session_token),
    INDEX idx_type (violation_type),
    INDEX idx_recorded (recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add termination columns to active_sessions if they don't exist
ALTER TABLE active_sessions 
ADD COLUMN IF NOT EXISTS termination_reason VARCHAR(100) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS violation_count INT DEFAULT 0;

-- Add status column to exam_progress if it doesn't exist
ALTER TABLE exam_progress 
ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'active';

-- Add roll_no column to exam_progress if it doesn't exist
ALTER TABLE exam_progress 
ADD COLUMN IF NOT EXISTS roll_no VARCHAR(50) DEFAULT NULL;

SELECT 'Security violation tables created successfully!' as Result;
