Added position to networks

3 files changed, 52 insertions, 40 deletions

diff --git a/query/networks.sql b/query/networks.sql
index b2720b7..64f56dc 100644
--- a/query/networks.sql
+++ b/query/networks.sql
@@ -16,24 +16,6 @@ INSERT INTO networks (
 )
 RETURNING *;
 
--- name: GetNetworkBannedUsers :many
-SELECT
-  sqlc.embed(users),
-  users_networks.ban_reason
-FROM users_networks
-JOIN users ON users.id = users_networks.user_id
-WHERE users_networks.network_id = ?;
-
--- name: GetNetworkMembers :many
-SELECT
-  sqlc.embed(users),
-  users_networks.joined_at,
-  users_networks.is_admin,
-  users_networks.is_muted
-FROM users_networks
-JOIN users ON users.id = users_networks.user_id
-WHERE users_networks.network_id = ? AND is_member = true;
-
 -- name: SetNetworkName :one
 UPDATE networks SET
   name = ?
@@ -62,20 +44,3 @@ RETURNING *;
 
 -- name: DeleteNetwork :exec
 DELETE FROM networks WHERE id = ?;
-
--- name: SetNetworkUser :one
-INSERT INTO users_networks (
-  user_id, network_id,
-  is_member, is_admin, is_muted,
-  is_banned, ban_reason
-) VALUES (
-  ?1, ?2,
-  ?3, ?4, ?5,
-  ?6, ?7
-)
-ON CONFLICT DO 
-UPDATE SET
-  is_member = ?3, is_admin = ?4, is_muted = ?5,
-  is_banned = ?6, ban_reason = ?7
-WHERE user_id = ?1 AND network_id = ?2
-RETURNING *;
diff --git a/query/users.sql b/query/users.sql
index 2528513..d136f15 100644
--- a/query/users.sql
+++ b/query/users.sql
@@ -46,8 +46,3 @@ RETURNING *;
 UPDATE users SET
   is_deleted = true
 WHERE id = ? AND is_deleted = false;
-
--- name: GetUserNetworks :many
-SELECT networks.* FROM networks
-JOIN users_networks ON networks.id = users_networks.network_id
-WHERE users_networks.user_id = ?;
diff --git a/query/users_networks.sql b/query/users_networks.sql
new file mode 100644
index 0000000..f467753
--- /dev/null
+++ b/query/users_networks.sql
@@ -0,0 +1,52 @@
+-- name: GetNetworkBannedUsers :many
+SELECT
+  sqlc.embed(users),
+  users_networks.ban_reason
+FROM users_networks
+JOIN users ON users.id = users_networks.user_id
+WHERE users_networks.network_id = ?;
+
+-- name: GetNetworkMembers :many
+SELECT
+  sqlc.embed(users),
+  users_networks.joined_at,
+  users_networks.is_admin,
+  users_networks.is_muted
+FROM users_networks
+JOIN users ON users.id = users_networks.user_id
+WHERE users_networks.network_id = ? AND is_member = true;
+
+-- name: GetUserNetworks :many
+SELECT sqlc.embed(networks), users_networks.position FROM networks
+JOIN users_networks ON networks.id = users_networks.network_id
+WHERE users_networks.user_id = ?
+ORDER BY users_networks.position;
+
+-- name: SetNetworkUser :one
+INSERT INTO users_networks (
+  user_id, network_id,
+  is_member, is_admin, is_muted,
+  is_banned, ban_reason, position
+) VALUES (
+  @user_id, @network_id,
+  @is_member, @is_admin, @is_muted,
+  @is_banned, @ban_reason,
+  CASE
+    WHEN @is_member = false THEN NULL
+    ELSE (SELECT COUNT(*) FROM users_networks WHERE user_id = @user_id)
+  END
+)
+ON CONFLICT DO 
+UPDATE SET
+  is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted,
+  is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position
+WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id
+RETURNING *;
+
+-- name: SwapUserNetworks :exec
+UPDATE users_networks SET
+  position = CASE
+    WHEN position = @pos1 THEN @pos2
+    WHEN position = @pos2 THEN @pos1
+  END
+WHERE user_id = @user_id AND position IN (@pos1, @pos2);