<?php
// Load environment variables from .env file
if (!function_exists('loadEnv')) {
function loadEnv($file = null) {
    if ($file === null) {
        $file = __DIR__ . '/../.env';
    }
    if (!file_exists($file)) {
        return;
    }

    $lines = file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        // Skip comments
        if (strpos(trim($line), '#') === 0) {
            continue;
        }

        // Parse KEY=VALUE
        if (strpos($line, '=') !== false) {
            list($key, $value) = explode('=', $line, 2);
            $key = trim($key);
            $value = trim($value);

            // Remove quotes if present
            if (preg_match('/^(["\'])(.*)\1$/', $value, $matches)) {
                $value = $matches[2];
            }

            // Set as environment variable
            putenv("$key=$value");
            $_ENV[$key] = $value;
            $_SERVER[$key] = $value;
        }
    }
}
}

// Load .env file
loadEnv(__DIR__ . '/../.env');

// Database configuration from environment variables
define('DB_HOST', getenv('DB_HOST') ?: 'localhost');
define('DB_USER', getenv('DB_USER') ?: 'root');
define('DB_PASS', getenv('DB_PASS') ?: '');
define('DB_NAME', getenv('DB_NAME') ?: 'cpp_learning_system');

// Create connection
function getDBConnection() {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    if ($conn->connect_error) {
        // Log the actual error securely
        error_log("Database connection failed: " . $conn->connect_error);
        // Show generic error to user
        die("Service temporarily unavailable. Please try again later.");
    }

    return $conn;
}

// Create database and tables if they don't exist
function initializeDatabase() {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS);

    if ($conn->connect_error) {
        error_log("Database initialization failed: " . $conn->connect_error);
        die("Service temporarily unavailable. Please try again later.");
    }
    
    // Create database
    $sql = "CREATE DATABASE IF NOT EXISTS " . DB_NAME;
    $conn->query($sql);
    
    $conn->select_db(DB_NAME);
    
    // Create users table
    $sql = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        full_name VARCHAR(100) NOT NULL,
        user_type ENUM('student', 'instructor') NOT NULL,
        code_points INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $conn->query($sql);

    // Add code_points column if it doesn't exist (check first to avoid error)
    $check = $conn->query("SHOW COLUMNS FROM users LIKE 'code_points'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE users ADD COLUMN code_points INT DEFAULT 0");
    }

    // Add id_number column if it doesn't exist
    $check = $conn->query("SHOW COLUMNS FROM users LIKE 'id_number'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE users ADD COLUMN id_number VARCHAR(50) DEFAULT NULL AFTER user_type");
    }

    // Add year_level column if it doesn't exist
    $check = $conn->query("SHOW COLUMNS FROM users LIKE 'year_level'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE users ADD COLUMN year_level VARCHAR(20) DEFAULT NULL AFTER id_number");
    }

    // Add section column if it doesn't exist
    $check = $conn->query("SHOW COLUMNS FROM users LIKE 'section'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE users ADD COLUMN section VARCHAR(50) DEFAULT NULL AFTER year_level");
    }

    // Add profile_picture column if it doesn't exist
    $check = $conn->query("SHOW COLUMNS FROM users LIKE 'profile_picture'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE users ADD COLUMN profile_picture VARCHAR(255) DEFAULT NULL");
    }

    // Create subjects table
    $sql = "CREATE TABLE IF NOT EXISTS subjects (
        id INT AUTO_INCREMENT PRIMARY KEY,
        subject_name VARCHAR(100) NOT NULL,
        subject_code VARCHAR(20) UNIQUE NOT NULL,
        description TEXT,
        instructor_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (instructor_id) REFERENCES users(id)
    )";
    $conn->query($sql);
    
    // Create enrollments table
    $sql = "CREATE TABLE IF NOT EXISTS enrollments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT,
        subject_id INT,
        enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE ON UPDATE CASCADE,
        UNIQUE KEY unique_enrollment (student_id, subject_id)
    )";
    $conn->query($sql);
    
    // Create activities table
    $sql = "CREATE TABLE IF NOT EXISTS activities (
        id INT AUTO_INCREMENT PRIMARY KEY,
        subject_id INT,
        title VARCHAR(200) NOT NULL,
        description TEXT,
        starter_code TEXT,
        expected_output TEXT,
        due_date DATETIME,
        time_limit INT DEFAULT NULL COMMENT 'Time limit in minutes for timed activities',
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
    )";
    $conn->query($sql);

    // Add time_limit column if it doesn't exist (for existing databases)
    $check = $conn->query("SHOW COLUMNS FROM activities LIKE 'time_limit'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE activities ADD COLUMN time_limit INT DEFAULT NULL");
    }
    
    // Create submissions table
    $sql = "CREATE TABLE IF NOT EXISTS submissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        activity_id INT,
        student_id INT,
        code TEXT,
        output TEXT,
        score DECIMAL(5,2) DEFAULT NULL,
        feedback TEXT,
        status ENUM('pending', 'graded') DEFAULT 'pending',
        submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        graded_at DATETIME,
        violation_count INT DEFAULT 0 COMMENT 'Anti-cheat violation count',
        is_auto_submitted TINYINT(1) DEFAULT 0 COMMENT 'Auto-submitted due to violations or time limit',
        FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
    )";
    $conn->query($sql);

    // Add anti-cheat columns if they don't exist (for existing databases)
    $check = $conn->query("SHOW COLUMNS FROM submissions LIKE 'violation_count'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE submissions ADD COLUMN violation_count INT DEFAULT 0");
    }
    $check = $conn->query("SHOW COLUMNS FROM submissions LIKE 'is_auto_submitted'");
    if ($check->num_rows == 0) {
        $conn->query("ALTER TABLE submissions ADD COLUMN is_auto_submitted TINYINT(1) DEFAULT 0");
    }
    
    // Create activity_attempts table for tracking timed activities
    $sql = "CREATE TABLE IF NOT EXISTS activity_attempts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        activity_id INT NOT NULL,
        start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE ON UPDATE CASCADE,
        UNIQUE KEY unique_attempt (student_id, activity_id)
    )";
    $conn->query($sql);

    // Create notifications table
    $sql = "CREATE TABLE IF NOT EXISTS notifications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        message TEXT NOT NULL,
        activity_id INT,
        is_read BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE ON UPDATE CASCADE
    )";
    $conn->query($sql);

    // Create code_challenges table
    $sql = "CREATE TABLE IF NOT EXISTS code_challenges (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(200) NOT NULL,
        description TEXT,
        difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'easy',
        code_template TEXT NOT NULL,
        correct_answer TEXT NOT NULL,
        hint TEXT,
        points INT DEFAULT 1,
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $conn->query($sql);

    // Create challenge_completions table
    $sql = "CREATE TABLE IF NOT EXISTS challenge_completions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT,
        challenge_id INT,
        completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        completion_date DATE,
        is_daily BOOLEAN DEFAULT FALSE,
        FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (challenge_id) REFERENCES code_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE,
        UNIQUE KEY unique_daily_completion (student_id, challenge_id, completion_date)
    )";
    $conn->query($sql);

    // Create daily_challenges table to track the challenge of the day
    $sql = "CREATE TABLE IF NOT EXISTS daily_challenges (
        id INT AUTO_INCREMENT PRIMARY KEY,
        challenge_id INT,
        challenge_date DATE UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (challenge_id) REFERENCES code_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE
    )";
    $conn->query($sql);

    // Insert sample challenges if table is empty
    $result = $conn->query("SELECT COUNT(*) as count FROM code_challenges");
    $row = $result->fetch_assoc();

    if ($row['count'] == 0) {
        $challenges = [
            [
                'title' => 'Hello World',
                'description' => 'Complete the classic Hello World program',
                'difficulty' => 'easy',
                'template' => '#include <stdio.h>\n\nint main() {\n    printf("___");\n    return 0;\n}',
                'answer' => 'Hello, World!',
                'hint' => 'Fill in the blank with the text to print',
                'points' => 1
            ],
            [
                'title' => 'Variable Declaration',
                'description' => 'Declare an integer variable',
                'difficulty' => 'easy',
                'template' => '#include <stdio.h>\n\nint main() {\n    ___ age = 25;\n    printf("%d", age);\n    return 0;\n}',
                'answer' => 'int',
                'hint' => 'What data type is used for whole numbers?',
                'points' => 1
            ],
            [
                'title' => 'For Loop',
                'description' => 'Complete the for loop',
                'difficulty' => 'medium',
                'template' => '#include <stdio.h>\n\nint main() {\n    for(int i = 0; i ___ 5; i++) {\n        printf("%d ", i);\n    }\n    return 0;\n}',
                'answer' => '<',
                'hint' => 'What operator is used to check if i is less than 5?',
                'points' => 2
            ],
            [
                'title' => 'Array Declaration',
                'description' => 'Declare an array of 10 integers',
                'difficulty' => 'easy',
                'template' => '#include <stdio.h>\n\nint main() {\n    int numbers___;\n    return 0;\n}',
                'answer' => '[10]',
                'hint' => 'Use square brackets with the size',
                'points' => 1
            ],
            [
                'title' => 'If Statement',
                'description' => 'Complete the if condition',
                'difficulty' => 'easy',
                'template' => '#include <stdio.h>\n\nint main() {\n    int x = 10;\n    ___ (x > 5) {\n        printf("Greater");\n    }\n    return 0;\n}',
                'answer' => 'if',
                'hint' => 'What keyword starts a conditional statement?',
                'points' => 1
            ],
            [
                'title' => 'Pointer Declaration',
                'description' => 'Declare a pointer to an integer',
                'difficulty' => 'medium',
                'template' => '#include <stdio.h>\n\nint main() {\n    int x = 10;\n    int ___ptr = &x;\n    return 0;\n}',
                'answer' => '*',
                'hint' => 'What symbol is used to declare a pointer?',
                'points' => 2
            ],
            [
                'title' => 'Include Header',
                'description' => 'Include the standard input/output library',
                'difficulty' => 'easy',
                'template' => '___\n\nint main() {\n    printf("Hello");\n    return 0;\n}',
                'answer' => '#include <stdio.h>',
                'hint' => 'Use #include with the stdio.h library',
                'points' => 1
            ],
            [
                'title' => 'While Loop',
                'description' => 'Complete the while loop',
                'difficulty' => 'medium',
                'template' => '#include <stdio.h>\n\nint main() {\n    int i = 0;\n    ___ (i < 3) {\n        printf("%d ", i);\n        i++;\n    }\n    return 0;\n}',
                'answer' => 'while',
                'hint' => 'What keyword is used for a while loop?',
                'points' => 2
            ]
        ];

        $stmt = $conn->prepare("INSERT INTO code_challenges (title, description, difficulty, code_template, correct_answer, hint, points) VALUES (?, ?, ?, ?, ?, ?, ?)");
        foreach ($challenges as $challenge) {
            $stmt->bind_param("ssssssi",
                $challenge['title'],
                $challenge['description'],
                $challenge['difficulty'],
                $challenge['template'],
                $challenge['answer'],
                $challenge['hint'],
                $challenge['points']
            );
            $stmt->execute();
        }
        $stmt->close();
    }

    $conn->close();
}

// Initialize database on first run
initializeDatabase();
?>
