#!/usr/bin/perl -w

package czartool::CzarDb;

use warnings;
use strict;

use File::Temp;

my @stages = ("burntool", "chip", "cam", "fake", "warp", "stack", "diff", "magic", "magicDS", "dist"); # TODO put elsewhere

use base 'czartool::MySQLDb';
our @ISA = qw(czartool::MySQLDb);    # inherits from MySQLDb

use czartool::StageMetrics;

###########################################################################
#
# Constructor (overrides superclass)
#
###########################################################################
sub new {
    my ($class) = @_;

    # Call the constructor of the parent class
    my $self = $class->SUPER::new($_[1], $_[2], $_[3], $_[4], $_[5], $_[6]);

    bless $self, $class;

    $self->update();    
    return $self;
}

###########################################################################
#
# Gets current_labels table
#
###########################################################################
sub getCurrentLabels {
    my ($self, $server, $labels) = @_;

    my $query = $self->{_db}->prepare(<<SQL);

    SELECT label
        FROM current_labels
        WHERE server LIKE '$server';
SQL

    if (!$query->execute) {return 0;}

    ${$labels} = $query->fetchall_arrayref();

    return 1;
}

###########################################################################
#
# Gets stdscience lables active in the provided time period
# TODO ugly hack to avoind getting update labels
#
###########################################################################
sub getStdscienceLabelsInTimePeriod {
    my ($self, $begin, $end, $labels) = @_;

    my $query = $self->{_db}->prepare(<<SQL);

    SELECT DISTINCT label
        FROM chip 
        WHERE timestamp >= '$begin' 
        AND timestamp <= '$end' 
        AND label NOT LIKE '%_ud%'
        AND label NOT LIKE 'update%'
        AND label NOT LIKE 'all_%_labels'
SQL

    if (!$query->execute) {return 0;}

    ${$labels} = $query->fetchall_arrayref();

    return 1;
}



###########################################################################
#
# Updates server table
#
###########################################################################
sub updateServerStatus {
    my ($self, $server, $alive, $running) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO servers
        (server, alive, running)
        VALUES
        ('$server', $alive, $running);
SQL

       $query->execute;
}

###########################################################################
#
# Gets revert status for this stage
#
###########################################################################
sub getRevertStatus {
    my ($self, $stage, $reverting) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT reverting 
        FROM reverts
         WHERE stage LIKE '$stage'; 
SQL

    $query->execute;
    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Updates revert status for this stage
#
###########################################################################
sub updateRevertStatus {
    my ($self, $stage, $reverting) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
     UPDATE reverts 
         SET reverting = $reverting 
         WHERE stage LIKE '$stage';
SQL

    $query->execute;
}

###########################################################################
#
# Sets priority for this label
#
###########################################################################
sub setLabelPriority {
    my ($self, $label, $priority) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
     UPDATE current_labels 
         SET priority = $priority 
         WHERE label LIKE '$label' 
         AND server LIKE 'stdscience';
SQL

    $query->execute;
}

###########################################################################
#
# Cleans out server_dates table
#
###########################################################################
sub emptyServerDates {
    my ($self, $server, $dates) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    DELETE FROM server_dates;
SQL

    $query->execute;
}

###########################################################################
#
# Updates current_labels table
#
###########################################################################
sub updateServerDates {
    my ($self, $server, $dates) = @_;

    my $size = scalar @{$dates};
    if ($size < 1) { return; }

    my $date = undef;

    foreach $date (@{$dates}) {

        my $query = $self->{_db}->prepare(<<SQL);
        INSERT INTO server_dates
            (server, date)
            VALUES
            ('$server', '$date');
SQL

       $query->execute;
    }
}

###########################################################################
#
# Updates nightlyscience table
#
###########################################################################
sub updateNightlyScience {
    my ($self, $status) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    DELETE FROM nightlyscience;
SQL

    $query->execute;

    $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO nightlyscience
        (status)
        VALUES
        ('$status');
SQL

       $query->execute;
}

###########################################################################
#
# Updates current_labels table
#
###########################################################################
sub updateCurrentLabels {
    my ($self, $server, $labels) = @_;

    my $size = scalar @{$labels};
    if ($size < 1) { return; }

    my $query = $self->{_db}->prepare(<<SQL);
    DELETE FROM current_labels WHERE server LIKE '$server';
SQL

    $query->execute;

    my $label = undef;

    foreach $label (@{$labels}) {

        my $query = $self->{_db}->prepare(<<SQL);
        INSERT INTO current_labels
            (server, label)
            VALUES
            ('$server', '$label');
SQL

       $query->execute;
    }
}

###########################################################################
#
# Updates hosts table
#
###########################################################################
sub updateHost {
    my ($self, $host, $total, $available, $used, $readable, $writable) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    DELETE FROM hosts WHERE host LIKE '$host';
SQL

    $query->execute;
    $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO hosts
        (host, total, available, used, readable, writable)
        VALUES
        ('$host', $total, $available, $used, $readable, $writable);
SQL
    $query->execute;

}

###########################################################################
#
# Inserts new cluster space info
#
###########################################################################
sub insertNewClusterSpace {
    my ($self, $total, $available, $used, $hostsOver98) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO cluster_space
        (total, available, used, hostsOver98)
        VALUES
        ( $total, $available, $used, $hostsOver98);
SQL

        $query->execute;
}

###########################################################################
#
# Inserts new time data into relevant table for a given label
#
###########################################################################
sub insertNewTimeData {
    my ($self, $stage, $label, $pending, $processed, $faults) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO $stage
        (label, pending, processed, faults)
        VALUES
        ('$label', $pending, $processed, $faults);
SQL

        $query->execute;
}

###########################################################################
#
# Gets time min/max/diff for a given period
#
###########################################################################
sub getTimeMinMaxDiff {
    my ($self, $label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        DATE_FORMAT(MAX(timestamp),'$self->{_dateFormat}'), 
        DATE_FORMAT(MIN(timestamp),'$self->{_dateFormat}'),
        TIME_TO_SEC(TIMEDIFF(max(timestamp ), min(timestamp)))
            FROM $stage 
            WHERE label LIKE '$label'
            AND timestamp >= '$fromTime' AND timestamp <= '$toTime'; 
SQL

    if (!$query->execute) {return 0;}

    my ($_timeDiff);
    (${$maxX}, ${$minX}, $_timeDiff) = $query->fetchrow_array();

    if ($_timeDiff > ${$timeDiff}) {${$timeDiff} = $_timeDiff;}

return 1;
}

###########################################################################
#
# Returns the start and end times during one day for the provided label and start/end stages
#
###########################################################################
sub getDayTimings {
    my ($self, $label, $startStage, $endStage, $begin, $started, $finished, $timeTaken) = @_;

    my $end = $self->addInterval($begin, "1 DAY");

    my $startStageMetrics = new czartool::StageMetrics($startStage, $label, $begin, $end);
    my $endStageMetrics = new czartool::StageMetrics($endStage, $label, $begin, $end);

    if (!$self->runAnalysis($startStageMetrics)) {return 0;}
    if (!$self->runAnalysis($endStageMetrics)) {return 0;}

    ${$started} = $startStageMetrics->getStarted();
    ${$finished} = $endStageMetrics->getFinished100();

    if (!${$started} || !${$finished}) {${$timeTaken} = undef;}
    else {${$timeTaken} = $self->diffTimes(${$finished}, ${$started});}

    return 1;
}

###########################################################################
#
# Analysis this stage between these times 
#
###########################################################################
sub runAnalysis {
    my ($self, $stageMetrics) = @_;

    if (!$stageMetrics) {return 0;}

    my $stage = $stageMetrics->getStage();
    my $label = $stageMetrics->getLabel();
    my $fromTime = $stageMetrics->getStartTime();
    my $toTime = $stageMetrics->getEndTime();

    $toTime = $self->addInterval($toTime, "20 MINUTE"); # TODO dodgy

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        timestamp, pending, faults, processed 
        FROM $stage 
        WHERE label LIKE '$label' 
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime' 
        ORDER BY timestamp;
SQL

    my $lastProcessed = -1;
    my $lastPending = -1;
    my $lastFaults = -1;
    my $lastTimestamp = undef;
    my ($linearProcessed, $linearPending, $linearFaults);

    # first, count toatl processed in time window so that we can find time of 50%, 75%, 90% etc
    $query->execute;
    my $totalProcessed = 0;
    my $firstRow = 1;
    while (my @row = $query->fetchrow_array()) {
        my ($thisTimestamp, $thisPending, $thisFaults, $thisProcessed) = @row;

        # get linear values
        if($lastProcessed != -1 && $thisProcessed > $lastProcessed) {
            
            $totalProcessed = $totalProcessed + ($thisProcessed - $lastProcessed);
        }

        $lastProcessed = $thisProcessed;
        if ($firstRow) {$stageMetrics->setInitialPending($thisPending); $firstRow = 0;}
    }

    # nothing to do here
    if (!$totalProcessed) {return 1;}

    my $percent25 = $totalProcessed * 0.25;
    my $percent50 = $totalProcessed * 0.5;
    my $percent75 = $totalProcessed * 0.75;
    my $percent90 = $totalProcessed * 0.9;
    my $percent95 = $totalProcessed * 0.95;

    my ($derivProcessed, $derivPending, $derivFaults);
    my $notProcessing = undef;
    my $howLongNotProcessing = undef;
    my $notProcessingLongerThanInterval = 0;
    my $stuck = undef;
    my $howLongStuck = undef;
    my $stuckLongerThanInterval = 0;
    my $stuffToDo = undef;
    my $interval = "00:20:00"; # TODO dodgy - see above
    my $started = undef;
    my $finished = undef;
   
    my ($have25, $have50, $have75, $have90, $have95, $have100) = 0;

    # now, loop again, this time recording more data
    $query->execute;
    $linearProcessed = 0;
    $lastProcessed = -1;
    while (my @row = $query->fetchrow_array()) {
        my ($thisTimestamp, $thisPending, $thisFaults, $thisProcessed) = @row;

        # get linear values
        if($lastProcessed != -1 && $thisProcessed > $lastProcessed) {
            
            $linearProcessed = $linearProcessed + ($thisProcessed - $lastProcessed);
        }
        $linearPending = $thisPending;
        $linearFaults = $thisFaults;

        if (!$have25 && $linearProcessed >= $percent25) {$stageMetrics->setFinished25($thisTimestamp); $have25 = 1;}
        if (!$have50 && $linearProcessed >= $percent50) {$stageMetrics->setFinished50($thisTimestamp); $have50 = 1;}
        if (!$have75 && $linearProcessed >= $percent75) {$stageMetrics->setFinished75($thisTimestamp); $have75 = 1;}
        if (!$have90 && $linearProcessed >= $percent90) {$stageMetrics->setFinished90($thisTimestamp); $have90 = 1;}
        if (!$have95 && $linearProcessed >= $percent95) {$stageMetrics->setFinished95($thisTimestamp); $have95 = 1;}

        # get first derivative values
        if (defined $lastTimestamp) {

            my $timeDiff = $self->diffTimesInSecs($thisTimestamp, $lastTimestamp);
                
            if ($thisProcessed >= $lastProcessed){
                $derivProcessed = abs($thisProcessed - $lastProcessed)/$timeDiff; 
            }
            else {$derivProcessed = 0;}
            $derivPending = abs($thisPending - $lastPending)/$timeDiff;    
            $derivFaults = abs($thisFaults - $lastFaults)/$timeDiff;    
        }
        else {
        
            $derivProcessed = 0;
            $derivPending = 0;
            $derivFaults = 0;
        }

        # analysis
        if (defined $lastTimestamp) {

            # anything to do?
            $stuffToDo = $linearPending - $linearFaults;

            # we are not processing
            if ($derivProcessed < 0.001 ) {

                if (!defined $notProcessing) {$notProcessing = $lastTimestamp;}
                if ($stuffToDo && !defined $stuck) {$stuck = $lastTimestamp;}
            }

            # we are processing
            else {
                if (defined $notProcessing) {
                    
                    $notProcessing = undef; 
                    $stuck = undef; 
                }
                if (!defined $started) {

                    $started = $lastTimestamp;
                    $finished = undef; 
                }
            }

            # how long stuck?
            if (defined $stuck) {

                $howLongStuck = $self->diffTimes($lastTimestamp, $stuck);
                $stuckLongerThanInterval = $self->isBefore($interval, $howLongStuck)
            }
            else {

                $howLongStuck = 0;
                $stuckLongerThanInterval = 0;
            }

            # how long not processing?
            if (defined $notProcessing) {
                
                $howLongNotProcessing = $self->diffTimes($lastTimestamp, $notProcessing);
                $notProcessingLongerThanInterval = $self->isBefore($interval, $howLongNotProcessing);
            }
            else {
            
                $howLongNotProcessing = 0;
                $notProcessingLongerThanInterval = 0;
            }

            if ($stuffToDo) {$finished = undef;}
            elsif (!defined $finished && $started && $notProcessingLongerThanInterval) {

                $finished = $notProcessing;
            }
        }

        $lastProcessed = $thisProcessed;
        $lastPending = $thisPending;
        $lastFaults = $thisFaults;
        $lastTimestamp = $thisTimestamp;
    }

    $stageMetrics->setProcessed($linearProcessed);
    $stageMetrics->setFinalPending($stuffToDo);
    $stageMetrics->setFaults($lastFaults);


    if (!defined $finished) {

        $stageMetrics->setFinished25(undef);
        $stageMetrics->setFinished50(undef);
        $stageMetrics->setFinished75(undef);
        $stageMetrics->setFinished90(undef);
        $stageMetrics->setFinished95(undef);
        $stageMetrics->setFinished100(undef);
        $stageMetrics->setTotalTime($self->diffTimes($lastTimestamp, $started));
    }

    if (defined $finished) {

        $stageMetrics->setFinished100($finished);
        $stageMetrics->setTotalTime($self->diffTimes($finished, $started));
    }


    if (!defined $started){
    
        $stageMetrics->setStarted(undef);
    }
    else {
    
        my $rate = ($stageMetrics->getProcessed()/$self->getTimeInSecs($stageMetrics->getTotalTime) ) * 3600.0;
        $stageMetrics->setRate($rate);
        $stageMetrics->setStarted($started);
    }
    if ($stuck && $stuckLongerThanInterval && !defined $finished) {

        $stageMetrics->setStuck($stuck);
    }
    else {
    
        $stageMetrics->setStuck(undef);
    }

    return 1;
}
###########################################################################
#
# Gets time series data and stores it to temp file
#
###########################################################################
sub createTimeSeriesData {
    my ($self, $label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff, $dataFile, $isLog, $showCleanup, $firstDeriv) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        MIN(processed)
        FROM $stage 
        WHERE label LIKE '$label'
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime'; 
SQL

    if (!$query->execute) {return 0;}
    my $minProcessed = scalar  $query->fetchrow_array();
    if (!defined $minProcessed) {return 0;}

    $self->getTimeMinMaxDiff($label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff);

    $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        timestamp, pending, faults, processed 
        FROM $stage 
        WHERE label LIKE '$label' 
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime' 
        ORDER BY timestamp;
SQL

    $query->execute;

    my $tmpFile = File::Temp->new( TEMPLATE => "czarplot_gnuplot_".$label."_".$stage."_t.XXXXX", DIR => '/tmp', SUFFIX => 'dat');
    $tmpFile->unlink_on_destroy( 0 );
    ${$dataFile} = $tmpFile->filename;
    open (GNUDAT, ">${$dataFile}") or print "* Problem opening gnuplot data file for plot for '$label' '$stage'\n";

    my $lastProcessed = -1;
    my $lastPending = -1;
    my $lastFaults = -1;
    my $lastTimestamp = undef;
    my ($linearProcessed, $linearPending, $linearFaults);
    my ($logProcessed, $logPending, $logFaults);
    my ($derivProcessed, $derivPending, $derivFaults);
    my $timestamp = undef;
    
    $linearProcessed = 0;
    my $someData = 0;
    while (my @row = $query->fetchrow_array()) {
        my ($thisTimestamp, $thisPending, $thisFaults, $thisProcessed) = @row;

        # get linear values
        if ($showCleanup) {
        
            $linearProcessed = $thisProcessed - $minProcessed;
        }
        elsif($lastProcessed != -1 && $thisProcessed > $lastProcessed) {
            
            $linearProcessed = $linearProcessed + ($thisProcessed - $lastProcessed);
        }
        $linearPending = $thisPending;
        $linearFaults = $thisFaults;

        # get first derivative values
        if (defined $lastTimestamp) {

            my $timeDiff = $self->diffTimesInSecs($thisTimestamp, $lastTimestamp);
                
            if ($thisProcessed >= $lastProcessed && $timeDiff){
                $derivProcessed = abs($thisProcessed - $lastProcessed)/$timeDiff; 
            }
            else {$derivProcessed = 0;}
            $derivPending = 0;
            $derivFaults = 0;
        #    $derivPending = abs($thisPending - $lastPending)/$timeDiff;    
        #    $derivFaults = abs($thisFaults - $lastFaults)/$timeDiff;    
        }
        else {
        
            $derivProcessed = 0;
            $derivPending = 0;
            $derivFaults = 0;
        }

        # get log values
        $logProcessed = ($linearProcessed < 0) ? 0 : $linearProcessed;
        $logPending =  ($linearPending < 0) ? 0 : $linearPending;
        $logFaults =  ($linearFaults < 0) ? 0 : $linearFaults;
        $logProcessed = log($logProcessed + 1)/log(10);
        $logPending = log($logPending + 1)/log(10);
        $logFaults = log($logFaults + 1)/log(10);

        # print to data file
        $timestamp = $self->getFormattedDate($thisTimestamp);
        if ($firstDeriv) {print GNUDAT "$timestamp $derivPending $derivFaults $derivProcessed\n";}
        elsif($isLog) {print GNUDAT "$timestamp $logPending $logFaults $logProcessed\n";}
        else {print GNUDAT "$timestamp $linearPending $linearFaults $linearProcessed\n";}

        if ($linearPending > 0 || $linearFaults > 0 || $linearProcessed > 0) {$someData = 1;}

        $lastProcessed = $thisProcessed;
        $lastPending = $thisPending;
        $lastFaults = $thisFaults;
        $lastTimestamp = $thisTimestamp;
    }

    close(GNUDAT) or print "* Problem closing gnuplot data file for plot for '$label' '$stage'\n";

    #return $someData;
}

###########################################################################
#
# Returns number of faults at a particular time in the past for a given label and stage
#
###########################################################################
sub countFaultsInPast {
    my ($self, $label, $stage, $interval) = @_; # TODO use time not interval

    # get earliest time for this label
    my $query = $self->{_db}->prepare(<<SQL);
    SELECT GREATEST(MIN(timestamp), now() - INTERVAL $interval) 
        FROM $stage
        WHERE label LIKE '$label'; 
SQL
    $query->execute;

my $timestamp = $query->fetchrow_array(); 

    $query = $self->{_db}->prepare(<<SQL);
    SELECT faults 
        FROM $stage
        WHERE label LIKE '$label' 
        AND timestamp <= '$timestamp' LIMIT 1;
SQL
    $query->execute;

return $query->fetchrow_array();
}

###########################################################################
#
# Returns the number of exposures that are pending right now for a given stage and label
#
###########################################################################
sub countPendingNow {
    my ($self, $label, $stage) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT pending - faults 
        FROM $stage 
        WHERE label LIKE '$label' 
        ORDER BY timestamp DESC LIMIT 1;
SQL
        $query->execute;

    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Creates histogram data
#
###########################################################################
sub countProcessedPendingAndFaults {
    my ($self, $label, $stage, $fromTime, $toTime, $processed, $pending, $faults) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        pending, faults 
        FROM $stage
        WHERE label LIKE '$label'
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime'
        ORDER BY timestamp DESC LIMIT 1;
SQL

    if (!$query->execute) {return 0;}

    (${$pending}, ${$faults}) = $query->fetchrow_array();    

    ${$processed} = $self->countProcessed($label, $stage, $fromTime, $toTime);

    if (!${$pending}) {${$pending} = 0;}
    if (!${$faults}) {${$faults} = 0;}
    if (!${$processed}) {${$processed} = 0;}

    return 1;
}

###########################################################################
#
# Gets host data and stores it to temp file
#
###########################################################################
sub createHostsData {
    my ($self, $limit, $tmpFile) = @_;

    open (GNUDAT, ">".$tmpFile->filename);

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        host, used, available, writable, readable 
        FROM hosts
        ORDER BY host;
SQL

    $query->execute;

    my $underLimit;

    # loop round results
    while (my @row = $query->fetchrow_array()) {

        my ($host, $used, $available, $writable, $readable) = @row;
   
        if (($used/($used+$available))*100 > $limit) {$underLimit = 0;}
        else {$underLimit = 1;}

        # Col 1: host
        print GNUDAT "$host";
        
        # Col 2: available, readable used space under limit
        if ($readable && $underLimit) {print GNUDAT " $used";}
        else {print GNUDAT " 0";}

        # Col 3: available, readable used space OVER limit
        if ($readable && !$underLimit) {print GNUDAT " $used";}
        else {print GNUDAT " 0";}

        # Col 4: NOT available used space
        if (!$readable) {print GNUDAT " $used";}
        else {print GNUDAT " 0";}

        # Col 5: available writable space
        if($writable) {print GNUDAT " $available";}
        else {print GNUDAT " 0";}

        # Col 6: NOT available writable space
        if(!$writable) {print GNUDAT " $available";}
        else {print GNUDAT " 0";}

        print GNUDAT "\n";
    }

    close(GNUDAT);
}

###########################################################################
#
# Gets total used cluster space as a percentage
#
############################################################################
sub getTotalClusterStorageAsPercentage {
    my ($self) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT used/total*100 
        FROM cluster_space 
        ORDER BY timestamp DESC LIMIT 1;
SQL
    $query->execute;
    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Gets time series data for cluster storage and saves it to file 
#
###########################################################################
sub createStorageTimeSeriesData {
    my ($self, $tmpFile, $fromTime, $toTime, $minX, $maxX, $minY, $maxY, $timeDiff) = @_;

    open (GNUDAT, ">".$tmpFile->filename);

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        MAX(available), MIN(available),  
        DATE_FORMAT(MAX(timestamp),'$self->{_dateFormat}'), 
        DATE_FORMAT(MIN(timestamp),'$self->{_dateFormat}'),
        TIME_TO_SEC(TIMEDIFF(max(timestamp ), min(timestamp)))
            FROM cluster_space 
            WHERE timestamp >= '$fromTime' AND timestamp <= '$toTime'; 
SQL

    if (!$query->execute) {return undef;}

    (${$maxY}, ${$minY}, ${$maxX}, ${$minX}, ${$timeDiff}) = $query->fetchrow_array();


    if (!${$maxY} || !${$minY} || !${$maxX} || !${$minX} || !${$timeDiff}) {return 0;}

    $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        DATE_FORMAT(timestamp, '$self->{_dateFormat}'), available 
        FROM cluster_space 
        WHERE timestamp >= '$fromTime' AND timestamp <= '$toTime' 
        ORDER BY timestamp;
SQL

    $query->execute;

    # loop round results
    while (my @row = $query->fetchrow_array()) {

        my ($timestamp, $available) = @row;
        print GNUDAT "$timestamp $available\n";
    }

    close(GNUDAT);

    return 1;
}

###########################################################################
#
# Creates data for processing rate plots
#
###########################################################################
sub createProcessingRateData {
    my ($self, $stage, $label, $begin, $end, $interval, $dataFile) = @_;

    my $startTime = $begin;
    my $endTime;
    my $quit = 0;
    my $processed;
    my $pending;
    my $faults;
    my $timestamp;

    my $tmpFile = File::Temp->new( TEMPLATE => "czarplot_gnuplot_".$label."_".$stage."_r.XXXXX", DIR => '/tmp', SUFFIX => 'dat');
    $tmpFile->unlink_on_destroy(0);
    ${$dataFile} = $tmpFile->filename;

    open (GNUDAT, ">${$dataFile}") or print "* Problem opening gnuplot data file for plot for '$label' '$stage'\n";
    my $cleanupCarry = 0;
    my $someData = 0;
    while(1) {

        if (!$self->isBefore($startTime, $end)) {last;}
        $endTime = $self->addInterval($startTime, $interval);
        if (!$self->countProcessedPendingAndFaults($label, $stage, $startTime, $endTime, \$processed, \$pending, \$faults)) {}
        $timestamp = $self->getFormattedDate($endTime);
        if (!$processed) {$processed = 0;}
        print GNUDAT "$timestamp $processed 0 0\n";

        if ($processed > 0 ) {$someData = 1;}

        $startTime = $endTime;
    }

    close(GNUDAT) or print "* Problem closing gnuplot data file for rate plot for '$label' '$stage'\n";
}

###########################################################################
#
# Gets count of processed stuff in a given time period 
#
###########################################################################
sub countProcessed {
    my ($self, $label, $stage, $begin, $end) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        processed 
        FROM $stage
        WHERE label LIKE '$label' 
        AND timestamp >= '$begin' 
        AND timestamp < '$end' 
SQL
        $query->execute;

    my $processedArray = $query->fetchall_arrayref();

    my $processed;
    my $thisCount = -1;
    my $lastCount = -1;
    my $count = 0;
    foreach $processed ( @{$processedArray} ){

        ($thisCount) = @{$processed};

        if ($thisCount > $lastCount && $lastCount != -1) {$count = $count + ($thisCount -  $lastCount);}
        $lastCount = $thisCount;
    }

    return $count;
}

###########################################################################
#
# Deletes all but one row per interval from all stage tables for all labels between the two dates
#
###########################################################################
sub cleanupDateRange {
    my ($self, $startDay, $endDay, $interval) = @_;

    my $thisDay = $startDay;
    my $quit = 0;
    while(!$quit) {

        if (!$self->isBefore($thisDay, $endDay)) {

            $quit = 1;
        }

        print "* Running cleanup for $thisDay with an interval of $interval\n";
        $self->cleanupADay($thisDay, $interval);
        $thisDay = $self->addInterval($thisDay, "1 DAY");

    }
}
###########################################################################
#
# Deletes all but one row per interval from all tables for the provided date range
# TODO this is very clumsy, I just have time to thinmk of something more elegant
#
###########################################################################
sub cleanupADay {
    my ($self, $startDay, $interval) = @_;

    my $endDay =  $self->addInterval($startDay, "1 DAY");

    my $labels = undef;
    my $fromTime = $startDay;
    my $toTime = undef;
    my $totalDeleted = undef;
    my $quit = 0;
    my $query = undef;
    while(!$quit) {

        $toTime = $self->addInterval($fromTime, $interval);
        if (!$self->isBefore($toTime, $endDay)) {

            $toTime = $endDay;
            $quit = 1;
        }

        my $stage = undef;
        $totalDeleted = 0;
        foreach $stage (@stages) {

            if (!$self->getLabelsInThisTimePeriod($stage, $fromTime, $toTime, \$labels)) {next;}

            my $label = undef;
            my $row = undef;
            foreach $row ( @{$labels} ) {
                my ($label) = @{$row};

                $query = $self->{_db}->prepare(<<SQL);
                SELECT COUNT(*) 
                    FROM $stage 
                    WHERE timestamp > '$fromTime'
                    AND timestamp <= '$toTime'
                    AND label = '$label' 
SQL

                    $query->execute;

                my $toDelete = scalar $query->fetchrow_array() - 1;
                if ($toDelete < 1) {next;}

                $query = $self->{_db}->prepare(<<SQL);
                DELETE FROM $stage 
                    WHERE timestamp > '$fromTime' 
                    AND timestamp <= '$toTime' 
                    AND label = '$label' ORDER BY timestamp DESC LIMIT $toDelete
SQL
                    $query->execute;

                $totalDeleted += $toDelete;
            }

        }

        # servers table
        my $servers = undef;
        if ($self->getServers(\$servers)) {

            my $server = undef;
            my $row = undef;
            foreach $row ( @{$servers} ) {
                my ($server) = @{$row};

                $query = $self->{_db}->prepare(<<SQL);
                SELECT COUNT(*) 
                    FROM servers 
                    WHERE timestamp > '$fromTime'
                    AND timestamp <= '$toTime'
                    AND server = '$server' 
SQL
                    $query->execute;

                my $toDelete = scalar $query->fetchrow_array() - 1;
                if ($toDelete > 0) {

                    $query = $self->{_db}->prepare(<<SQL);
                    DELETE FROM servers 
                        WHERE timestamp > '$fromTime' 
                        AND timestamp <= '$toTime' 
                        AND server = '$server' ORDER BY timestamp DESC LIMIT $toDelete
SQL
                        $query->execute;

                    $totalDeleted += $toDelete;
                }
            }
        }

        # now deal with cluster_space table
        $query = $self->{_db}->prepare(<<SQL);
        SELECT COUNT(*) 
            FROM cluster_space 
            WHERE timestamp > '$fromTime'
            AND timestamp <= '$toTime'
SQL
            $query->execute;

        my $toDelete = scalar $query->fetchrow_array() - 1;
        if ($toDelete > 0) {

            $query = $self->{_db}->prepare(<<SQL);
            DELETE FROM cluster_space 
                WHERE timestamp > '$fromTime' 
                AND timestamp <= '$toTime' 
                ORDER BY timestamp DESC LIMIT $toDelete
SQL
                $query->execute;

            $totalDeleted += $toDelete;
        }

        print "   * Deleted $totalDeleted between $fromTime and  $toTime\n";
        $fromTime = $toTime;
    }
}

###########################################################################
#
# Optimizes all tables that need to be optimized 
#
###########################################################################
sub optimize {
    my ($self) = @_;

    my $stage = undef;
    foreach $stage (@stages) {

        $self->optimizeTable($stage);
    }

    $self->optimizeTable("cluster_space");
    $self->optimizeTable("servers");
}

###########################################################################
#
# Returns if a particular server has been stopped for a certain interval 
#
###########################################################################
sub isServerStopped {
    my ($self, $server, $interval, $since) = @_;

    return $self->getServerStatus($server, $interval, $since, "running");
}

###########################################################################
#
# Returns if a particular server has been down for a certain interval 
#
###########################################################################
sub isServerDown {
    my ($self, $server, $interval, $since) = @_;

    return $self->getServerStatus($server, $interval, $since, "alive");
}

###########################################################################
#
# Returns if a particular server has been down for a certain interval 
#
###########################################################################
sub getServerStatus {
    my ($self, $server, $interval, $since, $mode) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT COUNT(*) 
        FROM servers
        WHERE server = '$server' 
        AND timestamp > now() - INTERVAL $interval
SQL

        $query->execute;
    my $numOfReadings = scalar $query->fetchrow_array();

    if ($numOfReadings == 0) {return 0;}

    $query = $self->{_db}->prepare(<<SQL);
    SELECT COUNT(*) 
        FROM servers
        WHERE server = '$server' 
        AND timestamp > now() - INTERVAL $interval
        AND !$mode
SQL

        $query->execute;
    my $numNotRunning = scalar $query->fetchrow_array();

    if ($numOfReadings != $numNotRunning) {return 0;}

    $query = $self->{_db}->prepare(<<SQL);
    SELECT 
        timestamp
        FROM servers
        WHERE server = '$server'
        AND $mode 
        ORDER BY timestamp DESC 
        LIMIT 1

SQL
    $query->execute;
    ${$since} = scalar $query->fetchrow_array();

    return 1;
}

###########################################################################
#
# Returns an array of servers 
#
###########################################################################
sub getServers {
    my ($self, $servers) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT DISTINCT server
        FROM servers
SQL

        if (!$query->execute) {

            return 0;
        }

    ${$servers} = $query->fetchall_arrayref();

    return 1;
}

###########################################################################
#
# Returns an array of labels present during the provided time frame 
#
###########################################################################
sub getLabelsInThisTimePeriod {
    my ($self, $stage, $fromTime, $toTime, $labels) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT DISTINCT label 
        FROM $stage 
        WHERE timestamp > '$fromTime' 
        AND timestamp <= '$toTime';
SQL

        if (!$query->execute) {

            return 0;
        }

    ${$labels} = $query->fetchall_arrayref();

    return 1;
}

###########################################################################
#
# Update Db to newest version 
#
###########################################################################
sub update { 
    my ($self) = @_;

    my $currentRevision = -1;

    while (1) {

        $currentRevision = $self->getRevision();
        if ($self->{_verbose}) {print "* Current Db revision = $currentRevision\n";}

        if ($currentRevision == 0) {$self->createRevision_1();}
        elsif ($currentRevision == 1) {$self->createRevision_2();}
        elsif ($currentRevision == 2) {$self->createRevision_3();}
        elsif ($currentRevision == 3) {$self->createRevision_4();}
        elsif ($currentRevision == 4) {$self->createRevision_5();}
        elsif ($currentRevision == 5) {$self->createRevision_6();}
        elsif ($currentRevision == 6) {$self->createRevision_7();}
        elsif ($currentRevision == 7) {$self->createRevision_8();}
        elsif ($currentRevision == 8) {$self->createRevision_9();}
        elsif ($currentRevision == 9) {$self->createRevision_10();}
        elsif ($currentRevision == 10) {$self->createRevision_11();}
        elsif ($currentRevision == 11) {$self->createRevision_12();}
        elsif ($currentRevision == 12) {$self->createRevision_13();}
        else {last;}
    }
}

#######################################################################################
# 
# Sets current revision of ippToPsps database
#
#######################################################################################
sub setRevision {
    my ($self, $revision) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO revision (revision) VALUES ($revision);
SQL
        $query->execute;
}

#######################################################################################
# 
# Gets current revision of ippToPsps database
#
#######################################################################################
sub getRevision {
    my ($self) = @_;

    if (!$self->SUPER::doesTableExist("revision")) {return 0;}

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT revision
        FROM revision
        ORDER BY revision DESC LIMIT 1;
SQL

        $query->execute;
    my @row = $query->fetchrow_array();

    return $row[0];
}

#######################################################################################
# 
# Create revision 1 of the database 
#
#######################################################################################
sub createRevision_1 {
    my ($self) = @_;

    print "* Creating revision 1 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE revision (
            revision INT, 
            created TIMESTAMP DEFAULT NOW(),
            primary key (revision)
            );
SQL
        $query->execute;

    my $stage = undef;
    foreach $stage (@stages) {

        my $query = $self->{_db}->prepare(<<SQL);
        CREATE TABLE $stage (
                timestamp TIMESTAMP DEFAULT NOW(),
                label VARCHAR(128) DEFAULT "NONE", 
                pending BIGINT NOT NULL,
                faults BIGINT NOT NULL
                );
SQL
            $query->execute;

    }

    $self->setRevision(1);
}

#######################################################################################
# 
# Create revision 2 of the database 
#
#######################################################################################
sub createRevision_2 {
    my ($self) = @_;

    print "* Creating revision 2 of '$self->{_dbName}'\n";


    my $stage = undef;
    foreach $stage (@stages) {

        my $query = $self->{_db}->prepare(<<SQL);
        ALTER TABLE $stage 
            ADD COLUMN reverting TINYINT NOT NULL DEFAULT 0;
SQL
            $query->execute;

    }

    $self->setRevision(2);
}

#######################################################################################
# 
# Create revision 3 of the database 
#
#######################################################################################
sub createRevision_3 {
    my ($self) = @_;

    print "* Creating revision 3 of '$self->{_dbName}'\n";


    my $stage = undef;
    foreach $stage (@stages) {

        my $query = $self->{_db}->prepare(<<SQL);
        ALTER TABLE $stage 
            ADD COLUMN processed BIGINT NOT NULL DEFAULT 0;
SQL

            $query->execute;
    }

    $self->setRevision(3);
}

#######################################################################################
# 
# Create revision 4 of the database 
#
#######################################################################################
sub createRevision_4 {
    my ($self) = @_;

    print "* Creating revision 4 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE current_labels (server VARCHAR(128), label VARCHAR(128));
SQL

        $query->execute;

    $self->setRevision(4);
}

#######################################################################################
# 
# Create revision 5 of the database 
#
#######################################################################################
sub createRevision_5 {
    my ($self) = @_;

    print "* Creating revision 5 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE servers (
            timestamp TIMESTAMP DEFAULT NOW(),
            server VARCHAR(128), 
            alive TINYINT, running TINYINT);
SQL

        $query->execute;

    $self->setRevision(5);
}

#######################################################################################
# 
# Create revision 6 of the database 
#
#######################################################################################
sub createRevision_6 {
    my ($self) = @_;

    print "* Creating revision 6 of '$self->{_dbName}'\n";

    my $stage = undef;
    my $index = undef;
    foreach $stage (@stages) {

        $index = $stage . "Index";
        my $query = $self->{_db}->prepare(<<SQL);
        CREATE INDEX $index ON $stage (timestamp, label);
SQL

            $query->execute;
    }

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE INDEX serverIndex ON servers (timestamp, server);
SQL

        $query->execute;


    $self->setRevision(6);
}

#######################################################################################
# 
# Create revision 7 of the database 
#
#######################################################################################
sub createRevision_7 {
    my ($self) = @_;

    print "* Creating revision 7 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    ALTER TABLE current_labels 
        ADD COLUMN priority INT NOT NULL DEFAULT 0;
SQL

        $query->execute;

    $self->setRevision(7);
}

#######################################################################################
# 
# Create revision 8 of the database 
#
#######################################################################################
sub createRevision_8 {
    my ($self) = @_;

    print "* Creating revision 8 of '$self->{_dbName}'\n";

    # drop reverting column from all stages tables
    my $stage = undef;
    foreach $stage (@stages) {

        my $query = $self->{_db}->prepare(<<SQL);
        ALTER TABLE $stage 
            DROP COLUMN reverting;
SQL

            $query->execute;
    }

    # create new 'revert' table
    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE reverts (
            stage VARCHAR(128), 
            reverting TINYINT);
SQL

        $query->execute;

    # insert stages into revert table
    foreach $stage (@stages) {
        my $query = $self->{_db}->prepare(<<SQL);
        INSERT INTO reverts
            (stage, reverting)
            VALUES
            ('$stage', 0);
SQL
            $query->execute;
    }

    $self->setRevision(8);
}

#######################################################################################
# 
# Create revision 9 of the database 
#
#######################################################################################
sub createRevision_9 {
    my ($self) = @_;

    print "* Creating revision 9 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE cluster_space (
            timestamp TIMESTAMP DEFAULT NOW(),
            total FLOAT, 
            available FLOAT, 
            used FLOAT, 
            hostsOver98 SMALLINT);
SQL

        $query->execute;
    $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE hosts (
            host VARCHAR(128), 
            total FLOAT, 
            available FLOAT, 
            used FLOAT); 
SQL

        $query->execute;

    $query = $self->{_db}->prepare(<<SQL);
    CREATE INDEX clusterSpaceIndex ON cluster_space (timestamp);
SQL

        $query->execute;

    $self->setRevision(9);
}

#######################################################################################
# 
# Create revision 10 of the database 
#
#######################################################################################
sub createRevision_10 {
    my ($self) = @_;

    print "* Creating revision 10 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    ALTER TABLE hosts 
        ADD COLUMN writable TINYINT NOT NULL DEFAULT 0,
            ADD COLUMN readable TINYINT NOT NULL DEFAULT 0;
SQL

        $query->execute;

    $self->setRevision(10);
}

#######################################################################################
# 
# Create revision 11 of the database 
#
#######################################################################################
sub createRevision_11 {
    my ($self) = @_;

    print "* Creating revision 11 of '$self->{_dbName}'\n";

    # same shape as other stage tables to enable easy update
    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE burntool (
            timestamp TIMESTAMP DEFAULT NOW(),
            label VARCHAR(128) DEFAULT "NONE", 
            pending BIGINT NOT NULL,
            processed BIGINT NOT NULL,
            faults BIGINT NOT NULL);
SQL

        $query->execute;
    $query = $self->{_db}->prepare(<<SQL);
    CREATE INDEX burntoolIndex ON burntool (timestamp, label);
SQL

        $query->execute;

    $self->setRevision(11);
}

#######################################################################################
# 
# Create revision 12 of the database 
#
# - adding nightlyscience table
#
#######################################################################################
sub createRevision_12 {
    my ($self) = @_;

    print "* Creating revision 12 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE nightlyscience (
            timestamp TIMESTAMP DEFAULT NOW(),
            status VARCHAR(128) DEFAULT "NONE"); 
SQL

        $query->execute;

    $self->setRevision(12);
}
#######################################################################################
# 
# Create revision 13 of the database 
#
# adding server_dates table
#
#######################################################################################
sub createRevision_13 {
    my ($self) = @_;

    print "* Creating revision 13 of '$self->{_dbName}'\n";

    my $query = $self->{_db}->prepare(<<SQL);
    CREATE TABLE server_dates (
            timestamp TIMESTAMP DEFAULT NOW(),
            server VARCHAR(64) DEFAULT "NONE", 
            date VARCHAR(64) DEFAULT "NONE"); 
SQL

        $query->execute;

    $self->setRevision(13);
}
1;

