<?php
/**
 * Database Migration: Fix Activity Start Bug
 * This migration adds missing tables and columns for timed activities
 * Run this file once to update your existing database
 */

require_once __DIR__ . '/../config/database.php';

$conn = getDBConnection();

echo "<h2>Fixing Activity Start Bug - Database Migration</h2>";
echo "<p>This will add missing tables and columns for timed activities.</p>";

$errors = [];
$success = [];

// 1. Create activity_attempts table
echo "<h3>1. Creating activity_attempts table...</h3>";
$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,
    FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attempt (student_id, activity_id)
)";

if ($conn->query($sql)) {
    echo "<p style='color: green;'>✓ activity_attempts table created successfully</p>";
    $success[] = "activity_attempts table created";
} else {
    echo "<p style='color: red;'>✗ Error creating activity_attempts table: " . $conn->error . "</p>";
    $errors[] = "activity_attempts table: " . $conn->error;
}

// 2. Add time_limit column to activities table
echo "<h3>2. Adding time_limit column to activities table...</h3>";
$check = $conn->query("SHOW COLUMNS FROM activities LIKE 'time_limit'");
if ($check->num_rows == 0) {
    $sql = "ALTER TABLE activities ADD COLUMN time_limit INT DEFAULT NULL COMMENT 'Time limit in minutes for timed activities'";
    if ($conn->query($sql)) {
        echo "<p style='color: green;'>✓ time_limit column added successfully</p>";
        $success[] = "time_limit column added to activities";
    } else {
        echo "<p style='color: red;'>✗ Error adding time_limit column: " . $conn->error . "</p>";
        $errors[] = "time_limit column: " . $conn->error;
    }
} else {
    echo "<p style='color: orange;'>⚠ time_limit column already exists</p>";
    $success[] = "time_limit column already exists";
}

// 3. Add violation_count column to submissions table
echo "<h3>3. Adding violation_count column to submissions table...</h3>";
$check = $conn->query("SHOW COLUMNS FROM submissions LIKE 'violation_count'");
if ($check->num_rows == 0) {
    $sql = "ALTER TABLE submissions ADD COLUMN violation_count INT DEFAULT 0 COMMENT 'Anti-cheat violation count'";
    if ($conn->query($sql)) {
        echo "<p style='color: green;'>✓ violation_count column added successfully</p>";
        $success[] = "violation_count column added to submissions";
    } else {
        echo "<p style='color: red;'>✗ Error adding violation_count column: " . $conn->error . "</p>";
        $errors[] = "violation_count column: " . $conn->error;
    }
} else {
    echo "<p style='color: orange;'>⚠ violation_count column already exists</p>";
    $success[] = "violation_count column already exists";
}

// 4. Add is_auto_submitted column to submissions table
echo "<h3>4. Adding is_auto_submitted column to submissions table...</h3>";
$check = $conn->query("SHOW COLUMNS FROM submissions LIKE 'is_auto_submitted'");
if ($check->num_rows == 0) {
    $sql = "ALTER TABLE submissions ADD COLUMN is_auto_submitted TINYINT(1) DEFAULT 0 COMMENT 'Auto-submitted due to violations or time limit'";
    if ($conn->query($sql)) {
        echo "<p style='color: green;'>✓ is_auto_submitted column added successfully</p>";
        $success[] = "is_auto_submitted column added to submissions";
    } else {
        echo "<p style='color: red;'>✗ Error adding is_auto_submitted column: " . $conn->error . "</p>";
        $errors[] = "is_auto_submitted column: " . $conn->error;
    }
} else {
    echo "<p style='color: orange;'>⚠ is_auto_submitted column already exists</p>";
    $success[] = "is_auto_submitted column already exists";
}

// Summary
echo "<hr>";
echo "<h3>Migration Summary</h3>";

if (count($success) > 0) {
    echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px; border-left: 4px solid #28a745;'>";
    echo "<h4 style='color: #155724; margin-top: 0;'>✓ Success (" . count($success) . " items)</h4>";
    echo "<ul style='color: #155724;'>";
    foreach ($success as $item) {
        echo "<li>$item</li>";
    }
    echo "</ul>";
    echo "</div>";
}

if (count($errors) > 0) {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px; border-left: 4px solid #dc3545; margin-top: 15px;'>";
    echo "<h4 style='color: #721c24; margin-top: 0;'>✗ Errors (" . count($errors) . " items)</h4>";
    echo "<ul style='color: #721c24;'>";
    foreach ($errors as $error) {
        echo "<li>$error</li>";
    }
    echo "</ul>";
    echo "</div>";
} else {
    echo "<div style='background: #d1ecf1; padding: 15px; border-radius: 5px; border-left: 4px solid #0c5460; margin-top: 15px;'>";
    echo "<p style='color: #0c5460; margin: 0;'><strong>✓ All migrations completed successfully!</strong></p>";
    echo "</div>";
}

echo "<hr>";
echo "<h3>Next Steps</h3>";
echo "<ol>";
echo "<li>Verify the changes in your database</li>";
echo "<li>Test creating a timed activity (with time_limit > 0)</li>";
echo "<li>Test starting a timed activity as a student</li>";
echo "<li>Test the timer and anti-cheat features</li>";
echo "</ol>";

echo "<p><a href='../index.php' style='display: inline-block; padding: 10px 20px; background: #667eea; color: white; text-decoration: none; border-radius: 5px; margin-top: 15px;'>← Back to Home</a></p>";

$conn->close();
?>
