-- Coin shop (Phase 1): unlockable avatar cosmetics.
-- Manual-apply — see CLAUDE.md "Database" section (no migration framework).

CREATE TABLE shop_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    icon VARCHAR(16) NOT NULL,          -- emoji, rendered directly (no image upload)
    description VARCHAR(500) NULL,
    cost_coins INT NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE hero_unlocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    shop_item_id INT NOT NULL,
    unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_student_item (student_id, shop_item_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (shop_item_id) REFERENCES shop_items(id)
);

-- seed catalog so the feature is usable without an admin UI
INSERT INTO shop_items (name, icon, cost_coins) VALUES
    ('Космонавт', '🧑‍🚀', 50),
    ('Дракон', '🐉', 80),
    ('Единорог', '🦄', 80),
    ('Ниндзя', '🥷', 60),
    ('Волшебник', '🧙', 70),
    ('Робот', '🤖', 60),
    ('Феникс', '🔥', 100),
    ('Король', '👑', 120);
