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