<?php
/**
 * DATABASE UPDATE SCRIPT
 * Adds all missing columns for enhanced task management features
 * Safe to run multiple times - checks if columns exist before adding
 */

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

$conn = getConnection();

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "<h2>🔧 Database Update Script</h2>";
echo "<p>Adding missing columns for enhanced task features...</p>";
echo "<hr>";

$updates = [];
$errors = [];

// ============================================================================
// FUNCTION: Check if column exists
// ============================================================================
function columnExists($conn, $table, $column) {
    $result = mysqli_query($conn, "SHOW COLUMNS FROM `$table` LIKE '$column'");
    return mysqli_num_rows($result) > 0;
}

// ============================================================================
// UPDATE 1: Add enable_timer to tasks table
// ============================================================================
try {
    if (!columnExists($conn, 'tasks', 'enable_timer')) {
        $sql = "ALTER TABLE `tasks` ADD COLUMN `enable_timer` TINYINT(1) DEFAULT 0 AFTER `min_duration_seconds`";
        if (mysqli_query($conn, $sql)) {
            $updates[] = "✅ Added 'enable_timer' column to tasks table";
        } else {
            $errors[] = "❌ Error adding enable_timer: " . mysqli_error($conn);
        }
    } else {
        $updates[] = "⏭️ Column 'enable_timer' already exists in tasks table";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding enable_timer: " . $e->getMessage();
}

// ============================================================================
// UPDATE 2: Add pass_percentage to tasks table
// ============================================================================
try {
    if (!columnExists($conn, 'tasks', 'pass_percentage')) {
        $sql = "ALTER TABLE `tasks` ADD COLUMN `pass_percentage` INT DEFAULT 70 AFTER `category`";
        if (mysqli_query($conn, $sql)) {
            $updates[] = "✅ Added 'pass_percentage' column to tasks table";
        } else {
            $errors[] = "❌ Error adding pass_percentage: " . mysqli_error($conn);
        }
    } else {
        $updates[] = "⏭️ Column 'pass_percentage' already exists in tasks table";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding pass_percentage: " . $e->getMessage();
}

// ============================================================================
// UPDATE 3: Add total_limit to tasks table
// ============================================================================
try {
    if (!columnExists($conn, 'tasks', 'total_limit')) {
        $sql = "ALTER TABLE `tasks` ADD COLUMN `total_limit` INT NULL DEFAULT NULL AFTER `daily_limit_per_user`";
        if (mysqli_query($conn, $sql)) {
            $updates[] = "✅ Added 'total_limit' column to tasks table";
        } else {
            $errors[] = "❌ Error adding total_limit: " . mysqli_error($conn);
        }
    } else {
        $updates[] = "⏭️ Column 'total_limit' already exists in tasks table";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding total_limit: " . $e->getMessage();
}

// ============================================================================
// UPDATE 4: Add enable_word_count to task_blog_requirements table
// ============================================================================
try {
    // First check if table exists
    $tableExists = mysqli_query($conn, "SHOW TABLES LIKE 'task_blog_requirements'");
    
    if (mysqli_num_rows($tableExists) > 0) {
        if (!columnExists($conn, 'task_blog_requirements', 'enable_word_count')) {
            $sql = "ALTER TABLE `task_blog_requirements` ADD COLUMN `enable_word_count` TINYINT(1) DEFAULT 1 AFTER `guidelines`";
            if (mysqli_query($conn, $sql)) {
                $updates[] = "✅ Added 'enable_word_count' column to task_blog_requirements table";
            } else {
                $errors[] = "❌ Error adding enable_word_count: " . mysqli_error($conn);
            }
        } else {
            $updates[] = "⏭️ Column 'enable_word_count' already exists in task_blog_requirements table";
        }
    } else {
        $updates[] = "⚠️ Table 'task_blog_requirements' does not exist yet";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding enable_word_count: " . $e->getMessage();
}

// ============================================================================
// UPDATE 5: Add points to task_quizzes table
// ============================================================================
try {
    $tableExists = mysqli_query($conn, "SHOW TABLES LIKE 'task_quizzes'");
    
    if (mysqli_num_rows($tableExists) > 0) {
        if (!columnExists($conn, 'task_quizzes', 'points')) {
            $sql = "ALTER TABLE `task_quizzes` ADD COLUMN `points` INT DEFAULT 1 AFTER `order_number`";
            if (mysqli_query($conn, $sql)) {
                $updates[] = "✅ Added 'points' column to task_quizzes table";
            } else {
                $errors[] = "❌ Error adding points: " . mysqli_error($conn);
            }
        } else {
            $updates[] = "⏭️ Column 'points' already exists in task_quizzes table";
        }
    } else {
        $updates[] = "⚠️ Table 'task_quizzes' does not exist yet";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding points: " . $e->getMessage();
}

// ============================================================================
// UPDATE 6: Add reviewed_at to task_completions table
// ============================================================================
try {
    $tableExists = mysqli_query($conn, "SHOW TABLES LIKE 'task_completions'");
    
    if (mysqli_num_rows($tableExists) > 0) {
        if (!columnExists($conn, 'task_completions', 'reviewed_at')) {
            $sql = "ALTER TABLE `task_completions` ADD COLUMN `reviewed_at` TIMESTAMP NULL DEFAULT NULL AFTER `admin_notes`";
            if (mysqli_query($conn, $sql)) {
                $updates[] = "✅ Added 'reviewed_at' column to task_completions table";
            } else {
                $errors[] = "❌ Error adding reviewed_at: " . mysqli_error($conn);
            }
        } else {
            $updates[] = "⏭️ Column 'reviewed_at' already exists in task_completions table";
        }
    } else {
        $updates[] = "⚠️ Table 'task_completions' does not exist yet";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding reviewed_at: " . $e->getMessage();
}

// ============================================================================
// UPDATE 7: Ensure min_duration_seconds exists and is correct type
// ============================================================================
try {
    if (!columnExists($conn, 'tasks', 'min_duration_seconds')) {
        $sql = "ALTER TABLE `tasks` ADD COLUMN `min_duration_seconds` INT DEFAULT 0 AFTER `daily_limit_per_user`";
        if (mysqli_query($conn, $sql)) {
            $updates[] = "✅ Added 'min_duration_seconds' column to tasks table";
        } else {
            $errors[] = "❌ Error adding min_duration_seconds: " . mysqli_error($conn);
        }
    } else {
        $updates[] = "⏭️ Column 'min_duration_seconds' already exists in tasks table";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding min_duration_seconds: " . $e->getMessage();
}

// ============================================================================
// UPDATE 8: Ensure category column exists
// ============================================================================
try {
    if (!columnExists($conn, 'tasks', 'category')) {
        $sql = "ALTER TABLE `tasks` ADD COLUMN `category` VARCHAR(100) DEFAULT 'general' AFTER `difficulty`";
        if (mysqli_query($conn, $sql)) {
            $updates[] = "✅ Added 'category' column to tasks table";
        } else {
            $errors[] = "❌ Error adding category: " . mysqli_error($conn);
        }
    } else {
        $updates[] = "⏭️ Column 'category' already exists in tasks table";
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception adding category: " . $e->getMessage();
}

// ============================================================================
// UPDATE 9: Set default values for existing tasks
// ============================================================================
try {
    // Set default enable_timer = 0 for existing tasks
    if (columnExists($conn, 'tasks', 'enable_timer')) {
        $sql = "UPDATE `tasks` SET `enable_timer` = 0 WHERE `enable_timer` IS NULL";
        if (mysqli_query($conn, $sql)) {
            $affected = mysqli_affected_rows($conn);
            if ($affected > 0) {
                $updates[] = "✅ Updated $affected tasks with default enable_timer value";
            }
        }
    }
    
    // Set default pass_percentage = 70 for existing tasks
    if (columnExists($conn, 'tasks', 'pass_percentage')) {
        $sql = "UPDATE `tasks` SET `pass_percentage` = 70 WHERE `pass_percentage` IS NULL OR `pass_percentage` = 0";
        if (mysqli_query($conn, $sql)) {
            $affected = mysqli_affected_rows($conn);
            if ($affected > 0) {
                $updates[] = "✅ Updated $affected tasks with default pass_percentage value (70%)";
            }
        }
    }
    
    // Set default points = 1 for existing quiz questions
    $tableExists = mysqli_query($conn, "SHOW TABLES LIKE 'task_quizzes'");
    if (mysqli_num_rows($tableExists) > 0 && columnExists($conn, 'task_quizzes', 'points')) {
        $sql = "UPDATE `task_quizzes` SET `points` = 1 WHERE `points` IS NULL OR `points` = 0";
        if (mysqli_query($conn, $sql)) {
            $affected = mysqli_affected_rows($conn);
            if ($affected > 0) {
                $updates[] = "✅ Updated $affected quiz questions with default points value (1)";
            }
        }
    }
} catch (Exception $e) {
    $errors[] = "❌ Exception updating default values: " . $e->getMessage();
}

// ============================================================================
// DISPLAY RESULTS
// ============================================================================
echo "<div style='background: #d1fae5; padding: 20px; border-radius: 10px; margin: 20px 0;'>";
echo "<h3 style='color: #065f46; margin-top: 0;'>✅ Successful Updates</h3>";
if (empty($updates)) {
    echo "<p style='color: #065f46;'>No updates needed - all columns already exist!</p>";
} else {
    echo "<ul style='color: #065f46;'>";
    foreach ($updates as $update) {
        echo "<li>$update</li>";
    }
    echo "</ul>";
}
echo "</div>";

if (!empty($errors)) {
    echo "<div style='background: #fee2e2; padding: 20px; border-radius: 10px; margin: 20px 0;'>";
    echo "<h3 style='color: #991b1b; margin-top: 0;'>❌ Errors</h3>";
    echo "<ul style='color: #991b1b;'>";
    foreach ($errors as $error) {
        echo "<li>$error</li>";
    }
    echo "</ul>";
    echo "</div>";
}

// ============================================================================
// SUMMARY
// ============================================================================
$successCount = count($updates);
$errorCount = count($errors);

echo "<div style='background: #e0e7ff; padding: 20px; border-radius: 10px; margin: 20px 0;'>";
echo "<h3 style='color: #3730a3; margin-top: 0;'>📊 Summary</h3>";
echo "<p style='color: #3730a3;'><strong>Total Updates:</strong> $successCount</p>";
echo "<p style='color: #3730a3;'><strong>Errors:</strong> $errorCount</p>";

if ($errorCount === 0) {
    echo "<p style='color: #15803d; font-weight: bold; font-size: 18px;'>🎉 Database update completed successfully!</p>";
    echo "<p style='color: #15803d;'>You can now use all enhanced task management features.</p>";
    echo "<p style='color: #3730a3;'><a href='manage-tasks.php' style='background: linear-gradient(135deg, #667eea 0%, #ec4899 100%); color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; display: inline-block; margin-top: 10px;'>Go to Task Management</a></p>";
} else {
    echo "<p style='color: #991b1b; font-weight: bold;'>⚠️ Some errors occurred. Please check the errors above and fix them manually.</p>";
}
echo "</div>";

// ============================================================================
// CURRENT STRUCTURE INFO
// ============================================================================
echo "<div style='background: #f3f4f6; padding: 20px; border-radius: 10px; margin: 20px 0;'>";
echo "<h3 style='color: #374151; margin-top: 0;'>📋 Added Columns</h3>";
echo "<table style='width: 100%; border-collapse: collapse;'>";
echo "<tr style='background: #e5e7eb;'>";
echo "<th style='padding: 10px; text-align: left; border: 1px solid #d1d5db;'>Table</th>";
echo "<th style='padding: 10px; text-align: left; border: 1px solid #d1d5db;'>Column</th>";
echo "<th style='padding: 10px; text-align: left; border: 1px solid #d1d5db;'>Type</th>";
echo "<th style='padding: 10px; text-align: left; border: 1px solid #d1d5db;'>Purpose</th>";
echo "</tr>";

$columns = [
    ['tasks', 'enable_timer', 'TINYINT(1)', 'Enable/disable timer feature'],
    ['tasks', 'min_duration_seconds', 'INT', 'Minimum time required to complete task'],
    ['tasks', 'pass_percentage', 'INT', 'Minimum pass percentage for quizzes (default 70)'],
    ['tasks', 'total_limit', 'INT', 'Maximum total completions allowed'],
    ['tasks', 'category', 'VARCHAR(100)', 'Task category (e.g., general, education)'],
    ['task_blog_requirements', 'enable_word_count', 'TINYINT(1)', 'Enable/disable word count validation'],
    ['task_quizzes', 'points', 'INT', 'Points per question (default 1)'],
    ['task_completions', 'reviewed_at', 'TIMESTAMP', 'When admin reviewed the completion']
];

foreach ($columns as $col) {
    echo "<tr>";
    echo "<td style='padding: 10px; border: 1px solid #d1d5db;'><code>{$col[0]}</code></td>";
    echo "<td style='padding: 10px; border: 1px solid #d1d5db;'><code>{$col[1]}</code></td>";
    echo "<td style='padding: 10px; border: 1px solid #d1d5db;'><code>{$col[2]}</code></td>";
    echo "<td style='padding: 10px; border: 1px solid #d1d5db;'>{$col[3]}</td>";
    echo "</tr>";
}

echo "</table>";
echo "</div>";

mysqli_close($conn);

echo "<hr>";
echo "<p style='text-align: center; color: #6b7280;'>Database update script completed at " . date('Y-m-d H:i:s') . "</p>";
?>

<style>
body {
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
    max-width: 1200px;
    margin: 0 auto;
    padding: 20px;
    background: #f9fafb;
}

h2 {
    color: #111827;
    border-bottom: 3px solid #667eea;
    padding-bottom: 10px;
}

code {
    background: #e5e7eb;
    padding: 2px 6px;
    border-radius: 4px;
    font-family: 'Courier New', monospace;
}

ul {
    line-height: 1.8;
}

li {
    margin-bottom: 8px;
}
</style>