Trigger: Detect & correct Subscriptions diagnostic settings without capturing proper categories

with enabled_settings as (
  select
    name,
    id,
    _ctx,
    resource_group,
    subscription_id,
    count(*) filter (
      where
        l ->> 'enabled' = 'true'
        and l ->> 'category' in ('Administrative', 'Security', 'Alert', 'Policy')
    ) as valid_category_count,
    string_agg(l ->> 'category', ', ') filter (
      where
        l ->> 'enabled' = 'true'
        and l ->> 'category' in ('Administrative', 'Security', 'Alert', 'Policy')
    ) as valid_categories
  from
    azure_diagnostic_setting,
    jsonb_array_elements(logs) as l
  group by
    name,
    id,
    _ctx,
    resource_group,
    subscription_id
),
enabled_settings_count as (
  select
    subscription_id,
    count(*) as enabled_setting_counts
  from
    enabled_settings
  where
    valid_category_count = 4
  group by
    subscription_id
)
select
  distinct sub.subscription_id as title,
  sub._ctx ->> 'connection_name' as conn
from
  azure_subscription sub
  left join enabled_settings_count as i on i.subscription_id = sub.subscription_id
where
  enabled_setting_counts = 0
  or enabled_setting_counts is null;