CREATE TABLE `Team` (
	`team_id` INT NOT NULL,
	`team_name` TEXT(32) NOT NULL UNIQUE,
	`team_name_eng` TEXT(32),
	`win` INT NOT NULL,
	`draw` INT NOT NULL,
	`lose` INT NOT NULL,
	`goals_for` INT NOT NULL,
	`goals_agains` INT NOT NULL,
	`place` INT NOT NULL,
	`difference` INT NOT NULL,
	`points` INT NOT NULL,
	`group_id` INT NOT NULL,
	PRIMARY KEY (`team_id`),
	FOREIGN KEY ('group_id') REFERENCES `Champs`(`group_id`)
);

CREATE TABLE `Game` (
	`game_id` INT NOT NULL,
	`team1_id` INT NOT NULL,
	`team2_id` INT NOT NULL,
	`stage_id` INT NOT NULL,
	`date_time` INT NOT NULL,
	`game_state` INT NOT NULL,
	`followed_game` INT NOT NULL,
    `current_tour` TEXT(10),
	PRIMARY KEY (`game_id`),
	FOREIGN KEY (`team1_id`) REFERENCES `Team`(`team_id`),
	FOREIGN KEY (`team2_id`) REFERENCES `Team`(`team_id`),
	FOREIGN KEY (`stage_id`) REFERENCES `Stage`(`stage_id`)
);

CREATE TABLE `Stage` (
	`stage_id` INT NOT NULL,
	`stage_name` TEXT(16) NOT NULL UNIQUE,
	PRIMARY KEY (`stage_id`)
);

CREATE TABLE `Player` (
	`player_id` INT NOT NULL,
	`team_id` INT,
	`player_name` TEXT(32),
	`player_number` INT,
	`character_id` INT NOT NULL,
	`player_age` INT,
	PRIMARY KEY (`player_id`),
    FOREIGN KEY (`team_id`) REFERENCES `Team`(`team_id`),
	FOREIGN KEY (`character_id`) REFERENCES `Character`(`character_id`)
);

CREATE TABLE `Character` (
	`character_id` INT NOT NULL,
	`character_name` TEXT(16) NOT NULL UNIQUE,
	PRIMARY KEY (`character_id`)
);

CREATE TABLE `Champs` (
	`group_id` INT NOT NULL,
	`group_name` TEXT(8) NOT NULL UNIQUE,
	`current_tour` TEXT(10),
	PRIMARY KEY (`group_id`)
);

CREATE TABLE `Lineup` (
	`game_id` INT NOT NULL,
	`team_id` INT NOT NULL,
	`player_id` INT NOT NULL,
	`reserve` INT NOT NULL,
	`player_name` TEXT(32) NOT NULL,
	`player_number` INT,
	`character_id` INT NOT NULL,
	`position_top` REAL,
	`position_left` REAL,
	PRIMARY KEY (`game_id`,`player_id`),
	FOREIGN KEY (`team_id`) REFERENCES `Team`(`team_id`),
	FOREIGN KEY (`game_id`) REFERENCES `Game`(`game_id`)
);

CREATE TABLE `Event` (
	`event_id` INT NOT NULL,
	`game_id` INT NOT NULL,
	`team_id` INT,
	`event_type_id` INT NOT NULL,
	`player1_id` INT,
	`player2_id` INT,
	`minute` TEXT(10) NOT NULL,
	`description` TEXT(256),
	PRIMARY KEY (`event_id`),
	FOREIGN KEY (`game_id`) REFERENCES `Game`(`game_id`),
	FOREIGN KEY (`team_id`) REFERENCES `Team`(`team_id`),
	FOREIGN KEY (`event_type_id`) REFERENCES `Event_type`(`event_type_id`),
	FOREIGN KEY (`player1_id`) REFERENCES `Player`(`player_id`),
	FOREIGN KEY (`player2_id`) REFERENCES `Player`(`player_id`)
);

CREATE TABLE `Event_type` (
	`event_type_id` INT NOT NULL,
	`event_type_name` TEXT(16) NOT NULL,
	PRIMARY KEY (`event_type_id`)
);

CREATE TABLE `Game_scores` (
	`game_id` INT NOT NULL,
	`team1_score` INT NOT NULL,
	`team1_penalty_score` INT,
	`team2_score` INT NOT NULL,
	`team2_penalty_score` INT,
	PRIMARY KEY (`game_id`),
	FOREIGN KEY (`game_id`) REFERENCES `Game`(`game_id`)
);

CREATE TABLE `Team_group_statistic` (
	`team_id` INT NOT NULL,
	`win` INT NOT NULL,
	`draw` INT NOT NULL,
	`lose` INT NOT NULL,
	`goals_for` INT NOT NULL,
	`goals_agains` INT NOT NULL,
	`place` INT NOT NULL,
	`points` INT NOT NULL,
	`group_id` INT NOT NULL,
	FOREIGN KEY (`group_id`) REFERENCES `Champs`(`group_id`)
);

