#!/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
    'report'            => \$report,   # Formatted for ps-obs report
    'very-verbose'      => \$verbose2, # Print to stdout
    'chunks'            => \$chunks,   # give information on the current status of the chunks
    'mopsreport'            => \$mopsreport,   # return a report in the new style requested by MOPS
    ) 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;

# 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 (ALWAYS do this)
$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";
    }
    if ($verbose2) { print "Reference exp_id: $refExpID\n"; }
}

##################
# testing how to get more information parsed by chunk
if ($mopsreport or $report) {
    print "\n\n\n";
    print "--- MOPS report for $dbname : $date ---\n\n";

    my $badcamflag = 0;
    my $badwarpflag = 0;
    my $baddiffflag = 0;

    #do a query of raw/chip/cam/warp counts for this night, split by chunk. 
    #We want to stick to only MOPS products, so we do labels %SS%, %EU% and %Bright.% (to avoid BrightTwi and Bright3PI which are not in quads)
    #we need to do a subquery to find the total number of skycells in warpSkyfile, as well as the number of those with a bad quality
    my $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad FROM ";
    $query .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(if(rawExp.comment LIKE '%visit 1%',1,NULL)) AS Nvis1,";
    $query .= "    count(if(rawExp.comment LIKE '%visit 2%',1,NULL)) AS Nvis2,count(if(rawExp.comment LIKE '%visit 3%',1,NULL)) AS Nvis3,";
    $query .= "    count(if(rawExp.comment LIKE '%visit 4%',1,NULL)) AS Nvis4, count(if(camProcessedExp.sigma_ra <= 5 AND camProcessedExp.sigma_dec <= 5 AND camProcessedExp.quality = 0,1,NULL)) AS Ncamgood,";
    $query .= "    count(if(camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5 OR camProcessedExp.quality > 0,1,NULL)) AS Ncambad";
    $query .= "    FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) JOIN camProcessedExp USING (cam_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id)";
    $query .= "    WHERE dateobs like '$date%' AND exp_id > $refExpID";
    $query .= "    AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
    $query .= "    GROUP BY chunk ORDER BY dateobs) as suba ";
    $query .= "LEFT JOIN";
    $query .= "    (SELECT subc.chunk, count(if(subc.Nwarpskycellbad < subc.Nwarpskycell,1,NULL)) AS Nwarpgood, count(if(subc.Nwarpskycellbad = subc.Nwarpskycell,1,NULL)) AS Nwarpbad";
    $query .= "    FROM ";
    $query .= "      (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad";
    $query .= "      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 warpSkyfile USING (warp_id)";
    $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
    $query .= "      AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
    $query .= "      GROUP BY exp_name) as subc";
    $query .= "    GROUP BY chunk) AS subb ";
    $query .= "ON suba.chunk=subb.chunk";
 
    my $result = &mysql_select ($query);

    printf "%18s | %11s | %4s | %4s | %7s | %4s | %7s | %4s | %7s |\n", "chunk", "Nobs", "Ncam","Nwrp","Nwrp_pub", "Nwwd", "Nwwd_pub", "Nwsd", "Nwsd_pub"; 
    while (@row = $result->fetchrow_array()) {
          my $Nvis1 = 0;
          my $Nvis2 = 0;
          my $Nvis3 = 0;
          my $Nvis4 = 0;
          my $Ncamgood = 0;
          my $Ncambad = 0;
          my $Nwarpgood = 0;
          my $Nwarpbad = 0;
          my $NexpWWdiff = 0;
          my $Npubwarps = 0;
          my $uniwarps = undef;

          my $chunkname = $row[0];
          if($row[1] gt 0) {$Nvis1 = $row[1]};
          if($row[2] gt 0) {$Nvis2 = $row[2]};
          if($row[3] gt 0) {$Nvis3 = $row[3]};
          if($row[4] gt 0) {$Nvis4 = $row[4]};
          if($row[5] gt 0) {$Ncamgood = $row[5]};
          if($row[6] gt 0) {$Ncambad = $row[6]};
          if($row[7] gt 0) {$Nwarpgood = $row[7]};
          if($row[8] gt 0) {$Nwarpbad = $row[8]};

          #set flags if present
          if($Ncambad >= 1) {$badcamflag = 1};
          if($Nwarpbad >= 1) {$badwarpflag = 1};

          #Find the total published number of unique warps for this chunk
          #my $query0 = "     SELECT count(DISTINCT warpRun.warp_id) FROM ";
          #$query0 .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) JOIN publishRun ON (diffInputSkyfile.diff_id = stage_id)";
          #$query0 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%')";
          #$query0 .= "            AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by warpRun.warp_id";
          #my $result0 = &mysql_select ($query0);
          #while (@row = $result0->fetchrow_array()) {$Npubwarps = $row[0];}


          #Find the expected number of diffs for this chunk
          my $query1 = "SELECT suba.chunk, SUM(IF(suba.Nvisits = 2,1,NULL)) as totvis2, SUM(IF(suba.Nvisits = 3 OR suba.Nvisits = 4,2,NULL)) as totvis34 FROM";
          $query1 .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits FROM";
          $query1 .= "    rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) WHERE dateobs like '$date%'";
          $query1 .= "    AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
          $query1 .= "    AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by object) as suba group by chunk;";
          my $result1 = &mysql_select ($query1);
          while (@row = $result1->fetchrow_array()) {$NexpWWdiff = $row[1] + $row[2];}

          #find the unique diff_ids for each chunk, to be matched to the diffRun catalog
          my $query2 = "     SELECT diff_id,warp1,stack1,warp2,stack2 FROM ";
          $query2 .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
          $query2 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%')";
          $query2 .= "            AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' GROUP By diff_id";
          my $result2 = &mysql_select ($query2);

          my $NWWdiffgood = 0;
          my $NWWdiffbad = 0;
          my $NWWdiffpub = 0;

          my $NWSdiffgood = 0;
          my $NWSdiffbad = 0;
          my $NWSdiffpub = 0;
          my %wrpcnt = ();

          while (@row2 = $result2->fetchrow_array()) {
              #distinguish between WW and WS diffs based on stack2
              if ($row2[4] == 0) {
                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
                  my $result3 = &mysql_select ($query3);
                  while (@row3 = $result3->fetchrow_array()) {
                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
                      if ($row3[1]<$row3[0]) {$NWWdiffgood ++;}
                      if ($row3[1]==$row3[0]) {$NWWdiffbad ++;}
                      if ($row3[2]==$row3[0]) {$NWWdiffpub ++;}

                      #if this wwdiff is published, record its corresponding warp_ids to see how many unique ones there are
                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[1]}++;}  # record the presence of warp1
                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[3]}++;}  # record the presence of warp2
                  }
              }

              if ($row2[4] > 0) {
                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
                  my $result3 = &mysql_select ($query3);
                  while (@row3 = $result3->fetchrow_array()) {
                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
                      if ($row3[1]<$row3[0]) {$NWSdiffgood ++;}
                      if ($row3[1]==$row3[0]) {$NWSdiffbad ++;}
                      if ($row3[2]==$row3[0]) {$NWSdiffpub ++;}
                  }
              }
          }
          #pull out the unique warp_ids
          $Npubwarps = keys %wrpcnt;

          #check for bad diffs 
          if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) {$baddiffflag = 1;}

          if ($Nvis1 ne $Nvis4) {
            if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, not done";}  
            if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, done";}
          }
          if ($Nvis1 eq $Nvis4) {
            if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, not done";}
            if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, done";}
          }
    }

    print "\n";

    #-----------------------------------------
    #list bad quality cam exposures
    if ($badcamflag != 0) {
        my $query = "SELECT exp_name,cam_id, camProcessedExp.quality, camProcessedExp.sigma_ra, camProcessedExp.sigma_dec, 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"; 
        $query .= " AND exp_type = 'OBJECT'";
        $query .= " AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
        $query .= " AND (camProcessedExp.quality > 0 OR camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5)";
        $query .= " ORDER BY comment";

        my $result = &mysql_select ($query);

        $doHeader = 1; 

        while (@row = $result->fetchrow_array()) {
            if ($doHeader) {
                print "Bad quality camRun exposures:\n";
                printf "%11s | %7s | %7s | %9s | %9s | %15s\n", "exp_name","cam_id", "quality", "sigma_ra", "sigma_dec", "comment";
                $doHeader = 0;
	    }
            printf "%11s | %7s | %7d | %9s | %9s | %15s\n", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]; 
        }
        if (!$doHeader) { print "\n"; }
    }

    #-----------------------------------------
    #list bad quality warp exposures
    if ($badwarpflag != 0) {
        $query = "SELECT exp_name,warp_id,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad, comment";
        $query .= "      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 warpSkyfile USING (warp_id)";
        $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
        $query .= "      AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
        $query .= "      GROUP BY exp_name ORDER BY comment";

        my $result = &mysql_select ($query);

        $doHeader = 1; 

        while (@row = $result->fetchrow_array()) {
    	if ($row[2] == $row[3]) {
                if ($doHeader) {
                    print "Bad quality warpRun exposures:\n";
                    printf "%11s | %7s | %15s\n", "exp_name", "warp_id", "comment";
                    $doHeader = 0;
	        }
                printf "%11s | %7d | %15s \n", $row[0], $row[1], $row[4]; 
            }
        }
        if (!$doHeader) { print "\n"; }
    }

    #-----------------------------------------
    #list bad quality diff exposures
    if ($baddiffflag != 0) {
        my $query = "     SELECT diff_id,warp1,stack1,warp2,stack2,exp_name,comment FROM ";
        $query .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
        $query .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
        $query .= "           GROUP By diff_id";
        my $result = &mysql_select ($query);

        $doHeader = 1; 

        while (@row = $result->fetchrow_array()) {
            if ($doHeader) {
                print "Bad quality diffRun exposures:\n";
                printf "%11s | %7s | %7s | %7s | %7s | %7s | %15s\n", "exp_name", "diff_id", "warp1", "stack1", "warp2", "stack2", "comment";
                $doHeader = 0;
            }

            #distinguish between WW and WS diffs based on stack2
            if ($row[4] == 0) {
                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
                  my $result3 = &mysql_select ($query3);
                  while (@row3 = $result3->fetchrow_array()) {
                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
                      if ($row3[1]==$row3[0]) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6];}
                  }
            }

            if ($row[4] > 0) {
                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
                  my $result3 = &mysql_select ($query3);
                  while (@row3 = $result3->fetchrow_array()) {
                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
                      if ($row3[1]==$row3[0]) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6];}
                  }
            }
        }
        if (!$doHeader) { print "\n"; }
    }
}


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



### testing the diff grouping -- this does not work for the Euclid data taken 2018.07.29 because the OBJECT
### field is missing
if (0) {
    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 $query = "SELECT Nvisit, count(*) FROM";
    # $query .= " (";
    my $query = "   SELECT object, filter, chunk, count(*) as Nvisit FROM";
    $query .= "   (";
    $query .= "SELECT object, filter, comment, 'S', substr(comment, 1, position(' ' in comment)) as chunk, 'E'";
    $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%' or obs_mode like 'BRIGHT')";
    $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);
    &mysql_dump_result ($result);
    die;

    $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 = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 1 visit, no WWdiffs\n", $row[1], $verb;
	} elsif ($row[0] == 2) {
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples 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 = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 3 visits, expect %d WWdiffs (2 per tuple)\n", $row[1], $verb,  2 * $row[1];
	    $diffExpect += $row[1]*2;
	} elsif ($row[0] == 4) {
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 4 visits, expect %d WWdiffs (2 per tuple)\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";
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; }
	    if ($row[0] % 2) { $Ndiffs = int($row[0] / 2) + 1; } else { $Ndiffs = int($row[0] / 2); }
	    printf "%3d %-11s %d visits, expect %d WWdiffs (2 per quad)\n", $row[1], $verb,  $row[0], $Ndiffs; 
	    $diffExpect += $Ndiffs;
	}
    }
    print "\n";
    print " * $diffExpect diffRuns expected *\n";
    if ($verbose) { print "\n"; }

    die;
}
######################

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

    $summitSkipped = 0;
    $summitPending = 0;

    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";

    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"; 

        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 like 'new' or state IS NULL)";
        $query .= " AND fault != 1042";

        my $result = &mysql_select ($query);
        printf "%12s %8s %15s %6s\n", "exp_name", "exp_type", "fault", "state"; 
        while (@row = $result->fetchrow_array()) {
            if ($row[3] eq "") { $row[3] = "NULL"; }
	    printf "%12s %8s %15s %6s\n", $row[0], $row[1], $row[2], $row[3]; 
        }
    }
    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 or $report) { 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 or $report) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; }
    while (@row = $result->fetchrow_array()) {
	if ($verbose or $report) { 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:
{
    if ($report) { 
	print "\n"; 
    } else {
	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%' OR obs_mode LIKE '%BRIGHT%')";
    $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 = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 1 visit, no WWdiffs\n", $row[1], $verb;
	} elsif ($row[0] == 2) {
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples 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 = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 3 visits, expect %d WWdiffs (2 per tuple)\n", $row[1], $verb,  2 * $row[1];
	    $diffExpect += $row[1]*2;
	} elsif ($row[0] == 4) {
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; }
	    printf "%3d %-11s 4 visits, expect %d WWdiffs (2 per tuple)\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";
	    if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; }
	    if ($row[0] % 2) { $Ndiffs = int($row[0] / 2) + 1; } else { $Ndiffs = int($row[0] / 2); }
	    printf "%3d %-11s %d visits, expect %d WWdiffs (2 per quad)\n", $row[1], $verb,  $row[0], $Ndiffs; 
	    $diffExpect += $Ndiffs;
	}
    }
    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%' OR obs_mode LIKE '%BRIGHT%')";
    $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 'EXAMPLE', 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%' OR obs_mode LIKE '%BRIGHT%')";
    $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 .= " AND label NOT LIKE 'goto_cleaned'";
    $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"; 

        if ($verbose) { 
	   print "Published diffs split by Nvisit :\n"; 
           printf "%8s %10s\n", "Nvisit", "N_pubdiff";

           my $query = "SELECT Nvisit, SUM(Ndiffs) FROM ";
           $query .= " (SELECT object, filter, chunk, count(*) as Nvisit,count(DISTINCT diff_id) as Ndiffs FROM";
           $query .= "  (SELECT expall.object, expall.filter, expall.chunk as chunk, expall.exp_name,exppub.diff_id from";
           $query .= "  (";
           $query .= "    SELECT object, filter,exp_name, substr(comment, 1, position(' ' in comment)) as chunk, 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"; 
           $query .= "    AND (camProcessedExp.quality = 0)";
           $query .= "    AND (comment NOT LIKE '%twilight%')";
           $query .= "  ) AS expall";
           $query .= "  JOIN";
           $query .= "  (SELECT * FROM (";
           $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
           $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
           $query .= "    JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
           $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
           $query .= "    WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'";
           $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
           $query .= "  UNION";
           $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
           $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
           $query .= "    JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
           $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
           $query .= "    WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'";
           $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
           $query .= "  ) AS diffgather";
           $query .= "  GROUP BY diffgather.exp_name)";
           $query .= "  AS exppub"; 
           $query .= " ON expall.exp_name = exppub.exp_name";
           $query .= " ) AS pubdiffgather GROUP BY object, filter, chunk";
           $query .= " ) AS tableVisits GROUP BY Nvisit";

           my $result = &mysql_select ($query);
           while (@row = $result->fetchrow_array()) {
         	printf "%8s %10s\n", $row[0], $row[1]; 
           }

           #-------------------------------------------------------------
           print "\n"; 
	   print "Listing objects with 1< Nvisits < 4:\n"; 
           printf "| %12s | %8s | %19s | %8s |\n", "object", "filter", "chunk", "Nvisit";

           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 LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
           $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 WHERE Nvisit > 1 AND Nvisit < 4 ORDER BY chunk,Nvisit ";
           my $result = &mysql_select ($query);

           while (@row = $result->fetchrow_array()) {
         	printf "| %12s | %8s | %19s | %8s |\n", $row[0], $row[1], $row[2], $row[3]; 
           }
           print "\n"; 
        }

    } else {
	if (not $report) { 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 label NOT LIKE 'goto_cleaned'";
    $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();
    if (not $report) { 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 .= " AND diffRun.label NOT LIKE 'goto_cleaned'";
    $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 or $report) && $doHeader) {
	    print "Bad quality camRun exposures:\n";
	    printf "%11s %7s | %15s\n", "exp_name", "quality", "comment";
	    $doHeader = 0;
	}
	if ($verbose or $report) { printf "%11s %7d | %15s\n", $row[0], $row[1], $row[2]; } else { $NbadCam ++; }
    }
    if (($verbose or $report) && !$doHeader) { print "\n"; }
    if (!($verbose or $report)) { print "$NbadCam bad quality camera exposures\n\n"; }
}

# check the status of diff runs NOT for Solar System:
if ($verbose or $report) {
    # 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%' AND obs_mode NOT LIKE '%BRIGHT%')";
    $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 "%16s %-8s %20s | %8s\n", "Object", "Filter", "Chunk", "N(visit)";
	    $doHeader = 0;
	}
	printf "%16s %-8s %20s | %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%' OR obs_mode LIKE '%BRIGHT%')";
    $query .= "    AND (camProcessedExp.quality = 0)";
    $query .= "    AND (comment NOT LIKE '%twilight%')";
    $query .= "  ) AS expall";
    $query .= "  LEFT JOIN";
    $query .= "  (SELECT * FROM (";
    $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
    $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
    $query .= "    JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
    $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
    $query .= "    WHERE rawExp.dateobs like '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
    $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
    $query .= "  UNION";
    $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
    $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
    $query .= "    JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
    $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
    $query .= "    WHERE rawExp.dateobs like '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
    $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
    $query .= "  ) AS diffgather";
    $query .= "  GROUP BY diffgather.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 WW diffs (obs_mode like %SS% or obs_mode like %BRIGHT%) 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%' OR obs_mode LIKE '%BRIGHT%')";
    $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";
}

# count the number of chunks, and their status:
if ($verbose or $report) {
    print "Current status of SS and Euclid chunks:\n";

    #First find the number of chunks and their names
    my $query = "SELECT groupchunk.chunk,groupchunk.Nquad_firstvisit,group4.Nquad_fourvisits FROM";
    $query .= "   (";
    $query .= "     SELECT dateobs,comment,substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nquad_firstvisit";
    $query .= "     FROM rawExp";
    $query .= "     WHERE dateobs LIKE '$date%' AND exp_id > $refExpID";
    $query .= "     AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING'";
    $query .= "     AND comment LIKE '%visit 1%'";
    $query .= "     GROUP BY chunk";
    $query .= "   ) AS groupchunk";
    $query .= "   LEFT JOIN";
    $query .= "   (";
    $query .= "     SELECT groupobj.chunk,count(*) AS Nquad_fourvisits FROM ";
    $query .= "       (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits";
    $query .= "         FROM rawExp";
    $query .= "         WHERE dateobs LIKE '$date%' AND exp_id > $refExpID AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
    $query .= "         AND obs_mode NOT LIKE 'ENGINEERING' AND comment LIKE '%visit%'";
    $query .= "         GROUP BY object, filter, chunk";
    $query .= "       ) AS groupobj";
    $query .= "     WHERE groupobj.Nvisits = 4 ";
    $query .= "     GROUP BY groupobj.chunk";
    $query .= "   ) AS group4";
    $query .= "   on groupchunk.chunk=group4.chunk ORDER BY dateobs";
 
    my $result = &mysql_select ($query);

    printf "%18s | %13s | %13s | %14s |\n", "chunk", "Nobs_1stvisit", "Nobs_4visits", "Ndiffs_publish"; 
    while (@row = $result->fetchrow_array()) {
          my $Nfirstvisits = 0;
          my $Nfourvisits = 0;

          my $chunkname = $row[0];
          if($row[1] gt 0) {$Nfirstvisits = $row[1]};
          if($row[2] gt 0) {$Nfourvisits = $row[2]};

          my $query2 .= "     SELECT warp_id FROM ";
          $query2 .= "           warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
          $query2 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
          $query2 .= "           AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%'";
          my $result2 = &mysql_select ($query2);

          my $Ndiffs = 0;
          while (@row2 = $result2->fetchrow_array()) {
              my $query3 .= "     SELECT diff_id FROM ";
              $query3 .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp1=warp_id)";
              $query3 .= "           WHERE stack2 IS NULL AND publishRun.state LIKE 'full' AND warp_id='$row2[0]' GROUP BY diff_id";
              my $result3 = &mysql_select ($query3);
              while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}}

              my $query3 .= "     SELECT diff_id FROM ";
              $query3 .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp2=warp_id)";
              $query3 .= "           WHERE stack2 IS NULL AND publishRun.state LIKE 'full' AND warp_id='$row2[0]' GROUP BY diff_id";
              my $result3 = &mysql_select ($query3);
              while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}}
          }

          if ($Nfirstvisits ne $Nfourvisits) {
            if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%4s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/?  ", "chunk not done";}  # chunk	Nquad_firstvisit	Nquad_fourvisits	Ndiffs
            if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%4s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/?  ", "chunk done";}          # chunk	Nquad_firstvisit	Nquad_fourvisits	Ndiffs
          }
          if ($Nfirstvisits eq $Nfourvisits) {
            if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%1s%3s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk not done";}  # chunk	Nquad_firstvisit	Nquad_fourvisits	Ndiffs
            if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%1s%3s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk done";}          # chunk	Nquad_firstvisit	Nquad_fourvisits	Ndiffs
          }
    }

    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;
}
