CREATE TABLE unx511.avatars (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(40) NOT NULL,
    level tinyint UNSIGNED NOT NULL DEFAULT 1,
    lvl_points INT UNSIGNED DEFAULT 0,
    health INT UNSIGNED NOT NULL DEFAULT 100,
    HP INT UNSIGNED NOT NULL DEFAULT 1,
    cash DECIMAL DEFAULT 0,
    banked DOUBLE DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE = InnoDB;


CREATE TABLE unx511.avatar_attack (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attacker_id INT UNSIGNED NOT NULL,
    victim_id INT UNSIGNED NOT NULL,
    winner INT NOT NULL,
    attack_date datetime NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (attacker_id) REFERENCES avatars (id),
    FOREIGN KEY (victim_id) REFERENCES avatars (id)
)  ENGINE = InnoDB;


CREATE TABLE unx511.hitlist (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    placed_hit INT UNSIGNED NOT NULL,
    person_to_beat INT UNSIGNED NOT NULL,
    amount_paid DECIMAL NOT NULL,
    paid_date datetime NOT NULL,
    active ENUM('Y', 'N') NOT NULL,
    beat_by INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (placed_hit) REFERENCES avatars (id),
    FOREIGN KEY (person_to_beat) REFERENCES avatars (id)
)  ENGINE = InnoDB;


CREATE TABLE unx511.missions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(256) NOT NULL,
    description VARCHAR(1024),
    pay_amount DECIMAL NOT NULL,
    score_to_beat INT NOT NULL,
    attack_points INT NOT NULL,
    defence_points INT NOT NULL,
    EXP FLOAT NOT NULL,
    loot DECIMAL NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;


CREATE TABLE unx511.item (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(256) NOT NULL,
    attack_points INT UNSIGNED NULL,
    defence_points INT UNSIGNED NULL,
    cost DECIMAL UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;


CREATE TABLE unx511.avatar_item (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    avatar_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    amount INT NOT NULL DEFAULT 0,  # how many OF items exist IN USER inventory
    amount_paid DECIMAL NOT NULL,   # IS this necessary? since each item has a cost associated WITH it
    # DATE    -- if there are multiple items how does date work?
    PRIMARY KEY (id),
    FOREIGN KEY (avatar_id) REFERENCES avatars (id),
    FOREIGN KEY (item_id) REFERENCES item (id)
) ENGINE = InnoDB;


CREATE TABLE unx511.avatar_mission (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    avatar_id INT UNSIGNED NOT NULL,
    mission_id INT UNSIGNED NOT NULL,
    mission_date datetime NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (avatar_id) REFERENCES avatars (id),
    FOREIGN KEY (mission_id) REFERENCES missions (id)
) ENGINE = InnoDB;