#!/usr/bin/perl
require 5.002;

use DBI;

$|++;

use vars qw/ $query %cache %dates $res $sth /;

my $DATABASE = "";
my $HOSTNAME = undef;
my $USER     = "";
my $PASSWORD = "";

my %minvalue = ( syslog => -50, reported => -150 );

# Setup the DB connection.
my $dsn = "DBI:mysql:database=$DATABASE";
if ( defined $HOSTNAME ) {
    $dsn .= ";host=$HOSTNAME";
}
my $dbh = DBI->connect( $dsn, $USER, $PASSWORD, { RaiseError=>0, PrintError=>0 } );
if ( !defined($dbh) ) {
    die "DBI->connect failed: $DBI::errstr";
}

%files = (	month => q/1 month/,
		week => q/7 day/,
		day => q/1 day/,
	 );

my $table = "reported";
if ( @ARGV ) {
	$table = shift @ARGV;
	die "Illegal table name!\n" if ( $table !~ /^(syslog|reported)$/ );
}

while ( ($k,$v) = each %files ) {
	$query = "SELECT DATE_SUB(MAX(date),INTERVAL $v),UNIX_TIMESTAMP(MAX(date)) FROM $table";
	( $res, $sth ) = runsql( $dbh, $query );
	my($mdate,$mdate_ts) = $sth->fetchrow_array;

	$query = "SELECT DATE_FORMAT(date,'%m/%d/%Y %H:%i') as date,value FROM $table WHERE date >= ? AND value > ? ORDER BY date ASC";
	( $res, $sth ) = runsql( $dbh, $query, $mdate, (exists $minvalue{$table}?$minvalue{$table}:-10000) );

	open(OUT,">tmp/$k") || die "Can't write to tmp/$k:$!";

	while ( my $ref = $sth->fetchrow_hashref ) {
    		print OUT join(" ",$ref->{date},$ref->{value}),"\n";
	}

	close(OUT);

	# Set the [am]times appropriately.
	utime time,$mdate_ts,"tmp/$k";
}

# Clean up the sth cache
endsql();

# Disconnect and exit.
$dbh->disconnect();
exit 0;

{
    my %queries = ();

    sub runsql ($$@) {
        my ( $dbh, $sql, @bind_values ) = @_;

        if ( !defined( $queries{$sql} ) ) {
            $queries{$sql} = $dbh->prepare($sql);
        }

        my $sth = $queries{$sql};
        if ( !$sth ) {
            die "Error:" . $dbh->errstr . "\n";
        }

        my $res = $sth->execute(@bind_values);
        if ( !$res ) {
            die "Error:" . $sth->errstr . "\n";
        }

        return ( $res, $sth );
    }

    sub endsql () {
        while ( my ( $k, $v ) = each %queries ) {
            $v->finish;
        }
    }
}