1

with whmcs as (select distinct datas.*
               from temp_segment,
                    json_to_recordset(temp_segment.data::json)
                        as datas(user_id bigint,
                                 service_id bigint,
                                 tunnel character varying,
                                 segment_sync_id character varying)
               where segment_sync_id = '19fe2cf3-1967-4f27-be5a-3d3753b56ae2'),
     swsdk as (select distinct datas.*
               from temp_segment,
                    json_to_recordset(temp_segment.data::json)
                        as datas(contragent_id bigint,
                                 contragent_agreement_id bigint,
                                 whmcs_id bigint,
                                 usage_id bigint,
                                 whmcs_service_id bigint,
                                 usage_type character varying,
                                 usage_protected_object_id bigint,
                                 protected_object_entity character varying,
                                 protected_object_id bigint,
                                 segment_sync_id character varying)
               where segment_sync_id = '9257b13b-81af-44d7-be4e-b2bb10788773'),
     l3manager as (select distinct datas.*
                   from temp_segment,
                        json_to_recordset(temp_segment.data::json)
                            as datas(protected_object_id bigint,
                                     type character varying,
                                     name character varying,
                                     segment_sync_id character varying)
                   where segment_sync_id = '54876e72-eb64-4ec8-b0f7-676b6a82aa1d'),
     swsdkl3 as (select distinct *
                 from swsdk
                          join l3manager on swsdk.protected_object_id = l3manager.protected_object_id and
                                            swsdk.usage_type = l3manager.type),
     whmcsswsdkclient as (select distinct user_id, service_id, tunnel, contragent_id, contragent_agreement_id, whmcs_id
                          from whmcs
                                   left join swsdk on whmcs.user_id = swsdk.whmcs_id),
     whmcsswsdkservice as (select distinct user_id,
                                           service_id,
                                           tunnel,
                                           whmcsswsdkclient.contragent_id,
                                           whmcsswsdkclient.contragent_agreement_id,
                                           whmcsswsdkclient.whmcs_id,
                                           swsdk.usage_id,
                                           swsdk.usage_type
                           from whmcsswsdkclient
                                    left join swsdk on whmcsswsdkclient.service_id = swsdk.whmcs_service_id),
     swsdkCustomTunnels as (select name
                            from swsdk
                                     left join sync_custom_tunnels
                                               on (protected_object_entity = 'customTunnel' and protected_object_id = id)
                            where id is not null)

select distinct user_id,
                service_id,
                tunnel,
                contragent_id,
                contragent_agreement_id,
                usage_id,
                usage_type,
                l3manager.protected_object_id,
                type,
                name
from whmcsswsdkservice
         left join l3manager on whmcsswsdkservice.tunnel = l3manager.name
where tunnel != ''
  and tunnel not in (select name from swsdkl3)
  and tunnel not in (select name from swsdkCustomTunnels)
order by user_id, service_id, type;