#!/usr/bin/perl

use strict;
use Mail::MboxParser;
use DBI;

############################
# ACCESSING MBOX

my $mymailbox="Sent";

#DUMMY VARIABLE FOR PARSER OPTIONS
my $parseropts="";

open(FD,"$mymailbox");
my @myarray = <FD>;
close (FD);

#CREATE A NEW MboxParser OBJECT_
my $mb = Mail::MboxParser->new(\@myarray,
         decode     => 'ALL',
         parseropts => $parseropts);

my $num_messages = $mb->nmsgs;
my $message_counter = 0;
#
############################

############################
# DB CONNECTION VARIABLES
my $db = "temp";
my $dsn = "dbi:Pg:dbname=$db;host=127.0.0.1;port=5432";
my $user = "postgres";
my $pass = "123";

my $dbh = DBI->connect($dsn,$user,$pass);
unless (defined $dbh) {die $DBI::errstr;} $dbh->{PrintError} = 0;
#
############################

print "\nstarting upload of mail box \'$mymailbox\'\n";

#########################################################
# LOOPING THROUGH MESSAGES IN MBOX
while (my $msg = $mb->next_message) {

   my $id = $msg->id;
   my $from = $msg->from->{email};
   my $subject = $msg->header->{subject};
   my $body = $msg->body($msg->find_body);
   my $body_file = "/tmp/body.txt";


   #POPULATE TABLE 'mailid'
   my $sql = "INSERT INTO mailid VALUES(\'$id\')";
   my $sth = $dbh->prepare($sql) or die $DBI::errstr;
   $sth->execute() or die $DBI::errstr;


   #POPULATE TABLE 'main'
   open(FD,">$body_file");
   print FD $body;
   close (FD);

   my $myoid = `psql -U postgres --command "\\lo_import \'$body_file\' \'$mymailbox\'" $db`;
   my @oidarray = split(/ /,$myoid);
   $sql = "INSERT INTO main VALUES(\'$id\',\'$from\',\'$subject\',@oidarray[1])";
   $sth = $dbh->prepare($sql) or die $DBI::errstr;
   $sth->execute() or die $DBI::errstr;

   #POPULATE TABLE 'mailto'
   for my $msg_TO ($msg->to) {
     $sql = "INSERT INTO mailto VALUES(\'$id\',\'$msg_TO->{email}\')";
     $sth = $dbh->prepare($sql) or die $DBI::errstr;
     $sth->execute() or die $DBI::errstr;
   }

   #POPULATE TABLE 'mailcc'
   for my $msg_CC ($msg->cc) {
     $sql = "INSERT INTO mailcc VALUES(\'$id\',\'$msg_CC->{email}\')";
     $sth = $dbh->prepare($sql) or die $DBI::errstr;
     $sth->execute() or die $DBI::errstr;
   }

   #########################################################
   #POPULATE TABLE 'attachment'
   my $mapping = $msg->get_attachments;

   for my $attachment_name(keys %$mapping)
   {
     my $attachment = $msg->body($mapping->{$attachment_name});
     my $attachment_file = "/tmp/attachment";
     my $msword_file = "/tmp/msword.txt";


     # DETERMINE ATTACHMENT MIME-TYPES
     my $decodedattachment = `echo "$attachment"|./decode.pl|tee $attachment_file|file -i -`;
     my @test0 = split(/ /,$decodedattachment);
     my $mime_type = @test0[1];
     chomp $mime_type;


     #VALIDATING MSWORD DOCS
     if ($mime_type eq "application/msword") {
       #CONVERT INTO READABLE TEXT
       `antiword $attachment_file > $msword_file;mv -f $msword_file $attachment_file`;
     } else {
       #NOT MSWORD DOCS,
       #USE THE ENCODED MIME VERSION IN THE FILE
       print "not msword document\n";
       `echo "$attachment" > $attachment_file`;
     }


     #ALL ATTACHMENTS ARE ARCHIVED IN MIME FORMAT
     #INCLUDING BINARY MSWORD DOCS
     $myoid = `psql -U postgres --command "\\lo_import \'$attachment_file\' \'$attachment_name\'" $db`;
     @oidarray = split(/ /,$myoid);
     chomp $mime_type;

     #STILL NEED TO CONSIDER THE OID FOR THE READABLE VERSION OF MSWORD DOCUMENT
     $sql = "INSERT INTO attachment VALUES(\'$id\',\'$attachment_name\',\'$mime_type\',@oidarray[1])";
     $sth = $dbh->prepare($sql) or die $DBI::errstr;
     $sth->execute() or die $DBI::errstr;
   }
   # END OF ATTACHMENT PROCESSING
   ######################################################

 print "processed ".++$message_counter."/".$num_messages." messages\n";
}
# END OF WHILE LOOP
#########################################################

print "DONE\n";