Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */
--  ContactsPhones
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_PHONES $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_PHONES ()
Begin
    Truncate Table TMP_V_PUBLIC_CONTACTS_PHONES_VIEW;

    Insert Into TMP_V_PUBLIC_CONTACTS_PHONES_VIEW (
        CONTACTS_PHONES_ID_CONTACTS_PHONES
        , CONTACTS_ID_CONTACTS
        , DF_PHONE_TYP_ID_DF_PHONE_TYP
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_PHONE_NUMBER
        , CONTACTS_PHONE_NUMERIC_ONLY
        , CONTACTS_PHONE_PREFERENCE
        , CONTACTS_PHONES_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_PHONE_TYPES_LNG_LABEL
        , DF_CONTACTS_PHONE_TYPES_CODE
        , DF_CHECK_STATE_ORDER
        , DF_CHECK_STATE_DEFAULT
    )
    Select 
        CONTACTS_PHONES_ID_CONTACTS_PHONES
        , CONTACTS_ID_CONTACTS
        , DF_PHONE_TYP_ID_DF_PHONE_TYP
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_PHONE_NUMBER
        , CONTACTS_PHONE_NUMERIC_ONLY
        , CONTACTS_PHONE_PREFERENCE
        , CONTACTS_PHONES_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_PHONE_TYPES_LNG_LABEL
        , DF_CONTACTS_PHONE_TYPES_CODE
        , DF_CHECK_STATE_ORDER
        , DF_CHECK_STATE_DEFAULT
    From V_PUBLIC_CONTACTS_PHONES_VIEW Where
        -- ci-dessous la champ TMP_V_PUBLIC_CONTACTS_PHONES_VIEW.CONTACTS_PHONES_ID_CONTACTS_PHONES n'est pas nullable et
        -- la valeur par défaut n'est pas inséré du fait de la presence du champ dans la projection
        -- pour avoir touts les contacts, il faudra rendre nullable le champ TMP_V_PUBLIC_CONTACTS_PHONES_VIEW.CONTACTS_PHONES_ID_CONTACTS_PHONES
        V_PUBLIC_CONTACTS_PHONES_VIEW.CONTACTS_PHONES_ID_CONTACTS_PHONES Is Not Null
    ;

    -- les données sont populées
    -- on va calculer le RANK
    Drop Temporary table If Exists TMP_CONTACTS_PHONES_RANK;
    Create Temporary table TMP_CONTACTS_PHONES_RANK (
                ID_TMP_CONTACTS_PHONES_RANK Int Unsigned Not Null Primary Key AUTO_INCREMENT
                , CONTACTS_PHONES_ID_CONTACTS_PHONES Int Unsigned Not Null
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_PHONES_RANK_01 (CONTACTS_PHONES_ID_CONTACTS_PHONES)
                , Index IX_TMP_CONTACTS_PHONES_RANK_02 (CONTACTS_ID_CONTACTS)
            )
        ;

    Drop Temporary table If Exists TMP_CONTACTS_PHONES_MINRANK;
    Create Temporary table TMP_CONTACTS_PHONES_MINRANK (
                TMP_CONTACTS_PHONES_RANK_ID_TMP_CONTACTS_PHONES_RANK Int Unsigned Not Null Primary Key
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_PHONES_MINRANK_01 (CONTACTS_ID_CONTACTS)
            )
        ;
    
    Insert Into TMP_CONTACTS_PHONES_RANK (
            CONTACTS_PHONES_ID_CONTACTS_PHONES, CONTACTS_ID_CONTACTS
        )
    Select CONTACTS_PHONES_ID_CONTACTS_PHONES, CONTACTS_ID_CONTACTS
    From TMP_V_PUBLIC_CONTACTS_PHONES_VIEW
    Order By CONTACTS_ID_CONTACTS
            , CONTACTS_PHONE_PREFERENCE Desc
            , Case DF_CHECK_STATE_CODE 
                    When 'DOCHECK' Then '1'
                    When 'CHECKED' Then '2'
                    Else '0'
                End Desc
            , CONTACTS_PHONES_ID_CONTACTS_PHONES Desc
        -- si préf
        -- si valid
        -- dernier id
    ;

    Insert Into TMP_CONTACTS_PHONES_MINRANK(
            TMP_CONTACTS_PHONES_RANK_ID_TMP_CONTACTS_PHONES_RANK
            , CONTACTS_ID_CONTACTS
        )
    Select min(ID_TMP_CONTACTS_PHONES_RANK)
        , CONTACTS_ID_CONTACTS
    from TMP_CONTACTS_PHONES_RANK
    Group By CONTACTS_ID_CONTACTS
    ;

    Update
        TMP_V_PUBLIC_CONTACTS_PHONES_VIEW tar
        Inner Join TMP_CONTACTS_PHONES_RANK r1
            On tar.CONTACTS_PHONES_ID_CONTACTS_PHONES = r1.CONTACTS_PHONES_ID_CONTACTS_PHONES
        Inner Join TMP_CONTACTS_PHONES_MINRANK r2
            On tar.CONTACTS_ID_CONTACTS = r2.CONTACTS_ID_CONTACTS
    Set
        -- les tél existant commencent à 1
        tar.CONTACTS_PHONES_VIEW_RANK = r1.ID_TMP_CONTACTS_PHONES_RANK - r2.TMP_CONTACTS_PHONES_RANK_ID_TMP_CONTACTS_PHONES_RANK + 1
    ;

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

    -- purges des données provisoires
    Drop Temporary table If Exists TMP_CONTACTS_PHONES_RANK;
    Drop Temporary table If Exists TMP_CONTACTS_PHONES_MINRANK;

End $$
Delimiter ;


