use [master]

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

CREATE TABLE Team
(
team_id INT IDENTITY(1,1) 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_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_number int not Null,
contract_start_time DateTime not null,
contract_end_time DateTime not null,
PRIMARY KEY(Contract_id)
);
/**/
CREATE TABLE Player_statistic
(
statistic_id INT IDENTITY(1,1) NOT NULL,
Contract_id int not Null,
one_point int not Null,
two_point int not Null,
three_point int not Null,
fols_commited int not null,
block_shots int not null,
wins int not null,
loses int not null,
loses_on_draw int not null,
wins_on_draw int not null,
contract_end_time DateTime 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_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_end_time DateTime not null,
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,
draws 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,
 referies_status VARCHAR(50) NOT NULL,
PRIMARY KEY(coach_id)

PRIMARY KEY(statistic_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_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,
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,
/*guest_team int not null,
home_team_score int not null,
guest_team_score int not null,*/
action_time Time 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)
);