#!/usr/bin/perl
# 
# ***** BEGIN LICENSE BLOCK *****
# Version: MPL 1.1
# 
# The contents of this file are subject to the Mozilla Public License
# Version 1.1 ("License"); you may not use this file except in
# compliance with the License. You may obtain a copy of the License at
# http://www.zimbra.com/license
# 
# Software distributed under the License is distributed on an "AS IS"
# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
# the License for the specific language governing rights and limitations
# under the License.
# 
# The Original Code is: Zimbra Collaboration Suite Server.
# 
# The Initial Developer of the Original Code is Zimbra, Inc.
# Portions created by Zimbra are Copyright (C) 2006 Zimbra, Inc.
# All Rights Reserved.
# 
# Contributor(s):
# 
# ***** END LICENSE BLOCK *****
# 

use strict;
no strict "refs";
use lib "/opt/zimbra/zimbramon/lib";
use Zimbra::Util::Common;
use Zimbra::Mon::Logger;

use DBI;
use Time::Local;
use Mail::Mailer;
use FileHandle;
use IPC::Open3;
use Getopt::Std;

my %options = ();
my @msgIds;
my %senders = ();
my %recipients = ();
my %errors = ();
my $report = ();

unless ( getopts( 'mndu:t:h', \%options ) ) { usage(); }
if ($options{h}) {usage();}
my $user = $options{u};
my $debug = $options{d} ? 1 : 0;

if ($options{m} && !$options{d}) {
  #close(STDOUT);
  #close(STDERR);
}

my $dbh = connectDb();

my $startTime;
my $endTime;

if ($options{t}) {
	($startTime, $endTime) = split (',',$options{t});
	$startTime = timeToSqlTime($startTime);
	if ($endTime) {
		$endTime = timeToSqlTime($endTime);
	}
} elsif ($options{n}) {
  $startTime = tsToSqlTime(time(),1);
  $endTime = tsToSqlTime(time(),0);
} else {
	# default to the previous day
  $startTime = tsToSqlTime((time() - (60*60*24)),1);
  $endTime = tsToSqlTime(time(),1);
}


addToReport("Generating report\n");
if ($user) {
	addToReport("\tfor $user\n");
}
addToReport("\tFrom $startTime to $endTime\n\n");


# We may have a msgId from the middle of the path - get the initial ID
my $msgs = getMsgIds(\@msgIds);

getSenders(\%senders);
getRecipients(\%recipients);
getErrors(\%errors);

displayStats($msgs, %senders, %recipients, %errors);
if ($options{m}) {
  sendEmailReport($report);
} else {
  print @$report;
}

#------------- 
# Subroutines
#-------------
sub getMsgIds {
	my $ids = shift;
	my $user = $options{u};

	my $whereClause = "";
	my @clauses = ();
	my $statement = "";
	my $ary;

	my %uniqIds = ();
	my @clauses = ();

	my $count = 0;

	%uniqIds = ();

	if ($user ne "") {
		push @clauses, "(sender like '%".$user."%' or recipient like '%".$user."%')";
	}

	if ($startTime) {
		push @clauses, "arrive_time >= '".$startTime."'";
	}

	if ($endTime) {
		push @clauses, "arrive_time <= '".$endTime."'";
	}

	$statement = "select distinct(quote(msgid)) from mta ";

	if ($#clauses >= 0) {
		$statement .= " where";
	}
	for (my $i = 0; $i <= $#clauses; $i++) {
		if ($i) { $statement .= " and"; }
		$statement .= " $clauses[$i]";
	}

	#print "$statement\n\n";

	$ary = execSelect($statement);
	foreach my $row (@$ary) {
		my $id = $$row[0];
		$uniqIds{$id} = $id;
	}

	my @msgs = ();

	foreach my $id (sort keys %uniqIds) {
		print "Tracing msg: $id\n" if $debug;
		$count++;
		push @msgs, traceProcessedMsg($id);
	}
	@msgs = sort {$$a{ARRIVE} cmp $$b{ARRIVE}} @msgs;
	foreach (@msgs) {
		displayMsg ($_) if $debug;
	}

	# print "\n$count messages found\n\n";

	return \@msgs;

}

sub getSenders {
	my $senders = shift;

	my $st = "select s2, count(s2) as C from ".
		"(select distinct(msgid), sender as s2 from mta ".
		"where sender is not null and arrive_time >= '".$startTime."' and arrive_time <= '".$endTime."') ".
		"as foo group by s2 order by C desc";

	#print $st,"\n\n";

	my $ary = execSelect($st);
	map { $$senders{$$_[0]} = $$_[1] } @$ary;

}

sub getRecipients {
	my $recipients = shift;
	my $st = "select recipient, count(distinct(msgid)) as C from mta ".
		"where recipient is not null and arrive_time >= '".$startTime."' and arrive_time <= '".$endTime."' ".
		"group by recipient order by C desc";

	my $ary = execSelect($st);
	map { $$recipients{$$_[0]} = $$_[1] } @$ary;
}

sub getErrors {
	my $errors = shift;
	my $st = "select distinct(msgid), arrive_time, status, statusmsg from mta ".
		"where status != 'sent' ".
		"and arrive_time >= '".$startTime."' and arrive_time <= '".$endTime."' ".
		"order by msgid";

	my $ary = execSelect($st);
	map {  $$errors{$$_[0]}{TIME} = $$_[1]; $$errors{$$_[0]}{STATUS} = $$_[2]; $$errors{$$_[0]}{MSG} = $$_[3] } @$ary;
}

sub displayStats {
	my $msgs = shift;
	my $senders = shift;
	my $recipients = shift;

	my $msgcount = 0;
	my $msgbytes = 0;
	my $numrcpt = 0;
	my $delaytot = 0;
	my %statii = ();
	foreach my $msg (@$msgs) {
		$msgcount++;
		$msgbytes += $$msg{BYTES};
		foreach my $msgId (sort keys %{$msg}) {

			if ($msgId eq "ARRIVE" || $msgId eq "BYTES") {next;}
			# $msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{status}
			foreach my $fromIP ( sort keys %{$$msg{$msgId}} ) {
        print "$fromIP $msgId\n" if $debug;
				$numrcpt += scalar (keys %{$$msg{$msgId}{$fromIP}{recipList}});
				#$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{delay}
				#$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{status} = $$row[5];
				foreach my $recip (keys %{$$msg{$msgId}{$fromIP}{recipList}}) {
					foreach my $lt (keys %{$$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}}){
						if ($$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$lt}{status} ne "sent") {
							$statii{$$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$lt}{status}}++;
						}
						$delaytot += $$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$lt}{delay};
					}
				}
				last;
			}
		}
	}
	if (!$msgcount) {
		addToReport("No messages found\n");
		return;
	}
	my $bytesavg = sprintf ("%02.2f",$msgbytes/$msgcount);
	my $rcptavg = sprintf ("%02.2f",$numrcpt/$msgcount);
	my $delayavg = sprintf ("%02.2f",$delaytot/$msgcount);
  my $uniquercpt = scalar keys %recipients;
	addToReport("\n$msgcount messages found for $numrcpt total recipients ($uniquercpt unique)\n");
	addToReport("\t$msgbytes total bytes\n");
	addToReport("\t$bytesavg average bytes/msg\n");
	addToReport("\t$rcptavg average recipients/msg\n");
	addToReport("\t$delayavg average delay/msg (sec)\n\n");


	addToReport("Errors\n");
	foreach (sort keys %errors) {
		addToReport("$errors{$_}{TIME} $errors{$_}{STATUS} $errors{$_}{MSG}\n");
		#print sprintf ("%10s %48s %s\n", $errors{$_}{STATUS}, $errors{$_}{MSG}, $_);
	}

	addToReport("\n");

	my $numSenders = getLocalConfig("zimbra_mtareport_max_senders");
	if ($numSenders == 0 || $numSenders eq "") { $numSenders = 50; }

	my $i = 0;

	addToReport("Most active senders\n");
	foreach (sort {$senders{$b} <=> $senders{$a}} keys %senders) {
		if ($i++ > $numSenders) { last; }
    my $senderline = sprintf ("%9d %12s\n", $senders{$_}, $_);
    addToReport($senderline);
	}

	addToReport("\n");

	my $numRecipients = getLocalConfig("zimbra_mtareport_max_recipients");
	if ($numRecipients == 0 || $numRecipients eq "") { $numRecipients = 50; }

	$i = 0;

	addToReport("Most active recipients\n");
	foreach (sort {$recipients{$b} <=> $recipients{$a}} keys %recipients) {
		if ($i++ > $numRecipients) { last; }
    my $recipientline = sprintf ("%9d %12s\n", $recipients{$_}, $_);
    addToReport($recipientline);
	}

}

sub displayMsg {
	my $msg = shift;
	my $indent = shift;
	my $only = shift;

	my $sender = $options{s};
	my $recip = $options{r};

	foreach my $msgId (sort keys %{$msg}) {

		if ($msgId eq "ARRIVE") {next;}
		if ($recip ne "" && $only eq "") {
			my $found = 0;
			foreach my $fromIP ( sort keys %{$$msg{$msgId}} ) {
				foreach my $r ( sort keys %{$$msg{$msgId}{$fromIP}{recipList}} ) {
					if ($r !~ /$recip/o) {next;}
					$found = 1; last;
				}
				if ($found) {last;}
			}
			if (!$found) {next;}
		} 

		# We need to back trace to see if $fromIP is 
		# entered as a toIP in $$msg{$msgId}
		my $firstIP;

		foreach ( keys %{$$msg{$msgId}} ) {
			# Just grab the first key and backtrack
			$firstIP = $_;
			last;
		}

		my %beenthere = ();
		my $done = 0;
		TOP: while (!$done) {
			$beenthere{$firstIP} = $firstIP;

			foreach ( keys %{$$msg{$msgId}} ) {
				foreach my $r (keys %{$$msg{$msgId}{$_}{recipList}}) {
					if ($$msg{$msgId}{$_}{recipList}{$r}{nextIp} eq $firstIP) {
						$firstIP = $_;
						if (defined ($beenthere{$firstIP})) {
							$done=1;
							next TOP;
						}
						$done = 0;
						next TOP;
					}
				}
			}
			$done = 1;

		}

		$done = 0;

		if ($sender ne "") {
			if ($$msg{$msgId}{$firstIP}{sender} !~ /$sender/) {next;}
		}

    next if ($msgId eq "BYTES");
		addToReport("\nMessage ID $msgId\n");
		addToReport("$$msg{$msgId}{$firstIP}{sender} -->");

		foreach my $r ( sort keys %{$$msg{$msgId}{$firstIP}{recipList}} ) {
			addToReport("$r");
			my $origrcpt =  $$msg{$msgId}{$firstIP}{recipList}{$r}{origRecip}?
				" (originally to $$msg{$msgId}{$firstIP}{recipList}{$r}{origRecip})\n":"\n";
      addToReport($origrcpt);
		}

		foreach my $r ( sort keys %{$$msg{$msgId}{$firstIP}{recipList}} ) {
			if (defined ($recip) && $r !~ /$recip/o) {next;}
			if (defined ($only) && $r ne $only) {next;}

			my $curIP = $firstIP;

			$indent = "  ";


			my %beenthere = ();

			while (defined($$msg{$msgId}{ $curIP })) {
				$beenthere{$curIP} = $curIP;
				printMessage($msg, $msgId, $r, $curIP, $indent);
				if (defined( $$msg{$msgId}{$curIP}{recipList}{$r}{amavisPid} )) {

					#$indent = $indent."  ";

					my $line = $indent, $$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{arriveTime},
						" ", $$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{disposition},
						" by amavisd on ",$$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{host},
						" (", $$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{status};
					$line .= $$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{reason}?
						" - ".$$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{reason}:"";
					$line .= ") HITS: ",
						$$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{hits}," in ",
						$$msg{$msgId}{$curIP}{recipList}{$r}{amavis}{time}," ms\n";
          addToReport($line);
				}
				$indent = $indent."  ";
				$curIP = $$msg{$msgId}{$curIP}{recipList}{$r}{nextIp};

				if (defined ($beenthere{$curIP})) {
					last;
				}

			}

		}

	}
}

sub printMessage {
	my $msg = shift;
	my $msgId = shift;
	my $r = shift;
	my $curIP = shift;
	my $indent = shift;

	if ($indent eq "  ") { addToReport("$indent Recipient $r\n"); }
	  addToReport("$indent $$msg{$msgId}{$curIP}{recipList}{$r}{arriveTime} - $$msg{$msgId}{$curIP}{prevHost} ($$msg{$msgId}{$curIP}{prevIp}) --> $$msg{$msgId}{$curIP}{host}\n");
	foreach my $lt (sort keys %{$$msg{$msgId}{$curIP}{recipList}{$r}{leaveTime}}) {

		addToReport("$indent $lt - $$msg{$msgId}{$curIP}{host} --> $$msg{$msgId}{$curIP}{recipList}{$r}{nextHost} ($$msg{$msgId}{$curIP}{recipList}{$r}{nextIp})");
		addToReport(" status $$msg{$msgId}{$curIP}{recipList}{$r}{leaveTime}{$lt}{status}");
		if ($$msg{$msgId}{$curIP}{recipList}{$r}{leaveTime}{$lt}{status} ne "sent") {
			addToReport("$indent\n$indent  $$msg{$msgId}{$curIP}{recipList}{$r}{leaveTime}{$lt}{statusmsg}");
		}

		addToReport("\n");
	}
}

sub traceProcessedMsg {
	my $msgId = shift;

	print "Tracing processed msg $msgId\n" if $debug;

	my %msg = ();

	my $statement = 
		"select distinct(quote(from_IP)) from mta where msgid=$msgId";
	my $fary = execSelect($statement);


	foreach my $f (@$fary) {

		my $fromIP = $$f[0];

		my $statement = 
			"select distinct(quote(recipient)) from mta where msgid=$msgId";

		if ($fromIP ne "") {
			$statement .=" and from_IP=$fromIP";
		} else {
			$statement .=" and from_IP is null";
		}

		my $rary = execSelect($statement);

		foreach my $r (@$rary) {
			my $recip = $$r[0];

			my $statement =
				"select arrive_time, leave_time, host, ".
				"sender, recipient, status, statusmsg, from_host, from_IP, to_host, to_IP, ".
				"bytes, amavis_pid ".
				"from mta where msgid=$msgId and recipient=$recip";

			if ($fromIP ne "") {
				$statement .= " and from_IP=$fromIP";
			} else {
				$statement .= " and from_IP is null";
			}

			my $ary = execSelect($statement);

			foreach my $row (@$ary) {
				$msg{ARRIVE} = $$row[0];
				$msg{BYTES} = $$row[11];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{arriveTime} = $$row[0];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]} = ();
				$msg{$msgId}{$fromIP}{host} = $$row[2];
				$msg{$msgId}{$fromIP}{sender} = $$row[3]?$$row[3]:"postmaster";

				$msg{$msgId}{$fromIP}{recipList}{$recip}{recip} = $recip;

				$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{status} = $$row[5];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{statusmsg} = $$row[6];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{leaveTime}{$$row[1]}{delay} = sqlTimeToTs($$row[1])-sqlTimeToTs($$row[0]);
				$msg{$msgId}{$fromIP}{prevHost} = $$row[7];
				$msg{$msgId}{$fromIP}{prevIp} = $$row[8];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{nextHost} = $$row[9];
				$msg{$msgId}{$fromIP}{recipList}{$recip}{nextIp} = $$row[10];
				if (defined ($$row[11])) {
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavisPid} = $$row[12];
				}
			}
			if (defined ($msg{$msgId}{$fromIP}{recipList}{$recip}{amavisPid})) {

				$statement =
					"select arrive_time, msgid, disposition, status, reason, hits, time, host ".
					"from amavis where msgid=$msgId ".
					"and pid='".$msg{$msgId}{$fromIP}{recipList}{$recip}{amavisPid}."'";

				$ary = execSelect($statement);

				foreach my $row (@$ary) {
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{arriveTime} = $$row[0];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{msgid} = $$row[1];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{disposition} = $$row[2];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{status} = $$row[3];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{reason} = $$row[4];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{hits} = $$row[5];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{time} = $$row[6];
					$msg{$msgId}{$fromIP}{recipList}{$recip}{amavis}{host} = $$row[7];
				}
			}
		}

	}

	return (\%msg);
}

sub execSelect {
	my $statement = shift;

	#print "Searching database: \n";
	#print "\t $statement\n\n";

	my $sth = $dbh->prepare($statement);

	if (!$sth->execute() ) {
    logError("\'$statement\'\n");
		logError("$0 sql execute failed: ",$sth->errstr,"\n");
	}

	my $aryref = $sth->fetchall_arrayref;

	return $aryref;
}

sub usage {
	print STDERR "Usage: $0 [ -u user ] [ -t start,end ]\n";
	print STDERR "\ttimes in YYYYMMDD[hhmmss] format\n\n";

	exit (1);
}

sub connectDb {
	my $data_source="dbi:mysql:database=zimbra_logger;mysql_read_default_file=/opt/zimbra/conf/my.logger.cnf;mysql_socket=/opt/zimbra/logger/db/mysql.sock";

	my $username="zimbra";
	my $password = getLocalConfig("zimbra_logger_mysql_password");
	chomp $password;

	my $dbh = DBI->connect($data_source, $username, $password);

	if (!$dbh) { 
		logError("DB: Can't connect to $data_source: $DBI::errstr\n");
		exit 1;
	}

	return $dbh;
}

sub tsToSqlTime {
	my $ts = shift;
	my $trunc = shift;

	my @T = localtime($ts);
	return (timeToSqlTime(sprintf ("%04d%02d%02d%02d%02d%02d", $T[5]+1900,$T[4]+1,$T[3],$T[2],$T[1],$T[0]), 
		$trunc));
}

sub timeToSqlTime {
	my $ts = shift;
	my $trunc = shift;
	if ($ts eq "" || !defined($ts)) {
		return '1000-01-01 00:00:00';
	}

	# IN: YYYYMMDDHHMMSS

	# OUT: YYYY-MM-DD HH:MM:SS
	my $dt;
	if ($trunc) {
		$dt = sprintf ("%4d-%02d-%02d 00:00:00",
			substr($ts,0,4), substr($ts,4,2), substr($ts,6,2));
	} else {
		$dt = sprintf ("%4d-%02d-%02d %02d:%02d:%02d",
			substr($ts,0,4), substr($ts,4,2), substr($ts,6,2),
			substr($ts,8,2)?substr($ts,8,2):0, 
			substr($ts,10,2)?substr($ts,10,2):0, 
			substr($ts,12,2)?substr($ts,12,2):0);
	}

	return $dt;
}

sub sqlTimeToTs {
	my $sqlTime = shift;
	# 2005-09-18 04:03:33
	if ($sqlTime ne "") {
		return timelocal(substr($sqlTime,17,2),substr($sqlTime,14,2),
		substr($sqlTime,11,2),substr($sqlTime,8,2),
		(substr($sqlTime,5,2)-1),substr($sqlTime,0,4));
	}
	return 0;
}

sub getLocalConfig {
	my $key = shift;
	if (defined ($ENV{zmsetvars})) {
		return $ENV{$key};
	}
	open CONF, 
		"/opt/zimbra/bin/zmlocalconfig -q -m shell |" or die "Can't open local config: $!";
	my @conf = <CONF>;
	close CONF;

	chomp @conf;

	foreach (@conf) {
		my ($key, $val) = split '=', $_, 2;
		$val =~ s/;$//;
		$val =~ s/'$//;
		$val =~ s/^'//;
		$ENV{$key} = $val;
	}
	$ENV{zmsetvars} = 'true';
	return $ENV{$key};
}

sub getLdapConfigValue {
  my $attrib = shift;
  my ($val,$err);
  my ($rfh,$wfh,$efh,$cmd,$rc);
  $rfh = new FileHandle;
  $wfh = new FileHandle;
  $efh = new FileHandle;
  $cmd = "/opt/zimbra/bin/zmprov -l gcf $attrib";
  my $pid = open3($wfh,$rfh,$efh, $cmd);
  unless(defined($pid)) {
    return undef;
  }
  close $wfh;
  chomp($val = (split(/\s+/, <$rfh>))[-1]);
  chomp($err = join "", <$efh>);
  waitpid($pid,0);
  if ($? == -1) {
    # failed to execute
    return undef;
  } elsif ($? & 127) {
    # died with signal 
    return undef;
  } else {
    $rc = $? >> 8;
    return undef if ($rc != 0);
  }

  return $val;
}

sub sendEmailReport {
  my $msg = shift;

  my $subject =  "Daily mail report from $startTime to $endTime";
  my $from_address = getLocalConfig("smtp_source");
  my $to_address = getLocalConfig("smtp_destination");
  my $smtphost = getLdapConfigValue("zimbraSmtpHostname");

  print "Sending daily report to $to_address via $smtphost\n" if $debug;
  eval { 
    my $mailer = Mail::Mailer->new("smtp", Server => $smtphost);
    $mailer->open( { From => $from_address,
                   To   => $to_address, 
                   Subject => $subject,
                })
    or warn "ERROR: Can't open: $!\n";
    print $mailer $msg;
    $mailer->close();
  };
  if ($@) {
    logError("Failed to email report: $@\n");
  } else {
    print "Email report sent to $to_address\n" if $debug;
  } 
}

sub addToReport {
  my ($line) = @_;
  push(@$report, $line);
}

sub logError {
  my $msg = shift;
  if ($options{m} && !$options{d}) {
    my $dt = `date "+%Y-%m-%d %H:%M:%S"`;
    chomp($dt);
    Zimbra::Mon::Logger::Log("err", "$dt, zmdailyreport: $msg");
  } else {
    print STDERR $msg;
  }
  return;
}


