setup_progress_table.php

<?php
require 'includes/config.php';

echo "<h2>Setting up user_course_progress table...</h2>";

try {
    $sql = "CREATE TABLE IF NOT EXISTS `user_course_progress` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(50) NOT NULL,
      `course_id` VARCHAR(50) NOT NULL,
      `tool_id` VARCHAR(50) NOT NULL,
      `activity_type` VARCHAR(50) NOT NULL,
      `is_completed` TINYINT(1) DEFAULT 0,
      `completed_at` DATETIME DEFAULT NULL,
      `last_accessed` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_progress` (`username`, `course_id`, `tool_id`, `activity_type`),
      KEY `idx_user_course` (`username`, `course_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    $con->exec($sql);
    
    // Attempt to fix column type if table already existed with INT
    try {
        $con->exec("ALTER TABLE `user_course_progress` MODIFY `tool_id` VARCHAR(50) NOT NULL");
        echo "<p style='color:blue'>ℹ️ Updated tool_id column to VARCHAR(50).</p>";
    } catch (Exception $e) {
        // Ignore if already correct or other minor issue
    }

    echo "<p style='color:green'>✅ Table user_course_progress checked/created/updated successfully.</p>";
    
    // Check columns
    $stmt = $con->query("DESCRIBE user_course_progress");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "<pre>";
    print_r($columns);
    echo "</pre>";
    
} catch(PDOException $e) {
    echo "<p style='color:red'>❌ Error: " . $e->getMessage() . "</p>";
}
?>