#!/usr/bin/perl -w

use strict;

use DBI;
use DBD::SQLite;
use Data::Dumper;
use Getopt::Long;
use LWP::Simple;
use XML::Simple;

$| = 1;

print $DBD::SQLite::VERSION,        qq{\n};
print $DBD::SQLite::sqlite_version, qq{\n};

my $datafile = $0 . q{.sqlite};

if ( scalar grep( /^-/, @ARGV ) ) {
    GetOptions(
        "help|?"     => sub { &help($datafile) },
        "datafile=s" => \$datafile,
    );
}

if ( !-e $datafile ) {
    open( DF, q{>>} . $datafile ) or die(qq{Couldn't open $datafile for append: $!\n});
    close(DF);
}

my $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile}, q{}, q{}, { AutoCommit => 0 } );

test_table_existance($dbh);

# my $cb_xml_url = q{http://www.perlmonks.org/index.pl?node_id=207304};
my $cb_xml_url = q{358181.pl.1132118668.xml};

foreach my $cb_xml_url (@ARGV) {
    next unless ( -f $cb_xml_url );
    print $cb_xml_url, qq{\n};

    # Setup
    my $xs = new XML::Simple;

    my $cb_xml;
    open( DF, $cb_xml_url ) or die($!);
    {
        my @temp = <DF>;
        chomp @temp;
        $cb_xml = join( qq{\n}, @temp );
        $cb_xml =~ s/&([^a])/&amp;$1/g;
        $cb_xml =~ s/<([^CIm?\/])/&lt;$1/g;
        $cb_xml =~ s/<\/([^CIm?])/&lt;\/$1/g;
    }

    # my $cb_xml = get($cb_xml_url) or die(qq{Could not retrieve CB XML ticker: $!\n});
    if ( $cb_xml =~ m/[\x00-\x08\x0b-\x0c\x0e-\x1f]/ ) {
        my @parts = split( //, $cb_xml );
        foreach my $c ( 0 .. $#parts ) {
            if ( $parts[$c] =~ m/[\x00-\x08\x0b-\x0c\x0e-\x1f]/ ) {
                $parts[$c] = sprintf( '<!-- XML disallowed character 0x%x detected -->', ord( $parts[$c] ) );
            }
        }
        $cb_xml = join( '', @parts );
    }

    my $ref = $xs->XMLin( $cb_xml, ForceArray => [q{message}] ) or die(qq{Could not parse CB XML: $!\n});

    # print Data::Dumper->Dump( [ \$ref ], [qw(*ref)] ), "\n";

    my $ior_updateinfo = q{
    INSERT OR REPLACE INTO updateinfo 
        ( updateinfo_id, archived, foruser, foruser_id, gentimeGMT, 
            hard_limit, max_recs, min_poll_seconds, since_id, site, 
            sitename, style, content ) 
        VALUES 
            ( NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
    };
    my $iorus = $dbh->prepare($ior_updateinfo);
    my $iorur = $iorus->execute(
        $ref->{INFO}{archived},         $ref->{INFO}{foruser},    $ref->{INFO}{foruser_id},
        $ref->{INFO}{gentimeGMT},       $ref->{INFO}{hard_limit}, $ref->{INFO}{max_recs},
        $ref->{INFO}{min_poll_seconds}, $ref->{INFO}{since_id},   $ref->{INFO}{site},
        $ref->{INFO}{sitename},         $ref->{INFO}{style},      $ref->{INFO}{content},
        )
        or die( $dbh->errstr );

    my $ior_message = q{
    INSERT OR REPLACE INTO message 
        ( message_id, author, status, time, user_id, content )
        VALUES
            ( ?, ?, ?, ?, ?, ? );
    };
    my $iorms = $dbh->prepare($ior_message);
    foreach my $i ( 0 .. $#{ $ref->{message} } ) {
        my $iormr = $iorms->execute(
            $ref->{message}[$i]{message_id}, $ref->{message}[$i]{author},  $ref->{message}[$i]{status},
            $ref->{message}[$i]{q{time}},    $ref->{message}[$i]{user_id}, $ref->{message}[$i]{content},
            )
            or die( $dbh->errstr );
    }

    $dbh->commit;
}

$dbh->disconnect;

do_maintainance($datafile);

#
# Subroutines
#
sub help {
    my ($datafile) = @_;
    printf <<HELP_TEXT, $datafile;

Usage:  $0 [-help| -?] [-datafile filename] 

    -help | -?      - display this help message and exit.

    -datafile filename  - name of SQLite database file to use 
                            (created if does not exist, default: %s).

HELP_TEXT
    exit(0);
}

sub do_maintainance {
    my ($datafile) = @_;

    my @maintainence_query = (
        q{
        VACUUM updateinfo;
    },
        q{
        VACUUM message;
    },
    );

    foreach my $i ( 0 .. $#maintainence_query ) {
        my $maintenance = $maintainence_query[$i];

        my $pre_fs   = ( stat($datafile) )[7];
        my $pre_time = time;
        $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile}, q{}, q{} );
        $dbh->do($maintenance);
        $dbh->disconnect;
        my $post_time = time;
        my $post_fs   = ( stat($datafile) )[7];

        if ( $pre_fs != $post_fs ) {
            printf
                qq{Vacuuming of database table %d lead to a change of %d bytes in size (from %d to %d bytes, in %d seconds)\n},
                $i, ( $post_fs - $pre_fs ), $pre_fs, $post_fs, ( $post_time - $pre_time );
        }
    }
}

sub test_table_existance {
    {
        my ($dbh) = @_;

        my $tables_query = q{
SELECT COUNT(name) FROM 
( 
    SELECT * FROM sqlite_master UNION ALL 
    SELECT * FROM sqlite_temp_master
) WHERE type='table'; 
};
        my $tqs     = $dbh->prepare($tables_query);
        my $tqr     = $tqs->execute;
        my @row_tqr = $tqs->fetchrow_array;
        $tqs->finish;

        if ( !$row_tqr[0] ) {

            my @creation_query = (
                q{
                CREATE TABLE updateinfo (
                    updateinfo_id INTEGER,
                    archived TEXT,
                    foruser TEXT,
                    foruser_id INTEGER,
                    gentimeGMT TEXT,
                    hard_limit INTEGER,
                    max_recs INTEGER,
                    min_poll_seconds INTEGER,
                    since_id INTEGER,
                    site TEXT,
                    sitename TEXT,
                    style TEXT,
                    content TEXT,
                    UNIQUE(gentimeGMT, updateinfo_id)
                );
},
                q{
                CREATE TABLE message (
                    message_id INTEGER,
                    author TEXT,
                    status TEXT,
                    time INTEGER,
                    user_id INTEGER,
                    content TEXT,
                    UNIQUE(message_id)
                );
},
                q{
                CREATE INDEX ui_idx ON updateinfo ( updateinfo_id );
},
                q{
                CREATE INDEX m_idx ON message ( message_id );
},
                q{
                CREATE INDEX m_idx_2 ON message ( author, status, user_id, time, content );
},
            );

            foreach my $i ( 0 .. $#creation_query ) {
                my $cr = $dbh->do( $creation_query[$i] );
            }

            $dbh->commit;
        }
    }
}
__END__