#!/usr/bin/perl
#
# Copyright (c) 2012, 2014, The Trusted Domain Project.  All rights reserved.
#
# Script to import per-message DMARC data.

###
### Setup
###

use strict;
use warnings;

use Switch;

use DBI;
use File::Basename;
use Fcntl qw(:flock);
use Getopt::Long;
use POSIX;

require DBD::mysql;

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

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

my $dbscheme     = "mysql";

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

my $lineno;
my $key;
my $value;

my $action;
my $adkim;
my $align_dkim;
my $align_spf;
my $aspf;
my $dd;
my $dkim_align;
my @dkim_data;
my $dkim_domain;
my @dkim_entry;
my $dkim_result;
my $envdomain;
my $fdomain;
my $ipaddr;
my $jobid;
my $p;
my $pct;
my $pdomain;
my $policy;
my $received;
my $reporter;
my $repuri;
my $sigcount = 0;
my $sp;
my $spf;
my @rua;

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

sub get_value
{
	my $table;
	my $column;
	my $id;
	my $out;

	($table, $column, $id) = @_;

	$dbi_t = $dbi_h->prepare("SELECT $column FROM $table WHERE id = ?");
	if (!$dbi_t->execute($id))
	{
		print STDERR "$progname: failed to $column value for ID $id: " . $dbi_h->errstr . "\n";
		return undef;
	}

	while ($dbi_a = $dbi_t->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$out = $dbi_a->[0];
		}
	}

	return $out;
}
	
sub get_table_id
{
	my $name;
	my $table;
	my $column;
	my $out;

	($name, $table, $column) = @_;

	if (!defined($name) || !defined($table))
	{
		return undef;
	}

	if (!defined($column))
	{
		$column = "name";
	}

	$dbi_t = $dbi_h->prepare("SELECT id FROM $table WHERE $column = ?");
	if (!$dbi_t->execute($name))
	{
		print STDERR "$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
		return undef;
	}

	undef $out;
	while ($dbi_a = $dbi_t->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$out = $dbi_a->[0];
		}
	}

	$dbi_t->finish;

	if (!defined($out))
	{
		$dbi_t = $dbi_h->prepare("INSERT INTO $table ($column) VALUES(?)");
		if (!$dbi_t->execute($name))
		{
			print STDERR "$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}

		$dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
		if (!$dbi_t->execute())
		{
			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}

		while ($dbi_a = $dbi_t->fetchrow_arrayref())
		{
			if (defined($dbi_a->[0]))
			{
				$out = $dbi_a->[0];
			}
		}

		$dbi_t->finish;

		if (!defined($out))
		{
			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}
	}

	return $out;
}

sub update_db
{
	my $rep_id;
	my $from_id;
	my $envfrom_id;
	my $pdomain_id;
	my $ipaddr_id;
	my $msg_id;
	my $sdomain_id;
	my $request_id;

	if ($verbose)
	{
		print STDERR "$progname: updating at line $lineno\n";
	}

	$rep_id = get_table_id($reporter, "reporters");
	$from_id = get_table_id($fdomain, "domains");
	$envfrom_id = get_table_id($envdomain, "domains");
	$pdomain_id = get_table_id($pdomain, "domains");
	$ipaddr_id = get_table_id($ipaddr, "ipaddr", "addr");
	$request_id = get_table_id($from_id, "requests", "domain");

	if (!defined($rep_id) ||
	    !defined($from_id) ||
	    !defined($envfrom_id) ||
	    !defined($pdomain_id) ||
	    !defined($ipaddr_id) ||
	    !defined($request_id))
	{
		return;
	}

	$dbi_s = $dbi_h->prepare("INSERT INTO messages (date, jobid, reporter, policy, disp, ip, env_domain, from_domain, spf, align_spf, align_dkim, sigcount) VALUES(FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	if (!$dbi_s->execute($received, $jobid, $rep_id, $policy, $action, $ipaddr_id, $envfrom_id, $from_id, $spf, $align_spf, $align_dkim, $sigcount))
	{
		print STDERR "$progname: failed to insert message: " . $dbi_h->errstr . "\n";
		return;
	}

	$dbi_s->finish;

	undef $msg_id;
	$dbi_s = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
	if (!$dbi_s->execute())
	{
		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
		return;
	}

	while ($dbi_a = $dbi_s->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$msg_id = $dbi_a->[0];
		}
	}

	$dbi_s->finish;

	if (!defined($msg_id))
	{
		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
		return;
	}

	$dbi_s = $dbi_h->prepare("INSERT INTO signatures (message, domain, pass, error) VALUES(?, ?, ?, ?)");
	foreach $dd (0 .. $#dkim_data)
	{
		my $sdomain;
		my $pass;
		my $error;

		$sdomain = $dkim_data[$dd][0];
		$pass = $dkim_data[$dd][1];
		$error = $dkim_data[$dd][2];

		$sdomain_id = get_table_id($sdomain, "domains");
		if (!defined($sdomain_id))
		{
			next;
		}

		if (!$dbi_s->execute($msg_id, $sdomain_id, $pass, $error))
		{
			print STDERR "$progname: failed to insert DKIM data: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}
	$dbi_s->finish;

	if (get_value("requests", "locked", $request_id) != 1)
	{
		if (scalar @rua > 0)
		{
			$repuri = join(",", @rua);
			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = ? WHERE id = ?");

			if (!$dbi_s->execute($repuri, $request_id))
			{
				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
				$dbi_s->finish;
				return;
			}

			$dbi_s->finish;
		}
		else
		{
			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = NULL WHERE id = ?");

			if (!$dbi_s->execute($request_id))
			{
				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
				$dbi_s->finish;
				return;
			}

			$dbi_s->finish;
		}

		$dbi_s = $dbi_h->prepare("UPDATE requests SET adkim = ?, aspf = ?, policy = ?, spolicy = ?, pct = ? WHERE id = ?");

		if (!$dbi_s->execute($adkim, $aspf, $p, $sp, $pct, $request_id))
		{
			print STDERR "$progname: failed to update policy data for $fdomain: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}

	$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--help             print help and exit\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 ('dbhost=s' => \$dbhost,
                                            'dbname=s' => \$dbname,
                                            'dbpasswd=s' => \$dbpasswd,
                                            'dbport=s' => \$dbport,
                                            'dbuser=s' => \$dbuser,
                                            'help!' => \$helponly,
                                            '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 (!defined($dbhost))
{
	if (defined($ENV{'OPENDMARC_DBHOST'}))
	{
		$dbhost = $ENV{'OPENDMARC_DBHOST'};
	}
	else
	{
		$dbhost = $def_dbhost;
	}
}

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

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

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

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

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";
}

#
# Read history file from stdin.
#

$lineno = 0;
if (!flock(STDIN, LOCK_SH))
{
	print STDERR "$progname: warning: unable to establish read lock\n";
}

while (<STDIN>)
{
	$lineno++;

	chomp;
	($key, $value, $dkim_result) = split;

	switch ($key)
	{
	  case "action"		{
					$action = $value;
				}

	  case "adkim"		{
					$adkim = $value;
				}

	  case "align_dkim"	{
					$align_dkim = $value;
				}

	  case "align_spf"	{
					$align_spf = $value;
				}

	  case "aspf"		{
					$aspf = $value;
				}

	  case "dkim"		{
					undef @dkim_entry;
					push(@dkim_entry, $value);
					push(@dkim_entry, $dkim_result);
					if ($dkim_result eq "4" ||
					    $dkim_result eq "5")
					{
						push(@dkim_entry, 1);
					}
					else
					{
						push(@dkim_entry, 0);
					}
					push(@dkim_data, [ @dkim_entry ]);

					$sigcount++;
				}

	  case "from"		{
					$fdomain = $value;
				}

	  case "job"		{
					if (defined($jobid))
					{
						update_db();

						undef $action;
						undef $adkim;
						undef $align_dkim;
						undef $align_spf;
						undef $aspf;
						undef @dkim_data;
						undef $envdomain;
						undef $fdomain;
						undef $ipaddr;
						undef $jobid;
						undef $p;
						undef $pct;
						undef $pdomain;
						undef $policy;
						undef $received;
						undef $reporter;
						undef @rua;
						$sigcount = 0;
						undef $sp;
						undef $spf;
					}

					$jobid = $value;
				}

	  case "ipaddr"		{
					$ipaddr = $value;
				}

	  case "mfrom"		{
					$envdomain = $value;
				}

	  case "p"		{
					$p = $value;
				}

	  case "pct"		{
					$pct = $value;
				}

	  case "pdomain"	{
					$pdomain = $value;
				}

	  case "policy"		{
					$policy = $value;
				}

	  case "received"	{
					$received = $value;
				}

	  case "reporter"	{
					$reporter = $value;
				}

	  case "rua"		{
					if ($value ne "-")
					{
						push(@rua, $value);
					}
				}

	  case "sp"		{
					$sp = $value;
				}

	  case "spf"		{
					$spf = $value;
				}

	  else			{
					print STDERR "$progname: unknown key '$key' at line $lineno\n";
				}
	}
}

if (defined($jobid))
{
	update_db();
}

#
# all done!
#

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

$dbi_h->disconnect;

exit(0);