Query: egress_nacl_for_vpc_sankey

with aces as (
  select
    arn,
    title,
    network_acl_id,
    is_default,
    e -> 'Protocol' as protocol_number,
    e ->> 'CidrBlock' as ipv4_cidr_block,
    e ->> 'Ipv6CidrBlock' as ipv6_cidr_block,
    coalesce(e ->> 'CidrBlock', e ->> 'Ipv6CidrBlock') as cidr_block,
    e -> 'PortRange' -> 'To' as to_port,
    e -> 'PortRange' -> 'From' as from_port,
    e ->> 'RuleAction' as rule_action,
    e -> 'RuleNumber' as rule_number,
    to_char((e ->> 'RuleNumber') :: numeric, 'fm00000') as rule_num_padded,
    -- e -> 'IcmpTypeCode' as icmp_type_code,
    e -> 'IcmpTypeCode' -> 'Code' as icmp_code,
    e -> 'IcmpTypeCode' -> 'Type' as icmp_type,
    e -> 'Protocol' as protocol_number,
    e -> 'Egress' as is_egress,
    case
      when e ->> 'RuleAction' = 'allow' then 'Allow '
      else 'Deny '
    end || case
      when e ->> 'Protocol' = '-1' then 'All Traffic'
      when e ->> 'Protocol' = '1'
      and e -> 'IcmpTypeCode' is null then 'All ICMP'
      when e ->> 'Protocol' = '58'
      and e -> 'IcmpTypeCode' is null then 'All ICMPv6'
      when e ->> 'Protocol' = '6'
      and e -> 'PortRange' is null then 'All TCP'
      when e ->> 'Protocol' = '17'
      and e -> 'PortRange' is null then 'All UDP'
      when e ->> 'Protocol' = '6'
      and e -> 'PortRange' ->> 'From' = '0'
      and e -> 'PortRange' ->> 'To' = '65535' then 'All TCP'
      when e ->> 'Protocol' = '17'
      and e -> 'PortRange' ->> 'From' = '0'
      and e -> 'PortRange' ->> 'To' = '65535' then 'All UDP'
      when e ->> 'Protocol' = '1'
      and e -> 'IcmpTypeCode' is not null then concat(
        'ICMP Type ',
        e -> 'IcmpTypeCode' ->> 'Type',
        ', Code ',
        e -> 'IcmpTypeCode' ->> 'Code'
      )
      when e ->> 'Protocol' = '58'
      and e -> 'IcmpTypeCode' is not null then concat(
        'ICMPv6 Type ',
        e -> 'IcmpTypeCode' ->> 'Type',
        ', Code ',
        e -> 'IcmpTypeCode' ->> 'Code'
      )
      when e ->> 'Protocol' = '6'
      and e -> 'PortRange' ->> 'To' = e -> 'PortRange' ->> 'From' then concat(e -> 'PortRange' ->> 'To', '/TCP')
      when e ->> 'Protocol' = '17'
      and e -> 'PortRange' ->> 'To' = e -> 'PortRange' ->> 'From' then concat(e -> 'PortRange' ->> 'To', '/UDP')
      when e ->> 'Protocol' = '6'
      and e -> 'PortRange' ->> 'From' <> e -> 'PortRange' ->> 'To' then concat(
        e -> 'PortRange' ->> 'To',
        '-',
        e -> 'PortRange' ->> 'From',
        '/TCP'
      )
      when e ->> 'Protocol' = '17'
      and e -> 'PortRange' ->> 'From' <> e -> 'PortRange' ->> 'To' then concat(
        e -> 'PortRange' ->> 'To',
        '-',
        e -> 'PortRange' ->> 'From',
        '/UDP'
      )
      else concat('Procotol: ', e ->> 'Protocol')
    end as rule_description,
    a ->> 'SubnetId' as subnet_id,
    a ->> 'NetworkAclAssociationId' as nacl_association_id
  from
    aws_vpc_network_acl,
    jsonb_array_elements(entries) as e,
    jsonb_array_elements(associations) as a
  where
    vpc_id = $1
    and (e -> 'Egress') :: boolean
) -- Subnet Nodes
select
  distinct subnet_id as id,
  subnet_id as title,
  'subnet' 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_num_padded) as id,
  concat(rule_number, ': ', 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 -- subnet -> edge
union
select
  null as id,
  null as title,
  'attached' as category,
  subnet_id as from_id,
  network_acl_id as to_id,
  null as depth
from
  aces -- NACL -> Rule edge
union
select
  null as id,
  null as title,
  rule_action as category,
  network_acl_id as from_id,
  concat(network_acl_id, '_', rule_num_padded) as to_id,
  null as depth
from
  aces -- rule -> ip edge
union
select
  null as id,
  null as title,
  rule_action as category,
  concat(network_acl_id, '_', rule_num_padded) as from_id,
  cidr_block as to_id,
  null as depth
from
  aces