-- ============================================================
-- Migration : Renommer elemental_efficiency → spell_damage
-- + Ajouter spell_damage_bonus aux talents (fallen_1, fallen_21)
-- ============================================================
-- L'ancienne stat "Efficacité élémentaire" est devenue "Dégâts des sorts".
-- On renomme les colonnes pour que le code soit cohérent.
-- Cette migration est idempotente (re-exécutable sans erreur).
-- ============================================================

SET @dbname = DATABASE();

-- ============================================================
-- 1. character_stats : elemental_efficiency → spell_damage
-- ============================================================
SET @col_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'elemental_efficiency');
SET @col_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'spell_damage');
SET @sql = IF(@col_old = 1 AND @col_new = 0, 'ALTER TABLE character_stats CHANGE COLUMN elemental_efficiency spell_damage INT DEFAULT 100', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Si la colonne n'existe pas du tout (nouveau setup), la créer
SET @col_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'spell_damage');
SET @sql = IF(@col_new = 0, 'ALTER TABLE character_stats ADD COLUMN spell_damage INT DEFAULT 100 AFTER crit_resistance', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 2. enemies : elemental_efficiency → spell_damage
-- ============================================================
SET @col_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'enemies' AND COLUMN_NAME = 'elemental_efficiency');
SET @col_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'enemies' AND COLUMN_NAME = 'spell_damage');
SET @sql = IF(@col_old = 1 AND @col_new = 0, 'ALTER TABLE enemies CHANGE COLUMN elemental_efficiency spell_damage INT DEFAULT 100', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 3. talents : ajouter spell_damage_bonus (et supprimer elemental_efficiency_bonus si existe)
-- ============================================================
-- Ajouter spell_damage_bonus si absent
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'talents' AND COLUMN_NAME = 'spell_damage_bonus');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE talents ADD COLUMN spell_damage_bonus INT DEFAULT 0 AFTER damage_reflection_bonus', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Copier les valeurs depuis elemental_efficiency_bonus si cette colonne existe
SET @col_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'talents' AND COLUMN_NAME = 'elemental_efficiency_bonus');
SET @sql = IF(@col_old = 1, 'UPDATE talents SET spell_damage_bonus = elemental_efficiency_bonus WHERE elemental_efficiency_bonus > 0', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Supprimer l'ancienne colonne elemental_efficiency_bonus si elle existe
SET @sql = IF(@col_old = 1, 'ALTER TABLE talents DROP COLUMN elemental_efficiency_bonus', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 4. Migrer fallen_1 et fallen_21 : magical_damage_bonus → spell_damage_bonus
-- ============================================================
UPDATE talents SET magical_damage_bonus = 0, spell_damage_bonus = 10 WHERE id = 'fallen_1';
UPDATE talents SET magical_damage_bonus = 0, spell_damage_bonus = 25 WHERE id = 'fallen_21';

-- ============================================================
-- Vérification :
-- SELECT id, magical_damage_bonus, spell_damage_bonus FROM talents WHERE id IN ('fallen_1', 'fallen_21');
-- SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'character_stats' AND COLUMN_NAME IN ('elemental_efficiency', 'spell_damage');
-- SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'enemies' AND COLUMN_NAME IN ('elemental_efficiency', 'spell_damage');
-- ============================================================
