Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */
--  ContactsEmails
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_EMAILS $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_EMAILS ()
Begin
    Truncate Table TMP_V_PUBLIC_CONTACTS_EMAILS_VIEW;

    Insert Into TMP_V_PUBLIC_CONTACTS_EMAILS_VIEW (
        CONTACTS_EMAILS_ID_CONTACTS_EMAILS
        , CONTACTS_ID_CONTACTS
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_EMAILS_EMAIL
        , CONTACTS_EMAILS_PREFERENCE
        , CONTACTS_EMAILS_TRASHED
        , CONTACTS_EMAILS_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_CHECK_STATE_DEFAULT
        , DF_CHECK_STATE_ORDER
    )
    Select 
        CONTACTS_EMAILS_ID_CONTACTS_EMAILS
        , CONTACTS_ID_CONTACTS
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_EMAILS_EMAIL
        , CONTACTS_EMAILS_PREFERENCE
        , CONTACTS_EMAILS_TRASHED
        , CONTACTS_EMAILS_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_CHECK_STATE_DEFAULT
        , DF_CHECK_STATE_ORDER
    From V_PUBLIC_CONTACTS_EMAILS_VIEW ;
    
    -- les données sont populées
    -- on va calculer le RANK
    Drop Temporary table If Exists TMP_CONTACTS_EMAILS_RANK;
    Create Temporary table TMP_CONTACTS_EMAILS_RANK (
                ID_TMP_CONTACTS_EMAILS_RANK Int Unsigned Not Null Primary Key AUTO_INCREMENT
                , CONTACTS_EMAILS_ID_CONTACTS_EMAILS Int Unsigned Not Null
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_EMAILS_RANK_01 (CONTACTS_EMAILS_ID_CONTACTS_EMAILS)
                , Index IX_TMP_CONTACTS_EMAILS_RANK_02 (CONTACTS_ID_CONTACTS)
            )
        ;
    Drop Temporary table If Exists TMP_CONTACTS_EMAILS_MINRANK;
    Create Temporary table TMP_CONTACTS_EMAILS_MINRANK (
                TMP_CONTACTS_EMAILS_RANK_ID_TMP_CONTACTS_EMAILS_RANK Int Unsigned Not Null Primary Key
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_EMAILS_MINRANK_01 (CONTACTS_ID_CONTACTS)
            )
        ;

    Insert Into TMP_CONTACTS_EMAILS_RANK (
            CONTACTS_EMAILS_ID_CONTACTS_EMAILS, CONTACTS_ID_CONTACTS
        )
    Select CONTACTS_EMAILS_ID_CONTACTS_EMAILS, CONTACTS_ID_CONTACTS
    From TMP_V_PUBLIC_CONTACTS_EMAILS_VIEW
    Where
        CONTACTS_EMAILS_TRASHED = 0
    Order By CONTACTS_ID_CONTACTS
            , CONTACTS_EMAILS_PREFERENCE Desc
            , Case DF_CHECK_STATE_CODE 
                    When 'DOCHECK' Then '1'
                    When 'CHECKED' Then '2'
                    Else '0'
                End Desc
            , CONTACTS_EMAILS_ID_CONTACTS_EMAILS Desc

        -- si préf
        -- si valid
        -- dernier id
    ;

    Insert Into TMP_CONTACTS_EMAILS_MINRANK(
            TMP_CONTACTS_EMAILS_RANK_ID_TMP_CONTACTS_EMAILS_RANK
            , CONTACTS_ID_CONTACTS
        )
    Select min(ID_TMP_CONTACTS_EMAILS_RANK)
        , CONTACTS_ID_CONTACTS
    from TMP_CONTACTS_EMAILS_RANK
    Group By CONTACTS_ID_CONTACTS
    ;




    Update
        TMP_V_PUBLIC_CONTACTS_EMAILS_VIEW tar
        Inner Join TMP_CONTACTS_EMAILS_RANK r1
            On tar.CONTACTS_EMAILS_ID_CONTACTS_EMAILS = r1.CONTACTS_EMAILS_ID_CONTACTS_EMAILS
        Inner Join TMP_CONTACTS_EMAILS_MINRANK r2
            On tar.CONTACTS_ID_CONTACTS = r2.CONTACTS_ID_CONTACTS
    Set
        -- les email existant commencent à 1
        tar.CONTACTS_EMAILS_VIEW_RANK = r1.ID_TMP_CONTACTS_EMAILS_RANK - r2.TMP_CONTACTS_EMAILS_RANK_ID_TMP_CONTACTS_EMAILS_RANK + 1
    ;

    -- mise à jour des rank 0
    Update
        TMP_V_PUBLIC_CONTACTS_EMAILS_VIEW tar
    Set
        tar.CONTACTS_EMAILS_VIEW_RANK = 0
    Where 
        tar.CONTACTS_EMAILS_VIEW_RANK Is Null
    ;

    -- purges des données provisoires
    Drop Temporary table If Exists TMP_CONTACTS_EMAILS_RANK;
    Drop Temporary table If Exists TMP_CONTACTS_EMAILS_MINRANK;

End $$
Delimiter ;

