Delimiter //
/**
 * Does the Guaranteed already exist ?
 *
 * @param int batchnum
 * @return void
 * @author Ben
 */
Drop Procedure If Exists LOADER_GUARANTEED_NORMALIZE_MAP_CREATE_OR_UPDATE//
Create Procedure LOADER_GUARANTEED_NORMALIZE_MAP_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin

    -- Temporary table for new id generation
    Drop Temporary Table If Exists temp_GUARANTEED;
    Create Temporary Table temp_GUARANTEED (
        ID_GUARANTEED Int Unsigned Not Null Auto_increment
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , LOADER_GUARANTEED_ID_LOADER_GUARANTEED Int Unsigned Not Null
        , Primary Key (ID_GUARANTEED)
        , index (LOADER_STATUS_ID_LOADER_STATUS)
        , index (LOADER_GUARANTEED_ID_LOADER_GUARANTEED)
    );

    -- New id generation
    Insert Into temp_GUARANTEED (LOADER_STATUS_ID_LOADER_STATUS, LOADER_GUARANTEED_ID_LOADER_GUARANTEED)
        Select 
            LS.ID_LOADER_STATUS
			, LDR.ID_LOADER_GUARANTEED
        From 
            LOADER_GUARANTEED LDR
            Inner Join LOADER_STATUS LS
                On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
            Left Join GUARANTEED CL
                On CL.ACCOUNTS_ID_ACCOUNTS = LDR.ACCOUNTS_ID_ACCOUNTS
                And CL.GUARANTEED_NAME = LDR.GUARANTEED_NAME
                And (
                    CL.GUARANTEED_VAL_DATE >= CURDATE()
                    Or CL.GUARANTEED_VAL_DATE Is Null
                )
        Where
            LS.LOADER_STATUS_BATCH_NUM = batchnum
            And LS.LOADER_STATUS_ERRCOUNT = 0
            And (
                LDR.GUARANTEED_VAL_DATE < CURDATE() -- pour historisation
                Or (
                    CL.ID_GUARANTEED Is Null -- pas de guaranteed en cours de validité
                    Or ( -- guaranteed en cours, mais montant différent
                        CL.ID_GUARANTEED Is Not Null 
                        And CL.GUARANTEED_AMOUNT <> LDR.GUARANTEED_AMOUNT
                    )
                )
            )
    ;

    -- New id mapping
    Update 
        LOADER_GUARANTEED LDR
        Inner Join temp_GUARANTEED TMP
            ON TMP.LOADER_GUARANTEED_ID_LOADER_GUARANTEED = LDR.ID_LOADER_GUARANTEED
    Set
        LDR.ID_GUARANTEED_NEW = TMP.ID_GUARANTEED
	Where
		LDR.ID_GUARANTEED_NEW Is Null
		And LDR.ID_GUARANTEED Is Null
    ;

    -- Existing id mapping (= historisation)
    -- ATTENTION : 
    --   conduit à mapping simultanné ID et ID_NEW, pour éviter d'avoir a créer une table supplémentaire
    --   bien penser à tester les 2 valeurs dans l'ensemble des procédures, à la copie en particulier
    Update
        LOADER_GUARANTEED LDR
        Inner Join LOADER_STATUS LS
            On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
        Inner Join GUARANTEED CL
            On CL.ACCOUNTS_ID_ACCOUNTS = LDR.ACCOUNTS_ID_ACCOUNTS
            And CL.GUARANTEED_NAME = LDR.GUARANTEED_NAME
            And (
                CL.GUARANTEED_VAL_DATE >= CURDATE()
                Or CL.GUARANTEED_VAL_DATE Is Null
            )
    Set
        LDR.ID_GUARANTEED = CL.ID_GUARANTEED
    Where
        LS.LOADER_STATUS_BATCH_NUM = batchnum
        And LS.LOADER_STATUS_ERRCOUNT = 0
        And ( -- pas historique
            LDR.GUARANTEED_VAL_DATE >= CURDATE()
            Or LDR.GUARANTEED_VAL_DATE Is Null
        )
        And LDR.ID_GUARANTEED_NEW Is Not Null -- a été mappé en ajout
        And LDR.ID_GUARANTEED Is Null
    ;

End//
Delimiter ;
