USE [master]

IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyTeamAccountingDBUA')
    CREATE DATABASE [HockeyTeamAccountingDBUA]
ELSE
    DROP DATABASE [HockeyTeamAccountingDBUA]
    CREATE DATABASE [HockeyTeamAccountingDBUA]
GO
USE [HockeyTeamAccountingDBUA]

CREATE TABLE Team
(
team_id INT IDENTITY(1,1) NOT NULL,
team_country VARCHAR(50) NOT NULL,
team_city VARCHAR(50) NOT NULL,
team_name VARCHAR(50) NOT NULL,
team_fondation_year int NOT NULL,
team_main_stadion INT,
CONSTRAINT UniqueTeams UNIQUE (team_name),
PRIMARY KEY(team_id)
);/*+*/
CREATE TABLE Trainer
(
trainer_id INT IDENTITY(1,1) NOT NULL,

trainer_name VARCHAR(50) NOT NULL,
trainer_surname VARCHAR(50) NOT NULL,
trainer_country VARCHAR(50) NOT NULL,
birth_date DateTime not null,
current_team_id int,

CONSTRAINT UniqueTrainer UNIQUE (trainer_name,trainer_surname,trainer_country,birth_date/*,team_id*/),
PRIMARY KEY(trainer_id)
);


CREATE TABLE Stadion
(
stadion_id INT IDENTITY(1,1) NOT NULL,

stadion_name VARCHAR(50) NOT NULL,
stadion_country VARCHAR(50) NOT NULL,
stadion_city VARCHAR(50) NOT NULL,
CONSTRAINT UniqueStadion_ UNIQUE (stadion_name),

PRIMARY KEY(stadion_id)
);/*+*/

CREATE TABLE Players
(
player_id INT IDENTITY(1,1) NOT NULL,
current_team_id INT,
player_name VARCHAR(50) NOT NULL,
player_surname VARCHAR(50) NOT NULL,
player_country  VARCHAR(50) NOT NULL,
player_born_date DateTime NOT NULL,
player_amplua VARCHAR(50) NOT NULL,
CONSTRAINT UniquePlayers UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),
PRIMARY KEY(player_id)
);





CREATE TABLE Team_Tournament
(
team_tournament_id INT IDENTITY(1,1) NOT NULL,
tournament_id INT NOT NULL,
team_id INT NOT NULL,
CONSTRAINT UniqueStadion UNIQUE (tournament_id,team_id/*,team_id*/),
PRIMARY KEY(team_tournament_id )
);


CREATE TABLE Tournament
(
tournament_id INT IDENTITY(1,1) NOT NULL,
tournament_name VARCHAR(50) NOT NULL,

tournament_start Datetime not null,
tournament_finish Datetime not null,

PRIMARY KEY(tournament_id)
);

CREATE TABLE Game
(
game_id INT IDENTITY(1,1) NOT NULL,
tournament_id INT NOT NULL,

home_team_id INT NOT NULL,
guest_team_id INT NOT NULL,

stadion_id INT ,
home_team_trainer_id INT,
guest_team_trainer_id INT,

home_team_score INT DEFAULT 0,
guest_team_score INT DEFAULT 0 ,
overtime_home_team_score INT DEFAULT 0,
overtime_guest_team_score INT DEFAULT 0,
bullits_home_team_score INT DEFAULT 0,
bullits_guest_team_score INT DEFAULT 0,
start_time DATETIME DEFAULT NULL,

PRIMARY KEY(game_id)
);


CREATE TABLE PlayerGameApplication
(
gameApplication_id INT IDENTITY(1,1) NOT NULL,
game_id INT NOT NULL,
player_id INT NOT NULL,
team_id int not null,
ganeral_game_time_goal_scored int Default 0,
overtime_goal_scored int Default 0,
after_total_time_bullit_scored int Default 0,
PRIMARY KEY(gameApplication_id),
CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
);




ALTER TABLE Players
WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
REFERENCES Team(team_id);



ALTER TABLE Team_Tournament
WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
REFERENCES Tournament(tournament_id);

ALTER TABLE Team_Tournament
WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
REFERENCES Team(team_id);

ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
REFERENCES Tournament(tournament_id);

 ALTER TABLE Team
WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
REFERENCES Stadion(stadion_id);

ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
REFERENCES Stadion(stadion_id);


ALTER TABLE PlayerGameApplication
WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
REFERENCES Game(game_id);

 ALTER TABLE PlayerGameApplication
WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
REFERENCES Players(player_id);




 ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team_id)
REFERENCES Team(team_id);

 ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team_id)
REFERENCES Team(team_id);

  ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Home_Team_Trainer_ID FOREIGN KEY(home_team_trainer_id)
REFERENCES Trainer(trainer_id);

  ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Guest_Team_Trainer_ID FOREIGN KEY(guest_team_trainer_id)
REFERENCES Trainer(trainer_id);



ALTER TABLE Trainer
WITH CHECK ADD CONSTRAINT FK_CurrentTeam_id FOREIGN KEY(current_team_id)
REFERENCES Team(team_id);