<?php
echo "<h1>🔧 SPM Database Connection Fix</h1>";
echo "<style>body{font-family:Arial,sans-serif;margin:20px;max-width:800px;} .success{color:green;} .error{color:red;} .info{color:blue;} pre{background:#f5f5f5;padding:10px;border-radius:4px;}</style>";

// Step 1: Check if .env file exists
echo "<h2>1. Checking .env file...</h2>";
if (file_exists('../.env')) {
    echo "<p class='success'>✅ .env file exists</p>";

    // Read and check database configuration
    $envContent = file_get_contents('../.env');
    $hasDbConfig = strpos($envContent, 'DB_CONNECTION=mysql') !== false;
    $hasDbHost = strpos($envContent, 'DB_HOST=127.0.0.1') !== false;
    $hasDbDatabase = strpos($envContent, 'DB_DATABASE=spm_db') !== false;

    echo "<p><strong>Database config check:</strong></p>";
    echo "<ul>";
    echo "<li>DB_CONNECTION=mysql: <span class='" . ($hasDbConfig ? "success" : "error") . "'>" . ($hasDbConfig ? "✅" : "❌") . "</span></li>";
    echo "<li>DB_HOST=127.0.0.1: <span class='" . ($hasDbHost ? "success" : "error") . "'>" . ($hasDbHost ? "✅" : "❌") . "</span></li>";
    echo "<li>DB_DATABASE=spm_db: <span class='" . ($hasDbDatabase ? "success" : "error") . "'>" . ($hasDbDatabase ? "✅" : "❌") . "</span></li>";
    echo "</ul>";

} else {
    echo "<p class='error'>❌ .env file does not exist</p>";
    echo "<p class='info'>🔄 Creating .env file...</p>";

    $envContent = "APP_NAME=Spm1
APP_ENV=local
APP_KEY=base64:YourAppKeyHere
APP_DEBUG=true
APP_TIMEZONE=UTC
APP_URL=http://localhost

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=spm_db
DB_USERNAME=root
DB_PASSWORD=

SESSION_DRIVER=database
CACHE_STORE=database";

    if (file_put_contents('../.env', $envContent)) {
        echo "<p class='success'>✅ .env file created successfully</p>";
    } else {
        echo "<p class='error'>❌ Failed to create .env file</p>";
    }
}

// Step 2: Test database connection
echo "<h2>2. Testing database connection...</h2>";
try {
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=spm_db', 'root', '');
    echo "<p class='success'>✅ Direct MySQL connection successful</p>";

    // Test Laravel database 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 actual query that's failing
    echo "<p class='info'>🔄 Testing profile query...</p>";
    $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() . " profiles</p>";

    echo "<h2>🎉 SUCCESS! Database connection is now working!</h2>";
    echo "<p>You can now access your Laravel application normally.</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 XAMPP MySQL is running</li>";
        echo "<li>Check that database 'spm_db' exists in phpMyAdmin</li>";
        echo "<li>Verify firewall is not blocking MySQL connections</li>";
        echo "</ul>";
        echo "</div>";
    }
}

echo "<hr>";
echo "<p><small>This script is located at: public/fix_db.php</small></p>";
?>
