<?php
/**
 * Database diagnostic script
 */

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

echo "<h2>Database Diagnostic Report</h2>";

try {
    // Check if buses table exists
    $result = $pdo->query("SHOW TABLES LIKE 'buses'");
    $buses_table_exists = $result->rowCount() > 0;

    if ($buses_table_exists) {
        echo "<p style='color: green;'>✅ buses table exists</p>";
        
        // Count records in buses table
        $count = $pdo->query("SELECT COUNT(*) as count FROM buses")->fetch();
        echo "<p>Records in buses table: {$count['count']}</p>";
        
        // Show table structure
        $columns = $pdo->query("DESCRIBE buses")->fetchAll();
        echo "<h3>buses table structure:</h3><ul>";
        foreach ($columns as $column) {
            echo "<li>{$column['Field']} - {$column['Type']} - {$column['Null']} - {$column['Key']}</li>";
        }
        echo "</ul>";
    } else {
        echo "<p style='color: red;'>❌ buses table does not exist</p>";
    }

    // Check other transport tables
    $transport_tables = ['bus_routes', 'bus_route_stops', 'bus_assignments', 'student_bus_subscriptions', 'bus_attendance'];
    echo "<h3>Other Transport Tables:</h3>";
    foreach ($transport_tables as $table) {
        $result = $pdo->query("SHOW TABLES LIKE '$table'");
        $exists = $result->rowCount() > 0;
        $status = $exists ? "✅ $table exists" : "❌ $table does not exist";
        $color = $exists ? "green" : "red";
        echo "<p style='color: $color;'>$status</p>";
    }

    // Check if user is logged in by checking session
    session_start();
    if (isset($_SESSION['user_id'])) {
        echo "<p style='color: green;'>✅ User is logged in (user_id: {$_SESSION['user_id']})</p>";
        echo "<p>User role: {$_SESSION['user_role']}</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ User is not logged in - this could be why buses.php is not loading</p>";
        echo "<p><a href='login.php'>Go to login page</a></p>";
    }

} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}
?>