Delimiter //
/**
 * @test
 * Test For LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED()
 */
Drop Procedure IF EXISTS TEST_LOADER_CONTACTS_MAP_EMAIL_PROVIDED//

Create Procedure TEST_LOADER_CONTACTS_MAP_EMAIL_PROVIDED()
Begin

        -- initial dataset
        Call LOADER_TEST_MOCK_CONTACTS_CLEAN();
        Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS_EMAILS');

        Call LOADER_TEST_MOCK_CONTACTS_PUSH(1, 'ID_CONTACTS = 1, CONTACTS_EMAILS_EMAIL = ''exist@me.com''', 1, Null);
        Call LOADER_TEST_MOCK_CONTACTS_PUSH(2, 'ID_CONTACTS = 1, CONTACTS_EMAILS_EMAIL = ''new@me.com''', 2, Null);
        Call LOADER_TEST_MOCK_CONTACTS_PUSH(3, 'ID_CONTACTS_NEW = 1, CONTACTS_EMAILS_EMAIL = ''newnew@me.com''', 3, Null);
        Call LOADER_TEST_MOCK_CONTACTS_PUSH(4, 'ID_CONTACTS = 2, CONTACTS_EMAILS_EMAIL = ''''', 4, Null);
        Call LOADER_TEST_MOCK_CONTACTS_PUSH(5, 'ID_CONTACTS_NEW = 2, CONTACTS_EMAILS_EMAIL = ''first@me.com; extra@you.org''', 5, Null);

        Call LOADER_TEST_MOCK_BUSINESS_PUSH('CONTACTS_EMAILS',
                'ID_CONTACTS_EMAILS = 1
                , CONTACTS_ID_CONTACTS = 1
                , DF_CHECK_STAT_ID_DF_CHECK_STAT = 2
                , CONTACTS_EMAILS_EMAIL = ''exist@me.com''
                , CONTACTS_EMAILS_PREFERENCE = 0
                , CONTACTS_EMAILS_TRASHED = 0
                , CONTACTS_EMAILS_LAST_UPDATE = ''2000-01-01 02:01:01'''
        );

        -- Procedure
        Call LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED(1);

        -- Assertions
        Call utAssert_eq('Contacts with existing email keep their emails and associated data'
            , NULL
            , (SELECT LOADER_STATUS_ID_LOADER_STATUS FROM LOADER_CONTACTS_EMAILS 
               WHERE CONTACTS_EMAILS_EMAIL = 'exist@me.com' 
                    AND DF_CHECK_STAT_ID_DF_CHECK_STAT = 2
                    AND CONTACTS_EMAILS_LAST_UPDATE = '2000-01-01 02:01:01'
               )
        );
        Call utAssert_eq('Contacts with new email receveive required data for insertion' 
            , 2
            , (SELECT LOADER_STATUS_ID_LOADER_STATUS FROM LOADER_CONTACTS_EMAILS 
               WHERE CONTACTS_EMAILS_EMAIL = 'new@me.com' 
                    AND DF_CHECK_STAT_ID_DF_CHECK_STAT = 1
                    AND CONTACTS_EMAILS_LAST_UPDATE IS NOT NULL
               )
        );
        Call utAssert_eq('New contacts with email receveive required data for insertion' 
            , 3
            , (SELECT LOADER_STATUS_ID_LOADER_STATUS FROM LOADER_CONTACTS_EMAILS 
               WHERE CONTACTS_EMAILS_EMAIL = 'newnew@me.com' 
                    AND DF_CHECK_STAT_ID_DF_CHECK_STAT = 1
                    AND CONTACTS_EMAILS_LAST_UPDATE IS NOT NULL
               )
        );
        Call utAssert_eq('Contacts with no email do not receveive data for insertion' 
            , NULL
            , (SELECT LOADER_STATUS_ID_LOADER_STATUS FROM LOADER_CONTACTS_EMAILS 
               WHERE CONTACTS_EMAILS_EMAIL = '' 
                    AND DF_CHECK_STAT_ID_DF_CHECK_STAT IS NULL
                    AND CONTACTS_EMAILS_LAST_UPDATE IS NULL
               )
        );
        Call utAssert_eq('New contacts with multiple email or extra data receveive data for insertion' 
            , 5
            , (SELECT LOADER_STATUS_ID_LOADER_STATUS FROM LOADER_CONTACTS_EMAILS 
               WHERE CONTACTS_EMAILS_EMAIL = 'first@me.com' 
                    AND DF_CHECK_STAT_ID_DF_CHECK_STAT = 1
                    AND CONTACTS_EMAILS_LAST_UPDATE IS NOT NULL
               )
        );

    -- clean
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS_EMAILS');
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();

End //
Delimiter ;
