<?php
require_once 'config/db.php';

header('Content-Type: text/plain; charset=utf-8');

echo "=== DIAGNÓSTICO DE MENSAJES ===\n\n";

try {
    // 1. Contar total de mensajes
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM messages");
    $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    echo "1. Total de mensajes en BD: $total\n\n";
    
    // 2. Mostrar últimos 5 mensajes
    echo "2. Últimos 5 mensajes:\n";
    $stmt = $pdo->query("SELECT id, chat_id, sender_id, content, created_at FROM messages ORDER BY id DESC LIMIT 5");
    $recent = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($recent as $msg) {
        echo "   - ID: {$msg['id']}, Chat: {$msg['chat_id']}, Sender: {$msg['sender_id']}, Content: " . substr($msg['content'], 0, 30) . "...\n";
    }
    echo "\n";
    
    // 3. Listar todos los chat_ids únicos
    echo "3. Chat IDs únicos en mensajes:\n";
    $stmt = $pdo->query("SELECT DISTINCT chat_id, COUNT(*) as msg_count FROM messages GROUP BY chat_id");
    $chats = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($chats as $chat) {
        echo "   - Chat ID: {$chat['chat_id']} ({$chat['msg_count']} mensajes)\n";
    }
    echo "\n";
    
    // 4. Probar la consulta que usa get_messages.php
    echo "4. Probando consulta de get_messages.php:\n";
    $test_chat_id = $chats[0]['chat_id'] ?? 1; // Usar el primer chat_id encontrado
    $test_last_id = 0;
    
    echo "   Parámetros: chat_id=$test_chat_id, last_id=$test_last_id\n";
    
    $sql = "SELECT m.*, u.i_username as username, u.user_avatar as avatar 
            FROM messages m 
            JOIN i_users u ON m.sender_id = u.iuid 
            WHERE m.chat_id = ? AND m.id > ? 
            ORDER BY m.id ASC LIMIT 50";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$test_chat_id, $test_last_id]);
    $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "   Resultados: " . count($messages) . " mensajes encontrados\n";
    
    if (count($messages) > 0) {
        echo "   Primer mensaje: ID={$messages[0]['id']}, Content=" . substr($messages[0]['content'], 0, 30) . "...\n";
    }
    echo "\n";
    
    // 5. Verificar tabla i_users
    echo "5. Verificando usuarios en i_users:\n";
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM i_users");
    $user_count = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    echo "   Total usuarios: $user_count\n";
    
    $stmt = $pdo->query("SELECT iuid, i_username FROM i_users LIMIT 3");
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($users as $user) {
        echo "   - ID: {$user['iuid']}, Username: {$user['i_username']}\n";
    }
    
} catch (PDOException $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
}

echo "\n=== FIN DEL DIAGNÓSTICO ===\n";
?>
