Trigger: Detect & correct Cosmos DB accounts without a private link

with cosmosdb_private_connection as (
  select
    distinct a.id
  from
    azure_cosmosdb_account as a,
    jsonb_array_elements(private_endpoint_connections) as connection
  where
    connection -> 'properties' -> 'privateLinkServiceConnectionState' ->> 'status' = 'Approved'
)
select
  concat(
    a.id,
    ' [',
    a.subscription_id,
    '/',
    a.resource_group,
    ']'
  ) as title,
  a.id as id,
  subscription_id,
  _ctx ->> 'connection_name' as conn
from
  azure_cosmosdb_account as a
  left join cosmosdb_private_connection as c on c.id = a.id
where
  c.id is null;