operations.sql

-- 1. createAccount

CREATE OR REPLACE FUNCTION createAccount(
    arg_username VARCHAR(30),
    arg_passkey VARCHAR(30),
    arg_p_role olympics.role
) RETURNS VOID as $$
DECLARE
    date_time TIMESTAMP;
BEGIN
    INSERT INTO olympics.ACCOUNT (username, passkey, role, last_login)
    VALUES(arg_username, arg_passkey, arg_p_role, date_time);
END;
$$ language plpgsql;

-- 2. deleteAccount

CREATE OR REPLACE FUNCTION deleteAccount(
    arg_account_id INT
) RETURNS VOID as $$
BEGIN
    DELETE FROM olympics.ACCOUNT WHERE account_id = arg_account_id;
END;
$$ language plpgsql;


-- 3. addParticipant

CREATE OR REPLACE FUNCTION addParticipant(
    arg_account_id INT,
    arg_fname VARCHAR(30),
    arg_middle VARCHAR(30),
    arg_lname VARCHAR(30),
    arg_country CHAR(3),
    arg_dob TIMESTAMP,
    arg_gender olympics.athlete_gender
) RETURNS VOID AS $$
BEGIN
    INSERT INTO olympics.PARTICIPANT (account, first_name, middle_name, last_name, birth_country, dob, gender)
    VALUES (arg_account_id, arg_fname, arg_middle, arg_lname, arg_country, arg_dob, arg_gender);
END;
$$ language plpgsql;

--4. deleteParticipant
CREATE OR REPLACE FUNCTION deleteParticipant(
    arg_participant_id INT
) RETURNS VOID as $$
BEGIN
    DELETE FROM olympics.PARTICIPANT WHERE participant_id = arg_participant_id;
END;
$$ language plpgsql;


--5. addTeamMember

CREATE OR REPLACE FUNCTION addTeamMember(
    arg_participant_id INT,
    arg_team_id INT
) RETURNS VOID as $$
BEGIN
    INSERT INTO olympics.TEAM_MEMBERS (team, participant) VALUES (arg_team_id, arg_participant_id);
END;
$$ language plpgsql;

--6. removeTeamMember

CREATE OR REPLACE FUNCTION removeTeamMember(
    arg_participant_id INT,
    arg_team_id INT
) RETURNS VOID as $$
BEGIN
    DELETE FROM olympics.TEAM_MEMBERS  WHERE team = arg_team_id AND (participant_id = arg_participant_id);
END;
$$ language plpgsql;

-- 7. registerTeam

CREATE OR REPLACE FUNCTION registerTeam(
    arg_olympiad_id VARCHAR(30),
    arg_sport_id INT,
    arg_coach_id INT,
    arg_country_id CHAR(3),
    arg_gender olympics.team_gender
) RETURNS VOID AS $$
BEGIN
    INSERT INTO olympics.TEAM (olympiad, sport, coach, country, gender, eligible)
    VALUES (arg_olympiad_id, arg_sport_id, arg_coach_id, arg_country_id, arg_gender, TRUE);
END;
$$ language plpgsql;

--8. addEvent

CREATE OR REPLACE FUNCTION addEvent(
    arg_venue_id INT,
    arg_olympiad_id VARCHAR(30),
    arg_sport_id INT,
    arg_gender olympics.team_gender,
    arg_date TIMESTAMP
) RETURNS VOID AS $$
BEGIN
    INSERT INTO olympics.EVENT (venue, olympiad, sport, gender, date)
    VALUES (arg_venue_id, arg_olympiad_id, arg_sport_id, arg_gender, arg_date);
END;
$$ language plpgsql;

--9. addTeamToEvent

CREATE OR REPLACE FUNCTION addTeamToEvent(
    arg_event_id INT,
    arg_team_id INT
) RETURNS VOID as $$
BEGIN
    INSERT INTO olympics.PLACEMENT (event, team, medal, position)
    VALUES (arg_event_id, arg_team_id, NULL, NULL);
END;
$$ language plpgsql;

-- 10. addEventOutcome

CREATE OR REPLACE FUNCTION addEventOutcome(
    arg_event_id INT,
    arg_team_id INT,
    arg_position INT
) RETURNS VOID as $$
BEGIN
    INSERT INTO olympics.PLACEMENT (event, team, medal, position)
    VALUES (arg_event_id, arg_team_id, NULL, arg_position);
END;
$$ language plpgsql;

--11. disqualifyTeam

CREATE OR REPLACE FUNCTION disqualifyTeam(
    arg_team_id INT
) RETURNS VOID as $$
BEGIN
    UPDATE olympics.PLACEMENT
    SET position = -1
    WHERE team = arg_team_id;
END;
$$ language plpgsql;

--12. listVenuesInOlympiad


CREATE OR REPLACE FUNCTION listVenuesInOlympiad(
    arg_olympiad_id INT
) RETURNS TABLE (
    venue_name VARCHAR(30),
    capacity INT
) as $$
BEGIN
    RETURN QUERY (
        SELECT olympics.VENUE.venue_name, olympics.VENUE.capacity
        FROM olympics.VENUE
        JOIN olympics.EVENT ON olympics.VENUE.venue_name = olympics.EVENT.venue
        WHERE olympics.EVENT.olympiad = arg_olympiad_id
    );
END;
$$ language plpgsql;


--13. listEventsOfOlympiad

CREATE OR REPLACE FUNCTION listEventsOfOlympiad(
    arg_olympiad_id VARCHAR(30)
) RETURNS TABLE(
    event_id INT,
    venue INT,
    olympiad VARCHAR(30),
    sport INT,
    gender olympics.team_gender,
    date TIMESTAMP

) as $$
BEGIN
    RETURN QUERY (SELECT *
                  FROM olympics.EVENT
                  WHERE olympiad = arg_olympiad_id
                  );
END;
$$ language plpgsql;

--14. listTeamsInEvent
CREATE OR REPLACE FUNCTION listTeamsInEvent(
    arg_event_id INT
) RETURNS TABLE(
    team_id INT
) as $$
BEGIN
    RETURN QUERY (
        SELECT team
        FROM olympics.PLACEMENT
        WHERE event = arg_event_id
 );
END;
$$ language plpgsql;

--15. showPlacementsInEvent
CREATE OR REPLACE FUNCTION showPlacementsInEvent(
    arg_event_id INT
) RETURNS TABLE(
    team_id INT
) as $$
BEGIN
    RETURN QUERY (
        SELECT team, position
        FROM olympics.PLACEMENT
        WHERE event = arg_event_id
 );
END;
$$ language plpgsql;

-- 16. listParticipantsOnTeam

CREATE OR REPLACE FUNCTION listParticipantsOnTeam(
    arg_team_id INT
) RETURNS TABLE(
    participant_id INT,
    account INT,
    first VARCHAR(30),
    middle VARCHAR(30),
    last VARCHAR(30),
    birth_country CHAR(3),
    dob TIMESTAMP,
    gender athlete_gender
) AS $$

BEGIN
    RETURN QUERY
    SELECT olympics.participant.participant_id, olympics.participant.account,
           olympics.participant.first, olympics.participant.middle, olympics.participant.last,
           olympics.participant.birth_country, olympics.participant.dob, olympics.participant.gender
    FROM olympics.team_members LEFT JOIN olympics.participant
    ON olympics.team_members.participant = olympics.participant.participant_id
    AND olympics.team_members.team = arg_team_id;
END;
$$ language plpgsql;