<?php
require_once 'includes/init.php';

try {
    $sql = "
    CREATE TABLE IF NOT EXISTS classes (
        class_id INT AUTO_INCREMENT PRIMARY KEY,
        class_name VARCHAR(100) NOT NULL UNIQUE
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS users (
        user_id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('admin', 'student', 'teacher') NOT NULL DEFAULT 'student',
        full_name VARCHAR(100) DEFAULT NULL,
        photo VARCHAR(255) DEFAULT NULL,
        access_pin VARCHAR(20) DEFAULT NULL,
        class_id INT DEFAULT NULL,
        permissions TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT fk_user_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL
    ) ENGINE=InnoDB;

    CREATE TABLE IF NOT EXISTS student_activity_log (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        activity VARCHAR(255) NOT NULL,
        details TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT fk_log_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS subjects (
        subject_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        subject_code VARCHAR(20) NOT NULL UNIQUE
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS exams (
        exam_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100) NOT NULL,
        duration INT NOT NULL,
        status ENUM('active', 'inactive') NOT NULL DEFAULT 'inactive',
        class_id INT DEFAULT NULL,
        subject_id INT DEFAULT NULL,
        randomize_questions TINYINT(1) DEFAULT 1,
        session VARCHAR(20) DEFAULT NULL,
        term VARCHAR(20) DEFAULT NULL,
        question_quota INT DEFAULT NULL,
        show_result_to_student TINYINT(1) DEFAULT 0,
        allowed_attempts INT DEFAULT 1,
        exam_type ENUM('test','exam') NOT NULL DEFAULT 'test',
        total_score INT NOT NULL DEFAULT 20,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT fk_exam_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL,
        CONSTRAINT fk_exam_subject FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS passages (
        passage_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(150) DEFAULT NULL,
        content TEXT NOT NULL,
        subject_id INT DEFAULT NULL,
        class_id INT DEFAULT NULL,
        session VARCHAR(20) DEFAULT NULL,
        term VARCHAR(20) DEFAULT NULL,
        CONSTRAINT fk_passage_subject FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL,
        CONSTRAINT fk_passage_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS media_library (
        media_id INT AUTO_INCREMENT PRIMARY KEY,
        file_path VARCHAR(255) NOT NULL,
        caption VARCHAR(255) DEFAULT NULL,
        uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS questions (
        question_id INT AUTO_INCREMENT PRIMARY KEY,
        subject_id INT DEFAULT NULL,
        exam_id INT DEFAULT NULL,
        class_id INT DEFAULT NULL,
        question_text TEXT NOT NULL,
        image_path VARCHAR(255) DEFAULT NULL,
        passage_id INT DEFAULT NULL,
        media_id INT DEFAULT NULL,
        option_a VARCHAR(255) NOT NULL,
        option_b VARCHAR(255) NOT NULL,
        option_c VARCHAR(255) NOT NULL,
        option_d VARCHAR(255) NOT NULL,
        option_e VARCHAR(255) DEFAULT NULL,
        correct_answer ENUM('A', 'B', 'C', 'D', 'E') NOT NULL,
        session VARCHAR(20) DEFAULT NULL,
        term VARCHAR(20) DEFAULT NULL,
        CONSTRAINT fk_q_subject FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL,
        CONSTRAINT fk_q_exam FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE,
        CONSTRAINT fk_q_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL,
        CONSTRAINT fk_q_passage FOREIGN KEY (passage_id) REFERENCES passages(passage_id) ON DELETE SET NULL,
        CONSTRAINT fk_q_media FOREIGN KEY (media_id) REFERENCES media_library(media_id) ON DELETE SET NULL
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS exam_assignments (
        assignment_id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL,
        user_id INT NOT NULL,
        assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT fk_ea_exam FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE,
        CONSTRAINT fk_ea_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
        UNIQUE KEY unique_assignment (exam_id, user_id)
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS results (
        result_id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        exam_id INT NOT NULL,
        score DECIMAL(5, 2) NOT NULL,
        total_questions INT NOT NULL,
        date_taken TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status ENUM('completed', 'pending') DEFAULT 'completed',
        CONSTRAINT fk_res_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
        CONSTRAINT fk_res_exam FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE
    ) ENGINE=InnoDB;
 
    CREATE TABLE IF NOT EXISTS settings (
        setting_id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(50) NOT NULL UNIQUE,
        setting_value TEXT
    ) ENGINE=InnoDB;
    ";
 
    $pdo->exec($sql);
    echo "Tables created successfully.<br>";

    // Default Settings
    $pdo->exec("INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('school_name', 'My School CBT'), ('school_logo', '')");
 
    // Create Default Admin if not exists
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
    $stmt->execute(['admin']);
    if ($stmt->fetchColumn() == 0) {
        $password = password_hash('admin123', PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("INSERT INTO users (username, password, role) VALUES (?, ?, ?)");
        $stmt->execute(['admin', $password, 'admin']);
        echo "Default Admin created (User: admin, Pass: admin123).<br>";
    } else {
        echo "Admin already exists.<br>";
    }

} catch (PDOException $e) {
    die("Setup failed: " . $e->getMessage());
}
