#$date = '2017-10-11';
$date = `date --utc --rfc-3339=date`;
chomp $date;
print "\nDATE $date\n";
$date2 = $date;
$date2=~ s/-//g;
$summary = "Report for $date\n";

#print "-- On the summit --\n";

print "\nChecking summitcopy and registration...\n";
print "(summary of exposures)\n";


@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, state,  count(*) from rawExp where dateobs like '$date%' group by exp_type, obs_mode, state;"`;
foreach $t (@summ) {
#    $summary=$summary.$t;
}
foreach $t (@summ) {
    print $t;
    if ($t =~ /new/) {
	$summary=$summary."raw Exposures not complete\n";
    }
    if ($t =~ /NULL/) {
	$summary =$summary."raw Exposures not complete\n";
    }
}

print "\nChecking chip processing (for ones not queued / not full state)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, chipRun.state,  count(*) from rawExp left join chipRun using (exp_id)  where dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' group by obs_mode, chipRun.state;"`;
foreach $t (@summ) {
#    print $t;
    if ($t =~ /new/) {
	$summary = $summary."chip processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."chipRuns not queued up\n$t";
    }
}

print "\nChecking cam processing (for ones not queued/ not full)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, camRun.state,camProcessedExp.fault, camProcessedExp.quality,  count(*) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join camProcessedExp using (cam_id)  where dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' group by obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality;"`;
foreach $t (@summ) {
    print $t;

    if ($t =~ /new/) {
	$summary = $summary."cam processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."camRuns not queued up\n$t";
    }

}

print "\nFinding bad quality camRun exposures...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_name, comment from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join camProcessedExp using (cam_id)  where dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' and (camProcessedExp.quality > 0);"`;
foreach $t (@summ) {
    print $t;

    if ($t =~ /new/) {
	$summary = $summary."cam processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."camRuns not queued up\n$t";
    }
}

print "\nChecking status of fake processing (not queued/ not full state)...\n";
print "This is expected, bad cam quality exps are excluded (camProcessed.quality is null or = 0)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, fakeRun.state,  count(*) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id) left join camProcessedExp using (cam_id) left join fakeRun using (cam_id)  where dateobs like '$date%'  and obs_mode Not like 'ENGINEERING' and exp_type = 'OBJECT' and (camProcessedExp.quality is null  or camProcessedExp.quality = 0 )  group by obs_mode, fakeRun.state;"`;
foreach $t (@summ) {
    #print $t;
    if ($t =~ /new/) {
	$summary = $summary."fake processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."fakeRuns not queued up\n$t";
    }
}

print "\nChecking status of warp processing (not queued/not full state)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, warpRun.state,  count(*) from rawExp left join chipRun using (exp_id) left join camRun using (chip_id)  left join camProcessedExp using (cam_id)  left join fakeRun using (cam_id) left join warpRun using (fake_id)  where dateobs like '$date%' and obs_mode not like 'ENGINEERING' and exp_type = 'OBJECT' and (camProcessedExp.quality is null  or camProcessedExp.quality = 0  ) group by obs_mode, warpRun.state;"`;
foreach $t (@summ) {
    print $t;
    if ($t =~ /new/) {
	$summary = $summary."warp processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."warpRuns not queued up\n$t";
    }

}
#print "counts of things with visit 2 or visit 4 in the comment string\n";

#@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select exp_type, obs_mode, warpRun.state,  count(*) 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)  where dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' and (rawExp.comment like '%visit 2%' or rawExp.comment like '%visit 4%') group by obs_mode, warpRun.state;"`;
#foreach $t (@summ) {
#    print $t;
#}

print "\nCalculating expected diff processing excluding camRuns with bad quality...\n";
print "note: multiple exposures with same comment string (xyz visit a) are treated as 1 exposure\n";
#@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select c, count(*) from (select object, count(*) as c from (select object from rawExp where  dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' and rawExp.comment like '%visit%' group by comment) as a group by object) as d group by c;"`;
#@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select c, count(*) from (select object, filter, count(*) as c from (select object , filter from rawExp  left join chipRun using (exp_id) left join camRun using (chip_id) left join camProcessedExp using (cam_id) where  dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' and rawExp.comment like '%visit%' and (camProcessedExp.quality is null  or camProcessedExp.quality = 0 ) group by comment, filter) as a group by object, filter) as d group by c;"`;
#new query to handle overlapping chunks
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select c, count(*) from (select object, filter, chunk, count(*) as c from (select object , filter, substr(comment, 1, position(' ' in comment)) as chunk from rawExp  left join chipRun using (exp_id) left join camRun using (chip_id) left join camProcessedExp using (cam_id) where  dateobs like '$date%' and exp_type = 'OBJECT' and obs_mode not like 'ENGINEERING' and rawExp.comment like '%visit%' and (camProcessedExp.quality is null  or camProcessedExp.quality = 0 ) group by comment, filter,substr(comment, 1, position(' ' in comment))) as a group by object, filter, chunk  ) as d group by c;"`;



foreach $t (@summ) {
#    print $t;
    my @split = split (/\s+/, $t);
#    print $split[0], $split[1];
    if ($split[0] =~ /c/ ){
	$sum = 0;
    }elsif ($split[0] == 1) {
	print "$split[1] has 1 visit (visit A in comments), not diffable\n";
    } elsif ($split[0] == 2) {
	print "$split[1] have 2 visits (visit A and visit B in comment), expect $split[1] diff(s)\n";
	$sum= $sum+$split[1];
    } elsif ($split[0] == 3) {
	print "$split[1] have 3 visits (visit A/B/C in comment), expect 2* $split[1] diffs\n";
	$sum= $sum+$split[1]*2;
    } elsif ($split[0] == 4) {
	print "$split[1] have 4 visits (visit A/B/C/D in comment), expect 2* $split[1] diffs\n";
    	$sum= $sum+$split[1]*2;
	     } else {
		 print "I don't understand, there are more visits than I expect\n";
	     }
    
}

print "\nNumber of diffRuns expected based on rawExp: $sum\n\n";
$summary = $summary."Number of diffRuns expected based on rawExp: $sum\n";

print "Checking diff processing current status (no WS, not queued, not full state)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select label, state, count(*) from diffRun where data_group like '%$date2' and label not like '%WS.nigh%' group by label, state;"`;
foreach $t (@summ) {
    print $t;
    if ($t =~ /new/) {
	$summary = $summary."diff processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."diffRuns not queued up\n$t";
    }
}

print "\nFinding number of diffRuns that can potentially be published (has at least 1 skycell with quality = 0 or quality is NULL)\n\n";
$diffpot = `mysql -h scidbm -u ipp -pipp gpc1 -e "select count(*) from (select label, state, count(*) from diffRun left join diffSkyfile using (diff_id) where data_group like '%$date2%' and label not like '%WS.nigh%' and (quality is NULL or quality = 0) group by label, state, diff_id) as a;"`;
chomp $diffpot;
print "we expect $diffpot diffs to be published\n";




print "\nChecking publishing current status (no WS, not queued, not full state)...\n";
@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select diffRun.label, publishRun.state, count(*) from diffRun left join publishRun on diff_id = stage_id  where data_group like '%$date2' and diffRun.label not like '%WS.nigh%' group by diffRun.label, publishRun.state;"`;
foreach $t (@summ) {
    print $t;
    if ($t =~ /new/) {
	$summary = $summary."publish processing not complete\n$t";
    }
    if ($t =~ /NULL/) {
	$summary = $summary."publishRuns not queued up\n$t";
    }
}

@summ = `mysql -h scidbm -u ipp -pipp gpc1 -e "select count(*) from diffRun left join publishRun on diff_id = stage_id  where data_group like '%$date2' and diffRun.label not like '%WS.nigh%' and publishRun.state = 'full'"`;
$sum2=0;
foreach $t (@summ) {
    chomp $t;
    if ($t =~/count/) {

    } else {
    print "\nTotal published = $t, expected $sum\n"; ;
    $summary = $summary."Total published = $t, expected $sum based on good quality diffs, or $diffpot (excluding bad quality)\n"; 
    }
}


print "\nRunning unpublished query (mark's)...\n";

@summ = `mysql -hscidbm -uipp -pipp gpc1 -B -e " select expall.exp_name,expall.comment,expall.dateobs from ( select exp_name,comment,dateobs from rawExp where dateobs like '$date%' and obs_mode like '%SS%') expall left join ( select exp_name from diffRun join diffInputSkyfile using (diff_id) join publishRun join 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) where ( warp1=warp_id or warp2=warp_id ) and diffRun.reduction like 'SWEETSPOT' and stack2 is NULL and diffRun.data_group like '%SS.$date2' and diffRun.diff_id=stage_id group by exp_name ) exppub on expall.exp_name=exppub.exp_name where exppub.exp_name is NULL ; "`;

foreach $t (@summ) {
print $t;
}



print "\n\n\n";


print $summary;
