#! /usr/local/bin/perl -w

use DBI;
use Getopt::Std;

getopts('PC',\%opt);

$dbname = 'gpc1';
sub init_gpc_db {
    use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock';
    my $dbserver = 'ippdb01';
    my $dbuser = 'ippuser';
    my $dbpass = 'ippuser';
    $db = DBI->connect("DBI:mysql:database=${dbname};host=${dbserver};" .
		       "mysql_socket=" . DB_SOCKET(),
		       ${dbuser},${dbpass}, 
		       { RaiseError => 1, AutoCommit => 1}
	) or die "Unable to connect to database $DBI::errstr\n";
    return($db);
}

$db = init_gpc_db();

@targets = ('M31','MD01','MD02','MD03','MD04','MD05','MD06','MD07','MD08','MD09','MD10','ThreePi','STS','SweetSpot');

# potential targets
if (exists($opt{P})) {
    foreach $t (@targets) {
	$comment = $t;
	if ($comment eq 'ThreePi') {
	    $comment = '3Pi';
	}
	$sth = "select filter,DATE_FORMAT(rawExp.dateobs,'%Y-%m-%d') AS day,count(filter) FROM rawExp JOIN newExp using(exp_id) WHERE rawExp.fault = 0 AND exp_type = 'OBJECT' AND comment LIKE '%${comment}%' AND dateobs >= '2010-02-12T00:00:00' GROUP BY day,filter";
	$data_ref = $db->selectall_arrayref( $sth );
	foreach $rr (@{ $data_ref }) {
	    ($filter,$date,$N) = @{ $rr };
	    $filter =~ s/.00000//;
	    $potential{$date}{$t}{$filter} = $N;
	}
    }
    print "|| '''Potential (Nexp):'''\n||\n";
    print '|| Date ||';
    foreach $t (@targets) {
	print "$t ||";
    }
    print "\n";
    
    foreach $d (sort (keys %potential)) {
	print '||' . $d ;
	foreach $t (@targets) {
	    print '|| ' ;
	    $i = 0;
	    foreach $f (sort (keys %{ $potential{$d}{$t} })) {
		if ($i == 0) {
		    print "${f}($potential{$d}{$t}{$f})";
		    $i++;
		}
		else {
		    print ",${f}($potential{$d}{$t}{$f})";
		}
	    }
	}
	print '||' . "\n";
    }
    print "||\n";
}
#queued in chip

$sth = "select rawExp.dateobs,chipRun.state,rawExp.filter,count(chipRun.state),chipRun.data_group,chipRun.dist_group from chipRun JOIN rawExp using (exp_id)  where chipRun.label LIKE '%nightlyscience' GROUP BY data_group,filter,state ORDER BY dateobs";

$data_ref = $db->selectall_arrayref( $sth );

foreach $rr (@{ $data_ref }) {
    ($date,$state,$filter,$N,$data_group,$dist_group) = @{ $rr };
    if ($state eq 'full') {
	($date,$trash) = split /\s+/,$date;
	$filter =~ s/.00000//;
	$chips{$date}{$dist_group}{$filter} = $N;
    }
    if ($state eq 'new') {
	($date,$trash) = split /\s+/,$date;
	$filter =~ s/.00000//;
	$queued{$date}{$dist_group}{$filter} = $N;
    }

}
print "|| '''Queued for Chip (Nexp):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %queued)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $queued{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($queued{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($queued{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print "||\n";
print "|| '''Through Chip (Nexp):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %chips)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $chips{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($chips{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($chips{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print "||\n";

#warps
$sth = "select rawExp.dateobs,warpRun.state,rawExp.filter,count(warpRun.state),warpRun.data_group,warpRun.dist_group from warpRun JOIN fakeRun using (fake_id) JOIN camRun using (cam_id) JOIN chipRun using (chip_id) JOIN rawExp using (exp_id)  where warpRun.label LIKE '%nightlyscience' GROUP BY data_group,filter,state ORDER BY data_group,filter"; 
$data_ref = $db->selectall_arrayref( $sth );

foreach $rr (@{ $data_ref }) {
    ($date,$state,$filter,$N,$data_group,$dist_group) = @{ $rr };
    if ($state eq 'full') {
	($date,$trash) = split /\s+/,$date;
	$filter =~ s/.00000//;
	$data{$date}{$dist_group}{$filter} = $N;
    }
}

print "|| '''Through Warp (Nexp):'''\n||\n";

print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %data)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $data{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($data{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($data{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print "||\n";

# stacks	    


$sth = "select state,count(skycell_id),data_group,dist_group,filter from stackRun where label LIKE '%nightlyscience' group by data_group,filter,state";
$data_ref = $db->selectall_arrayref( $sth );

foreach $rr (@{ $data_ref }) {
    ($state,$N,$data_group,$dist_group,$filter) = @{ $rr };
    $filter =~ s/.00000//;
    if ($state eq 'full') {
	($trash,$date_str) = split /\./, $data_group;
	$date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
	$stacks{$date}{$dist_group}{$filter} = $N;
    }
    if ($state eq 'new') {
	($trash,$date_str) = split /\./, $data_group;
	$date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
	$queuestacks{$date}{$dist_group}{$filter} = $N;
    }	
}
print "|| '''Queued Stack (Nskycells):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %queuestacks)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $queuestacks{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($queuestacks{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($queuestacks{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print "||\n";
print "|| '''Through Stack (Nskycells):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %stacks)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $stacks{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($stacks{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($stacks{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print "||\n";

# distribution

$sth = "select distRun.data_group,dist_group,filter,count(stage_id) from distRun JOIN stackRun ON (stage_id = stack_id) where distRun.label LIKE '%.nightlyscience' AND distRun.state = 'full' GROUP BY distRun.data_group,filter";

$data_ref = $db->selectall_arrayref( $sth );

foreach $rr (@{ $data_ref }) {
    ($data_group,$dist_group,$filter,$N) = @{ $rr };
    $filter =~ s/.00000//;
    ($trash,$date_str) = split /\./, $data_group;
    $date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
    
    $diststacks{$date}{$dist_group}{$filter} = $N;
}
print "|| '''Distributed (Nstack_ids):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %diststacks)) {
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	foreach $f (sort (keys %{ $diststacks{$d}{$t} })) {
	    if ($i == 0) {
		print "${f}($diststacks{$d}{$t}{$f})";
		$i++;
	    }
	    else {
		print ",${f}($diststacks{$d}{$t}{$f})";
	    }
	}
    }
    print '||' . "\n";
}
print '||' . "\n";
# faulted distributed

$sth = "select * from rcDSFileset join distRun using(dist_id) where rcDSFileset.fault > 0 and date(registered) >= '2010-01-01' AND label LIKE 'M%.nightlyscience' order by fs_id asc";
$data_ref = $db->selectall_arrayref( $sth );

foreach $rr (@{ $data_ref }) {
#    ($dist_id,$fs_id,$dest_id,$name,$state,$registered,$fault,$target_id,$stage,$stage_id,$magic_ds_id,$label,$outroot,$outdir,$clean,$no_magic,$state2,$time_stamp,$fault,$data_group,$note) = @{ $rr };
    
    $data_group = ${ $rr }[19];
    ($dist_group,$date_str) = split /\./, $data_group;
    $date = sprintf("%d%d%d%d-%d%d-%d%d",(split //,$date_str));
    
    unless(defined($fault_diststacks{$date}{$dist_group})) {
	$fault_diststacks{$date}{$dist_group} = 0;
    }
    
    $fault_diststacks{$date}{$dist_group} ++;

    if (exists($opt{C})) {
        print STDERR "## disttool -dbname gpc1 -revertfileset -fs_id ${ $rr }[1]\n";
    }

}

print "|| '''Faulted Dists (Nstack_ids):'''\n||\n";
print '|| Date ||';
foreach $t (@targets) {
    print "$t ||";
}
print "\n";

foreach $d (sort (keys %diststacks)) {
    unless (exists($fault_diststacks{$d})) {
	next;
    }
    print '||' . $d ;
    foreach $t (@targets) {
	print '|| ' ;
	$i = 0;
	if (exists($fault_diststacks{$d}{$t})) {
	    print "$fault_diststacks{$d}{$t}";
	}
    }
    print '||' . "\n";
}
print "\n\n";
