Delimiter $$
/**
 * Author:  armen
 * Created: 21 juin 2018
 */
--  ContactsAdresses
Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_ADDRESSES $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_CONTACTS_ADDRESSES ()
Begin
    Truncate Table TMP_V_PUBLIC_CONTACTS_ADDRESSES_VIEW;

    Insert Into TMP_V_PUBLIC_CONTACTS_ADDRESSES_VIEW (
        CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES
        , DF_COUNTRY_ID_DF_COUNTRY
        , CONTACTS_ID_CONTACTS
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_ADDRESSES_LINE_1
        , CONTACTS_ADDRESSES_LINE_2
        , CONTACTS_ADDRESSES_LINE_3
        , CONTACTS_ADDRESSES_LINE_4
        , CONTACTS_ADDRESSES_ZIP_CODE
        , CONTACTS_ADDRESSES_CITY
        , CONTACTS_ADDRESSES_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_CHECK_STATE_DEFAULT
        , DF_CHECK_STATE_ORDER
        , DF_COUNTRY_LABEL
        , ID_DF_COUNTRY
        , DF_COUNTRY_LONG_CODE
        , DF_COUNTRY_SHORT_CODE
    )
    Select 
        CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES
        , DF_COUNTRY_ID_DF_COUNTRY
        , CONTACTS_ID_CONTACTS
        , DF_CHECK_STAT_ID_DF_CHECK_STAT
        , CONTACTS_ADDRESSES_LINE_1
        , CONTACTS_ADDRESSES_LINE_2
        , CONTACTS_ADDRESSES_LINE_3
        , CONTACTS_ADDRESSES_LINE_4
        , CONTACTS_ADDRESSES_ZIP_CODE
        , CONTACTS_ADDRESSES_CITY
        , CONTACTS_ADDRESSES_LAST_UPDATE
        , DF_CHECK_STATE_LNG_LABEL
        , DF_CHECK_STATE_CODE
        , DF_CHECK_STATE_DEFAULT
        , DF_CHECK_STATE_ORDER
        , DF_COUNTRY_LABEL
        , ID_DF_COUNTRY
        , DF_COUNTRY_LONG_CODE
        , DF_COUNTRY_SHORT_CODE
    From V_PUBLIC_CONTACTS_ADDRESSES_VIEW
    Where
        -- ci-dessous la champ TMP_V_PUBLIC_CONTACTS_ADDRESSES_VIEW.CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES 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_ADDRESSES_VIEW.CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES
        V_PUBLIC_CONTACTS_ADDRESSES_VIEW.CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES Is Not Null

    ;

    -- les données sont populées
    -- on va calculer le RANK
    Drop Temporary table If Exists TMP_CONTACTS_ADDRESSES_RANK;
    Create Temporary table TMP_CONTACTS_ADDRESSES_RANK (
                ID_TMP_CONTACTS_ADDRESSES_RANK Int Unsigned Not Null Primary Key AUTO_INCREMENT
                , CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES Int Unsigned Not Null
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_ADDRESSES_RANK_01 (CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES)
                , Index IX_TMP_CONTACTS_ADDRESSES_RANK_02 (CONTACTS_ID_CONTACTS)
            )
        ;
    Drop Temporary table If Exists TMP_CONTACTS_ADDRESSES_MINRANK;
    Create Temporary table TMP_CONTACTS_ADDRESSES_MINRANK (
                TMP_CONTACTS_ADDRESSES_RANK_ID_TMP_CONTACTS_ADDRESSES_RANK Int Unsigned Not Null Primary Key
                , CONTACTS_ID_CONTACTS Int Unsigned Not Null
                , Index IX_TMP_CONTACTS_ADDRESSES_MINRANK_01 (CONTACTS_ID_CONTACTS)
            )
        ;

    Insert Into TMP_CONTACTS_ADDRESSES_RANK (
            CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES, CONTACTS_ID_CONTACTS
        )
    Select CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES, CONTACTS_ID_CONTACTS
    From TMP_V_PUBLIC_CONTACTS_ADDRESSES_VIEW
    Order By CONTACTS_ID_CONTACTS
            , CONTACTS_ADDRESSES_LAST_UPDATE Desc
            , CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES Desc
    ;
    Insert Into TMP_CONTACTS_ADDRESSES_MINRANK(
            TMP_CONTACTS_ADDRESSES_RANK_ID_TMP_CONTACTS_ADDRESSES_RANK
            , CONTACTS_ID_CONTACTS
        )
    Select min(ID_TMP_CONTACTS_ADDRESSES_RANK)
        , CONTACTS_ID_CONTACTS
    from TMP_CONTACTS_ADDRESSES_RANK
    Group By CONTACTS_ID_CONTACTS
    ;

    Update
        TMP_V_PUBLIC_CONTACTS_ADDRESSES_VIEW tar
        Inner Join TMP_CONTACTS_ADDRESSES_RANK r1
            On tar.CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES = r1.CONTACTS_ADDRESSES_ID_CONTACTS_ADDRESSES
        Inner Join TMP_CONTACTS_ADDRESSES_MINRANK r2
            On tar.CONTACTS_ID_CONTACTS = r2.CONTACTS_ID_CONTACTS
    Set
        -- les adresses existant commencent à 1
        tar.CONTACTS_ADDRESSES_VIEW_RANK = r1.ID_TMP_CONTACTS_ADDRESSES_RANK - r2.TMP_CONTACTS_ADDRESSES_RANK_ID_TMP_CONTACTS_ADDRESSES_RANK + 1
    ;

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

    -- purges des données provisoires
    Drop Temporary table If Exists TMP_CONTACTS_ADDRESSES_RANK;
    Drop Temporary table If Exists TMP_CONTACTS_ADDRESSES_MINRANK;


End $$
Delimiter ;

