Scripts for receiving id's and wrapper id's for selected jobProfile

set SEARCH_PATH = '<tenant_id>_mod_di_converter_storage';    
SELECT DISTINCT
        job_profiles.id, 
        job_profiles.jsonb ->> 'name' AS job_name, 
        match_profiles.jsonb ->> 'name' AS match_name,
        action_profiles.jsonb ->> 'name' AS action_name,
        mapping_profiles.jsonb ->> 'name' AS mapping_name
    FROM 
        job_profiles
    left JOIN profile_wrappers pw ON job_profiles.id = pw.job_profile_id
    left JOIN job_to_match_profiles ON job_to_match_profiles.masterwrapperid = pw.id 
    left JOIN profile_wrappers pw2 ON pw2.id = job_to_match_profiles.detailwrapperid
    left JOIN match_profiles ON match_profiles.id = pw2.match_profile_id 
    left JOIN match_to_action_profiles ON match_to_action_profiles.masterwrapperid = pw2.id
    left JOIN profile_wrappers pw3 ON pw3.id = match_to_action_profiles.detailwrapperid
    left JOIN action_profiles ON action_profiles.id = pw3.action_profile_id
    left JOIN action_to_mapping_profiles ON action_to_mapping_profiles.masterwrapperid = pw3.id
    left JOIN profile_wrappers pw4 ON pw4.id = action_to_mapping_profiles.detailwrapperid
    left JOIN mapping_profiles ON mapping_profiles.id = pw4.mapping_profile_id
    WHERE 
        job_profiles.id = '<job_profile_id>'

set SEARCH_PATH = '<tenantId>_mod_di_converter_storage';        
WITH RECURSIVE relations AS (
    SELECT 
        j2match.masterwrapperid AS master_id,
        j2match.detailwrapperid AS detail_id,
        jp.id AS job_id,
        jp.jsonb ->> 'name' AS job_name,
        mp.id AS sub_profile_id,
        mp.jsonb ->> 'name' AS sub_profile_name,
        j2match.jsonb ->> 'order' as match_order,
        'JOB_TO_MATCH' as profile_type,
        j2match.jsonb ->> 'reactTo' as react_to,
        jp.id::text as level
    FROM 
        job_profiles jp
        left join profile_wrappers pw ON pw.job_profile_id = jp.id
        left join job_to_match_profiles j2match on j2match.masterwrapperid = pw.id
        left join profile_wrappers pw2 ON pw2.id = j2match.detailwrapperid
        left join match_profiles mp ON mp.id = pw2.match_profile_id
    WHERE jp.id = '<job_profile_id>'

    UNION ALL

    SELECT DISTINCT
        m2m.masterwrapperid AS master_id,
        m2m.detailwrapperid AS detail_id,
        relations.job_id,
        relations.job_name,
        mp2.id AS sub_profile_id,
        mp2.jsonb ->> 'name' AS sub_profile_name,
        m2m.jsonb ->> 'order' AS match_order,
        'MATCH_TO_MATCH' as profile_type,
        m2m.jsonb ->> 'reactTo' as react_to,
        relations.level::text || '|' || pw2.id as level
    FROM
        relations
        left join match_to_match_profiles m2m ON m2m.masterwrapperid = relations.detail_id
        left join profile_wrappers pw2 ON m2m.detailwrapperid = pw2.id
        left join match_profiles mp2 ON pw2.match_profile_id = mp2.id
    WHERE mp2.jsonb is not null
    )
    
    SELECT 
        relations.master_id, 
        relations.detail_id, 
        relations.job_id, 
        relations.job_name, 
        relations.sub_profile_id, 
        relations.sub_profile_name, 
        relations.match_order, 
        relations.profile_type,
        relations.react_to,
        relations.level
    FROM relations
    
    UNION ALL
    
    SELECT DISTINCT
        j2a.masterwrapperid AS master_id,
        j2a.detailwrapperid AS detail_id,
        relations.job_id, 
        relations.job_name, 
        ap.id as sub_profile_id, 
        ap.jsonb ->> 'name' AS sub_profile_name,
        j2a.jsonb ->> 'order' AS match_order,
        'JOB_TO_ACTION' as profile_type,
        j2a.jsonb->> 'reactTo' as react_to,
        relations.job_id || '|' || pw2.id as level
    FROM relations
        left join profile_wrappers pw ON pw.job_profile_id = relations.job_id
        left join job_to_action_profiles j2a on j2a.masterwrapperid = pw.id
        left join profile_wrappers pw2 ON pw2.id = j2a.detailwrapperid 
        left join action_profiles ap on ap.id = pw2.action_profile_id
    WHERE relations.profile_type = 'JOB_TO_MATCH' and ap.id is not null

    UNION ALL
    
    SELECT DISTINCT
        m2a.masterwrapperid AS master_id,
        m2a.detailwrapperid AS detail_id,
        relations.job_id, 
        relations.job_name, 
        ap.id as sub_profile_id, 
        ap.jsonb ->> 'name' AS sub_profile_name,
        m2a.jsonb ->> 'order' AS match_order,
        'MATCH_TO_ACTION' as profile_type,
        m2a.jsonb ->> 'reactTo' as react_to,
        relations.level || '|' || pw3.id as level
    FROM relations
        left join match_to_action_profiles m2a on m2a.masterwrapperid = relations.master_id
        left join profile_wrappers pw3 ON pw3.id = m2a.detailwrapperid 
        left join action_profiles ap on ap.id = pw3.action_profile_id
    WHERE relations.profile_type = 'MATCH_TO_MATCH' and ap.id is not null

    UNION ALL
    
    SELECT DISTINCT
        m2a.masterwrapperid AS master_id,
        m2a.detailwrapperid AS detail_id,
        relations.job_id, 
        relations.job_name, 
        ap.id as sub_profile_id, 
        ap.jsonb ->> 'name' AS sub_profile_name,
        m2a.jsonb ->> 'order' AS match_order,
        'MATCH_TO_ACTION' as profile_type,
        m2a.jsonb ->> 'reactTo' as react_to,
        relations.level || '|' || pw3.id as level
    FROM relations
        left join match_to_action_profiles m2a on m2a.masterwrapperid = relations.detail_id
        left join profile_wrappers pw3 ON pw3.id = m2a.detailwrapperid 
        left join action_profiles ap on ap.id = pw3.action_profile_id
    WHERE relations.profile_type = 'MATCH_TO_MATCH' and ap.id is not null
    ORDER BY level, match_order ASC;