Query: ibm_is_security_group_inbound_rule_sankey

with associations as (
  select
    name,
    t ->> 'name' as resource_name,
    t ->> 'id' as resource_id,
    id as group_id,
    t ->> 'resource_type' as category
  from
    ibm_is_security_group,
    jsonb_array_elements(targets) as t
  where
    crn = $1
  ),
  rules as (
    select
      concat((r -> 'remote' ->> 'cidr_block'), (r -> 'remote' ->> 'name'), (r -> 'remote' ->> 'address')) as source,
      id as group_id,
      name as group_name,
      case
        when r ->>'protocol' = 'all' then 'All Traffic'
        when r ->>'protocol' = 'icmp' then 'All ICMP'
        when r ->>'protocol' = 'udp' and r ->> 'port_min' = '1' and r ->> 'port_max' = '65535' then 'All UDP'
        when r ->>'protocol' = 'tcp' and r ->>'port_min' = '1' and r ->>'port_max' = '65535' then  'All TCP'
        when r ->> 'port_min' is not null
        and r ->> 'port_max' is not null
        and r ->> 'port_min' = r ->>'port_max' then concat((r ->>'port_min'), '/', (r ->>'protocol'))
        else concat(
          (r ->>'port_min'),
          '-',
          (r ->>'port_max' ),
          '/',
          (r ->>'protocol')
        )
      end as port_proto,
      case
        when (r -> 'remote' ->> 'cidr_block' = '0.0.0.0/0')
            and r ->>'protocol' <> 'icmp'
            and
              ((r ->>'port_min') = '1' and (r ->>'port_max') = '65535')
            then 'alert'
        else 'ok'
      end as category
    from
      ibm_is_security_group,
      jsonb_array_elements(rules) as r
    where
      crn = $1
      and r ->> 'direction' = 'inbound'
      )

  -- Nodes  ---------

  select
    distinct concat('src_',source) as id,
    source as title,
    0 as depth,
    'source' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
    distinct port_proto as id,
    port_proto as title,
    1 as depth,
    'port_proto' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
    distinct group_id as id,
    group_name as title,
    2 as depth,
    'security_group' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
      distinct resource_id as id,
      resource_name || '(' || category || ')' as title,
      3 as depth,
      category,
      group_id as from_id,
      null as to_id
    from
      associations

  -- Edges  ---------
  union select
    null as id,
    null as title,
    null as depth,
    category,
    concat('src_',source) as from_id,
    port_proto as to_id
  from
    rules

  union select
    null as id,
    null as title,
    null as depth,
    category,
    port_proto as from_id,
    group_id as to_id
  from
    rules