Query: service_tree

with pods as (
  select
    uid as pod_uid,
    title as pod_title
  from
    kubernetes_pod
  where
    selector_search in (select selector_query from kubernetes_service where uid = $1)
),
services as (
  select
    uid,
    title,
    pod_uid,
    pod_title,
    cluster_ip,
    external_ips,
    selector_query,
    l as lb,
    p ->> 'protocol' as protocol_number,
    concat(p ->> 'port','/', p ->> 'protocol') as port,
    concat(p ->> 'targetPort','/', p ->> 'protocol') as targetPort
  from
    pods,
    kubernetes_service,
    jsonb_array_elements(ports) as p,
    jsonb_array_elements(load_balancer_ingress) as l
  where
    uid = $1
  union
  select
    uid,
    title,
    pod_uid,
    pod_title,
    cluster_ip,
    external_ips,
    selector_query,
    load_balancer_ingress as lb,
    p ->> 'protocol' as protocol_number,
    concat(p ->> 'port','/', p ->> 'protocol') as port,
    concat(p ->> 'targetPort','/', p ->> 'protocol') as targetPort
  from
    pods,
    kubernetes_service,
    jsonb_array_elements(ports) as p
  where
    uid = $1
    and load_balancer_ingress is null
  )

-- LB
  select
    lb::text as id,
    lb ->> 'ip' as title,
    'lb' as category,
    null as from_id,
    null as to_id
  from
    services

-- EIP
  union all
  select
    eip as id,
    eip as title,
    'external_ip' as category,
    null as from_id,
    null as to_id
  from
    services,
    jsonb_array_elements_text(external_ips) as eip

-- ClusterIP
  union all
  select
    cluster_ip as id,
    cluster_ip as title,
    'cluster_ip' as category,
    null as from_id,
    null as to_id
  from
    services

-- Ports
  union all
  select
    port as id,
    port as title,
    'port' as category,
    null as from_id,
    null as to_id
  from
    services

  -- service
  union all
  select
    distinct title as id,
    title as title,
    'service' as category,
    null as from_id,
    null as to_id
  from
    services

  -- targetPorts
  union all
  select
    concat(targetPort,' (Target Port)') as id,
    targetPort as title,
    'targetPort' as category,
    null as from_id,
    null as to_id
  from
    services

  -- pods
  union all
  select
    distinct pod_title as id,
    pod_title as title,
    'pod' as category,
    null as from_id,
    null as to_id
  from
    services

  -- externalIP -> port
  union select
    null as id,
    null as title,
    'external_ip' as category,
    eip as from_id,
    port as to_id
  from services,
  jsonb_array_elements_text(external_ips) as eip

  -- clusterIP -> port
  union select
    null as id,
    null as title,
    'cluster_ip' as category,
    cluster_ip as from_id,
    port as to_id
  from services

  -- lb -> port
  union select
    null as id,
    null as title,
    'lb' as category,
    lb::text as from_id,
    port as to_id
  from services

  -- port -> service
  union select
    null as id,
    null as title,
    'port' as category,
    port as from_id,
    title as to_id
  from services

 -- service -> target
  union select
    null as id,
    null as title,
    'service' as category,
    title as from_id,
    concat(targetPort,' (Target Port)') as to_id
  from services

 -- target -> pod
  union select
    null as id,
    null as title,
    'targetPort' as category,
    concat(targetPort,' (Target Port)') as from_id,
    pod_title as to_id
  from services