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

echo "<h2>Applying Schema Updates...</h2>";

if (!$conn) {
    die("Database connection failed.");
}

// 1. Add fields to 'users' table
$updates = [
    "ADD COLUMN IF NOT EXISTS role ENUM('admin', 'editor', 'contributor') DEFAULT 'contributor' AFTER password",
    "ADD COLUMN IF NOT EXISTS status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' AFTER role",
    "ADD COLUMN IF NOT EXISTS assigned_category_id INT NULL AFTER status",
    "ADD INDEX (assigned_category_id)"
];

foreach ($updates as $sql) {
    echo "Executing: <code>ALTER TABLE users $sql</code>... ";
    try {
        if ($conn->query("ALTER TABLE users $sql")) {
            echo "<span style='color:green'>Success</span><br>";
        } else {
            // Check if error is due to duplicate column (MariaDB doesn't support IF NOT EXISTS in ALTER consistently in older versions)
            echo "<span style='color:orange'>Skipped/Error (" . $conn->error . ")</span><br>";
        }
    } catch (Exception $e) {
        echo "<span style='color:red'>Error: " . $e->getMessage() . "</span><br>";
    }
}

// 2. Set default admin role
$conn->query("UPDATE users SET role='admin', status='approved' WHERE email='admin@washvoice.org'");
echo "Updated default admin role.<br>";

echo "<h3>Schema update complete.</h3>";
echo "<a href='admin.php'>Go to Admin Panel</a>";
?>
