-- name: FindRole :one
SELECT *
FROM roles
WHERE scope = $1 AND name = $2;

-- name: GetRolesGroupedWithPermissions :many
SELECT
    r.scope,
    json_agg (
        json_build_object (
            'id',
            r.id,
            'name',
            r.name,
            'scope',
            r.scope,
            'description',
            r.description,
            'permissions',
            COALESCE(p_list.permissions, '[]')
        )
        ORDER BY
            r.name
    ) AS roles
FROM
    roles r
    LEFT JOIN (
        SELECT
            rp.role_id,
            json_agg (
                json_build_object (
                    'id',
                    p.id,
                    'name',
                    p.name,
                    'scope',
                    p.scope,
                    'description',
                    p.description
                )
                ORDER BY
                    p.name
            ) AS permissions
        FROM
            role_permissions rp
            JOIN permissions p ON p.id = rp.permission_id
        GROUP BY
            rp.role_id
    ) p_list ON p_list.role_id = r.id
GROUP BY
    r.scope;

-- name: CreateRole :one
INSERT INTO roles (name, scope, description)
VALUES ($1, $2, $3)
RETURNING *;

-- name: GetRoleAssignment :one
SELECT * FROM role_permissions
WHERE role_id = $1 AND permission_id = (SELECT id FROM permissions p WHERE p.scope = split_part(sqlc.arg('key'), '_', 1) AND p.name = right(sqlc.arg('key'), length(sqlc.arg('key')) - position('_' IN sqlc.arg('key'))))
LIMIT 1;

-- name: AssignRolePermission :exec
INSERT INTO role_permissions (role_id, permission_id)
VALUES (
  $1,
  (
    SELECT id
    FROM permissions p
    WHERE p.scope = split_part(sqlc.arg('key'), '_', 1)
      AND p.name = right(sqlc.arg('key'), length(sqlc.arg('key')) - position('_' IN sqlc.arg('key')))
  )
);
-- name: AddPermissionsToRoleByKey :exec
INSERT INTO role_permissions (role_id, permission_id)
SELECT
  sqlc.arg(role_id),
  p.id
FROM
  permissions p
JOIN
  unnest(sqlc.arg(permission_keys)::text[]) AS key_str
    ON key_str = p.scope || '_' || p.name;

-- name: GetUserRoles :many
SELECT r.* FROM roles r
JOIN user_roles ur ON r.id = ur.role_id
WHERE ur.user_id = $1;

-- name: AssignUserRole :exec
INSERT INTO user_roles (user_id, role_id)
VALUES ($1, (
    SELECT id FROM roles r
    WHERE r.scope = split_part(sqlc.arg('key'), '_', 1)
          AND r.name = right(sqlc.arg('key'), length(sqlc.arg('key')) - position('_' IN sqlc.arg('key')))
));

-- name: UnassignUserRole :exec
DELETE FROM user_roles
WHERE user_id = $1 AND role_id = (
    SELECT id FROM roles r
    WHERE r.scope = split_part(sqlc.arg('key'), '_', 1)
          AND r.name = right(sqlc.arg('key'), length(sqlc.arg('key')) - position('_' IN sqlc.arg('key')))
);

-- name: FindUserRole :one
SELECT * FROM user_roles
WHERE user_id = $1 AND role_id = (SELECT id FROM roles r WHERE r.scope = split_part(sqlc.arg('key'), '_', 1) AND r.name = right(sqlc.arg('key'), length(sqlc.arg('key')) - position('_' IN sqlc.arg('key'))))
LIMIT 1;