Query: vpc_egress_nacl_sankey

 with aces as (
   select
     title,
     network_acl_id,
     e ->> 'SourceCidrIp' as cidr_block,
     SPLIT_PART(e ->> 'Port','/',2) as to_port,
     SPLIT_PART(e ->> 'Port','/',1) as from_port,
     e ->> 'Policy' as rule_action,
     e ->> 'NetworkAclEntryName' as rule_name,
     e ->> 'Protocol' as protocol,
     case when e ->> 'Policy' = 'accept' then 'Accept ' else 'Drop ' end ||
       case
           when e->>'Protocol' = 'all' then 'All Traffic'
           when e->>'Protocol' = 'icmp' then 'All ICMP'
           when e->>'Protocol' = 'tcp' and (e ->> 'Port' = '1/1' or e ->> 'Port' = '1/65535') then 'All TCP'
           when e->>'Protocol' = 'udp' and (e ->> 'Port' = '1/1' or e ->> 'Port' = '1/65535') then 'All UDP'
           when e->>'Protocol' = 'tcp' and SPLIT_PART(e ->> 'Port','/',2) = SPLIT_PART(e ->> 'Port','/',1)
             then  concat(SPLIT_PART(e ->> 'Port','/',2), '/TCP')
            when e->>'Protocol' = 'udp' and SPLIT_PART(e ->> 'Port','/',2) = SPLIT_PART(e ->> 'Port','/',1)
             then  concat(SPLIT_PART(e ->> 'Port','/',2), '/UDP')

           when e->>'Protocol' = 'tcp' and SPLIT_PART(e ->> 'Port','/',2) <> SPLIT_PART(e ->> 'Port','/',1)
             then  concat(SPLIT_PART(e ->> 'Port','/',2), '-', SPLIT_PART(e ->> 'Port','/',1), '/TCP')
           when e->>'Protocol' = 'udp' and SPLIT_PART(e ->> 'Port','/',2) <> SPLIT_PART(e ->> 'Port','/',1)
             then  concat(SPLIT_PART(e ->> 'Port','/',2), '-', SPLIT_PART(e ->> 'Port','/',1), '/UDP')

           else concat('Procotol: ', e->>'Protocol')
     end as rule_description,
     r ->> 'ResourceId' as associated_id
   from
     alicloud_vpc_network_acl,
     jsonb_array_elements(ingress_acl_entries) as e,
     jsonb_array_elements(resources) as r
   where
     vpc_id = $1
 )
-- Subnet Nodes
 select
   distinct associated_id as id,
   associated_id as title,
   'vswitch' as category,
   null as from_id,
   null as to_id,
   0 as depth
 from aces

 -- ACL Nodes
 union select
   distinct network_acl_id as id,
   network_acl_id as title,
   'nacl' as category,
   null as from_id,
   null as to_id,
   1 as depth
 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,
   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 -> vswitch edge
 union select
   null as id,
   null as title,
   'attached' as category,
   network_acl_id as from_id,
   associated_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