<?php
require_once __DIR__ . '/config.php';

try {
    $pdo = db();

    // Create students table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL
        )
    ");

    // Create courses table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS courses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT
        )
    ");

    // Create enrollments table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS enrollments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            course_id INT NOT NULL,
            FOREIGN KEY (student_id) REFERENCES students(id),
            FOREIGN KEY (course_id) REFERENCES courses(id)
        )
    ");

    // Add a student
    $username = 'student';
    $password = password_hash('password123', PASSWORD_DEFAULT);
    
    $stmt = $pdo->prepare("SELECT id FROM students WHERE username = ?");
    $stmt->execute([$username]);
    $student = $stmt->fetch();

    if (!$student) {
        $stmt = $pdo->prepare("INSERT INTO students (username, password) VALUES (?, ?)");
        $stmt->execute([$username, $password]);
        $student_id = $pdo->lastInsertId();
    } else {
        $student_id = $student['id'];
    }


    // Add courses
    $courses = [
        ['Introduction to PHP', 'Learn the basics of PHP programming.'],
        ['Database Management with MySQL', 'Master the art of database management.'],
        ['Web Design Fundamentals', 'Understand the principles of modern web design.'],
    ];

    $stmt = $pdo->prepare("INSERT INTO courses (name, description) VALUES (?, ?)");
    foreach ($courses as $course) {
        // Check if course exists
        $checkStmt = $pdo->prepare("SELECT id FROM courses WHERE name = ?");
        $checkStmt->execute([$course[0]]);
        if (!$checkStmt->fetch()) {
            $stmt->execute($course);
            $course_id = $pdo->lastInsertId();

            // Enroll student in the course
            $enrollStmt = $pdo->prepare("INSERT INTO enrollments (student_id, course_id) VALUES (?, ?)");
            $enrollStmt->execute([$student_id, $course_id]);
        }
    }

    // Create admins table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS admins (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL
        )
    ");

    // Add an admin
    $admin_username = 'admin';
    $admin_password = password_hash('password123', PASSWORD_DEFAULT);

    $stmt = $pdo->prepare("SELECT id FROM admins WHERE username = ?");
    $stmt->execute([$admin_username]);
    if (!$stmt->fetch()) {
        $stmt = $pdo->prepare("INSERT INTO admins (username, password) VALUES (?, ?)");
        $stmt->execute([$admin_username, $admin_password]);
    }

    // Create grades table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS grades (
            id INT AUTO_INCREMENT PRIMARY KEY,
            enrollment_id INT NOT NULL,
            grade VARCHAR(255),
            FOREIGN KEY (enrollment_id) REFERENCES enrollments(id)
        )
    ");

    echo "Database setup completed successfully.\n";

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