-- 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';


-- -----------------------------------------------------
-- Table `PERSON`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `PERSON` ;

CREATE TABLE IF NOT EXISTS `PERSON` (
  `PersonID` INT NOT NULL,
  `TEAM_TeamName` VARCHAR(45) NOT NULL,
  `PersonType` INT NOT NULL,
  PRIMARY KEY (`PersonID`),
  INDEX `fk_PERSON_TEAM1_idx` (`TEAM_TeamName` ASC) VISIBLE,
  CONSTRAINT `fk_PERSON_TEAM1`
    FOREIGN KEY (`TEAM_TeamName`)
    REFERENCES `TEAM` (`TeamName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TEAM`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `TEAM` ;

CREATE TABLE IF NOT EXISTS `TEAM` (
  `TeamName` VARCHAR(45) NOT NULL,
  `City` VARCHAR(45) NULL,
  `Street` VARCHAR(45) NULL,
  `State` VARCHAR(45) NULL,
  `Zip` VARCHAR(45) NULL,
  `Division` VARCHAR(45) NULL,
  `PERSON_ManagerID` INT NULL,
  PRIMARY KEY (`TeamName`),
  INDEX `fk_TEAM_PERSON1_idx` (`PERSON_ManagerID` ASC) VISIBLE,
  CONSTRAINT `fk_TEAM_PERSON1`
    FOREIGN KEY (`PERSON_ManagerID`)
    REFERENCES `PERSON` (`PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `GAME`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `GAME` ;

CREATE TABLE IF NOT EXISTS `GAME` (
  `VisitorTeam` VARCHAR(45) NOT NULL,
  `HomeTeam` VARCHAR(45) NOT NULL,
  `GameDateTime` DATETIME NOT NULL,
  `VisitorGoals` INT NULL,
  `HomeGoals` INT NULL,
  PRIMARY KEY (`VisitorTeam`, `HomeTeam`, `GameDateTime`),
  INDEX `fk_GAME_TEAM1_idx` (`VisitorTeam` ASC) VISIBLE,
  CONSTRAINT `fk_GAME_TEAM`
    FOREIGN KEY (`HomeTeam`)
    REFERENCES `TEAM` (`TeamName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GAME_TEAM1`
    FOREIGN KEY (`VisitorTeam`)
    REFERENCES `TEAM` (`TeamName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `PLAYER`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `PLAYER` ;

CREATE TABLE IF NOT EXISTS `PLAYER` (
  `PERSON_PersonID` INT NOT NULL,
  `TEAM_TeamName` VARCHAR(45) NOT NULL,
  `Position` VARCHAR(45) NULL,
  `Footed` VARCHAR(45) NULL,
  `Weight` VARCHAR(45) NULL,
  `Height` INT NULL,
  PRIMARY KEY (`PERSON_PersonID`),
  INDEX `fk_PLAYER_TEAM1_idx` (`TEAM_TeamName` ASC) VISIBLE,
  CONSTRAINT `fk_PLAYER_PERSON1`
    FOREIGN KEY (`PERSON_PersonID`)
    REFERENCES `PERSON` (`PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PLAYER_TEAM1`
    FOREIGN KEY (`TEAM_TeamName`)
    REFERENCES `TEAM` (`TeamName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `PLAYER_GAME_STATS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `PLAYER_GAME_STATS` ;

CREATE TABLE IF NOT EXISTS `PLAYER_GAME_STATS` (
  `GAME_HomeTeam` VARCHAR(45) NOT NULL,
  `GAME_VisitorTeam` VARCHAR(45) NOT NULL,
  `GAME_GameDateTime` DATETIME NOT NULL,
  `PLAYER_PersonID` INT NOT NULL,
  `RedCards` INT NULL,
  `YellowCards` INT NULL,
  `Goals` VARCHAR(45) NULL,
  `Fouls` INT NULL,
  `Assists` INT NULL,
  `Minutes` INT NULL,
  PRIMARY KEY (`GAME_HomeTeam`, `GAME_VisitorTeam`, `GAME_GameDateTime`, `PLAYER_PersonID`),
  INDEX `fk_PLAYER_GAME_STATS_PLAYER1_idx` (`PLAYER_PersonID` ASC) VISIBLE,
  CONSTRAINT `fk_PLAYER_GAME_STATS_GAME1`
    FOREIGN KEY (`GAME_HomeTeam` , `GAME_VisitorTeam` , `GAME_GameDateTime`)
    REFERENCES `GAME` (`HomeTeam` , `VisitorTeam` , `GameDateTime`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PLAYER_GAME_STATS_PLAYER1`
    FOREIGN KEY (`PLAYER_PersonID`)
    REFERENCES `PLAYER` (`PERSON_PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `REFEREE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `REFEREE` ;

CREATE TABLE IF NOT EXISTS `REFEREE` (
  `PERSON_PersonID` INT NOT NULL,
  PRIMARY KEY (`PERSON_PersonID`),
  CONSTRAINT `fk_REFEREE_PERSON1`
    FOREIGN KEY (`PERSON_PersonID`)
    REFERENCES `PERSON` (`PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `GOALIE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `GOALIE` ;

CREATE TABLE IF NOT EXISTS `GOALIE` (
  `PLAYER_PersonID` INT NOT NULL,
  `OverallSavePercent` DECIMAL(3,2) NULL,
  PRIMARY KEY (`PLAYER_PersonID`),
  CONSTRAINT `fk_GOALIE_PLAYER1`
    FOREIGN KEY (`PLAYER_PersonID`)
    REFERENCES `PLAYER` (`PERSON_PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `REFEREE_OFFICIATES_GAME`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `REFEREE_OFFICIATES_GAME` ;

CREATE TABLE IF NOT EXISTS `REFEREE_OFFICIATES_GAME` (
  `REFEREE_PersonID` INT NOT NULL,
  `GAME_HomeTeam` VARCHAR(45) NOT NULL,
  `GAME_VisitorTeam` VARCHAR(45) NOT NULL,
  `GAME_GameDateTime` DATETIME NOT NULL,
  `RedCards` INT NULL,
  `YellowCards` VARCHAR(45) NULL,
  PRIMARY KEY (`REFEREE_PersonID`, `GAME_HomeTeam`, `GAME_VisitorTeam`, `GAME_GameDateTime`),
  INDEX `fk_REFEREE_has_GAME_GAME1_idx` (`GAME_HomeTeam` ASC, `GAME_VisitorTeam` ASC, `GAME_GameDateTime` ASC) VISIBLE,
  INDEX `fk_REFEREE_has_GAME_REFEREE1_idx` (`REFEREE_PersonID` ASC) VISIBLE,
  CONSTRAINT `fk_REFEREE_has_GAME_REFEREE1`
    FOREIGN KEY (`REFEREE_PersonID`)
    REFERENCES `REFEREE` (`PERSON_PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_REFEREE_has_GAME_GAME1`
    FOREIGN KEY (`GAME_HomeTeam` , `GAME_VisitorTeam` , `GAME_GameDateTime`)
    REFERENCES `GAME` (`HomeTeam` , `VisitorTeam` , `GameDateTime`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `PERSON_PHONE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `PERSON_PHONE` ;

CREATE TABLE IF NOT EXISTS `PERSON_PHONE` (
  `PERSON_PersonID` INT NOT NULL,
  `PhoneNumber` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`PERSON_PersonID`, `PhoneNumber`),
  CONSTRAINT `fk_PERSON_PHONE_PERSON1`
    FOREIGN KEY (`PERSON_PersonID`)
    REFERENCES `PERSON` (`PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `GOALIE_IN_GAME`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `GOALIE_IN_GAME` ;

CREATE TABLE IF NOT EXISTS `GOALIE_IN_GAME` (
  `GOALIE_PersonID` INT NOT NULL,
  `GAME_VisitorTeam` VARCHAR(45) NOT NULL,
  `GAME_HomeTeam` VARCHAR(45) NOT NULL,
  `GAME_GameDateTime` DATETIME NOT NULL,
  `ShotsAgainst` INT NULL,
  `GoalsAgainst` INT NULL,
  `SavePercent` DECIMAL(3,2) NULL,
  PRIMARY KEY (`GOALIE_PersonID`, `GAME_VisitorTeam`, `GAME_HomeTeam`, `GAME_GameDateTime`),
  INDEX `fk_GOALIE_has_GAME_GAME1_idx` (`GAME_VisitorTeam` ASC, `GAME_HomeTeam` ASC, `GAME_GameDateTime` ASC) VISIBLE,
  INDEX `fk_GOALIE_has_GAME_GOALIE1_idx` (`GOALIE_PersonID` ASC) VISIBLE,
  CONSTRAINT `fk_GOALIE_has_GAME_GOALIE1`
    FOREIGN KEY (`GOALIE_PersonID`)
    REFERENCES `GOALIE` (`PLAYER_PersonID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GOALIE_has_GAME_GAME1`
    FOREIGN KEY (`GAME_VisitorTeam` , `GAME_HomeTeam` , `GAME_GameDateTime`)
    REFERENCES `GAME` (`VisitorTeam` , `HomeTeam` , `GameDateTime`)
    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;
