CREATE DATABASE clash;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    exp INT,
    level INT,
    username VARCHAR(16),
    password VARCHAR(64),
    townhall_level INT(2),
    lab_level INT(2),
    PRIMARY KEY(user_id)
);

CREATE TABLE buildings (
    building_id INT AUTO_INCREMENT,
    display_name VARCHAR(24),
    size int(1),
    PRIMAY KEY(building_id)
);

CREATE TABLE buildings_levels (
    building_level_id INT AUTO_INCREMENT,
    building_id INT,
    level INT(3),
    cost INT(8),
    townhall_level int(2),
    PRIMARY KEY(building_level_id),
    FOREIGN KEY(building_id) REFERENCES buildings(building_id)
    ON DELETE CASCADE;
);

CREATE TABLE users_buildings (
    user_building_id INT AUTO_INCREMENT,
    building_id INT,
    user_id INT,
    pos_x INT(3),
    pos_y INT(3),
    level INT(2),
    PRIMARY KEY(user_building_id),
    FOREIGN KEY(building_id) REFERENCES buildings(building_id)
    ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES users(user_id)
    ON DELETE CASCADE
);

CREATE TABLE spells (
    spell_id INT AUTO_INCREMENT,
    display_name VARCHAR(24),
    space INT(1),
    PRIMARY KEY(spell_id)
);

CREATE TABLE spells_levels (
    spell_level_id INT AUTO_INCREMENT,
    spell_id INT,
    level INT(2),
    cost INT(8),
    lab_level INT(2),
    PRIMARY KEY(spell_level_id),
    FOREIGN KEY (spell_id) REFERENCES spells(spell_id)
    ON DELETE CASCADE;
);

CREATE TABLE user_spells (
    user_spell_id INT AUTO_INCREMENT,
    user_id INT,
    spell_id INT,
    level INT(2),
    PRIMARY KEY(user_spell_id),
    FOREIGN KEY(spell_id) REFERENCES spells(spell_id)
    ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES users(user_id)
    ON DELETE CASCADE
);

CREATE TABLE troop (
    troop_id INT AUTO_INCREMENT,
    display_name VARCHAR(24),
    space INT(2),
    PRIMARY KEY(troop_id)
);

CREATE TABLE troops_levels (
    troop_level_id INT AUTO_INCREMENT,
    troop_id INT,
    barrack_level INT(2),
    level INT(2),
    cost INT(8),
    PRIMARY KEY(troop_level_id),
    FOREIGN KEY(troop_id) REFERENCES troops(troop_id)
    ON DELETE CASCADE
);