-- AutoHero initial schema migration CREATE TABLE IF NOT EXISTS weapons ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, type TEXT NOT NULL CHECK (type IN ('daggers', 'sword', 'axe')), rarity TEXT NOT NULL DEFAULT 'common' CHECK (rarity IN ('common', 'uncommon', 'rare', 'epic', 'legendary')), damage INT NOT NULL DEFAULT 0, speed DOUBLE PRECISION NOT NULL DEFAULT 1.0, crit_chance DOUBLE PRECISION NOT NULL DEFAULT 0.0, special_effect TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS armor ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, type TEXT NOT NULL CHECK (type IN ('light', 'medium', 'heavy')), rarity TEXT NOT NULL DEFAULT 'common' CHECK (rarity IN ('common', 'uncommon', 'rare', 'epic', 'legendary')), defense INT NOT NULL DEFAULT 0, speed_modifier DOUBLE PRECISION NOT NULL DEFAULT 1.0, agility_bonus INT NOT NULL DEFAULT 0, set_name TEXT NOT NULL DEFAULT '', special_effect TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS heroes ( id BIGSERIAL PRIMARY KEY, telegram_id BIGINT NOT NULL UNIQUE, name TEXT NOT NULL, hp INT NOT NULL DEFAULT 100, max_hp INT NOT NULL DEFAULT 100, attack INT NOT NULL DEFAULT 10, defense INT NOT NULL DEFAULT 5, speed DOUBLE PRECISION NOT NULL DEFAULT 1.0, strength INT NOT NULL DEFAULT 1, constitution INT NOT NULL DEFAULT 1, agility INT NOT NULL DEFAULT 1, luck INT NOT NULL DEFAULT 1, state TEXT NOT NULL DEFAULT 'walking' CHECK (state IN ('walking', 'fighting', 'dead')), weapon_id BIGINT REFERENCES weapons(id), armor_id BIGINT REFERENCES armor(id), gold BIGINT NOT NULL DEFAULT 0, xp BIGINT NOT NULL DEFAULT 0, level INT NOT NULL DEFAULT 1, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_heroes_telegram_id ON heroes(telegram_id); CREATE TABLE IF NOT EXISTS enemies ( id BIGSERIAL PRIMARY KEY, type TEXT NOT NULL, name TEXT NOT NULL, hp INT NOT NULL, max_hp INT NOT NULL, attack INT NOT NULL, defense INT NOT NULL, speed DOUBLE PRECISION NOT NULL DEFAULT 1.0, crit_chance DOUBLE PRECISION NOT NULL DEFAULT 0.0, min_level INT NOT NULL DEFAULT 1, max_level INT NOT NULL DEFAULT 100, xp_reward BIGINT NOT NULL DEFAULT 0, gold_reward BIGINT NOT NULL DEFAULT 0, special_abilities TEXT[] NOT NULL DEFAULT '{}', is_elite BOOLEAN NOT NULL DEFAULT false, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS buffs ( id BIGSERIAL PRIMARY KEY, type TEXT NOT NULL CHECK (type IN ('rush', 'rage', 'shield', 'luck', 'resurrection', 'heal', 'power_potion', 'war_cry')), name TEXT NOT NULL, duration_ms BIGINT NOT NULL, magnitude DOUBLE PRECISION NOT NULL DEFAULT 0.0, cooldown_ms BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS debuffs ( id BIGSERIAL PRIMARY KEY, type TEXT NOT NULL CHECK (type IN ('poison', 'freeze', 'burn', 'stun', 'slow', 'weaken')), name TEXT NOT NULL, duration_ms BIGINT NOT NULL, magnitude DOUBLE PRECISION NOT NULL DEFAULT 0.0, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS hero_active_buffs ( id BIGSERIAL PRIMARY KEY, hero_id BIGINT NOT NULL REFERENCES heroes(id) ON DELETE CASCADE, buff_id BIGINT NOT NULL REFERENCES buffs(id), applied_at TIMESTAMPTZ NOT NULL DEFAULT now(), expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_hero_active_buffs_hero ON hero_active_buffs(hero_id); CREATE INDEX IF NOT EXISTS idx_hero_active_buffs_expires ON hero_active_buffs(expires_at); CREATE TABLE IF NOT EXISTS hero_active_debuffs ( id BIGSERIAL PRIMARY KEY, hero_id BIGINT NOT NULL REFERENCES heroes(id) ON DELETE CASCADE, debuff_id BIGINT NOT NULL REFERENCES debuffs(id), applied_at TIMESTAMPTZ NOT NULL DEFAULT now(), expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_hero_active_debuffs_hero ON hero_active_debuffs(hero_id); CREATE INDEX IF NOT EXISTS idx_hero_active_debuffs_expires ON hero_active_debuffs(expires_at); CREATE TABLE IF NOT EXISTS loot_history ( id BIGSERIAL PRIMARY KEY, hero_id BIGINT NOT NULL REFERENCES heroes(id) ON DELETE CASCADE, enemy_type TEXT NOT NULL, item_type TEXT NOT NULL, item_id BIGINT, rarity TEXT NOT NULL CHECK (rarity IN ('common', 'uncommon', 'rare', 'epic', 'legendary')), gold_amount BIGINT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_loot_history_hero ON loot_history(hero_id); -- Seed all 15 weapons INSERT INTO weapons (name, type, rarity, damage, speed, crit_chance, special_effect) VALUES -- Daggers ('Rusty Dagger', 'daggers', 'common', 3, 1.3, 0.05, ''), ('Iron Dagger', 'daggers', 'uncommon', 5, 1.3, 0.08, ''), ('Assassin''s Blade', 'daggers', 'rare', 8, 1.35, 0.20, ''), ('Phantom Edge', 'daggers', 'epic', 12, 1.4, 0.25, ''), ('Fang of the Void', 'daggers', 'legendary', 18, 1.5, 0.30, ''), -- Swords ('Iron Sword', 'sword', 'common', 7, 1.0, 0.03, ''), ('Steel Sword', 'sword', 'uncommon', 10, 1.0, 0.05, ''), ('Longsword', 'sword', 'rare', 15, 1.0, 0.08, ''), ('Excalibur', 'sword', 'epic', 22, 1.05, 0.10, ''), ('Soul Reaver', 'sword', 'legendary', 30, 1.1, 0.12, 'lifesteal'), -- Axes ('Rusty Axe', 'axe', 'common', 12, 0.7, 0.02, ''), ('Battle Axe', 'axe', 'uncommon', 18, 0.7, 0.04, ''), ('War Axe', 'axe', 'rare', 25, 0.75, 0.06, ''), ('Infernal Axe', 'axe', 'epic', 35, 0.75, 0.08, ''), ('Godslayer''s Edge', 'axe', 'legendary', 50, 0.8, 0.10, 'splash'); -- Seed all 15 armor pieces INSERT INTO armor (name, type, rarity, defense, speed_modifier, agility_bonus, set_name, special_effect) VALUES -- Light ('Leather Armor', 'light', 'common', 3, 1.05, 3, '', ''), ('Ranger''s Vest', 'light', 'uncommon', 5, 1.08, 5, '', ''), ('Shadow Cloak', 'light', 'rare', 8, 1.10, 8, 'Assassin''s Set', 'crit_bonus'), ('Phantom Garb', 'light', 'epic', 12, 1.12, 12, 'Assassin''s Set', 'crit_bonus'), ('Whisper of the Void', 'light', 'legendary', 16, 1.15, 18, 'Assassin''s Set', 'crit_bonus'), -- Medium ('Chainmail', 'medium', 'common', 7, 1.0, 0, '', ''), ('Reinforced Mail', 'medium', 'uncommon', 10, 1.0, 0, '', ''), ('Battle Armor', 'medium', 'rare', 15, 1.0, 0, 'Knight''s Set', 'def_bonus'), ('Royal Guard', 'medium', 'epic', 22, 1.0, 0, 'Knight''s Set', 'def_bonus'), ('Crown of Eternity', 'medium', 'legendary', 30, 1.0, 0, 'Knight''s Set', 'def_bonus'), -- Heavy ('Iron Plate', 'heavy', 'common', 14, 0.7, -3, '', ''), ('Steel Plate', 'heavy', 'uncommon', 20, 0.7, -3, '', ''), ('Fortress Armor', 'heavy', 'rare', 28, 0.7, -5, 'Berserker''s Set','atk_bonus'), ('Dragon Scale', 'heavy', 'epic', 38, 0.7, -5, 'Berserker''s Set','atk_bonus'), ('Dragon Slayer', 'heavy', 'legendary', 50, 0.7, -5, 'Berserker''s Set','atk_bonus'); -- Seed all 8 buff definitions INSERT INTO buffs (type, name, duration_ms, magnitude, cooldown_ms) VALUES ('rush', 'Rush', 10000, 0.5, 30000), ('rage', 'Rage', 8000, 1.0, 45000), ('shield', 'Shield', 12000, 0.5, 40000), ('luck', 'Luck', 15000, 1.5, 60000), ('resurrection', 'Resurrection', 20000, 0.5, 120000), ('heal', 'Heal', 1000, 0.5, 60000), ('power_potion', 'Power Potion', 10000, 1.5, 90000), ('war_cry', 'War Cry', 8000, 1.0, 60000); -- Seed all 6 debuff definitions INSERT INTO debuffs (type, name, duration_ms, magnitude) VALUES ('poison', 'Poison', 5000, 0.02), ('freeze', 'Freeze', 3000, 0.50), ('burn', 'Burn', 4000, 0.03), ('stun', 'Stun', 2000, 1.00), ('slow', 'Slow', 4000, 0.40), ('weaken', 'Weaken', 5000, 0.30); -- Seed all 13 enemy templates INSERT INTO enemies (type, name, hp, max_hp, attack, defense, speed, crit_chance, min_level, max_level, xp_reward, gold_reward, special_abilities, is_elite) VALUES -- Basic enemies ('wolf', 'Forest Wolf', 30, 30, 8, 2, 1.8, 0.05, 1, 5, 10, 5, '{}', false), ('boar', 'Wild Boar', 50, 50, 15, 5, 0.8, 0.08, 2, 6, 15, 8, '{}', false), ('zombie', 'Rotting Zombie', 80, 80, 6, 3, 0.5, 0.00, 3, 8, 12, 6, '{poison}', false), ('spider', 'Cave Spider', 25, 25, 10, 1, 2.0, 0.15, 4, 9, 12, 7, '{critical}', false), ('orc', 'Orc Warrior', 60, 60, 12, 8, 1.0, 0.05, 5, 12, 20, 12, '{}', false), ('skeleton_archer', 'Skeleton Archer', 45, 45, 11, 4, 1.3, 0.06, 6, 14, 18, 10, '{dodge}', false), ('battle_lizard', 'Battle Lizard', 90, 90, 9, 12, 0.7, 0.03, 7, 15, 22, 14, '{regen}', false), -- Elite enemies ('fire_demon', 'Fire Demon', 100, 100, 20, 10, 1.2, 0.10, 10, 20, 50, 30, '{burn}', true), ('ice_guardian', 'Ice Guardian', 120, 120, 14, 15, 0.7, 0.04, 12, 22, 50, 30, '{freeze}', true), ('skeleton_king', 'Skeleton King', 200, 200, 18, 12, 0.9, 0.08, 15, 25, 80, 50, '{regen,summon}', true), ('water_element', 'Water Element', 250, 250, 16, 10, 0.8, 0.05, 18, 28, 100, 60, '{slow}', true), ('forest_warden', 'Forest Warden', 400, 400, 14, 25, 0.5, 0.03, 20, 30, 120, 80, '{regen}', true), ('lightning_titan', 'Lightning Titan', 350, 350, 30, 15, 1.5, 0.12, 25, 35, 200, 120, '{stun,chain_lightning}', true);