 -- Table definitions for the tournament project.
 --
 
+-- Containtais all the players registered in database
+-- It stores the lastname and firstname separately 
+-- to allows diferents orders
 CREATE TABLE players (
 	firstname TEXT,
 	lastname TEXT,
 	id SERIAL PRIMARY KEY
 );
 
+
+-- Table Events has every tournament record
+-- It is possible to add a future event for event_date
 CREATE TABLE events (
 	name TEXT,
 	event_date TIMESTAMP,
 	id SERIAL PRIMARY KEY
 );
 
+
+-- Table Players in Event specifies the relation between 
+-- the registered player and the current event
 CREATE TABLE playersInEvent (
 	event INTEGER REFERENCES events(id),
 	player INTEGER REFERENCES players(id),
 	PRIMARY KEY(event, player)
 );
 
+
+-- Table Matches stores every match among players
+-- It allows to store the round number,
+-- player order (if we can implement white and black order for chess)
+-- and the points granted for each player
 CREATE TABLE matches (
 	player_one INTEGER REFERENCES players(id),
 	player_two INTEGER REFERENCES players(id),

	id SERIAL PRIMARY KEY
);

CREATE OR REPLACE FUNCTION activePlayersInEvent(actualEvent INTEGER)
RETURNS TABLE(
	id INTEGER,
	name TEXT
) AS $func$
SELECT players.id, (firstname || ' ' || lastname) as name 
        FROM players, playersInEvent WHERE event=actualEvent 
        AND players.id=playersInEvent.player;
$func$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION matchesByPlayersInEvent(actualEvent INTEGER)
RETURNS TABLE(
	id INTEGER,
	name TEXT,

) AS $func$
SELECT id, name, SUM(matchesByPlayers.score) AS points,
		COUNT(matchesByPlayers.match) AS matches
        FROM matchesByPlayersInEvent(currentEvent) as matchesByPlayers
        GROUP BY id, name 
        ORDER BY points DESC, name ASC;
$func$  LANGUAGE sql;
