Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */
--  AccountsGuaranteeLimit
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_GUARANTEE_LIMIT $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_ACCOUNTS_GUARANTEE_LIMIT ()
Begin
    Truncate Table TMP_V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW;

    Insert Into TMP_V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW (
        ID_GUARANTEED
        , ACCOUNTS_ID_ACCOUNTS
        , GUARANTEED_NAME
        , GUARANTEED_CREAT_DATE
        , GUARANTEED_AMOUNT
        , GUARANTEED_VAL_DATE
        , GUARANTEED_COMMENT
    )
    Select 
        ID_GUARANTEED
        , ACCOUNTS_ID_ACCOUNTS
        , GUARANTEED_NAME
        , GUARANTEED_CREAT_DATE
        , GUARANTEED_AMOUNT
        , GUARANTEED_VAL_DATE
        , GUARANTEED_COMMENT
    From V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW ;

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

    Set @bcpagl_curdate = curdate();

    Drop Temporary table If Exists TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK;
    Create Temporary table TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK (
                ID_TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK Int Unsigned Not Null Primary Key AUTO_INCREMENT
                , GUARANTEE_LIMIT_ID_GUARANTEE_LIMIT Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , Index IX_TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK_01 (GUARANTEE_LIMIT_ID_GUARANTEE_LIMIT)
                , Index IX_TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK_02 (ACCOUNTS_ID_ACCOUNTS)
            )
        ;
    Drop Temporary table If Exists TMP_ACCOUNTS_GUARANTEE_LIMIT_MIN_AGE_RANK;
    Create Temporary table TMP_ACCOUNTS_GUARANTEE_LIMIT_MIN_AGE_RANK (
                TMP_ACC_GRT_LMT_AG_RNK_ID_TMP_ACC_GRT_LMT_AG_RNK Int Unsigned Not Null Primary Key 
                , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , Index IX_TMP_ACCOUNTS_GUARANTEE_LIMIT_MIN_AGE_RANK_01 (ACCOUNTS_ID_ACCOUNTS)
            )
        ;
    Insert Into TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK (
                GUARANTEE_LIMIT_ID_GUARANTEE_LIMIT
                , ACCOUNTS_ID_ACCOUNTS
            )
    Select
        ID_GUARANTEED, ACCOUNTS_ID_ACCOUNTS
    From TMP_V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW
    Where Coalesce(GUARANTEED_VAL_DATE, @bcpagl_curdate) >= @bcpagl_curdate
    Order By ACCOUNTS_ID_ACCOUNTS
        , Coalesce(GUARANTEED_VAL_DATE, '2050-01-01') Desc
        , GUARANTEED_CREAT_DATE Desc
        , ID_GUARANTEED Desc
    ;


    Insert Into TMP_ACCOUNTS_GUARANTEE_LIMIT_MIN_AGE_RANK (
                TMP_ACC_GRT_LMT_AG_RNK_ID_TMP_ACC_GRT_LMT_AG_RNK
                , ACCOUNTS_ID_ACCOUNTS
            )
    Select
        Min(ID_TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK), ACCOUNTS_ID_ACCOUNTS
    From TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK
    Group By ACCOUNTS_ID_ACCOUNTS
    ;

    Update TMP_V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW tar
        Inner Join TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK r1
            On tar.ID_GUARANTEED = r1.GUARANTEE_LIMIT_ID_GUARANTEE_LIMIT
        Inner Join TMP_ACCOUNTS_GUARANTEE_LIMIT_MIN_AGE_RANK r2
            On tar.ACCOUNTS_ID_ACCOUNTS = r2.ACCOUNTS_ID_ACCOUNTS
    Set tar.ACCOUNTS_GUARANTEE_LIMIT_VIEW_AGE_RANK = 1 + r1.ID_TMP_ACCOUNTS_GUARANTEE_LIMIT_AGE_RANK - r2.TMP_ACC_GRT_LMT_AG_RNK_ID_TMP_ACC_GRT_LMT_AG_RNK
    ;
    Update TMP_V_PUBLIC_ACCOUNTS_GUARANTEE_LIMIT_VIEW tar
    Set tar.ACCOUNTS_GUARANTEE_LIMIT_VIEW_AGE_RANK = 0
    Where tar.ACCOUNTS_GUARANTEE_LIMIT_VIEW_AGE_RANK Is Null
    ;
End $$
Delimiter ;
