<?php
require_once('config.php');

echo "Starting database migration...<br>";

try {
    // 1. Create modules table
    $sql = "CREATE TABLE IF NOT EXISTS modules (
        id INT AUTO_INCREMENT PRIMARY KEY,
        course_id INT NOT NULL,
        module_name VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
    $pdo->exec($sql);
    echo "✓ modules table created<br>";

    // 2. Create units table
    $sql = "CREATE TABLE IF NOT EXISTS units (
        id INT AUTO_INCREMENT PRIMARY KEY,
        module_id INT NOT NULL,
        unit_name VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
    $pdo->exec($sql);
    echo "✓ units table created<br>";

    // 3. Check if notes table has module_id and unit_id columns
    $stmt = $pdo->query("DESCRIBE notes");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('module_id', $columns)) {
        $sql = "ALTER TABLE notes ADD COLUMN module_id INT AFTER course_id";
        $pdo->exec($sql);
        echo "✓ module_id column added to notes table<br>";
    }
    
    if (!in_array('unit_id', $columns)) {
        $sql = "ALTER TABLE notes ADD COLUMN unit_id INT AFTER module_id";
        $pdo->exec($sql);
        echo "✓ unit_id column added to notes table<br>";
    }

    // 4. Add foreign keys to notes table
    $sql = "ALTER TABLE notes 
        ADD CONSTRAINT fk_notes_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE SET NULL,
        ADD CONSTRAINT fk_notes_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE SET NULL";
    try {
        $pdo->exec($sql);
        echo "✓ Foreign keys added to notes table<br>";
    } catch (PDOException $e) {
        echo "Note: Foreign keys may already exist - " . $e->getMessage() . "<br>";
    }

    echo "<br><strong>Database migration completed successfully!</strong>";
    echo "<br><br>You can now delete this file.";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>