#!/usr/bin/env perl
# 
# query the summit data store and the gpc1 database to get information as to
# the status of summit copy and registration for "today's" exposures.
# Optionally start and end dates may be applied.
#

use strict;
use warnings;

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

my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
my $siteConfig = $ipprc->{_siteConfig};

my $startDate;
my $endDate;
my $verbose;
my $dbname;

# this program must be in the path. There is one in the tools directory
my $croot = 'croot';

GetOptions(
           'start|s=s'    => \$startDate,
           'end|e=s'      => \$endDate,
           'dbname|d=s'   => \$dbname,
           'camera=s'     => \$dbname,
           'verbose|v'    => \$verbose,
) or pod2usage( 0 );

pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
#pod2usage( -msg => "Required options: --start",
#           -exitval => 3)
#    unless defined $startDate;

if (!$dbname) {
    $dbname = "gpc1";
}

my $dbh  = getDBHandle($ipprc, $dbname);

# if startDate not provided use today
if (!$startDate) {
    my ($sec, $min, $hour, $mday, $month, $year) = gmtime;
    $year += 1900;
    $month += 1;
    $startDate = sprintf "%4d-%02d-%02d", $year, $month, $mday;
}


# find all exposures in summitExp
my $query = "SELECT exp_name, summitExp.dateobs AS registered, rawExp.dateobs, summitExp.imfiles,
        summitExp.fault AS summit_fault, 
        pzDownloadExp.state AS download_state, count(pzDownloadImfile.class_id) AS download_count,
        newExp.state AS newExp_state, newExp.exp_id,
        rawExp.state as rawExp_state
        FROM summitExp LEFT JOIN pzDownloadExp USING(exp_name) 
            LEFT JOIN pzDownloadImfile USING(exp_name) 
            LEFT JOIN newExp ON exp_name = tmp_exp_name 
            LEFT JOIN rawExp USING(exp_id, exp_name) ";

if ($startDate) {
    $query .= "  WHERE date(summitExp.dateobs) >= '$startDate'";
    $query .= " AND date(summitExp.dateobs) <= '$endDate'" if $endDate;
}

$query .= " GROUP BY exp_name ORDER BY summitExp.dateobs";

print "Summit Copy and Registration Status\n\n";
my $scode = system "$croot --camera $dbname";
if ($scode) {
    print STDERR "croot failed with $scode\n";
    exit 1;
}
print "\n";

my $se_stmt = $dbh->prepare($query);
$se_stmt->execute();

my @summit_exps;
my @summit_faults;
my @summit_lost;
my @download_faults;
my @register_faults;

my $last_exp_name;
my $last_exp_id;
my $last_new_exp_name = "";
my $last_new_exp_id = "";
my $first_exp_id;
my $first_exp_name;
my $last_registered_exp_id = "";
my $last_registered_exp_name = "";
while (my $se_ref = $se_stmt->fetchrow_hashref()) {
    push @summit_exps, $se_ref;

    my $exp_name = $se_ref->{exp_name};
    my $summit_fault = $se_ref->{summit_fault};
    my $download_state = $se_ref->{download_state};
    my $new_state = $se_ref->{newExp_state};
    my $exp_id = $se_ref->{exp_id};
    my $imfiles = $se_ref->{imfiles};
    my $dateobs = $se_ref->{dateobs};
    my $raw_state = $se_ref->{rawExp_state};
    if ($summit_fault == 1000) {
        push @summit_lost, $se_ref;
    } elsif ($summit_fault) {
        push @summit_faults, $se_ref;
    } elsif ( !$download_state or $download_state eq 'run' ) {
        push @download_faults, $se_ref;
    } elsif ( !$new_state or $new_state eq 'run' ) {
        push @register_faults, $se_ref;
    }
    if (!$first_exp_name) {
        $first_exp_id = $exp_id ? $exp_id : "null";
        $first_exp_name = $exp_name;
    }
    if ($exp_name) {
        $last_exp_name = $exp_name;
        $last_exp_id = $exp_id ? $exp_id : "not done";
        if ($last_exp_id ne "not done") {
            $last_new_exp_id = $exp_id;
            $last_new_exp_name = $exp_name;
        }
        if ($raw_state and $raw_state eq 'full') {
            $last_registered_exp_id = $exp_id;
            $last_registered_exp_name = $exp_name;
        }
    }
}

my $numSummitExp = scalar @summit_exps;
my $numSummitLost = scalar @summit_lost;
my $numSummitFaults = scalar @summit_faults;
my $numDownloadFaults = scalar @download_faults;
my $numRegisterFaults = scalar @register_faults;

print "$numSummitExp summit exposures since $startDate";
print " up to $endDate" if $endDate;
print "\n";

exit 0 if $numSummitExp == 0;

if ($numSummitLost) {
  print "$numSummitLost exposures corrupted at the summit\n";
  if ($verbose) {
    print  "  exp_name         registered      fault\n";
          # c4951g0003d   2009-04-30 12:39:34   110
    foreach my $s (@summit_lost) {
        printf "%-12s %20s %5d\n", $s->{exp_name}, $s->{registered}, $s->{summit_lost};
    }
  }
}
if ($numSummitFaults) {
  print "$numSummitFaults summit faults\n";
  if ($verbose) {
    print  "  exp_name         registered      fault\n";
          # c4951g0003d   2009-04-30 12:39:34   110
    foreach my $s (@summit_faults) {
        printf "%-12s %20s %5d\n", $s->{exp_name}, $s->{registered}, $s->{summit_fault};
    }
  }
}
if ($numDownloadFaults) {
  print "\n$numDownloadFaults incomplete downloads\n";
  if ($verbose) {
    print " exp_name    summit_imfiles downloaded imfiles  faulted\n";
    my $stmt = $dbh->prepare("SELECT exp_name, count(fault) as fault_count  FROM pzDownloadImfile WHERE exp_name = ? AND fault > 0");
    foreach my $p (@download_faults) {
        $stmt->execute($p->{exp_name});
        my $ref = $stmt->fetchrow_hashref();
        printf "%-12s     %3d          %3d                %3d\n", 
            $p->{exp_name} ? $p->{exp_name} : "null", 
            $p->{imfiles} ? $p->{imfiles} : 0,
            $p->{download_count} ? $p->{download_count} : 0,
            $ref->{fault_count} ? $ref->{fault_count} : 0;
    }
  }
}
if ($numRegisterFaults) {
 print "\n$numRegisterFaults exposures copied but not registered\n";
 if ($verbose) {
    my $stmt = $dbh->prepare("SELECT exp_id, count(class_id) as fault_count FROM rawImfile where exp_name = ? and fault > 0");
    print " exp_name           exp_id  fault_count\n";
         # c4895g0607o         57173       1

    foreach my $r (@register_faults) {
        my $exp_name = $r->{exp_name};
        $stmt->execute($exp_name);
        my $ref = $stmt->fetchrow_hashref();
        my $exp_id = $ref->{exp_id};
        $exp_id = "null" if !defined $exp_id;
        
        my $fault_count = $ref->{fault_count};
        $fault_count = "null" if !defined $fault_count;
        printf "%-12s %12s     %3d\n", $exp_name, $exp_id, $fault_count;
    }
  }
}

print "\n";

print "first exposure:  $first_exp_name $first_exp_id\n";
print "last registered: $last_registered_exp_name $last_registered_exp_id\n" 
            if $last_registered_exp_id ne $last_exp_id;
print "last copied:     $last_new_exp_name $last_new_exp_id\n"
            if $last_new_exp_id ne $last_exp_id;
print "last exposure:   $last_exp_name $last_exp_id\n";

exit 0;


sub getDBHandle {
    my $ipprc = shift;
    my $dbname = shift;
    if (!$dbname) {
        $dbname = metadataLookupStr($siteConfig, 'DBNAME');
        if (($dbname eq 'XXX') or ($dbname eq "NONE")) {
            $dbname = 'gpc1';
        }
    }
    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;


    die "database enviornment not set up" unless defined($dbserver) and defined($dbuser)
        and defined($dbpass) and defined($dbname);

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

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

    return $dbh;
}
