Delimiter //
/**
 * Si le téléphone est nouveau pour ce contact, le créer avec les paramètres suivants :
 *  - clic-valideur = à valider
 *  - type de téléphone = importation
 *
 *  TA Si le téléphone existe déjà pour ce contact, ne rien faire
 * 
 * @param int batchnum
 * @author Jean-François
 * @ticket #2132
 */ 
Drop Procedure If Exists LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED//

Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED(
    In batchnum int
)
Begin

--     Declare default_phone_type int default 5; -- RELOADED
--     Declare default_phone_state int default 1; -- DOCHECK
    Set @default_phone_type  = (
                SELECT ID_DF_CONTACTS_PHONE_TYPES 
                from DF_CONTACTS_PHONE_TYPES 
                where DF_CONTACTS_PHONE_TYPES_CODE = 'RELOADED' 
                limit 1
            )
        ;
    Set @default_phone_state  = (
                select ID_DF_CHECK_STATE 
                from DF_CHECK_STATE 
                where DF_CHECK_STATE_CONTACTS = 1 
                    and DF_CHECK_STATE_CODE = 'DOCHECK' 
                limit 1
            )
        ;

    set @lcnmpp_batchnum = batchnum;
    -- set @lcnmpp_batchnum = 1;


    -- 1) Phones are given horizontally, we need for horizontal datas
    --    -> phones whose are not CONTACTS_PHONES
    -- recopi de la structure
    Drop Temporary Table If Exists tmp_CONTACTS_PHONES;
    Create Temporary Table tmp_CONTACTS_PHONES ( 
            LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null,

            LOADER_CONTACTS_ID_LOADER_CONTACTS Int Unsigned Not Null,
            `DF_PHONE_TYP_ID_DF_PHONE_TYP` int(10) unsigned Null,
            `DF_CHECK_STAT_ID_DF_CHECK_STAT` int(10) unsigned Null,
            `CONTACTS_PHONE_NUMBER` varchar(25) Null,
            `CONTACTS_PHONE_NUMERIC_ONLY` varchar(15) Null,
            `CONTACTS_PHONE_PREFERENCE` smallint(6) Null Default '0',
            `CONTACTS_PHONES_TRASHED` smallint(6) Null Default '0',
            `CONTACTS_PHONES_LAST_UPDATE` datetime Null,

            CONTACTS_PHONES_ORIGIN smallint(5) Not Null Default 2, -- origine = import 

            Index (LOADER_STATUS_ID_LOADER_STATUS)
            , Index (LOADER_CONTACTS_ID_LOADER_CONTACTS)
            , Index (CONTACTS_PHONE_NUMERIC_ONLY)
        )
    ;
    
    -- 2) Insertions
    call LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED_DOINSERT(@lcnmpp_batchnum, 'tmp_CONTACTS_PHONES', 'CONTACTS_PHONE1', @default_phone_type, @default_phone_state);
    call LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED_DOINSERT(@lcnmpp_batchnum, 'tmp_CONTACTS_PHONES', 'CONTACTS_PHONE2', @default_phone_type, @default_phone_state);
    call LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED_DOINSERT(@lcnmpp_batchnum, 'tmp_CONTACTS_PHONES', 'CONTACTS_PHONE3', @default_phone_type, @default_phone_state);

    -- mise à jour des info qualité et préférence
    Update
        tmp_CONTACTS_PHONES tmpsrc
        Inner Join LOADER_CONTACTS lc
            On tmpsrc.LOADER_CONTACTS_ID_LOADER_CONTACTS = lc.ID_LOADER_CONTACTS
    Set
        tmpsrc.DF_CHECK_STAT_ID_DF_CHECK_STAT = Coalesce(
                (
                    Select tar1.DF_CHECK_STAT_ID_DF_CHECK_STAT
                    From CONTACTS_PHONES tar1
                    Where lc.ID_CONTACTS = tar1.CONTACTS_ID_CONTACTS
                        And tmpsrc.CONTACTS_PHONE_NUMERIC_ONLY = tar1.CONTACTS_PHONE_NUMERIC_ONLY
                    Limit 1
                )
                , tmpsrc.DF_CHECK_STAT_ID_DF_CHECK_STAT
            )
        , tmpsrc.CONTACTS_PHONE_PREFERENCE = Coalesce(
                (
                    Select tar2.CONTACTS_PHONE_PREFERENCE
                    From CONTACTS_PHONES tar2
                    Where lc.ID_CONTACTS = tar2.CONTACTS_ID_CONTACTS
                        And tmpsrc.CONTACTS_PHONE_NUMERIC_ONLY = tar2.CONTACTS_PHONE_NUMERIC_ONLY
                    Limit 1
                )
                , tmpsrc.CONTACTS_PHONE_PREFERENCE
            )
--         , tmpsrc.CONTACTS_PHONES_TRASHED = Coalesce(
--                 (
--                     Select tar3.CONTACTS_PHONES_TRASHED
--                     From CONTACTS_PHONES tar3
--                     Where lc.ID_CONTACTS = tar3.CONTACTS_ID_CONTACTS
--                         And tmpsrc.CONTACTS_PHONE_NUMERIC_ONLY = tar3.CONTACTS_PHONE_NUMERIC_ONLY
--                     Limit 1
--                 )
--                 , tmpsrc.CONTACTS_PHONES_TRASHED
--             )
    ;

--     3) détection du mode d'import (ajout / pure et remplace),
    set @codeModeImportPurge= 'M-PHONES-PURGE';
    set @codeModeImportAdd= 'M-PHONES-ADD';
    Set @loaderTemplateId = GET_LOADER_INFO('ID_TEMPLATE_LOADER', batchnum);
    Set @modeImportTel = Coalesce(
                (
                    select
                        TEMPLATE_OPTIONS_CODE
                    From
                        TEMPL_LOADER_has_TEMPL_OPT tmplhasopt
                        Inner Join TEMPLATE_OPTIONS templo
                            On tmplhasopt.TEMPL_OPT_ID_TEMPL_OPT = templo.ID_TEMPLATE_OPTIONS
                    where
                        tmplhasopt.TEMPL_LOADER_ID_TEMPL_LOADER = @loaderTemplateId
                        And templo.TEMPLATE_OPTIONS_CODE In (@codeModeImportPurge, @codeModeImportAdd)
                    Limit 1
                )
                , @codeModeImportAdd
            )
        ;

    -- règle scpécifiés par OB (slack le 2020/06/17
    --  - l'import à le droit de modifier le type de tél (y compris pour les tél saisié manuellement)

    if  @modeImportTel = @codeModeImportAdd then
        -- mode ajout:
        --  - Ajoute systématiquement les numéros non encore créés dans l’application 
        --  - Les favoris et qualité (grillé/validé/non déterminé) sont conservés.
        -- on suprime dans les données à ajouter, les tels qui existent déjà en prod
        -- => permet de ne pas avoir de doublons
        -- => pas la peine de les mettre à jour
        -- => vu avec Oliver, permet de conserver le FLAG TRASHED, car l'import
        --     ne doit pas ecraser ce FLAG
        Delete TCP
        From 
            LOADER_STATUS LST
            Inner Join LOADER_CONTACTS LCT
                On LCT.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
            Inner Join tmp_CONTACTS_PHONES TCP
                On TCP.LOADER_CONTACTS_ID_LOADER_CONTACTS = LCT.ID_LOADER_CONTACTS
        Where LST.LOADER_STATUS_BATCH_NUM = @lcnmpp_batchnum
            And Exists (
                Select
                    CTPH.CONTACTS_PHONE_NUMERIC_ONLY
                From CONTACTS_PHONES CTPH
                Where
                    -- meme ctc
                    -- meme tel
                    -- => on suprimme la données des tels à ajouter/MAJ
                    CTPH.CONTACTS_ID_CONTACTS = LCT.ID_CONTACTS 
                    And CTPH.CONTACTS_PHONE_NUMERIC_ONLY = TCP.CONTACTS_PHONE_NUMERIC_ONLY 
                Limit 1
            ) 
        ;

    End if;

        

    -- 4) Update intermediate table
    Insert Into LOADER_CONTACTS_PHONES (
        LOADER_STATUS_ID_LOADER_STATUS, LOADER_CONTACTS_ID_LOADER_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_TRASHED
        , CONTACTS_PHONES_LAST_UPDATE, CONTACTS_PHONES_ORIGIN 
    )
    Select 
        tcp.LOADER_STATUS_ID_LOADER_STATUS, tcp.LOADER_CONTACTS_ID_LOADER_CONTACTS
        , tcp.DF_PHONE_TYP_ID_DF_PHONE_TYP, tcp.DF_CHECK_STAT_ID_DF_CHECK_STAT
        , tcp.CONTACTS_PHONE_NUMBER, tcp.CONTACTS_PHONE_NUMERIC_ONLY
        , tcp.CONTACTS_PHONE_PREFERENCE, tcp.CONTACTS_PHONES_TRASHED
        , tcp.CONTACTS_PHONES_LAST_UPDATE, tcp.CONTACTS_PHONES_ORIGIN 
    From tmp_CONTACTS_PHONES tcp
    ;
    
    -- 4) Suppression table temporaire
    Drop Temporary Table tmp_CONTACTS_PHONES;

End//
Delimiter ;