-- name: GetUserRolesByUserID :many
SELECT ur.*, r.name AS role_name, r.description AS role_description
FROM user_roles ur
JOIN roles r ON r.id = ur.role_id
WHERE ur.user_id = sqlc.arg(user_id)
ORDER BY ur.assigned_at DESC;

-- name: GetUserRolesByRoleID :many
SELECT ur.*, u.username, u.email, u.full_name
FROM user_roles ur
JOIN users u ON u.id = ur.user_id
WHERE ur.role_id = sqlc.arg(role_id)
ORDER BY ur.assigned_at DESC;

-- name: GetUserRole :one
SELECT * FROM user_roles
WHERE user_id = sqlc.arg(user_id) AND role_id = sqlc.arg(role_id);

-- name: AssignRoleToUser :one
INSERT INTO user_roles (user_id, role_id)
VALUES (
    sqlc.arg(user_id),
    sqlc.arg(role_id))
RETURNING *;

-- name: RemoveRoleFromUser :exec
DELETE FROM user_roles
WHERE user_id = sqlc.arg(user_id) AND role_id = sqlc.arg(role_id);

-- name: RemoveAllRolesFromUser :exec
DELETE FROM user_roles
WHERE user_id = sqlc.arg(user_id);

-- name: CountUsersByRoleID :one
SELECT COUNT(*) FROM user_roles
WHERE role_id = sqlc.arg(role_id);