<?php
// Database connection
$conn = new mysqli("localhost", "root", "", "firstchoice_dashboard");

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

echo "<h2>Checking Team Table Schema...</h2>";

// Array of columns to check and add
$columnsToAdd = [
    ['name' => 'role', 'type' => 'VARCHAR(100)', 'after' => 'name'],
    ['name' => 'email', 'type' => 'VARCHAR(100)', 'after' => 'role'],
    ['name' => 'phone', 'type' => 'VARCHAR(20)', 'after' => 'email'],
    ['name' => 'bio', 'type' => 'TEXT', 'after' => 'phone']
];

// Check and add each column
foreach ($columnsToAdd as $col) {
    $result = $conn->query("SHOW COLUMNS FROM team WHERE Field = '{$col['name']}'");
    
    if ($result->num_rows == 0) {
        $sql = "ALTER TABLE team ADD COLUMN {$col['name']} {$col['type']} AFTER {$col['after']}";
        if ($conn->query($sql) === TRUE) {
            echo "✓ Added '{$col['name']}' column<br>";
        } else {
            echo "✗ Error adding '{$col['name']}': " . $conn->error . "<br>";
        }
    } else {
        echo "✓ '{$col['name']}' column already exists<br>";
    }
}

echo "<br><a href='admin/pages/team/team.php' style='padding:10px 20px; background:#2d7a5c; color:white; text-decoration:none; border-radius:5px;'>Go to Team Management</a>";

echo "<hr><h2>Checking Certifications Table Schema...</h2>";

$certColumnsToAdd = [
    ['name' => 'issuer', 'type' => 'VARCHAR(100)', 'after' => 'title'],
    ['name' => 'image', 'type' => 'VARCHAR(255)', 'after' => 'issuer'],
    ['name' => 'issued_date', 'type' => 'DATE', 'after' => 'image']
];

foreach ($certColumnsToAdd as $col) {
    $result = $conn->query("SHOW COLUMNS FROM certifications WHERE Field = '{$col['name']}'");

    if ($result->num_rows == 0) {
        $sql = "ALTER TABLE certifications ADD COLUMN {$col['name']} {$col['type']} AFTER {$col['after']}";
        if ($conn->query($sql) === TRUE) {
            echo "âœ“ Added '{$col['name']}' column<br>";
        } else {
            echo "âœ— Error adding '{$col['name']}': " . $conn->error . "<br>";
        }
    } else {
        echo "âœ“ '{$col['name']}' column already exists<br>";
    }
}

$conn->close();
?>
