Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */

--  AccountsCreditLimit
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_CREDIT_LIMIT $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_CREDIT_LIMIT ()
Begin
    Truncate Table TMP_V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW;

    Insert Into TMP_V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW (
        ID_CREDIT_LIMIT
        , ACCOUNTS_ID_ACCOUNTS
        , CREDIT_LIMIT_DATE
        , CREDIT_LIMIT_AMOUNT
        , CREDIT_LIMIT_VAL_DATE
        , CREDIT_LIMIT_COMMENT
    )
    Select 
        ID_CREDIT_LIMIT
        , ACCOUNTS_ID_ACCOUNTS
        , CREDIT_LIMIT_DATE
        , CREDIT_LIMIT_AMOUNT
        , CREDIT_LIMIT_VAL_DATE
        , CREDIT_LIMIT_COMMENT
    From V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW ;

    -- les données sont populées
    -- on va calculer le AGE RANK

    Set @bcpacl_curdate = curdate();
    Drop Temporary table If Exists TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK;
    Create Temporary table TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK (
                ID_TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK Int Unsigned Not Null Primary Key AUTO_INCREMENT
                , CREDIT_LIMIT_ID_CREDIT_LIMIT Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , Index IX_TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK_01 (CREDIT_LIMIT_ID_CREDIT_LIMIT)
                , Index IX_TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK_02 (ACCOUNTS_ID_ACCOUNTS)
            )
        ;
    Drop Temporary table If Exists TMP_ACCOUNTS_CREDIT_LIMIT_MIN_AGE_RANK;
    Create Temporary table TMP_ACCOUNTS_CREDIT_LIMIT_MIN_AGE_RANK (
                TMP_ACC_CRD_LMT_AG_RNK_ID_TMP_ACC_CRD_LMT_AG_RNK Int Unsigned Not Null Primary Key 
                , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , Index IX_TMP_ACCOUNTS_CREDIT_LIMIT_MIN_AGE_RANK_01 (ACCOUNTS_ID_ACCOUNTS)
            )
        ;

    Insert Into TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK (
            CREDIT_LIMIT_ID_CREDIT_LIMIT, ACCOUNTS_ID_ACCOUNTS
        )
    Select ID_CREDIT_LIMIT, ACCOUNTS_ID_ACCOUNTS
    From TMP_V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW
    Where Coalesce(CREDIT_LIMIT_VAL_DATE, @bcpacl_curdate) >= @bcpacl_curdate
    Order By ACCOUNTS_ID_ACCOUNTS
        , Coalesce(CREDIT_LIMIT_VAL_DATE, '2050-01-01') Desc
        , CREDIT_LIMIT_DATE Desc
        , ID_CREDIT_LIMIT Desc
    ;

    Insert Into TMP_ACCOUNTS_CREDIT_LIMIT_MIN_AGE_RANK(
        TMP_ACC_CRD_LMT_AG_RNK_ID_TMP_ACC_CRD_LMT_AG_RNK
        , ACCOUNTS_ID_ACCOUNTS
    )
    Select
        Min(ID_TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK), ACCOUNTS_ID_ACCOUNTS
    From TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK
    Group By ACCOUNTS_ID_ACCOUNTS;


    Update TMP_V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW tar
        Inner Join TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK r1
            On tar.ID_CREDIT_LIMIT = r1.CREDIT_LIMIT_ID_CREDIT_LIMIT
        Inner Join TMP_ACCOUNTS_CREDIT_LIMIT_MIN_AGE_RANK r2
            On tar.ACCOUNTS_ID_ACCOUNTS = r2.ACCOUNTS_ID_ACCOUNTS 
    Set
        tar.ACCOUNTS_CREDIT_LIMIT_VIEW_AGE_RANK = r1.ID_TMP_ACCOUNTS_CREDIT_LIMIT_AGE_RANK - r2.TMP_ACC_CRD_LMT_AG_RNK_ID_TMP_ACC_CRD_LMT_AG_RNK +1
    ;

    Update TMP_V_PUBLIC_ACCOUNTS_CREDIT_LIMIT_VIEW tar
    Set
        tar.ACCOUNTS_CREDIT_LIMIT_VIEW_AGE_RANK = 0
    Where
        tar.ACCOUNTS_CREDIT_LIMIT_VIEW_AGE_RANK  Is Null
    ;

End $$
Delimiter ;

