Added frequency queries

9 files changed, 202 insertions, 38 deletions

diff --git a/internal/data/frequencies.sql.go b/internal/data/frequencies.sql.go
index d978f1f..ae7cd43 100644
--- a/internal/data/frequencies.sql.go
+++ b/internal/data/frequencies.sql.go
@@ -13,9 +13,12 @@ import (
 
 const createFrequency = `-- name: CreateFrequency :one
 INSERT INTO frequencies (
-  id, network_id, name
+  id, network_id,
+  name, hex_color,
+  perms, position
 ) VALUES (
-  ?, ?, ?
+  ?, ?, ?, ?, ?,
+  (SELECT COUNT(*) FROM frequencies WHERE network_id = ?)
 )
 RETURNING id, network_id, name, hex_color, perms, position
 `
@@ -24,10 +27,18 @@ type CreateFrequencyParams struct {
 	ID        snowflake.ID
 	NetworkID snowflake.ID
 	Name      string
+	HexColor  *string
+	Perms     int64
 }
 
 func (q *Queries) CreateFrequency(ctx context.Context, arg CreateFrequencyParams) (Frequency, error) {
-	row := q.db.QueryRowContext(ctx, createFrequency, arg.ID, arg.NetworkID, arg.Name)
+	row := q.db.QueryRowContext(ctx, createFrequency,
+		arg.ID,
+		arg.NetworkID,
+		arg.Name,
+		arg.HexColor,
+		arg.Perms,
+	)
 	var i Frequency
 	err := row.Scan(
 		&i.ID,
@@ -40,6 +51,15 @@ func (q *Queries) CreateFrequency(ctx context.Context, arg CreateFrequencyParams
 	return i, err
 }
 
+const deleteFrequency = `-- name: DeleteFrequency :exec
+DELETE FROM frequencies WHERE id = ?
+`
+
+func (q *Queries) DeleteFrequency(ctx context.Context, id snowflake.ID) error {
+	_, err := q.db.ExecContext(ctx, deleteFrequency, id)
+	return err
+}
+
 const getNetworkFrequencies = `-- name: GetNetworkFrequencies :many
 SELECT id, network_id, name, hex_color, perms, position FROM frequencies
 WHERE network_id = ?
@@ -75,3 +95,23 @@ func (q *Queries) GetNetworkFrequencies(ctx context.Context, networkID snowflake
 	}
 	return items, nil
 }
+
+const swapFrequencies = `-- name: SwapFrequencies :exec
+UPDATE frequencies SET
+  position = CASE
+    WHEN position = ? THEN ?
+    WHEN position = ? THEN ?
+  END
+WHERE network_id = ? AND position IN (?, ?)
+`
+
+type SwapFrequenciesParams struct {
+	Pos1      int64
+	Pos2      int64
+	NetworkID snowflake.ID
+}
+
+func (q *Queries) SwapFrequencies(ctx context.Context, arg SwapFrequenciesParams) error {
+	_, err := q.db.ExecContext(ctx, swapFrequencies, arg.Pos1, arg.Pos2, arg.NetworkID)
+	return err
+}
diff --git a/internal/data/models.go b/internal/data/models.go
index 193b0f5..fab7489 100644
--- a/internal/data/models.go
+++ b/internal/data/models.go
@@ -49,7 +49,7 @@ type User struct {
 	Name        string
 	PublicKey   ed25519.PublicKey
 	Description *string
-	IsPublicDm  bool
+	IsPublicDM  bool
 	IsDeleted   bool
 }
 
diff --git a/internal/data/networks.sql.go b/internal/data/networks.sql.go
index 5a9457a..0752609 100644
--- a/internal/data/networks.sql.go
+++ b/internal/data/networks.sql.go
@@ -11,8 +11,63 @@ import (
 	"github.com/kyren223/eko/pkg/snowflake"
 )
 
+const createNetwork = `-- name: CreateNetwork :one
+INSERT INTO networks (
+  id, owner_id, name, is_public,
+  icon, bg_hex_color, fg_hex_color
+) VALUES (
+  ?, ?, ?, ?, ?, ?, ?
+)
+RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public
+`
+
+type CreateNetworkParams struct {
+	ID         snowflake.ID
+	OwnerID    snowflake.ID
+	Name       string
+	IsPublic   bool
+	Icon       string
+	BgHexColor *string
+	FgHexColor string
+}
+
+func (q *Queries) CreateNetwork(ctx context.Context, arg CreateNetworkParams) (Network, error) {
+	row := q.db.QueryRowContext(ctx, createNetwork,
+		arg.ID,
+		arg.OwnerID,
+		arg.Name,
+		arg.IsPublic,
+		arg.Icon,
+		arg.BgHexColor,
+		arg.FgHexColor,
+	)
+	var i Network
+	err := row.Scan(
+		&i.ID,
+		&i.OwnerID,
+		&i.Name,
+		&i.Icon,
+		&i.BgHexColor,
+		&i.FgHexColor,
+		&i.IsPublic,
+	)
+	return i, err
+}
+
+const deleteNetwork = `-- name: DeleteNetwork :exec
+DELETE FROM networks WHERE id = ?
+`
+
+func (q *Queries) DeleteNetwork(ctx context.Context, id snowflake.ID) error {
+	_, err := q.db.ExecContext(ctx, deleteNetwork, id)
+	return err
+}
+
 const getBannedUsersInNetwork = `-- name: GetBannedUsersInNetwork :many
-SELECT users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted, network_banned_users.banned_at, network_banned_users.reason
+SELECT
+  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted,
+  network_banned_users.banned_at,
+  network_banned_users.reason
 FROM network_banned_users
 JOIN users ON users.id = network_banned_users.banned_user_id
 WHERE network_banned_users.network_id = ?
@@ -38,7 +93,7 @@ func (q *Queries) GetBannedUsersInNetwork(ctx context.Context, networkID snowfla
 			&i.User.Name,
 			&i.User.PublicKey,
 			&i.User.Description,
-			&i.User.IsPublicDm,
+			&i.User.IsPublicDM,
 			&i.User.IsDeleted,
 			&i.BannedAt,
 			&i.Reason,
@@ -111,3 +166,54 @@ func (q *Queries) GetPublicNetworks(ctx context.Context) ([]Network, error) {
 	}
 	return items, nil
 }
+
+const getUsersInNetwork = `-- name: GetUsersInNetwork :many
+SELECT
+  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted,
+  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 = ?
+`
+
+type GetUsersInNetworkRow struct {
+	User     User
+	JoinedAt string
+	IsAdmin  bool
+	IsMuted  bool
+}
+
+func (q *Queries) GetUsersInNetwork(ctx context.Context, networkID snowflake.ID) ([]GetUsersInNetworkRow, error) {
+	rows, err := q.db.QueryContext(ctx, getUsersInNetwork, networkID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetUsersInNetworkRow
+	for rows.Next() {
+		var i GetUsersInNetworkRow
+		if err := rows.Scan(
+			&i.User.ID,
+			&i.User.Name,
+			&i.User.PublicKey,
+			&i.User.Description,
+			&i.User.IsPublicDM,
+			&i.User.IsDeleted,
+			&i.JoinedAt,
+			&i.IsAdmin,
+			&i.IsMuted,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go
index 9728c21..4411a80 100644
--- a/internal/data/users.sql.go
+++ b/internal/data/users.sql.go
@@ -35,31 +35,21 @@ func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, e
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
 }
 
-const deleteUser = `-- name: DeleteUser :one
+const deleteUser = `-- name: DeleteUser :exec
 UPDATE users SET
   is_deleted = true
 WHERE id = ? AND is_deleted = false
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
 `
 
-func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) (User, error) {
-	row := q.db.QueryRowContext(ctx, deleteUser, id)
-	var i User
-	err := row.Scan(
-		&i.ID,
-		&i.Name,
-		&i.PublicKey,
-		&i.Description,
-		&i.IsPublicDm,
-		&i.IsDeleted,
-	)
-	return i, err
+func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) error {
+	_, err := q.db.ExecContext(ctx, deleteUser, id)
+	return err
 }
 
 const getDeletedUserById = `-- name: GetDeletedUserById :one
@@ -75,7 +65,7 @@ func (q *Queries) GetDeletedUserById(ctx context.Context, id snowflake.ID) (User
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -94,7 +84,7 @@ func (q *Queries) GetUserById(ctx context.Context, id snowflake.ID) (User, error
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -113,7 +103,7 @@ func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.Publ
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -139,7 +129,7 @@ func (q *Queries) SetUserDescription(ctx context.Context, arg SetUserDescription
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -165,7 +155,7 @@ func (q *Queries) SetUserName(ctx context.Context, arg SetUserNameParams) (User,
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -179,19 +169,19 @@ RETURNING id, name, public_key, description, is_public_dm, is_deleted
 `
 
 type SetUserPublicDMsParams struct {
-	IsPublicDm bool
+	IsPublicDM bool
 	ID         snowflake.ID
 }
 
 func (q *Queries) SetUserPublicDMs(ctx context.Context, arg SetUserPublicDMsParams) (User, error) {
-	row := q.db.QueryRowContext(ctx, setUserPublicDMs, arg.IsPublicDm, arg.ID)
+	row := q.db.QueryRowContext(ctx, setUserPublicDMs, arg.IsPublicDM, arg.ID)
 	var i User
 	err := row.Scan(
 		&i.ID,
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
@@ -217,7 +207,7 @@ func (q *Queries) SetUserPublicKey(ctx context.Context, arg SetUserPublicKeyPara
 		&i.Name,
 		&i.PublicKey,
 		&i.Description,
-		&i.IsPublicDm,
+		&i.IsPublicDM,
 		&i.IsDeleted,
 	)
 	return i, err
diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql
index 49b840e..233ceba 100644
--- a/migrations/20241026174345_init.sql
+++ b/migrations/20241026174345_init.sql
@@ -91,6 +91,16 @@ BEGIN
 END
 -- +goose StatementEnd
 
+-- +goose StatementBegin
+CREATE TRIGGER 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 messages;
 DROP TABLE users;
diff --git a/query/frequencies.sql b/query/frequencies.sql
index ed68c78..959512e 100644
--- a/query/frequencies.sql
+++ b/query/frequencies.sql
@@ -5,8 +5,22 @@ ORDER BY id;
 
 -- name: CreateFrequency :one
 INSERT INTO frequencies (
-  id, network_id, name
+  id, network_id,
+  name, hex_color,
+  perms, position
 ) VALUES (
-  ?, ?, ?
+  ?, @network_id, ?, ?, ?,
+  (SELECT COUNT(*) FROM frequencies WHERE network_id = @network_id)
 )
 RETURNING *;
+
+-- name: SwapFrequencies :exec
+UPDATE frequencies SET
+  position = CASE
+    WHEN position = @pos1 THEN @pos2
+    WHEN position = @pos2 THEN @pos1
+  END
+WHERE network_id = ? AND position IN (@pos1, @pos2);
+
+-- name: DeleteFrequency :exec
+DELETE FROM frequencies WHERE id = ?;
diff --git a/query/networks.sql b/query/networks.sql
index 424bb2d..2253de4 100644
--- a/query/networks.sql
+++ b/query/networks.sql
@@ -6,7 +6,7 @@ WHERE is_public = true;
 SELECT * FROM networks
 WHERE id = ?;
 
--- -- name: CreateNetwork :one
+-- name: CreateNetwork :one
 INSERT INTO networks (
   id, owner_id, name, is_public,
   icon, bg_hex_color, fg_hex_color
@@ -16,7 +16,7 @@ INSERT INTO networks (
 RETURNING *;
 
 -- name: GetBannedUsersInNetwork :many
-SELECT 
+SELECT
   sqlc.embed(users),
   network_banned_users.banned_at,
   network_banned_users.reason
@@ -26,10 +26,13 @@ WHERE network_banned_users.network_id = ?;
 
 -- name: GetUsersInNetwork :many
 SELECT
-  sqlc.embed(users.),
+  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 = ?;
+
+-- name: DeleteNetwork :exec
+DELETE FROM networks WHERE id = ?;
diff --git a/query/users.sql b/query/users.sql
index f3629ca..d136f15 100644
--- a/query/users.sql
+++ b/query/users.sql
@@ -42,8 +42,7 @@ UPDATE users SET
 WHERE id = ? AND is_deleted = false
 RETURNING *;
 
--- name: DeleteUser :one
+-- name: DeleteUser :exec
 UPDATE users SET
   is_deleted = true
-WHERE id = ? AND is_deleted = false
-RETURNING *;
+WHERE id = ? AND is_deleted = false;
diff --git a/sqlc.yml b/sqlc.yml
index d48d5c2..a188a02 100644
--- a/sqlc.yml
+++ b/sqlc.yml
@@ -19,3 +19,5 @@ sql:
             go_type: "github.com/kyren223/eko/pkg/snowflake.ID"
           - column: "*.*_id"
             go_type: "github.com/kyren223/eko/pkg/snowflake.ID"
+        rename:
+          is_public_dm: "IsPublicDM"