<?php
/**
 * Database Table Verification Script
 */
require_once 'config.php';

echo "<!DOCTYPE html>
<html>
<head>
    <title>Database Verification</title>
    <style>
        body { font-family: Arial; margin: 40px; background: #f5f5f5; }
        .container { max-width: 800px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        h1 { color: #333; border-bottom: 3px solid #667eea; padding-bottom: 10px; }
        .success { background: #d4edda; color: #155724; padding: 12px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #28a745; }
        .error { background: #f8d7da; color: #721c24; padding: 12px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #dc3545; }
        .warning { background: #fff3cd; color: #856404; padding: 12px; margin: 10px 0; border-radius: 5px; border-left: 4px solid #ffc107; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
        th { background: #667eea; color: white; }
        tr:hover { background: #f9f9f9; }
        .check { font-weight: bold; }
        .check-pass { color: #28a745; }
        .check-fail { color: #dc3545; }
    </style>
</head>
<body>
<div class='container'>
    <h1>✅ Database Verification Report</h1>";

// Check connection
if ($conn->connect_error) {
    echo "<div class='error'>❌ Database Connection Failed: " . $conn->connect_error . "</div>";
    die();
} else {
    echo "<div class='success'>✅ Database Connected Successfully</div>";
}

// Define required tables
$required_tables = [
    'users' => ['id', 'email', 'password', 'role', 'status'],
    'properties' => ['id', 'title', 'price', 'broker_id', 'city'],
    'inquiries' => ['id', 'assigned_to', 'status', 'property_id'],
    'appointments' => ['id', 'user_id', 'appointment_date', 'status'],
    'services' => ['id', 'title'],
    'blog_posts' => ['id', 'title'],
    'agents' => ['id', 'name', 'email'],
];

echo "<h2>📋 Table Structure Verification</h2>";
echo "<table>
    <tr>
        <th>Table Name</th>
        <th>Status</th>
        <th>Required Columns</th>
        <th>Check</th>
    </tr>";

foreach ($required_tables as $table => $required_columns) {
    $result = $conn->query("SHOW COLUMNS FROM $table");
    
    if (!$result) {
        echo "<tr>
            <td><strong>$table</strong></td>
            <td style='background: #f8d7da;'>❌ Missing</td>
            <td>N/A</td>
            <td><span class='check check-fail'>MISSING TABLE</span></td>
        </tr>";
        continue;
    }
    
    $columns = [];
    while ($col = $result->fetch_assoc()) {
        $columns[] = $col['Field'];
    }
    
    $missing = array_diff($required_columns, $columns);
    
    if (empty($missing)) {
        echo "<tr>
            <td><strong>$table</strong></td>
            <td style='background: #d4edda;'>✅ Exists</td>
            <td>" . implode(', ', $required_columns) . "</td>
            <td><span class='check check-pass'>PASS</span></td>
        </tr>";
    } else {
        echo "<tr>
            <td><strong>$table</strong></td>
            <td style='background: #fff3cd;'>⚠️ Incomplete</td>
            <td>Missing: " . implode(', ', $missing) . "</td>
            <td><span class='check check-fail'>FAIL</span></td>
        </tr>";
    }
}

echo "</table>";

// Check test users
echo "<h2>👥 Test Users Verification</h2>";
$users_check = $conn->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
if ($users_check) {
    echo "<table>
        <tr>
            <th>Role</th>
            <th>Count</th>
            <th>Status</th>
        </tr>";
    
    $roles_found = [];
    while ($row = $users_check->fetch_assoc()) {
        $roles_found[$row['role']] = $row['count'];
        $status = $row['count'] > 0 ? '✅ Found' : '❌ Not Found';
        echo "<tr>
            <td><strong>" . htmlspecialchars($row['role']) . "</strong></td>
            <td>" . $row['count'] . "</td>
            <td>" . $status . "</td>
        </tr>";
    }
    echo "</table>";
}

// Check sample data
echo "<h2>📊 Sample Data Check</h2>";
echo "<table>
    <tr>
        <th>Data Type</th>
        <th>Count</th>
        <th>Status</th>
    </tr>";

$checks = [
    'Properties' => 'SELECT COUNT(*) as count FROM properties',
    'Inquiries' => 'SELECT COUNT(*) as count FROM inquiries',
    'Appointments' => 'SELECT COUNT(*) as count FROM appointments',
    'Services' => 'SELECT COUNT(*) as count FROM services',
    'Blog Posts' => 'SELECT COUNT(*) as count FROM blog_posts',
];

foreach ($checks as $label => $query) {
    $result = $conn->query($query);
    if ($result) {
        $row = $result->fetch_assoc();
        $count = $row['count'];
        $status = $count > 0 ? '✅ Has Data' : '⚠️ Empty';
        echo "<tr>
            <td><strong>$label</strong></td>
            <td>$count</td>
            <td>$status</td>
        </tr>";
    }
}
echo "</table>";

// Login page verification
echo "<h2>🔐 Login Pages Status</h2>";
echo "<table>
    <tr>
        <th>Login Page</th>
        <th>URL</th>
        <th>Status</th>
    </tr>";

$login_pages = [
    'Customer Login' => 'login.php',
    'Staff Login' => 'staff-login.php',
    'Broker Login' => 'broker-login.php',
    'Admin Login' => 'admin-login.php',
];

foreach ($login_pages as $label => $file) {
    $path = dirname(__FILE__) . '/' . $file;
    $status = file_exists($path) ? '✅ Exists' : '❌ Missing';
    $url = 'http://localhost/Zemaraim/' . $file;
    echo "<tr>
        <td><strong>$label</strong></td>
        <td><a href='$url' target='_blank'>$file</a></td>
        <td>$status</td>
    </tr>";
}
echo "</table>";

// Dashboard verification
echo "<h2>📊 Dashboard Pages Status</h2>";
echo "<table>
    <tr>
        <th>Dashboard</th>
        <th>File</th>
        <th>Status</th>
    </tr>";

$dashboards = [
    'Admin Dashboard' => 'dashboards/admin-dashboard.php',
    'Customer Dashboard' => 'dashboards/customer-dashboard.php',
    'Staff Dashboard' => 'dashboards/staff-dashboard.php',
    'Broker Dashboard' => 'dashboards/broker-dashboard.php',
];

foreach ($dashboards as $label => $file) {
    $path = dirname(__FILE__) . '/' . $file;
    $status = file_exists($path) ? '✅ Exists' : '❌ Missing';
    echo "<tr>
        <td><strong>$label</strong></td>
        <td>$file</td>
        <td>$status</td>
    </tr>";
}
echo "</table>";

echo "<h2>🚀 Next Steps</h2>";
echo "<div class='success'>
    <strong>✅ Database Setup Complete!</strong><br><br>
    <ol>
        <li>Visit <a href='setup-users.php'>setup-users.php</a> to create test accounts</li>
        <li>Test customer login: <a href='login.php'>login.php</a></li>
        <li>Test staff login: <a href='staff-login.php'>staff-login.php</a></li>
        <li>Test broker login: <a href='broker-login.php'>broker-login.php</a></li>
        <li>Test admin login: <a href='admin-login.php'>admin-login.php</a></li>
    </ol>
</div>";

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