Trigger: Detect & correct S3 buckets with Macie disabled

with bucket_list as (
  select
    trim(b :: text, '"') as bucket_name
  from
    aws_macie2_classification_job,
    jsonb_array_elements(s3_job_definition -> 'BucketDefinitions') as d,
    jsonb_array_elements(d -> 'Buckets') as b
)
select
  concat(b.name, ' [', b.account_id, '/', b.region, ']') as title,
  b.sp_connection_name as conn,
  b.region
from
  aws_s3_bucket as b
  left join bucket_list as l on b.name = l.bucket_name
where
  b.region != any(array [ 'us-gov-east-1', 'us-gov-west-1' ])
  and l.bucket_name is null;