#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package Job::DB::Item::Job;
use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::DB::AutoLock;
use Common::DB::Item::TableState;
use Common::Log;
use Data::Dumper;

use base 'Common::DB::Item';

use lib '/app/tools/job/lib';
use Job::Status;
use Job::DB::Item::JobSkipRule;

use constant kTable => 'job';
use constant kDB    => Common::DB::Item::kCommonDB();

# Weighting factor to change how much job priority is decrimented if there is a
# process of the same class running;
use constant kRunningPriorityFactor => 1;

sub GetNumQueued {
    my ($class) = @_;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = 'SELECT COUNT(*) as numqueued FROM ' . kTable . ' WHERE start_date = 0';
    my $sth = $dbo->DoCmd($sql);

    my $hr = $sth->fetchrow_hashref();
    return $hr->{numqueued};

}

sub GetAllHosts {
    my ($class) = @_;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = 'SELECT distinct run_hostname FROM ' . kTable;
    my $sth = $dbo->DoCmd($sql);

    my @result;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @result, $hr->{run_hostname};
    }

    return \@result;
}

sub GetAllRecentHosts {
    my ($class) = @_;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = 'SELECT distinct run_hostname FROM ' . kTable;
    $sql   .= '  WHERE end_date > DATE_SUB(NOW(),INTERVAL 1 YEAR)';
    my $sth = $dbo->DoCmd($sql);

    my @result;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @result, $hr->{run_hostname};
    }

    return \@result;
}

sub GetAllClassesAndSubclasses {
    my ($class) = @_;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = 'SELECT distinct class, subclass FROM ' . kTable;
    my $sth = $dbo->DoCmd($sql);

    my %data;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $class    = $hr->{class};
        my $subclass = $hr->{subclass};

        if ( !defined $data{$class} ) {
            $data{$class} = [];
        }
        if ($subclass) {
            push @{ $data{$class} }, $subclass;
        }
    }

    my @result;
    foreach my $class ( keys %data ) {
        my $classData = $data{$class};

        my %resultRecord;
        $resultRecord{Class}{Name} = $class;

        if ( $classData && scalar @$classData ) {
            $resultRecord{Class}{Subclass} = $classData;
        }
        push @result, \%resultRecord;
    }

    return \@result;
}

sub GetAll {
    my $class = shift;
    my $sql = shift || "SELECT * FROM " . kTable . " ORDER BY end_date DESC, job_id DESC";

    return $class->SUPER::GetAll($sql);
}

# This method returns all jobs that are either queued, running, or have
# somehow finished after the specified date.
#
sub GetAllUpToDate {
    my ( $class, $date ) = @_;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT * FROM " . kTable;

    if ($date) {
        $sql .= " WHERE end_date = " . $dbo->DBQuote(Common::DB::Item::kDateTimeNULL) . " OR end_date > " . $dbo->DBQuote($date);
    }

    $sql .= " ORDER BY end_date DESC, job_id DESC";

    return $class->SUPER::GetAll($sql);
}

sub GetNextQueuedJob {
    my $class     = shift;
    my %args      = @_;
    my $rules     = $args{rules};
    my $skiprules = $args{skiprules};
    my $hostname  = $args{hostname};

    assert( $rules,     "rules required" );
    assert( $skiprules, "skiprules required" );

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql = "SELECT job.* FROM " . kTable . " LEFT JOIN job as parent_job ON ( job.parent_job_id = parent_job.job_id )" . " WHERE ";

    my $ruleSQL     = $class->_ruleSQL($rules);
    my $skipruleSQL = $class->_skipRuleSQL($skiprules);
    assert( $ruleSQL, "No sql returned from _ruleSQL" );

    $sql .= "\n\n ($ruleSQL) ";
    $sql .= "\n\n AND NOT($skipruleSQL) \n\n" if ($skipruleSQL);

    $sql .=
        " AND job.queue_date > "
      . $class->quote(Common::DB::Item::kDateTimeNULL)
      . " AND job.start_date = "
      . $class->quote(Common::DB::Item::kDateTimeNULL)
      . " AND (job.run_hostname='' OR job.run_hostname IS NULL)"
      . " AND (job.hostname='' OR job.hostname IS NULL OR job.hostname="
      . $dbo->DBQuote($hostname) . ")"
      . " AND job.on_hold = 0"
      . " AND job.min_start_date < NOW() "
      . " AND ( parent_job.paused IS NULL OR parent_job.paused = 0 ) "
      . " ORDER BY priority DESC"
      . " LIMIT 1";

    #    Common::Log::Print( "QUERY: $sql" );

    # Lock the table before attempting to fetch the next job.
    # Then update the semaphore immediately.
    # Then exit.
    #
    # Note that we are NOT going to use the DB::Item interface here - The mysql lock is quite fragile,
    # and I don't want to risk some future code change in the DB::Item class or the DB::Item::Job class
    # that might cause the lock to be released as a side-effect..
    #
    my $lock = Common::DB::AutoLock->new( $dbo, 'job', 'job as parent_job' );

    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return undef unless $hr;

    my $jobID = $hr->{'job_id'};
    $dbo->DoCmd( "UPDATE "
          . kTable
          . " SET start_date="
          . Common::DB::Item::kDateTimeNow
          . ",run_hostname="
          . $dbo->DBQuote($hostname)
          . " WHERE job_id=$jobID" );

    return $class->Lookup( job_id => $jobID );
}

sub _ruleSQL {
    my $class   = shift;
    my $ruleset = shift;
    my @rules;
    my $sql;
    my $exceptionSQL;

    foreach my $rule (@$ruleset) {
        $sql = undef;

        $sql .= sprintf( " job.client_id = %s ", $class->quote( $rule->ClientID ) )
          if ( $rule->ClientID );
        $sql .= sprintf( " %s job.class = %s ", $sql ? " AND " : "", $class->quote( $rule->Class ) )
          if ( $rule->Class );
        $sql .= sprintf( " %s job.subclass = %s ", $sql ? " AND " : "", $class->quote( $rule->Subclass ) )
          if ( $rule->Subclass );

        my $maxedClientIDs = $rule->MaxedClientIDs();
        if ( $maxedClientIDs && scalar @$maxedClientIDs ) {
            $sql .= sprintf( " %s job.client_id NOT IN (%s) ", $sql ? ' AND ' : '', join( ',', @$maxedClientIDs ) );
        }

        $exceptionSQL = $class->_ruleExceptionSQL( $rule->Exceptions );

        $sql .= " AND NOT($exceptionSQL) " if ($exceptionSQL);

        push( @rules, "( $sql )" ) if ($sql);
    }

    return join( " OR ", @rules );
}

sub _ruleExceptionSQL {
    my $class = shift;
    my $except = shift || return;
    my @exceptions;
    my $sql;

    foreach my $e (@$except) {
        $sql = '';
        $sql = sprintf( " job.client_id = %s ", $class->quote( $e->{clientID} ) )
          if ( $e->{clientID} );
        $sql .= sprintf( " %s job.subclass = %s ", $sql ? " AND " : "", $class->quote( $e->{subclass} ) )
          if ( $e->{subclass} );

        if ( $e->{otherRule} ) {
            my $maxedClientIDs = $e->{otherRule}->MaxedClientIDs();
            if ( $maxedClientIDs && scalar @$maxedClientIDs ) {
                $sql .= sprintf( " %s job.client_id IN (%s) ", $sql ? ' AND ' : '', join( ',', @$maxedClientIDs ) );
            }
        }
        push( @exceptions, "( $sql )" ) if ($sql);
    }

    return join( " OR ", @exceptions );
}

sub _skipRuleSQL {
    my $class = shift;
    my $ruleset = shift || return;
    my $sql;
    my @skips;

    foreach my $rule (@$ruleset) {
        $sql = undef;

        $sql .= sprintf( " job.client_id = %s ", $class->quote( $rule->ClientID ) )
          if ( $rule->ClientID );
        $sql .= sprintf( " %s job.class = %s ", $sql ? " AND " : "", $class->quote( $rule->Class ) )
          if ( $rule->Class );
        $sql .= sprintf( " %s job.subclass = %s ", $sql ? " AND " : "", $class->quote( $rule->Subclass ) )
          if ( $rule->Subclass );

        push( @skips, "( $sql )" ) if ($sql);
    }

    return join( " AND ", @skips );
}

# overriding DB::Item::_create() to:
#  - lock the table (to be implemented) (!!! Is that really necessary?)
#  - insert the new record
#  - increment the priority of all the existing queued jobs
#
sub _create {
    my ($self) = @_;

    # Go ahead and create the db entry.
    # This code will throw an exception if anything goes wrong...
    #
    $self->SUPER::_create();

    # !!! I have not inserted the AutoLock object here yet.
    # It may not be necessary - it depends on whether:
    # - the priority update command is atomic and
    # - whether that really matters.
    #
    # I believe that it won't really matter either way - Priority does
    # not have to be perfect, so whether or not this operation is atomic is
    # pretty much irrelevant.
    #

    my $newID = $self->job_id;

    # Increment the priority of all _other_ queued jobs
    #
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql =
        "update job set priority=(priority+1) where job_id<>$newID and queue_date <> "
      . $dbo->DBQuote(Common::DB::Item::kDateTimeNULL)
      . " AND start_date = "
      . $dbo->DBQuote(Common::DB::Item::kDateTimeNULL);
    $dbo->DoCmd($sql);
}

sub StateToken {
    return Job::DB::Item::Job->GetTableStatusHash()->{Update_time};
}

# Return the number of jobs that are either running or in queue.
sub GetActiveJobCount {
    my ( $class, $clientID, $jobSubClass ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql =
        "SELECT count(*) FROM job WHERE client_id = "
      . $dbo->DBQuote($clientID) . " AND "
      . "subclass = "
      . $dbo->DBQuote($jobSubClass) . " AND "
      . "exit_code IS NULL";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

sub MarkChildrenAborted {
    my $class = shift;
    my $parentJobID = shift || die;

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql = "UPDATE job SET aborted = 1" . " WHERE parent_job_id = " . $dbo->DBQuote($parentJobID);
    $dbo->DoCmd($sql);
}

sub MarkPendingChildrenComplete {
    my $class       = shift;
    my $parentJobID = shift || die;
    my $dbo         = Common::RSApp::GetCommonDB();

    my $sql =
        "UPDATE job SET start_date = NOW(), end_date = NOW(), pid = -1 "
      . " WHERE parent_job_id = "
      . $dbo->DBQuote($parentJobID) . " AND "
      . "pid IS NULL ";

    $dbo->DoCmd($sql);
}

sub GetRunningAbortedJobs {
    my $class = shift;
    my $sql   = "SELECT * FROM job WHERE aborted = 1 AND pid IS NOT NULL AND end_date = 0";

    return $class->GetAll($sql);
}

sub Search {
    my $class  = shift;
    my %args   = @_;
    my $dbo    = Common::RSApp::GetCommonDB;
    my $weight = kRunningPriorityFactor;

    my $sql = "SELECT * FROM " . kTable;
    my $criteria;
    my $sort;
    my $limit;

    foreach my $key ( keys %args ) {
        next unless ( $args{$key} );

        $criteria .= " AND " if ($criteria);

        if ( $key eq 'maxAge' ) {
            $criteria .=
                " ( job.end_date IS NULL OR job.end_date = 0 OR job.end_date > DATE_SUB( NOW(), interval "
              . $dbo->DBQuote( $args{maxAge} )
              . " hour ) ) ";
        }

        elsif ( $key =~ /^(class|subclass)$/ ) {
            $criteria .= " job.$key = " . $dbo->DBQuote( $args{$key} );
        }

        elsif ( $key eq 'hostname' ) {
            $criteria .= " job.run_hostname = " . $dbo->DBQuote( $args{$key} );
        }

        elsif ( $key eq 'clientID' ) {
            $criteria .= " job.client_id = " . $dbo->DBQuote( $args{$key} );
        }

        # Only hide children if they are complete
        elsif ( $key eq 'hideChildren' ) {
            $criteria .= " ( job.end_date = 0 OR (job.parent_job_id IS NULL OR job.parent_job_id = 0) )";
        }

        elsif ( $key eq 'state' ) {
            my $state = $args{state};
            if ( 'queued' eq $state ) {
                $criteria .= " job.start_date = 0";
                $sort  = " ORDER BY job.priority DESC";
                $limit = 50;
            } elsif ( 'running' eq $state ) {
                $criteria .= " (job.start_date <> 0 AND job.end_date = 0)";
                $sort = " ORDER BY job.run_hostname,job.client_id,job.class,job.subclass";
            } elsif ( 'complete' eq $state ) {
                $criteria .= " job.end_date <> 0";
                $sort  = " ORDER BY job.end_date DESC,job.job_id";
                $limit = 250;
            } else {
                die "Unknown state '$state'";
            }
        }

        else {
            die "Unknown parameter '$key'";
        }
    }

    $sql .= " WHERE $criteria" if ($criteria);
    $sql .= " GROUP BY job.job_id";
    $sql .= " $sort"           if ($sort);

    $sql .= " LIMIT $limit" if $limit;

    Common::Log::Debug("QUERY: $sql");
    return $class->GetAll($sql);
}

sub ChildCount {
    my $parentID = shift || die;
    my $dbo = Common::RSApp::GetCommonDB;

    my $sql = "SELECT count(*) FROM " . kTable . " WHERE parent_job_id = " . $dbo->DBQuote($parentID);

    my $sth = $dbo->DoCmd($sql);
    my $row = $sth->fetchrow_arrayref;

    return $row->[0];
}

sub WaitingChildCount {
    my $parentID = shift || die;
    my $dbo = Common::RSApp::GetCommonDB;

    my $sql = "SELECT count(*) FROM " . kTable . " WHERE parent_job_id = " . $dbo->DBQuote($parentID) . " AND start_date = 0";

    my $sth = $dbo->DoCmd($sql);
    my $row = $sth->fetchrow_arrayref;

    return $row->[0];
}

sub getCacheDate {
    my $class = shift;
    my $date  = shift;

    my $sql = "SELECT max(date_modified) as date_modified FROM " . kTable;

    $sql .= " WHERE date_modified > " . $class->quote($date) if ($date);

    my $collection = $class->SUPER::GetAll($sql);

    if ( $collection->hasNext() ) {
        my $row = $collection->next();
        return $row->date_modified();
    }

    return;
}

sub GetRunningJobs {
    my $class = shift;
    my $sql   = "SELECT * FROM job WHERE start_date <> 0 AND end_date = 0";

    return $class->GetAll($sql);
}

sub GetRunningOrQueuedJobs {
    my $class = shift;
    my $sql   = "SELECT * FROM job WHERE end_date = 0";

    return $class->GetAll($sql);
}

sub GetRunningOrQueuedJobsArray {
    my ($class) = @_;

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql =
"SELECT COUNT(*) as count, client_id, class, subclass, run_hostname, IF(start_date=0, 'q', 'r') as state FROM job WHERE end_date = 0 group by 2,3,4,5,6";
    my $sth = $dbo->DoCmd($sql);
    my @result;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @result, $hr;
    }

    return \@result;
}

sub updateHeartbeat {
    my $class = shift;
    my $jobID = shift || return;
    my $dbo   = Common::RSApp::GetCommonDB;

    my $sql = "UPDATE " . kTable . " SET heartbeat = NOW(), date_modified = date_modified " . "WHERE job_id = " . $class->quote($jobID);

    $dbo->DoCmd($sql);
}

###
1;    #
###