Queries

--finds what the current speedrun records are, from fastest to longest run, when looking at the game "Minecraft"

SELECT r.RunID, r.Time, r.UserSubmitted
FROM Runs r
JOIN Games g ON r.GameID = g.GameID
WHERE g.Name = 'Minecraft'
ORDER BY r.Time;

--finds the user that is #1 rank in the most games, probably not super useful but could be used for a profile award/badge maybe?
SELECT r.UserSubmitted, COUNT(*) as NumTopRanks
FROM Runs r
JOIN GameRuns gr ON r.RunID = gr.RunID
WHERE r.Ranking = 1
GROUP BY r.UserSubmitted
ORDER BY NumTopRanks DESC
LIMIT 1;

--Finda the top runs for Darksouls, minus any unverified runs, basically would show filtering of runs
SELECT r.RunID, r.Time, r.UserSubmitted, r.Category
FROM Runs r
JOIN Games g ON r.GameID = g.GameID
WHERE g.Name = 'Dark Souls' AND r.Verified = TRUE
ORDER BY r.Time
LIMIT 1;

--shows all games that have at least 1 run associated with them
SELECT g.GameID, g.Name
FROM Games g
JOIN Runs r ON g.GameID = r.GameID
GROUP BY g.GameID, g.Name
HAVING COUNT(r.RunID) >= 1
ORDER BY g.Name;

--comments on the top run for Cuphead
SELECT c.CommentID, c.Comment, cr.Username
FROM Comments c
JOIN Users u ON c.UserID = u.UserID
JOIN Credentials cr ON u.UserID = cr.UserID
JOIN Runs r ON c.RunID = r.RunID
JOIN Games g ON r.GameID = g.GameID
WHERE g.Name = 'Cuphead' AND r.RunID = SPECIFIC_RUN_ID;


--same as above, but for a specfic run (would be like selecting a run and seeing its comments)
SELECT c.CommentID, c.Comment, cr.Username
FROM Comments c
JOIN Users u ON c.UserID = u.UserID
JOIN Credentials cr ON u.UserID = cr.UserID
JOIN Runs r ON c.RunID = r.RunID
JOIN Games g ON r.GameID = g.GameID
WHERE g.Name = 'Cuphead' AND r.RunID = INSERT_SPECIFIC_RUN_ID;

--Transaction that will perform a ban on a user and update appropriate tables
--user_id and admin_id should be replaced by whoever was banned and what admin did the ban
BEGIN;

INSERT INTO BannedUsers (UserID, AdminID, BanExplanation, BanDuration)
VALUES (user_id, admin_id, 'Violation of community guidelines', INTERVAL '30 days');

UPDATE Administrators
SET NumUsersBanned = NumUsersBanned + 1
WHERE AdminID = admin_id;

DELETE FROM Users
WHERE UserID = user_id;

COMMIT;

--for an admin removing a comment
BEGIN;

DELETE FROM Runs WHERE RunID = run_id;

UPDATE Administrators
SET NumPostsRemoved = NumPostsRemoved + 1
WHERE AdminID = admin_id;

COMMIT;

--moderator removing a run
BEGIN;

DELETE FROM Runs WHERE RunID = run_id;

UPDATE Moderators
SET RemovedRuns = RemovedRuns + 1
WHERE ModID = mod_id;

COMMIT;

--moderator verifying a run
BEGIN;

-- Update the run's verified status
UPDATE Runs
SET Verified = TRUE
WHERE RunID = run_id;

-- Update the number of runs verified by the moderator
UPDATE Moderators
SET VerifiedRuns = VerifiedRuns + 1
WHERE ModID = mod_id;

COMMIT;