-- Pre/Post Tests and Evaluation Schema
-- Add to existing database

-- Pre-tests table
CREATE TABLE IF NOT EXISTS pre_tests (
    test_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    instructions TEXT,
    passing_score INT DEFAULT 60,
    time_limit INT DEFAULT 30 COMMENT 'Time limit in minutes',
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Post-tests table (same structure as pre-tests)
CREATE TABLE IF NOT EXISTS post_tests (
    test_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    instructions TEXT,
    passing_score INT DEFAULT 60,
    time_limit INT DEFAULT 30 COMMENT 'Time limit in minutes',
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Test questions table (for both pre and post tests)
CREATE TABLE IF NOT EXISTS test_questions (
    question_id INT PRIMARY KEY AUTO_INCREMENT,
    test_id INT NOT NULL,
    test_type ENUM('pre_test', 'post_test') NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('multiple_choice', 'true_false', 'essay') NOT NULL DEFAULT 'multiple_choice',
    option_a VARCHAR(500),
    option_b VARCHAR(500),
    option_c VARCHAR(500),
    option_d VARCHAR(500),
    correct_answer VARCHAR(10) COMMENT 'A, B, C, D, or TRUE/FALSE',
    points INT DEFAULT 1,
    order_num INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_test (test_id, test_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pre-test responses
CREATE TABLE IF NOT EXISTS pre_test_responses (
    response_id INT PRIMARY KEY AUTO_INCREMENT,
    test_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    registration_id INT,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP NULL,
    score DECIMAL(5,2) DEFAULT 0,
    total_points INT DEFAULT 0,
    percentage DECIMAL(5,2) DEFAULT 0,
    passed TINYINT(1) DEFAULT 0,
    time_taken INT COMMENT 'Time taken in seconds',
    FOREIGN KEY (test_id) REFERENCES pre_tests(test_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES activity_registrations(registration_id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_test (test_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Post-test responses
CREATE TABLE IF NOT EXISTS post_test_responses (
    response_id INT PRIMARY KEY AUTO_INCREMENT,
    test_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    registration_id INT,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP NULL,
    score DECIMAL(5,2) DEFAULT 0,
    total_points INT DEFAULT 0,
    percentage DECIMAL(5,2) DEFAULT 0,
    passed TINYINT(1) DEFAULT 0,
    time_taken INT COMMENT 'Time taken in seconds',
    FOREIGN KEY (test_id) REFERENCES post_tests(test_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES activity_registrations(registration_id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_test (test_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Test answers (individual question responses)
CREATE TABLE IF NOT EXISTS test_answers (
    answer_id INT PRIMARY KEY AUTO_INCREMENT,
    response_id INT NOT NULL,
    test_type ENUM('pre_test', 'post_test') NOT NULL,
    question_id INT NOT NULL,
    answer_text TEXT,
    is_correct TINYINT(1) DEFAULT 0,
    points_earned DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_response (response_id, test_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Evaluation forms
CREATE TABLE IF NOT EXISTS evaluation_forms (
    form_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    instructions TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Evaluation questions
CREATE TABLE IF NOT EXISTS evaluation_questions (
    question_id INT PRIMARY KEY AUTO_INCREMENT,
    form_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('rating', 'multiple_choice', 'text', 'yes_no') NOT NULL DEFAULT 'rating',
    options TEXT COMMENT 'JSON array of options for multiple choice',
    is_required TINYINT(1) DEFAULT 1,
    order_num INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES evaluation_forms(form_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Evaluation responses
CREATE TABLE IF NOT EXISTS evaluation_responses (
    response_id INT PRIMARY KEY AUTO_INCREMENT,
    form_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    registration_id INT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES evaluation_forms(form_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES activity_registrations(registration_id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_form (form_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Evaluation answers
CREATE TABLE IF NOT EXISTS evaluation_answers (
    answer_id INT PRIMARY KEY AUTO_INCREMENT,
    response_id INT NOT NULL,
    question_id INT NOT NULL,
    answer_value TEXT,
    rating_value INT COMMENT 'For rating questions (1-5)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (response_id) REFERENCES evaluation_responses(response_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES evaluation_questions(question_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add indexes for better performance
CREATE INDEX idx_pre_test_activity ON pre_tests(activity_id);
CREATE INDEX idx_post_test_activity ON post_tests(activity_id);
CREATE INDEX idx_pre_response_user ON pre_test_responses(user_id, activity_id);
CREATE INDEX idx_post_response_user ON post_test_responses(user_id, activity_id);
CREATE INDEX idx_eval_form_activity ON evaluation_forms(activity_id);
CREATE INDEX idx_eval_response_user ON evaluation_responses(user_id, activity_id);

