#!/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 $FROM = "<email2phpbb\@server.com>";	# The valid reply address that goes to the email2phpbb.pl script
my $MSGID = "myforum\@server.com";	# The local message-id part. Must be "yourforumname@yourdomainname"

my $dbh;

# mail {{{
sub mail {
# This function sends mail. I'm quite proud of it :-)
# mail("sender@localhost", "blindcoder@host.com", "Headers", "Subject", "All your base\nare belong to us!", ( "import/import_invalid.txt", "errors.txt" ), ( "/etc/passwd", "passwd.txt" ));
	my $sender=shift;
	my $recipient=shift;
	my $header=shift;
	my $subject=shift;
	my $text=shift;
	my $att=0;
	my $boundary="----------".(time());
	my @files;
	while ($_[0]){
		push @files, $_[0];
		shift;
		$att++;
	}

	open MAIL, "|/usr/bin/sendmail $recipient";
	print MAIL "From: $sender\n";
	print MAIL "$header";
	print MAIL "Subject: $subject\n";
	if ($att > 0){
		print MAIL "MIME-Version: 1.0\n";
		print MAIL "Content-Type: multipart/mixed;\n";
		print MAIL " boundary=\"$boundary\"\n";
		print MAIL "\n";
		print MAIL "This is a multi-part message in MIME format.\n";
		print MAIL "--$boundary\n";
		print MAIL "Content-Type: text/plain;\n";
		print MAIL "\n";
	} else {
		print MAIL "\n";
	}

	print MAIL "$text";

	if ($att > 0){
		while ($files[0]){
			my ($file, $sendas) = (@files);
			shift @files; shift @files;
			print MAIL "--$boundary\n";
			print MAIL "Content-Type: text/plain;\n";
			print MAIL " name=\"$sendas\"\n";
			print MAIL "Content-Disposition: inline;\n";
			print MAIL " filename=\"$sendas\"\n";
			print MAIL "\n";
			open IN, "$file";
			while (<IN>){
				print MAIL $_;
			}
			close IN;
		}
		print MAIL "--$boundary--\n";
	}

	close MAIL;
}
# }}}
# 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);
	}

	$dbh->do("CREATE TABLE IF NOT EXISTS `${DB_PREFIX}mailgateway` (
		      `${DB_PREFIX}posts_post_id` BIGINT NOT NULL ,
		      `done` TINYINT DEFAULT '0' NOT NULL ,
		      UNIQUE (
			  `${DB_PREFIX}posts_post_id`
		      )
		    );")
		or die ("Can't create ${DB_PREFIX}mailgateway: ".$dbh->errstr);
}
# }}}
# 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;
}
# }}}
# getSubscribers {{{
sub getSubscribers {
# no parameters
# returns all users who want to receive posts by mail in a 1D array (email1, email2, ...)
# This will need to be done on a per-site basis
# For example by a public group which one can join.
	my @retval;
	push @retval, "testuser\@server.com";
	return @retval;
}
# }}}
# getNewPosts {{{
sub getNewPosts {
# no parameters
# returns array (postID, topic_id, forum_id, post_time, enable_sig, subject, post_text, username, user_sig, forum_name);
# yeah, so, this is  a bit complex.
# The above information will be retrieved for all posts that are NOT YET in the ${DB_PREFIX}mailgateway table. I didn't want
# to screw around with existing phpbb tables.

	my @retvalue;
	my $sth = $dbh->prepare("SELECT posts.post_id, posts.topic_id, posts.forum_id, posts.post_time, posts.enable_sig, ".
				"posts_text.post_subject, posts_text.post_text, ".
				"users.username, users.user_sig, ".
				"forums.forum_name ".
				"FROM ${DB_PREFIX}posts AS posts, ${DB_PREFIX}posts_text AS posts_text, ${DB_PREFIX}users AS users, ".
				"     ${DB_PREFIX}forums AS forums ".
				"WHERE posts_text.post_id = posts.post_id AND ".
				"posts.poster_id = users.user_id AND ".
				"forums.forum_id = posts.forum_id AND ".
				"NOT EXISTS (".
					"SELECT * FROM ${DB_PREFIX}mailgateway WHERE ${DB_PREFIX}posts_post_id = posts.post_id".
				") ".
				"ORDER BY posts.post_id ASC")
	or die ("Can't select posts: ".$dbh->errstr);
	$sth->execute();

	while (my @row = $sth->fetchrow_array()){
		my @r;
		(@r) = (@row);
		push @retvalue, \@row;
	}
	return @retvalue;
}
# }}}
Initialise();
my @forums = getForums(); # needs to be done here or we will do it way too often in UserCanRead
my @subscribers = getSubscribers();
my @posts = getNewPosts();
# getPost {{{
sub getPost {
# parameter 1: posts.post_id
# returns array (postID, topic_id, forum_id, post_time, enable_sig, subject, post_text, username, user_sig, forum_name);
# This returns information about a single post regardless if it exists in ${DB_PREFIX}mailgateway or not
	my @retvalue;
	my $sth = $dbh->prepare("SELECT posts.post_id, posts.topic_id, posts.forum_id, posts.post_time, posts.enable_sig, ".
				"posts_text.post_subject, posts_text.post_text, ".
				"users.username, users.user_sig ".
				"forums.forum_name ".
				"FROM ${DB_PREFIX}posts AS posts, ${DB_PREFIX}posts_text AS posts_text, ${DB_PREFIX}users AS users ".
				"     ${DB_PREFIX}forums AS forums ".
				"WHERE posts_text.post_id = posts.post_id AND ".
				"forums.forum_id = posts.forum_id AND ".
				"posts.poster_id = users.user_id AND ".
				"posts.post_id = ?")
	or die ("Can't select posts: ".$dbh->errstr);
	$sth->execute($_[0]);

	while (my @row = $sth->fetchrow_array()){
		my @r;
		(@r) = (@row);
		push @retvalue, \@row;
	}
	return @retvalue;
}
# }}}
# MarkPostProcessed {{{
sub MarkPostProcessed {
# parameter 1: posts.post_id
# no return
# This marks a post as processed wrt. mailgateway
	$dbh->do("DELETE FROM ${DB_PREFIX}mailgateway WHERE ${DB_PREFIX}posts_post_id = ?", undef, $_[0]);
	$dbh->do("INSERT INTO ${DB_PREFIX}mailgateway VALUES (?, 1)", undef, $_[0]);
}
# }}}
# UserCanRead {{{
sub UserCanRead {
# This checks if a User can read a forum or not
	my $email = shift;
	my @post = @_;
	my @forum;

# First get the information of our Forum
	foreach (@forums){
		next unless ($$_[0] == $post[2]);
		@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[12] <= 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_read = 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;
}
# }}}

foreach my $post (@posts){
	foreach my $subscriber (@subscribers){
		next unless UserCanRead($subscriber, @$post); # don't send mail if the user can't read that forum
		my $sender;
		my $body;
		my $headers;
		my $subject;

		$body = "$$post[6]";
# try to get the [quote] thingies into nicely indented mail using "> " at beginning-of-line
		while ($body =~ /\[quote:([^=]+)="([^"]*)"[^\]]*\]/){
			my $quid = $1;
			my $user = $2;
			$body =~ /\[quote:${quid}="${user}"\](.*?)\[\/quote:${quid}\]/s;
			my $text = $1;
			$text =~ s/^/> /smg;
			$text = "$user wrote:\n$text";
			$body =~ s/\[quote:${quid}=[^\]+].*?\[\/quote:${quid}.*\]/$text/gs;
		}
# if the user wants his signature attached, do so using the standardised "-- " signature delimiter
		if ($$post[4] == 1){
			$body.="\n-- \n";
			$body.=$$post[8];
		}

# Try to get a good subject
		$subject=$$post[5];
		unless (defined($subject)){
			$subject="None";
			if ($$post[1] != $$post[0]){
				my @p = getPost($$post[1]);
				$subject=$p[5];
				$subject="None" unless defined($subject);
			}
		}
		$sender = "$$post[7]: $FROM"; # add the Forum name

# add the message id and in-reply-to headers. These are necessary so that all but really bad MUAs can display threads nicely.
# Also, we need this to pin replies to their correct threads.
		$headers = "Message-Id: <forumid_$$post[2]_topicid_$$post[1]_postid_$$post[0]_$MSGID>\n";
		if ($$post[1] != $$post[0]){
			$headers .= "In-Reply-To: <forumid_$$post[2]_topicid_$$post[1]_postid_$$post[1]_$MSGID>\n";
		}
		$headers .= "Reply-To: $FROM\n";
		$headers .= "To: $subscriber\n";
		mail($sender, $subscriber, $headers, "$$post[9]: $subject", $body);
	}
	MarkPostProcessed($$post[0]); # mark this post as processed so that we don't send mails more than once
}