#!/usr/oracle/bin/oraperl

$ENV{"ORACLE_HOME"} = "/usr/oracle";
$ORACLE_SID = 'nodb';
$USERID = 'cse';
$PASSWD = 'cse';
$MAX_MSGID = 1000000;

($DraftDir,$MSGID) = @ARGV;
if (!(-r $DraftDir)) {
	print "Error: UNIX folder directory <$DraftDir> is not readable by the server !\n";
	exit;
}
elsif (!(-e $DraftDir && -d _)) {
	print "Error: UNIX folder directory <$DraftDir> is not exist !\n";
	exit;
}
else {		
	#open(STDERR,"> /dev/null");
	print "before opened\n";
	$lda = &ora_login($ORACLE_SID,$USERID,$PASSWD) || die $ora_errstr;
	print "opened\n";
	if(!$MSGID) { 
		@subdir = ();
		$count = 0;
		push(@subdir,$DraftDir);
		$subfolder = 1;

		while ( $subfolder != 0) {
			$DraftDir = pop(@subdir);
			$subfolder--;

			$csr = &ora_open($lda,'select max_msgid from folder where path=:1')|| die $ora_errstr;
			&ora_bind($csr,"$DraftDir");			
			($most) = &ora_fetch($csr); 
			warn $ora_errstr if $ora_errno;
			do ora_close($csr) || die "can't close cursor";
			if ($most eq "") {$most = 0;}
			print ("All mails >= $most in $DraftDir will be parsed !\n");
			opendir(FOLDER,$DraftDir) || warn "unable to open $DraftDir\n";
			$n = 0;
			$maxid = $most;
			$F_ID = 0;
			foreach $MSGID (sort by_number readdir(FOLDER)) {
		 		if (($MSGID =~ /[^0-9]/)||($MSGID <= $most)) {next;}
		  		$DraftCur = "$DraftDir/$MSGID";
		  		if (-d $DraftCur && -r _ && ($MSGID =~ /[^.]/)) { 
					push(@subdir,$DraftCur);
					$subfolder++; 
					next;
		  		}
	      	  		if (-f $DraftCur && -r _) {
					&draft_parse($DraftCur);
					$n++;
					if ($MSGID > $maxid) {$maxid = $MSGID}; 					
		  		}
		  		else {
					print ("Error: mail file <$DraftCur> cannot be open !\n");
		  		}
			}	
			closedir(FOLDER);
			if ($n > 0) {
				$count += $n;
				&ora_do($lda,"update folder set max_msgid='$maxid' where path='$DraftDir'")|| die $ora_errstr;
				&ora_commit($lda);
				print "MAXID = $maxid is updated for $DraftDir in the DB\n";
			}			
		}
	}
	else {
		if ($MSGID =~ /[^0-9]/) 
			{die ("Error: Wrong File Id <$MSGID>\n");}
		$DraftCur = "$DraftDir/$MSGID";
		if (-e $DraftCur && -r _) {

			$csr = &ora_open($lda,'select max_msgid from folder where path=:1')|| die $ora_errstr;
			&ora_bind($csr,"$DraftDir");			
			($most) = &ora_fetch($csr); 
			warn $ora_errstr if $ora_errno;
			do ora_close($csr) || die "can't close cursor";
			if ($most eq "") {$most = 0;}

			#if ($MSGID > $most) {
				&draft_parse($DraftCur);
				$count++;
			#	$csr = &ora_open($lda,'update folder set max_msgid=:1 where path=:2')|| die $ora_errstr;
			#	&ora_bind($csr,"$MSGID","$DraftDir");			
			#	do ora_close($csr) || die "can't close cursor";	
			#	&ora_commit($lda);
			#	print "MAXID = $MSGID is updated for $DraftDir in the DB\n";
			#}
		}
		else {print ("Error: File <$DraftCur> cannot be open !\n");}	
	}
	do ora_logoff($lda) || die "can't log off Oracle";
}
print "\n**** $USERID: Total $count mails parsed.****\n";
exit 0;

sub by_number {
	if ($a < $b) {
		-1;
	} elsif ($a == $b) {
		0;
	} elsif ($a > $b) {
		 1;
	}
}

#--------------------------------------------------------------------------
# Usage:	&draft_parse();
# Action:	Parse current draft and construct header attributes
#--------------------------------------------------------------------------
sub draft_parse	{
local($draft) = @_;
local($i,$key,$value,$out,$state,$len,$fid);
local($p) = ('draft_parse');
local($timezone,$junk);

%key_value = ("Fid","","Received","","Message-Id","","To","",
	      "Cc","","Subject","","Date","","From","",
	      "In-Reply-To","","Content-Type","TEXT/PLAIN",
	      "Content-Length","","Folder","","Size","","Return-Path","",
	      "Message-ID","","Message-id","");

$fid = $draft;
$fid =~ s/$DraftDir\///;
$key_value{"Fid"} = $fid;
$key_value{"Folder"} = $DraftDir;
open(FILE,$draft) || die "Error in $p: Open $draft";
$key_value{"Size"} = -s $draft;
$state = 'head';
@HeaderKeyVec = @HeaderValueVec = @ValueHeightVec = @KeyLabelVec = (); 
@BodyVec = @Body_Str = ();
$KeyMaxLength = '';
$i = $j = 0;
$BodyHeight = 0;
local($chars,$words,$lines);
$chars = $words = $lines = 0;
$tt = 0;

while(<FILE>)	{
	chop;
	#print $_;
	if($state eq 'head')	{
		if(/^([^\s:]+):\s*(.*)\s*$/)	{
			$key = $1; $value = $2;
			$len = length($key);
			if($len > $KeyMaxLength) { $KeyMaxLength = $len; }
	                $key_value{$key} = $value;
			push(@HeaderKeyVec,$key);
			push(@HeaderValueVec,$value);
			push(@ValueHeightVec,$height=1);
						}
		elsif(/^\s*-*\s*$/) { $state = 'body'; }
		else {
			if(!$key) { 
				if (/^From/) {next;}; # handle mbox format
				print "Error in $p: HeaderFmt $_"; }
			else { 
				$value = pop(@HeaderValueVec);
				push(@HeaderValueVec,"$value$_");
				$key_value{$key} = "$value$_";
				$height = pop(@ValueHeightVec);
				push(@ValueHeightVec,$height+1);
			}			
		}
		$i++;
		next;
	}
	if($state eq 'body') {
		$line_chars = tr///c;
		if (($j < 10) &&  (($tt + $line_chars) >= 1600)) {
			$Body_Str[$j] = join("\n",@BodyVec);
			$Body_Str[$j] =~ s/'/''/g;
			#$lines += $BodyHeight;
			@BodyVec = ("\n");
			push(@BodyVec,$_);
			#$chars += $tt;
			$_ = "$Body_Str[$j]";
			#$words += tr/ //;
			#$BodyHeight = 1;
			$tt = $line_chars;
			$j++;
		}
		else {
			push(@BodyVec,$_);
			#$BodyHeight++;
			$tt += $line_chars;
     		}
	}
}
close(FILE);
	
$_ = "@BodyVec";
$chars += $tt;
#$words += tr/ //;
$Body_Str[$j] = join("\n",@BodyVec); 
$Body_Str[$j] =~ s/'/''/g;
#$lines += $BodyHeight;

$total_body_field = $j;
if ($key_value{"Message-Id"} eq "") {
	$key_value{"Message-Id"} = $key_value{"Message-ID"};
}
if ($key_value{"Message-Id"} eq "") {
	$key_value{"Message-Id"} = $key_value{"Message-id"};
}		

$junk = $key_value{"Date"};
if ($junk =~ /,/) {	# in case there is no weekday char
	($junk,$key_value{"Date"}) = split(/, /,$key_value{"Date"});
}
($key_value{"Date"},$junk) = split(/-/,$key_value{"Date"});
($key_value{"Date"},$junk) = split(/[+]/,$key_value{"Date"});

$timezone = $key_value{"Date"};
$key_value{"Date"} =~ s/:(.*):(.*)/:\1/;  # get rid of seconds
if ($key_value{"Date"} =~ /:(.*):(.*)/) {
	$key_value{"Date"} = $1;
	$timezone = $2;
}
$timezone =~ s/\s*.*([A-Z][A-Z][A-Z]).*/\1/;

$key_value{"Date"} =~ s/:([0-9]*)\s+(.*)$/:\1/; # get rid of time Zone

if (!$timezone || !($timezone =~ /AST|ADT|BST|BDT|CST|CDT|EST|EDT|HST|HDT|MST|MDT|NST|PST|PDT|YST|YDT|GMT/)) 
	{$timezone = "EST";}

$key_value{"Date"} =~ s/^\s*(\d+)\s+([a-zA-Z]+)\s+(\d+)\s+(\d+:\d+)/$1 $2 $3 $4/;
if ($key_value{"Date"} =~ /^(\d+)\s+([a-zA-Z]+)\s+(\d+)\s+(\d+:\d+)/) {
	if ($3 =~ /^\d\d$/) { $key_value{"Date"} = "$1 $2 19$3 $4";}
	else { $key_value{"Date"} = "$1 $2 $3 $4";}
}
else {
	print "Date Format Error in $DraftCur ...\n";
	return 1;
}

$key_value{"In-Reply-To"} =~ s/\n//g;
$key_value{"In-Reply-To"} =~ s/^(\s|.*)(<.*>)(\s|.)*$/\2/; # only need meg id
$key_value{"Message-Id"} =~ s/(\s|.*)(<.*>)(\s|.)*$/\2/; # only need meg id

#local($first,$second);
#while (($first,$second) = each(%key_value)) {
#	$key_value{$first} =~ s/'/''/g; # require by plsql for single quote
#        if (length($key_value{$first}) >= 2000) { 
#		$key_value{$first} = substr($key_value{$first},0,2000);
#		$key_value{$first} =~ s/,(\w+)$//;
#	}
#}
$key_value{"Subject"} =~ s/'/''/g; # require by plsql for single quote
$key_value{"From"} =~ s/'/''/g; # require by plsql for single quote
$key_value{"Message-Id"} =~ s/'/''/g; # require by plsql for single quote
$key_value{"In-Reply-To"} =~ s/'/''/g; # require by plsql for single quote

local($sname,$userid,$email,$a);
$a = $key_value{"From"};
if ($a =~ /^\s*(.*)<(.*)>\s*$/) {
	$sname = $1;
	$email = $2;
}
elsif ($a =~ /^\s*(.*)\s\((.*)\)\s*$/) {
	$sname = $2;
	$email = $1;
}
elsif ($a =~ /^\s*(.*)\((.*)\)\s*$/) {
	$sname = $2;
	$email = $1;
}
else {	
	$sname = "";
	$email = $a;
}
$sname =~ s/\s*$//;
$sname =~ s/"//g;
$email =~ s/\s*$//;
$email =~ s/^\s*//;
$email =~ s/<//g;
$email =~ s/>//g;
($userid,$a) = split(/@/,$email);
if ($userid eq "") {
	if ($email eq "") {print "Bad msg header $key_value{\"Fid\"}\n"; return;}
	$userid = $email;
}

if ($key_value{"Cc"} eq "") {
	$key_value{"Cc"} = $key_value{"Followup-To"};
}
if ($key_value{"To"} eq "") {
	$key_value{"To"} = $key_value{"Newsgroups"};
}
$key_value{"To"} =~ s/'/''/g; # require by plsql for single quote
if (length($key_value{"To"}) >= 2000) { 
		$key_value{"To"} = substr($key_value{"To"},0,2000);
		$key_value{"To"} =~ s/,(\w+)$//;
}
$key_value{"Cc"} =~ s/'/''/g; # require by plsql for single quote
if (length($key_value{"Cc"}) >= 2000) { 
		$key_value{"Cc"} = substr($key_value{"Cc"},0,2000);
		$key_value{"Cc"} =~ s/,(\w+)$//;
}

$key_value{"Content-Type"} = ""; # ignore this field for the time-being

#&ora_do($lda,<<END_OF_PLSQL1)|| return 1;
#&ora_do($lda,<<END_OF_PLSQL1)|| die $ora_errstr;
$err = &ora_do($lda,<<END_OF_PLSQL1);
begin
MAILS0.insert_header(i_id=>'$key_value{"Fid"}',
	    i_msg_date=>'$key_value{"Date"}',i_subject=>'$key_value{"Subject"}',
            i_sender=>'$email',i_receiver=>'$key_value{"To"}',
	    i_msg_id=>'$key_value{"Message-Id"}',i_bytes=>'$key_value{"Size"}',
	    i_body_type=>'$key_value{"Content-Type"}',i_folder=>'$key_value{"Folder"}',
	    i_replied_id=>'$key_value{"In-Reply-To"}',
	    i_timezone=>'$timezone',i_cc=>'$key_value{"Cc"}',
	    uid=>'$userid',sname=>'$sname',email=>'$email');
commit;
end;
END_OF_PLSQL1

if (!$err) {
	print "error while inserting $DraftCur header\n";
	next;
}

#&ora_do($lda,<<END_OF_PLSQL2)|| die $ora_errstr;
$err = &ora_do($lda,<<END_OF_PLSQL2);
begin
MAILS0.insert_body(i_id=>'$key_value{"Fid"}',i_body_type=>'$key_value{"Content-Type"}',
		i_chars=>$chars,
		i_words=>$words,i_lines=>$lines,
		i_folder=>'$key_value{"Folder"}');
commit;
end;
END_OF_PLSQL2

if (!$err) {
	print "error while inserting $DraftCur body\n";
	return 0;
	next;
}

	
print "$DraftCur done\n";

local($i_id,$i_folder);
$i_id = $key_value{"Fid"};
$i_folder = $key_value{"Folder"};

if ($F_ID == 0) {
	$csr = &ora_open($lda,"select id from folder where path='$i_folder'")|| die $ora_errstr;
	($F_ID) = &ora_fetch($csr);	
	do ora_close($csr) || die "can't close cursor";
	$F_ID = ($F_ID - 1)*$MAX_MSGID;
}
$id = $F_ID + $i_id;
&ora_do($lda,"update body set msg_body1='$Body_Str[0]',msg_body2='$Body_Str[1]',
	msg_body3='$Body_Str[2]',msg_body4='$Body_Str[3]',msg_body5='$Body_Str[4]',
	msg_body6='$Body_Str[5]',msg_body7='$Body_Str[6]',msg_body8='$Body_Str[7]',
	msg_body9='$Body_Str[8]',msg_body10='$Body_Str[9]' where id = $id");

#$i = 0;
#while ($i < 10 && $Body_Str[$i]) {
#	$j = $i + 1;
#	&ora_do($lda,"update body set msg_body$j = '$Body_Str[$i]' where id = $id");
#	$i++;
#}
&ora_commit($lda);
if ($total_body_field = 10) {
	$Body_Str[10] = substr($Body_Str[10],1,32600);  # 32k limit in PL/SQL long variable
	$csr = &ora_open($lda,"update body set msg_body11=:1 where id = $id")|| die $ora_errstr;
	&ora_bind($csr,"$Body_Str[10]");
	&ora_commit($lda);	
	do ora_close($csr) || die "can't close cursor";		
}
&ora_do($lda,"update folder set max_msgid='$fid' where path='$DraftDir'")|| die $ora_errstr;
&ora_commit($lda);
## keep them for a while until backup server is installed
#system("/bin/rm -f $DraftCur"); # remove mail in MH folder to save space
return 1;
}
