#!/usr/bin/perl -w
use strict;
use lib qw(../);
use Thraxil;
use DBI;

my $thraxil = Thraxil->new("anders","fnordola") or die "couldn't make thraxil";
my $old = DBI->connect("DBI:Pg:dbname=random","anders","");
$thraxil->start_transaction();
# create main user accounts
my %users;
$users{anders} = 1;
my $sth = $old->prepare("select username,password,email from users where username != 'anders';") or $thraxil->error("bad sql");
$sth->execute() or $thraxil->error($@);

print "adding user accounts\n";
while(my ($username,$password,$email) = $sth->fetchrow_array()) {
    $thraxil->add_user($username,$password,$password,$username,$email);
    $thraxil->user($username)->authorize();
    $users{$username} = 1;
    print "\tadded $username\n";
}

# create comment user accounts
print "adding comment user accounts\n";
$sth = $old->prepare("select distinct poster from comments;") or $thraxil->error("bad sql");
$sth->execute() or $thraxil->error($@);
while(my ($username) = $sth->fetchrow_array()) {
    next if $users{$username};
    $users{$username} = 1;
    my $user = $username;
    $user =~ s/\s//g;
    next unless $user;
    print "\tadding $username [$user]\n";
    $thraxil->add_user($user,$username,$username,$username,
		       'unknown@unknown.com');

}

# create nodes
print "creating nodes\n";
$sth = $old->prepare("select idx,poster,type,post_time,post_date,mod_time,
mod_date,num_comments from items;") or $thraxil->error("bad sql");
my $post_sql = $old->prepare("select subject, body from posts 
where i_idx = ?;") or $thraxil->error("bad sql");
my $image_sql = $old->prepare("select src,t_src,width,height,t_width,
t_height from sketches where i_idx = ?;") or $thraxil->error("bad sql");
my $bookmark_sql = $old->prepare("select b.title,b.href,b.description,c.cat 
from bookmarks b, categories c where b.b_idx = c.b_idx and b.i_idx = ?;");
$sth->execute() or $thraxil->error($@);
my $max_id = 1;
while(my ($idx,$poster,$type,$post_time,$post_date,$mod_time,$mod_date,
	  $num_comments) = $sth->fetchrow_array()) {
    my ($subject,$body,$src,$t_src,$width,$height,$t_width,$t_height,
	$title,$href,$description,$category);
    if($type eq "p") {
	$post_sql->execute($idx) or $thraxil->error($@);
	($subject,$body) = $post_sql->fetchrow_array();
	$type = 'post';
    } elsif ($type eq "s") {
	$image_sql->execute($idx) or $thraxil->error($@);
	($src,$t_src,$width,$height,$t_width,$t_height) = $image_sql->fetchrow_array();
	$type = 'image';
	$subject = "image: $src";
    } else {
	$bookmark_sql->execute($idx) or $thraxil->error($@);
	($title,$href,$description,$category) = $bookmark_sql->fetchrow_array();
	$type = 'bookmark';
	$subject = "bookmark: $title";
    }
    my $sql = qq{insert into nodes (nid,type,author,added,modified,replies,
				    reply_to,subject,body,src,thumb_src,
				    width,height,thumb_width,thumb_height,
				    description,title,url) values 
					(?,?,?,?,?,?,0,?,?,?,?,?,?,?,?,?,?,?);};
    $thraxil->update($sql,[$idx,$type,$poster,"$post_date $post_time",
			   "$mod_date $mod_time",$num_comments,$subject,$body,
			   $src,$t_src,$width,$height,$t_width,$t_height,
			   $description,$title,$href]);
    if($category) {
	my @keywords = split /\//, $category;
	foreach my $kw (@keywords) {
	    $thraxil->update("insert into keywords (nid,keyword)
values (?,?);",[$idx,$kw]);
	}
    }

    print "\tadded [$idx] $subject\n";
    $max_id = $idx if ($idx > $max_id);
}
$post_sql->finish;
$image_sql->finish;
$bookmark_sql->finish;

# create comment nodes
print "adding comments\n";

$max_id++;

$sth = $old->prepare("select c.i_idx,c.poster,c.post_time,c.post_date,c.body
from comments c order by c.post_date ASC, 
c.post_time ASC;") or $thraxil->error("bad sql");
$sth->execute() or $thraxil->error($@);
while(my ($reply_to,$poster,$post_time,$post_date,$body) = $sth->fetchrow_array()) {
    my $sql = qq{insert into nodes (nid,type,author,added,modified,replies,
				    reply_to,subject,body) values 
					(?,'comment',?,?,?,0,?,?,?);};
    $poster =~ s/\s//g;
    unless ($poster) {
	print STDERR "blank poster for reply to $reply_to\n";
	next;
    }
    my $subject = "Re: $reply_to";
    print "\tadding reply [$max_id] to [$reply_to] $subject\n";
    $thraxil->update($sql,[$max_id++,$poster,"$post_date $post_time",
			   "$post_date $post_time",$reply_to,$subject,$body]);

}

# clean up.
$thraxil->end_transaction();
$sth->finish;
$old->disconnect;
print "done\n";
exit 0;