Query: network_firewall_outbound_analysis

with rules as (
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r->>'protocol' = 'icmp' and r ->> 'ports' = '0' then 'All ICMP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' = '0' then 'All TCP'
      when r->>'protocol' = 'udp' and r ->> 'ports' = '0' then 'All UDP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r->>'protocol' = 'udp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
        else concat('Procotol: ', r->>'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'addresses') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r->>'protocol' = 'icmp' and r ->> 'ports' = '0' then 'All ICMP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' = '0' then 'All TCP'
      when r->>'protocol' = 'udp' and r ->> 'ports' = '0' then 'All UDP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r->>'protocol' = 'udp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
        else concat('Procotol: ', r->>'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'droplet_ids') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r->>'protocol' = 'icmp' and r ->> 'ports' = '0' then 'All ICMP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' = '0' then 'All TCP'
      when r->>'protocol' = 'udp' and r ->> 'ports' = '0' then 'All UDP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r->>'protocol' = 'udp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
        else concat('Procotol: ', r->>'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'kubernetes_ids') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r->>'protocol' = 'icmp' and r ->> 'ports' = '0' then 'All ICMP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' = '0' then 'All TCP'
      when r->>'protocol' = 'udp' and r ->> 'ports' = '0' then 'All UDP'
      when r->>'protocol' = 'tcp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r->>'protocol' = 'udp' and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
        else concat('Procotol: ', r->>'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'load_balancer_uids') as cidr
  where
    urn = $1
)

select
  distinct title as id,
  title as title,
  'inbound' as category,
  null as from_id,
  null as to_id,
  0 as depth
from rules

-- Rule Nodes
union select
  concat(title,'_',rule_description) as id,
  rule_description as title,
  'rule' as category,
  null as from_id,
  null as to_id,
  1 as depth
from rules

-- 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,
  2 as depth
from rules

-- rule -> Firewall edge
union select
  null as id,
  null as title,
  protocol_number as category,
  concat(title,'_',rule_description) as from_id,
  title as to_id,
  null as depth
from rules

-- ip -> rule edge
union select
  null as id,
  null as title,
  protocol_number as category,
  cidr_block as from_id,
  concat(title,'_',rule_description) as from_id,
  null as depth
from rules