#!/usr/bin/perl
#
# Copyright (c) 2010-2012, The Trusted Domain Project.  All rights reserved.
#
#
# Script to generate some graphs showing DKIM stats for some domains

###
### Setup
###

use strict;
use warnings;

use DBI;
use File::Basename;
use Getopt::Long;
use IO::Handle;
use POSIX;

require DBD::mysql;

# general
my $progname      = basename($0);
my $version       = "\2.11.0";
my $verbose       = 0;
my $helponly      = 0;
my $showversion   = 0;

my $out;
my $reportdir;
my $datafile;
my $pngfile;

my $anchor;
my $domain;
my $tmpdom;

my $dbi_s;
my $dbi_h;
my $dbi_a;

my $unsigned      = 1;
my $circles       = 1;
my $circlestr;

my $idx;
my $tmpin;

my @domains;
my @xdata;
my @ydata;
my @zdata;
my %yhash;

my $plot;

# DB parameters
my $def_dbhost    = "localhost";
my $def_dbname    = "opendkim";
my $def_dbuser    = "opendkim";
my $def_dbpasswd  = "opendkim";
my $def_dbport    = "3306";
my $dbhost;
my $dbname;
my $dbuser;
my $dbpasswd;
my $dbport;

my $dbscheme     = "mysql";

# output location (default)
my $reportout;
my $tmpout;

###
### NO user-serviceable parts beyond this point
###

sub make_temporary_table
{
	$dbi_s = $dbi_h->prepare("CREATE TEMPORARY TABLE t1 (
		id INT UNSIGNED NOT NULL AUTO_INCREMENT,
		ddate DATE,
		msgcount INT UNSIGNED NOT NULL,
		spamcount INT UNSIGNED NOT NULL,
		domain VARCHAR(255) NOT NULL,
		PRIMARY KEY(id),
		KEY(domain)
	)");

	if (!$dbi_s->execute)
	{
		print STDERR "$progname: CREATE TEMPORARY TABLE failed: " . $dbi_h->errstr;
		$dbi_s->finish;
		$dbi_h->disconnect;
		exit(1);
	}

	$dbi_s->finish;
}

sub usage
{
	print STDERR "$progname: usage: $progname [options]\n";
	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
	print STDERR "\t--domain=name      domain(s) on which to report\n";
	print STDERR "\t--help             print help and exit\n";
	print STDERR "\t--nocircles        don't tell gnuplot to use circles\n";
	print STDERR "\t--output=file      output file\n";
	print STDERR "\t--unsigned         report about unsigned mail\n";
	print STDERR "\t--verbose          verbose output\n";
	print STDERR "\t--version          print version and exit\n";
}

# parse command line arguments
my $opt_retval = &Getopt::Long::GetOptions ('circles!' => \$circles,
                                            'dbhost=s' => \$dbhost,
                                            'dbname=s' => \$dbname,
                                            'dbpasswd=s' => \$dbpasswd,
                                            'dbport=s' => \$dbport,
                                            'dbuser=s' => \$dbuser,
                                            'domain=s@' => \@domains,
                                            'help!' => \$helponly,
                                            'output=s' => \$reportout,
                                            'unsigned!' => \$unsigned,
                                            'verbose!' => \$verbose,
                                            'version!' => \$showversion,
                                           );

if (!$opt_retval || $helponly)
{
	usage();

	if ($helponly)
	{
		exit(0);
	}
	else
	{
		exit(1);
	}
}

if ($showversion)
{
	print STDOUT "$progname v$version\n";
	exit(0);
}

# apply defaults
if ($circles)
{
	$circlestr = "with circles";
}
else
{
	$circlestr = "";
}

if (!defined($dbhost))
{
	if (defined($ENV{'OPENDKIM_DBHOST'}))
	{
		$dbhost = $ENV{'OPENDKIM_DBHOST'};
	}
	else
	{
		$dbhost = $def_dbhost;
	}
}

if (!defined($dbname))
{
	if (defined($ENV{'OPENDKIM_DB'}))
	{
		$dbname = $ENV{'OPENDKIM_DB'};
	}
	else
	{
		$dbname = $def_dbname;
	}
}

if (!defined($dbpasswd))
{
	if (defined($ENV{'OPENDKIM_PASSWORD'}))
	{
		$dbpasswd = $ENV{'OPENDKIM_PASSWORD'};
	}
	else
	{
		$dbpasswd = $def_dbpasswd;
	}
}

if (!defined($dbport))
{
	if (defined($ENV{'OPENDKIM_PORT'}))
	{
		$dbport = $ENV{'OPENDKIM_PORT'};
	}
	else
	{
		$dbport = $def_dbport;
	}
}

if (!defined($dbuser))
{
	if (defined($ENV{'OPENDKIM_USER'}))
	{
		$dbuser = $ENV{'OPENDKIM_USER'};
	}
	else
	{
		$dbuser = $def_dbuser;
	}
}

if (!defined($reportout))
{
	if (defined($ENV{'OPENDKIM_OUTPUT'}))
	{
		$reportout = $ENV{'OPENDKIM_OUTPUT'};
	}
}

if ($verbose)
{
	print STDERR "$progname: started at " . localtime() . "\n";
}

my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
              ";host=" . $dbhost . ";port=" . $dbport;

$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
if (!defined($dbi_h))
{
	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
	exit(1);
}

if ($verbose)
{
	print STDERR "$progname: connected to database\n";
}

#
# If any of the domains started with "/" or "./", convert them into
# whatever's in the files they reference.
#

if (scalar @domains > 0)
{
	foreach $idx (0 .. $#domains)
	{
		if (substr($domains[$idx], 0, 1) eq "/" ||
		    substr($domains[$idx], 0, 2) eq "./")
		{
			if (!open($tmpin, "<", $domains[$idx]))
			{
				print STDERR "$progname: cannot open $domains[$idx]: $!\n";
				$dbi_s->finish;
				$dbi_h->disconnect;
				exit(1);
			}

			while (<$tmpin>)
			{
				# ignore comments
				s/#.*//;

				# ignore blank lines
				next if /^(\s)*$/;

				chomp;

				push @domains, $_;
			}

			close($tmpin);

			delete $domains[$idx];
		}
	}
}

#
# start the report
#
if (!defined($reportout))
{
	print STDERR "$progname: no output selected\n";
	exit(1);
}

$reportdir = dirname($reportout);

$tmpout = $reportout . "." . $$;
open($out, ">", $tmpout) or die "$progname: can't open $tmpout: $!";
if ($verbose)
{
	print STDERR "$progname: report started in $tmpout\n";
}

print $out "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">\n";
print $out "<html>\n";
print $out "  <head>\n";
print $out "    <meta http-equiv=\"content-type\" content=\"text/html;charset=us-ascii\">\n";
print $out "    <title>\n";
print $out "      OpenDKIM Data Per Domain\n";
print $out "    </title>\n";
print $out "  </head>\n";
print $out "\n";
print $out "  <body>\n";

print $out "<h1>OpenDKIM Data Per Domain</h1>\n";
print $out "Generated " . strftime("%b %e %Y %H:%M:%S", localtime) . "\n";
print $out "<hr>\n";
print $out "<ul>\n";
if (scalar @domains > 0)
{
	foreach $domain (@domains)
	{
		if (!defined($domain))
		{
			next;
		}

		$tmpdom = $domain;
		$tmpdom =~ s/\./_/;
		print $out " <li> <a href=\"#" . $tmpdom . "\">" . $domain . "</a>\n";
	}
}
if ($unsigned)
{
	print $out " <li> <a href=\"#unsigned\">Unsigned Mail</a>\n";
}
print $out "</ul>\n";
print $out "<hr>\n";

#
# build a temporary table
#
if ($verbose)
{
	print STDERR "$progname: constructing temporary table\n";
}

make_temporary_table();

if ($verbose)
{
	print STDERR "$progname: populating temporary table\n";
}

$dbi_s = $dbi_h->prepare("INSERT INTO t1 ( 
	ddate,
	msgcount,
	spamcount,
	domain)
	SELECT
	DATE(messages.msgtime),
	COUNT(messages.id),
	SUM(messages.spam),
	domains.name
	FROM messages
	JOIN reporters ON reporters.id = messages.reporter
	JOIN signatures ON messages.id = signatures.message
	JOIN domains ON domains.id = signatures.domain
	WHERE reporters.enabled = 1
	AND   signatures.pass = 1
	AND   NOT messages.spam = -1
	GROUP BY signatures.domain, DATE(messages.msgtime)");
if (!$dbi_s->execute)
{
	print STDERR "$progname: INSERT INTO TEMPORARY TABLE failed: " . $dbi_h->errstr . "\n";
	$dbi_s->finish;
	$dbi_h->disconnect;
	exit(1);
}
$dbi_s->finish;

$dbi_s = $dbi_h->prepare("SELECT msgcount, spamcount, 1+LOG10(COUNT(id)) FROM t1 WHERE domain = ? GROUP BY 1, 2");

$datafile = $reportdir . "/ogg.data." . $$;
$pngfile = $reportdir . "/ogg.png." . $$;

if (scalar @domains > 0)
{
	foreach $domain (@domains)
	{
		if (!defined($domain))
		{
			next;
		}

		if ($verbose)
		{
			print STDERR "$progname: reporting for $domain\n";
		}

		$tmpdom = $domain;
		$tmpdom =~ s/\./_/;
		print $out "<a name=\"" . $tmpdom . "\">" . $domain . ":</a>\n";
		print $out "<br>\n";

		if (!$dbi_s->execute($domain))
		{
			print STDERR "$progname: domain report for " . $domain . " failed: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			$dbi_h->disconnect;
			exit(1);
		}

		open(GNUDATA, ">" . $datafile)
			or die $progname . ": can't create temporary file $datafile\n";
		while ($dbi_a = $dbi_s->fetchrow_arrayref())
		{
			print GNUDATA $dbi_a->[0] . " " . $dbi_a->[1] . " " . $dbi_a->[2] . "\n";
		}
		$dbi_s->finish;
		close GNUDATA;

		open(GNUPLOT, "|gnuplot > /dev/null 2>&1") or die $progname . ": can't execute gnuplot\n";
		print GNUPLOT "set xrange [0:*]\n";
		print GNUPLOT "set yrange [0:*]\n";
		print GNUPLOT "unset log\n";
		print GNUPLOT "unset label\n";
		print GNUPLOT "set xtic auto\n";
		print GNUPLOT "set ytic auto\n";
		print GNUPLOT "set xlabel 'received'\n";
		print GNUPLOT "set ylabel 'spam'\n";
		print GNUPLOT "set terminal png size 800,600\n";
		print GNUPLOT "set title '" . $domain . " received-spam scatter plot'\n";
		print GNUPLOT "set output '$pngfile'\n";
		print GNUPLOT "plot '$datafile' using 1:2:3 notitle $circlestr\n";
		close(GNUPLOT);

		rename($pngfile, $reportdir . "/" . $domain . ".png");
		unlink($datafile);

		print $out "<img src=\"" . $domain . ".png\">\n";
		print $out "<br>\n";
	}
}

if ($unsigned)
{
	$dbi_s = $dbi_h->prepare("DROP TEMPORARY TABLE t1\n");
	if (!$dbi_s->execute)
	{
		print STDERR "$progname: DROP TEMPORARY TABLE failed: " . $dbi_h->errstr . "\n";
		$dbi_s->finish;
		$dbi_h->disconnect;
		exit(1);
	}
	$dbi_s->finish;

	make_temporary_table();

	if ($verbose)
	{
		print STDERR "$progname: repopulating temporary table for unsigned mail\n";
	}

	$dbi_s = $dbi_h->prepare("INSERT INTO t1 ( 
		ddate,
		msgcount,
		spamcount,
		domain)
		SELECT
		DATE(messages.msgtime),
		COUNT(messages.id),
		SUM(messages.spam),
		'UNSIGNED'
		FROM messages
		JOIN reporters ON reporters.id = messages.reporter
		JOIN signatures ON messages.id = signatures.message
		WHERE reporters.enabled = 1
		AND   NOT messages.spam = -1
		AND   NOT messages.id IN (SELECT DISTINCT message
		                          FROM signatures
		                          WHERE pass = 1)
		GROUP BY DATE(messages.msgtime)");
	if (!$dbi_s->execute)
	{
		print STDERR "$progname: INSERT INTO TEMPORARY TABLE failed: " . $dbi_h->errstr . "\n";
		$dbi_s->finish;
		$dbi_h->disconnect;
		exit(1);
	}
	$dbi_s->finish;

	if ($verbose)
	{
		print STDERR "$progname: reporting for unsigned mail\n";
	}

	$dbi_s = $dbi_h->prepare("SELECT msgcount, spamcount, 1+LOG10(COUNT(id)) FROM t1 GROUP BY 1, 2");
	if (!$dbi_s->execute)
	{
		print STDERR "$progname: domain report for unsigned mail failed: " . $dbi_h->errstr . "\n";
		$dbi_s->finish;
		$dbi_h->disconnect;
		exit(1);
	}

	open(GNUDATA, ">" . $datafile) or die $progname . ": can't create temporary file $datafile\n";
	while ($dbi_a = $dbi_s->fetchrow_arrayref())
	{
		print GNUDATA $dbi_a->[0] . " " . $dbi_a->[1] . " " . $dbi_a->[2] . "\n";
	}
	$dbi_s->finish;
	close GNUDATA;

	print $out "<a name=\"unsigned\"></a>\n";
	print $out "<p> unsigned mail: </p>\n";

	open(GNUPLOT, "|gnuplot > /dev/null 2>&1") or die $progname . ": can't execute gnuplot\n";
	print GNUPLOT "set xrange [0:*]\n";
	print GNUPLOT "set yrange [0:*]\n";
	print GNUPLOT "unset log\n";
	print GNUPLOT "unset label\n";
	print GNUPLOT "set xtic auto\n";
	print GNUPLOT "set ytic auto\n";
	print GNUPLOT "set xlabel 'received'\n";
	print GNUPLOT "set ylabel 'spam'\n";
	print GNUPLOT "set terminal png size 800,600\n";
	print GNUPLOT "set title 'unsigned received-spam scatter plot'\n";
	print GNUPLOT "set output '$pngfile'\n";
	print GNUPLOT "plot '$datafile' using 1:2:3 notitle $circlestr\n";
	close(GNUPLOT);

	rename($pngfile, $reportdir . "/unsigned.png");
	unlink($datafile);

	print $out "<img src=\"unsigned.png\">\n";
}

#
# all done!
#

print $out " </body>\n";
print $out "</html>\n";

if ($verbose)
{
	print STDERR "$progname: terminating at " . localtime() . "\n";
}

$dbi_h->disconnect;

if (defined($reportout))
{
	close $out;
	rename($tmpout, $reportout) or die "$progname: rename to $reportout failed; $!";
}

exit(0);