-- 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 ESS_Trading
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema ESS_Trading
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ESS_Trading` ;
USE `ESS_Trading` ;

-- -----------------------------------------------------
-- Table `ESS_Trading`.`Portfolio`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Portfolio` (
  `idPortfolio` INT NOT NULL,
  PRIMARY KEY (`idPortfolio`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ESS_Trading`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`User` (
  `idUser` INT NOT NULL,
  `Username` VARCHAR(45) NOT NULL,
  `Email` VARCHAR(255) NOT NULL,
  `Password` VARCHAR(255) NOT NULL,
  `isAdmin` INT NOT NULL,
  `Credit` DOUBLE NOT NULL,
  `Portfolio_idPortfolio` INT NOT NULL,
  PRIMARY KEY (`idUser`, `Portfolio_idPortfolio`),
  INDEX `fk_User_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
  CONSTRAINT `fk_User_Portfolio1`
    FOREIGN KEY (`Portfolio_idPortfolio`)
    REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ESS_Trading`.`Asset`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Asset` (
  `idAsset` INT NOT NULL,
  `Company` VARCHAR(45) NOT NULL,
  `Type` ENUM('coin', 'stock', 'commodity') NOT NULL,
  `Value` DOUBLE NOT NULL,
  PRIMARY KEY (`idAsset`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ESS_Trading`.`CFD`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`CFD` (
  `idCFD` INT NOT NULL,
  `AquisitionPrice` DOUBLE NOT NULL,
  `Position` ENUM('short', 'long') NOT NULL,
  `TakeProfit` DOUBLE NULL,
  `StopLoss` DOUBLE NULL,
  `Date` VARCHAR(45) NOT NULL,
  `Quantity` DOUBLE NOT NULL,
  `Portfolio_idPortfolio` INT NOT NULL,
  `Asset_idAsset` INT NOT NULL,
  PRIMARY KEY (`idCFD`, `Portfolio_idPortfolio`, `Asset_idAsset`),
  INDEX `fk_CFD_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
  INDEX `fk_CFD_Asset1_idx` (`Asset_idAsset` ASC) VISIBLE,
  CONSTRAINT `fk_CFD_Portfolio1`
    FOREIGN KEY (`Portfolio_idPortfolio`)
    REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CFD_Asset1`
    FOREIGN KEY (`Asset_idAsset`)
    REFERENCES `ESS_Trading`.`Asset` (`idAsset`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ESS_Trading`.`Bug`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Bug` (
  `idBug` INT NOT NULL,
  `Error` VARCHAR(255) NOT NULL,
  `Date` VARCHAR(45) NOT NULL,
  `User_idUser` INT NOT NULL,
  PRIMARY KEY (`idBug`, `User_idUser`),
  INDEX `fk_Bug_User1_idx` (`User_idUser` ASC) VISIBLE,
  CONSTRAINT `fk_Bug_User1`
    FOREIGN KEY (`User_idUser`)
    REFERENCES `ESS_Trading`.`User` (`idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ESS_Trading`.`Portfolio_has_Asset`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Portfolio_has_Asset` (
  `Portfolio_idPortfolio` INT NOT NULL,
  `Asset_idAsset` INT NOT NULL,
  PRIMARY KEY (`Portfolio_idPortfolio`, `Asset_idAsset`),
  INDEX `fk_Portfolio_has_Asset_Asset1_idx` (`Asset_idAsset` ASC) VISIBLE,
  INDEX `fk_Portfolio_has_Asset_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
  CONSTRAINT `fk_Portfolio_has_Asset_Portfolio1`
    FOREIGN KEY (`Portfolio_idPortfolio`)
    REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Portfolio_has_Asset_Asset1`
    FOREIGN KEY (`Asset_idAsset`)
    REFERENCES `ESS_Trading`.`Asset` (`idAsset`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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