*: Support subgroups via SQL recursion

1 files changed, 37 insertions, 5 deletions

diff --git a/http_handle_repo_info.go b/http_handle_repo_info.go
index d8227c9..466e0bb 100644
--- a/http_handle_repo_info.go
+++ b/http_handle_repo_info.go
@@ -8,15 +8,47 @@ import (
 	"io"
 	"net/http"
 	"os/exec"
+
+	"github.com/jackc/pgx/v5/pgtype"
 )
 
 func handle_repo_info(w http.ResponseWriter, r *http.Request, params map[string]any) (err error) {
-	var group_name, repo_name, repo_path string
+	var group_path []string
+	var repo_name, repo_path string
 
-	group_name, repo_name = params["group_name"].(string), params["repo_name"].(string)
-	if err = database.QueryRow(r.Context(),
-		"SELECT r.filesystem_path FROM repos r JOIN groups g ON r.group_id = g.id WHERE g.name = $1 AND r.name = $2;",
-		group_name, repo_name,
+	if err := database.QueryRow(r.Context(), `
+	WITH RECURSIVE group_path_cte AS (
+		-- Start: match the first name in the path where parent_group IS NULL
+		SELECT
+			id,
+			parent_group,
+			name,
+			1 AS depth
+		FROM groups
+		WHERE name = ($1::text[])[1]
+			AND parent_group IS NULL
+	
+		UNION ALL
+	
+		-- Recurse: jion next segment of the path
+		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 r.filesystem_path
+	FROM group_path_cte c
+	JOIN repos r ON r.group_id = c.id
+	WHERE c.depth = cardinality($1::text[])
+		AND r.name = $2
+	`,
+		pgtype.FlatArray[string](group_path),
+		repo_name,
 	).Scan(&repo_path); err != nil {
 		return err
 	}