ddl.sql

-- create database faceit;

create type TournamentTypeEnum as enum ('single elimination', 'double elimination', 'round robin');
create type MapEnum as enum ('de_dust2', 'de_cache', 'de_train', 'de_nuke', 'de_mirage', 'de_inferno', 'de_ancient', 'de_vertigo');

create table Users (
	UserId integer not null generated by default as identity,
	Username varchar(200) not null,
	AuthToken text not null,
	ELO integer not null,
	
	primary key (UserId)
);

create table Teams (
	TeamId integer not null generated by default as identity,
	TeamName varchar(200) not null,
	CaptainId integer not null references Users(UserId),
	
	primary key (TeamId)
);

create table TeamsUsers (
	TeamId integer not null references Teams(TeamId),
	UserId integer not null references Users(UserId),
	
	primary key (TeamId, UserId)
);

create table Hubs (
	HubId integer not null generated by default as identity,
	ELOLeftBound integer not null,
	ELORightBound integer not null,
	CreatorId integer not null references Users(UserId),
	
	primary key (HubId)
);

create table HubPlayers (
	HubId integer not null references Hubs(HubId),
	UserId integer not null references Users(UserId),
	
	primary key (HubId, UserId)
);

create table Tournaments (
	TournamentId integer not null generated by default as identity,
	TournamentName varchar(200) not null,
	TournamentYear integer not null,
	TournamentType TournamentTypeEnum not null,
	
	primary key (TournamentId),
	unique (TournamentName, TournamentYear)
);

create table TournamentPrizes (
	TournamentId integer not null references Tournaments(TournamentId),
	Place integer not null,
	Prize integer not null,
	
	primary key (TournamentId, Place),
	constraint CorrectPrize
		check (Prize > 0)
);

create table TeamTournaments (
	TeamId integer not null references Teams(TeamId),
	Tournamentid integer not null references Tournaments(TournamentId),
	Place integer not null,
	
	primary key (TeamId, TournamentId)
);

create table Matches (
	MatchId integer not null generated by default as identity,
	PickingSideTeamId integer not null references Teams(TeamId),
	OtherTeam integer not null references Teams(TeamId),
	
	primary key (MatchId),
	constraint NotSameTeams
		check (PickingSideTeamId <> OtherTeam)
);

create table HubMatches (
	HubId integer not null references Hubs(HubId),
	MatchId integer not null references Matches(MatchId),
	
	primary key (HubId, MatchId)
);

create table Games (
	GameId integer not null generated by default as identity,
	PlayedMap MapEnum not null,
	StartTime timestamp not null,
	EndTime timestamp,
	WonRoundsTeam1 integer not null,
	WonRoundsTeam2 integer not null,
	MatchId integer not null references Matches(MatchId),
	
-- В check constraint CorrectTimes возможен EndTime = null, при сравнении получится null
-- Но в документации написано, что check выполяется если значение true или null,
-- значит стоит обработать этот случай отдельно

	primary key (GameId),
	unique (MatchId, StartTime),
	constraint CorrectTimes
		check ((Endtime is not null and StartTime <= EndTime) or EndTime is null)
);

create table UserStat (
	GameId integer not null references Games(GameId),
	UserId integer not null references Users(UserId),
	Kills integer not null,
	Assists integer not null,
	Deaths integer not null,
	
	primary key (GameId, UserId)
);

create table TournamentMatches (
	TournamentId integer not null references Tournaments(TournamentId),
	MatchId integer not null references Matches(MatchId),
	
	primary key (TournamentId, MatchId)
);

-- Для ускорения AllPlayersKDInCertainPeriod
create index on Games using Btree(StartTime);

-- Для ускорения AllPlayersKDOnCertainMap
create index on Games using hash(PlayedMap);

-- Для ускорения PlayerWinrate
create index on Games using Btree(WonRoundsTeam1, WonRoundsTeam2);