Delimiter //
/**
 * Find the id of the given sequence
 * muste be executed AFTER LOADER_REFDATA_NORMALIZE_MAP_ID_ENT_REF
 */
Drop Procedure If Exists LOADER_REFDATA_NORMALIZE_MAP_ID_ENT_REF_DATAS//
Create Procedure LOADER_REFDATA_NORMALIZE_MAP_ID_ENT_REF_DATAS(
    In batchnum Int
)
Begin
    

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

    Update
        LOADER_STATUS S
        Inner Join
            LOADER_REFDATA ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = S.ID_LOADER_STATUS
        Inner Join ENTITIES_REF_DATAS ERD
            On ARD.ENTITIES_REF_ID_ENTITIES_REF = ERD.ENTITIES_REF_ID_ENTITIES_REF
            And ARD.ENTITIES_REF_DATA_CODE = ERD.ENTITIES_REF_DATAS_CODE
    Set
        ARD.ID_ENTITIES_REF_DATAS = ERD.ID_ENTITIES_REF_DATAS
    Where
        S.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(trim(ARD.ENTITIES_REF_DATA_CODE), '') != ''
    ;

    --
    -- table temporaire pour les références en création
    -- permet d'obtenir un auto increment
    DROP TEMPORARY TABLE IF EXISTS temp_ENTREFDATA;
    CREATE TEMPORARY TABLE temp_ENTREFDATA (
        ID_ENTITIES_REF_DATAS Int Unsigned Not Null Auto_increment
        , ENTITIES_REF_ID_ENTITIES_REF Int Unsigned Not Null 
        , ENTITIES_REF_DATA_CODE Varchar(20)
--         , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , Primary Key (ID_ENTITIES_REF_DATAS)
        , index (ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATA_CODE)
    );

    -- insert des données
    Insert Into temp_ENTREFDATA (ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATA_CODE)
    Select Distinct
        ARD.ENTITIES_REF_ID_ENTITIES_REF
        , ARD.ENTITIES_REF_DATA_CODE
    From    
        LOADER_STATUS S
        Inner Join
            LOADER_REFDATA ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = S.ID_LOADER_STATUS
    Where 
        S.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(trim(ARD.ENTITIES_REF_DATA_CODE), '') != ''
        And ARD.ID_ENTITIES_REF_DATAS Is Null
    ;

    -- mise à jour du champ NEW_ID_ENTITIES_REF_DATAS 

    Update
        LOADER_REFDATA ARD
        Inner Join LOADER_STATUS S
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = S.ID_LOADER_STATUS
        Inner Join temp_ENTREFDATA TMPERD
            On ARD.ENTITIES_REF_ID_ENTITIES_REF = TMPERD.ENTITIES_REF_ID_ENTITIES_REF
            And ARD.ENTITIES_REF_DATA_CODE = TMPERD.ENTITIES_REF_DATA_CODE

    Set
        ARD.NEW_ID_ENTITIES_REF_DATAS = TMPERD.ID_ENTITIES_REF_DATAS
    Where 
        S.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(trim(ARD.ENTITIES_REF_DATA_CODE), '') != ''
        And ARD.ID_ENTITIES_REF_DATAS Is Null
    ;

    -- maj des erreur
    -- @todo à verifier si util ci-dessous
    Update
        LOADER_STATUS S
        Inner Join
            LOADER_REFDATA ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = S.ID_LOADER_STATUS
    Set
        S.LOADER_STATUS_ERRCOUNT = S.LOADER_STATUS_ERRCOUNT + 1
        , S.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(S.LOADER_STATUS_ERRMSGS, Concat('Référence inexistante :', ARD.ENTITIES_REF_DATA_CODE ) )
    Where 
        S.LOADER_STATUS_BATCH_NUM = batchnum
        And Coalesce(trim(ARD.ENTITIES_REF_DATA_CODE), '') != ''
        And Coalesce(ARD.ID_ENTITIES_REF_DATAS,  ARD.NEW_ID_ENTITIES_REF_DATAS) Is Null
    ;

End//
Delimiter ;
