#!/usr/bin/env perl

use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );

use DBI;
use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock'; # Socket for mysql

# this saves the original arguments for later reporting
my @ARGS = @ARGV;

GetOptions(
    'date=s'            => \$date,
    'dbname|d=s'        => \$dbname,    # Database name
    'verbose'           => \$verbose,   # Print to stdout
    'very-verbose'      => \$verbose2,   # Print to stdout
    ) or pod2usage( 2 );

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

# example if I require some options
# pod2usage( -msg => "Required options: --exp_id --chip_id", -exitval => 3) unless defined $exp_id and defined $chip_id;

$date   = `date --utc --rfc-3339=date` unless defined $date;
chomp $date;
$date=~ s/\//-/g;
$date=~ s/\./-/g;

# database connection information:
$dbhost = 'ippdb08';
$dbname = 'gpc1' unless defined $dbname;
$dbuser = 'ippuser';
$dbpass = 'ippuser';

# dataGroupDate : formatted to match data_group values (YYYYMMDD)
$dataGroupDate = $date;
$dataGroupDate=~ s/-//g;
$dataGroupDate=~ s/\.//g;

print "\n\n\n";
print "--- Report for $dbname : $date ---\n\n";

# set up the database connection:
my $dsn = "DBI:mysql:host=$dbhost;database=$dbname";
my $dbh = DBI->connect( $dsn, $dbuser, $dbpass ) or die "Unable to connect to database: $DBI::errstr";

# find a useful exp_id
$refExpID = 0;
if (1) { 
    # print "Exposures 10 days ago:\n";

    my $query = "SELECT MAX(exp_id)";
    $query .= " FROM rawExp";
    $query .= " WHERE dateobs >= date_sub('$date', interval 20 day)";
    $query .= " AND dateobs <= date_sub('$date', interval 10 day)";

    my $result = &mysql_select ($query);

    @row = $result->fetchrow_array();
    $refExpID = $row[0];
    if ($refExpID eq "") { 
      $refExpID = 0; 
      print "*** WARNING: no data in period 10-20 days before requested date (query will be slower) **\n";
    }
    # print "Reference exp_id: $refExpID\n";
}

# check the status of summit exposures:
if (1) { 
    if ($verbose) { print "Exposures at summit:\n"; }

    $summitSkipped = 0;
    $summitPending = 0;

#   my $query = "SELECT exp_name, exp_type, fault, state, count(*)";
    my $query = "SELECT exp_type, fault, state, count(*)";
    $query .= " FROM summitExp";
    $query .= " LEFT JOIN pzDownloadExp USING (summit_id)";
    $query .= " WHERE dateobs like '$date%'";
    $query .= " AND (state != 'drop' or state IS NULL)";
    $query .= " AND fault != 1042";
    $query .= " GROUP BY exp_type, fault, state";

#    print "$query\n";
#    die;

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "fault", "state", "N(exp)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; }
	if ($row[2] eq "run") {
	    $summitPending += $row[3];
	}
	if ($row[2] eq "") {
	    $summitSkipped += $row[3];
	}
    }
    if ($summitSkipped || $summitPending) { print "*** WARNING: $summitSkipped skipped rawExp, $summitPending incomplete rawExp ***\n\n"; } else { if ($verbose) { print "\n"; }}
}

# check for exposures not sent to chip:
if ($verbose2) {

    my $query = "SELECT summitExp.exp_name, exp_type, fault, state";
    $query .= " FROM summitExp";
    $query .= " LEFT JOIN pzDownloadExp USING (summit_id)";
    $query .= " WHERE dateobs like '$date%'";
#   $query .= " AND (state != 'drop' or state IS NULL)";
    $query .= " AND fault != 1042";
    $query .= " AND state IS NULL";

    print "$query\n";

    my $result = &mysql_select ($query);

    $doHeader = 1; 
    while (@row = $result->fetchrow_array()) {
	if ($doHeader) {
	    print "OBJECT Exposures NOT sent to chipRun:\n";
	    printf "%11s %15s %14s %14s\n", "exp_name", "exp_type", "fault", "state";
	    $doHeader = 0;
	}
	printf "%11s %15s %14s %14s\n", $row[0], $row[1], $row[2], $row[3];
    }
    if (! $doHeader) { print "\n"; }
}

# check the status of raw exposures:
if (1) { 
    if ($verbose) { print "Exposures in rawExp:\n"; }

    $expSkipped = 0;
    $expPending = 0;

    my $query = "SELECT exp_type, obs_mode, state, count(*)";
    $query .= " FROM rawExp";
    $query .= " WHERE dateobs like '$date%'";
    $query .= " AND exp_id > $refExpID"; # REF
#   $query .= " AND exp_type = 'OBJECT'";
    $query .= " GROUP BY exp_type, obs_mode, state";
    $query .= " ORDER BY exp_type, obs_mode";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; }
	if ($row[2] eq "new") {
	    $expPending += $row[3];
	}
	if ($row[2] eq "") {
	    $expSkipped += $row[3];
	}
    }
    if ($expSkipped || $expPending) { print "*** WARNING: $expSkipped skipped rawExp, $expPending incomplete rawExp ***\n\n"; } else { if ($verbose) { print "\n"; }}
}
    
# check the status of chip runs:
if (1) {
    if ($verbose) { print "Exposures in chipRun (OBJECT, obs_mode not like ENGINEERING, not NULL):\n"; } # the obs_mode not NULL is implicit given the obs_mode not like ENGINEERING 

    $chipPending = 0;
    $chipSkipped = 0;

    my $query = "SELECT exp_type, obs_mode, chipRun.state, count(*)";
    $query .= " FROM rawExp";
    $query .= " LEFT JOIN chipRun using (exp_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " GROUP BY obs_mode, chipRun.state";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s", "exp_type", "obs_mode", "state", "N(exp)\n"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; }
	if ($row[2] eq "new") {
	    $chipPending += $row[3];
	}
	if ($row[2] eq "") {
	    $chipSkipped += $row[3];
	}
    }
    if ($chipPending || $chipSkipped) { print "*** WARNING: $chipSkipped skipped chipRun, $chipPending incomplete chipRun ***\n\n"; } else { if ($verbose) { print "\n"; }}
}

# check the status of camera runs:
$camPending = 0;
$camSkipped = 0;
{
    if ($verbose) { print "Exposures in camRun (OBJECT, not ENGINEERING)...\n"; }

    # my $result = &mysql_select ("");
    my $query = "SELECT exp_type, obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality, count(*)";
    $query .= " FROM rawExp join chipRun using (exp_id)";
    $query .= " LEFT JOIN camRun using (chip_id)";
    $query .= " LEFT JOIN camProcessedExp using (cam_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " GROUP BY obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s | %5s %5s\n", "exp_type", "obs_mode", "state", "N(exp)", "fault", "quality"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d   | %5d %5d\n", $row[0], $row[1], $row[2], $row[5], $row[3], $row[4]; } # exp_type, obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality,  count(*)
	if ($row[2] eq "new") {
	    $camPending += $row[5];
	}
	if ($row[2] eq "") {
	    $camSkipped += $row[5];
	}
    }
    if ($camSkipped || $camPending) { print "*** WARNING: $camSkipped skipped camRuns, $camPending pending camRuns ***\n\n"; } else { if ($verbose) { print "\n"; }}
}

# check the status of fake runs:
{
    if ($verbose) { print "Exposures in fakeRun:\n"; }

    $fakePending = 0;
    $fakeSkipped = 0;

    my $query = "SELECT exp_type, obs_mode, fakeRun.state, count(*)";
    $query .= " FROM rawExp";
    $query .= " JOIN chipRun using (exp_id)";
    $query .= " JOIN camRun using (chip_id)";
    $query .= " JOIN camProcessedExp using (cam_id)";
    $query .= " LEFT JOIN fakeRun using (cam_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND (camProcessedExp.quality is null or camProcessedExp.quality = 0)";
    $query .= " GROUP BY obs_mode, fakeRun.state";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; } # exp_type, obs_mode, fakeRun.state,  count(*)
	if ($row[2] eq "new") {
	    $fakePending += $row[3];
	}
	if ($row[2] eq "") {
	    $fakeSkipped += $row[3];
	}
    }
    if ($fakeSkipped || $fakePending) { print "$fakeSkipped skipped fakeRuns, $fakePending pending fakeRuns\n\n"; } else { if ($verbose) { print "\n"; }}
}

# check the status of warp runs:
{
    if ($verbose) { print "Exposures in warpRuns:\n"; }

    $warpPending = 0;
    $warpSkipped = 0;

    # my $result = &mysql_select ("");
    my $query = "SELECT exp_type, obs_mode, warpRun.state, count(*)";
    $query .= " FROM rawExp";
    $query .= " JOIN chipRun using (exp_id)";
    $query .= " JOIN camRun using (chip_id)";
    $query .= " JOIN camProcessedExp using (cam_id)";
    $query .= " JOIN fakeRun using (cam_id)";
    $query .= " LEFT JOIN warpRun using (fake_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND (camProcessedExp.quality IS NULL OR camProcessedExp.quality = 0)";
    $query .= " GROUP BY obs_mode, warpRun.state";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state",  "N(exp)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; } # exp_type, obs_mode, warpRun.state,  count(*)
	if ($row[2] eq "new") {
	    $warpPending += $row[3];
	}
	if ($row[2] eq "") {
	    $warpSkipped += $row[3];
	}
    }
    if ($warpSkipped || $warpPending) { print "$warpSkipped skipped warpRuns, $warpPending pending warpRuns\n\n"; } else { if ($verbose) { print "\n"; }}
}

# count the number of exposures per chunk, determine expected diffRuns:
{
    print "Expected number of WWdiffs excluding camRuns with bad quality:\n";
    print "NOTE: multiple exposures with same comment string (xyz visit a) are treated as 1 exposure\n";

    # my $result = &mysql_select ("");
    my $query = "SELECT Nvisit, count(*) FROM";
    $query .= " (";
    $query .= "   SELECT object, filter, chunk, count(*) as Nvisit FROM";
    $query .= "   (";
    $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
    $query .= "     FROM rawExp";
    $query .= "     LEFT JOIN chipRun using (exp_id)";
    $query .= "     LEFT JOIN camRun using (chip_id)";
    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
    $query .= "     WHERE dateobs LIKE '$date%'";
    $query .= "     AND exp_id > $refExpID"; #REF
    $query .= "     AND exp_type = 'OBJECT'";
    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "     AND obs_mode like '%SS%'";
    $query .= "     AND rawExp.comment like '%visit%'";
    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
    $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
    $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";
    $query .= " ) AS tableVisits GROUP BY Nvisit";

    my $result = &mysql_select ($query);

    $diffExpect = 0;
    while (@row = $result->fetchrow_array()) {
	# printf "%8s %15s : ", $row[0], $row[1]; # c, count(*)
	if ($row[0] == 1) {
	    if ($row[1] == 1) { $verb = "quad has"; } else { $verb = "quads have"; }
	    printf "%3d %-11s 1 visit, no WWdiffs\n", $row[1], $verb;
	} elsif ($row[0] == 2) {
	    if ($row[1] == 1) { $verb = "quad has"; } else { $verb = "quads have"; }
	    printf "%3d %-11s 2 visits, expect $row[1] WWdiff(s)\n", $row[1], $verb;
	    $diffExpect += $row[1];
	} elsif ($row[0] == 3) {
	    if ($row[1] == 1) { $verb = "quad has"; } else { $verb = "quads have"; }
	    printf "%3d %-11s 3 visits, expect %d WWdiffs (2 per quad)\n", $row[1], $verb,  2 * $row[1];
	    $diffExpect += $row[1]*2;
	} elsif ($row[0] == 4) {
	    if ($row[1] == 1) { $verb = "quad has"; } else { $verb = "quads have"; }
	    printf "%3d %-11s 4 visits, expect %d WWdiffs (2 per quad)\n", $row[1], $verb,  2 * $row[1];
	    $diffExpect += $row[1]*2;
	} else {
	    print "I don't understand, there are more visits than I expect\n";
	}
    }
    print "\n";
    print " * $diffExpect diffRuns expected *\n";
    if ($verbose) { print "\n"; }
}

# List chunks with duplicate visits
{
    # my $result = &mysql_select ("");
#   my $query = "SELECT Nvisit, count(*) FROM";
#    $query .= " (";
    $query .= "   SELECT object, filter, chunk, Nexp_in_chunk FROM";
    $query .= "   (";
    $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk, count(*) as Nexp_in_chunk";
    $query .= "     FROM rawExp";
    $query .= "     LEFT JOIN chipRun using (exp_id)";
    $query .= "     LEFT JOIN camRun using (chip_id)";
    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
    $query .= "     WHERE dateobs LIKE '$date%'";
    $query .= "     AND exp_id > $refExpID"; #REF
    $query .= "     AND exp_type = 'OBJECT'";
    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "     AND obs_mode like '%SS%'";
    $query .= "     AND rawExp.comment like '%visit%'";
    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
    $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
    $query .= "   ) AS tableChunks where (Nexp_in_chunk > 1)";
#    $query .= " ) AS tableVisits GROUP BY Nvisit";

    my $result = &mysql_select ($query);

    $doHeader = 1; 
    while (@row = $result->fetchrow_array()) {
	if ($doHeader) {
	    print "chunks with duplicate visits:\n";
	    printf "%11s %15s %14s %5s\n", "object", "filter", "chunk", "Nexp in chunk";
	    $doHeader = 0;
	}
	printf "%11s %15s %14s %5s\n", $row[0], $row[1], $row[2], $row[3];
    }
    if (! $doHeader) { print "\n"; }
}

# List exposures which may be used for WWdiffs
if (0) {
    print "List exposures for WWdiffs excluding camRuns with bad quality:\n";

    # my $result = &mysql_select ("");
    my $query = "";
    $query .= "     SELECT exp_name, object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
    $query .= "     FROM rawExp";
    $query .= "     LEFT JOIN chipRun using (exp_id)";
    $query .= "     LEFT JOIN camRun using (chip_id)";
    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
    $query .= "     WHERE dateobs LIKE '$date%'";
    $query .= "     AND exp_id > $refExpID"; #REF
    $query .= "     AND exp_type = 'OBJECT'";
    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "     AND obs_mode like '%SS%'";
    $query .= "     AND rawExp.comment like '%visit%'";
    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";

    my $result = &mysql_select ($query);
    &mysql_dump_result ($result);
}

# check the status of diff runs:
{
    if ($verbose) { print "diff processing status (not WS):\n"; }

    $diffDone = 0;

    my $query = "SELECT label, state, count(*)";
    $query .= " FROM diffRun";
    $query .= " WHERE data_group like '%$dataGroupDate'";
    $query .= " AND label NOT LIKE '%WS.nigh%'";
    $query .= " GROUP BY label, state";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%20s %6s | %8s\n", "label", "state", "N(diffs)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%20s %6s | %8d\n", $row[0], $row[1], $row[2]; } # label, state, count
	if (($row[1] ne "new") && ($row[1] ne "")) {
	    $diffDone += $row[2];
	}
    }
    if ($verbose) { print "\n"; }
    if ($diffDone != $diffExpect) { 
	print "*** WARNING: Expect $diffExpect diffRuns, complete $diffDone diffRuns: inconsistent ***\n"; 
    } else {
	print " * $diffDone of $diffExpect diffRuns completed *\n"; 
    }
}

# count diffRuns to be published:
{
    my $query = "SELECT count(*) FROM";
    $query .= "  (";
    $query .= "    SELECT label, state, count(*)";
    $query .= "    FROM diffRun";
    $query .= "    LEFT JOIN diffSkyfile using (diff_id)";
    $query .= "    WHERE data_group LIKE '%$dataGroupDate%'";
    $query .= "    AND label NOT LIKE '%WS.nigh%'";
    $query .= "    AND (quality IS NULL OR quality = 0)";
    $query .= "    GROUP BY label, state, diff_id";
    $query .= "  ) as tableDiffs";

    my $result = &mysql_select ($query);

    @row = $result->fetchrow_array();
    printf " * %d diffRuns can be published *   (has at least 1 skycell with quality = 0 or quality is NULL)\n", $row[0];
    if ($verbose) { print "\n"; }
    $diffRunsToBePublished = $row[0];
}

# check the status of publish runs:
{
    if ($verbose) { print "publishing status (not WS)...\n"; }

    $pubDone = 0;

    my $query = "SELECT diffRun.label, publishRun.state, count(*)";
    $query .= " FROM diffRun";
    $query .= " LEFT JOIN publishRun on (diff_id = stage_id)";
    $query .= " WHERE data_group LIKE '%$dataGroupDate'";
    $query .= " AND diffRun.label NOT LIKE '%WS.nigh%'";
    $query .= " GROUP BY diffRun.label, publishRun.state";

    my $result = &mysql_select ($query);

    if ($verbose) { printf "%20s %6s | %8s\n", "label", "state", "N(diffs)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose) { printf "%20s %6s | %8d\n", $row[0], $row[1], $row[2]; } # label, state, count 
	if (($row[1] ne "new") && ($row[1] ne "")) {
	    $pubDone += $row[2];
	}
    }
    if ($verbose) { print "\n"; }
    if ($pubDone != $diffRunsToBePublished) { 
	print "*** WARNING: Expect $diffRunsToBePublished diffRuns to be published, only $pubDone pubRuns: inconsistent ***\n\n"; 
    } else {
	print " * $pubDone of $diffRunsToBePublished diffRuns published **\n\n"; 
    }
}

# check for exposures not sent to chip:
if ($verbose) {

    my $query = "SELECT exp_name, obs_mode, comment";
    $query .= " FROM rawExp left join chipRun using (exp_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " AND chip_id is null";

    my $result = &mysql_select ($query);

    $doHeader = 1; 
    while (@row = $result->fetchrow_array()) {
	if ($doHeader) {
	    print "OBJECT Exposures NOT sent to chipRun:\n";
	    printf "%11s %15s %14s", "exp_name", "obs_mode", "comment\n";
	    $doHeader = 0;
	}
	printf "%11s %15s %14s\n", $row[0], $row[1], $row[2];
    }
    if (! $doHeader) { print "\n"; }
}

# check for chipRuns not sent to camera
if ($camSkipped && $verbose) {
    print "chipRuns NOT sent to camRun:\n";

    my $query = "SELECT exp_name, obs_mode, comment, chipRun.state";
    $query .= " FROM rawExp";
    $query .= " JOIN chipRun USING (exp_id)";
    $query .= " LEFT JOIN camRun using (chip_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " AND cam_id IS NULL";

    my $result = &mysql_select ($query);

    printf "%11s %15s %10s %14s\n", "exp_name", "obs_mode", "chip.state", "comment";
    while (@row = $result->fetchrow_array()) {
	printf "%11s %15s %10s %14s\n", $row[0], $row[1], $row[2], $row[3];
    }
    print "\n";
}

# check for bad quality camera exposurs
if (1) {
    my $query = "SELECT exp_name, camProcessedExp.quality, comment";
    $query .= " FROM rawExp";
    $query .= " JOIN chipRun USING (exp_id)";
    $query .= " JOIN camRun USING (chip_id)";
    $query .= " JOIN camProcessedExp USING (cam_id)";
    $query .= " WHERE dateobs LIKE '$date%'";
    $query .= " AND exp_id > $refExpID"; #REF
    $query .= " AND exp_type = 'OBJECT'";
    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= " AND (camProcessedExp.quality > 0)";
    $query .= " ORDER BY comment";

    my $result = &mysql_select ($query);

    $doHeader = 1; 
    $NbadCam = 0;

    while (@row = $result->fetchrow_array()) {
	if ($verbose && $doHeader) {
	    print "Bad quality camRun exposures:\n";
	    printf "%11s %7s | %15s\n", "exp_name", "quality", "comment";
	    $doHeader = 0;
	}
	if ($verbose) { printf "%11s %7d | %15s\n", $row[0], $row[1], $row[2]; } else { $NbadCam ++; }
    }
    if ($verbose && !$doHeader) { print "\n"; }
    if (!$verbose) { print "$NbadCam bad quality camera exposures\n\n"; }
}

# check the status of diff runs NOT for Solar System:
if ($verbose) {
    # my $result = &mysql_select ("");
    my $query = "SELECT * FROM";
    $query .= " (";
    $query .= "  SELECT object, filter, chunk, count(*) as Nvisit FROM";
    $query .= "  (";
    $query .= "   SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
    $query .= "   FROM rawExp";
    $query .= "   LEFT JOIN chipRun using (exp_id)";
    $query .= "   LEFT JOIN camRun using (chip_id)";
    $query .= "   LEFT JOIN camProcessedExp using (cam_id)";
    $query .= "   WHERE dateobs LIKE '$date%'";
    $query .= "   AND exp_id > $refExpID"; #REF
    $query .= "   AND exp_type = 'OBJECT'";
    $query .= "   AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "   AND obs_mode NOT like '%SS%'";
    $query .= "   AND rawExp.comment like '%visit%'";
    $query .= "   AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
    $query .= "   GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
    $query .= "  ) AS tableChunks GROUP BY object, filter, chunk";
    $query .= " ) AS tableVisits ORDER BY Nvisit, chunk, object";

    my $result = &mysql_select ($query);
    
    $doHeader = 1;
    while (@row = $result->fetchrow_array()) {
	if ($doHeader) {
	    print "Chunks which should not generate WWdiffs (not Solar System diffs)\n";
	    printf "%8s %8s %8s %8s\n", "Object", "Filter", "Chunk", "N(visit)";
	    $doHeader = 0;
	}
	printf "%8s %8s %8s %4d\n", $row[0], $row[1], $row[2], $row[3];
    }
    if (!$doHeader) { print "\n"; }
}

# check for unpublished solar-system exposures
{
    my $query = "SELECT expall.exp_name, expall.dateobs, expall.comment, expall.warp_id, exppub.diff_id, exppub.pubstate from";
    $query .= "  (";
    $query .= "    SELECT exp_name, comment, dateobs, warp_id";
    $query .= "    FROM rawExp";
    $query .= "    JOIN chipRun using (exp_id)";
    $query .= "    JOIN camRun using (chip_id)";
    $query .= "    JOIN camProcessedExp using (cam_id)";
    $query .= "    JOIN fakeRun using (cam_id)";
    $query .= "    JOIN warpRun using (fake_id)";
    $query .= "    WHERE dateobs like '$date%'";
    $query .= "    AND exp_id > $refExpID"; #REF
    $query .= "    AND obs_mode like '%SS%'";
    $query .= "    AND (camProcessedExp.quality = 0)";
    $query .= "  ) AS expall";
    $query .= "  LEFT JOIN";
    $query .= "  (";
    $query .= "    SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate";
    $query .= "    FROM diffRun";
    $query .= "    JOIN diffInputSkyfile using (diff_id)";
    $query .= "    JOIN publishRun on (diffRun.diff_id = stage_id)";
    $query .= "    JOIN warpRun ON ( warp1 = warp_id or warp2 = warp_id)";
    $query .= "    JOIN fakeRun using (fake_id)";
    $query .= "    JOIN camRun using (cam_id)";
    $query .= "    JOIN camProcessedExp using (cam_id)";
    $query .= "    JOIN chipRun using (chip_id)";
    $query .= "    JOIN rawExp using (exp_id)";
    $query .= "    WHERE diffRun.reduction like 'SWEETSPOT'";
##  $query .= "    AND exp_id > $refExpID"; -- adding this restriction here slows the query way down1
    $query .= "    AND stack2 IS NULL";
    $query .= "    AND diffRun.data_group LIKE '%SS.$dataGroupDate'";
    $query .= "    GROUP BY exp_name";
    $query .= "   ) AS exppub";
    $query .= " ON expall.exp_name = exppub.exp_name";
    $query .= " WHERE exppub.exp_name is NULL";
    $query .= " OR exppub.pubstate = 'new'";

    my $result = &mysql_select ($query);

    $doHeader = 1;
    while (@row = $result->fetchrow_array()) {
	if ($doHeader) {
	    print "Unpublished Solar System Exposures ( obs_mode like %SS% ) with good camera quality :\n";
	    printf "%12s %12s %12s | %-19s | %-16s\n", "exp_name", "warp_id", "diff_id", "dateobs", "comment";
	    $doHeader = 0;
	}
	if ($row[3] eq "") { $row[3] = "NULL"; }
	if ($row[4] eq "") { $row[4] = "NULL"; }
	printf "%12s %12s %12s | %-19s | %-16s\n", $row[0], $row[3], $row[4], $row[1], $row[2]
    }
}
print "\n\n";

# count the number of exposures per chunk, determine expected diffRuns:
if ($verbose2) {
    print "Number of exposures for each chunk:\n";

    # my $result = &mysql_select ("");
    my $query = "SELECT object, filter, chunk, count(*) as Nvisit FROM";
    $query .= "   (";
    $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
    $query .= "     FROM rawExp";
    $query .= "     LEFT JOIN chipRun using (exp_id)";
    $query .= "     LEFT JOIN camRun using (chip_id)";
    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
    $query .= "     WHERE dateobs LIKE '$date%'";
    $query .= "     AND exp_id > $refExpID"; #REF
    $query .= "     AND exp_type = 'OBJECT'";
    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "     AND obs_mode like '%SS%'";
    $query .= "     AND rawExp.comment like '%visit%'";
    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
    $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
    $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";

    my $result = &mysql_select ($query);

    printf "%11s %7s %18s %-3s\n", "object", "filter", "chunk", "N(visit)";
    while (@row = $result->fetchrow_array()) {
	printf "%11s %7s %18s %3d\n", $row[0], $row[1], $row[2], $row[3];
    }
    print "\n";
}

exit 0;

# global dbh?
sub mysql_select {
    
    my $query = $_[0];
    
    my $statement = $dbh->prepare($query);
    
    $statement->execute();
    
    return $statement;
}

sub mysql_dump_result {
    
    my $result = $_[0];
    
    $Nfields = $result->{NUM_OF_FIELDS};

    print "Nfields: $Nfields\n";

    for ($i = 0; $i < $Nfields; $i++) {
	printf "Field %d : %s\n", $i, $result->{NAME}->[$i];
    }

    while (@row = $result->fetchrow_array()) {
	for ($i = 0; $i < $Nfields; $i++) {
	    print "$row[$i]  ";
	}
	print "\n";
    }
    return;
}

# example using the dbh prepare, execute, fetch commands:
if (0) {

    my $result = &mysql_select ("SELECT * from rawExp limit 2");
    
    &mysql_dump_result ($result);
    
    exit 1;
}
