-- Migration : Système de potions en base de données
-- Stocke les effets, durées et ingrédients côté serveur (anti-cheat)

-- Table des potions (effets et durée)
CREATE TABLE IF NOT EXISTS potions (
    id INT PRIMARY KEY,
    effect_type VARCHAR(30) NOT NULL,
    effect_value INT NOT NULL DEFAULT 0,
    duration_seconds INT NOT NULL DEFAULT 300
);

-- Table des ingrédients par potion
CREATE TABLE IF NOT EXISTS potion_ingredients (
    potion_id INT NOT NULL,
    resource_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (potion_id, resource_id),
    FOREIGN KEY (potion_id) REFERENCES potions(id)
);

-- Seed : 13 potions (mêmes IDs que PotionDatabase.cs client)
INSERT INTO potions (id, effect_type, effect_value, duration_seconds) VALUES
(1,  'strength',         5,  300),
(2,  'dexterity',        5,  300),
(3,  'intelligence',     5,  300),
(4,  'max_health_flat',  50, 600),
(5,  'max_mana_flat',    30, 600),
(6,  'crit_chance',      10, 300),
(7,  'speed',            10, 300),
(8,  'armor',            5,  600),
(9,  'magic_resistance', 15, 600),
(10, 'health_regen',     2,  600),
(11, 'mana_regen',       1,  600),
(12, 'exp_gain',         25, 900),
(13, 'gold_gain',        25, 900);

-- Seed : ingrédients (IDs champignons : 21=Morille, 22=Chanterelle, 23=Amanite, 24=Bolet, 25=Russule)
INSERT INTO potion_ingredients (potion_id, resource_id, quantity) VALUES
(1,  21, 3),
(2,  22, 3),
(3,  23, 3),
(4,  24, 2),
(5,  25, 2),
(6,  21, 2), (6,  22, 2),
(7,  22, 2), (7,  23, 2),
(8,  24, 1), (8,  25, 1),
(9,  23, 2), (9,  25, 1),
(10, 21, 2), (10, 24, 1),
(11, 23, 2), (11, 24, 1),
(12, 21, 1), (12, 22, 1), (12, 23, 1),
(13, 24, 1), (13, 25, 1);
