<?php
// server_php/check_db.php
header('Content-Type: text/plain; charset=utf-8');

echo "=== Environment Diagnostic ===\n";
echo "PHP Version: " . phpversion() . "\n";
echo "OS: " . PHP_OS . "\n";

require_once 'config/database.php';
echo "\n=== Extension Check ===\n";
echo "pdo_mysql: " . (extension_loaded('pdo_mysql') ? "[OK] loaded" : "[ERROR] missing") . "\n";
echo "redis: " . (extension_loaded('redis') ? "[OK] loaded" : "[WARNING] missing") . "\n";
echo "json: " . (extension_loaded('json') ? "[OK] loaded" : "[ERROR] missing") . "\n";

echo "\n=== Runtime Path Check ===\n";
$runtimeDir = db_runtime_dir();
$cfg = db_mysql_config();
echo "DB_BACKEND: " . DB_BACKEND . "\n";
echo "Runtime Dir: " . $runtimeDir . "\n";
echo "Host: " . $cfg['host'] . "\n";
echo "Port: " . $cfg['port'] . "\n";
echo "Database: " . $cfg['database'] . "\n";
echo "User: " . $cfg['username'] . "\n";

if (is_dir($runtimeDir) && is_writable($runtimeDir)) {
    echo "[OK] Runtime directory is writable.\n";
} else {
    echo "[ERROR] Runtime directory is NOT writable. The web server user needs write permissions to this directory.\n";
}

echo "\n=== Connection Test ===\n";
try {
    $db = new Database();
    $conn = $db->getConnection();
    $db->initDB();
    echo "[OK] Successfully connected to MySQL database.\n";

    $stmt = $conn->query("SELECT VERSION()");
    $ver = $stmt->fetchColumn();
    db_close_cursor($stmt);
    echo "[OK] MySQL Version: $ver\n";

    $stmt = $conn->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()");
    $tableCount = intval($stmt->fetchColumn());
    db_close_cursor($stmt);
    echo "[OK] Table Count: $tableCount\n";

    $stmt = $conn->query("SELECT meta_value FROM schema_meta WHERE meta_key = '__schema_version__' LIMIT 1");
    $schemaVersion = $stmt->fetchColumn();
    db_close_cursor($stmt);
    echo "[OK] Schema Version: " . ($schemaVersion === false ? "0" : $schemaVersion) . "\n";
} catch (Exception $e) {
    echo "[ERROR] Connection Failed: " . $e->getMessage() . "\n";
}

echo "\n=== Schema Spot Check ===\n";
try {
    $db = new Database();
    $conn = $db->getConnection();
    $requiredTables = array('users', 'characters', 'servers', 'realtime_sessions', 'realtime_presence', 'chat_live_messages');
    foreach ($requiredTables as $tableName) {
        $stmt = $conn->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :name");
        db_execute_with_retry($stmt, array(':name' => $tableName));
        $exists = intval($stmt->fetchColumn()) > 0;
        db_close_cursor($stmt);
        echo ($exists ? "[OK] " : "[MISSING] ") . $tableName . "\n";
    }
} catch (Exception $e) {
    echo "[ERROR] Schema Spot Check Failed: " . $e->getMessage() . "\n";
}
?>