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