-- name: GetAllPermissions :many
SELECT *
FROM permissions p
ORDER BY p.scope;

-- name: GetGroupedPermissions :many
SELECT scope, json_agg(to_jsonb(permissions.*) ORDER BY name) as permissions
FROM permissions
GROUP BY scope;

-- name: CreatePermission :one
INSERT into permissions (
    name, scope, description
) VALUES (
    $1, $2, $3
) RETURNING *;

-- name: FindPermission :one
SELECT * FROM permissions
WHERE name = $1 AND scope = $2;

-- name: GetUserPermissions :many
SELECT DISTINCT p.id,p.name,p.scope,p.description
FROM user_roles ur
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE ur.user_id = $1
ORDER BY p.scope;