#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;

my $DB_DRIVER="mysql";		# The database type you are using
my $DB_NAME="phpbb";		# The database holding your phpbb data
my $DB_HOST="localhost";	# The database server
my $DB_USER="phpbbuser";	# The username to connect to your database
my $DB_PASSWD="goodpassword";	# The password to connect to your database
my $DB_PREFIX="phpbb_";		# The tablename prefix of your phpbb installation

my $dbh;
my $mail;
my $body;

# Initialise {{{
sub Initialise {
# no parameters
# no returnvalue
# initialises the database connection and creates the table if necessary
	my $DSN = "DBI:$DB_DRIVER:database=$DB_NAME:host=$DB_HOST";

	$dbh = DBI->connect($DSN, $DB_USER, $DB_PASSWD);
	unless (defined($dbh)){
		die("Can't connect to database!\n".$dbh->errstr);
	}
	my $isbody=0;
	while(<STDIN>){
		if ($_ eq "\n"){
			$isbody=1;
			next;
		}
		if ($isbody == 0){
			$mail.=$_;
		} else {
			$body.=$_;
		}
	}
}
# }}}
# getForums {{{
sub getForums {
# no parameters
# returns array (array (forumID, catID, forumName, auth_read, auth_view, auth_post, auth_reply))
# reads all Forums into an array
	my @retvalue;
	my $sth = $dbh->prepare("SELECT * FROM ${DB_PREFIX}forums ORDER BY forum_id");
	$sth->execute();

	while (my @row = $sth->fetchrow_array()){
		my @r;
		(@r) = (@row);
		push @retvalue, \@r;
	}
	return @retvalue;
}
# }}}
Initialise();
my @forums = getForums(); # needs to be done here or we will do it way too often in UserCanPost
# UserCanPost {{{
sub UserCanPost {
# This checks if a User can post to a forum or not (new topic)
	my $email = shift;
	my $forumid = shift;
	my @forum;

# First get the information of our Forum
	foreach (@forums){
		next unless ($$_[0] == $forumid);
		@forum = @$_;
		last;
	}

	unless (@forum){
		# uh-oh
		die ("Unknown Forum ID!");
	}

# check for publically available forum
# 0 = all, 1 = registered, 2 = private, 3 = mod, 4 = admin
	return 1 if ($forum[13] <= 1);

# check for permissions
# yeah, this is complicated again. phpBB creates a group for every user. Only the user itself is in that group
# Also, there are groups with more than one user. Fortunately, this enables us to query both cases with the same
# SQL query. Unfortunately, it makes it unreadable. What is done here is this:
# auth_access holds the group <--> privilege data (if a group may access a forum)
# user_group holds the user <--> group data (which user is in which group)
# groups holds the group data (group ID, description, type (single user or multiple user group))
# combining that data gives us a YES or NO.
	my $sth=$dbh->prepare("SELECT groups.* FROM ${DB_PREFIX}groups AS groups, ${DB_PREFIX}auth_access AS auth, ${DB_PREFIX}user_group AS ug, ${DB_PREFIX}users as users ".
				"WHERE groups.group_id = auth.group_id ".
				"AND auth.forum_id = ? ".
				"AND auth.auth_post = 1 ".
				"AND users.user_email = ? ".
				"AND ug.user_id = users.user_id ".
				"AND ug.group_id=auth.group_id")
		or die ("Can't read authorisation: ".$dbh->errstr);
	$sth->execute($forum[0], $email);
	while (my @row = $sth->fetchrow_array()){
# we have a YES
		return 1;
	}
# not readable
	return 0;
}
# }}}
# UserCanReply {{{
sub UserCanReply {
# This checks if a User can post to a forum or not (reply)
	my $email = shift;
	my $forumid = shift;
	my $topicid = shift;
	my $postid = shift;
	my @forum;

# First get the information of our Forum
	foreach (@forums){
		next unless ($$_[0] == $forumid);
		@forum = @$_;
		last;
	}

	unless (@forum){
		# uh-oh
		die ("Unknown Forum ID!");
	}

# check for publically available forum
# 0 = all, 1 = registered, 2 = private, 3 = mod, 4 = admin
	return 1 if ($forum[14] <= 1);

# check for permissions
# yeah, this is complicated again. phpBB creates a group for every user. Only the user itself is in that group
# Also, there are groups with more than one user. Fortunately, this enables us to query both cases with the same
# SQL query. Unfortunately, it makes it unreadable. What is done here is this:
# auth_access holds the group <--> privilege data (if a group may access a forum)
# user_group holds the user <--> group data (which user is in which group)
# groups holds the group data (group ID, description, type (single user or multiple user group))
# combining that data gives us a YES or NO.
	my $sth=$dbh->prepare("SELECT groups.* FROM ${DB_PREFIX}groups AS groups, ${DB_PREFIX}auth_access AS auth, ${DB_PREFIX}user_group AS ug, ${DB_PREFIX}users as users ".
				"WHERE groups.group_id = auth.group_id ".
				"AND auth.forum_id = ? ".
				"AND auth.auth_reply = 1 ".
				"AND users.user_email = ? ".
				"AND ug.user_id = users.user_id ".
				"AND ug.group_id=auth.group_id")
		or die ("Can't read authorisation: ".$dbh->errstr);
	$sth->execute($forum[0], $email);
	while (my @row = $sth->fetchrow_array()){
# we have a YES
		return 1;
	}
# not readable
	return 0;
}
# }}}
# TopicIsLocked {{{
sub TopicIsLocked {
	my $topicid = shift;
	my $sth=$dbh->prepare("SELECT topic_status FROM ${DB_PREFIX}topics WHERE topic_id = ?")
		or die ("Can't select topic_status: ".$dbh->errstr);
	$sth->execute($topicid)
		or die ("Unknown Topic: $topicid!");
	my @topic = $sth->fetchrow_array();
	return $topic[0];
}
# }}}
# getHeader {{{
sub getHeader {
# parameter 1: header to retrieve
# return value: header without ^$header: 
	my $header = shift;
	foreach my $line (split /\n/, $mail){
		if ($line =~ /^$header: /){
			$line =~ s/^$header: //g;
			return $line;
		}
	}
	return undef;
}
# }}}

my $forumid;
my $topicid;
my $postid;

my $inreplyto = getHeader("In-Reply-To");
my $subscriber = getHeader("From");
my $subject = getHeader("Subject");

$subscriber =~ /<(.*?)>/;
$subscriber = $1;
if (defined($inreplyto)){
	die ("Can't parse Message-ID: '$inreplyto'") unless $inreplyto =~ /forumid_([0-9]+)_topicid_([0-9]+)_postid_([0-9]+)/;
	$inreplyto =~ /forumid_([0-9]+)_topicid_([0-9]+)_postid_([0-9]+)/;
	$forumid=$1;
	$topicid=$2;
	$postid=$3;
	die ("User can't reply here!") unless UserCanReply($subscriber, $forumid, $topicid, $postid); # don't add post if user can't write here
	die ("Topic is locked!") if TopicIsLocked($topicid);

# everything's fine. Let's write!
	my $sth = $dbh->prepare("SELECT * FROM ${DB_PREFIX}users WHERE user_email = ?");
	$sth->execute($subscriber) or die ("E-Mail address '$subscriber' has on user!");
	my @user = $sth->fetchrow_array();
	$dbh->do("INSERT INTO ${DB_PREFIX}posts (topic_id, forum_id, poster_id, post_time, poster_ip, post_username) VALUES ".
		 "(?, ?, ?, ?, '7F000001', ?)", undef, $topicid, $forumid, $user[0], time(), $user[2])
		or die ("Can't insert post: ".$dbh->errstr);
	$sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM ${DB_PREFIX}posts");
	$sth->execute();
	my @row = $sth->fetchrow_array();
	$dbh->do("INSERT INTO ${DB_PREFIX}posts_text (post_id, bbcode_uid, post_subject, post_text) VALUES (?, '', ?, ?)", undef, $row[0], $subject, $body);
	$dbh->do("UPDATE ${DB_PREFIX}topics SET topic_replies=topic_replies+1, topic_last_post_id=? WHERE topic_id = ?", undef, $row[0], $topicid);
	$dbh->do("UPDATE ${DB_PREFIX}forums SET forum_posts=forum_posts+1, forum_last_post_id=? WHERE forum_id = ?", undef, $row[0], $forumid);
	$dbh->do("UPDATE ${DB_PREFIX}users SET user_posts=user_posts+1 WHERE user_id = ?", undef, $user[0]);
} else {
	my @forum = undef;
	if ($subject !~ /:/){
		die ("Subject in wrong format!");
	}
	my ($name, $topic) = split(/:/, $subject);
	foreach my $f (@forums){
		if ($$f[2] =~ /$name/i){
			if ($forum[0]){
				die("Can't get a distinct Forum! Got: $name");
			} else {
				@forum = @$f;
			}
		}
	}
	unless (@forum){
		die("Can't get any Forum! Got: $name");
	}
	die ("User can't post here!") unless UserCanPost($subscriber, $forum[0]); # don't add post if user can't write here

# Looking good :-)
	my $sth = $dbh->prepare("SELECT * FROM ${DB_PREFIX}users WHERE user_email = ?");
	$sth->execute($subscriber) or die ("E-Mail address '$subscriber' has on user!");
	my @user = $sth->fetchrow_array();
	$dbh->do("INSERT INTO ${DB_PREFIX}posts (forum_id, poster_id, post_time, poster_ip, post_username) VALUES ".
			"(?, ?, ?, '7F000001', ?)", undef, $forum[0], $user[0], time(), $user[2])
			or die ("Can't insert post: ".$dbh->errstr);
	$sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM ${DB_PREFIX}posts");
	$sth->execute();
	my @row = $sth->fetchrow_array();
	$dbh->do("UPDATE ${DB_PREFIX}posts SET topic_id=post_id WHERE post_id=?", undef, $row[0]);
	$dbh->do("INSERT INTO ${DB_PREFIX}posts_text (post_id, bbcode_uid, post_subject, post_text) VALUES (?, '', ?, ?)", undef, $row[0], $subject, $body);
	$dbh->do("INSERT INTO ${DB_PREFIX}topics (topic_id, forum_id, topic_title, topic_poster, topic_time, topic_first_post_id, topic_last_post_id) VALUES".
		 "(?, ?, ?, ?, ?, ?, ?)", undef, $row[0], $forum[0], $subject, $user[0], time(), $row[0], $row[0]);
	$dbh->do("UPDATE ${DB_PREFIX}forums SET forum_topics=forum_topics+1, forum_posts=forum_posts+1, forum_last_post_id=? WHERE forum_id=?", undef, $row[0], $forum[0]);
	$dbh->do("UPDATE ${DB_PREFIX}users SET user_posts=user_posts+1 WHERE user_id = ?", undef, $user[0]);
}