From 91bbd29dbe2738459cef83b5204844a2c136a84f Mon Sep 17 00:00:00 2001
From: Kyren223 <Kyren223@proton.me>
Date: Sat, 23 Nov 2024 22:41:28 +0200
Subject: Added message queries

---
 migrations/20241026174345_init.sql |  4 ++++
 query/messages.sql                 | 14 +++++++++++++-
 2 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql
index 233ceba..366d338 100644
--- a/migrations/20241026174345_init.sql
+++ b/migrations/20241026174345_init.sql
@@ -77,6 +77,8 @@ CREATE INDEX idx_network_name ON networks (name) WHERE is_public = 1;
 CREATE INDEX idx_frequency_network ON frequencies (network_id);
 CREATE INDEX idx_blocked_by_user ON user_blocked_users (blocker_user_id);
 CREATE INDEX idx_banned_by_network ON network_banned_users (network_id);
+CREATE INDEX idx_direct_messages ON messages (sender_id, receiver_id);
+CREATE INDEX idx_frequency_messages ON messages (frequency_id);
 
 -- +goose StatementBegin
 CREATE TRIGGER on_user_delete
@@ -115,3 +117,5 @@ DROP INDEX idx_network_name;
 DROP INDEX idx_frequency_network;
 DROP INDEX idx_blocked_by_user;
 DROP INDEX idx_banned_by_network;
+DROP INDEX idx_direct_messages;
+DROP INDEX idx_frequency_messages;
diff --git a/query/messages.sql b/query/messages.sql
index c345068..f7be629 100644
--- a/query/messages.sql
+++ b/query/messages.sql
@@ -1,5 +1,13 @@
--- name: ListMessages :many
+-- name: GetFrequencyMessages :many
 SELECT * FROM messages
+WHERE frequency_id = ?
+ORDER BY id;
+
+-- name: GetDirectMessages :many
+SELECT * FROM messages
+WHERE
+  (sender_id = @user1 AND receiver_id = @user2) OR 
+  (sender_id = @user2 AND receiver_id = @user1)
 ORDER BY id;
 
 -- name: CreateMessage :one
@@ -16,3 +24,7 @@ UPDATE messages SET
   content = ?
 WHERE id = ?
 RETURNING *;
+
+-- name: DeleteMessage :exec
+DELETE FROM messages
+WHERE id = ?;
-- 
cgit v1.3.1