Added goose for migrations, refactored SQL schema, server and client

are broken

1 files changed, 18 insertions, 0 deletions

diff --git a/internal/server/api/migrations/20250109140401_messages.sql b/internal/server/api/migrations/20250109140401_messages.sql
new file mode 100644
index 0000000..92f75f4
--- /dev/null
+++ b/internal/server/api/migrations/20250109140401_messages.sql
@@ -0,0 +1,18 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS messages (
+  id INTEGER PRIMARY KEY,
+  sender_id INT NOT NULL REFERENCES users (id),
+  content TEXT NOT NULL,
+  edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false,
+
+  frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE,
+  receiver_id INT REFERENCES users (id),
+
+  CHECK (
+    (frequency_id IS NOT NULL AND receiver_id IS NULL) OR
+    (frequency_id IS NULL AND receiver_id IS NOT NULL)
+  )
+);
+
+-- +goose Down
+DROP TABLE IF EXISTS messages;