<?php
require_once '../app/Core/init.php';
Auth::requireAdmin();

echo "<h1>Messages Table Deep Debug</h1>";

$db = Database::getInstance();

// Check actual data in messages table
$messages_data = $db->fetchAll("SELECT * FROM messages");
echo "<h2>Raw Messages Data:</h2>";
echo "<pre>";
print_r($messages_data);
echo "</pre>";

// Check users table structure to see if 'name' column exists
$users_structure = $db->fetchAll("DESCRIBE users");
echo "<h2>Users Table Structure:</h2>";
echo "<pre>";
print_r($users_structure);
echo "</pre>";

// Test the JOIN query directly
$test_query = "SELECT m.*, 
                      COALESCE(u.name, u.email) as sender_name, 
                      u.email as sender_email 
               FROM messages m 
               LEFT JOIN users u ON m.sender_id = u.id 
               ORDER BY m.sent_at DESC 
               LIMIT 10";
               
$test_results = $db->fetchAll($test_query);
echo "<h2>Test JOIN Query Results:</h2>";
echo "<pre>";
print_r($test_results);
echo "</pre>";

// Check if sender_id exists in users table
if (!empty($messages_data)) {
    foreach ($messages_data as $message) {
        $user_check = $db->fetch("SELECT id, email FROM users WHERE id = ?", [$message['sender_id']]);
        echo "<p>Sender ID {$message['sender_id']} exists in users: " . ($user_check ? 'YES - ' . $user_check['email'] : 'NO') . "</p>";
    }
}
?>