#!/usr/bin/perl -w

package czartool::CzarDb;

use warnings;
use strict;

use File::Temp;

my @stages = (
        "chip",
        "cam",
        "fake",
        "warp",
        "stack",
        "diff",
        "dist",
        "pub",
        "chipbackground",
        "summitExp",
        "downloadExp",
        "newExp",
        "rawExp"); # TODO put elsewhere

my @ippToPspsStages = (
        "processed",
        "loaded_to_datastore",
        "loaded_to_ODM",
        "merge_worthy",
        "deleted_datastore",
        "deleted_dxlayer",
        "merged",
        "deleted_local");

my @allStages = (@stages, @ippToPspsStages);

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;

    return $self;
}

###########################################################################
#
# Gets last czarpoll iteration time
#
###########################################################################
sub getPollDate {
    my ($self,$polldate) = @_;

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

    $query->execute;

    $polldate = $query->fetchrow_array();

    return $polldate;
}

###########################################################################
#
# Gets current ippToPsps labels
#
###########################################################################
sub getCurrentIppToPspsLabels {
    my ($self, $begin, $end, $labels) = @_;

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

    SELECT DISTINCT label
        FROM processed
        WHERE timestamp >= '$begin'
        AND timestamp <= '$end'
SQL

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

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

    return 1;
}

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

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

    SELECT label
        FROM current_labels
        WHERE server LIKE '$server'
        ORDER BY priority DESC, label;
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,$telescope, $alive, $running) = @_;

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

       $query->execute;
}

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

    my $query = $self->{_db}->prepare(<<SQL);
    DELETE FROM live_servers where timestamp < CURRENT_TIMESTAMP - INTERVAL 12 hour;
SQL
       $query->execute;

    $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO live_servers
        (server,telescope, alive, running)
        VALUES
        ('$server','$telescope', $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,$telescope, $reverting) = @_;

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

    $query->execute;
}

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

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

SQL

    $query->execute;
}

###########################################################################
#
# Update server_dates table
#
###########################################################################
sub updateServerDates {
    my ($self, $server, $dates,$telescope) = @_;

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

    my $date = undef;

    foreach $date (@{$dates}) {

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

       $query->execute;
    }
}

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

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

    $query->execute;

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

       $query->execute;
}

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

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

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

    $query->execute;

    my $label = undef;
    my $priority = undef;

    foreach my $i (0 .. $#$labels) {
        $label = @{$labels}[$i];
        $priority = @{$priorities}[$i];

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

        #execute the query, but check for deadlocks
        my $db_res=0;
        while($db_res==0)
        {
            $db_res=1;
            my $result = eval{$query->execute;};
            unless($result) {
                 if(/DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction/){
                     $db_res=0;
                     sleep 3;
                 } 
            }
        }
        
    }
}

###########################################################################
#
# 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, $usable, $used, $hostsOver98) = @_;

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

        $query->execute;
}

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

    my $query = $self->{_db}->prepare(<<SQL);
    INSERT INTO $stage
        (label, dbname, pending, processed, faults)
        VALUES
        ('$label', '$dbname', $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) = @_;

    # XXX: I'm not splitting this by project (dbname).
    # Is that ok? Seems like a good idea because this will make the time
    # range of the plots for both projects the same.

    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 ($_maxX, $_minX, $_timeDiff) = $query->fetchrow_array();

    # no values - get out of here
    if (!$_maxX || !$_minX || !$_timeDiff) {return 0;}
    if ($_timeDiff > ${$timeDiff}) {${$timeDiff} = $_timeDiff;}

    # set new min/max
    if (!$self->isBefore(${$maxX}, $_maxX)) {${$maxX} = $_maxX;}
    if (!$self->isBefore($_minX, ${$minX})) {${$minX} = $_minX;}

    return 1;
}

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

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

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

    if (!$self->runAnalysis($startStageMetrics, $dbname)) {return 0;}
    if (!$self->runAnalysis($endStageMetrics, $dbname)) {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, $dbname) = @_;

    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 dbname LIKE '$dbname'
        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) + 0.001;

            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 files
#
###########################################################################
sub createTimeSeriesData {
    my ($self, $label, $dbname, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff, $linDataFile, $logDataFile, $ratDataFile) = @_;

    # get total number of data points
    my $query = $self->{_db}->prepare(<<SQL);
    SELECT  COUNT(*)
        FROM $stage
        WHERE label LIKE '$label'
        AND dbname LIKE '$dbname'
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime';
SQL

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

    # decide on a number for the running mean for rate plots - use 10% of total data points
    my $range = int($count/10);
    if ($range < 1) {$range = 1;}

    $self->getTimeMinMaxDiff($label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff);
    # grab all the data for the provided time range
    $query = $self->{_db}->prepare(<<SQL);
    SELECT
        timestamp, pending, faults, processed
        FROM $stage
        WHERE label LIKE '$label'
        AND dbname LIKE '$dbname'
        AND timestamp >= '$fromTime' AND timestamp <= '$toTime'
        ORDER BY timestamp;
SQL

    $query->execute;

    # print "fromTime: $fromTime, toTime: $toTime\n";

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

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

    $tmpFile = File::Temp->new( TEMPLATE => "czarplot_gnuplot_".$label."_".$stage."_t.XXXXX", DIR => '/tmp', SUFFIX => 'dat');
    $tmpFile->unlink_on_destroy( 0 );
    ${$ratDataFile} = $tmpFile->filename;
    open (RATDAT, ">${$ratDataFile}") 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 (@rateProcessed, @ratePending, @rateFaults);
    my $formattedTimestamp = undef;
    my $middleTime = undef;
    my $formattedMiddleTimestamp = undef;
    my $positiveJump = 0;
    my @rateTimes;
    my $diffPending = 0;
    my $diffProcessed = 0;
    my $diffFaults = 0;
    my $lastDiffProcessed = 0;
    $derivProcessed = 0;

    $linearProcessed = 0;
    my $someData = 0;
    my $timeSep;
    while (my @row = $query->fetchrow_array()) {

        my ($thisTimestamp, $thisPending, $thisFaults, $thisProcessed) = @row;

        # print "$thisTimestamp, $thisPending, $thisFaults, $thisProcessed\n";

        $formattedTimestamp = $self->getFormattedDate($thisTimestamp);

        # if not first time in....
        if (defined $lastTimestamp) {

            # times
            $timeSep = $self->diffTimesInSecs($thisTimestamp, $lastTimestamp);
            if ($timeSep == 0) {$timeSep = 1;}; # HACK to prevent divide-by-zero

            $middleTime = $self->getMiddleTime($lastTimestamp, $thisTimestamp);
            $formattedMiddleTimestamp = $self->getFormattedDate($middleTime);

            $diffPending = $thisPending - $lastPending;
            $diffFaults = $thisFaults - $lastFaults;

            # First, look for large positive jumps in processed, most likely a label
            # added, and store value to subtract from linear value below. If > 5000
            # processed per hour, then it must be wrong
            if (($thisProcessed - $lastProcessed)/($timeSep/3600) > 5000) {
                $positiveJump = $thisProcessed - $lastProcessed;
            }
            else {

                $positiveJump = 0;
            }
            # we only count increases in processing - drops are due to cleanup etc
            if ($thisProcessed > $lastProcessed) {

                $diffProcessed = ($thisProcessed - $lastProcessed) - $positiveJump;
                $linearProcessed = $linearProcessed + $diffProcessed;
            }
            else {

                $diffProcessed = 0;
                $linearProcessed = $linearProcessed;
            }

            # print "$thisTimestamp, $thisPending, $thisFaults, $thisProcessed, $timeSep, $diffProcessed, $linearProcessed\n";

            # calculate first derivative in units of images per hour
            $derivPending = $diffPending/($timeSep/3600);
            $derivFaults = $diffFaults/($timeSep/3600);
            $derivProcessed = $diffProcessed/($timeSep/3600);

        }
        # first time in
        else {

            $derivPending = 0;
            $derivFaults = 0;
            $derivProcessed = 0;
            $formattedMiddleTimestamp = $formattedTimestamp;
        }

        # deal with rate plot
        push(@ratePending, $derivPending);
        push(@rateFaults, $derivFaults);
        push(@rateProcessed, $derivProcessed);
        push(@rateTimes, $middleTime);

        # get last few values
        my $size = scalar (@rateProcessed);
        if ($size > $range) {

            my $counter = 0;
            my $oldTime;
            my $newTime = $rateTimes[$size-1];
            my $totalProcessed = 0;
            my $totalPending = 0;
            my $totalFaults = 0;
            for (my $i=$size-1; $i>=($size - $range); $i--) {

                $totalPending = $totalPending + $ratePending[$i];
                $totalFaults = $totalFaults + $rateFaults[$i];
                $totalProcessed = $totalProcessed + $rateProcessed[$i];
                $oldTime = $rateTimes[$i];
                $counter++;
            }

            my $meanPending = $totalPending / $counter;
            my $meanFaults = $totalFaults / $counter;
            my $meanProcessed = $totalProcessed / $counter;

            my $formattedTimestamp = $self->getFormattedDate($self->getMiddleTime($oldTime, $newTime));
            print RATDAT "$formattedTimestamp $meanPending $meanFaults $meanProcessed\n";
        }

        $linearPending = $thisPending;
        $linearFaults = $thisFaults;

        # 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
        print LOGDAT "$formattedTimestamp $logPending $logFaults $logProcessed\n";
        print LINDAT "$formattedTimestamp $linearPending $linearFaults $linearProcessed\n";

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

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

    close(LINDAT) or print "* Problem closing linear gnuplot data file for plot for '$label' '$stage'\n";
    close(LOGDAT) or print "* Problem closing log gnuplot data file for plot for '$label' '$stage'\n";
    close(RATDAT) or print "* Problem closing rate gnuplot data file for plot for '$label' '$stage'\n";
}

###########################################################################
#
# Returns number of faults at a particular time in the past for a given label and stage
#
###########################################################################
sub countFaultsInPast {
    my ($self, $label, $dbname, $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'
        AND dbname LIKE '$dbname';
SQL
    $query->execute;

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

    $query = $self->{_db}->prepare(<<SQL);
    SELECT faults
        FROM $stage
        WHERE label LIKE '$label'
        AND dbname LIKE '$dbname'
        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, $dbname, $stage) = @_;

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

    return scalar $query->fetchrow_array();
}

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

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT
        pending, faults
        FROM $stage
        WHERE label LIKE '$label'
        AND dbname like '$dbname'
        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, $dbname, $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 DESC;
SQL

    $query->execute;

    my $underLimit;

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

        my ($host, $used, $available, $writable, $readable) = @row;

	# stsci hosts are no longer with us
	next if $host =~ /stsci/;

        if (($used+$available) == 0) {
          $underLimit = 1;
        } elsif (($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) = @_;
    my $maxAvailable;
    my $maxUsable;
    my $minAvailable;
    my $minUsable;

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

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT
        MAX(available), MIN(available), MAX(usable), MIN(usable),
        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

# test to print out the sql
#     print "SQL: ", (<<CODE);
#     SELECT
#         MAX(available), MIN(available), MAX(usable), MIN(usable),
#         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';
# CODE

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

    ($maxAvailable, $minAvailable, $maxUsable, $minUsable, ${$maxX}, ${$minX}, ${$timeDiff}) = $query->fetchrow_array();
    ${$maxY} = ($maxAvailable > $maxUsable ? $maxAvailable : $maxUsable);
    ${$minY} = ($minAvailable > $minUsable ? $minAvailable : $minUsable);

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

    $query = $self->{_db}->prepare(<<SQL);
    SELECT
        DATE_FORMAT(timestamp, '$self->{_dateFormat}'), available, usable
        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, $usable) = @row;
        print GNUDAT "$timestamp $available $usable\n";
    }

    close(GNUDAT);

    return 1;
}

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

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

    $self->getTimeMinMaxDiff($label, $stage, $begin, $end, $minX, $maxX, $timeDiff);

    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, $dbname, $stage, $startTime, $endTime, \$processed, \$pending, \$faults)) {}
        $timestamp = $self->getFormattedDate($endTime);
        if (!$processed) {$processed = 0;}
        print GNUDAT "$timestamp $pending $faults $processed\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, $dbname, $stage, $begin, $end) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT
        processed
        FROM $stage
        WHERE label LIKE '$label'
        AND dbname LIKE '$dbname'
        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 don't have time to think 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 (@allStages) {

            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 (@allStages) {

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

1;

