Detect & correct ECS container instances with incorrect tags

with tags as (
  select
    title as title,
    arn,
    region,
    account_id,
    sp_connection_name as cred,
    coalesce(tags, '{}' :: jsonb) as tags,
    key,
    value
  from
    aws_ecs_container_instance
    left join jsonb_each_text(tags) as t(key, value) on true
),
updated_tags as (
  select
    arn,
    key as old_key,
    case
      when false then key
      else key
    end as new_key,
    value
  from
    tags
  where
    key is not null
    and key not like 'aws:%'
),
required_tags as (
  select
    r.arn,
    null as old_key,
    a.key as new_key,
    a.value
  from
    (
      select
        distinct arn
      from
        aws_ecs_container_instance
    ) r
    cross join (
      values
        (null, null)
    ) as a(key, value)
  where
    not exists (
      select
        1
      from
        updated_tags ut
      where
        ut.arn = r.arn
        and ut.new_key = a.key
    )
),
all_tags as (
  select
    arn,
    old_key,
    new_key,
    value
  from
    updated_tags
  union all
  select
    arn,
    old_key,
    new_key,
    value
  from
    required_tags
  where
    new_key is not null
),
allowed_tags as (
  select
    distinct arn,
    new_key
  from
    (
      select
        arn,
        new_key,
        case
          when new_key like '%' then true
          else false
        end as allowed
      from
        all_tags
    ) a
  where
    allowed = true
),
remove_tags as (
  select
    distinct arn,
    key
  from
    (
      select
        arn,
        new_key as key,
        case
          when new_key like '%' then false
          else false
        end as remove
      from
        all_tags
    ) r
  where
    remove = true
  union
  select
    arn,
    old_key as key
  from
    all_tags
  where
    old_key is not null
    and old_key != new_key
  union
  select
    arn,
    new_key as key
  from
    all_tags a
  where
    not exists (
      select
        1
      from
        allowed_tags at
      where
        at.arn = a.arn
        and at.new_key = a.new_key
    )
),
updated_values as (
  select
    arn,
    new_key,
    value as old_value,
    case
      when false then value
      else value
    end as updated_value
  from
    all_tags
)
select
  *
from
  (
    select
      t.title,
      t.arn,
      t.region,
      t.account_id,
      t.cred,
      coalesce(
        (
          select
            jsonb_agg(key)
          from
            remove_tags rt
          where
            rt.arn = t.arn
        ),
        '[]' :: jsonb
      ) as remove,
      coalesce(
        (
          select
            jsonb_object_agg(at.new_key, at.value)
          from
            all_tags at
          where
            at.arn = t.arn
            and at.new_key != coalesce(at.old_key, '')
            and not exists (
              select
                1
              from
                remove_tags rt
              where
                rt.arn = at.arn
                and rt.key = at.new_key
            )
        ),
        '{}' :: jsonb
      ) || coalesce(
        (
          select
            jsonb_object_agg(uv.new_key, uv.updated_value)
          from
            updated_values uv
          where
            uv.arn = t.arn
            and uv.updated_value != uv.old_value
            and not exists (
              select
                1
              from
                remove_tags rt
              where
                rt.arn = uv.arn
                and rt.key = uv.new_key
            )
        ),
        '{}' :: jsonb
      ) as upsert
    from
      tags t
    group by
      t.title,
      t.arn,
      t.region,
      t.account_id,
      t.cred
  ) result
where
  remove != '[]' :: jsonb
  or upsert != '{}' :: jsonb;