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

are broken

1 files changed, 24 insertions, 0 deletions

diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql
new file mode 100644
index 0000000..a444af1
--- /dev/null
+++ b/internal/server/api/migrations/20250109135557_frequencies.sql
@@ -0,0 +1,24 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS frequencies (
+  id INTEGER PRIMARY KEY,
+  network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE,
+  name TEXT NOT NULL,
+  hex_color TEXT NOT NULL,
+  perms INT NOT NULL CHECK (perms IN (0, 1, 2)),
+  -- 0 no access | 1 read | 2 read & write
+  position INT NOT NULL
+);
+
+-- +goose StatementBegin
+CREATE TRIGGER IF NOT EXISTS on_frequency_delete
+AFTER DELETE ON frequencies
+BEGIN
+  UPDATE frequencies SET
+    position = position - 1
+  WHERE network_id = OLD.network_id AND position > OLD.position;
+END
+-- +goose StatementEnd
+
+-- +goose Down
+DROP TABLE IF EXISTS frequencies;
+DROP TRIGGER IF EXISTS on_frequency_delete;