-- MySQL Script generated by MySQL Workbench
-- Mon May 10 21:56:56 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema helldrop
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema helldrop
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `helldrop` DEFAULT CHARACTER SET utf8 ;
USE `helldrop` ;

-- -----------------------------------------------------
-- Table `helldrop`.`item_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_types` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_classes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_classes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `image` VARCHAR(45) NOT NULL,
  `item_type_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `item_class_item_type_id_foreign_idx` (`item_type_id` ASC) VISIBLE,
  CONSTRAINT `item_class_item_type_id_foreign`
    FOREIGN KEY (`item_type_id`)
    REFERENCES `helldrop`.`item_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`items` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `image` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_rarities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_rarities` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_exteriors`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_exteriors` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_features`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_features` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`collections`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`collections` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_details` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `item_id` INT UNSIGNED NOT NULL,
  `item_rarity_id` INT UNSIGNED NOT NULL,
  `item_exterior_id` INT UNSIGNED NOT NULL,
  `item_class_id` INT UNSIGNED NOT NULL,
  `item_feature_id` INT UNSIGNED NOT NULL,
  `collection_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `item_details_item_id_foreign_idx` (`item_id` ASC) VISIBLE,
  INDEX `item_details_item_rarity_id_foreign_idx` (`item_rarity_id` ASC) VISIBLE,
  INDEX `item_details_item_exterior_id_foreign_idx` (`item_exterior_id` ASC) VISIBLE,
  INDEX `item_details_item_class_id_foreigh_idx` (`item_class_id` ASC) VISIBLE,
  INDEX `item_details_item_feature_id_foreign_idx` (`item_feature_id` ASC) VISIBLE,
  INDEX `item_details_collection_id_idx` (`collection_id` ASC) VISIBLE,
  CONSTRAINT `item_details_item_id_foreign`
    FOREIGN KEY (`item_id`)
    REFERENCES `helldrop`.`items` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `item_details_item_rarity_id_foreign`
    FOREIGN KEY (`item_rarity_id`)
    REFERENCES `helldrop`.`item_rarities` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_details_item_exterior_id_foreign`
    FOREIGN KEY (`item_exterior_id`)
    REFERENCES `helldrop`.`item_exteriors` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_details_item_class_id_foreigh`
    FOREIGN KEY (`item_class_id`)
    REFERENCES `helldrop`.`item_classes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_details_item_feature_id_foreign`
    FOREIGN KEY (`item_feature_id`)
    REFERENCES `helldrop`.`item_features` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_details_collection_id`
    FOREIGN KEY (`collection_id`)
    REFERENCES `helldrop`.`collections` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`cases`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`cases` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`case_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`case_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` INT UNSIGNED NOT NULL,
  `item_id` INT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `case_items_case_id_foreign_idx` (`case_id` ASC) VISIBLE,
  INDEX `case_items_item_id_foreign_idx` (`item_id` ASC) VISIBLE,
  CONSTRAINT `case_items_case_id_foreign`
    FOREIGN KEY (`case_id`)
    REFERENCES `helldrop`.`cases` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `case_items_item_id_foreign`
    FOREIGN KEY (`item_id`)
    REFERENCES `helldrop`.`items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`item_prices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`item_prices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `item_id` INT UNSIGNED NOT NULL,
  `item_exterior_id` INT UNSIGNED NOT NULL,
  `item_feature_id` INT UNSIGNED NOT NULL,
  `price` DOUBLE(14,2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `item_prices_item_id_foreign_idx` (`item_id` ASC) VISIBLE,
  INDEX `item_prices_item_exterior_id_foreign_idx` (`item_exterior_id` ASC) VISIBLE,
  INDEX `item_prices_item_feature_id_freign_idx` (`item_feature_id` ASC) VISIBLE,
  CONSTRAINT `item_prices_item_id_foreign`
    FOREIGN KEY (`item_id`)
    REFERENCES `helldrop`.`items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_prices_item_exterior_id_foreign`
    FOREIGN KEY (`item_exterior_id`)
    REFERENCES `helldrop`.`item_exteriors` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `item_prices_item_feature_id_freign`
    FOREIGN KEY (`item_feature_id`)
    REFERENCES `helldrop`.`item_features` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`case_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`case_details` (
  `id` INT NOT NULL,
  `case_id` INT UNSIGNED NOT NULL,
  `item_count` INT NOT NULL,
  `total_item_count` INT UNSIGNED NOT NULL,
  `price` DOUBLE(14,2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `case_details_case_id_foreign_idx` (`case_id` ASC) VISIBLE,
  CONSTRAINT `case_details_case_id_foreign`
    FOREIGN KEY (`case_id`)
    REFERENCES `helldrop`.`cases` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `helldrop`.`admins`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `helldrop`.`admins` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `avatar` VARCHAR(45) NOT NULL,
  `steam_id` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;