-- ============================================
-- WebVal - Mise à jour chat_conversations
-- Ajout colonnes pour tracking API (sécurisé)
-- ============================================

-- Désactiver les erreurs temporairement
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- Ajouter colonnes une par une (ignore si existe déjà)
SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_conversations'
     AND column_name = 'provider') > 0,
    'SELECT ''Column provider already exists''',
    'ALTER TABLE chat_conversations ADD COLUMN provider VARCHAR(50) DEFAULT ''ollama'''
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_conversations'
     AND column_name = 'model_used') > 0,
    'SELECT ''Column model_used already exists''',
    'ALTER TABLE chat_conversations ADD COLUMN model_used VARCHAR(100) DEFAULT ''ministral-3:3b'''
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_conversations'
     AND column_name = 'api_response_time_ms') > 0,
    'SELECT ''Column api_response_time_ms already exists''',
    'ALTER TABLE chat_conversations ADD COLUMN api_response_time_ms INT DEFAULT NULL'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_conversations'
     AND column_name = 'id_enseignant') > 0,
    'SELECT ''Column id_enseignant already exists''',
    'ALTER TABLE chat_conversations ADD COLUMN id_enseignant INT DEFAULT NULL'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index
SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_conversations'
     AND index_name = 'idx_provider') > 0,
    'SELECT ''Index idx_provider already exists''',
    'ALTER TABLE chat_conversations ADD INDEX idx_provider (provider)'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Foreign key (plus complexe, on teste avec SHOW CREATE TABLE)
SET @fk_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                  WHERE table_schema = DATABASE()
                  AND table_name = 'chat_conversations'
                  AND referenced_table_name = 'utilisateurs'
                  AND column_name = 'id_enseignant');

SET @s = IF(@fk_exists > 0,
    'SELECT ''Foreign key already exists''',
    'ALTER TABLE chat_conversations ADD FOREIGN KEY (id_enseignant) REFERENCES utilisateurs(id_utilisateur) ON DELETE SET NULL'
);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Colonne api_cost dans chat_messages
SET @s = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE table_schema = DATABASE()
     AND table_name = 'chat_messages'
     AND column_name = 'api_cost') > 0,
    'SELECT ''Column api_cost already exists''',
    'ALTER TABLE chat_messages ADD COLUMN api_cost DECIMAL(10,6) DEFAULT 0.000000'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Restaurer les notes SQL
SET SQL_NOTES=@OLD_SQL_NOTES;

-- Remplir id_enseignant rétroactivement
UPDATE chat_conversations cc
JOIN tentatives_eleves te ON cc.id_tentative = te.id_tentative
JOIN evaluations e ON te.id_evaluation = e.id_evaluation
SET cc.id_enseignant = e.id_enseignant
WHERE cc.id_enseignant IS NULL;

SELECT '✅ Colonnes ajoutées avec succès' as Status;