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

$sqls = [
    "CREATE TABLE IF NOT EXISTS conversations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        item_id INT DEFAULT NULL,
        type ENUM('user_user','user_admin','admin_assisted') DEFAULT 'user_user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )",
    "CREATE TABLE IF NOT EXISTS conversation_participants (
        id INT AUTO_INCREMENT PRIMARY KEY,
        conversation_id INT NOT NULL,
        user_id INT NOT NULL,
        UNIQUE KEY unique_participant (conversation_id, user_id),
        FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
    )",
    "CREATE TABLE IF NOT EXISTS messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        conversation_id INT NOT NULL,
        sender_id INT NOT NULL,
        message TEXT NOT NULL,
        seen_status ENUM('seen','unseen') DEFAULT 'unseen',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
    )",
    "CREATE TABLE IF NOT EXISTS conversation_reports (
        id INT AUTO_INCREMENT PRIMARY KEY,
        reporter_id INT NOT NULL,
        reported_user_id INT NOT NULL,
        conversation_id INT NOT NULL,
        reason TEXT NOT NULL,
        status ENUM('pending','reviewed') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )"
];

echo "<h2>Messaging System – Table Setup</h2><pre>";
foreach ($sqls as $sql) {
    $table = preg_match('/TABLE IF NOT EXISTS (\w+)/', $sql, $m) ? $m[1] : '?';
    if ($conn->query($sql)) {
        echo "✅ Table `$table` created / already exists.\n";
    } else {
        echo "❌ Error on `$table`: " . $conn->error . "\n";
    }
}
echo "\n✅ Done! You can now delete this file.\n";
echo "</pre><p><a href='/lostfound_relink/'>← Back to Home</a></p>";
