1 files changed, 47 insertions, 0 deletions
diff --git a/forged/sql/queries/groups.sql b/forged/sql/queries/groups.sql
new file mode 100644
index 0000000..f067aeb
--- /dev/null
+++ b/forged/sql/queries/groups.sql
@@ -0,0 +1,47 @@
+-- name: GetRootGroups :many
+SELECT name, COALESCE(description, '') FROM groups WHERE parent_group IS NULL;
+
+-- name: GetGroupByPath :one
+WITH RECURSIVE group_path_cte AS (
+	SELECT
+		id,
+		parent_group,
+		name,
+		1 AS depth
+	FROM groups
+	WHERE name = ($1::text[])[1]
+		AND parent_group IS NULL
+
+	UNION ALL
+
+	SELECT
+		g.id,
+		g.parent_group,
+		g.name,
+		group_path_cte.depth + 1
+	FROM groups g
+	JOIN group_path_cte ON g.parent_group = group_path_cte.id
+	WHERE g.name = ($1::text[])[group_path_cte.depth + 1]
+		AND group_path_cte.depth + 1 <= cardinality($1::text[])
+)
+SELECT
+	g.id,
+	g.name,
+	g.parent_group,
+	COALESCE(g.description, '') AS description,
+	EXISTS (
+		SELECT 1
+		FROM user_group_roles ugr
+		WHERE ugr.user_id = $2
+			AND ugr.group_id = g.id
+	) AS has_role
+FROM group_path_cte c
+JOIN groups g ON g.id = c.id
+WHERE c.depth = cardinality($1::text[]);
+
+
+-- name: GetReposInGroup :many
+SELECT name, COALESCE(description, '') FROM repos WHERE group_id = $1;
+
+-- name: GetSubgroups :many
+SELECT name, COALESCE(description, '') FROM groups WHERE parent_group = $1;