CREATE DATABASE IF NOT EXISTS champions_rpg;
USE champions_rpg;

-- Champions table
CREATE TABLE champions (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    title VARCHAR(100),
    role VARCHAR(100),
    tagline VARCHAR(200),
    description TEXT,
    theme_class VARCHAR(50),
    color VARCHAR(20),
    emoji VARCHAR(10),
    day_index INT UNIQUE
);

INSERT INTO champions VALUES 
(1, 'Zealer', 'Radiant Vindicator', 'Tank / Support', 'Smite the wicked with unwavering faith.', 'A devout warrior clad in coated armor, channeling divine light to protect allies and smite evil.', 'theme-zealer', '#ffd700', '🛡️', 0),
(2, 'Rogue', 'Storm Huntress', 'Ranged DPS / Mobility', 'The storm is my weapon.', 'A descendant of the Amazonian bloodline, riding into battle with bow drawn and lightning crackling.', 'theme-rogue', '#00bfff', '⚡', 1),
(3, 'Willow', 'Arcflare Magus', 'AoE Mage / Control', 'Anybody need a lighter?', 'A master of opposing forces, conjuring blazing meteors and icy beasts.', 'theme-willow', '#ff4500', '🔥', 2),
(4, 'Gaia', 'Wildform Sage', 'Hybrid / Leader', 'Looting and polluting is not the way.', 'Abandoned by her WitchDoctor father, forming sacred bonds with nature creatures.', 'theme-gaia', '#228b22', '🐾', 3),
(5, 'Necro', 'Bonecaller Adept', 'Summoner / Debuff', 'I\'m 6ft from the edge and I\'m thinking.', 'A skeletal sorcerer commanding an army of zombies and the Four Horsemen.', 'theme-necro', '#8b0000', '💀', 4),
(6, 'Berserker', 'Marauder', 'Melee DPS / Bruiser', 'Whoa, mama! Man, I\'m pretty!', 'A godlike brute wielding an ogre mauler, embodying raw power and ancestral pride.', 'theme-berserker', '#ff4500', '💪', 5),
(7, 'Shadow', 'Assassin of the Veil', 'Melee DPS / Stealth', 'From the darkness I strike.', 'A disciplined killer trained in forbidden arts, moving like a whisper through the dark.', 'theme-shadow', '#9400d3', '🥷', 6);

-- Habits/Quests table
CREATE TABLE habits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    time_of_day ENUM('morning', 'midday', 'evening'),
    frequency ENUM('daily', 'weekly', 'monthly', 'once'),
    xp_reward INT DEFAULT 25,
    show_on_calendar BOOLEAN DEFAULT FALSE,
    image_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Items table
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    subtitle VARCHAR(100),
    description TEXT,
    rarity ENUM('common', 'uncommon', 'rare', 'epic', 'legendary'),
    stat_effect VARCHAR(200),
    quantity INT DEFAULT 1,
    image_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Bounties table
CREATE TABLE bounties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    xp_reward INT,
    completed BOOLEAN DEFAULT FALSE,
    completed_date DATE,
    image_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Champion progress table
CREATE TABLE champion_progress (
    champion_id INT,
    level INT DEFAULT 1,
    xp INT DEFAULT 0,
    FOREIGN KEY (champion_id) REFERENCES champions(id),
    PRIMARY KEY (champion_id)
);

INSERT INTO champion_progress (champion_id) SELECT id FROM champions;

-- Completion tracking
CREATE TABLE completions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    habit_id INT,
    completed_date DATE,
    champion_day INT,
    FOREIGN KEY (habit_id) REFERENCES habits(id)
);
