countdown-bot

commit 817e612be7ea6d06d1daa50b53c86ad6066a8c85
parent b0bcb33e4298c0ebe58fd7dfab0d9a63a0ca5b09
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date:   Sun,  7 Apr 2024 10:12:42 -0700

Implement addMessage procedure

Diffstat:
M
models/ddl.sql
 | 
51
+++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 51 insertions(+), 0 deletions(-)
diff --git a/models/ddl.sql b/models/ddl.sql
@@ -1,3 +1,5 @@
+DROP PROCEDURE IF EXISTS addMessage;
+DROP TYPE IF EXISTS addMessageResults;
 DROP TABLE IF EXISTS messages;
 DROP TABLE IF EXISTS countdowns;
 
@@ -13,3 +15,52 @@ CREATE TABLE messages (
     value INT NOT NULL,
     timestamp TIMESTAMP NOT NULL
 );
+
+CREATE TYPE addMessageResults AS ENUM (
+    'badCountdown', 'badNumber', 'badUser', 'good'
+);
+
+-- Validate and add a new countdown message
+CREATE PROCEDURE addMessage
+    (_messageID IN INT, _countdownID IN INT, _userID IN INT, _value IN INT,
+    _timestamp IN TIMESTAMP, result OUT addMessageResults)
+LANGUAGE plpgsql AS $$
+DECLARE
+    lastMessage record;
+BEGIN
+    -- Get last countdown message
+    SELECT countdowns.countdownID, messageID, userID, value, timestamp
+        INTO lastMessage
+        FROM countdowns
+
+        -- Still return a row if the countdown is empty
+        LEFT OUTER JOIN messages
+            ON messages.countdownID = countdowns.countdownID
+
+        WHERE countdowns.countdownID = _countdownID
+        ORDER BY messages.value ASC
+        LIMIT 1;
+
+    -- Validate message
+    IF lastMessage.countdownID IS NULL OR lastMessage.value = 0 THEN
+        -- Countdown doesn't exist or has ended
+        result := 'badCountdown';
+
+    ELSEIF lastMessage.value IS NOT NULL AND
+        lastMessage.value != _value + 1 THEN
+        -- Message contains the wrong number
+        result := 'badNumber';
+
+    ELSEIF lastMessage.userID = _userID THEN
+        -- User sent consecutive messages
+        result := 'badUser';
+
+    ELSE
+        -- Message is valid, insert it into messages
+        INSERT INTO messages
+            (messageID, userID, countdownID, value, timestamp)
+            VALUES (_messageID, _userID, _countdownID, _value, _timestamp);
+        result := 'good';
+    END IF;
+END
+$$