Query: vcn_gateway_sankey

with routes as (
select
    r.id as route_table_id,
    r.vcn_id,
    rule ->> 'networkEntityId' as gateway,
    rule ->> 'destination' as destination_cidr,
    s.display_name as associated_to
  from
    oci_core_subnet as s
    left join oci_core_route_table as r on s.route_table_id = r.id,
    jsonb_array_elements(route_rules) as rule
  where
    s.vcn_id = $1
),
gateway as (
  select
    associated_to,
    gateway,
    destination_cidr,
    case
      when i.id is not null then i.display_name
      when l.id is not null then l.name
      when n.id is not null then n.display_name
      when s.id is not null then s.display_name
    end as gateway_name
  from
    routes as r
    left join oci_core_internet_gateway as i on i.id = gateway
    left join oci_core_local_peering_gateway as l on l.id = gateway
    left join oci_core_nat_gateway as n on n.id = gateway
    left join oci_core_service_gateway as s on s.id = gateway
)
select
    null as from_id,
    associated_to as id,
    associated_to as title,
    'route_table' as category,
    0 as depth
  from
    gateway
  union
    select
      associated_to as from_id,
      destination_cidr as id,
      destination_cidr as title,
      'subnet' as category,
      1 as depth
    from
      gateway
  union
    select
      destination_cidr as from_id,
      gateway_name as id,
      gateway_name as title,
      'gateway' as category,
      2 as depth
    from
      gateway