Changes between Version 9 and Version 10 of DatabaseProgramming

Timestamp:

05/30/26 17:25:41 (2 weeks ago)

Author:

231070

Comment:

--

DatabaseProgramming

@@ -155 +155 @@
         SELECT
             uw.app_userid,
-            ROW_NUMBER() OVER (ORDER BY w.created_at ASC) AS pozicija
+            ROW_NUMBER() OVER (ORDER BY uw.joined_at ASC) AS pozicija
         FROM waitlist w
         JOIN user_waitlist uw ON uw.waitlistid = w.id
@@ -481 +481 @@
     RETURNING id INTO v_waitlist_id;
 
-    INSERT INTO user_waitlist (app_userid, waitlistid)
-    VALUES (p_userid, v_waitlist_id);
+    INSERT INTO user_waitlist (app_userid, waitlistid, joined_at)
+    VALUES (p_userid, v_waitlist_id, CURRENT_DATE);
 
     INSERT INTO notification (message, created_at, app_userid)
@@ -553 +553 @@
 DECLARE
     v_waitlist_id BIGINT;
+    v_userid      BIGINT;
 BEGIN
     IF NEW.status = 'CANCELLED' AND OLD.status != 'CANCELLED' THEN
-        SELECT w.id INTO v_waitlist_id
+
+        SELECT w.id, uw.app_userid INTO v_waitlist_id, v_userid
         FROM waitlist w
+        JOIN user_waitlist uw ON uw.waitlistid = w.id
         WHERE w.eventid = NEW.eventid AND w.status = 'PENDING'
-        ORDER BY w.created_at
+        ORDER BY uw.joined_at ASC
         LIMIT 1;
 
         IF v_waitlist_id IS NOT NULL THEN
-            UPDATE waitlist SET status = 'CONFIRMED'
+            UPDATE waitlist
+            SET status = 'CONFIRMED'
             WHERE id = v_waitlist_id;
+
+            INSERT INTO notification (message, created_at, app_userid)
+            VALUES ('A spot has opened up! Your waitlist request has been confirmed.', 
+                    CURRENT_DATE, v_userid);
 
             RAISE NOTICE 'Waitlist % e potvrden za event %', v_waitlist_id, NEW.eventid;