-- Unified gear table replacing weapons, armor, and equipment_items
CREATE TABLE IF NOT EXISTS gear (
    id              BIGSERIAL PRIMARY KEY,
    slot            TEXT NOT NULL,
    form_id         TEXT NOT NULL DEFAULT '',
    name            TEXT NOT NULL,
    subtype         TEXT NOT NULL DEFAULT '',
    rarity          TEXT NOT NULL DEFAULT 'common',
    ilvl            INT NOT NULL DEFAULT 1,
    base_primary    INT NOT NULL DEFAULT 0,
    primary_stat    INT NOT NULL DEFAULT 0,
    stat_type       TEXT NOT NULL DEFAULT 'mixed',
    speed_modifier  DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    crit_chance     DOUBLE PRECISION NOT NULL DEFAULT 0.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()
);

-- Hero gear: one row per equipped slot (replaces weapon_id, armor_id, and hero_equipment)
CREATE TABLE IF NOT EXISTS hero_gear (
    hero_id  BIGINT NOT NULL REFERENCES heroes(id) ON DELETE CASCADE,
    slot     TEXT NOT NULL,
    gear_id  BIGINT NOT NULL REFERENCES gear(id),
    PRIMARY KEY (hero_id, slot)
);
CREATE INDEX IF NOT EXISTS idx_hero_gear_hero ON hero_gear(hero_id);

-- Migrate existing weapon data to gear table (safe to re-run if migration retried)
INSERT INTO gear (id, slot, name, subtype, rarity, ilvl, base_primary, primary_stat, stat_type, speed_modifier, crit_chance, special_effect)
SELECT id, 'main_hand', name, type, rarity, ilvl, damage, damage, 'attack', speed, crit_chance, special_effect
FROM weapons
ON CONFLICT (id) DO NOTHING;

-- Migrate existing armor data to gear table (offset IDs by 1000 to avoid conflicts)
INSERT INTO gear (id, slot, name, subtype, rarity, ilvl, base_primary, primary_stat, stat_type, speed_modifier, agility_bonus, set_name, special_effect)
SELECT id + 1000, 'chest', name, type, rarity, ilvl, defense, defense, 'defense', speed_modifier, agility_bonus, set_name, special_effect
FROM armor
ON CONFLICT (id) DO NOTHING;

-- Migrate equipment_items to gear (offset by 2000)
INSERT INTO gear (id, slot, form_id, name, rarity, ilvl, base_primary, primary_stat, stat_type)
SELECT id + 2000, slot, form_id, name, rarity, ilvl, base_primary, primary_stat, stat_type
FROM equipment_items
ON CONFLICT (id) DO NOTHING;

-- Migrate hero weapon/armor refs to hero_gear
INSERT INTO hero_gear (hero_id, slot, gear_id)
SELECT id, 'main_hand', weapon_id FROM heroes WHERE weapon_id IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO hero_gear (hero_id, slot, gear_id)
SELECT id, 'chest', armor_id + 1000 FROM heroes WHERE armor_id IS NOT NULL
ON CONFLICT DO NOTHING;

-- Migrate hero_equipment to hero_gear
INSERT INTO hero_gear (hero_id, slot, gear_id)
SELECT hero_id, slot, item_id + 2000 FROM hero_equipment
ON CONFLICT DO NOTHING;

-- Reset gear sequence to avoid ID conflicts
SELECT setval('gear_id_seq', (SELECT COALESCE(MAX(id), 0) + 1 FROM gear));