#!/usr/bin/perl

# This script finds all the 'sciency' exposures for a given night ($date).
# It then traces through each of the processing steps.
#
# This is useful for verifying that we are processing all that we should be.
#
# It's a very ugly script.




use strict;
#use warnings;
use DBI;
use constant DB_SOCKET => '/var/run/mysqld/mysql.sock';
use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );

my $verbose;
my $date;
my $dbname = 'gpc1';
my $dbserver = 'ippdb01';
my $dbuser = 'ipp';
my $dbpass = 'ipp';




GetOptions(
           'start|s=s'    => \$date,
           'dbname|d'     => \$dbname,
           'verbose|v'    => \$verbose,
) or pod2usage( 0 );
pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;

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

my $db = DBI->connect(
    "DBI:mysql:database=${dbname};host=${dbserver};mysql_socket=" .
    DB_SOCKET(),${dbuser},${dbpass},
    {
	RaiseError =>, AutoCommit => 1}
    ) or die "Unable to connect to database $DBI::errstr\n";



my $warpq = "select exp_name, filter, comment, rawExp.state, chipRun.state, fakeRun.state, camRun.state, warpRun.state, count(skycell_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join warpImfile using (warp_id) where dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name;";
print "warp\n";
print `date`;
my $query = $db->selectall_arrayref($warpq) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my $stackq = "select exp_name, comment, rawExp.state, count(stack_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join stackInputSkyfile using (warp_id) left join stackRun using (stack_id) where dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name;";


print "stack\n";
print `date`;
my $squery = $db->selectall_arrayref($stackq) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %stack_hash =();
foreach my $item (@{$squery}) {
    $stack_hash{${$item}[0]} = ${$item}[3];
    #print ${$item};
}




my $diffq = "select exp_name, count(warp1), diff_mode from rawExp, chipRun, camRun, fakeRun, warpRun, diffInputSkyfile, diffRun  where rawExp.exp_id = chipRun.exp_id and chipRun.chip_id = camRun.chip_id and camRun.cam_id = fakeRun.cam_id and fakeRun.fake_id = warpRun.fake_id and diffInputSkyfile.warp1 = warpRun.warp_id and diffInputSkyfile.diff_id = diffRun.diff_id and dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name, diff_mode";



print "diff1\n";
print `date`;
my $dquery = $db->selectall_arrayref($diffq) || 
    die "Unable to execute SQL: $DBI::errstr\n";
#print 
my %diff_hash =();
my %diff_hashws= ();
print `date`;
foreach my $item (@{$dquery}) {
    if (${$item}[2] == 2) {

	$diff_hashws{${$item}[0]} = ${$item}[1];

    } else {
    
    $diff_hash{${$item}[0]} = ${$item}[1];
    }

   #print ${$item};
}

#my $diffq2 = "select exp_name, comment, rawExp.state, count(diff_id), diff_mode from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) join diffInputSkyfile on  warp_id = warp2 left join diffRun using (diff_id) where dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name;";
my $diffq2 = "select exp_name, count(warp2), diff_mode from rawExp, chipRun, camRun, fakeRun, warpRun, diffInputSkyfile, diffRun  where rawExp.exp_id = chipRun.exp_id and chipRun.chip_id = camRun.chip_id and camRun.cam_id = fakeRun.cam_id and fakeRun.fake_id = warpRun.fake_id and diffInputSkyfile.warp2 = warpRun.warp_id and diffInputSkyfile.diff_id = diffRun.diff_id and dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name, diff_mode";




#print $diffq2;
print "diff2\n";
print `date`;
my $dquery2 = $db->selectall_arrayref($diffq2) || 
    die "Unable to execute SQL: $DBI::errstr\n";
#print 
my %diff_hash2 =();
print `date`;

foreach my $item (@{$dquery2}) {
    $diff_hash2{${$item}[0]} = ${$item}[1];
    #print ${$item};
}

print "diff3\n";
print `date`;
my $s1diffq = "select exp_name, comment, rawExp.state, count(stack1), diff_mode from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join stackInputSkyfile using (warp_id) left join diffInputSkyfile on stack_id = stack1 left join diffRun using (diff_id) where dateobs like '$date\%' and exp_type = 'OBJECT' group by exp_name, diff_mode;";

#print $s1diffq;

my $s1dquery = $db->selectall_arrayref($s1diffq) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;

#print 
my %s1d_hash =();
foreach my $item (@{$s1dquery}) {
    if (${$item}[3] > 0) {
    $s1d_hash{${$item}[0]} = ${$item}[3];
    }
    #print ${$item};
}


print `date`;

my $dist1q = "select exp_name, comment, rawExp.state, count(stage_id) from rawExp left join chipRun using (exp_id) left join distRun on chip_id = stage_id and stage = 'chip' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist1 = $db->selectall_arrayref($dist1q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;

my %dist1_hash= ();
foreach my $item (@{$dist1}) {
 if (${$item}[3] > 0) {
    $dist1_hash{${$item}[0]} = ${$item}[3];
    }

}




print `date`;

my $dist2q =  "select exp_name, comment, rawExp.state, count(stage_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join distRun on cam_id = stage_id and stage = 'camera' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist2 = $db->selectall_arrayref($dist2q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist2_hash= ();
foreach my $item (@{$dist2}) {
 if (${$item}[3] > 0) {
    $dist2_hash{${$item}[0]} = ${$item}[3];
    }

}


print `date`;
my $dist3q =  "select exp_name, comment, rawExp.state, count(stage_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join distRun on warp_id = stage_id and stage = 'warp' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist3 = $db->selectall_arrayref($dist3q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist3_hash =();
foreach my $item (@{$dist3}) {
 if (${$item}[3] > 0) {
    $dist3_hash{${$item}[0]} = ${$item}[3];
    }

}


print `date`;

my $dist4q =  "select exp_name, comment, rawExp.state, count(stage_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join stackInputSkyfile using (warp_id) left join distRun on stack_id = stage_id and stage = 'stack' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist4 = $db->selectall_arrayref($dist4q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist4_hash= ();
foreach my $item (@{$dist4}) {
 if (${$item}[3] > 0) {
    $dist4_hash{${$item}[0]} = ${$item}[3];
    }

}


print `date`;
my $dist5q =  "select exp_name, comment, rawExp.state, count(diff_id), stack1 from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join diffInputSkyfile on warp_id = warp1 left join distRun on stage_id = diff_id and stage = 'diff' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist5 = $db->selectall_arrayref($dist5q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist5_hash =();
my %dist5_hashws =();
foreach my $item (@{$dist5}) {
    if (${$item}[3] > 0) {
	if (defined($diff_hashws{${$item}[0]})) { 
	    $dist5_hashws{${$item}[0]} = ${$item}[3]; 
	} 
       	else
	{
	    $dist5_hash{${$item}[0]} = ${$item}[3];
 	}
	
    }
}



print `date`;
my $dist6q =  "select exp_name, comment, rawExp.state, count(diff_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join diffInputSkyfile on warp_id = warp2 left join distRun on stage_id = diff_id and stage = 'diff' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist6 = $db->selectall_arrayref($dist6q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist6_hash= ();
foreach my $item (@{$dist6}) {
 if (${$item}[3] > 0) {
    $dist6_hash{${$item}[0]} = ${$item}[3];
    }

}


print `date`;




my $dist7q =  "select exp_name, comment, rawExp.state, count(diff_id) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join fakeRun using (cam_id) left join warpRun using (fake_id) left join stackInputSkyfile using (warp_id) left join diffInputSkyfile on stack_id = stack1 left join distRun on stage_id = diff_id and stage = 'diff' where dateobs like '$date%' and exp_type = 'OBJECT' group by exp_name;";
my $dist7 = $db->selectall_arrayref($dist7q) || 
    die "Unable to execute SQL: $DBI::errstr\n";
print `date`;
my %dist7_hash= ();
foreach my $item (@{$dist7}) {
 if (${$item}[3] > 0) {
    $dist7_hash{${$item}[0]} = ${$item}[3];
    }

}


print "$date\n";

print "|| exp_name || filter || comment || rawExp || chip(#) || cam(#)  || fake || warp (#) || stack (#) || WS (#) || WW1 (#) ||WW2 (#) || SS (#) || \n";

foreach my $item (@{$query}) {

    if (${$item}[2] =~ /calcite/ || ${$item}[2] =~ /focus/ || ${$item}[2] =~/pointing/ || ${$item}[2] =~/stare/) {

    } else {

    print "||${$item}[0] || ${$item}[1]|| ${$item}[2]|| ${$item}[3] ||${$item}[4] ($dist1_hash{${$item}[0]}) || ${$item}[5]($dist2_hash{${$item}[0]}) ||  ${$item}[6] || ${$item}[7] ($dist3_hash{${$item}[0]}) || $stack_hash{${$item}[0]} ($dist4_hash{${$item}[0]})  ||  $diff_hashws{${$item}[0]}($dist5_hashws{${$item}[0]}) || $diff_hash{${$item}[0]}($dist5_hash{${$item}[0]})  ||  $diff_hash2{${$item}[0]}($dist6_hash{${$item}[0]})  || $s1d_hash{${$item}[0]} ($dist7_hash{${$item}[0]}) ||\n";  

    }
}



$db->disconnect();
