#!/usr/bin/perl
use strict;
use warnings;

use Date::Calc 'Day_of_Week';
use DBI;

my %holiday = (
    '19990101' => 'NEW YEARS',
    '20000101' => 'NEW YEARS',
    '20010101' => 'NEW YEARS',
    '20020101' => 'NEW YEARS',
    '20030101' => 'NEW YEARS',
    '20040101' => 'NEW YEARS',
    '19990118' => 'MLK BDAY',
    '20000117' => 'MLK BDAY',
    '20010115' => 'MLK BDAY',
    '20020121' => 'MLK BDAY',
    '20030120' => 'MLK BDAY',
    '20040119' => 'MLK BDAY',
    '19990214' => 'VALENTINES DAY',
    '20000214' => 'VALENTINES DAY',
    '20010214' => 'VALENTINES DAY',
    '20020214' => 'VALENTINES DAY',
    '20030214' => 'VALENTINES DAY',
    '20040214' => 'VALENTINES DAY',
    '19990215' => 'WASHINGTONS BDAY',
    '20000221' => 'WASHINGTONS BDAY',
    '20010219' => 'WASHINGTONS BDAY',
    '20020218' => 'WASHINGTONS BDAY',
    '20030217' => 'WASHINGTONS BDAY',
    '20040216' => 'WASHINGTONS BDAY',
    '19990531' => 'MEMORIAL DAY',
    '20000529' => 'MEMORIAL DAY',
    '20010528' => 'MEMORIAL DAY',
    '20020527' => 'MEMORIAL DAY',
    '20030526' => 'MEMORIAL DAY',
    '20040531' => 'MEMORIAL DAY',
    '19990704' => 'INDEPENDENCE DAY',
    '19990705' => 'INDEPENDENCE DAY (OBSERVED)',
    '20000704' => 'INDEPENDENCE DAY',
    '20010704' => 'INDEPENDENCE DAY',
    '20020704' => 'INDEPENDENCE DAY',
    '20030704' => 'INDEPENDENCE DAY',
    '20040704' => 'INDEPENDENCE DAY',
    '20040705' => 'INDEPENDENCE DAY (OBSERVED)',
    '19990906' => 'LABOR DAY',
    '20000904' => 'LABOR DAY',
    '20010903' => 'LABOR DAY',
    '20020902' => 'LABOR DAY',
    '20030901' => 'LABOR DAY',
    '20040906' => 'LABOR DAY',
    '19991011' => 'COLUMBUS DAY',
    '20001009' => 'COLUMBUS DAY',
    '20011008' => 'COLUMBUS DAY',
    '20021014' => 'COLUMBUS DAY',
    '20031013' => 'COLUMBUS DAY',
    '20041011' => 'COLUMBUS DAY',
    '19991111' => 'VETERANS DAY',
    '20001110' => 'VETERANS DAY (OBSERVED)',
    '20001111' => 'VETERANS DAY',
    '20011111' => 'VETERANS DAY',
    '20011112' => 'VETERANS DAY (OBSERVED)',
    '20021111' => 'VETERANS DAY',
    '20031111' => 'VETERANS DAY',
    '20041111' => 'VETERANS DAY',
    '19991125' => 'THANKSGIVING DAY',
    '20001123' => 'THANKSGIVING DAY',
    '20011122' => 'THANKSGIVING DAY',
    '20021128' => 'THANKSGIVING DAY',
    '20031127' => 'THANKSGIVING DAY',
    '20041125' => 'THANKSGIVING DAY',
    '19991224' => 'CHRISTMAS EVE',
    '20001224' => 'CHRISTMAS EVE',
    '20011224' => 'CHRISTMAS EVE',
    '20021224' => 'CHRISTMAS EVE',
    '20031224' => 'CHRISTMAS EVE',
    '20041224' => 'CHRISTMAS EVE',
    '19991225' => 'CHRISTMAS DAY',
    '20001225' => 'CHRISTMAS DAY',
    '20011225' => 'CHRISTMAS DAY',
    '20021225' => 'CHRISTMAS DAY',
    '20031225' => 'CHRISTMAS DAY',
    '20041225' => 'CHRISTMAS DAY',
    '19991231' => 'NEW YEARS EVE',
    '20001231' => 'NEW YEARS EVE',
    '20011231' => 'NEW YEARS EVE',
    '20021231' => 'NEW YEARS EVE',
    '20031231' => 'NEW YEARS EVE',
    '20041231' => 'NEW YEARS EVE',
);

my $xml = $ARGV[0] || 'pm.xml';
open ( INPUT , '<' , $xml ) or die "Unable to open $xml for reading : $!";

my $tab = $ARGV[1] || 'pm.tab';
open ( OUTPUT , '>' , $tab ) or die "Unable to open $tab for writing : $!";
select OUTPUT;

while ( <INPUT> ) {
    last if /^<AUTHOR/;
    if ( /^<NODE createtime="(\d+)" node_id="(\d+)" nodetype="([^"]+)" (root_node="(\d+)")?/ ) {
        my ($year, $month, $day, $hour) = unpack "a4a2a2a2" , $1;
        my $id = $2;
        my $type = $3;
        next if $type eq 'user' || $type eq 'system';
        my $root = $5 || '';
        my $dow = Day_of_Week($year, $month, $day);
        my $hol = $holiday{"$year$month$day"} || '';
        print join "\t" , $id, $year, $month, $day, $hour, $type, $root, $dow, $hol;
        print "\n";
    }
}
close INPUT;
close OUTPUT;
my $srt = $ARGV[2] || 'pm.srt';
system("sort -n $tab > $srt");

my $sql = $ARGV[3] || 'COPYSQL';
open ( INPUT , '<' , $srt ) or die "Unable to open $srt for reading : $!"; 
open ( OUTPUT , '>' , $sql ) or die "Unable to open $sql for writing : $!";
select OUTPUT;

my %node;
while ( <INPUT> ) {
    my @field = split /\t/;
    my $id = shift @field;
    die "NOT 8 FIELDS\n" if @field != 8;    
    if ( $field[4] eq 'note' ) {    
        if ( $node{ $field[5] } ) {
            $field[4] = $node{ $field[5] };
        }
        else {
            $field[4] = 'Poll';
            $field[5] ||= '1';
        }
    }
    else {
        $node{ $id } = $field[4];
    }
    $_ ||= '' for @field;
    print join "\t" , @field;
}
close INPUT;
close OUTPUT;

my $db = 'pmstats.db';
my $dbh = DBI->connect("dbi:SQLite:dbname=$db", { AutoCommit => 0 }) or die $DBI::errstr;

$dbh->do(
    "CREATE TABLE nodes (year, month, day, hour, type, root, dow, holiday)"
) or die $dbh->errstr;
$dbh->do("COPY nodes FROM $sql") or die $dbh->errstr;
$dbh->disconnect;