DROP TABLE `Customer`, `OrderContents`, `Orders`, `Product`;


-- 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 mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema azziedev_ronnieshared
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema azziedev_ronnieshared
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `azziedev_ronnieshared` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `azziedev_ronnieshared` ;

-- -----------------------------------------------------
-- Table `azziedev_ronnieshared`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_ronnieshared`.`Customer` (
  `CustID` INT(11) NOT NULL,
  `FName` VARCHAR(45) NOT NULL,
  `LName` VARCHAR(45) NOT NULL,
  `Email` VARCHAR(45) NOT NULL,
  `Phone` INT(10) NOT NULL,
  `Password` VARCHAR(45) NOT NULL,
  `Street` VARCHAR(45) NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `State` VARCHAR(45) NOT NULL,
  `ZIP` INT(5) NOT NULL,
  PRIMARY KEY (`CustID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_ronnieshared`.`Orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_ronnieshared`.`Orders` (
  `OrderID` INT(11) NOT NULL,
  `CustID` INT(11) NOT NULL,
  `Payment` VARCHAR(45) NOT NULL,
  `Cost` DECIMAL(6,2) NOT NULL,
  `Gift` VARCHAR(45) NOT NULL,
  `Promos` VARCHAR(45) NOT NULL,
  `D_Method` VARCHAR(45) NOT NULL,
  `Street` VARCHAR(45) NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `State` VARCHAR(45) NOT NULL,
  `ZIP` INT(5) NOT NULL,
  `Status` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`OrderID`, `CustID`),
  INDEX `CustID_idx` (`CustID` ASC),
  CONSTRAINT `CustID`
    FOREIGN KEY (`CustID`)
    REFERENCES `azziedev_ronnieshared`.`Customer` (`CustID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_ronnieshared`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_ronnieshared`.`Product` (
  `SKU` INT(11) NOT NULL,
  `PName` VARCHAR(45) NOT NULL,
  `Attributes` VARCHAR(45) NOT NULL,
  `Inventory` VARCHAR(45) NOT NULL,
  `Price` DECIMAL(6,2) NOT NULL,
  PRIMARY KEY (`SKU`, `Attributes`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_ronnieshared`.`OrderContents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_ronnieshared`.`OrderContents` (
  `OrderID` INT(11) NOT NULL,
  `SKU` INT(11) NOT NULL,
  `Attributes` VARCHAR(45) NOT NULL,
  `Quantity` INT(11) NOT NULL,
  PRIMARY KEY (`OrderID`, `SKU`, `Attributes`),
  INDEX `SKU_idx` (`SKU` ASC),
  CONSTRAINT `OrderID`
    FOREIGN KEY (`OrderID`)
    REFERENCES `azziedev_ronnieshared`.`Orders` (`OrderID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `SKU`
    FOREIGN KEY (`SKU`)
    REFERENCES `azziedev_ronnieshared`.`Product` (`SKU`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


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