#!perl -w
use strict;
use HTML::TreeBuilder;
use POSIX;
use IO::File;
$|=1;
my %g_months= (
    Jan=>1, Feb=>2, Mar=>3, Apr=>4, May=>5, Jun=>6, Jul=>7, Aug=>8, Sep=>9, Oct=>10, Nov=>11, Dec=>12
);

my $g_bbcodeuid= "7265636f76657265";
my $g_attinfo= read_attachinfo("xda-developers-orig.sql");
#@0.1.3.0.0.2.0   topic info
#@0.1.3.0.0.4.*.0.0.1.0 author profile
#@0.1.3.0.0.4.*.0.0.1.0.0  author name
#@0.1.3.0.0.4.*.1.0.0.0.0.0.1.0  post date
#@0.1.3.0.0.4.*.1.0.0.0.0.1.1.0.1  post subject
#@0.1.3.0.0.4.*.1.0.2.0.0   post body

#my $g_db= DB->new("dbi:mysql:tstdb", "root", "Reutel66#");
my $g_db= DBTST->new();
#my $g_db= DB_RO->new(GetDatabaseConfig("/home/hosting/forum.xda-developers.com/config.php"));

for my $g_filename (@ARGV) {
    print "\n------------------$g_filename\n\n";

    my $tree = HTML::TreeBuilder->new();
    $tree->parse_file($g_filename);

    my $topic= ParseHtmlFile($tree);

    $tree->delete;

    AddToDatabase($g_db, $topic);
}
$g_db->close();
exit(0);

################################################################
# database functions
sub GetDatabaseConfig {
	my ($phpbbconfig)= @_;
	my %params;

	my $fh= IO::File->new($phpbbconfig, "r") or die "readdbcfg: $phpbbconfig\n";

	while (<$fh>) {
		if (/^\s*\$(\w+)\s*=\s*'(.*?)';/) {
			$params{$1}= $2;
		}
	}

	$fh->close();

	my $dbi_datasource="dbi:$params{dbms}:$params{dbname}";
	return $dbi_datasource, @params{qw(dbuser dbpasswd table_prefix)};
}
sub AddToDatabase {
    my ($db, $topic)= @_;

    # try to find all author's, use -1,'name'  when not found.
    for my $post (@{$topic->{posts}}) {
        my $author= $post->{author};
        $author->{dbid}= find_author($db, $author);
    }


    # get topic id.
    $topic->{dbid} = find_topic($db, $topic);
    if (!$topic->{dbid}) {
        $topic->{dbid}= make_new_topic($db, $topic);
    }

    # find post id's for all posts.
    for my $post (@{$topic->{posts}}) {
        $post->{dbid}= find_post($db, $post, $topic);

        if (!$post->{dbid}) {
            $post->{dbid}= make_new_post($db, $post, $topic);
        }

        # todo: how to deal with attachments?
        #for my $att (@{$post->{attachments}}) {
        #    $att->{dbid}= $db->execsql("select attach_id from phpbb_attachments where attach_id=?", $post->{id}, $att->{id});
        #}
    }
}
sub find_author {
    my ($db, $author)= @_;
    my ($id)= $db->execsql("select user_id from phpbb_users where user_id=? and username=?", $author->{id}, $author->{name});
    if ($id) {
        return $id;
    }
    ($id) = $db->execsql("select user_id from phpbb_users where username=?", $author->{name});
    if ($id) {
        return $id;
    }
    return undef;
}
sub find_topic {
    my ($db, $topic)= @_;
    my ($id, $topictime)= $db->execsql("select topic_id, topic_time from phpbb_topics where topic_id=?", $topic->{id});

    if (!$id || $id>12400) {
        return undef;
    }
    return $id;
}
sub find_post {
    my ($db, $post, $topic)= @_;
    my ($id, $posttime)= $db->execsql("select post_id, post_time from phpbb_posts where post_id=? and topic_id=?", $post->{id}, $topic->{id});

    if (!$id || $id>63405) {
        return undef;
    }
    return $id;
}

sub make_new_topic {
    my ($db, $topic)= @_;

    return $db->execchange("insert into phpbb_topics (forum_id, topic_title, topic_poster, topic_wallaby, topic_himalaya, topic_blueangel) values(?,?,?,?,?,?)", 
        $topic->{forumid}, $topic->{title}, $topic->{posts}[0]{author}{dbid} || -1, 
        $topic->{type} eq "wallaby", $topic->{type} eq "himalaya", $topic->{type} eq "blueangel");
}
sub make_new_post {
    my ($db, $post, $topic)= @_;

    my $postid= $db->execchange("insert into phpbb_posts (forum_id, poster_id, post_time, post_username, enable_bbcode, enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count, post_attachment) values(?,?,?,?,?,?,?,?,?,?,?)", 
        $topic->{forumid}, $post->{author}{dbid} || -1, $post->{posttime}, 
        defined $post->{author}{dbid} ? "" : $post->{author}{name} , 1, 0, 1, 0, 
        $post->{lastedit}, $post->{editcount}, @{$post->{attachments}}!=0);

    print "new post: $postid\n";

    $db->execchange("insert into phpbb_posts_text (post_id, bbcode_uid, post_subject, post_text) values (?,?,?,?)",
        $postid, $g_bbcodeuid, $post->{subject}, $post->{body});
    return $postid;
}
#####################################################
#  html parsing functions

sub ParseHtmlFile {
    my $tree= shift;

    my $topic= ParseTopic($tree->address("0.1.3.0.0.2.0"));
    $topic->{forumid}= ParseForumId($tree->address("0.1.3.0.0.3.0.0.0.0"));

    #print "forumid: $topic->{forumid}\n";

    my $table= $tree->address("0.1.3.0.0.4");

    my @trs= $table->content_list();
    my $nlines= scalar @trs;
    #print "nr of lines: $nlines\n";

    my $startline= ($trs[1]->look_down("_tag", "th")) ? 2 : 3 ;

    # todo: if startline == 3 -> poll
    for (my $line=$startline ; $line < $nlines-1 ; $line+=3) {
        my $post= {};

        #print "........ $line\n";
        $post->{id}=      ParsePostId(        $trs[$line]->address(".0.0.0"));
        $post->{author}=  ParseAuthor(        $trs[$line]->address(".0.0.1.0"));
        $post->{posttime}=ParsePostingDate(   $trs[$line]->address(".1.0.0.0.0.0.1.0"));
        $post->{subject}= ParsePostingSubject($trs[$line]->address(".1.0.0.0.0.1.1.0.1"));
        $post->{body}=    ParsePostingBody(   $trs[$line]->address(".1.0.2.0"));
        $post->{attachments}= ParsePostingAttachments(   $trs[$line]->address(".1.0.2.0"));

        # todo: edittime, editcount
        $post->{edittime}= undef;
        $post->{editcount}= 0;

        #write_post($post, $topic);

        push @{$topic->{posts}}, $post;
    }
    return $topic;
}
sub write_post {
    my ($post, $topic)= @_;
    printf("Id: %d\nFrom: %s\nDate: %s\nSubject: %s\nAttachments: %s\n\n%s\n\n", 
        $post->{id},
        $post->{author}{name} || "NONAME", $post->{posttime} || "NODATE", 
        $post->{subject} || "NOSUBJECT", join(",",map { $_->{id} || $_->{filename} } @{$post->{attachments}}) || "NOATTACHMENTS",
        $post->{body} || "NOBODY");
}
sub ParseAttachment {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty attachment\n";
        return;
    }
    # todo: embedded attachments are formatted different.
    my $ah= $el->look_down("_tag", "a", "class", "genmed");
    if (!$ah) {
        my $embed= $el->look_down("_tag", "embed", "name", "MediaPlayer2");
        if (!$embed) {
            print "WARNING: no link to attachment\n";
            $el->dump;
            return;
        }

        return {
            filename=>$embed->attr("src"),
        };
    }
    if ($ah->attr("href") !~ /download.php\?id=(\d+)/) {
        print "WARNING: no id for attachment\n";
        return;
    }
    my $atid= $1;

    return {
        id=>$atid,
    };
}
sub ParseTopic {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty topic\n";
        return;
    }

    my $topic= {};

    my $imgtag= $el->look_down("_tag", "img");
    if (!$imgtag) {
        #print "WARNING: no topic type\n";
        $topic->{type}= "";
    }
    elsif ($imgtag->attr("src") =~ /himalaya/i) {
        $topic->{type}= "himalaya"
    }
    elsif ($imgtag->attr("src") =~ /wallaby/i) {
        $topic->{type}= "wallaby"
    }
    elsif ($imgtag->attr("src") =~ /blueangel/i) {
        $topic->{type}= "blueangel"
    }
    else {
        print "WARNING: unknown topic type:", $imgtag->attr("src"), "\n";
    }

    my $subjtag= $el->look_down("_tag", "a", "class", "maintitle");
    if (!$subjtag) {
        print "WARNING: no topic title\n";
    }
    else {
        my @content= $subjtag->content_list();
        if (@content != 1) {
            print "WARNING: unknown topic title a content\n";
            return;
        }
        $topic->{title}= $content[0];

        if ($subjtag->attr("href") !~ /\?t=(\d+)/) {
            print "WARNING: unknown topic id a href\n";
            return;
        }
        $topic->{id}= $1;
    }
    return $topic;
}
sub ParseForumId {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty forumid\n";
        return;
    }

    if ($el->tag ne "a") {
        print "WARNING: unknown tag in forumid: ", $el->tag, "\n";
        return;
    }
    if ($el->attr("href") !~ /&f=(\d+)/) {
        print "WARNING: unknown forum id a href\n";
        return;
    }
    return $1;
}


sub ParsePostId {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty post id\n";
        return;
    }
    if ($el->tag ne "a") {
        print "WARNING: unknown post id tag: ", $el->tag, "\n";
        return;
    }
    if (!$el->attr("name")) {
        print "WARNING: unknown post id a tag\n";
        return;
    }
    return $el->attr("name");
}
sub ParseAuthor {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty author\n";
        return;
    }
    if ($el->tag ne "a") {
        print "WARNING: unknown author tag: ", $el->tag, "\n";
        return;
    }
    if (!$el->attr("href")) {
        print "WARNING: unknown author a tag\n";
        return;
    }
    if ($el->attr("href") !~ /&u=(\d+)/i) {
        print "WARNING: no author id in url: ", $el->attr("href"), "\n";
        return;
    }
    my $userid= $1;

    my @content= $el->content_list();
    if (@content < 2) {
        print "WARNING: author content list has ", scalar @content, " elements\n";
        $el->dump;
        return;
    }
    my $username= $content[-1];

    return {
        id=>$userid,
        name=>$username,
    };
}
sub ParsePostingDate {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty posting date\n";
        return;
    }
    if ($el->tag ne "span") {
        print "WARNING: posting date unknown tag: ", $el->tag, "\n";
        return;
    }
    my @content= $el->content_list();
    if (@content != 1) {
        print "WARNING: date element has ", scalar @content, " elements\n";
        return;
    }
    # Aug 18, 2004 22:23
    if ($content[0] !~ /(\w+)\s(\d+),\s(\d+)\s(\d+):(\d+)/) {
        print "WARNING: unknown date format: $content[0]\n";
        return;
    }
    my ($monthname, $mday, $year, $hour, $minute)= ($1, $2, $3, $4, $5);

    return POSIX::mktime(0, $minute, $hour, $mday, monthnumber($monthname)-1, $year-1900);
}
sub ParsePostingSubject {
    my $el= shift;
    if (!ref $el) {
        #print "WARNING: empty posting subject\n";
        return "";
    }
    if ($el->tag ne "strong") {
        print "WARNING: posting subject unknown tag: ", $el->tag, "\n";
        return;
    }
    my @content= $el->content_list();
    if (@content != 1) {
        print "WARNING: subject element has ", scalar @content, " elements\n";
        return;
    }
    return $content[0];

}
sub ParsePostingAttachments {
    my $el= shift;
    my @attachments;
    for my $attel ($el->look_down("_tag", "table", "class", "attachtable")) {
        push @attachments, ParseAttachment($attel);
    }
    for my $attel ($el->look_down("_tag", "table", "class", "plaatje")) {
        push @attachments, ParseInlineImage($attel);
    }
    return \@attachments;
}
sub ParsePostingBody {
    my $el= shift;
    if (!ref $el) {
        print "WARNING: empty posting body\n";
        return;
    }
    if ($el->tag ne "td") {
        print "WARNING: posting body unknown tag: ", $el->tag, "\n";
        return;
    }

    return to_bbcode($el);
}

sub monthnumber {
    my $name= shift;
    if (!exists $g_months{$name}) {
        print "WARNING: unknown monthname $name\n";
        return 0;
    }
    return $g_months{$name};
}

sub ParseInlineImage {
    my $el= shift;

    if (!$el->look_down("_tag", "table", "class", "plaatje")) {
        print "WARNING: unrecognised inline image table\n";
        return;
    }

    my @images;
    for my $href ($el->look_down("_tag", "a", "target", "_blank")) {
        #print "..... inline image .....\n";
        push @images, {
            filename=>$href->attr("href"),
        }
    }

    #print "found ", scalar @images, " images\n";

    return @images;
}
# [code]            [/code:uid]     <table...>
# [quote:uid]       [/quote:uid]    <table...>
# [quote:uid="..."] [/quote:uid]    <table...>
# [list:uid]        [/list:u:uid]   <ul>
# [list=1:uid]      [/list:o:uid]   <ol type="1">
# [list=a:uid]      [/list:o:uid]   <ol type="a">
# [*:uid]                           <li>
# [color=...:uid]   [/color:uid]    <span style="color: {COLOR}">
# [size=...:uid]    [/size]         <span style="font-size: {SIZE}px; line-height: normal">
# [b:uid]           [/b:uid]        <span style="font-weight: bold">
# [u:uid]           [/u:uid]        <span style="text-decoration: underline">
# [i:uid]           [/i:uid]        <span style="font-style: italic">
# [img:uid]url[/img:uid]            <img src="{URL}" border="0" />
# [url]url[/url]
# [url=...]desc[/url]      <a href="{URL}" target="_blank" class="postlink">{DESCRIPTION}</a>
# [email]addr[/email]                  <a href="mailto:{EMAIL}">{EMAIL}</A>

sub to_bbcode {
    my $el= shift;

    my $bb= "";
    my $open= "";
    my $close= "";
    if (!ref $el) {
        return $el;
    }
    #print "to_bbcode(", $el->tag, ")\n";
    if ($el->tag eq "span" && aequals($el->attr("class"), "postbody"))  {
        $open= $close= "";
    }
    elsif ($el->tag eq "style") {
        return "";
    }
    elsif ($el->tag eq "span" && aequals($el->attr("style"), "font-style: italic")) {
        $open = "[i]";
        $close = "[/i]";
    }
    elsif ($el->tag eq "span" && aequals($el->attr("style"), "font-weight: bold")) {
        $open = "[b]";
        $close = "[/b]";
    }
    elsif ($el->tag eq "span" && $el->attr("style") &&  $el->attr("style") =~ /font-size: (\d+)px; line-height: normal/) {
    # todo: verify if this is how 'size' maps.
        $open = "[size=$1]";
        $close = "[/size]";
    }
    elsif ($el->tag eq "span" && $el->attr("style") && $el->attr("style") =~ /color: (\w+)/) {
        $open = "[color=$1]";
        $close = "[/color]";
    }
    elsif ($el->tag eq "span" && aequals($el->attr("style"), "text-decoration: underline")) {
        $open = "[u]";
        $close = "[/u]";
    }
    elsif ($el->tag eq "li") {
        $open = "[*]";
        $close= "";
    }
    elsif ($el->tag eq "ul") {
        $open = "[list]";
        $close= "[/list]";
    }
    elsif ($el->tag eq "ol") {
        $open = "[list=".$el->attr("type")."]";
        $close= "[/list]";
    }
    elsif ($el->tag eq "span" && aequals($el->attr("class"), "tt_inline")) {
        $open= $close= "";
        #print ".....................encyclop\n";
    }
    elsif ($el->tag eq "br") {
        $bb .= "\n";
        return $bb;
    }
    elsif ($el->tag eq "img") {
        $bb .= "[img]";
        $bb .= $el->attr("src");
        $bb .= "[/img]";

        return $bb;
    }
    elsif ($el->tag eq "a" && $el->attr("href") && $el->attr("href") =~ /mailto:/) {
        return "[email]".$el->attr("href")."[/email]";
    }
    elsif ($el->tag eq "a") {
        # aequals($el->attr("target"), "_blank") && aequals($el->attr("class"), "postlink")
        $open = "[url=".$el->attr("href")."]";
        $close = "[/url]";

        my @content= $el->content_list;
        if (@content==1 && $content[0] eq $el->attr("href")) {
            return "[url]".$el->attr("href")."[/url]";
        }
    }
    elsif ($el->tag eq "table" && aequals($el->attr("class"), "attachtable")) {
        print "NOTE: skipping attachtable\n";
        return "";
    }
    elsif ($el->tag eq "table" && aequals($el->attr("class"), "plaatje")) {
        print "NOTE: skipping inline image\n";
        return "";
    }
    elsif ($el->tag eq "table") {
        my $head= $el->look_down("_tag", "b");
        if (!$head) {
            #print "WARNING: unrecognised table format - no b: class=", $el->attr("class") || "", "\n";
            #return ParseInlineImage($el);
            return "";
        }

        if ($head->as_text =~ /^(.*?) wrote:/) {
            my $username= $1;
            my $content= to_bbcode($el->look_down("_tag", "td", "class", "quote"));
            return "[quote=\"$username\"]$content\[/quote]";
        }
        elsif ($head->as_text eq "Quote:") {
            my $content= to_bbcode($el->look_down("_tag", "td", "class", "quote"));
            return "[quote]$content\[/quote]";
        }
        elsif ($head->as_text eq "Code:") {
            my $content= to_bbcode($el->look_down("_tag", "td", "class", "code"));
            return "[code]$content\[/code]";
        }
        else {
            #print "WARNING: unrecognised b in table @", $head->address, "\n";
            return "";
        }

    }
    elsif ($el->tag eq "span" && $el->attr("style") && $el->attr("style") =~ /color:#\w+/) {
        #google highlighted 
        #print ".....................google-highlite\n";
        $open="";
        $close="";
    }
    elsif ($el->tag eq "b") {
        #google highlighted 
        $open="";
        $close="";
    }
    else {

        # center .... inline image.
        #print "WARNING: ignoring ", $el->tag, " @", $el->address, "\n";
        $open="";
        $close="";
    }
    $bb .= $open;
    for my $topel ($el->content_list) {
        $bb .= to_bbcode($topel);
    }
    $bb .= $close;

    return $bb;
}
sub aequals {
    my ($a, $b)= @_;
    return $a eq $b if (defined $a && defined $b);
}
sub amatches {
    my ($a, $b)= @_;
    return $a =~ $b if (defined $a && defined $b);
}

########################################################
sub read_attachinfo {
    my $fn= shift;
    my $fh= IO::File->new($fn, "r") or die "$fn: $!\n";

    my %attachments;
    while (<$fh>) {
        if (/^INSERT INTO phpbb_attachments_config/) {
            # ignore
        }
        elsif (/^INSERT INTO phpbb_attachments VALUES \((\d+), (\d+), (\d+), (\d+), (\d+)\)/) {
            my ($id, $postid, $privid, $usr1, $usr2)= ($1, $2, $3, $4, $5);
            if (!exists $attachments{$id}) {
                $attachments{$id}= {};
            }
            my $att= $attachments{$id};
            $att->{id}= $id;
            $att->{postid}= $postid;
            $att->{privid}= $privid;
            $att->{usr1}= $usr1;
            $att->{usr2}= $usr2;
        }
        elsif (/^INSERT INTO phpbb_attachments_desc VALUES \((\d+), '(.*?)', '(.*?)', (\d+), '(.*?)', '(.*?)', '(.*?)', (\d+), (\d+), (\d+)\)/) {
            my ($id, $physname, $realname, $dncount, $comment, $extension, $mimetype, $filesize, $filetime, $thumbnail)
                = ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10);
            if (!exists $attachments{$id}) {
                $attachments{$id}= {};
            }
            my $att= $attachments{$id};
            $att->{id}= $id;
            $att->{physname}= $physname;
            $att->{realname}= $realname;
            $att->{dncount}= $dncount;
            $att->{comment}= $comment;
            $att->{extension}= $extension;
            $att->{mimetype}= $mimetype;
            $att->{filesize}= $filesize;
            $att->{filetime}= $filetime;
            $att->{thumbnail}= $thumbnail;

        }
        elsif (/^INSERT INTO phpbb_attach/) {
            warn "WARING: missed attachment insert: $_\n";
        }
    }
    $fh->close();

    return \%attachments;
}

########################################################
package DB;

########################################################
# database wrapper object
#use DBI;

sub new {
    my ($class, $dbname, $username, $password)= @_;
    my $self= bless { cmds=>{}, curid=>0, }, $class;
    $self->{db}= DBI->connect($dbname, $username, $password) or die "dbi connect $dbname\n";

    return $self;
}
sub close {
    my ($self)= @_;
    $self->{db}->disconnect;
}
sub execchange {
    my ($self, $sql, @params)= @_;
    print "change: '$sql', ", join(", ", map { defined $_ ? "'$_'" : "NULL" } @params), "\n";

    my $cmd= $self->getcmd($sql);
    $cmd->execute(@params);

    my ($id)= $self->execsql("select last_insert_id()") if ($sql =~ /^\s*insert\s+into/i);

    print "new id: $id\n";

    return $id;
}
sub getcmd {
    my ($self, $sql)= @_;
    if (!exists $self->{cmds}{$sql}) {
        $self->{cmds}{$sql}= $self->{db}->prepare($sql);
    }
    return $self->{cmds}{$sql};
}
sub execsql {
    my ($self, $sql, @params)= @_;

    my $cmd= $self->getcmd($sql);
    $cmd->execute(@params);
    my @row= $self->{cmds}{$sql}->fetchrow_array();
    if (@row)  {
        while ($self->{cmds}{$sql}->fetchrow_array()) {
        }
    }
    return @row;
}
########################################################
package DBTST;

########################################################
# dummy database wrapper object

sub new {
    my ($class)= @_;
    my $self= bless { cmds=>{}, curid=>0, }, $class;

    return $self;
}
sub close {
    my ($self)= @_;
}
sub execchange {
    my ($self, $sql, @params)= @_;
    print "change '$sql', ", join(", ", map { defined $_ ? "'$_'" : "NULL" } @params), "\n";
    return ++$self->{curid};
}
sub execsql {
    my ($self, $sql, @params)= @_;

    print "select '$sql', ", join(", ", map { defined $_ ? "'$_'" : "NULL" } @params), "\n";

    return undef;
}
########################################################
package DB_RO;
#use DBI;
########################################################
# dummy database wrapper object

sub new {
    my ($class, $dbname, $username, $password)= @_;
    my $self= bless { cmds=>{}, curid=>0, }, $class;
    $self->{db}= DBI->connect($dbname, $username, $password) or die "dbi connect $dbname\n";
    return $self;
}
sub close {
    my ($self)= @_;
    $self->{db}->disconnect;
}
sub execchange {
    my ($self, $sql, @params)= @_;
    print "change: '$sql', ", join(", ", map { defined $_ ? "'$_'" : "NULL" } @params), "\n";
    return ++$self->{curid};
}
sub getcmd {
    my ($self, $sql)= @_;

    if (!exists $self->{cmds}{$sql}) {
        $self->{cmds}{$sql}= $self->{db}->prepare($sql);
    }
    return $self->{cmds}{$sql};
}
sub execsql {
    my ($self, $sql, @params)= @_;

    print "select '$sql', ", join(", ", map { defined $_ ? "'$_'" : "NULL" } @params), "\n";

    my $cmd= $self->getcmd($sql);
    $cmd->execute(@params);
    my @row= $self->{cmds}{$sql}->fetchrow_array();
    if (@row)  {
        while ($self->{cmds}{$sql}->fetchrow_array()) {
        }
    }
    print "result: @row\n";
    return @row;
}

