<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "admission_system";

$conn = new mysqli($host, $user, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Fixing database schema and admin role...\n\n";

// First, update the role field to accept system_admin
echo "=== Updating Database Schema ===\n";
$alter_sql = "ALTER TABLE users MODIFY COLUMN role ENUM('student','admin','system_admin') DEFAULT 'student'";
if ($conn->query($alter_sql) === TRUE) {
    echo "Database schema updated successfully\n";
} else {
    echo "Error updating schema: " . $conn->error . "\n";
}

// Now update the admin role
echo "\n=== Updating Admin Role ===\n";
$email = "admin@uol.edu.pk";
$update_sql = "UPDATE users SET role = 'system_admin' WHERE email = '$email'";
if ($conn->query($update_sql) === TRUE) {
    echo "Admin role updated to 'system_admin'\n";
} else {
    echo "Error updating role: " . $conn->error . "\n";
}

// Verification
echo "\n=== Final Verification ===\n";
$verify = $conn->query("SELECT id, email, role, full_name FROM users WHERE email = '$email'");
if($verify->num_rows > 0) {
    $row = $verify->fetch_assoc();
    echo "ID: {$row['id']}\n";
    echo "Email: {$row['email']}\n";
    echo "Role: '{$row['role']}'\n";
    echo "Full Name: {$row['full_name']}\n";
    
    // Test login query
    echo "\n=== Testing Login Query ===\n";
    $test_sql = "SELECT id, full_name, password, role FROM users WHERE email = '$email' AND role IN ('admin', 'system_admin')";
    $test_result = $conn->query($test_sql);
    echo "Results found: " . $test_result->num_rows . "\n";
    
    if ($test_result->num_rows == 1) {
        echo "SUCCESS: Login query will find this user\n";
        echo "\nLogin credentials ready:\n";
        echo "Email: admin@uol.edu.pk\n";
        echo "Password: admin@123\n";
        echo "Role: system_admin\n";
    } else {
        echo "FAILED: Login query still won't find this user\n";
    }
}

$conn->close();
?>
