#!/usr/bin/perl -w

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

# Database connection
my $db_host = 'localhost';
my $db_user = 'username';
my $db_pass = 'password';
my $db_name = 'my_database';

# From which Nucleus blog to export
my $from_blog_id = 1;
# Copy post starting from which date
my $from_date = '2003-07-04 12:00:00';   # Make empty for all
# What WordPress status should posts be given?
my $post_status = 'publish';             # publish, draft, private, etc...            

# Connect to the database
my $dbh = DBI->connect("dbi:mysql:database=$db_name;host=$db_host",$db_user,$db_pass)
			or die $DBI::errstr;

#
# This part migrates posts
#
my %cats = %{ get_categories({'blog_id' => $from_blog_id}) };
print "Categories: ", Dumper(\%cats), "\n";
my %users = %{ get_users() };
print "Users ", Dumper(\%users), "\n";
print "Migrated: ", migrate_posts({'blog_id'=>$from_blog_id,'since'=>$from_date,'status'=>$post_status},\%cats,\%users), " posts\n";


#
# This part migrates comments
#
my %members = %{ get_members() };
print Dumper(\%members);
print "Migrated ", migrate_comments(\%members), " comments\n";


#################################################################################################################

# This sub returns a hashref.  Keys are IDs of Nucleus categories, values are IDs of WordPress categories.
# If any of the categories is missing in WordPress, than the script will die here with the list of missing
# categories in the error message.
sub get_categories {
	my $args = shift;
	my %categories = ();

	return undef unless ($args->{'blog_id'});

	# Get the list of categories in Nucleus
	my $sth = $dbh->prepare('SELECT catid,cname FROM nucleus_category WHERE cblog = ' . $args->{'blog_id'});
	my $rv = $sth->execute();

	my %nucleus_categories = ();
	while (my $data = $sth->fetchrow_hashref()) {
		my $catid = $data->{'catid'};
		my $catname = $data->{'cname'};
		$nucleus_categories{$catid} = $catname;
	}

	# Get the list of matching categories
	$sth = $dbh->prepare('SELECT catid,cname,cat_ID FROM nucleus_category,wp_categories WHERE cname=cat_name');
	$rv = $sth->execute();

	while (my $data = $sth->fetchrow_hashref()) {
		my $nucleus_id = $data->{'catid'};
		my $category_name = $data->{'catname'};
		my $wordpress_id = $data->{'cat_ID'};

		$categories{$nucleus_id} = $wordpress_id;
		delete $nucleus_categories{$nucleus_id};
	}

	# Make sure that all categories exist in WordPress before import
	my $err_str = '';
	foreach my $cat (values %nucleus_categories) {
		$err_str .= " $cat";
	}
	if ($err_str) {
		die "Cannot proceed due to missing categories in WordPress: $err_str\n";
	}

	return \%categories;
}

# This sub returns a hashref.  Keys are IDs of Nucleus users, values are IDs of WordPress users.
# If any of the users is missing in WordPress, than the script will die here with the list of missing
# users in the error message.
sub get_users {
	my $args = shift;
	my %users = ();

	# Get the list of all users who posted items in Nucleus
	my $sth = $dbh->prepare('SELECT iauthor,mname FROM nucleus_item,nucleus_member WHERE iauthor=mnumber GROUP BY iauthor');
	my $rv = $sth->execute();

	my %nucleus_members = ();
	while (my $data = $sth->fetchrow_hashref()) {
		my $member_id = $data->{'iauthor'};
		my $member_name = $data->{'mname'};
		$nucleus_members{$member_id} = $member_name;
	}

	# Get the list of matching users in WordPress
	$sth = $dbh->prepare('SELECT mnumber,mname,ID FROM nucleus_member,wp_users WHERE mname=user_login');
	$rv = $sth->execute();

	while (my $data = $sth->fetchrow_hashref()) {
		my $nucleus_id = $data->{'mnumber'};
		my $member_name = $data->{'mname'};
		my $wordpress_id = $data->{'ID'};

		$users{$nucleus_id} = $wordpress_id;
		delete $nucleus_members{$nucleus_id};
	}

	# Make sure that all members exist in WordPress before import
	my $err_str = '';
	foreach my $member (values %nucleus_members) {
		$err_str .= " $member";
	}
	if ($err_str) {
		die "Cannot proceed due to missing users in WordPress: $err_str\n";
	}

	return \%users;
}

sub migrate_posts {
	my ($args,$categories,$users) = @_;
	my $counter = 0;

	return undef unless ($args->{'blog_id'});

	my $sth;
	# Get the list of posts to migrate
	if ($args->{'since'}) {
		$sth = $dbh->prepare('SELECT ititle,ibody,imore,itime,itime - INTERVAL 2 hour AS gmttime,icat,iauthor FROM nucleus_item WHERE iblog = ' . $args->{'blog_id'} . ' AND itime > \'' . $args->{'since'} . '\' ORDER BY itime');
	}
	else {
		$sth = $dbh->prepare('SELECT ititle,ibody,imore,itime,itime - INTERVAL 2 hour AS gmttime,icat,iauthor FROM nucleus_item WHERE iblog = ' . $args->{'blog_id'} . ' ORDER BY itime');
	}
	my $rv = $sth->execute();

	my $status = $args->{'status'};
	while (my $data = $sth->fetchrow_hashref()) {
		# Keep the counter ticking
		$counter++;
		my $title = $data->{'ititle'};
		$title =~ s#'#\\'#g;

		my $content = $data->{'ibody'};
		# Combine the body and the extended part. Ff there is an extended part.
		if ($data->{'imore'}) {
			$content .=  ' <!--more--> ' . $data->{'imore'};
		}

		# Correct post content links to images and other media.
		if ($content =~ m#\%media#) {
			$content =~ s#\%media\((.+)?\|(.+)?\)\%#a href="/wordpress/wp-content/$1">$2</a#gi;
		}
		elsif ($content =~ m#\%popup#) {
			$content =~ s#\%popup\((.+)?\|(.+)?\|(.+)?\|(.*)?\)\%#img src="/wordpress/wp-content/$1" alt="$4"#gi;
		}
		elsif ($content =~ m#\%image#) {
			$content =~ s#div\sclass="(left|right)box"\>\<\%image\((.+)?\|(.+)?\|(.+)?\|(.*)?\)\%\>\<\/div#img class="align$1" src="/wordpress/wp-content/$2" alt="$5"#gi;
		}
		$content =~ s#'#\\'#g;
		
		my $time = $data->{'itime'};
		my $gmttime = $data->{'gmttime'};  # I guess this one is always empty...
		my $cat = $categories->{ $data->{'icat'} };
		my $user = $users->{ $data->{'iauthor'} };


		print "Importing title: $title\n";
		my $sth1 = $dbh->prepare("INSERT INTO wp_posts(post_author,post_date,post_date_gmt,post_content,post_title,post_status) 
									VALUES('$user','$time','$gmttime','$content','$title','$status')");
		my $rv1 = $sth1->execute();

		print "Done ... ";
		my $insertid = $dbh->{'mysql_insertid'};
		print "insert ID = $insertid\n";

		print "Importing category: $cat\n";

		$sth1 = $dbh->prepare("INSERT INTO wp_post2cat(post_id,category_id) VALUES ($insertid,$cat)");
		$rv1 = $sth1->execute();
		print "Done ... ";

		print "Category: $cat\n";
		print "-------\n";


	}

	return $counter;
}

# This sub returns a hashref.  Keys are IDs of Nucleus members, values are hashes of member information.
sub get_members {
	my $args = shift;
	my %members = ();

	# Get members
	my $sth = $dbh->prepare('SELECT mnumber,mname,mrealname,memail,murl FROM nucleus_member');
	my $rv = $sth->execute();

	while (my $data = $sth->fetchrow_hashref()) {
		my $member_id = $data->{'mnumber'};
		my $member_login = $data->{'mname'};
		my $member_realname = $data->{'mrealname'};
		my $member_email = $data->{'memail'};
		my $member_url = $data->{'murl'};

		$members{$member_id} = {
								'login'=>$member_login,
								'realname'=>$member_realname,
								'email'=>$member_email,
								'url'=>$member_url,
							}
	}

	return \%members;
}

sub migrate_comments {
	my $users = shift;

	# Get all comments from Nucleus
	my $sth = $dbh->prepare('
							SELECT wp_posts.ID,
									nucleus_comment.citem,
									nucleus_comment.ctime,
									nucleus_comment.ctime - INTERVAL 2 HOUR AS gmttime,
									nucleus_comment.cbody,
									nucleus_comment.cuser,
									nucleus_comment.cmail,
									nucleus_comment.cmember,
									nucleus_comment.cip,
									nucleus_item.ititle
							FROM wp_posts,nucleus_comment,nucleus_item 
							WHERE nucleus_comment.citem=nucleus_item.inumber
							AND nucleus_comment.cblog=1
							AND wp_posts.post_date=nucleus_item.itime
							AND wp_posts.post_title=nucleus_item.ititle
							ORDER BY nucleus_comment.cnumber
							');
	my $rv = $sth->execute();

	my $counter = 0;
	while (my $data = $sth->fetchrow_hashref()) {
		# Keep the counter ticking
		$counter++;

		# Cleanup comments quoting
		foreach my $key (keys %{ $data }) {
			$data->{$key} =~ s#'#\\'#g; 
		}

		my $nucleus_item = $data->{'citem'};
		my $wordpress_id = $data->{'ID'};
		my $nucleus_time = $data->{'ctime'};
		my $nucleus_gmttime = $data->{'gmttime'};
		my $comment_body = $data->{'cbody'};
		my $nucleus_user = $data->{'cuser'};
		my $nucleus_mail = $data->{'cmail'};
		my $nucleus_url  = $data->{'cmail'};
		my $nucleus_member = $data->{'cmember'};
		my $nucleus_ip = $data->{'cip'};
		my $item_title = $data->{'ititle'};

		# Nucleus is a bit messy about author emails and URLs, while WordPress is distinct.
		$nucleus_mail = '' unless ($nucleus_mail =~ m/@/);
		$nucleus_url  = '' if ($nucleus_url =~ m/@/);

		# Use member information, if author is a member
		if ($nucleus_member > 0) {
			$nucleus_user = $users->{$nucleus_member}->{'login'} unless $nucleus_user;
			$nucleus_mail = $users->{$nucleus_member}->{'email'} unless $nucleus_mail;
			$nucleus_url  = $users->{$nucleus_member}->{'url'}   unless $nucleus_url;
		}

		print "$nucleus_user ($nucleus_mail, $nucleus_ip) commented on\n";
		print "$item_title ($nucleus_item , $wordpress_id)\n";
		print "\n";
		my $sth1 = $dbh->prepare("INSERT INTO wp_comments(comment_post_ID,comment_author,comment_author_email,comment_author_url,comment_author_IP,comment_date,comment_date_gmt,comment_content)
								VALUES($wordpress_id,'$nucleus_user','$nucleus_mail','$nucleus_url','$nucleus_ip','$nucleus_time','$nucleus_gmttime','$comment_body')");
		my $rv1 = $sth1->execute();
		
	}
	return $counter;
}

# Disconnect from the database
my $rc = $dbh->disconnect();