Query: gmail_pop_imap_disabled

with all_users as (
  select primary_email as user_email from googledirectory_user
),
gmail_settings as (
  select 
    u.user_email,
    gs.pop,
    gs.imap
  from all_users u
  left join googleworkspace_gmail_settings gs on gs.user_email = u.user_email
)
select
  user_email as resource,
  case
    when (pop ->> 'accessWindow' = 'disabled' or pop ->> 'accessWindow' is null)
    and (imap ->> 'enabled' = 'false' or imap ->> 'enabled' is null) then 'ok'
    else 'alarm'
  end as status,
  case
    when (pop ->> 'accessWindow' = 'disabled' or pop ->> 'accessWindow' is null)
    and (imap ->> 'enabled' = 'false' or imap ->> 'enabled' is null) then 'POP and IMAP access disabled for user: ' || user_email || '.'
    when pop ->> 'accessWindow' != 'disabled' and pop ->> 'accessWindow' is not null then 'POP access enabled for user: ' || user_email || ' (access window: ' || (pop ->> 'accessWindow') || ').'
    when imap ->> 'enabled' = 'true' then 'IMAP access enabled for user: ' || user_email || '.'
    else 'POP or IMAP access may be enabled for user: ' || user_email || '.'
  end as reason
from
  gmail_settings;