Delimiter //
/**
 * @test
 * Test For LOADER_CONTACTS_NORMALIZE_CONTACTS_CHECK_EMAIL_VALID()
 */
DROP Procedure IF EXISTS TEST_LOADER_CONTACTS_EMAIL_VALID//

Create Procedure TEST_LOADER_CONTACTS_EMAIL_VALID()
Begin

    -- initial dataset
    CALL LOADER_TEST_MOCK_CONTACTS_CLEAN();

    -- Valid cases
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(101, 'CONTACTS_EMAILS_EMAIL = ''contact@amo-soft.fr''', 101, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(102, 'CONTACTS_EMAILS_EMAIL = ''Abc@test.example.com''', 102, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(103, 'CONTACTS_EMAILS_EMAIL = ''Abc.123@example.com''', 103, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(104, 'CONTACTS_EMAILS_EMAIL = ''user+mailbox/department=shipping@example.com''', 104, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(105, 'CONTACTS_EMAILS_EMAIL = ''!#$%&''''*+-/=?^_`.{|}~@example.com''', 105, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(106, 'CONTACTS_EMAILS_EMAIL = ''"Abc@def"@example.com''', 106, NULL);
--     CALL LOADER_TEST_MOCK_CONTACTS_PUSH(107, 'CONTACTS_EMAILS_EMAIL = ''"Fred Bloggs"@example.com''', 107, NULL); -- Normalization limitation (cut after space, comma or semicolon)
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(108, 'CONTACTS_EMAILS_EMAIL = ''"Joe.\Blow"@example.com''', 108, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(109, 'CONTACTS_EMAILS_EMAIL = ''Loïc.Accentué@voilà-voilou.fr''', 109, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(110, 'CONTACTS_EMAILS_EMAIL = ''contact@amo-soft.fr; Abc@test.example.com''', 110, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(111, 'CONTACTS_EMAILS_EMAIL = ''contact@ουτοπία.δπθ.gr''', 111, NULL);

    -- Invalid cases
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(201, 'CONTACTS_EMAILS_EMAIL = ''not an email''', 201, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(202, 'CONTACTS_EMAILS_EMAIL = ''Abc.example.com''', 202, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(203, 'CONTACTS_EMAILS_EMAIL = ''Abc.@example.com''', 203, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(204, 'CONTACTS_EMAILS_EMAIL = ''Abc..123@example.com''', 204, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(205, 'CONTACTS_EMAILS_EMAIL = ''Abc@example..com''', 205, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(206, 'CONTACTS_EMAILS_EMAIL = ''Abc;123@example.com''', 206, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(207, 'CONTACTS_EMAILS_EMAIL = ''"Fred Bloggs"@example.com''', 207, NULL); -- Normalization limitation (cut after space, comma or semicolon)
    -- Procedure
    CALL LOADER_CONTACTS_NORMALIZE_CONTACTS_CHECK_EMAIL_VALID(1);

    -- Assertions
    CALL utAssert_eq('Contacts with a valid email are not flagged (#1)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 101) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#2)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 102) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#3)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 103) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#4)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 104) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#5)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 105) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#6)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 106) 
    );
--     CALL utAssert_eq('Contacts with a valid email are not flagged (#7)' 
--         , 0
--         , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 107) 
--     );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#8)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 108) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#9)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 109) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#10)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 110) 
    );
    CALL utAssert_eq('Contacts with a valid email are not flagged (#11)' 
        , 0
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 111) 
    );

    CALL utAssert_eq('Contacts with an invalid email are flagged (#1)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 201)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#2)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 202)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#3)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 203)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#4)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 204)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#5)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 205)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#6)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 206)
    );
    CALL utAssert_eq('Contacts with an invalid email are flagged (#7)'
        , 1
        , (SELECT LOADER_STATUS_WARNCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 207)
    );
    
    -- clean
    CALL LOADER_TEST_MOCK_CONTACTS_CLEAN();

End //
Delimiter ;