From 3e9c0ee6a1e765d42d54acb71c10ae7e67353a17 Mon Sep 17 00:00:00 2001
From: Kyren223 <Kyren223@proton.me>
Date: Sun, 1 Dec 2024 15:47:54 +0200
Subject: Implemented frequency creation on both client and server

---
 migrations/20241026174345_init.sql | 18 ++++++++++++++++--
 1 file changed, 16 insertions(+), 2 deletions(-)

(limited to 'migrations/20241026174345_init.sql')

diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql
index 6fa0b93..e4f0f78 100644
--- a/migrations/20241026174345_init.sql
+++ b/migrations/20241026174345_init.sql
@@ -37,7 +37,7 @@ CREATE TABLE frequencies (
   id INTEGER PRIMARY KEY,
   network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE,
   name TEXT NOT NULL,
-  hex_color TEXT,
+  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
@@ -45,7 +45,7 @@ CREATE TABLE frequencies (
 
 CREATE TABLE users_networks (
   user_id INT NOT NULL REFERENCES users (id),
-  network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE,
+  network_id INT NOT NULL REFERENCES networks (id),
   joined_at TEXT NOT NULL DEFAULT current_timestamp,
   is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true,
   is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false,
@@ -99,6 +99,20 @@ BEGIN
 END
 -- +goose StatementEnd
 
+-- +goose StatementBegin
+CREATE TRIGGER on_network_delete
+AFTER DELETE ON networks
+BEGIN
+  UPDATE users_networks SET
+    position = position - 1
+  WHERE user_id IN (
+    SELECT user_id FROM users_networks WHERE network_id = OLD.network_id
+  ) AND position > OLD.position;
+  
+  DELETE FROM users_networks WHERE network_id = OLD.network_id;
+END
+-- +goose StatementEnd
+
 -- +goose Down
 DROP TABLE messages;
 DROP TABLE users;
-- 
cgit v1.3.1