<?php
// update_schema.php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

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

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

try {
    echo "Loading environment...\n";
    Env::load(__DIR__ . '/../../.env');
    
    echo "Connecting to database...\n";
    $db = Database::getInstance()->getConnection();
    
    echo "Adding last_message_id to conversations...\n";
    try {
        $db->exec("ALTER TABLE conversations ADD COLUMN last_message_id INT UNSIGNED NULL");
        echo "Column added successfully.\n";
    } catch (PDOException $e) {
        echo "Column might already exist: " . $e->getMessage() . "\n";
    }

    echo "Updating existing conversations...\n";
    $convs = $db->query("SELECT id FROM conversations")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($convs as $c) {
        $stmt = $db->prepare("SELECT id, created_at FROM messages WHERE conversation_id = ? ORDER BY id DESC LIMIT 1");
        $stmt->execute([$c['id']]);
        $msg = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($msg) {
            $update = $db->prepare("UPDATE conversations SET last_message_id = ?, last_message_at = ? WHERE id = ?");
            $update->execute([$msg['id'], $msg['created_at'], $c['id']]);
        }
    }
    echo "Schema update complete.\n";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
    echo "Trace: " . $e->getTraceAsString() . "\n";
}
