countdown-bot

commit 3b2ff9bc791e33765ef01839030aa51b86afafa0
parent eeda1b569470a75223f1035d9dace10f3dc00acf
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date:   Sun, 21 Apr 2024 14:53:47 -0700

Implement prefixes table

Diffstat:
M
models/tables.sql
 | 
9
+++++++++
M
models/utilities.sql
 | 
45
++++++++++++++++++++++++++++++++++++++++-----

2 files changed, 49 insertions(+), 5 deletions(-)
diff --git a/models/tables.sql b/models/tables.sql
@@ -1,5 +1,6 @@
 -- countdown-bot tabe definitions
 
+DROP TABLE IF EXISTS prefixes;
 DROP TABLE IF EXISTS messages;
 DROP TABLE IF EXISTS countdowns;
 
@@ -18,3 +19,11 @@ CREATE TABLE messages (
     timestamp TIMESTAMPTZ NOT NULL, -- The message timestamp
     FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
 );
+
+-- Records bot command prefixes
+CREATE table prefixes (
+    prefixID SERIAL PRIMARY KEY, -- The prefix ID
+    countdownID INT NOT NULL,    -- The countdown ID
+    value VARCHAR(8) NOT NULL,   -- The prefix
+    FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
+);
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -2,6 +2,7 @@
 
 DROP PROCEDURE IF EXISTS getUserContextCountdown;
 DROP PROCEDURE IF EXISTS getServerContextCountdown;
+DROP FUNCTION IF EXISTS getPrefixes;
 DROP PROCEDURE IF EXISTS addMessage;
 DROP TYPE IF EXISTS addMessageResults;
 
@@ -62,11 +63,43 @@ BEGIN
 END
 $$;
 
+-- Get the active prefixes for a server
+CREATE FUNCTION getPrefixes (
+    _serverID IN INT, -- The server ID
+    channelID IN INT  -- The channel ID
+)
+RETURNS TABLE (
+    prefix VARCHAR(8) -- An active prefix
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+    IF EXISTS(
+        SELECT 1
+        FROM countdowns
+        WHERE countdownID = channelID
+    ) THEN
+        -- Filter prefixes if channel is a countdown
+        RETURN QUERY
+        SELECT value
+        FROM prefixes
+        WHERE prefixes.countdownID = channelID;
+    ELSE
+        -- Return all server prefixes if channel is not a countdown
+        RETURN QUERY
+        SELECT DISTINCT value
+        FROM prefixes
+        JOIN countdowns ON countdowns.countdownID = prefixes.countdownID
+        WHERE countdowns.serverID = _serverID;
+    END IF;
+END
+$$;
+
 -- Get the most relevant countdown to a server channel
 CREATE PROCEDURE getServerContextCountdown (
-    _serverID IN INT,   -- The server ID
-    channelID IN INT,   -- The channel ID
-    countdownID OUT INT -- The ID of the most relevant countdown
+    _serverID IN INT,     -- The server ID
+    channelID IN INT,     -- The channel ID
+    prefix IN VARCHAR(8), -- The prefix used to invoke the bot
+    countdownID OUT INT   -- The ID of the most relevant countdown
 )
 LANGUAGE plpgsql AS $$
 BEGIN
@@ -78,12 +111,14 @@ BEGIN
 
     UNION ALL
     (
-        -- Get server countdowns sorted by most recent activity
+        -- Get server countdowns by prefix sorted by most recent activity
         SELECT countdowns.countdownID
         FROM countdowns
         LEFT OUTER JOIN messages
             ON messages.countdownID = countdowns.countdownID
-        WHERE serverID = _serverID
+        JOIN prefixes
+            ON prefixes.countdownID = countdowns.countdownID
+        WHERE serverID = _serverID AND prefixes.value = prefix
         GROUP BY countdowns.countdownID
         ORDER BY max(messages.timestamp) DESC NULLS LAST
     )