Added message queries

2 files changed, 17 insertions, 1 deletions

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 = ?;