<?php
/**
 * Database connection test script
 * 
 * This script tests connections to both the API database and the Deywuro database.
 * Run this script to verify your database configuration is correct.
 */

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

echo "===================================================\n";
echo "      Sender ID API - Database Connection Test      \n";
echo "===================================================\n\n";

// Display configuration
echo "Current Configuration:\n";
echo "API DB: " . API_DB_HOST . ":" . API_DB_PORT . " (user: " . API_DB_USER . ")\n";
echo "Deywuro DB: " . PRIMARY_DB_HOST . ":" . PRIMARY_DB_PORT . " (user: " . PRIMARY_DB_USER . ")\n\n";

// Test API database
echo "Testing API database connection...\n";
try {
    $apiDb = Database::getApiConnection();
    
    // Try to query the database to ensure connection works
    $result = $apiDb->query("SELECT DATABASE() as db_name");
    $row = $result->fetch_assoc();
    
    echo "\033[32m✅ API Database connection successful!\033[0m\n";
    echo "   Connected to database: " . $row['db_name'] . "\n";
    
    // Test some tables
    echo "\nChecking API database tables...\n";
    $tables = ['api_keys', 'ip_whitelist', 'sender_id_requests', 'api_request_logs'];
    foreach ($tables as $table) {
        $result = $apiDb->query("SHOW TABLES LIKE '$table'");
        if ($result->num_rows > 0) {
            echo "   \033[32m✅ Table '$table' exists\033[0m\n";
        } else {
            echo "   \033[31m❌ Table '$table' does not exist!\033[0m\n";
        }
    }
    
    $apiDb->close();
} catch (Exception $e) {
    echo "\033[31m❌ API Database connection failed: " . $e->getMessage() . "\033[0m\n";
    echo "   Check your API database credentials in config/config.php\n";
    echo "   Details: " . mysqli_connect_error() . "\n";
    echo "   Try these troubleshooting steps:\n";
    echo "   - Verify your MySQL server is running\n";
    echo "   - Check if port " . API_DB_PORT . " is correct\n";
    echo "   - Verify the username '" . API_DB_USER . "' has access to database '" . API_DB_NAME . "'\n";
    echo "   - Double-check your password\n";
    echo "   - Ensure database '" . API_DB_NAME . "' exists\n";
}

echo "\n";

// Test Deywuro database
echo "Testing Deywuro database connection...\n";
try {
    $deywuroDb = Database::getPrimaryConnection();
    
    // Try to query the database to ensure connection works
    $result = $deywuroDb->query("SELECT DATABASE() as db_name");
    $row = $result->fetch_assoc();
    
    echo "\033[32m✅ Deywuro Database connection successful!\033[0m\n";
    echo "   Connected to database: " . $row['db_name'] . "\n";
    
    // Test if we can access the users table (don't show data)
    $result = $deywuroDb->query("SHOW TABLES LIKE 'users'");
    if ($result->num_rows > 0) {
        echo "   \033[32m✅ Users table exists\033[0m\n";
    } else {
        echo "   \033[31m❌ Users table does not exist or is not accessible!\033[0m\n";
        echo "   Check that you have the correct database name and sufficient privileges.\n";
    }
    
    // Test if we can access the user_sender_ids table (don't show data)
    $result = $deywuroDb->query("SHOW TABLES LIKE 'user_sender_ids'");
    if ($result->num_rows > 0) {
        echo "   \033[32m✅ Sender IDs table exists\033[0m\n";
        
        // Count total sender IDs (just to check read access)
        $result = $deywuroDb->query("SELECT COUNT(*) as total FROM user_sender_ids");
        $row = $result->fetch_assoc();
        echo "   Total sender IDs in Deywuro database: " . $row['total'] . "\n";
    } else {
        echo "   \033[31m❌ Sender IDs table does not exist or is not accessible!\033[0m\n";
        echo "   Check that you have the correct database name and sufficient privileges.\n";
    }
    
    $deywuroDb->close();
} catch (Exception $e) {
    echo "\033[31m❌ Deywuro Database connection failed: " . $e->getMessage() . "\033[0m\n";
    echo "   Check your Deywuro database credentials in config/config.php\n";
    echo "   Details: " . mysqli_connect_error() . "\n";
    echo "   Try these troubleshooting steps:\n";
    echo "   - Verify the host " . PRIMARY_DB_HOST . " is correct and accessible from your network\n";
    echo "   - Check if port " . PRIMARY_DB_PORT . " is correct\n";
    echo "   - Make sure your IP is allowed to connect to the remote database (firewall/security rules)\n";
    echo "   - Verify the username '" . PRIMARY_DB_USER . "' has access to database '" . PRIMARY_DB_NAME . "'\n";
}

echo "\n===================================================\n";
echo "Test completed. Fix any errors before proceeding.\n";
echo "===================================================\n";