/********* Relationship (Nominee) *********/

-- CALL truncate_transaction("relationship", "TRUNCATE TABLE relationship");

INSERT INTO relationship (`Party_Id`, `Relationship_Type`, `Name`, `DOB`, `Nationality`, `Address`,
`Share_Percentage`, `Is_Nominee`, `Nominee_Index`, `Is_Active`, `created_date`, `created_by`, `Is_Latest_rec`)
with cte1 as (SELECT party_id,
b.id AS cbs_party_id,
a.id,
_sdc_source_key_encoded_key,
_sdc_level_0_id,
value,
case when a.id='nom_City' then _sdc_level_0_id end as assigned_value
FROM clients__custom_fields a
INNER JOIN clients b ON a._sdc_source_key_encoded_key = b.encoded_key
INNER JOIN party c ON b.id = c.cbs_party_id
where field_set_id in('_Nom_Details')),
cte2 as (SELECT party_id,t1._sdc_source_key_encoded_key,id,_sdc_level_0_id,value,
(CASE WHEN assigned_value IS NULL
THEN @prevValue
ELSE @prevValue := assigned_value END) assigned_sdc_level_0_id
FROM cte1 t1
CROSS JOIN (SELECT @prevValue := NULL) t2
group by _sdc_source_key_encoded_key,id,_sdc_level_0_id
ORDER BY t1._sdc_source_key_encoded_key)
select party_id as Party_Id,
IFNULL(max(nom_Relationship), NULL) as Relationship_Type,
IFNULL(max(nom_Name), NULL) as Name,
IFNULL(max(nom_DOB), NULL) as DOB,
IFNULL(max(nom_Country), NULL) as Nationality,
concat('{', '"Street":', '"', IFNULL(max(nom_Street), NULL), '"', ', ', '"City":', '"', IFNULL(max(nom_City), NULL), '"', ', ', '"ZipCode":', '"', IFNULL(max(nom_ZipCode), NULL), '"', ', ', '"State":', '"', IFNULL(max(nom_State), NULL), '"', ', ', '"Country":', '"', IFNULL(max(nom_Country), NULL), '"', '}') AS Address,
Null as Share_Percentage,
'Y' as Is_Nominee,
max(nom_Index) as Nominee_Index,
1 as Is_Active,
CURRENT_TIMESTAMP as Created_Date,
'MIG_SIT' as Created_By,
'Y' as Is_Latest_rec
from(
select _sdc_source_key_encoded_key,
party_id,
assigned_sdc_level_0_id,
Case when a.id='nom_City' then value End as nom_City,
Case when a.id='nom_Street' then value  End as nom_Street,
Case when a.id='_index' then value  End as _index,
Case when a.id='nom_Relationship' then value  End as nom_Relationship,
Case when a.id='nom_DOB' then value  End as nom_DOB,
Case when a.id='nom_Index' then value  End as nom_Index,
Case when a.id='nom_Name' then value  End as nom_Name,
Case when a.id='nom_State' then value  End as nom_State,
Case when a.id='nom_ZipCode' then value  End as nom_ZipCode,
Case when a.id='nom_Country' then value  End as nom_Country
from cte2 a 
group by _sdc_source_key_encoded_key, id, assigned_sdc_level_0_id
order by _sdc_source_key_encoded_key, assigned_sdc_level_0_id)a 
group by _sdc_source_key_encoded_key, assigned_sdc_level_0_id
order by _sdc_source_key_encoded_key, CAST(_index AS UNSIGNED);

/********* Relationship (Spouse) *********/

CALL insert_transaction("relationship (Spouse)", "INSERT INTO relationship (`Party_Id`, `Relationship_Type`, `Name`, `DOB`, `Nationality`, `Address`,
`Share_Percentage`, `Is_Nominee`, `Nominee_Index`, `Is_Active`, `created_date`, `created_by`, `Is_Latest_rec`)
with cte1 as (SELECT party_id, b.id AS cbs_party_id, a.id, _sdc_source_key_encoded_key, _sdc_level_0_id, value,
case when a.id='spouse_Nationality' then _sdc_level_0_id end as assigned_value
FROM clients__custom_fields a
INNER JOIN clients b ON a._sdc_source_key_encoded_key = b.encoded_key
INNER JOIN party c ON b.id = c.cbs_party_id
where field_set_id in('_Spouse_Details')),
cte2 as (SELECT party_id, t1._sdc_source_key_encoded_key, id, _sdc_level_0_id, value,
(CASE WHEN assigned_value IS NULL
THEN @prevValue
ELSE @prevValue := assigned_value END) assigned_sdc_level_0_id
FROM cte1 t1
CROSS JOIN (SELECT @prevValue := NULL) t2
group by _sdc_source_key_encoded_key,id,_sdc_level_0_id
ORDER BY t1._sdc_source_key_encoded_key)
select party_id as Party_Id,
'Spouse' as Relationship_Type,
IFNULL(max(spouse_Name), NULL) as Name,
IFNULL(max(spouse_DOB), NULL) as DOB,
IFNULL(max(spouse_Nationality), NULL) as Nationality,
NULL  AS Address,
NULL as Share_Percentage,
'Y' as Is_Nominee,
NULL as Nominee_Index,
1 as Is_Active,
CURRENT_TIMESTAMP as Created_Date,
'MIG_SIT' as Created_By,
'Y' as Is_Latest_rec
from (select _sdc_source_key_encoded_key, party_id, assigned_sdc_level_0_id,
Case when a.id='spouse_Nationality' then value End as spouse_Nationality,
Case when a.id='spouse_Employment' then value  End as spouse_Employment,
Case when a.id='spouse_Name' then value  End as spouse_Name,
Case when a.id='spouse_DOB' then value  End as spouse_DOB,
Case when a.id='spouse_Place_Of_Birth' then value  End as spouse_Place_Of_Birth
from cte2 a 
group by _sdc_source_key_encoded_key,id,assigned_sdc_level_0_id
order by _sdc_source_key_encoded_key,assigned_sdc_level_0_id)a 
group by _sdc_source_key_encoded_key,assigned_sdc_level_0_id
order by _sdc_source_key_encoded_key");