-- =====================================================
-- MIGRATION : Refonte du système d'armures
-- =====================================================
-- Ce script :
-- 1. Ajoute les colonnes de rolls armures à inventory_items
-- 2. Recrée la table armors avec la nouvelle structure
-- 3. Supprime les armures existantes de l'inventaire
-- =====================================================

-- 1. Ajouter colonnes rolls armures à inventory_items (si elles n'existent pas)
ALTER TABLE inventory_items
ADD COLUMN IF NOT EXISTS roll_evasion FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS roll_crit_resistance FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS roll_speed FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS roll_health_regen FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS roll_mana_regen FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS roll_damage_reflection FLOAT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS armor_stat_plus3 VARCHAR(20) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS armor_stat_plus6 VARCHAR(20) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS armor_stat_plus9 VARCHAR(20) DEFAULT NULL;

-- 2. Supprimer les armures existantes de l'inventaire
DELETE FROM inventory_items WHERE item_type = 'armor';

-- 3. Recréer la table armors
DROP TABLE IF EXISTS armors;

CREATE TABLE IF NOT EXISTS armors (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(20) NOT NULL,  -- Belt, Legs, Chest, Boots, Gloves, Helmet
    tier INT NOT NULL DEFAULT 1,
    rarity INT NOT NULL DEFAULT 0,
    required_level INT NOT NULL DEFAULT 1,
    sell_price INT NOT NULL DEFAULT 0,
    armor_bonus INT NOT NULL DEFAULT 0,
    magic_resistance_bonus INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Insérer les 18 armures de base avec toutes les variantes +0 à +9
-- Tier 1: Style cuir
-- Tier 2: Style asiatique, tissus rouges
-- Tier 3: Style elfique, jade

INSERT INTO armors (id, name, category, tier, rarity, required_level, sell_price, armor_bonus, magic_resistance_bonus) VALUES
-- ==================== JAMBIERES (200-399) ====================
-- Jambieres de cuir (base 200, +0 à +9)
(200, 'Jambieres de cuir', 'Legs', 1, 0, 1, 15, 3, 1),
(201, 'Jambieres de cuir +1', 'Legs', 1, 0, 1, 17, 3, 1),
(202, 'Jambieres de cuir +2', 'Legs', 1, 0, 1, 19, 3, 1),
(203, 'Jambieres de cuir +3', 'Legs', 1, 0, 1, 21, 3, 1),
(204, 'Jambieres de cuir +4', 'Legs', 1, 0, 1, 24, 3, 1),
(205, 'Jambieres de cuir +5', 'Legs', 1, 0, 1, 28, 3, 1),
(206, 'Jambieres de cuir +6', 'Legs', 1, 0, 1, 33, 3, 1),
(207, 'Jambieres de cuir +7', 'Legs', 1, 0, 1, 40, 3, 1),
(208, 'Jambieres de cuir +8', 'Legs', 1, 0, 1, 50, 3, 1),
(209, 'Jambieres de cuir +9', 'Legs', 1, 0, 1, 65, 3, 1),
-- Jambieres ecarlates (base 210, +0 à +9)
(210, 'Jambieres ecarlates', 'Legs', 2, 0, 3, 35, 5, 2),
(211, 'Jambieres ecarlates +1', 'Legs', 2, 0, 3, 39, 5, 2),
(212, 'Jambieres ecarlates +2', 'Legs', 2, 0, 3, 43, 5, 2),
(213, 'Jambieres ecarlates +3', 'Legs', 2, 0, 3, 47, 5, 2),
(214, 'Jambieres ecarlates +4', 'Legs', 2, 0, 3, 52, 5, 2),
(215, 'Jambieres ecarlates +5', 'Legs', 2, 0, 3, 60, 5, 2),
(216, 'Jambieres ecarlates +6', 'Legs', 2, 0, 3, 70, 5, 2),
(217, 'Jambieres ecarlates +7', 'Legs', 2, 0, 3, 85, 5, 2),
(218, 'Jambieres ecarlates +8', 'Legs', 2, 0, 3, 105, 5, 2),
(219, 'Jambieres ecarlates +9', 'Legs', 2, 0, 3, 130, 5, 2),
-- Jambieres de jade (base 220, +0 à +9)
(220, 'Jambieres de jade', 'Legs', 3, 0, 5, 60, 7, 3),
(221, 'Jambieres de jade +1', 'Legs', 3, 0, 5, 66, 7, 3),
(222, 'Jambieres de jade +2', 'Legs', 3, 0, 5, 72, 7, 3),
(223, 'Jambieres de jade +3', 'Legs', 3, 0, 5, 80, 7, 3),
(224, 'Jambieres de jade +4', 'Legs', 3, 0, 5, 90, 7, 3),
(225, 'Jambieres de jade +5', 'Legs', 3, 0, 5, 102, 7, 3),
(226, 'Jambieres de jade +6', 'Legs', 3, 0, 5, 118, 7, 3),
(227, 'Jambieres de jade +7', 'Legs', 3, 0, 5, 138, 7, 3),
(228, 'Jambieres de jade +8', 'Legs', 3, 0, 5, 165, 7, 3),
(229, 'Jambieres de jade +9', 'Legs', 3, 0, 5, 200, 7, 3),

-- ==================== PLASTRONS (400-599) ====================
-- Plastron de cuir (base 400, +0 à +9)
(400, 'Plastron de cuir', 'Chest', 1, 0, 1, 20, 5, 2),
(401, 'Plastron de cuir +1', 'Chest', 1, 0, 1, 23, 5, 2),
(402, 'Plastron de cuir +2', 'Chest', 1, 0, 1, 26, 5, 2),
(403, 'Plastron de cuir +3', 'Chest', 1, 0, 1, 29, 5, 2),
(404, 'Plastron de cuir +4', 'Chest', 1, 0, 1, 33, 5, 2),
(405, 'Plastron de cuir +5', 'Chest', 1, 0, 1, 38, 5, 2),
(406, 'Plastron de cuir +6', 'Chest', 1, 0, 1, 45, 5, 2),
(407, 'Plastron de cuir +7', 'Chest', 1, 0, 1, 55, 5, 2),
(408, 'Plastron de cuir +8', 'Chest', 1, 0, 1, 70, 5, 2),
(409, 'Plastron de cuir +9', 'Chest', 1, 0, 1, 90, 5, 2),
-- Plastron ecarlate (base 410, +0 à +9)
(410, 'Plastron ecarlate', 'Chest', 2, 0, 3, 50, 8, 3),
(411, 'Plastron ecarlate +1', 'Chest', 2, 0, 3, 56, 8, 3),
(412, 'Plastron ecarlate +2', 'Chest', 2, 0, 3, 62, 8, 3),
(413, 'Plastron ecarlate +3', 'Chest', 2, 0, 3, 70, 8, 3),
(414, 'Plastron ecarlate +4', 'Chest', 2, 0, 3, 80, 8, 3),
(415, 'Plastron ecarlate +5', 'Chest', 2, 0, 3, 92, 8, 3),
(416, 'Plastron ecarlate +6', 'Chest', 2, 0, 3, 108, 8, 3),
(417, 'Plastron ecarlate +7', 'Chest', 2, 0, 3, 130, 8, 3),
(418, 'Plastron ecarlate +8', 'Chest', 2, 0, 3, 160, 8, 3),
(419, 'Plastron ecarlate +9', 'Chest', 2, 0, 3, 200, 8, 3),
-- Plastron de jade (base 420, +0 à +9)
(420, 'Plastron de jade', 'Chest', 3, 0, 5, 85, 12, 5),
(421, 'Plastron de jade +1', 'Chest', 3, 0, 5, 94, 12, 5),
(422, 'Plastron de jade +2', 'Chest', 3, 0, 5, 103, 12, 5),
(423, 'Plastron de jade +3', 'Chest', 3, 0, 5, 115, 12, 5),
(424, 'Plastron de jade +4', 'Chest', 3, 0, 5, 130, 12, 5),
(425, 'Plastron de jade +5', 'Chest', 3, 0, 5, 150, 12, 5),
(426, 'Plastron de jade +6', 'Chest', 3, 0, 5, 175, 12, 5),
(427, 'Plastron de jade +7', 'Chest', 3, 0, 5, 210, 12, 5),
(428, 'Plastron de jade +8', 'Chest', 3, 0, 5, 260, 12, 5),
(429, 'Plastron de jade +9', 'Chest', 3, 0, 5, 320, 12, 5),

-- ==================== BOTTES (600-799) ====================
-- Bottes de cuir (base 600, +0 à +9)
(600, 'Bottes de cuir', 'Boots', 1, 0, 1, 10, 1, 1),
(601, 'Bottes de cuir +1', 'Boots', 1, 0, 1, 12, 1, 1),
(602, 'Bottes de cuir +2', 'Boots', 1, 0, 1, 14, 1, 1),
(603, 'Bottes de cuir +3', 'Boots', 1, 0, 1, 16, 1, 1),
(604, 'Bottes de cuir +4', 'Boots', 1, 0, 1, 18, 1, 1),
(605, 'Bottes de cuir +5', 'Boots', 1, 0, 1, 20, 1, 1),
(606, 'Bottes de cuir +6', 'Boots', 1, 0, 1, 25, 1, 1),
(607, 'Bottes de cuir +7', 'Boots', 1, 0, 1, 30, 1, 1),
(608, 'Bottes de cuir +8', 'Boots', 1, 0, 1, 40, 1, 1),
(609, 'Bottes de cuir +9', 'Boots', 1, 0, 1, 50, 1, 1),
-- Bottes ecarlates (base 610, +0 à +9)
(610, 'Bottes ecarlates', 'Boots', 2, 0, 3, 30, 3, 2),
(611, 'Bottes ecarlates +1', 'Boots', 2, 0, 3, 34, 3, 2),
(612, 'Bottes ecarlates +2', 'Boots', 2, 0, 3, 38, 3, 2),
(613, 'Bottes ecarlates +3', 'Boots', 2, 0, 3, 42, 3, 2),
(614, 'Bottes ecarlates +4', 'Boots', 2, 0, 3, 47, 3, 2),
(615, 'Bottes ecarlates +5', 'Boots', 2, 0, 3, 54, 3, 2),
(616, 'Bottes ecarlates +6', 'Boots', 2, 0, 3, 63, 3, 2),
(617, 'Bottes ecarlates +7', 'Boots', 2, 0, 3, 76, 3, 2),
(618, 'Bottes ecarlates +8', 'Boots', 2, 0, 3, 95, 3, 2),
(619, 'Bottes ecarlates +9', 'Boots', 2, 0, 3, 120, 3, 2),
-- Bottes de jade (base 620, +0 à +9)
(620, 'Bottes de jade', 'Boots', 3, 0, 5, 55, 5, 3),
(621, 'Bottes de jade +1', 'Boots', 3, 0, 5, 61, 5, 3),
(622, 'Bottes de jade +2', 'Boots', 3, 0, 5, 67, 5, 3),
(623, 'Bottes de jade +3', 'Boots', 3, 0, 5, 75, 5, 3),
(624, 'Bottes de jade +4', 'Boots', 3, 0, 5, 85, 5, 3),
(625, 'Bottes de jade +5', 'Boots', 3, 0, 5, 97, 5, 3),
(626, 'Bottes de jade +6', 'Boots', 3, 0, 5, 112, 5, 3),
(627, 'Bottes de jade +7', 'Boots', 3, 0, 5, 132, 5, 3),
(628, 'Bottes de jade +8', 'Boots', 3, 0, 5, 158, 5, 3),
(629, 'Bottes de jade +9', 'Boots', 3, 0, 5, 190, 5, 3),

-- ==================== GANTS (800-999) ====================
-- Gants de cuir (base 800, +0 à +9)
(800, 'Gants de cuir', 'Gloves', 1, 0, 1, 10, 1, 1),
(801, 'Gants de cuir +1', 'Gloves', 1, 0, 1, 12, 1, 1),
(802, 'Gants de cuir +2', 'Gloves', 1, 0, 1, 14, 1, 1),
(803, 'Gants de cuir +3', 'Gloves', 1, 0, 1, 16, 1, 1),
(804, 'Gants de cuir +4', 'Gloves', 1, 0, 1, 18, 1, 1),
(805, 'Gants de cuir +5', 'Gloves', 1, 0, 1, 20, 1, 1),
(806, 'Gants de cuir +6', 'Gloves', 1, 0, 1, 25, 1, 1),
(807, 'Gants de cuir +7', 'Gloves', 1, 0, 1, 30, 1, 1),
(808, 'Gants de cuir +8', 'Gloves', 1, 0, 1, 40, 1, 1),
(809, 'Gants de cuir +9', 'Gloves', 1, 0, 1, 50, 1, 1),
-- Gants ecarlates (base 810, +0 à +9)
(810, 'Gants ecarlates', 'Gloves', 2, 0, 3, 25, 2, 1),
(811, 'Gants ecarlates +1', 'Gloves', 2, 0, 3, 28, 2, 1),
(812, 'Gants ecarlates +2', 'Gloves', 2, 0, 3, 31, 2, 1),
(813, 'Gants ecarlates +3', 'Gloves', 2, 0, 3, 34, 2, 1),
(814, 'Gants ecarlates +4', 'Gloves', 2, 0, 3, 38, 2, 1),
(815, 'Gants ecarlates +5', 'Gloves', 2, 0, 3, 44, 2, 1),
(816, 'Gants ecarlates +6', 'Gloves', 2, 0, 3, 52, 2, 1),
(817, 'Gants ecarlates +7', 'Gloves', 2, 0, 3, 63, 2, 1),
(818, 'Gants ecarlates +8', 'Gloves', 2, 0, 3, 78, 2, 1),
(819, 'Gants ecarlates +9', 'Gloves', 2, 0, 3, 98, 2, 1),
-- Gantelets de jade (base 820, +0 à +9)
(820, 'Gantelets de jade', 'Gloves', 3, 0, 5, 45, 4, 2),
(821, 'Gantelets de jade +1', 'Gloves', 3, 0, 5, 50, 4, 2),
(822, 'Gantelets de jade +2', 'Gloves', 3, 0, 5, 55, 4, 2),
(823, 'Gantelets de jade +3', 'Gloves', 3, 0, 5, 62, 4, 2),
(824, 'Gantelets de jade +4', 'Gloves', 3, 0, 5, 70, 4, 2),
(825, 'Gantelets de jade +5', 'Gloves', 3, 0, 5, 80, 4, 2),
(826, 'Gantelets de jade +6', 'Gloves', 3, 0, 5, 93, 4, 2),
(827, 'Gantelets de jade +7', 'Gloves', 3, 0, 5, 110, 4, 2),
(828, 'Gantelets de jade +8', 'Gloves', 3, 0, 5, 132, 4, 2),
(829, 'Gantelets de jade +9', 'Gloves', 3, 0, 5, 160, 4, 2),

-- ==================== CASQUES (1000-1199) ====================
-- Casque de cuir (base 1000, +0 à +9)
(1000, 'Casque de cuir', 'Helmet', 1, 0, 1, 15, 2, 2),
(1001, 'Casque de cuir +1', 'Helmet', 1, 0, 1, 17, 2, 2),
(1002, 'Casque de cuir +2', 'Helmet', 1, 0, 1, 19, 2, 2),
(1003, 'Casque de cuir +3', 'Helmet', 1, 0, 1, 21, 2, 2),
(1004, 'Casque de cuir +4', 'Helmet', 1, 0, 1, 24, 2, 2),
(1005, 'Casque de cuir +5', 'Helmet', 1, 0, 1, 28, 2, 2),
(1006, 'Casque de cuir +6', 'Helmet', 1, 0, 1, 33, 2, 2),
(1007, 'Casque de cuir +7', 'Helmet', 1, 0, 1, 40, 2, 2),
(1008, 'Casque de cuir +8', 'Helmet', 1, 0, 1, 50, 2, 2),
(1009, 'Casque de cuir +9', 'Helmet', 1, 0, 1, 65, 2, 2),
-- Casque ecarlate (base 1010, +0 à +9)
(1010, 'Casque ecarlate', 'Helmet', 2, 0, 3, 35, 4, 3),
(1011, 'Casque ecarlate +1', 'Helmet', 2, 0, 3, 39, 4, 3),
(1012, 'Casque ecarlate +2', 'Helmet', 2, 0, 3, 43, 4, 3),
(1013, 'Casque ecarlate +3', 'Helmet', 2, 0, 3, 48, 4, 3),
(1014, 'Casque ecarlate +4', 'Helmet', 2, 0, 3, 55, 4, 3),
(1015, 'Casque ecarlate +5', 'Helmet', 2, 0, 3, 63, 4, 3),
(1016, 'Casque ecarlate +6', 'Helmet', 2, 0, 3, 74, 4, 3),
(1017, 'Casque ecarlate +7', 'Helmet', 2, 0, 3, 88, 4, 3),
(1018, 'Casque ecarlate +8', 'Helmet', 2, 0, 3, 108, 4, 3),
(1019, 'Casque ecarlate +9', 'Helmet', 2, 0, 3, 135, 4, 3),
-- Casque de jade (base 1020, +0 à +9)
(1020, 'Casque de jade', 'Helmet', 3, 0, 5, 60, 6, 4),
(1021, 'Casque de jade +1', 'Helmet', 3, 0, 5, 66, 6, 4),
(1022, 'Casque de jade +2', 'Helmet', 3, 0, 5, 72, 6, 4),
(1023, 'Casque de jade +3', 'Helmet', 3, 0, 5, 80, 6, 4),
(1024, 'Casque de jade +4', 'Helmet', 3, 0, 5, 90, 6, 4),
(1025, 'Casque de jade +5', 'Helmet', 3, 0, 5, 102, 6, 4),
(1026, 'Casque de jade +6', 'Helmet', 3, 0, 5, 118, 6, 4),
(1027, 'Casque de jade +7', 'Helmet', 3, 0, 5, 138, 6, 4),
(1028, 'Casque de jade +8', 'Helmet', 3, 0, 5, 165, 6, 4),
(1029, 'Casque de jade +9', 'Helmet', 3, 0, 5, 200, 6, 4),

-- ==================== CEINTURES (1200-1399) ====================
-- Ceinture de cuir (base 1200, +0 à +9)
(1200, 'Ceinture de cuir', 'Belt', 1, 0, 1, 10, 2, 1),
(1201, 'Ceinture de cuir +1', 'Belt', 1, 0, 1, 12, 2, 1),
(1202, 'Ceinture de cuir +2', 'Belt', 1, 0, 1, 14, 2, 1),
(1203, 'Ceinture de cuir +3', 'Belt', 1, 0, 1, 16, 2, 1),
(1204, 'Ceinture de cuir +4', 'Belt', 1, 0, 1, 18, 2, 1),
(1205, 'Ceinture de cuir +5', 'Belt', 1, 0, 1, 20, 2, 1),
(1206, 'Ceinture de cuir +6', 'Belt', 1, 0, 1, 25, 2, 1),
(1207, 'Ceinture de cuir +7', 'Belt', 1, 0, 1, 30, 2, 1),
(1208, 'Ceinture de cuir +8', 'Belt', 1, 0, 1, 40, 2, 1),
(1209, 'Ceinture de cuir +9', 'Belt', 1, 0, 1, 50, 2, 1),
-- Ceinture ecarlate (base 1210, +0 à +9)
(1210, 'Ceinture ecarlate', 'Belt', 2, 0, 3, 25, 4, 2),
(1211, 'Ceinture ecarlate +1', 'Belt', 2, 0, 3, 28, 4, 2),
(1212, 'Ceinture ecarlate +2', 'Belt', 2, 0, 3, 31, 4, 2),
(1213, 'Ceinture ecarlate +3', 'Belt', 2, 0, 3, 34, 4, 2),
(1214, 'Ceinture ecarlate +4', 'Belt', 2, 0, 3, 37, 4, 2),
(1215, 'Ceinture ecarlate +5', 'Belt', 2, 0, 3, 42, 4, 2),
(1216, 'Ceinture ecarlate +6', 'Belt', 2, 0, 3, 50, 4, 2),
(1217, 'Ceinture ecarlate +7', 'Belt', 2, 0, 3, 60, 4, 2),
(1218, 'Ceinture ecarlate +8', 'Belt', 2, 0, 3, 75, 4, 2),
(1219, 'Ceinture ecarlate +9', 'Belt', 2, 0, 3, 95, 4, 2),
-- Ceinture de jade (base 1220, +0 à +9)
(1220, 'Ceinture de jade', 'Belt', 3, 0, 5, 45, 6, 3),
(1221, 'Ceinture de jade +1', 'Belt', 3, 0, 5, 50, 6, 3),
(1222, 'Ceinture de jade +2', 'Belt', 3, 0, 5, 55, 6, 3),
(1223, 'Ceinture de jade +3', 'Belt', 3, 0, 5, 60, 6, 3),
(1224, 'Ceinture de jade +4', 'Belt', 3, 0, 5, 68, 6, 3),
(1225, 'Ceinture de jade +5', 'Belt', 3, 0, 5, 78, 6, 3),
(1226, 'Ceinture de jade +6', 'Belt', 3, 0, 5, 90, 6, 3),
(1227, 'Ceinture de jade +7', 'Belt', 3, 0, 5, 105, 6, 3),
(1228, 'Ceinture de jade +8', 'Belt', 3, 0, 5, 125, 6, 3),
(1229, 'Ceinture de jade +9', 'Belt', 3, 0, 5, 150, 6, 3);

-- Afficher le résultat
SELECT 'Migration armures terminée' AS status;
SELECT COUNT(*) AS nb_armors FROM armors;
