Delimiter //
/**
 * Checks if the contact's email is valid
 *
 * @param int batchnum
 * @return void
 * @author Benoit
 * @ticket #2133 #4127 #5734 #5735
 */
Drop Procedure if exists LOADER_CONTACTS_NORMALIZE_CONTACTS_CHECK_EMAIL_VALID//

Create Procedure LOADER_CONTACTS_NORMALIZE_CONTACTS_CHECK_EMAIL_VALID (
    In batchnum int
)
Begin

    main: Begin
		
		Call LOADER_BUILD_CONTACTS_CHECK_BASE_QUERY ('CONTACTS_EMAILS_EMAIL',
                    batchnum, 
                    'Adresse email non valide', 
                    null, 1,
                    @query
            );
        -- les sequences posi ne sont pas supportés par mariadb
#         Set @userpart = '[^[:cntrl:][:space:][.period.][.comma.][.semicolon.]]'; -- caractères imprimables sauf espaces et . , ;
        Set @userpart = '[^ .,;]'; -- caractères imprimables sauf espaces et . , ;
#         Set @dompart = '[[:alnum:][.hyphen.][.underscore.]]'; -- caractères alpha-numériques et - _
        Set @dompart = '[a-zA-Z0-9\-_]'; -- caractères alpha-numériques et - _
#         Set @extrapart = '([[.comma.][.semicolon.][.space.]]+[[:print:]]*)*'; -- éléments supplémentaires
        Set @extrapart = '([,; ]+[a-zA-Z0-9._\-éÉèÈêÊëËàÀœŒûÛüÜîÎïÏÇç<>]*)*'; -- éléments supplémentaires
        Set @query = Concat(@query,
#             ' And NOT LOADER_CONTACTS.CONTACTS_EMAILS_EMAIL REGEXP ''^(', @userpart, '+([[.period.]]', @userpart, '+)*@(', @dompart, '+[[.period.]])+[A-Z]{2,16})', @extrapart, '$''
            ' And NOT LOADER_CONTACTS.CONTACTS_EMAILS_EMAIL REGEXP ''^(', @userpart, '+([.]', @userpart, '+)*@(', @dompart, '+[.])+[A-Z]{2,16})', @extrapart, '$''
              And Char_Length(LOADER_CONTACTS.CONTACTS_EMAILS_EMAIL) > 0');

        Prepare stmt From @query;
		Execute stmt;
		Deallocate Prepare stmt;


    End ;

End //

Delimiter ;
