Query: vpc_ingress_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
)
-- 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 network_acl_id as id,
  network_acl_id as title,
  'nacl' as category,
  null as from_id,
  null as to_id
from aces

-- vswitch node
union select
  distinct associated_id as id,
  associated_id as title,
  'vswitch' as category,
  null as from_id,
  null as to_id
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
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 -> vswitch edge
union select
  null as id,
  null as title,
  'attached' as category,
  network_acl_id as from_id,
  associated_id as to_id
from aces