Delimiter //
/**
 * If email is provided:
 *  - and is in application, then set the email id
 *  - and is not in application, then set a new email id
 * 
 * @param int batchnum
 * @author Benoit
 * @ticket #3833 #6303
 */ 
Drop Procedure if Exists LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED//

Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED (
    In batchnum int
)
Begin

    set @lcnmep_batchnum = batchnum;
    Set @default_email_state  = (
                select ID_DF_CHECK_STATE 
                from DF_CHECK_STATE 
                where DF_CHECK_STATE_CONTACTS = 1 
                    and DF_CHECK_STATE_CODE = 'DOCHECK' 
                limit 1
            )
        ;

    Drop Temporary Table If Exists tmp_CONTACTS_EMAILS;
    Create Temporary Table tmp_CONTACTS_EMAILS (
            LOADER_STATUS_ID_LOADER_STATUS Int Not Null,
--             ID_CONTACTS_EMAILS int(10) Unsigned Not Null Auto_Increment,
            LOADER_CONTACTS_ID_LOADER_CONTACTS Int Unsigned Not Null, 


            DF_CHECK_STAT_ID_DF_CHECK_STAT int(10) Unsigned Not Null,
            CONTACTS_EMAILS_EMAIL varchar(255) Not Null,
            CONTACTS_EMAILS_PREFERENCE smallint(6) Not Null Default '0',
            CONTACTS_EMAILS_TRASHED smallint(6) Not Null Default '0',
            CONTACTS_EMAILS_LAST_UPDATE datetime Not Null,

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


             Index (LOADER_STATUS_ID_LOADER_STATUS)
            , Index (LOADER_CONTACTS_ID_LOADER_CONTACTS)
        ) 
    ;

    CALL LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED_DOINSERT(batchnum, 'CONTACTS_EMAILS_EMAIL', 'tmp_CONTACTS_EMAILS', @default_email_state);
    CALL LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED_DOINSERT(batchnum, 'CONTACTS_EMAILS_EMAIL2', 'tmp_CONTACTS_EMAILS', @default_email_state);
    CALL LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED_DOINSERT(batchnum, 'CONTACTS_EMAILS_EMAIL3', 'tmp_CONTACTS_EMAILS', @default_email_state);
-- select 'here', src.* from tmp_CONTACTS_EMAILS src;
    -- mise à jour des info qualité et préférence
    Update
        tmp_CONTACTS_EMAILS 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_EMAILS tar1
                    Where lc.ID_CONTACTS = tar1.CONTACTS_ID_CONTACTS
                        And tmpsrc.CONTACTS_EMAILS_EMAIL = tar1.CONTACTS_EMAILS_EMAIL
                    Limit 1
                )
                , tmpsrc.DF_CHECK_STAT_ID_DF_CHECK_STAT
            )
        , tmpsrc.CONTACTS_EMAILS_PREFERENCE = Coalesce(
                (
                    Select tar1.CONTACTS_EMAILS_PREFERENCE
                    From CONTACTS_EMAILS tar1
                    Where lc.ID_CONTACTS = tar1.CONTACTS_ID_CONTACTS
                        And tmpsrc.CONTACTS_EMAILS_EMAIL = tar1.CONTACTS_EMAILS_EMAIL
                    Limit 1
                )
                , tmpsrc.CONTACTS_EMAILS_PREFERENCE
            )
--         , tmpsrc.CONTACTS_EMAILS_TRASHED = Coalesce(
--                 (
--                     Select tar1.CONTACTS_EMAILS_TRASHED
--                     From CONTACTS_EMAILS tar1
--                     Where lc.ID_CONTACTS = tar1.CONTACTS_ID_CONTACTS
--                         And tmpsrc.CONTACTS_EMAILS_EMAIL = tar1.CONTACTS_EMAILS_EMAIL
--                     Limit 1
--                 )
--                 , tmpsrc.CONTACTS_EMAILS_TRASHED
--             )
    ;
--     détection du mode d'import (ajout / pure et remplace),
    set @codeModeImportPurge= 'M-EMAILS-PURGE';
    set @codeModeImportAdd= 'M-EMAILS-ADD';
    Set @loaderTemplateId = GET_LOADER_INFO('ID_TEMPLATE_LOADER', batchnum);
    Set @modeImportEmail = 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  @modeImportEmail = @codeModeImportAdd then
        -- mode ajout:
        --  - Ajoute systématiquement les emails 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 TCE
--         Select *
        From 
            LOADER_STATUS LST
            Inner Join LOADER_CONTACTS LCT
                On LCT.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
            Inner Join tmp_CONTACTS_EMAILS TCE
                On TCE.LOADER_CONTACTS_ID_LOADER_CONTACTS = LCT.ID_LOADER_CONTACTS
        Where LST.LOADER_STATUS_BATCH_NUM = @lcnmep_batchnum
            And Exists (
                Select
                    CTEM.CONTACTS_EMAILS_EMAIL
                From CONTACTS_EMAILS CTEM
                Where
                    -- meme ctc
                    -- meme tel
                    -- => on suprimme la données des tels à ajouter/MAJ
                    CTEM.CONTACTS_ID_CONTACTS = LCT.ID_CONTACTS 
                    And CTEM.CONTACTS_EMAILS_EMAIL = TCE.CONTACTS_EMAILS_EMAIL
                Limit 1
            ) 
        ;

    End if;

    Insert Into LOADER_CONTACTS_EMAILS (
        LOADER_STATUS_ID_LOADER_STATUS, LOADER_CONTACTS_ID_LOADER_CONTACTS
        , DF_CHECK_STAT_ID_DF_CHECK_STAT, CONTACTS_EMAILS_EMAIL
        , CONTACTS_EMAILS_PREFERENCE, CONTACTS_EMAILS_TRASHED
        , CONTACTS_EMAILS_LAST_UPDATE, CONTACTS_EMAILS_ORIGIN
    )
    Select tce.LOADER_STATUS_ID_LOADER_STATUS, tce.LOADER_CONTACTS_ID_LOADER_CONTACTS
        , tce.DF_CHECK_STAT_ID_DF_CHECK_STAT, tce.CONTACTS_EMAILS_EMAIL
        , tce.CONTACTS_EMAILS_PREFERENCE, tce.CONTACTS_EMAILS_TRASHED
        , tce.CONTACTS_EMAILS_LAST_UPDATE, tce.CONTACTS_EMAILS_ORIGIN
    From tmp_CONTACTS_EMAILS tce
    ;

    -- 4) Suppression table temporaire
    Drop Temporary Table tmp_CONTACTS_EMAILS;

End //
Delimiter ;