Delimiter //
/**
 * Author:  Benoit DD <b.danquechindorval@amo-soft.com>
 * Created: 2 nov. 2016
 */

Drop Procedure if Exists BUSINESS_PROCESS_MESSAGES_STATUS_LOGS//

Create Procedure BUSINESS_PROCESS_MESSAGES_STATUS_LOGS()
Begin
    main: begin

    -- Set status
    Update 
        MESSAGES_STATUS_LOGS MSL
        Inner Join DF_MSG_STATUS S
            On MESSAGES_STATUS_LOGS_RAW_STATUS = S.DF_MSG_STATUS_CODE
    Set
        MSL.DF_MSG_STATUS_ID_DF_MSG_STATUS = S.ID_DF_MSG_STATUS
    Where
        MSL.MESSAGES_STATUS_LOGS_IS_PROCESSED = 0
    ;

    -- Set recipients
    -- TODO
    
    -- Update contact emails status for 'Bounce'
    Set @idMsgStatusBounce = (Select ID_DF_MSG_STATUS From DF_MSG_STATUS Where DF_MSG_STATUS_CODE = 'BOUNCE');
    Set @idCheckStateDocheck = (Select ID_DF_CHECK_STATE From DF_CHECK_STATE Where DF_CHECK_STATE_CODE = 'DOCHECK');
    Update 
        CONTACTS_EMAILS E
        Inner Join (
            Select 
                MESSAGES_STATUS_LOGS_EMAIL
            From 
                MESSAGES_STATUS_LOGS
            Where 
                Coalesce(DF_MSG_STATUS_ID_DF_MSG_STATUS, 0) = @idMsgStatusBounce
                And MESSAGES_STATUS_LOGS_IS_PROCESSED = 0
            Group By 
                MESSAGES_STATUS_LOGS_EMAIL
        ) MSL
            On MSL.MESSAGES_STATUS_LOGS_EMAIL = E.CONTACTS_EMAILS_EMAIL
    Set
        DF_CHECK_STAT_ID_DF_CHECK_STAT = @idCheckStateDocheck
        , CONTACTS_EMAILS_LAST_UPDATE = NOW()
    Where
        CONTACTS_EMAILS_TRASHED = 0
    ;

    -- Update contact emails status for 'Blocked' or 'Spam'
    Set @idMsgStatusBlocked = (Select ID_DF_MSG_STATUS From DF_MSG_STATUS Where DF_MSG_STATUS_CODE = 'BLOCKED');
    Set @idMsgStatusSpam = (Select ID_DF_MSG_STATUS From DF_MSG_STATUS Where DF_MSG_STATUS_CODE = 'SPAM');
    Set @idCheckStateBad = (Select ID_DF_CHECK_STATE From DF_CHECK_STATE Where DF_CHECK_STATE_CODE = 'BAD');
    Update 
        CONTACTS_EMAILS E
        Inner Join (
            Select 
                MESSAGES_STATUS_LOGS_EMAIL
            From 
                MESSAGES_STATUS_LOGS
            Where 
                Coalesce(DF_MSG_STATUS_ID_DF_MSG_STATUS, 0) In (@idMsgStatusBlocked, @idMsgStatusSpam)
                And MESSAGES_STATUS_LOGS_IS_PROCESSED = 0
            Group By 
                MESSAGES_STATUS_LOGS_EMAIL
        ) MSL
            On MSL.MESSAGES_STATUS_LOGS_EMAIL = E.CONTACTS_EMAILS_EMAIL
    Set
        DF_CHECK_STAT_ID_DF_CHECK_STAT = @idCheckStateBad
        , CONTACTS_EMAILS_LAST_UPDATE = NOW()
    Where
        CONTACTS_EMAILS_TRASHED = 0
    ;

    -- Update recipient status
    -- TODO

    -- Set as processed
    Update 
        MESSAGES_STATUS_LOGS MSL
    Set
        MSL.MESSAGES_STATUS_LOGS_IS_PROCESSED = 1
    Where
        MSL.MESSAGES_STATUS_LOGS_IS_PROCESSED = 0
    ;

    End;
End //
