<?php
echo "<h1>🔧 Update Database Host Configuration</h1>";
echo "<style>body{font-family:Arial,sans-serif;margin:20px;} .success{color:green;font-weight:bold;} .error{color:red;font-weight:bold;} .info{color:blue;}</style>";

$envFile = '../.env';
$correctHost = '192.168.10.70';

if (file_exists($envFile)) {
    echo "<p class='info'>Reading current .env file...</p>";

    $envContent = file_get_contents($envFile);
    $originalContent = $envContent;

    // Update DB_HOST to the correct IP
    $envContent = preg_replace('/DB_HOST=.*/', "DB_HOST=$correctHost", $envContent);

    if ($envContent !== $originalContent) {
        if (file_put_contents($envFile, $envContent)) {
            echo "<p class='success'>✅ Updated DB_HOST to $correctHost</p>";
        } else {
            echo "<p class='error'>❌ Failed to update .env file</p>";
        }
    } else {
        echo "<p class='info'>DB_HOST already set to $correctHost</p>";
    }

    // Test connection with new host
    echo "<h2>Testing Database Connection</h2>";
    try {
        $pdo = new PDO("mysql:host=$correctHost;dbname=spm_db;charset=utf8", 'root', '');
        echo "<p class='success'>✅ MySQL connection successful to $correctHost</p>";

        // Test Laravel connection
        echo "<p class='info'>Testing Laravel database connection...</p>";
        require_once '../vendor/autoload.php';
        $app = require_once '../bootstrap/app.php';
        $app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();

        $laravelPdo = DB::connection('mysql')->getPdo();
        echo "<p class='success'>✅ Laravel database connection successful!</p>";

        // Test the problematic query
        $profiles = DB::table('profiles')->where('is_active', 1)->orderBy('order')->orderBy('created_at', 'desc')->get();
        echo "<p class='success'>✅ Profile query successful - found " . $profiles->count() . " active profiles</p>";

        echo "<h2>🎉 SUCCESS! Database connection fixed!</h2>";
        echo "<p>You can now access your Laravel application normally.</p>";
        echo "<p><strong>Main Application:</strong> <a href='http://192.168.10.70' target='_blank'>http://192.168.10.70</a></p>";
        echo "<p><strong>phpMyAdmin:</strong> <a href='http://192.168.10.70/phpmyadmin' target='_blank'>http://192.168.10.70/phpmyadmin</a></p>";

    } catch (Exception $e) {
        echo "<p class='error'>❌ Database connection failed: " . $e->getMessage() . "</p>";

        if (strpos($e->getMessage(), 'No connection could be made') !== false) {
            echo "<div style='background:#ffe6e6;border:1px solid #ff9999;padding:15px;border-radius:4px;margin:10px 0;'>";
            echo "<h3>🔍 Troubleshooting:</h3>";
            echo "<ul>";
            echo "<li>Make sure MySQL server is running on $correctHost</li>";
            echo "<li>Check if firewall is blocking connections to port 3306</li>";
            echo "<li>Verify that the database 'spm_db' exists on the remote server</li>";
            echo "<li>Check if remote MySQL allows connections from this IP address</li>";
            echo "</ul>";
            echo "</div>";
        }
    }

} else {
    echo "<p class='error'>❌ .env file not found</p>";
}

echo "<hr>";
echo "<p><a href='test.php'>← Back to Test Page</a></p>";
?>
