DROP DATABASE IF EXISTS PlayerStats;
CREATE DATABASE PlayerStats CHARSET 'utf8';
USE PlayerStats;

CREATE TABLE StatTypes(
	StatCode VARCHAR(3) NOT NULL PRIMARY KEY,
	Name VARCHAR(150) NOT NULL
);

CREATE TABLE Positions(
	PositionCode VARCHAR(3) NOT NULL PRIMARY KEY,
	Name VARCHAR(150) NOT NULL
);

CREATE TABLE Players(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(150) NOT NULL,
    Num INTEGER NOT NULL,
    PositionCode VARCHAR(3) NOT NULL
);

CREATE TABLE Tournaments(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(150) NOT NULL
);

CREATE TABLE Matches(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	MatchDate DATE NOT NULL,
    TournamentId INTEGER NOT NULL
);

CREATE TABLE MatchStats(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	MatchId INTEGER NOT NULL,
    PlayerId INTEGER NOT NULL,
    EventMinute INTEGER NOT NULL,
	StatCode VARCHAR(3) NOT NULL
);

INSERT INTO StatTypes VALUES('G', 'Гол');
INSERT INTO StatTypes VALUES('A', 'Асистенция');
INSERT INTO StatTypes VALUES('R', 'Червен картон');
INSERT INTO StatTypes VALUES('Y', 'Жълт картон');
INSERT INTO StatTypes VALUES('OG', 'Автогол');
INSERT INTO StatTypes VALUES('IN', 'Смяна влиза');
INSERT INTO StatTypes VALUES('OUT', 'Смяна излиза');

INSERT INTO Positions VALUES('GK', 'Вратар');
INSERT INTO Positions VALUES('RB', 'Десен защитник');
INSERT INTO Positions VALUES('LB', 'Ляв защитник');
INSERT INTO Positions VALUES('CB', 'Централен защитник');
INSERT INTO Positions VALUES('RM', 'Десен полузащитник');
INSERT INTO Positions VALUES('LM', 'Ляв полузащитник');
INSERT INTO Positions VALUES('CM', 'Полузащитник');
INSERT INTO Positions VALUES('CF', 'Централен нападател');

INSERT INTO Players VALUES(NULL, 'Ivaylo Trifonov', 1, 'GK');
INSERT INTO Players VALUES(NULL, 'Valko Trifonov', 2, 'RB');
INSERT INTO Players VALUES(NULL, 'Ognyan Yanev', 3, 'CB');
INSERT INTO Players VALUES(NULL, 'Zahari Dragomirov', 4, 'CB');
INSERT INTO Players VALUES(NULL, 'Bozhidar Chilikov', 5, 'LB');
INSERT INTO Players VALUES(NULL, 'Timotei Zahariev', 6, 'CM');
INSERT INTO Players VALUES(NULL, 'Marin Valentinov', 7, 'CM');
INSERT INTO Players VALUES(NULL, 'Mitre Cvetkov', 99, 'CF');
INSERT INTO Players VALUES(NULL, 'Zlatko Genov', 9, 'CF');
INSERT INTO Players VALUES(NULL, 'Matey Goranov', 10, 'RM');
INSERT INTO Players VALUES(NULL, 'Sergei Zhivkov', 11, 'LM');

INSERT INTO Tournaments VALUES(NULL, 'Шампионска лига');
INSERT INTO Tournaments VALUES(NULL, 'Първа лига');
INSERT INTO Tournaments VALUES(NULL, 'Купа на България');
INSERT INTO Tournaments VALUES(NULL, 'Суперкупа на България');

INSERT INTO Matches VALUES(NULL, '2018-04-08', 2);
INSERT INTO Matches VALUES(NULL, '2018-04-13', 2);
INSERT INTO Matches VALUES(NULL, '2018-04-21', 2);
INSERT INTO Matches VALUES(NULL, '2018-04-28', 2);
INSERT INTO Matches VALUES(NULL, '2018-05-06', 2);
INSERT INTO Matches VALUES(NULL, '2018-05-11', 2);
INSERT INTO Matches VALUES(NULL, '2017-09-21', 3);
INSERT INTO Matches VALUES(NULL, '2017-10-26', 3);

INSERT INTO MatchStats VALUES(NULL, 8, 9, 14, 'G');
INSERT INTO MatchStats VALUES(NULL, 8, 8, 14, 'A');
INSERT INTO MatchStats VALUES(NULL, 8, 3, 43, 'Y');
INSERT INTO MatchStats VALUES(NULL, 7, 2, 28, 'Y');
INSERT INTO MatchStats VALUES(NULL, 7, 10, 45, 'Y');
INSERT INTO MatchStats VALUES(NULL, 7, 10, 65, 'R');
INSERT INTO MatchStats VALUES(NULL, 1, 10, 23, 'G');
INSERT INTO MatchStats VALUES(NULL, 1, 9, 23, 'A');
INSERT INTO MatchStats VALUES(NULL, 1, 9, 43, 'G');
INSERT INTO MatchStats VALUES(NULL, 2, 4, 33, 'OG');
INSERT INTO MatchStats VALUES(NULL, 2, 9, 68, 'G');
INSERT INTO MatchStats VALUES(NULL, 2, 1, 68, 'A');
INSERT INTO MatchStats VALUES(NULL, 3, 3, 35, 'G');
INSERT INTO MatchStats VALUES(NULL, 3, 4, 35, 'A');
INSERT INTO MatchStats VALUES(NULL, 3, 8, 55, 'G');
INSERT INTO MatchStats VALUES(NULL, 3, 11, 55, 'A');
INSERT INTO MatchStats VALUES(NULL, 4, 3, 9, 'G');
INSERT INTO MatchStats VALUES(NULL, 4, 8, 9, 'G');
INSERT INTO MatchStats VALUES(NULL, 4, 8, 56, 'OG');
INSERT INTO MatchStats VALUES(NULL, 5, 8, 67, 'G');