Query: vcn_nsl_egress_rule_sankey

with subnets as (
  select
    display_name as subnet_name,
    id as subnet_id,
    sl as sl_id
  from
    oci_core_subnet as s,
    jsonb_array_elements_text(security_list_ids) as sl
  where
    vcn_id = $1
),
securityList as (
  select
    subnet_name,
    subnet_id,
    display_name as sl_name,
    l.id as sl_id,
    egress_security_rules as rules
  from
    oci_core_security_list as l
    left join subnets as s on s.sl_id = l.id
  where
    vcn_id = $1
),
rule as (
  select
    subnet_name,
    subnet_id,
    sl_id,
    sl_name,
    r ->> 'source' as source,
    case
      when r ->> 'protocol' = 'all' then 'Allow All Traffic'
      when r ->> 'protocol' = '6' and r ->> 'tcpOptions' is null then 'Allow All TCP'
      when r ->> 'protocol' = '17' and r ->> 'udpOptions' is null then 'Allow All UDP'
      when r ->> 'protocol' = '1' and r ->> 'icmpOptions' is null then 'Allow All ICMP'

      when r->>'protocol' = '6' and (r->'tcpOptions' -> 'sourcePortRange' ->>'min' = '1' or r->'tcpOptions' -> 'sourcePortRange' ->>'min' is null)
          and (r->'tcpOptions' -> 'destinationPortRange' ->>'max' = '65535' or r->'tcpOptions' -> 'destinationPortRange' ->>'max' is null)
          then 'Allow All TCP'

      when r->>'protocol' = '17' and (r->'udpOptions' -> 'sourcePortRange' ->>'min' = '1' or r->'udpOptions' -> 'sourcePortRange' ->>'min' is null)
          and (r->'udpOptions' -> 'destinationPortRange' ->>'max' = '65535' or r->'udpOptions' -> 'destinationPortRange' ->>'max' is null)
          then 'Allow All UDP'

      when r ->> 'protocol' = '1' and r -> 'icmpOptions' ->> 'code' is not null and r -> 'icmpOptions' ->> 'type' is not null
        then concat('ICMP Type ', r -> 'icmpOptions' ->> 'type', ', Code ',  r -> 'icmpOptions' ->> 'code')
      when r ->> 'protocol' = '1' and r -> 'icmpOptions' ->> 'code' is not null
        then concat('ICMP Code ',  r -> 'icmpOptions' ->> 'code')
      when r ->> 'protocol' = '1' and r -> 'icmpOptions' ->> 'type' is not null
        then concat('ICMP Type ',  r -> 'icmpOptions' ->> 'type')

      when r->>'protocol' = '6' and r->'tcpOptions' -> 'sourcePortRange' ->>'min' = r->'tcpOptions' -> 'destinationPortRange' ->>'max'
          then concat(r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min','/TCP')

      when r->>'protocol' = '17' and r->'udpOptions' -> 'sourcePortRange' ->>'min' = r->'udpOptions' -> 'destinationPortRange' ->>'max'
          then concat(r -> 'udpOptions' -> 'sourcePortRange' ->> 'min','/UDP')

      when r->>'protocol' = '6' and COALESCE(r->'tcpOptions' -> 'sourcePortRange' ->>'min','1') <> COALESCE(r->'tcpOptions' -> 'destinationPortRange' ->>'max','65535')
          then concat(COALESCE(r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min','1'), '-',COALESCE(r->'tcpOptions' -> 'destinationPortRange' ->>'max','65535'),'/TCP')

      when r->>'protocol' = '17' and COALESCE(r->'udpOptions' -> 'sourcePortRange' ->>'min','1') <> COALESCE(r->'udpOptions' -> 'destinationPortRange' ->>'max','65535')
          then concat(COALESCE(r -> 'udpOptions' -> 'sourcePortRange' ->> 'min','1'), '-',COALESCE(r->'udpOptions' -> 'destinationPortRange' ->>'max','65535'),'/UDP')


      else concat('Protocol: ', r ->> 'protocol')
    end as rule_description
  from
    securityList,
    jsonb_array_elements(rules) as r
)

  -- Subnet node
select
  distinct subnet_name as id,
  subnet_name as title,
  'subnet' as category,
  null as from_id,
  null as to_id,
  0 as depth
from rule

  -- SL Nodes
union select
  distinct sl_name as id,
  sl_name as title,
  'sl' as category,
  null as from_id,
  null as to_id,
  1 as depth
from rule

  -- Rule Nodes
union select
  distinct rule_description as id,
  rule_description as title,
  'rule' as category,
  null as from_id,
  null as to_id,
  2 as depth
from rule

  -- CIDR Nodes
union select
  distinct source as id,
  source as title,
  'source' as category,
  null as from_id,
  null as to_id,
  3 as depth
from rule

-- sl -> subnet edge
union select
  null as id,
  null as title,
  'attached' as category,
  sl_name as from_id,
  subnet_name as to_id,
  null as depth
from rule

-- rule -> SL edge
union select
  null as id,
  null as title,
  'rule' as category,
  rule_description as from_id,
  sl_name as to_id,
  null as depth
from rule

  -- ip -> rule edge
union select
  null as id,
  null as title,
  'rule' as category,
  source as from_id,
  rule_description as to_id,
  null as depth
from rule