Query: network_firewall_inbound_analysis

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

-- Firewall Nodes
union select
  distinct title as id,
  title as title,
  'inbound' as category,
  null as from_id,
  null as to_id
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 to_id
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
from rules