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