use strict;
use warnings;

use DBI;
use Email::Find;
use File::Find;


my $db_id;
my $dbh = DBI->connect("dsn", "user", "pword", {'RaiseError' => 1, 'AutoCommit' => 0}) or die "Connection Error: $DBI::errstr\n";

my $sthuser_insert = $dbh->prepare(q{INSERT INTO role(id,name,bw_name, role) VALUES (?,?,?,?)}) or die "Couldn't prepare statement: " . $dbh->errstr;

my $sthuser_check = $dbh->prepare(q{SELECT name FROM role  WHERE name = ?}) or die "Couldn't prepare statement: " . $dbh->errstr;

my $dir = "c:\\dir";

get_index_id();

find (\&wanted, $dir);
$dbh->disconnect();

sub wanted 
{

   my @useroutput;
   return unless ($_ =~ /\.LIST/i);
      
   open (IN, "$_") or die "Can't open $_";
   @useroutput = <IN>;
   close(IN);
   chomp @useroutput;
         
	
   foreach my $person (@useroutput) {
      my $finder = Email::Find->new(\&email_found);
      $finder->find(\$person);
    }
      
}

sub get_index_id 
{
   my $db = DBI->connect("dsn", "user", "pword") or die "Connection Error: $DBI::errstr\n";
   my $sthlast_user;
   
   $sthlast_user=$db->prepare("SELECT MAX(id) FROM role");

   $sthlast_user->execute();
   ($db_id) = $sthlast_user->fetchrow_array();
   $sthlast_user->finish();
   $db->disconnect();
   print $db_id ." : found and closed\n";

}

sub create_random 
{
   
   my @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9, qw(! @ $ ^ & *) );
   my $user_name = join("", @chars[ map { rand @chars } ( 1 .. 6 ) ]);
   return $user_name . $db_id++;
}

sub email_found 
{
   my($email, $orig_email) = @_;
   my $user = "user";
   my $founduser = 0;
   my $caluser = create_random(). "\@localhost.com";
         
   #need to check if user email exists. If not then add
   
   $sthuser_check->execute($orig_email);
   $founduser = $sthuser_check->fetchrow_array();
   $sthuser_check->finish();
    
   if (! $founduser){
      if ($orig_email !~ /[a-z, A-Z, 0-9]\@jiscmail.ac.uk/) {
         print "Inserting $orig_email \n";
         $sthuser_insert->execute(+$db_id, lc($orig_email), $caluser, $user);
         print "Id: $db_id belongs to $orig_email with name $caluser and role is $user\n";
         $sthuser_insert->finish();
         next;
      } else {
         next;
      }
   }
   else
   {
      print "Found " . $founduser ."\n";
      next;
   } 
   return $orig_email;
}