#!/usr/bin/perl

# Mantis Mail Gateway
# Shen Cheng-Da (cdsheen AT gmail.com)
# require DBI to connect MySQL database
# http://blog.urdada.net/2008/11/11/95/

use DBI;
use POSIX qw(strftime);

my $db_host    = 'localhost';
my $db_name    = 'mantis';
my $db_user    = 'monitor';
my $db_pass    = 'monitorpass';

my $debug      = 0;

my $db = "dbi:mysql:dbname=${db_name};host=${db_host}";

die "Usage: $0 [project-name] [reporter]\n" unless @ARGV > 1;

my $project_name  = $ARGV[0];
my $reporter_name = $ARGV[1];

my $dbh;
my $sql;
my $sth;
my $project  = -1;
my $reporter = -1;

$dbh = DBI->connect($db, $db_user, $db_pass)
	|| die 'ERROR: '.$dbh->errstr;

$sql = "SELECT id,name FROM mantis_project_table
	WHERE name LIKE '$project_name'";

$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute() || die 'ERROR: '.$dbh->errstr;
while( @data = $sth->fetchrow_array() ) {
	$project = $data[0];
}
$sth->finish;

die "ERROR: project \`$project_name' does not exist\n" unless $project > 0;

print "project: $project_name ($project)\n" if $debug;

$sql = "SELECT id,username FROM mantis_user_table
	WHERE username = '$reporter_name'";

$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute() || die 'ERROR: '.$dbh->errstr;
while( @data = $sth->fetchrow_array() ) {
	$reporter = $data[0];
}
$sth->finish;

die "ERROR: user \`$reporter_name' does not exist\n" unless $reporter > 0;

print "reporter: $reporter_name ($reporter)\n" if $debug;

my $subject = '';
my $content = '';

while(<STDIN>) {
	s/\s+$//;
	last if $_ eq '';
	$subject = $1 if /^Subject: (.+)$/;
}
while(<STDIN>) {
	$content .= $_;
}

$sql = 'INSERT INTO mantis_bug_text_table (description) VALUES (?)';

$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute($content) || die 'ERROR: '.$dbh->errstr;
$sth->finish;

my $textid = $dbh->{ q{mysql_insertid} };

print "bug text id: $textid\n" if $debug;

$sql = 'INSERT INTO mantis_bug_table
		( project_id, reporter_id,
		  date_submitted, last_updated,
		  bug_text_id, summary )
	VALUES (?,?,?,?,?,?)';

my $now = strftime('%Y-%m-%d %H:%M:%S', localtime(time));

$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute($project, $reporter, $now, $now, $textid, $subject)
	|| die 'ERROR: '.$dbh->errstr;
$sth->finish;

$dbh->disconnect;