<?php
session_start();

// Check if user is logged in and is admin
if (!isset($_SESSION['user_id'])) {
    die('Please login first');
}

require_once 'config/db.php';
$conn = $connection;

if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Check</title>
    <style>
        body { font-family: Arial; padding: 20px; background: #f5f5f5; }
        .container { max-width: 1200px; margin: 0 auto; }
        .section { background: white; padding: 20px; margin: 20px 0; border-radius: 8px; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
        h2 { color: #333; border-bottom: 2px solid #d4a574; padding-bottom: 10px; }
        table { width: 100%; border-collapse: collapse; margin-top: 10px; }
        th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
        th { background: #f9f9f9; font-weight: bold; }
        tr:hover { background: #f5f5f5; }
        .count { font-size: 24px; font-weight: bold; color: #d4a574; }
        .empty { color: #999; font-style: italic; }
    </style>
</head>
<body>
    <div class="container">
        <h1>📊 Database Data Check</h1>
        
        <div class="section">
            <h2>👥 Users Table</h2>
            <?php
            $result = $conn->query("SELECT COUNT(*) as count FROM users");
            $row = $result->fetch_assoc();
            $count = $row['count'];
            echo "<p>Total Users: <span class='count'>$count</span></p>";
            
            if ($count > 0) {
                $users = $conn->query("SELECT id, name, email, phone, created_at FROM users LIMIT 10");
                echo "<table>";
                echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Phone</th><th>Created</th></tr>";
                while ($user = $users->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($user['id']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['name']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['email']) . "</td>";
                    echo "<td>" . htmlspecialchars($user['phone'] ?? 'N/A') . "</td>";
                    echo "<td>" . date('M d, Y', strtotime($user['created_at'])) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<p class='empty'>No users found in database</p>";
            }
            ?>
        </div>

        <div class="section">
            <h2>📦 Orders Table</h2>
            <?php
            $result = $conn->query("SELECT COUNT(*) as count FROM orders");
            $row = $result->fetch_assoc();
            $count = $row['count'];
            echo "<p>Total Orders: <span class='count'>$count</span></p>";
            
            if ($count > 0) {
                $orders = $conn->query("SELECT o.id, o.status, o.created_at, u.name as customer FROM orders o LEFT JOIN users u ON o.user_id = u.id LIMIT 10");
                echo "<table>";
                echo "<tr><th>ID</th><th>Customer</th><th>Status</th><th>Created</th></tr>";
                while ($order = $orders->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>#" . htmlspecialchars($order['id']) . "</td>";
                    echo "<td>" . htmlspecialchars($order['customer'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($order['status'] ?? 'N/A') . "</td>";
                    echo "<td>" . date('M d, Y', strtotime($order['created_at'])) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<p class='empty'>No orders found in database</p>";
            }
            ?>
        </div>

        <div class="section">
            <h2>💬 Consultations Table</h2>
            <?php
            $result = $conn->query("SELECT COUNT(*) as count FROM consultations");
            $row = $result->fetch_assoc();
            $count = $row['count'];
            echo "<p>Total Consultations: <span class='count'>$count</span></p>";
            
            if ($count > 0) {
                $consultations = $conn->query("SELECT id, first_name, last_name, email, consultation_type, status, created_at FROM consultations LIMIT 10");
                echo "<table>";
                echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Type</th><th>Status</th><th>Created</th></tr>";
                while ($cons = $consultations->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>#" . htmlspecialchars($cons['id']) . "</td>";
                    echo "<td>" . htmlspecialchars(($cons['first_name'] ?? '') . ' ' . ($cons['last_name'] ?? '')) . "</td>";
                    echo "<td>" . htmlspecialchars($cons['email'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($cons['consultation_type'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($cons['status'] ?? 'N/A') . "</td>";
                    echo "<td>" . date('M d, Y', strtotime($cons['created_at'])) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<p class='empty'>No consultations found in database</p>";
            }
            ?>
        </div>

        <div class="section">
            <h2>🔧 Database Info</h2>
            <?php
            $result = $conn->query("SELECT DATABASE() as db");
            $row = $result->fetch_assoc();
            echo "<p><strong>Current Database:</strong> " . htmlspecialchars($row['db']) . "</p>";
            
            // Check table existence
            $tables = ['users', 'orders', 'consultations'];
            echo "<p><strong>Tables Status:</strong></p>";
            echo "<ul>";
            foreach ($tables as $table) {
                $check = $conn->query("SHOW TABLES LIKE '$table'");
                $exists = $check->num_rows > 0 ? '✅ Exists' : '❌ Missing';
                echo "<li>$table: $exists</li>";
            }
            echo "</ul>";
            ?>
        </div>
    </div>
</body>
</html>
