Query: ibm_outbound_nacl_for_vpc_sankey

with aces as (
  select
    crn,
    title,
    id as network_acl_id,
    e -> 'Protocol' as protocol,
    e ->> 'source' as cidr_block,
    e ->> 'action' as rule_action,
    e -> 'name' as rule_name,
   case
          when e ->>'protocol' = 'all' then 'All Traffic'
          when e ->>'protocol' = 'icmp' then 'All ICMP'
          when e ->>'protocol' = 'udp' and e ->> 'source_port_min' = '1' and e ->> 'source_port_max' = '65535'
            then 'All UDP'
          when e ->>'protocol' = 'tcp' and e ->>'source_port_min' = '1' and e ->>'source_port_max' = '65535'
            then  'All TCP'
          when e ->>'protocol' = 'tcp' and e ->> 'source_port_min' = e ->> 'source_port_max'
            then  concat(e ->> 'source_port_min', '/TCP')
          when e->>'protocol' = 'udp' and e ->> 'source_port_min'  = e ->> 'source_port_max'
            then  concat(e->> 'source_port_min', '/UDP')
          when e->>'protocol' = 'tcp' and e ->> 'source_port_min' <> e->> 'source_port_max'
            then  concat(e ->> 'source_port_min', '-', e ->> 'source_port_max', '/TCP')
          when e->>'protocol' = 'udp' and e ->> 'source_port_min' <> e->> 'source_port_max'
            then  concat(e ->> 'source_port_min', '-', e ->> 'source_port_max', '/udp')
          else concat('Procotol: ', e->>'protocol')
    end as rule_description,

    a ->> 'id' as subnet_id
  from
    ibm_is_network_acl,
    jsonb_array_elements(rules) as e,
    jsonb_array_elements(subnets) as a
  where
    vpc ->> 'crn' = $1
    and e ->> 'direction' = 'outbound'
)

-- Subnet Nodes
select
  distinct a.subnet_id as id,
  s.name as title,
  'subnet' as category,
  null as from_id,
  null as to_id,
  0 as depth
from aces as a left join ibm_is_subnet as s on s.id = a.subnet_id

-- ACL Nodes
union select
  distinct s.network_acl_id as id,
  acl.name as title,
  'nacl' as category,
  null as from_id,
  null as to_id,
  1 as depth
from aces as s left join ibm_is_network_acl as acl on s.network_acl_id = acl.id

-- Rule Nodes
union select
  concat(network_acl_id, '_', rule_name) as id,
 rule_description as title,
  'rule' as category,
  null as from_id,
  null as to_id,
  2 as depth
from aces

-- CIDR Nodes
union select
  distinct cidr_block as id,
  cidr_block as title,
  'cidr_block' as category,
  null as from_id,
  null as to_id,
  3 as depth
from aces

-- nacl -> subnet edge
union select
  null as id,
  null as title,
  'attached' as category,
  network_acl_id as from_id,
  subnet_id as to_id,
  null as depth
from aces

-- rule -> NACL edge
union select
  null as id,
  null as title,
  rule_action as category,
  concat(network_acl_id, '_', rule_name) as from_id,
  network_acl_id as to_id,
  null as depth
from aces

-- ip -> rule edge
union select
  null as id,
  null as title,
  rule_action as category,
  cidr_block as from_id,
  concat(network_acl_id, '_', rule_name) as to_id,
  null as depth
from aces;