-- Migration: Corriger toutes les incohérences des talents
-- Référence: AUDIT_TALENTS_EFFETS.md, AUDIT_TALENTS_RAPPORT.md, AUDIT_TALENTS_SYNC.md

-- ============================================================
-- A. VALEURS MANQUANTES EN DB (description dit X, DB a 0)
-- ============================================================

-- ice_8 "Armure de givre": description dit +8 Armure, DB a 0
UPDATE talents SET armor_bonus = 8 WHERE id = 'ice_8';

-- ice_14 "Voile de givre": description dit +10% évasion +5 Armure, DB manque armor
UPDATE talents SET armor_bonus = 5 WHERE id = 'ice_14';

-- air_6 "Anneaux d'Air": description dit +10% rés. critiques, DB a 0
UPDATE talents SET crit_resistance_bonus = 10 WHERE id = 'air_6';

-- air_14 "Dôme de Brume": description dit +5% rés. critiques (en plus de évasion=6), DB manque
UPDATE talents SET crit_resistance_bonus = 5 WHERE id = 'air_14';

-- air_18 "Noyau d'orage": description dit +25% dégâts critiques, DB a 0
UPDATE talents SET crit_damage_bonus = 25 WHERE id = 'air_18';

-- air_22 "Portails des Vents": description dit +20% rés. critiques (en plus de crit=8, evasion=8), DB manque
UPDATE talents SET crit_resistance_bonus = 20 WHERE id = 'air_22';

-- ice_4 "Fracture glaciale": CS a CooldownReduction=1 sur Volée d'éclats, DB a 0
UPDATE talents SET cooldown_reduction = 1 WHERE id = 'ice_4';

-- ============================================================
-- B. MIGRATION COLONNES OBSOLETES (physical_defense → armor, magical_defense → magic_resistance)
-- Les colonnes physical_defense_bonus et magical_defense_bonus ont déjà été supprimées.
-- On ne met à jour que les nouvelles colonnes (armor_bonus, magic_resistance_bonus).
-- ============================================================

-- paladin_4: physical_defense_bonus=5 → armor_bonus=5
UPDATE talents SET armor_bonus = 5 WHERE id = 'paladin_4';

-- paladin_11: physical_defense_bonus=3 + magical_defense_bonus=3 → armor_bonus=3 + magic_resistance_bonus=3
UPDATE talents SET armor_bonus = 3, magic_resistance_bonus = 3 WHERE id = 'paladin_11';

-- sacred_7: physical_defense_bonus=3 → armor_bonus=3
UPDATE talents SET armor_bonus = 3 WHERE id = 'sacred_7';

-- fallen_11: magical_defense_bonus=3 → magic_resistance_bonus=3
UPDATE talents SET magic_resistance_bonus = 3 WHERE id = 'fallen_11';

-- ============================================================
-- D. NOUVELLES COLONNES character_stats
-- Utilise IF NOT EXISTS pour être idempotent (re-exécutable sans erreur)
-- ============================================================

SET @dbname = DATABASE();

-- damage_reduction
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'damage_reduction');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE character_stats ADD COLUMN damage_reduction INT DEFAULT 0 AFTER damage_reflection', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- healing_bonus
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'healing_bonus');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE character_stats ADD COLUMN healing_bonus INT DEFAULT 0 AFTER damage_reduction', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- physical_damage_bonus
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'physical_damage_bonus');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE character_stats ADD COLUMN physical_damage_bonus INT DEFAULT 0 AFTER healing_bonus', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- magical_damage_bonus
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'character_stats' AND COLUMN_NAME = 'magical_damage_bonus');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE character_stats ADD COLUMN magical_damage_bonus INT DEFAULT 0 AFTER physical_damage_bonus', 'SELECT 1 INTO @noop');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- VÉRIFICATION (pas de changement, déjà correct en DB)
-- ============================================================
-- fire_1: damage_reflection_bonus=10 ✓
-- fire_6: crit_chance_bonus=8 ✓
-- ice_3: intelligence_bonus=5, armor_bonus=5 ✓
-- ice_10: magic_resistance_bonus=15 ✓
-- air_1: crit_chance_bonus=5 ✓
-- air_2: evasion_bonus=8 ✓
-- air_3: dexterity_bonus=5, attack_speed_bonus=3 ✓
-- air_8: attack_speed_bonus=4 ✓
-- sacred_1: healing_bonus=10 ✓
-- sacred_5: mana_regen_bonus=2 ✓
-- sacred_10: damage_reduction_bonus=5 ✓
-- sacred_14: all_stats_bonus=2 ✓
-- sacred_15: health_regen_bonus=2 ✓
-- sacred_21: healing_bonus=20 ✓
-- sacred_23: intelligence_bonus=5 ✓
-- fallen_1: spell_damage_bonus=10 (migré depuis magical_damage_bonus, voir migrate_elemental_efficiency_bonus.sql)
-- fallen_5: evasion_bonus=5 ✓
-- fallen_7: dexterity_bonus=2 ✓
-- fallen_15: intelligence_bonus=3 ✓
-- fallen_21: spell_damage_bonus=25 (migré depuis magical_damage_bonus, voir migrate_elemental_efficiency_bonus.sql)
-- fallen_23: dexterity_bonus=5, intelligence_bonus=5 ✓
-- paladin_1: physical_damage_bonus=10 ✓
-- paladin_7: strength_bonus=2 ✓
-- paladin_9: damage_reflection_bonus=10 ✓
-- paladin_14: strength_bonus=2, dexterity_bonus=2, intelligence_bonus=2 ✓
-- paladin_16: crit_chance_bonus=15 ✓
-- paladin_17: health_regen_bonus=3 ✓
-- paladin_21: physical_damage_bonus=15, damage_reduction_bonus=15 ✓
-- paladin_23: strength_bonus=5, crit_chance_bonus=10, damage_reduction_bonus=10 ✓
