Delimiter //
/**
 * Does the Credit limit already exist ?
 *
 * @param int batchnum
 * @return void
 * @author Ben
 */
Drop Procedure If Exists LOADER_CREDITLIMIT_NORMALIZE_MAP_CREATE_OR_UPDATE//
Create Procedure LOADER_CREDITLIMIT_NORMALIZE_MAP_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin
    Set @lcnmcou_batchnum = batchnum;
    Set @lcnmcou_curdate = curdate();

    -- Temporary table for new id generation
    Drop Temporary Table If Exists temp_CREDITLIMIT;
    Create Temporary Table temp_CREDITLIMIT (
        ID_CREDIT_LIMIT Int Unsigned Not Null Auto_increment
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
        , LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT Int Unsigned Not Null
        , LDR_CRDTLMT_ACC_ID_LDR_CRDTLMT_ACC Int Unsigned Not Null

        , Primary Key (ID_CREDIT_LIMIT)
        , index (LOADER_STATUS_ID_LOADER_STATUS)
        , index (LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT)
        , index (LDR_CRDTLMT_ACC_ID_LDR_CRDTLMT_ACC)
    );
    

--     Insert Into temp_CREDITLIMIT (LOADER_STATUS_ID_LOADER_STATUS, LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT)
--         Select 
--             LS.ID_LOADER_STATUS
-- 			, LDR.ID_LOADER_CREDITLIMIT
--         From 
--             LOADER_CREDITLIMIT LDR
--             Inner Join LOADER_STATUS LS
--                 On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
--             Left Join CREDIT_LIMIT CL
--                 On CL.ACCOUNTS_ID_ACCOUNTS = LDR.ACCOUNTS_ID_ACCOUNTS
--                 And (
--                     CL.CREDIT_LIMIT_VAL_DATE >= CURDATE()
--                     Or CL.CREDIT_LIMIT_VAL_DATE Is Null
--                 )
--         Where
--             LS.LOADER_STATUS_BATCH_NUM = batchnum
--             And LS.LOADER_STATUS_ERRCOUNT = 0
--             And (
--                 LDR.CREDIT_LIMIT_VAL_DATE < CURDATE() -- pour historisation
--                 Or (
--                     CL.ID_CREDIT_LIMIT Is Null -- pas de credit limit en cours de validité
--                     Or ( -- credit limit en cours, mais montant différent
--                         CL.ID_CREDIT_LIMIT Is Not Null 
--                         And CL.CREDIT_LIMIT_AMOUNT <> LDR.CREDIT_LIMIT_AMOUNT
--                     )
--                 )
--             )
--     ;
    -- New id generation

    Insert Into temp_CREDITLIMIT (
            LOADER_STATUS_ID_LOADER_STATUS
            , LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
            , LDR_CRDTLMT_ACC_ID_LDR_CRDTLMT_ACC
        )
    Select 
        LS.ID_LOADER_STATUS
        , LDR.ID_LOADER_CREDITLIMIT
        , LCA.ID_LOADER_CREDITLIMIT_ACCOUNTS
    From 
        LOADER_STATUS LS
        Inner Join LOADER_CREDITLIMIT LDR
            On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
        Inner Join LOADER_CREDITLIMIT_ACCOUNTS LCA
            On LDR.ID_LOADER_CREDITLIMIT = LCA.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
    Where
        LS.LOADER_STATUS_BATCH_NUM = @lcnmcou_batchnum
        And LS.LOADER_STATUS_ERRCOUNT = 0
        -- limit de credit actif : date val >= date du jour
        -- on considére que c'est une nouvelle limit de credit ( ligne à créer) pour 
        -- les cas suivants : 
        -- (A) - il y a une limit de crédit actif sur le compte, et la date de valeur de la 
        --      limit en cour d'import est anterieur à la date de valeur de la limit de 
        --      crédit active sur le compte ( historisation / retro import)
        --
        -- (B)  - il n'y pas de limit de credit actif sur le compte
        --
        -- (C)  - le montant de la limit de credit actif sur le compte diffère
        --      du montant de la limit de crédit en cours d'import
        -- 
        And (
                Coalesce(LDR.CREDIT_LIMIT_VAL_DATE, @lcnmcou_curdate) < @lcnmcou_curdate -- (A) historisation / retro import
                Or Not Exists (
                    -- test cas (B) & (C) 
                    Select CL.ACCOUNTS_ID_ACCOUNTS 
                    From CREDIT_LIMIT CL
                    Where 
                        LCA.ACCOUNTS_ID_ACCOUNTS = CL.ACCOUNTS_ID_ACCOUNTS 
                        -- limit de credit non echu
                        And Coalesce(CL.CREDIT_LIMIT_VAL_DATE, @lcnmcou_curdate) >= @lcnmcou_curdate
                        And CL.CREDIT_LIMIT_AMOUNT = LDR.CREDIT_LIMIT_AMOUNT
                    Limit 1
                )
            )
    ;

--     Update 
--         LOADER_CREDITLIMIT LDR
--         Inner Join temp_CREDITLIMIT TMP
--             ON TMP.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT = LDR.ID_LOADER_CREDITLIMIT
--     Set
--         LDR.ID_CREDIT_LIMIT_NEW = TMP.ID_CREDIT_LIMIT
-- 	Where
-- 		LDR.ID_CREDIT_LIMIT_NEW Is Null
-- 		And LDR.ID_CREDIT_LIMIT Is Null
--     ;

    -- New id mapping
    Update 
        LOADER_CREDITLIMIT_ACCOUNTS LDA
        Inner Join temp_CREDITLIMIT TMP
            ON TMP.LDR_CRDTLMT_ACC_ID_LDR_CRDTLMT_ACC = LDA.ID_LOADER_CREDITLIMIT_ACCOUNTS
    Set
        LDA.ID_CREDIT_LIMIT_NEW = TMP.ID_CREDIT_LIMIT
	Where
		LDA.ID_CREDIT_LIMIT_NEW Is Null
		And LDA.ID_CREDIT_LIMIT 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_CREDITLIMIT LDR
--         Inner Join LOADER_STATUS LS
--             On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
--         Inner Join CREDIT_LIMIT CL
--             On CL.ACCOUNTS_ID_ACCOUNTS = LDR.ACCOUNTS_ID_ACCOUNTS
--             And (
--                 CL.CREDIT_LIMIT_VAL_DATE >= CURDATE()
--                 Or CL.CREDIT_LIMIT_VAL_DATE Is Null
--             )
--     Set
--         LDR.ID_CREDIT_LIMIT = CL.ID_CREDIT_LIMIT
--     Where
--         LS.LOADER_STATUS_BATCH_NUM = batchnum
--         And LS.LOADER_STATUS_ERRCOUNT = 0
--         And ( -- pas historique
--             LDR.CREDIT_LIMIT_VAL_DATE >= CURDATE()
--             Or LDR.CREDIT_LIMIT_VAL_DATE Is Null
--         )
--         And LDR.ID_CREDIT_LIMIT_NEW Is Not Null -- a été mappé en ajout
--         And LDR.ID_CREDIT_LIMIT Is Null
--     ;

    Update
        LOADER_CREDITLIMIT LDR
        Inner Join LOADER_STATUS LS
            On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
        Inner Join LOADER_CREDITLIMIT_ACCOUNTS LDA
            On LDR.ID_LOADER_CREDITLIMIT = LDA.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
    Set
        LDA.ID_CREDIT_LIMIT = (
            Select
                CL.ID_CREDIT_LIMIT
            From CREDIT_LIMIT CL
            Where CL.ACCOUNTS_ID_ACCOUNTS = LDA.ACCOUNTS_ID_ACCOUNTS
                -- le plus récent limit de credit non echu
                And Coalesce(CL.CREDIT_LIMIT_VAL_DATE, @lcnmcou_curdate) >= @lcnmcou_curdate
            Order By CL.CREDIT_LIMIT_VAL_DATE Desc, CL.ID_CREDIT_LIMIT Desc
            Limit 1
        )
    Where
        LS.LOADER_STATUS_BATCH_NUM =  @lcnmcou_batchnum
        And LS.LOADER_STATUS_ERRCOUNT = 0
            -- pas historique
        And Coalesce( LDR.CREDIT_LIMIT_VAL_DATE, @lcnmcou_curdate) >=@lcnmcou_curdate

        And LDA.ID_CREDIT_LIMIT_NEW Is Not Null -- a été mappé en ajout
        And LDA.ID_CREDIT_LIMIT Is Null
    ;


End//
Delimiter ;
