#!/usr/bin/perl

use strict;
use warnings;
use HTML::TableExtract;
use LWP::Simple;
use DBI;

my $db_file = "best_nodes";
my $pm_site = "http://perlmonks.org/index.pl?node_id=%d";

my $make_table = ! -f $db_file;

my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file", "", "")
    or die "Can't connect to db: $DBI::errstr";

$dbh->do( qq[
  create table nodes (
    id int unique,
    title varchar(255),
    auth_id int,
    author varchar(255),
    rep int
  )
]) if $make_table;

my $html = get( sprintf $pm_site, 328478 );
my $te   = HTML::TableExtract->new(
               headers => [ qw/Node Author Rep/ ],
               keep_html => 1
           );

$te->parse($html);

foreach my $row ($te->rows) {
    my ($node, $author, $rep) = @$row;
    
    my ($id)      = $node =~ /\?node_id=(\d+)/;
    my ($auth_id) = $author =~ /\?node_id=(\d+)/;
       ($rep)     = $rep =~ /(\d+)/;    
    my ($title)   = $node =~ m{>(.+?)</a>$};
       ($author)  = $author =~ m{>(.+?)</a>$};
    
    $dbh->do("delete from nodes where id=?", undef, $id);
    $dbh->do("insert into nodes values (?,?,?,?,?)", undef,
                $id, $title, $auth_id, $author, $rep);
}

my $sth = $dbh->prepare( qq[
  select id,title,auth_id,author,rep from nodes order by rep desc
]);
$sth->execute;

open my $fh, ">bestnodes.html" or die;

print $fh "<table>\n";
while (my ($id, $title, $auth_id, $author, $rep) = $sth->fetchrow_array) {
    $id      = sprintf $pm_site, $id;
    $auth_id = sprintf $pm_site, $auth_id;
    
    print $fh qq[
      <tr><td><a href="$id">$title</a></td>
          <td><a href="$auth_id">$author</a></td>
          <td>$rep</td></tr>
    ];
}
print $fh "</table>\n";