<?php
require_once __DIR__ . '/../src/Utils/Env.php';
require_once __DIR__ . '/../src/Config/Database.php';

use App\Utils\Env;
use App\Config\Database;

Env::load(__DIR__ . '/../../.env');
$db = Database::getInstance()->getConnection();

echo "<h1>System Diagnostic</h1>";

// 1. Schema Check
echo "<h2>1. Database Schema</h2>";
$tables = ['conversations', 'messages', 'users'];
foreach ($tables as $table) {
    echo "<h3>Table: $table</h3>";
    $stmt = $db->query("SHOW COLUMNS FROM $table");
    $cols = $stmt->fetchAll(PDO::FETCH_COLUMN);
    echo implode(", ", $cols) . "<br>";
}

// 2. Data Check
echo "<h2>2. Recent Data</h2>";
echo "<h3>Conversations (Last 5)</h3>";
$stmt = $db->query("SELECT * FROM conversations ORDER BY id DESC LIMIT 5");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<table border='1'><tr>";
if ($rows) foreach (array_keys($rows[0]) as $k) echo "<th>$k</th>";
echo "</tr>";
foreach ($rows as $r) {
    echo "<tr>";
    foreach ($r as $v) echo "<td>$v</td>";
    echo "</tr>";
}
echo "</table>";

echo "<h3>Messages (Last 5)</h3>";
$stmt = $db->query("SELECT id, conversation_id, sender_id, sender_alias, is_anonymous, body_text FROM messages ORDER BY id DESC LIMIT 5");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<table border='1'><tr>";
if ($rows) foreach (array_keys($rows[0]) as $k) echo "<th>$k</th>";
echo "</tr>";
foreach ($rows as $r) {
    echo "<tr>";
    foreach ($r as $v) echo "<td>$v</td>";
    echo "</tr>";
}
echo "</table>";

// 3. Logic Simulation
echo "<h2>3. Logic Simulation</h2>";
$testUserId = 1; // Assuming ID 1 exists
$testPlaceholders = "?, ?";
$testParams = [$testUserId, $testUserId];

$sql = "
    SELECT c.id, c.user_a_id, c.user_b_id, c.is_anonymous
    FROM conversations c
    LEFT JOIN users u1 ON c.user_a_id = u1.id
    LEFT JOIN users u2 ON c.user_b_id = u2.id
    WHERE (c.user_a_id IN ($testUserId) OR c.user_b_id IN ($testUserId))
    ORDER BY c.last_message_at DESC
";
echo "<strong>Query:</strong> $sql<br>";

try {
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "<strong>Result for User ID $testUserId:</strong><pre>" . print_r($res, true) . "</pre>";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
