<?php
/**
 * Database Diagnostic Tool
 * Run this to check database connection issues
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<!DOCTYPE html>
<html>
<head>
    <title>Database Diagnostic</title>
    <style>
        body { font-family: Arial; padding: 20px; background: #f5f5f5; }
        .success { color: green; background: #e8f5e8; padding: 10px; margin: 5px; border-left: 4px solid green; }
        .error { color: red; background: #ffe8e8; padding: 10px; margin: 5px; border-left: 4px solid red; }
        .warning { color: orange; background: #fff3e0; padding: 10px; margin: 5px; border-left: 4px solid orange; }
        pre { background: #333; color: #fff; padding: 10px; border-radius: 5px; }
    </style>
</head>
<body>
    <h1>🔧 Database Connection Diagnostic</h1>";

// Step 1: Check if config file exists and load it
echo "<h2>Step 1: Loading Configuration</h2>";

$config_file = dirname(__DIR__) . '/config/config.php';
if (file_exists($config_file)) {
    require_once $config_file;
    echo "<div class='success'>✅ config.php loaded</div>";
    
    // Display database constants
    echo "<h3>Database Configuration:</h3>";
    echo "<ul>";
    echo "<li><strong>DB_HOST:</strong> " . (defined('DB_HOST') ? DB_HOST : 'NOT DEFINED') . "</li>";
    echo "<li><strong>DB_NAME:</strong> " . (defined('DB_NAME') ? DB_NAME : 'NOT DEFINED') . "</li>";
    echo "<li><strong>DB_USER:</strong> " . (defined('DB_USER') ? DB_USER : 'NOT DEFINED') . "</li>";
    echo "<li><strong>DB_PASS:</strong> " . (defined('DB_PASS') ? (empty(DB_PASS) ? '[empty]' : '[set]') : 'NOT DEFINED') . "</li>";
    echo "<li><strong>DB_CHARSET:</strong> " . (defined('DB_CHARSET') ? DB_CHARSET : 'NOT DEFINED') . "</li>";
    echo "</ul>";
} else {
    echo "<div class='error'>❌ config.php not found at: $config_file</div>";
    exit;
}

// Step 2: Test direct PDO connection
echo "<h2>Step 2: Testing Direct PDO Connection</h2>";

try {
    $dsn = "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET;
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<div class='success'>✅ Connected to MySQL server</div>";
    
    // Check if database exists
    $stmt = $pdo->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" . DB_NAME . "'");
    $db_exists = $stmt->fetch();
    
    if ($db_exists) {
        echo "<div class='success'>✅ Database '" . DB_NAME . "' exists</div>";
        
        // Connect to specific database
        $pdo->exec("USE " . DB_NAME);
        echo "<div class='success'>✅ Connected to database: " . DB_NAME . "</div>";
        
        // List tables
        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        echo "<div>Tables found: " . count($tables) . "</div>";
        if (count($tables) > 0) {
            echo "<ul>";
            foreach ($tables as $table) {
                echo "<li>" . $table . "</li>";
            }
            echo "</ul>";
        } else {
            echo "<div class='warning'>⚠️ No tables found in database</div>";
        }
        
        // Check specific tables needed for dashboard
        $required_tables = ['users', 'reservations', 'rooms', 'guests', 'folio_charges', 'notifications'];
        echo "<h3>Required Tables Check:</h3>";
        foreach ($required_tables as $table) {
            if (in_array($table, $tables)) {
                echo "<div class='success'>✅ Table '$table' exists</div>";
            } else {
                echo "<div class='error'>❌ Table '$table' does NOT exist</div>";
            }
        }
        
    } else {
        echo "<div class='error'>❌ Database '" . DB_NAME . "' does NOT exist</div>";
        echo "<p>Create it with:</p>";
        echo "<pre>CREATE DATABASE " . DB_NAME . " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;</pre>";
    }
    
} catch (PDOException $e) {
    echo "<div class='error'>❌ Connection failed: " . $e->getMessage() . "</div>";
    
    // Provide specific solutions based on error
    $error_msg = $e->getMessage();
    if (strpos($error_msg, 'Access denied') !== false) {
        echo "<div class='warning'>⚠️ Solution: Check username/password in config.php</div>";
    } elseif (strpos($error_msg, 'Connection refused') !== false) {
        echo "<div class='warning'>⚠️ Solution: MySQL server is not running. Start it from XAMPP.</div>";
    } elseif (strpos($error_msg, 'Unknown database') !== false) {
        echo "<div class='warning'>⚠️ Solution: Database doesn't exist. Create it first.</div>";
    }
}

// Step 3: Test database.php functions
echo "<h2>Step 3: Testing database.php Functions</h2>";

$db_file = dirname(__DIR__) . '/config/database.php';
if (file_exists($db_file)) {
    require_once $db_file;
    echo "<div class='success'>✅ database.php loaded</div>";
    
    if (function_exists('get_db_connection')) {
        try {
            $pdo = get_db_connection();
            echo "<div class='success'>✅ get_db_connection() works</div>";
        } catch (Exception $e) {
            echo "<div class='error'>❌ get_db_connection() failed: " . $e->getMessage() . "</div>";
        }
    } else {
        echo "<div class='error'>❌ get_db_connection() function not found</div>";
    }
    
    if (function_exists('db_query')) {
        try {
            $result = db_query("SELECT 1 as test");
            echo "<div class='success'>✅ db_query() works</div>";
        } catch (Exception $e) {
            echo "<div class='error'>❌ db_query() failed: " . $e->getMessage() . "</div>";
        }
    }
} else {
    echo "<div class='error'>❌ database.php not found</div>";
}

// Step 4: MySQL server status
echo "<h2>Step 4: MySQL Server Information</h2>";

try {
    $pdo = new PDO("mysql:host=" . DB_HOST, DB_USER, DB_PASS);
    $version = $pdo->query("SELECT VERSION()")->fetchColumn();
    echo "<div>MySQL Version: " . $version . "</div>";
    
    $variables = [
        'max_connections',
        'wait_timeout',
        'character_set_server',
        'collation_server'
    ];
    
    foreach ($variables as $var) {
        $value = $pdo->query("SHOW VARIABLES LIKE '$var'")->fetchColumn(1);
        echo "<div><strong>$var:</strong> $value</div>";
    }
    
} catch (Exception $e) {
    echo "<div class='error'>Could not get server info: " . $e->getMessage() . "</div>";
}

echo "<h2>Quick Fix Options</h2>";

echo "<h3>Option 1: Create Database and Tables</h3>";
echo "<pre>
-- Run this in phpMyAdmin or MySQL command line
CREATE DATABASE IF NOT EXISTS " . DB_NAME . " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE " . DB_NAME . ";

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    role_id INT DEFAULT 2,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME,
    last_login_ip VARCHAR(45)
);

-- Create rooms table
CREATE TABLE IF NOT EXISTS rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_number VARCHAR(10) UNIQUE NOT NULL,
    room_type_id INT,
    status ENUM('available','occupied','maintenance','housekeeping','blocked') DEFAULT 'available',
    housekeeping_status ENUM('clean','dirty','inspected') DEFAULT 'clean',
    is_active TINYINT(1) DEFAULT 1
);

-- Create guests table
CREATE TABLE IF NOT EXISTS guests (
    guest_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create reservations table
CREATE TABLE IF NOT EXISTS reservations (
    reservation_id INT PRIMARY KEY AUTO_INCREMENT,
    booking_code VARCHAR(20) UNIQUE NOT NULL,
    guest_id INT,
    room_id INT,
    room_type_id INT,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    booking_status ENUM('pending','confirmed','checked_in','checked_out','cancelled','no_show') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
);

-- Create notifications table
CREATE TABLE IF NOT EXISTS notifications (
    notification_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('info','success','warning','danger') DEFAULT 'info',
    title VARCHAR(255) NOT NULL,
    message TEXT,
    link VARCHAR(255),
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert a test user (password: Admin@123)
INSERT INTO users (username, email, password_hash, first_name, last_name, role_id) 
VALUES ('admin', 'admin@ruhondo.com', '" . password_hash('Admin@123', PASSWORD_DEFAULT) . "', 'Admin', 'User', 1);
</pre>";

echo "<h3>Option 2: Check XAMPP MySQL Status</h3>";
echo "<ul>";
echo "<li>Open XAMPP Control Panel</li>";
echo "<li>Make sure MySQL is running (green light)</li>";
echo "<li>If not, click Start button for MySQL</li>";
echo "<li>Click 'Admin' to open phpMyAdmin and verify database exists</li>";
echo "</ul>";

echo "<h3>Option 3: Update config.php with correct credentials</h3>";
echo "<pre>
// In config.php, make sure these are correct:
define('DB_HOST', 'localhost');
define('DB_NAME', 'ruhondo_hms');
define('DB_USER', 'root');  // Default XAMPP username
define('DB_PASS', '');       // Default XAMPP password is empty
</pre>";

echo "<hr>";
echo "<p><a href='dashboard.php'>Try dashboard again</a> | <a href='login.php'>Go to login</a></p>";

echo "</body></html>";
?>