From c77ac77e78268bc2f4265a872b213d4300b3eb66 Mon Sep 17 00:00:00 2001
From: Kyren223 <Kyren223@proton.me>
Date: Sun, 2 Feb 2025 19:23:50 +0200
Subject: Added support for offline notifications where the server calculates
 them and sends the info to the client when it connects

---
 query/notifications.sql | 14 ++++++++++++++
 1 file changed, 14 insertions(+)
 create mode 100644 query/notifications.sql

(limited to 'query/notifications.sql')

diff --git a/query/notifications.sql b/query/notifications.sql
new file mode 100644
index 0000000..5522fe8
--- /dev/null
+++ b/query/notifications.sql
@@ -0,0 +1,14 @@
+-- name: GetNotifications :many
+WITH entries(source, lastId) AS (
+  VALUES
+    ('source1', 12345),
+    ('source2', 54321)
+)
+SELECT
+  e.source,
+  CASE WHEN COUNT(m.id) > 0 THEN 1 ELSE 0 END AS hasNotif,
+  COALESCE(SUM(CASE WHEN m.ping = ? THEN 1 ELSE 0 END), 0) AS pings
+FROM entries e
+LEFT JOIN messages m ON m.id > e.lastId
+  AND (m.s1 = e.source OR m.s2 = e.source OR m.s3 = e.source)
+GROUP BY e.source, e.lastId;
-- 
cgit v1.3.1