Trigger: Detect & correct Data lake analytics accounts with incorrect tags

with tags as (
  select
    coalesce(name, title) as title,
    id,
    region,
    subscription_id,
    sp_connection_name as conn,
    coalesce(tags, '{}' :: jsonb) as tags,
    t.key,
    t.value
  from
    azure_data_lake_analytics_account
    left join jsonb_each_text(tags) as t(key, value) on true
),
updated_tags as (
  select
    id,
    key as old_key,
    case
      when false then key
      else key
    end as new_key,
    value
  from
    tags
  where
    key is not null
),
required_tags as (
  select
    r.id,
    null as old_key,
    a.key as new_key,
    a.value
  from
    (
      select
        distinct id
      from
        azure_data_lake_analytics_account
    ) r
    cross join (
      values
        (null, null)
    ) as a(key, value)
  where
    not exists (
      select
        1
      from
        updated_tags ut
      where
        ut.id = r.id
        and ut.new_key = a.key
    )
),
all_tags as (
  select
    id,
    old_key,
    new_key,
    value
  from
    updated_tags
  union all
  select
    id,
    old_key,
    new_key,
    value
  from
    required_tags
  where
    new_key is not null
),
allowed_tags as (
  select
    distinct id,
    new_key
  from
    (
      select
        id,
        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 id,
    key
  from
    (
      select
        id,
        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
    id,
    new_key as key
  from
    all_tags a
  where
    not exists (
      select
        1
      from
        allowed_tags at
      where
        at.id = a.id
        and at.new_key = a.new_key
    )
),
updated_values as (
  select
    id,
    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.id,
      t.region,
      t.subscription_id,
      t.conn,
      t.tags as old_tags,
      jsonb_object_agg(uv.new_key, uv.updated_value) as new_tags
    from
      tags t
      join updated_values uv on t.id = uv.id
    where
      not exists (
        select
          1
        from
          remove_tags rt
        where
          rt.id = uv.id
          and rt.key = uv.new_key
      )
    group by
      t.title,
      t.id,
      t.region,
      t.subscription_id,
      t.conn,
      t.tags
  ) result
where
  old_tags != new_tags;