<?php
require_once 'auth.php';

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

$host = "localhost";
$user = "root";
$pass = "";
$db   = "finggu";

try {
    // First, connect without database to check if we can connect to MySQL
    $pdo = new PDO("mysql:host=$host", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>✅ Successfully connected to MySQL server</h2>";
    
    // Check if database exists
    $stmt = $pdo->query("SHOW DATABASES LIKE '$db'");
    if ($stmt->rowCount() > 0) {
        echo "<p>✅ Database '$db' exists</p>";
        
        // Connect to the specific database
        $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Check for required tables
        $requiredTables = ['cities', 'services', 'city_service'];
        foreach ($requiredTables as $table) {
            $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
            echo $stmt->rowCount() > 0 
                ? "<p>✅ Table '$table' exists</p>" 
                : "<p style='color: red;'>❌ Table '$table' is MISSING</p>";
        }
        
        // Test insert
        try {
            $testCity = "TestCity_" . time();
            $stmt = $pdo->prepare("INSERT INTO cities (name, slug) VALUES (?, ?)");
            $stmt->execute([$testCity, strtolower(str_replace(' ', '-', $testCity))]);
            echo "<p>✅ Successfully inserted test city</p>";
            
            // Clean up
            $pdo->exec("DELETE FROM cities WHERE name LIKE 'TestCity_%'");
            
        } catch (Exception $e) {
            echo "<p style='color: red;'>❌ Test insert failed: " . htmlspecialchars($e->getMessage()) . "</p>";
            if (strpos($e->getMessage(), 'Table') !== false) {
                echo "<p>Would you like to create the missing tables? <a href='create-tables.php' target='_blank'>Click here to create tables</a></p>";
            }
        }
        
    } else {
        echo "<p style='color: red;'>❌ Database '$db' does not exist</p>";
        echo "<p>Would you like to create it? <a href='create-db.php' target='_blank'>Click here to create database</a></p>";
    }
    
} catch (PDOException $e) {
    die("<h2 style='color: red;'>❌ Connection failed: " . htmlspecialchars($e->getMessage()) . "</h2>");
}
?>

<h3>Next Steps:</h3>
<ol>
    <li>If you see any red error messages above, please fix those issues first</li>
    <li>If tables are missing, <a href='create-tables.php' target='_blank'>create the required tables</a></li>
    <li>After fixing any issues, <a href='manage-cities-services.php'>go back to manage cities</a></li>
</ol>
