use [master]

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'HockeyStatistic')
    CREATE DATABASE [HockeyStatistic]
else
    DROP DATABASE [HockeyStatistic]
    CREATE DATABASE [HockeyStatistic]
go
use [HockeyStatistic]

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_date DateTime Not null,
team_main_stadion int not null,
PRIMARY KEY(team_id)
);/*+*/

CREATE TABLE Team_Statistic
(
team_statistic_id INT IDENTITY(1,1) NOT NULL,
team_id int not Null,

wins int not null,
loses int not null,
loses_on_draw int not null,
wins_on_draw int not null,

PRIMARY KEY(team_statistic_id)
);/*+*/
/*CREATE TABLE Team_Stadion
(
    Team_Stadion_id INT IDENTITY(1,1) NOT NULL,
    team_id INT not null,
    Stadion_id INT NOT NULL,
    Start_stadion_use DateTime Not null,
    Finish_stadion_use DateTime Not null,
    PRIMARY KEY(Team_Stadion_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,
stadion_fondation_date DateTime Not null,
stadion_places_count int not null,
stadion_location_latitude float NOT NULL,
stadion_location_longitude fload NOT NULL,

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

CREATE TABLE Players
(
player_id INT IDENTITY(1,1) NOT NULL,
current_team_id int not Null,
player_name VARCHAR(50) NOT NULL,
player_surname VARCHAR(50) NOT NULL,
player_country  VARCHAR(50) NOT NULL,
player_born_date DateTime Not null,
PRIMARY KEY(player_id)
);/*+*/


CREATE TABLE Player_Contracts
(
contract_id INT IDENTITY(1,1) NOT NULL,
team_id int not Null,
player_id int not Null,
player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
player_number_on_contract int not Null,
contract_start_time DateTime not null,
contract_deyure_end_time DateTime not null,
contract_defacto_end_time DateTime,
PRIMARY KEY(contract_id)
);
/*+*/
CREATE TABLE Player_statistic
(
statistic_id INT IDENTITY(1,1) NOT NULL,
contract_id int not Null,

total_time_played time not null,
goals_from_game_bullits_count int not null,
goals_from_game_count int not Null,
key_passes_for_goals_count int not Null,
saves_count int not Null,
two_min_fare int not Null,
five_min_fare int not_Null,

wins int not null,
loses int not null,
loses_on_draw int not null,
wins_on_draw int not null,
PRIMARY KEY(statistic_id)
);/*+-*/

CREATE TABLE Coach
(
coach_id INT IDENTITY(1,1) NOT NULL,
current_team_id int not Null,
coach_name VARCHAR(50) NOT NULL,
coach_surname VARCHAR(50) NOT NULL,
coach_country  VARCHAR(50) NOT NULL,
coach_city VARCHAR(50) NOT NULL,
coach_born_date DateTime Not null,
PRIMARY KEY(coach_id)
);/*+*/

CREATE TABLE Coach_Contracts
(
contract_id INT IDENTITY(1,1) NOT NULL,
team_id int not Null,
coach_id int not Null,
contract_start_time DateTime not null,
contract_deyure_end_time DateTime not null,
contract_defacto_end_time DateTime,
PRIMARY KEY(contract_id)
);
/*+*/
CREATE TABLE Coach_statistic
(
statistic_id INT IDENTITY(1,1) NOT NULL,
Contract_id int not Null,

wins int not null,
loses int not null,
loses_on_draw int not null,
wins_on_draw int not null,

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


CREATE TABLE Referies
(
 referie_id INT IDENTITY(1,1) NOT NULL,
 referie_name VARCHAR(50) NOT NULL,
 referie_surname VARCHAR(50) NOT NULL,
 referie_born_date DateTime Not null,


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


CREATE TABLE Team_Tournament
(
team_tournament_id INT IDENTITY(1,1) NOT NULL,
tournament_id int not null,
team_id int not null,
PRIMARY KEY(team_tournament_id )
);
/*common table for Tour and team*/

CREATE TABLE Tournament
(
tournament_id INT IDENTITY(1,1) NOT NULL,
tournament_name VARCHAR(50) NOT NULL,
tournament_status  VARCHAR(50) NOT NULL,/*international local*/
PRIMARY KEY(tournament_id)
);/*+*/

CREATE TABLE Game
(
game_id INT IDENTITY(1,1) NOT NULL,
tournament_id int NOT NULL,
stadion_id int not Null,
referie_id int not null,
home_team int not null,
guest_team int not null,
home_team_score int not null,
guest_team_score int not null,
start_time date not null,

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

CREATE TABLE GameApplication
(
gameApplication_id INT IDENTITY(1,1) NOT NULL,
game_id int NOT NULL,
player_id int not null,
team_id int not null,
PRIMARY KEY(gameApplication_id)
);/*+*/


CREATE TABLE GameLog
(
gameLog_id INT IDENTITY(1,1) NOT NULL,
game_id int not null,

action_time Time not null,
main_game_period bit not null
game_period int not null,
player_id int not null,
player_action int not null,/*action of player triger for statistic and score update*/
PRIMARY KEY(gameLog_id)
);

CREATE TABLE PlayerAction
(
    player_Action_id INT IDENTITY(1,1) NOT NULL,
    action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
    PRIMARY KEY(player_Action_id)
);



ALTER TABLE Team_statistic
WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
REFERENCES Team(team_id);

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

ALTER TABLE Player_Contracts
WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
REFERENCES Team(team_id);

ALTER TABLE Player_Contracts
WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
REFERENCES Players(player_id);

ALTER TABLE Player_statistic
WITH CHECK ADD CONSTRAINT FK_Contract_Id FOREIGN KEY(contract_id)
REFERENCES Player_Contracts(contract_id );

ALTER TABLE Coach
WITH CHECK ADD CONSTRAINT FK_Current_team_id FOREIGN KEY(current_team_id)
REFERENCES Team(team_id);

ALTER TABLE Coach_Contracts
WITH CHECK ADD CONSTRAINT FK_Team_id FOREIGN KEY(team_id)
REFERENCES Team(team_id);

ALTER TABLE Coach_Contracts
WITH CHECK ADD CONSTRAINT FK_Coach_id FOREIGN KEY(coach_id)
REFERENCES Coach(coach_id);

ALTER TABLE Coach_statistic
WITH CHECK ADD CONSTRAINT FK_Contract_id FOREIGN KEY(сontract_id)
REFERENCES Coach_Contracts(сontract_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_Team_id FOREIGN KEY(team_id)
REFERENCES Team(team_id);

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

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

ALTER TABLE Game
WITH CHECK ADD CONSTRAINT FK_Referie_id FOREIGN KEY(referie_id)
REFERENCES Referies(referie_id);

ALTER TABLE GameApplication
WITH CHECK ADD CONSTRAINT FK_Game_id FOREIGN KEY(game_id)
REFERENCES Referies(referie_id);

ALTER TABLE GameLog
WITH CHECK ADD CONSTRAINT FK_Game_id FOREIGN KEY(game_id)
REFERENCES  Game(game_id);