CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE characters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(60) NOT NULL,
    race VARCHAR(30),
    class VARCHAR(30),
    level INT DEFAULT 1,
    xp INT DEFAULT 0,
    gold INT DEFAULT 50,
    hp INT DEFAULT 20,
    hp_max INT DEFAULT 20,
    mode ENUM('normal','hardcore') DEFAULT 'normal',
    str INT DEFAULT 10,
    dex INT DEFAULT 10,
    con INT DEFAULT 10,
    int_stat INT DEFAULT 10,
    wis INT DEFAULT 10,
    cha INT DEFAULT 10,
    inspiration INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE quests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    character_id INT,
    title VARCHAR(120) NOT NULL,
    description TEXT,
    type ENUM('daily','weekly','epic') NOT NULL,
    xp_reward INT NOT NULL,
    gold_reward INT NOT NULL,
    stat_bonus VARCHAR(3) DEFAULT 'DEX',
    is_active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE quest_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quest_id INT,
    character_id INT,
    rolled_d20 INT,
    total_xp INT,
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quest_id) REFERENCES quests(id)
);