Query: ibm_inbound_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 ->> 'action' = 'allow' then 'Allow '
      else 'Deny '
    end || 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' = 'inbound'
) -- CIDR Nodes
select
  distinct cidr_block as id,
  cidr_block as title,
  'cidr_block' as category,
  null as from_id,
  null as to_id
from
  aces -- 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
from
  aces -- 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
from
  aces as s
  left join ibm_is_network_acl as acl on s.network_acl_id = acl.id -- Subnet node
union
select
  distinct a.subnet_id as id,
  s.name as title,
  'subnet' as category,
  null as from_id,
  null as to_id
from
  aces as a
  left join ibm_is_subnet as s on s.id = a.subnet_id -- 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
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
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
from
  aces;