/***************************************************************************
 PIPER_RX  By Gary Piper

 (C) Copyright G.Piper 2011
 All rights reserved

File Name:       PIPER_RX_EMAIL_VALIDATION

E-mail validation checks:

    Minimum email address length
    minimum of one dot
    Must contain only one @ symbol
    @ and dot are not together
    Check @ not first character
    Check dot not first character
    Check @  not last character
    Check dot not last character
    There must be one dot after an @ symbol
    Check for two dots together
    Invalid Characters
    Check for banned domains ( OPTIONAL )

NOTE: This function does not use regexp as this only exists in 10g and above

+Version 1.0  G.Piper	Initial creation

****************************************************************************
 
 This program is free software: you can redistribute it and/or modify
 it under the terms of the GNU Affero General Public License as published by
 the Free Software Foundation, either version 3 of the License, or
 (at your option) any later version.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU Affero General Public License for more details.

 a copy of the GNU Affero General Public Licenses provided is part of the 
 down load Terms and Conditions available on our site www.piper-rx.com or 
 you can refere to www.gnu.org/licenses/gpl.html

****************************************************************************/

CREATE OR REPLACE FUNCTION piper_rx_email_validation ( i_email_address in varchar2 )
          RETURN VARCHAR2 IS

error_message          varchar2(200);
v_pass_fail            varchar2(10) := 'FAIL'; -- Assumes FAIL until passes tests
v_first_at_location    number(5);
v_first_dot_location   number(5);
v_last_email_value     varchar2(5);
v_dot_pos_after_at     number(5);
v_two_dots_location    number(5);
v_em_length            number(10);
v_at_count             number(5) :=0;

v_check_banned_domains boolean := TRUE;
v_banned_domain_count  number(5) :=0;

-- Loop Variables
v_length_counter       number(10);
v_email_char           varchar(5); -- Needs to be long enough to cover double byte characters
i                      number(10); -- loop counter

BEGIN

v_first_dot_location := nvl(instr(i_email_address,'.'), 0); -- Location of first dot   
v_first_at_location  := nvl(instr(i_email_address,'@'), 0); -- Location of first @ symbol
v_last_email_value   := substr(i_email_address, length(i_email_address), 1); -- last character in e-mail address
v_dot_pos_after_at   := instr(i_email_address, '.', instr(i_email_address,'@')); -- dot position after at
v_two_dots_location  := nvl(instr(i_email_address,'..'), 0); -- location of two dots

-- *************************************          
-- Check minimum length of email address - A@A.A
-- *************************************
v_em_length := length(i_email_address);
   
 IF ( v_em_length < 5 ) THEN 

    RETURN v_pass_fail;

 END IF;

-- ********************************          
-- Check for minimum of one dot
-- ********************************
   
 IF ( v_first_dot_location < 1 ) THEN 

    RETURN v_pass_fail;

 END IF;

-- ********************************
-- Must contain only one @ symbol
-- ********************************
v_length_counter := v_em_length;
i := 1;
v_at_count := 0;
      
WHILE v_length_counter > 0
  LOOP
     v_email_char := substr(i_email_address,i,1);
     i := i+1;
     v_length_counter := v_length_counter - 1;

     IF (v_email_char = '@')  THEN

        v_at_count := v_at_count + 1;

     END IF;

  END LOOP;

   -- If not one @ symbol then fail
  IF (v_at_count != 1 ) THEN
       
    RETURN v_pass_fail;

  END IF;

-- ***********************************
--   Check AT and DOT are not together
-- ***********************************
    
  IF (( v_first_dot_location = v_first_at_location + 1)
        OR 
        ( v_first_at_location = v_first_dot_location + 1) ) THEN

    RETURN v_pass_fail; 

  END IF;
    
-- ********************************
-- Check @ or dot not first vlue
-- ********************************
    
 IF ( v_first_dot_location = 1 ) THEN

    RETURN v_pass_fail;   
    
 END IF;
    
 IF ( v_first_at_location = 1 ) THEN

    RETURN v_pass_fail;   
    
 END IF;

-- ********************************
-- Check @ or dot not last value
-- ********************************

 IF ( v_last_email_value = '@' ) THEN

    RETURN v_pass_fail;       
    
 END IF;

 IF ( v_last_email_value = '.' ) THEN

    RETURN v_pass_fail;      
    
 END IF;

-- ****************************************
-- Check for at least one dot after the @
-- ****************************************

 IF ( v_dot_pos_after_at < 1  ) THEN

    RETURN v_pass_fail;    
    
 END IF;

-- ****************************************
-- Check for two dots together
-- ****************************************

 IF ( v_two_dots_location > 0 ) THEN

    RETURN v_pass_fail;    
    
 END IF;

-- ********************************
-- CHECK for invalid characters
-- ********************************

   --Reset for next email address
   v_length_counter := v_em_length;
   i := 1;
    
   WHILE v_length_counter > 0
   LOOP
      v_email_char := substr(i_email_address,i,1);
      i := i + 1;
      v_length_counter := v_length_counter -1;

      IF v_email_char IN ('',' ','=','"','!','#','$','%','^','&','*','(',')','+' ,'|','{','}','[',']',':','>','<','?','/','\') THEN

        RETURN v_pass_fail; 
          
        EXIT; -- Exit loop on first invalid character
          
      END IF;

   END LOOP;

-- ********************************
-- Check if Banned domain
-- ********************************
-- Given the large number of dodgy domains
-- This check should really reference a table of banned domains...

 IF ( v_check_banned_domains ) THEN

    SELECT count(*)
      INTO v_banned_domain_count
      FROM dual
     WHERE lower(substr(i_email_address,
               nvl(instr(i_email_address,'@'), 0) + 1)
               )
           IN  
           ( --Generaic mail accounts 
              'facebookmail.com',
              'gmail.com',
              'googlemail.com',              
              'hotmail.com',
              'hushmail.com',              
              'luckymail.com',
              'mail.com',                                          
              'yahoo.com',
              'y7mail.com', 
              -- Adult content domains
              'xxx.com'
              -- ADD MORE HERE
               );

     IF ( v_banned_domain_count > 0 ) THEN

        RETURN v_pass_fail;    

     END IF;        

 END IF;
 
-- ********************************
-- If it gets this far it passes :-)
-- ********************************

v_pass_fail := 'PASS';
RETURN v_pass_fail;

-- *****************************
-- **       EXCEPTION         **
-- *****************************
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  error_message := 'No data Found';
  dbms_output.put_line('PIPER_RX_EMAIL_VALIDATION: '||error_message);
 WHEN OTHERS THEN
   error_message := 'Other problem';
   dbms_output.put_line('PIPER_RX_EMAIL_VALIDATION: '||error_message);
   dbms_output.put_line('Oracle Error : '|| sqlerrm);

END piper_rx_email_validation;