-- 1. create table tmp_matches
-- 2. insert into tmp_matches
-- 3. create table teams (IDENTITY team_id + team_name)
-- 4. insert into teams [INSERT INTO teams SELECT]
-- 5. create table matches (IDENTITY match_id + team1ID + team2ID + goal1 + goal2 + point1 + point2)
-- 6. insert into matches [INSERT INTO matches SELECT]
-- 7. standings

IF object_id('tmp_matches', 'U') is not null DROP TABLE tmp_matches;
IF object_id('matches', 'U') is not null DROP TABLE matches;
IF object_id('teams', 'U') is not null DROP TABLE teams;

CREATE TABLE tmp_matches (
	m_team1 nvarchar(100),
	m_team2 nvarchar(100),
	m_scored int,
	m_against int
);

INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'ÉRD', 28, 33);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('ÉRD', 'Eszterházy SC', 33, 28);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'FTC Rail Cargo Hungaria', 25, 35);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'MTK Budapest', 35, 25);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'Alba FKC', 38, 26);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Alba FKC', 'Győri Audi ETO KC', 26, 38);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'Moyra-Budaörs Handball', 31, 29);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Moyra-Budaörs Handball', 'EUbility Group-Békéscsaba', 29, 31);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'DVSC SCHAEFFLER', 35, 18);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DVSC SCHAEFFLER', 'Siófok KTC KFT', 18, 35);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'DKKA', 29, 24);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'GVM Europe-Vác', 24, 29);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'Eu-Fire Mosonmagyaróvár', 19, 16);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eu-Fire Mosonmagyaróvár', 'Kisvárda Master Good SE', 16, 19);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Moyra-Budaörs Handball', 'Győri Audi ETO KC', 30, 45);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'Moyra-Budaörs Handball', 45, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DVSC SCHAEFFLER', 'EUbility Group-Békéscsaba', 37, 25);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'DVSC SCHAEFFLER', 25, 37);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eu-Fire Mosonmagyaróvár', 'FTC Rail Cargo Hungaria', 13, 24);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'Eu-Fire Mosonmagyaróvár', 24, 13);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'Siófok KTC KFT', 20, 26);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'DKKA', 26, 20);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Alba FKC', 'Kisvárda Master Good SE', 23, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'Alba FKC', 23, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'Eszterházy SC', 23, 19);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'MTK Budapest', 19, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('ÉRD', 'GVM Europe-Vác', 30, 27);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'ÉRD', 27, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'EUbility Group-Békéscsaba', 36, 21);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'FTC Rail Cargo Hungaria', 21, 36);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'Győri Audi ETO KC', 25, 35);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'MTK Budapest', 35, 25);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'GVM Europe-Vác', 28, 29);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'Siófok KTC KFT', 29, 28);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'Moyra-Budaörs Handball', 31, 22);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Moyra-Budaörs Handball', 'Kisvárda Master Good SE', 22, 31);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'Alba FKC', 30, 19);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Alba FKC', 'FTC Rail Cargo Hungaria', 19, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'Eu-Fire Mosonmagyaróvár', 22, 28);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eu-Fire Mosonmagyaróvár', 'Eszterházy SC', 28, 22);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'DKKA', 23, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'EUbility Group-Békéscsaba', 30, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('ÉRD', 'MTK Budapest', 32, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'ÉRD', 30, 32);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'DVSC SCHAEFFLER', 34, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DVSC SCHAEFFLER', 'Győri Audi ETO KC', 23, 34);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'FTC Rail Cargo Hungaria', 23, 36);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'DKKA', 36, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DVSC SCHAEFFLER', 'Kisvárda Master Good SE', 27, 27);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'DVSC SCHAEFFLER', 27, 27);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eu-Fire Mosonmagyaróvár', 'MTK Budapest', 29, 31);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'Eu-Fire Mosonmagyaróvár', 31, 29);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Moyra-Budaörs Handball', 'FTC Rail Cargo Hungaria', 26, 38);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'Moyra-Budaörs Handball', 38, 26);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'ÉRD', 34, 22);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('ÉRD', 'Siófok KTC KFT', 22, 34);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'EUbility Group-Békéscsaba', 43, 21);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'GVM Europe-Vác', 21, 43);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'Győri Audi ETO KC', 24, 28);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'DKKA', 28, 24);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Alba FKC', 'Eszterházy SC', 32, 15);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'Alba FKC', 15, 32);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'GVM Europe-Vác', 41, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'Győri Audi ETO KC', 30, 41);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'Moyra-Budaörs Handball', 31, 33);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Moyra-Budaörs Handball', 'Eszterházy SC', 33, 31);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'DKKA', 30, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DKKA', 'Kisvárda Master Good SE', 23, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('MTK Budapest', 'Alba FKC', 26, 27);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Alba FKC', 'MTK Budapest', 27, 26);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('ÉRD', 'Eu-Fire Mosonmagyaróvár', 40, 26);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eu-Fire Mosonmagyaróvár', 'ÉRD', 26, 40);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'Siófok KTC KFT', 25, 30);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'EUbility Group-Békéscsaba', 30, 25);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('FTC Rail Cargo Hungaria', 'DVSC SCHAEFFLER', 38, 27);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('DVSC SCHAEFFLER', 'FTC Rail Cargo Hungaria', 27, 38);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('GVM Europe-Vác', 'Kisvárda Master Good SE', 25, 24);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Kisvárda Master Good SE', 'GVM Europe-Vác', 24, 25);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Győri Audi ETO KC', 'EUbility Group-Békéscsaba', 47, 23);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('EUbility Group-Békéscsaba', 'Győri Audi ETO KC', 23, 47);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Eszterházy SC', 'Siófok KTC KFT', 11, 31);
INSERT INTO tmp_matches (m_team1, m_team2, m_scored, m_against)  VALUES ('Siófok KTC KFT', 'Eszterházy SC', 31, 11);

-- SELECT * FROM tmp_matches;

CREATE TABLE teams (
	team_id int identity primary key,
	team_name nvarchar(100)
);

INSERT INTO teams (team_name) 
SELECT distinct m_team1 FROM tmp_matches;

-- lpad
-- SELECT right(concat(replicate('0', 5),  team_id), 4), team_name FROM teams;

CREATE TABLE matches (
	match_id int identity primary key,
	match_team1 int references teams(team_id),
	match_team2 int references teams(team_id),
	match_goal1 int,
	match_goal2 int,
	match_point1 int,
	match_point2 int
);
INSERT INTO matches (match_team1, match_team2, match_goal1, match_goal2, match_point1, match_point2)
SELECT teamA.team_id, teamB.team_id, m_scored, m_against, 
	CASE
		WHEN m_scored>m_against THEN 2
		WHEN m_scored=m_against THEN 1
		WHEN m_scored<m_against THEN 0
	END as point1,
	CASE sign(m_scored-m_against) 
		WHEN -1 THEN 2
		WHEN 0 THEN 1
		WHEN 1 THEN 0
	END as point2
FROM tmp_matches 
	INNER JOIN teams teamA ON (m_team1 = teamA.team_name)
	INNER JOIN teams teamB ON (m_team2 = teamB.team_name);

-- SELECT * FROM matches;
-- tabella: csapatnév + lőtt gólok + kapott gólok + gólkülönbség + pontok

SELECT match_team1 as teamID,
	SUM(match_goal1) as goalsScored, SUM(match_goal2) as goalsAgainst, 
	SUM(match_goal1)-SUM(match_goal2) as goalsDiff,
	SUM(match_point1) as points
FROM matches 
GROUP BY match_team1
ORDER BY points desc, goalsDiff desc, goalsScored desc;

WITH data AS (
	SELECT match_team1 as teamID, 
		SUM(match_goal1) as goalsScored, SUM(match_goal2) as goalsAgainst, 
		SUM(match_goal1)-SUM(match_goal2) as goalsDiff,
		SUM(match_point1) as points
	FROM matches 
	GROUP BY match_team1
)
SELECT team_name, data.*
FROM data INNER JOIN teams ON (teamID = team_id)
ORDER BY points desc, goalsDiff desc, goalsScored desc;


SELECT match_team1 as teamID, team_name,
	SUM(match_goal1) as goalsScored, SUM(match_goal2) as goalsAgainst, 
	SUM(match_goal1)-SUM(match_goal2) as goalsDiff,
	SUM(match_point1) as points
FROM matches INNER JOIN teams ON (match_team1=team_id)
GROUP BY match_team1, team_name
ORDER BY points desc, goalsDiff desc, goalsScored desc;