-- Migration : salon commun → conversations privées
-- Exécuter si vous aviez déjà installé l'ancienne version

USE chat;

-- Renommer user_id en sender_id si l'ancienne colonne existe
SET @has_user_id = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'chat' AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'user_id'
);

SET @sql = IF(@has_user_id > 0,
    'ALTER TABLE messages CHANGE user_id sender_id INT UNSIGNED NOT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter recipient_id si absent
SET @has_recipient = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'chat' AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'recipient_id'
);

SET @sql = IF(@has_recipient = 0,
    'ALTER TABLE messages ADD COLUMN recipient_id INT UNSIGNED NULL AFTER sender_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Supprimer les anciens messages de salon (sans destinataire)
DELETE FROM messages WHERE recipient_id IS NULL;

-- Rendre recipient_id obligatoire (ignorer l'erreur si déjà NOT NULL)
ALTER TABLE messages MODIFY recipient_id INT UNSIGNED NOT NULL;
