Query: repo_deletion_limited_to_trusted_users

with repo_admins as (
  select
    distinct name_with_owner,
    array_agg(user_login) as admins
  from
    github_my_repository r
    join github_repository_collaborator c on r.name_with_owner = c.repository_full_name
    and c.permission = 'ADMIN'
  group by
    name_with_owner
)
select
  -- Required Columns
  r.url as resource,
  case
    when jsonb_array_length(to_jsonb(admins) - $1 :: text [ ]) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(to_jsonb(admins) - $1 :: text [ ]) > 2 then concat(
      'Repository deletion permission allowed to untrusted users ',
      to_jsonb(admins) - $1 :: text [ ] #> > '{0}',
      ', ',
      to_jsonb(admins) - $1 :: text [ ] #> > '{1}',
      ' and ',
      (
        jsonb_array_length(to_jsonb(admins) - $1 :: text [ ]) - 2
      ) :: text,
      ' more.'
    )
    when jsonb_array_length(to_jsonb(admins) - $1 :: text [ ]) = 2 then concat(
      'Repository deletion permission allowed to untrusted users ',
      to_jsonb(admins) - $1 :: text [ ] #> > '{0}',
      ' and ',
      to_jsonb(admins) - $1 :: text [ ] #> > '{1}',
      '.'
    )
    when jsonb_array_length(to_jsonb(admins) - $1 :: text [ ]) = 1 then concat(
      'Repository deletion permission allowed to untrusted user ',
      to_jsonb(admins) - $1 :: text [ ] #> > '{0}',
      '.'
    )
    else 'Repository deletion permission limited to trusted users.'
  end as reason,
  -- Additional Dimensions
  r.name_with_owner
from
  github_my_repository as r
  left join repo_admins as a on r.name_with_owner = a.name_with_owner;