#!/bin/env perl

use strict;
use warnings;

use DBI;
use PS::IPP::Config 1.01 qw( :standard );
use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );
use File::Temp qw(tempfile);

my $limit = 20;
my $running;
my $req_faulted;
my $job_faulted;
my $dbname;
my $dbserver;
my $dbuser;
my $dbpassword;
my $use_mysql = 1;
my $verbose;

GetOptions(
    'running|r',     \$running,
    'req_faulted',   \$req_faulted,
    'job_faulted',   \$job_faulted,
    'limit|l=i',     \$limit,
    'dbname=s',      \$dbname,
    'verbose|v',     \$verbose,
) or pod2usage (2);

pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;

my $no_args = ! (defined $running or defined $req_faulted or defined $job_faulted );

my $ipprc =  PS::IPP::Config->new();
my $dbh = getDBHandle();

my $select = "SELECT req_id, name, label, reqType, pstampRequest.state, pstampRequest.fault, count(job_id) AS numJobs, timestamp";

$select .= ", outdir" if ($verbose);
my $from = " FROM pstampRequest LEFT JOIN pstampJob USING(req_id)";
my $where = " WHERE (pstampRequest.state NOT like '%cleaned')";

my $sql = $select . $from . $where;

if ($no_args and $limit) {
    # use mysql to get the last_req_id
    # this works but it prints out the @last_req_id value which is distracting. Use dbi for the query
    my $stmt = $dbh->prepare("SELECT req_id FROM pstampRequest ORDER BY req_id DESC LIMIT 1");
    $stmt->execute();
    my $row = $stmt->fetchrow_hashref();
    die "failed to get last req_id\n"  if !$row;
    my $last_req_id = $row->{req_id};
    die "failed to get last req_id\n"  if !$last_req_id;

    my $first_req_id_to_select = $last_req_id - $limit + 1;
    $sql .= " AND (req_id >= $first_req_id_to_select)";
}
if ($running) {
    $sql .= " AND (pstampRequest.state = 'new' OR pstampRequest.state = 'run')"
}
if ($req_faulted) {
    $sql .= " AND (pstampRequest.fault > 0)";
}
if ($job_faulted) {
    $sql .= " AND (pstampJob.fault > 0 AND pstampJob.fault < 10)";
}
$sql .= " GROUP BY req_id";

if ($limit and !$no_args) {
    $sql .= " LIMIT $limit";
}

if ($use_mysql) {
#    print STDERR "$sql\n";
    my @args = ( "--table", "--user=$dbuser",  "--host=$dbserver", "--password=$dbpassword", $dbname);

    push @args, '--verbose' if $verbose;

    open my $mysql, "|-", 'mysql', @args or die "failed to run mysql\n";
    print $mysql "$sql;\n";
    close $mysql;

} else {
        # I thought that I wanted custom formatting but in the end I figured I'd just use mysql

        my $stmt = $dbh->prepare($sql);
        $stmt->execute();

        my $head = "| req_id | name                                                            | label           | reqType   | state | fault  |  numJobs   | outdir";
        my $head2= "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------";
        #           |   5499 | cclin_20100522T011813                                           | WEB.UP          | pstamp    | stop  |      0 |          1 |/data/ippdb02.0/pstamp/work/20100521/5517"

        my $printhead = 1;
        my $count = 0;
        while (  my $row = $stmt->fetchrow_hashref() ) {
            $count++;
            if ($printhead) {
                print "$head\n";
                print "$head2\n";
                $printhead = 0;
            }
            printf "|%7d | %-64s| %-16s| %-10s| %-6s| %6d | %10d | %-s |\n",
                $row->{req_id}, be_safe($row->{name}), $row->{label}, be_safe($row->{reqType}), $row->{state}, $row->{fault},
                $row->{numJobs}, $row->{outdir};
        }

        if (!$count) {
            print "no requests found\n";
        } else {
            print "$head2\n";
        }

}

exit 0;

sub be_safe {
    my $str = shift;

    return $str ? $str : 'null';
}


sub getDBHandle {
    $dbserver = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBSERVER");
    $dbuser = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBUSER");
    $dbpassword = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBPASSWORD");
    if (!$dbname) {
        $dbname = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBNAME");
    }

    die "database configuration set up" unless defined($dbserver) and defined($dbuser)
        and defined($dbpassword) and defined($dbname);

    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";

    my $dbh = DBI->connect($dsn, $dbuser, $dbpassword) 
        or die "Cannot connect to database.\n";

    return $dbh;
}


