Query: ecs_security_group_ingress_rule_sankey

with associations as (
  select
    title,
    arn,
    sg as security_group_id
  from
    alicloud_ecs_instance,
    jsonb_array_elements_text(security_group_ids) as sg
  where
    sg = $1
),
rules as (
  select
    concat(
      text(p ->> 'SourceCidrIp'),
      text(p ->> 'Ipv6SourceCidrIp'),
      text(p ->> 'SourceGroupId'),
      text(p ->> 'SourcePrefixListId')
    ) as "source",
    case
      when p ->> 'IpProtocol' = 'ALL' then 'All Traffic'
      when p ->> 'IpProtocol' = 'ICMP' then 'All ICMP'
      when p ->> 'PortRange' = '-1/-1'
      or p ->> 'PortRange' = '1/65535' then concat('All ', p ->> 'IpProtocol')
      when SPLIT_PART(p ->> 'PortRange', '/', 2) = SPLIT_PART(p ->> 'PortRange', '/', 1) then concat(
        SPLIT_PART(p ->> 'PortRange', '/', 2),
        '/',
        p ->> 'IpProtocol'
      )
      else concat(
        SPLIT_PART(p ->> 'PortRange', '/', 1),
        '-',
        SPLIT_PART(p ->> 'PortRange', '/', 2),
        '/',
        p ->> 'IpProtocol'
      )
    end as port_proto,
    case
      when (
        text(p ->> 'SourceCidrIp') = '0.0.0.0/0'
        or text(p ->> 'Ipv6SourceCidrIp') = '::/0'
      )
      and p ->> 'IpProtocol' <> 'ICMP'
      and (
        p ->> 'PortRange' = '-1/-1'
        or p ->> 'PortRange' = '1/65535'
      ) then 'alert'
      else 'ok'
    end as category,
    security_group_id
  from
    alicloud_ecs_security_group,
    jsonb_array_elements(permissions) as p
  where
    security_group_id = $1
    and p ->> 'Direction' = 'ingress'
) -- Nodes  ---------
select
  distinct concat('src_', source) as id,
  source as title,
  0 as depth,
  'source' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct port_proto as id,
  port_proto as title,
  1 as depth,
  'port_proto' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct sg.security_group_id as id,
  name as title,
  2 as depth,
  'security_group' as category,
  null as from_id,
  null as to_id
from
  alicloud_ecs_security_group sg
  inner join rules sgr on sg.security_group_id = sgr.security_group_id
union
select
  distinct arn as id,
  title as title,
  3 as depth,
  title,
  security_group_id as from_id,
  null as to_id
from
  associations -- Edges  ---------
union
select
  null as id,
  null as title,
  null as depth,
  category,
  concat('src_', source) as from_id,
  port_proto as to_id
from
  rules
union
select
  null as id,
  null as title,
  null as depth,
  category,
  port_proto as from_id,
  security_group_id as to_id
from
  rules