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;