<?php
require 'db.php';

try {
    // 1. Add is_deleted to messages
    $stmt = $pdo->query("SHOW COLUMNS FROM messages LIKE 'is_deleted'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE messages ADD COLUMN is_deleted TINYINT DEFAULT 0");
        echo "Added is_deleted column.\n";
    }

    // 2. Add Rooms table
    $pdo->exec("CREATE TABLE IF NOT EXISTS rooms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Created rooms table.\n";

    // 3. Ensure General room exists
    $stmt = $pdo->query("SELECT count(*) FROM rooms WHERE id = 1");
    if ($stmt->fetchColumn() == 0) {
        $pdo->exec("INSERT INTO rooms (id, name) VALUES (1, 'General')");
        echo "Created General room.\n";
    }

    // 4. Add room_id to messages
    $stmt = $pdo->query("SHOW COLUMNS FROM messages LIKE 'room_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE messages ADD COLUMN room_id INT DEFAULT 1");
        echo "Added room_id column to messages.\n";
    }
    
    echo "Migration V2 Complete.";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>
