-- DINO Exam Browser - Database Schema
-- Modern naming convention with instant cheating detection
-- Database: ahmeuesz_dino

-- Students and instructors table
CREATE TABLE IF NOT EXISTS learners (
    learner_id INT AUTO_INCREMENT PRIMARY KEY,
    student_code VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    email_address VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    account_type ENUM('student', 'instructor', 'admin') DEFAULT 'student',
    is_active BOOLEAN DEFAULT TRUE,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_student_code (student_code),
    INDEX idx_email (email_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessment/Exam definitions
CREATE TABLE IF NOT EXISTS assessments (
    assessment_id INT AUTO_INCREMENT PRIMARY KEY,
    exam_code VARCHAR(50) UNIQUE NOT NULL,
    assessment_title VARCHAR(255) NOT NULL,
    created_by INT NOT NULL,
    total_duration_mins INT NOT NULL,
    question_count INT NOT NULL,
    scheduled_start DATETIME NOT NULL,
    scheduled_end DATETIME NOT NULL,
    passing_threshold DECIMAL(5,2) DEFAULT 50.00,
    assessment_status ENUM('scheduled', 'active', 'completed', 'cancelled') DEFAULT 'scheduled',
    allow_review BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES learners(learner_id) ON DELETE CASCADE,
    INDEX idx_exam_code (exam_code),
    INDEX idx_status (assessment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Question bank
CREATE TABLE IF NOT EXISTS question_bank (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    question_order INT NOT NULL,
    question_prompt TEXT NOT NULL,
    choice_a TEXT,
    choice_b TEXT,
    choice_c TEXT,
    choice_d TEXT,
    correct_choice CHAR(1) NOT NULL,
    point_value DECIMAL(5,2) DEFAULT 1.00,
    FOREIGN KEY (assessment_id) REFERENCES assessments(assessment_id) ON DELETE CASCADE,
    INDEX idx_assessment_order (assessment_id, question_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Active exam sessions with real-time tracking
CREATE TABLE IF NOT EXISTS active_sessions (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    learner_id INT NOT NULL,
    assessment_id INT NOT NULL,
    current_question_index INT DEFAULT 1,
    questions_answered INT DEFAULT 0,
    time_left_seconds INT NOT NULL,
    window_focus_status ENUM('FOCUSED', 'BLURRED', 'HIDDEN') DEFAULT 'FOCUSED',
    network_connectivity ENUM('ONLINE', 'OFFLINE', 'UNSTABLE') DEFAULT 'ONLINE',
    session_started_at DATETIME NOT NULL,
    last_activity_ping TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    submitted_at DATETIME NULL,
    session_status ENUM('active', 'submitted', 'terminated', 'abandoned') DEFAULT 'active',
    termination_reason VARCHAR(255) NULL,
    browser_fingerprint VARCHAR(255),
    ip_address VARCHAR(45),
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id) ON DELETE CASCADE,
    FOREIGN KEY (assessment_id) REFERENCES assessments(assessment_id) ON DELETE CASCADE,
    INDEX idx_session_status (session_status),
    INDEX idx_learner_assessment (learner_id, assessment_id),
    INDEX idx_last_activity (last_activity_ping)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Student responses
CREATE TABLE IF NOT EXISTS learner_responses (
    response_id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(255) NOT NULL,
    question_id INT NOT NULL,
    selected_choice CHAR(1),
    response_time_seconds INT,
    answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_token) REFERENCES active_sessions(session_token) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES question_bank(question_id) ON DELETE CASCADE,
    UNIQUE KEY unique_response (session_token, question_id),
    INDEX idx_session (session_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cheating/violation logs
CREATE TABLE IF NOT EXISTS security_violations (
    violation_id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(255) NOT NULL,
    violation_type ENUM('tab_switch', 'window_blur', 'copy_attempt', 
                        'screenshot_attempt', 'network_disconnect', 
                        'alt_tab', 'unauthorized_app', 'other') NOT NULL,
    violation_severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    violation_details JSON,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action_taken ENUM('warning', 'flagged', 'terminated') NOT NULL,
    FOREIGN KEY (session_token) REFERENCES active_sessions(session_token) ON DELETE CASCADE,
    INDEX idx_session_type (session_token, violation_type),
    INDEX idx_severity (violation_severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Final assessment results
CREATE TABLE IF NOT EXISTS assessment_results (
    result_id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    learner_id INT NOT NULL,
    assessment_id INT NOT NULL,
    total_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    maximum_score DECIMAL(5,2) NOT NULL,
    score_percentage DECIMAL(5,2) GENERATED ALWAYS AS ((total_score / maximum_score) * 100) STORED,
    pass_status BOOLEAN,
    was_terminated BOOLEAN DEFAULT FALSE,
    graded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    graded_by INT NULL,
    FOREIGN KEY (session_token) REFERENCES active_sessions(session_token) ON DELETE CASCADE,
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id) ON DELETE CASCADE,
    FOREIGN KEY (assessment_id) REFERENCES assessments(assessment_id) ON DELETE CASCADE,
    FOREIGN KEY (graded_by) REFERENCES learners(learner_id) ON DELETE SET NULL,
    INDEX idx_learner (learner_id),
    INDEX idx_assessment (assessment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default admin user (password: admin123)
INSERT INTO learners (student_code, full_name, email_address, password_hash, account_type) VALUES
('ADMIN_001', 'System Administrator', 'admin@bilalcode.site', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');
