#!/usr/bin/perl -w
# 
## checks NEWZNAB mysql DB to make sure everything is current
# Parts are current
# Releases are current
# PostProcessing is not behind
# NFO post processing is not behind
#
# rob@rarforge.com
#

use strict;
use DBI;
#use Data::Dumper;

## Comment the next two lines for production
print "Comment me out when you update the variables below in $0\n";
exit(2);

## Update these Variables
my $user = 'USER?';
my $pass = 'PASSWORD?';
my $db   = 'newznab';
my $host = 'localhost';
my $port = 3306;

my $warn = {'releases' => 60*15,       # seconds old (15 minutes)
            'parts' => 60*15,          # seconds old (15 minutes)
            'releasenfoID' => '500',   # >= total count
            'postproc' => '1000',      # >= total count
};

my $crit = {'releases' => 60*30,      # seconds old (30 minutes)
            'parts' => 60*30,         # seconds old (30 minutes)
            'releasenfoID' => '1000',  # >= total count 
            'postproc' => '3000',      # >= total count
};
## End Config Variables

##############################################

my $dbh = DBI->connect("DBI:mysql:dbname=$db;host=$host;port=$port",$user,$pass,);
my %codes= ('0'=>'OK',
            '1'=>'WARN',
            '2'=>'CRIT',
    );
my ($c,$w,$type,$sql,$sth);
my $info  =();
my @row;

###############################################
## check release age
$type = 'releases';
$c = $crit->{$type};
$w = $warn->{$type};

$sql = "select unix_timestamp(adddate),adddate from releases order by adddate desc limit 1";
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh- >errstr\n";
$sth->execute();
@row = $sth->fetchrow_array;
if ($row[0] && $row[1]) {
    my ($epoch,$human) = @row;
    my $diff = time()-$epoch;
    $info->{$type}->{'value'} = $human;
    $info->{$type}->{'code'} = 0;
    $info->{$type}->{'raw_value'} = $diff;
    $info->{$type}->{'warn'} = $w;
    $info->{$type}->{'crit'} = $c;
    if ($diff > $c)    { $info->{$type}->{'code'} = 2;    } 
    elsif ($diff > $w) { $info->{$type}->{'code'} = 1;    } 
}

###############################################
## check release age
$type = 'parts';
$c = $crit->{$type};
$w = $warn->{$type};


$sql = "select unix_timestamp(dateadded),dateadded from parts order by dateadded desc limit 1";
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh- >errstr\n";
$sth->execute();
@row = $sth->fetchrow_array;
if ($row[0] && $row[1]) {
    my ($epoch,$human) = @row;
    my $diff = time()-$epoch;
    $info->{$type}->{'value'} = $human;
    $info->{$type}->{'code'} = 0;
    $info->{$type}->{'raw_value'} = $diff;
    $info->{$type}->{'warn'} = $w;
    $info->{$type}->{'crit'} = $c;
    if ($diff > $c)    { $info->{$type}->{'code'} = 2;    } 
    elsif ($diff > $w) { $info->{$type}->{'code'} = 1;    } 
}

## check release age
$type = 'releasenfoID';
$c = $crit->{$type};
$w = $warn->{$type};


$sql = "select count(*) as total from releases  where releasenfoID = 0";
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh- >errstr\n";
$sth->execute();
@row = $sth->fetchrow_array;
if ($row[0] > -1) {
    my ($total) = @row;
    $info->{$type}->{'value'} = $total;
    $info->{$type}->{'code'} = 0;
    $info->{$type}->{'raw_value'} = $total;
    $info->{$type}->{'warn'} = $w;
    $info->{$type}->{'crit'} = $c;
    if ($total > $c)    { $info->{$type}->{'code'} = 2;    } 
    elsif ($total > $w) { $info->{$type}->{'code'} = 1;    } 
}


## check release age
$type = 'postproc';
$c = $crit->{$type};
$w = $warn->{$type};

$sql = "select count(*) from releases r left join category c on c.ID = r.categoryID where (r.passwordstatus between 1 and -1) or (r.haspreview = -1 and c.disablepreview = 0)";
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh- >errstr\n";
$sth->execute();
@row = $sth->fetchrow_array;
if ($row[0] > -1) {
    my ($total) = @row;
    $info->{$type}->{'value'} = $total;
    $info->{$type}->{'code'} = 0;
    $info->{$type}->{'raw_value'} = $total;
    $info->{$type}->{'warn'} = $w;
    $info->{$type}->{'crit'} = $c;
    if ($total > $c)    { $info->{$type}->{'code'} = 2;    } 
    elsif ($total > $w) { $info->{$type}->{'code'} = 1;    } 
}

my $exit_code = 0;
my $errors;
my $msg;
foreach my $k ( keys %{$info}) {
    if ($info->{$k}->{'code'} > 0) {
        $errors .= "[ $k " . $codes{$info->{$k}->{'code'}} . ': ' . $info->{$k}->{'value'} . ' ] ';
    } else {
        $msg .= "[ $k " . $codes{$info->{$k}->{'code'}} . ': ' . $info->{$k}->{'value'} . ' ] ';
    }
    $exit_code =  $info->{$k}->{'code'} if $info->{$k}->{'code'} > $exit_code;    
}

print $codes{$exit_code} . ': ';

print $errors if $errors;
print $msg if $msg;
print "\n";
exit ($exit_code);
