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