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

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

    -- Account doesn't exist
    DROP TEMPORARY TABLE IF EXISTS temp_ACCOUNTS;
    CREATE TEMPORARY TABLE temp_ACCOUNTS (
        ID_ACCOUNTS Int Unsigned Not Null Auto_increment
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , Primary Key (ID_ACCOUNTS)
        , index (LOADER_STATUS_ID_LOADER_STATUS)
    );

    -- -- Stores new ids in a temporary table
    INSERT INTO 
        temp_ACCOUNTS (
            LOADER_STATUS_ID_LOADER_STATUS
        )
        SELECT 
            S.ID_LOADER_STATUS
        FROM
            LOADER_ACCOUNTS C
            INNER JOIN LOADER_STATUS S
                ON S.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
            LEFT JOIN ACCOUNTS A
                ON A.ENTITIES_ID_ENTITIES = @idEntities
                And A.ACCOUNTS_REF = C.ACCOUNTS_REF
        WHERE
            S.LOADER_STATUS_BATCH_NUM = batchnum
            And A.ACCOUNTS_REF is Null
    ;

    -- -- Update new accounts with their new id
    UPDATE 
        LOADER_ACCOUNTS C
        INNER JOIN temp_ACCOUNTS TMP
            ON TMP.LOADER_STATUS_ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
    SET
        C.ID_ACCOUNTS_NEW = TMP.ID_ACCOUNTS
    ;
    
    -- Account exists
    UPDATE
        LOADER_ACCOUNTS C
        INNER JOIN LOADER_STATUS S
            ON S.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
        INNER JOIN ACCOUNTS A
            ON A.ENTITIES_ID_ENTITIES = @idEntities
            And A.ACCOUNTS_REF = C.ACCOUNTS_REF
    SET
        C.ID_ACCOUNTS = A.ID_ACCOUNTS
    WHERE 
        S.LOADER_STATUS_BATCH_NUM = batchnum
    ;
    
--     SELECT ID_LOADER_ACCOUNTS, ID_ACCOUNTS, ID_ACCOUNTS_NEW, ACCOUNTS_REF FROM LOADER_ACCOUNTS;

End //
Delimiter ;