package TasksManager;

use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use DateTime;
use Switch;

use lib qw(Core);

use Database;
use LocalTaskConfiguration;
use Configuration;
#use ResultsTargetManager;

my $tasks_manager = undef;

### METHODS (last update 07/30/2010 M. Wojciechowski):
#   new (constructor - sets up the database connection)
#   exist (returns 1 if task with given id exists in database)
#   get_task (returns task for lga4, lga5 or mvsm algorithm)
#   get_task_for_ace (returns task for ace algorithm)
#   get_task_for_mammoth (returns task for mammoth algorithm)
#   info (returns task with all data)
#   set_for_calculation (sets task with given id as ready to evaluate)
#   set_for_calculation_all (sets tasks with status = 0 as ready to evaluate)
#   update_status (changes status of task AND sets timestamps)
#   add (adds new task into database)
#   delete (removes task with given id from database)
#   tasks (returns tasks ready for evaluation)
#   tasks_in_progress (returns tasks being evaluated - status = 2)
#   evaluate_model_for_target (sets task with given model name as ready to evaluate)
########################################################

sub new {
    my ($class) = @_;
    
    return $tasks_manager if(defined($tasks_manager));
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{TASKS_HOSTNAME}, $CONFIG->{TASKS_PORT}, $CONFIG->{TASKS_DATABASE}, $CONFIG->{TASKS_USERNAME}, $CONFIG->{TASKS_PASSWORD}),
	_casp_version => $LOCAL_CONFIG->{CASP_VERSION}
    };
    
    $tasks_manager = bless $self, $class;
    return $tasks_manager;
}

sub exist {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM evaluations.tasks WHERE (id = %d) AND casp_version = '%s'", $id, $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($count) = $sth->fetchrow_array();
        
        $result = ($count == 1) ? 1 : 0;
    }
    
    return $result;
}

sub get_task {
    my ($self) = @_;
    
    my $result = 0;
    
    my $query = "LOCK TABLE evaluations.tasks IN EXCLUSIVE MODE; SELECT evaluations.gettask();";
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub get_task_for_ace {
    my ($self) = @_;

    my $result = 0;

    my $query = "LOCK TABLE evaluations.tasks IN EXCLUSIVE MODE; SELECT evaluations.gettaskforace();";
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }

    return $result;
}

sub get_task_for_mammoth {
    my ($self) = @_;

    my $result = 0;

    my $query = "LOCK TABLE evaluations.tasks IN EXCLUSIVE MODE; SELECT evaluations.gettaskformammoth();";
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }

    return $result;
}


sub info {
    my ($self, $id) = @_;
    
    my %result = (
        ID => '',
        PLUGIN => '',
        PARAMETERS => '',
        STATUS => '',
        MODEL1_DIR => '',
        MODEL1_FILE => '',
        MODEL2_DIR => '',
        MODEL2_FILE => '',
        RESULT_DIR => '',
        RESULT_FILE => '',
        HOSTNAME => '',
        START_TIME => '',
        STOP_TIME => '',
        ENTRY_TIME => '',
	PARAM => '',
	CASP_VERSION => ''
    );
    
    return %result if(! $self->exist($id));
    
    my $query = sprintf("SELECT id, plugin, parameters, status, model1_dir, model1_file, model2_dir, model2_file, result_dir, result_file, hostname, start_time, stop_time, entry_time, param, casp_version FROM evaluations.tasks WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{ID}, $result{PLUGIN}, $result{PARAMETERS}, $result{STATUS}, $result{MODEL1_DIR}, $result{MODEL1_FILE}, $result{MODEL2_DIR}, $result{MODEL2_FILE}, $result{RESULT_DIR}, $result{RESULT_FILE}, $result{HOSTNAME}, $result{START_TIME}, $result{STOP_TIME}, $result{ENTRY_TIME}, $result{PARAM}, $result{CASP_VERSION}) = $sth->fetchrow_array();
    }
    
    return %result;
}

sub set_for_calculation {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("LOCK TABLE evaluations.tasks IN EXCLUSIVE MODE; UPDATE evaluations.tasks SET status = 1 WHERE (id = %d) AND casp_version='%s' ", $id, $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub set_for_calculation_all {
    my ($self) = @_;
    
    my $result = 0;
    
    my $query = sprintf("LOCK TABLE evaluations.tasks IN EXCLUSIVE MODE; UPDATE evaluations.tasks SET status = 1 WHERE (status = 0) AND casp_version='%s' ", $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub update_status {
    my ($self, $id, $status, $elapsed_time, $hostname) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("UPDATE evaluations.tasks SET stop_time = (start_time + interval '%s seconds'), status = '%d', hostname = '%s' WHERE (id = %d) AND casp_version='%s' ", $elapsed_time, $status, quotemeta($hostname), $id, $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, $target, $plugin, $parameters, $model1_dir, $model1_file, $model2_dir, $model2_file, $result_dir, $result_file, $param) = @_;
    
    my $result = 0;
    if(!defined($param)) {
        $param = "";
    }
    
    my $query = sprintf("INSERT INTO evaluations.tasks (target, plugin, parameters, model1_dir, model1_file, model2_dir, model2_file, result_dir, result_file, param, casp_version) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",quotemeta($target),quotemeta($plugin), quotemeta($parameters), quotemeta($model1_dir), quotemeta($model1_file), quotemeta($model2_dir), quotemeta($model2_file), quotemeta($result_dir), quotemeta($result_file), quotemeta($param), $self->{_casp_version});
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub delete {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("DELETE FROM evaluations.tasks WHERE (id = %d) AND casp_version='%s'", $id, $self->{_casp_version});
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}

sub delete_by {
        my ($self, %by) = @_;
        my $query = sprintf('DELETE FROM evaluations.tasks WHERE (%s)=(%s)', join(',', keys %by), join(',', map{'?'} keys %by));
        my $sth = $self->{_database}->query($query, values %by);
        return 1 if (defined($sth));
}

sub check_status {
    my ($self, $target) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT min(status) FROM evaluations.tasks WHERE (model1_file like '%s' or model2_file like '%s') AND casp_version='%s' ", $target . '%', $target . '%', $self->{_casp_version});
    
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub tasks {
    my ($self, $field, $order) = @_;
    
    my @targets = ();
    
    $field = (!defined($field) || ($field eq '')) ? 'substring(t.model1_file from 1 for 5)' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    
    my $query = sprintf("SELECT DISTINCT substring(t.model1_file from 1 for (position('.' in t.model1_file) - 1)) FROM evaluations.tasks t WHERE position('.' in t.model1_file) > 0 AND t.status = 0 AND casp_version='%s';",$self->{_casp_version});
# ORRDER BY %s %s", $field, $order);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($name) = $sth->fetchrow_array()) {
            if (length($name) > 1) {
	      push(@targets, {
		      INDEX => $index,
		      NAME => $name
		}
	      );
	      $index++;
	    }
        }
    }
    
    return @targets;
}

sub tasks_ready_to_upload {
    my ($self, $field, $order) = @_;
    
    my @targets = ();
    
    $field = (!defined($field) || ($field eq '')) ? 'substring(t.model1_file from 1 for 5)' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
	my $query = sprintf("SELECT target, SUM(CASE status WHEN 3 THEN 1 ELSE 0 END), count(*) FROM evaluations.tasks WHERE casp_version = '%s'GROUP BY target ORDER BY target",$self->{_casp_version});
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;

        while(my($name, $done, $total) = $sth->fetchrow_array()) {
            if (length($name) > 1) {
		  push(@targets, {
		      INDEX => $index,
		      NAME => $name, 
		      DONE => $done,
		      TOTAL => $total
		}
	      );
	      $index++;
	    }
        }
    }
    
    return @targets;
}

sub tasks_current_status {
    my ($self, $field, $order) = @_;
    
	my @targets = ();
	my @fields = qw(target plugin time);
	my $sort = sprintf('%s %s', exists {map {$_ => 1} @fields}->{$field}? $field : 'target', 'DESC' eq $order? 'DESC' : 'ASC');
	my $query = sprintf("SELECT target, plugin, status, count(*), min(entry_time) as time from evaluations.tasks WHERE casp_version='%s'  group by target, plugin, status order by $sort",$self->{_casp_version});
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 0;
	my $target_prev = '';
	my $plugin_prev = '';
        while (my ($target, $plugin, $status, $count, $time) = $sth->fetchrow_array()) {
		push @targets, {
			FIRST => ! ($target eq $target_prev && $plugin eq $plugin_prev),
			INDEX => ++$index,
			TARGET => $target,
			PLUGIN => $plugin,
			STATUS => $status,
			COUNT => $count,
			TIME => $time,
		};
		$target_prev = $target;
		$plugin_prev = $plugin;
	}
    }
    
    return @targets;
}

sub tasks_in_progress {
    my ($self) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM evaluations.tasks WHERE (status = 2) AND casp_version='%s'", $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub evaluate_models_for_target {
    my ($self, $target) = @_;
    
    my $result = 0;
    
    my $query = sprintf("UPDATE evaluations.tasks SET status = 1 WHERE (model1_file like '%s%' OR model2_file like '%s%') AND status = 0 AND casp_version='%s';", $target, $target, $self->{_casp_version});
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub re_evaluate_models_for_target {
    my ($self, $target, $plugin) = @_;
    
    my $result = 0;

    my $query = sprintf("UPDATE evaluations.tasks SET status=1 WHERE target='%s' AND plugin='%s' AND casp_version='%s' ", $target, $plugin, $self->{_casp_version});
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub delete_tasks_for_target {
    my ($self, $target) = @_;
    
    my $result = 0;
    
    my $query = sprintf("DELETE FROM evaluations.tasks WHERE target='%s' AND casp_version='%s'", $target, $self->{_casp_version});
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

1;