Delimiter //
/**
 * If a contact with the combination of Entity Id, Account Ref and Contact Ref 
 * already exists in application, update it with the imported one. 
 * Else, create a new contact.
 * 
 * @param int batchnum
 * @author Benoit
 * @ticket #2112, #3828
 */ 
Drop Procedure if Exists LOADER_CONTACTS_NORMALIZE_MAP_CONTACT_CREATE_OR_UPDATE//
 
Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_CONTACT_CREATE_OR_UPDATE (
    In batchnum int
)
Begin

--     Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);

    -- Contact doesn't exist
    DROP TEMPORARY TABLE IF EXISTS temp_CONTACTS;
    CREATE TEMPORARY TABLE temp_CONTACTS (
        ID_CONTACTS Int Unsigned Not Null Auto_increment
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , LOADER_CONTACTS_ID_LOADER_CONTACTS Int Unsigned Not Null
        , Primary Key (ID_CONTACTS)
        , index (LOADER_STATUS_ID_LOADER_STATUS)
        , index (LOADER_CONTACTS_ID_LOADER_CONTACTS)
    );

	Set @loader_type = GET_LOADER_INFO('LOADER_TYPE', batchnum);
	-- Stores new ids in a temporary table	
	if @loader_type = 'MEMBERS' Then
		INSERT INTO temp_CONTACTS (LOADER_STATUS_ID_LOADER_STATUS, LOADER_CONTACTS_ID_LOADER_CONTACTS)
        SELECT 
            LS.ID_LOADER_STATUS
			, LCT.ID_LOADER_CONTACTS
        FROM (
                LOADER_CONTACTS LCT
                INNER JOIN LOADER_STATUS LS
                    ON LS.ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS
            )
            LEFT JOIN CONTACTS CT
                ON LCT.CONTACTS_REF = CT.CONTACTS_REF
        WHERE
            LS.LOADER_STATUS_BATCH_NUM = batchnum
            And CT.ID_CONTACTS Is null -- contact non crée
            And LCT.ACCOUNTS_ID_ACCOUNTS Is null -- contact non lié à un compte
		;
	Else
		INSERT INTO temp_CONTACTS (LOADER_STATUS_ID_LOADER_STATUS, LOADER_CONTACTS_ID_LOADER_CONTACTS)
        SELECT 
            LS.ID_LOADER_STATUS
			, LCT.ID_LOADER_CONTACTS
        FROM (
                LOADER_CONTACTS LCT
                INNER JOIN LOADER_STATUS LS
                    ON LS.ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS
                Left Join LOADER_ACCOUNTS_REPLICATION LAR
                    On LS.ID_LOADER_STATUS = LAR.LOADER_STATUS_ID_LOADER_STATUS
            )
            LEFT JOIN CONTACTS CT
                ON LCT.CONTACTS_REF = CT.CONTACTS_REF
--                 And CT.ACCOUNTS_ID_ACCOUNTS = Coalesce(LCT.ACCOUNTS_ID_ACCOUNTS, LAR.ID_ACCOUNTS)
                And CT.ACCOUNTS_ID_ACCOUNTS in (LCT.ACCOUNTS_ID_ACCOUNTS, LAR.ID_ACCOUNTS)
        WHERE
            LS.LOADER_STATUS_BATCH_NUM = batchnum
            And CT.ID_CONTACTS Is null -- contact non crée
		;

	End If;

   
		

	-- check il multi target contact
	set @lcnmccu_maxContactNewId = Coalesce((
				Select max(LCT.ID_CONTACTS_NEW)
				From
					LOADER_CONTACTS LCT
					INNER JOIN LOADER_STATUS LS
						ON LS.ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS
				Where
					LS.LOADER_STATUS_BATCH_NUM = batchnum
					And LCT.ID_CONTACTS_NEW Is Not null 
			), 0)
		;
    -- -- Update new contacts with their new id
    UPDATE 
        LOADER_CONTACTS LCT
        INNER JOIN temp_CONTACTS TMP
--             ON TMP.LOADER_STATUS_ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS
            ON TMP.LOADER_CONTACTS_ID_LOADER_CONTACTS = LCT.ID_LOADER_CONTACTS
    SET
        LCT.ID_CONTACTS_NEW = TMP.ID_CONTACTS + @lcnmccu_maxContactNewId 
	Where
		LCT.ID_CONTACTS_NEW Is null 
		And LCT.ID_CONTACTS Is null 
    ;
    
	-- Contact exists
	if @loader_type = 'MEMBERS' Then
		UPDATE (
				LOADER_CONTACTS LCT
				INNER JOIN LOADER_STATUS LS
					ON LS.ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS
			)
			INNER JOIN CONTACTS CT
				ON CT.CONTACTS_REF = LCT.CONTACTS_REF
		SET
			LCT.ID_CONTACTS = CT.ID_CONTACTS
		WHERE 
			LS.LOADER_STATUS_BATCH_NUM = batchnum
			 And LCT.ACCOUNTS_ID_ACCOUNTS Is null -- contact non lié à un compte
		;
	else
		UPDATE (
				LOADER_CONTACTS LCT
				INNER JOIN LOADER_STATUS LS
					ON LS.ID_LOADER_STATUS = LCT.LOADER_STATUS_ID_LOADER_STATUS

				Left Join LOADER_ACCOUNTS_REPLICATION LAR
					On LS.ID_LOADER_STATUS = LAR.LOADER_STATUS_ID_LOADER_STATUS
			)
			INNER JOIN CONTACTS CT
				ON CT.CONTACTS_REF = LCT.CONTACTS_REF
	--             And CT.ACCOUNTS_ID_ACCOUNTS = Coalesce(LCT.ACCOUNTS_ID_ACCOUNTS, LAR.ID_ACCOUNTS)
				And CT.ACCOUNTS_ID_ACCOUNTS in (LCT.ACCOUNTS_ID_ACCOUNTS, LAR.ID_ACCOUNTS)
		SET
			LCT.ID_CONTACTS = CT.ID_CONTACTS
		WHERE 

			LS.LOADER_STATUS_BATCH_NUM = batchnum
		;
	end if;

End //
Delimiter ;