#create schema final_project_color_game;

use final_project_color_game;

CREATE TABLE Team
(
  TID INT NOT NULL AUTO_INCREMENT,
  Tname VARCHAR(50) NOT NULL,
  Tscore INT,
  PRIMARY KEY (TID)
)ENGINE = InnoDB;

CREATE TABLE League
(
  LID INT NOT NULL AUTO_INCREMENT,
  Sdate DATETIME,
  Edate DATETIME,
  PRIMARY KEY (LID)
)ENGINE = InnoDB;

CREATE TABLE TeamLeague
(
  MVP_ID INT,
  LID INT NOT NULL,
  PRIMARY KEY (LID),
  FOREIGN KEY (LID) REFERENCES League(LID)
)ENGINE = InnoDB;

CREATE TABLE SingleLeague
(
  LID INT NOT NULL,
  PRIMARY KEY (LID),
  FOREIGN KEY (LID) REFERENCES League(LID)
)ENGINE = InnoDB;

CREATE TABLE TCompetes
(
  TID INT NOT NULL,
  LID INT NOT NULL,
  PRIMARY KEY (TID, LID),
  FOREIGN KEY (TID) REFERENCES Team(TID),
  FOREIGN KEY (LID) REFERENCES TeamLeague(LID)
)ENGINE = InnoDB;

CREATE TABLE Player
(
  PID INT NOT NULL AUTO_INCREMENT,
  Fname VARCHAR(50),
  Lname VARCHAR(50),
  Mname VARCHAR(50) ,
  Pscore INT NOT NULL,
  TID INT NOT NULL DEFAULT 1, # - zero means no team
  PRIMARY KEY (PID),
  FOREIGN KEY (TID) REFERENCES Team(TID)
)ENGINE = InnoDB;

CREATE TABLE SCompetes
(
  PID INT NOT NULL,
  LID INT NOT NULL,
  PRIMARY KEY (PID, LID),
  FOREIGN KEY (PID) REFERENCES Player(PID),
  FOREIGN KEY (LID) REFERENCES SingleLeague(LID)
)ENGINE = InnoDB;

ALTER TABLE scompetes ADD CONSTRAINT fk_player_id FOREIGN KEY (PID) REFERENCES Player(PID) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE scompetes ADD CONSTRAINT fk_sl_id FOREIGN KEY (LID) REFERENCES SingleLeague(LID) ON DELETE RESTRICT ON UPDATE CASCADE;