package PredictionsManager;

use strict;
use warnings;

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

use lib qw(Core);

use Database;

use Account;
use GroupsManager;
use PredictorsManager;
use Configuration;

my $predictions_manager = undef;

sub new {
    my ($class) = @_;
    
    return $predictions_manager if(defined($predictions_manager));
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})
    };
    
    $predictions_manager = bless $self, $class;
    return $predictions_manager;
}

sub predictions {
    my ($self, $group_id, $field, $order) = @_;
    
    $field = (!defined($field) || ($field eq '')) ? 'p.target' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    
    my @predictions = ();
    
    my $query = sprintf("SELECT p.id, a.firstname, a.lastname, p.type, p.pfrmat, p.email, p.target, p.model, p.date FROM casp13.predictions p LEFT OUTER JOIN casp13.predictors pr ON (p.predictors_id = pr.id) LEFT OUTER JOIN public.accounts a ON (pr.accounts_id = a.id) WHERE p.groups_id=%d ORDER BY %s %s", $group_id, $field, $order);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $predictions_manager = new PredictionsManager();
        
        while(my($id, $firstname, $lastname, $type, $pfrmat, $email, $target, $model, $date) = $sth->fetchrow_array()) {
            push(@predictions, {
                    ID => $id,
                    INDEX => $index,
                    NAME => $predictions_manager->prediction_name($id),
                    TARGET => $target,
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    FIRSTNAME => ((defined($firstname) && ($firstname ne '')) ? $firstname : ''),
                    LASTNAME => ((defined($lastname) && ($lastname ne '')) ? $lastname : ''),
                    EMAIL => $email,
                    TYPE => $type,
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                }
            );
            $index++;
        }
    }
    
    return @predictions;
}

sub info {
    my ($self, $id) = @_;

    my %result = (
        ID => '',        
        NAME => '',
        TARGET => '',
        MODEL => '',
        PFRMAT => '',
        GROUP_NAME => '',
	GROUP_CODE => ''
    );
    
    ## we havent exist methog fot prediction return %result if(! $self->exist($id));
    
    my $query = sprintf("select pr.target, pr.pfrmat, pr.model, gr.name, gr.code from casp13.predictions pr JOIN casp13.groups gr on (gr.id = pr.groups_id) WHERE pr.id=%s", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{TARGET}, $result{PFRMAT}, $result{MODEL}, $result{GROUP_NAME}, $result{GROUP_CODE}) = $sth->fetchrow_array();        
    }    
    return %result;
}

sub predictions_of_account {
    my ($self, $account_id, $group_id) = @_;
    
    my @predictions = ();
    
    my $query;
    if ($group_id > 0 ) {
       $query = sprintf("SELECT p.id, a.firstname, a.lastname, p.type, p.pfrmat, p.email, p.target, p.model, p.date, gr.name FROM casp13.predictions p LEFT OUTER JOIN casp13.predictors pr ON (p.predictors_id = pr.id) LEFT OUTER JOIN public.accounts a ON (pr.accounts_id = a.id) LEFT OUTER JOIN casp13.groups gr ON (p.groups_id = gr.id)                        
                        WHERE gr.id IN (select gpr.groups_id from casp13.predictors pr JOIN casp13.groups_predictors_relations gpr ON (gpr.predictors_id = pr.id) where accounts_id=%d)
                        and gr.id=%d                        
                        ORDER BY p.date DESC", $account_id, $group_id);
    } else {
       $query = sprintf("SELECT p.id, a.firstname, a.lastname, p.type, p.pfrmat, p.email, p.target, p.model, p.date, gr.name FROM casp13.predictions p LEFT OUTER JOIN casp13.predictors pr ON (p.predictors_id = pr.id) LEFT OUTER JOIN public.accounts a ON (pr.accounts_id = a.id) LEFT OUTER JOIN casp13.groups gr ON (p.groups_id = gr.id)
                        WHERE gr.id IN (select gpr.groups_id from casp13.predictors pr JOIN casp13.groups_predictors_relations gpr ON (gpr.predictors_id = pr.id) where accounts_id=%d)
                        ORDER BY p.date DESC", $account_id);
       
    }
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $predictions_manager = new PredictionsManager();
        
        while(my($id, $firstname, $lastname, $type, $pfrmat, $email, $target, $model, $date, $group_name) = $sth->fetchrow_array()) {
            push(@predictions, {
                    ID => $id,
                    INDEX => $index,
                    NAME => $predictions_manager->prediction_name($id),
                    TARGET => $target,
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    FIRSTNAME => ((defined($firstname) && ($firstname ne '')) ? $firstname : ''),
                    LASTNAME => ((defined($lastname) && ($lastname ne '')) ? $lastname : ''),
                    EMAIL => $email,
                    TYPE => $type,
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                    GROUP_NAME => $group_name,
                }
            );
            $index++;
        }
    }
    
    return @predictions;
}

sub group_names {
    my ($self, $account_id) = @_;
    
    my @names = ();
    
    my $query = sprintf("SELECT DISTINCT ON (gr.id) gr.id, gr.name
                        FROM casp13.predictions p
                        LEFT OUTER JOIN casp13.predictors pr ON (p.predictors_id = pr.id)
                        LEFT OUTER JOIN public.accounts a ON (pr.accounts_id = a.id)
                        LEFT OUTER JOIN casp13.groups gr ON (p.groups_id = gr.id)
                        WHERE a.id=%d ORDER BY gr.id, gr.name DESC", $account_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $group_name) = $sth->fetchrow_array()) {
            push(@names, {
                    ID => $id,
                    GROUP_NAME => $group_name,
                }
            );
            $index++;
        }
    }
    
    return @names;
}

sub all_predictions {
    my ($self, $group_id, $field, $order) = @_;
    
    $group_id = (!defined($group_id) || ($group_id eq '')) ? -1 : $group_id;
    $field = (!defined($field) || ($field eq '')) ? 'date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
	
    my $sub_query = '';
    if($group_id > 0) {
        $sub_query .= sprintf(' AND (groups_id = %d)', $group_id);
    }
    
	my @predictions = ();
    
 	my $query = sprintf("SELECT id, type, pfrmat, email, target, model, date, groups_id, predictors_id FROM casp13.predictions WHERE (1 = 1) %s ORDER BY %s %s", $sub_query, $field, $order);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $groups_manager = new GroupsManager();
        my $predictors_manager = new PredictorsManager();
        my $predictions_manager = new PredictionsManager();
        my $accounts_manager = new Core::Account();
        
        while(my($id, $type, $pfrmat, $email, $target, $model, $date, $groups_id, $predictors_id) = $sth->fetchrow_array()) {
            push(@predictions, {
                    ID => $id,
                    NAME => $predictions_manager->prediction_name($id),
                    INDEX => $index,
                    TARGET => $target,
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    EMAIL => $email,
                    TYPE => $type,
                    GROUP => $groups_manager->name($groups_id),
                    PREDICTOR => $accounts_manager->name($predictors_manager->account_id($predictors_id)),
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                }
            );
            $index++;
        }
    }
    
    return @predictions;
}


sub tarballs_predictions {
    my ($self, $target_name, $type) = @_;
    my $sub_query = '';
    if(defined($type) && $type ne '') {
        if($type eq 'server') {
            $sub_query .= sprintf(' AND gr.type > 0 AND gr.type < 3 ');        
        } elsif ($type eq 'human') {
            $sub_query .= sprintf(' AND gr.type = 1');
        }    
    }
    
    my @predictions = ();
    #my $query = sprintf("SELECT id, type, pfrmat, email, target, model, date, groups_id, predictors_id FROM casp13.predictions WHERE (1 = 1) and (pfrmat = \'TS\' or pfrmat=\'AL\')  and (target = \'%s\')", $target_name);
    my $query = sprintf("SELECT pr.id, pr.type, pr.pfrmat, pr.email, pr.target, pr.model, pr.date, pr.groups_id, pr.predictors_id, gr.name, gr.code FROM casp13.predictions pr
                        JOIN casp13.groups gr ON (pr.groups_id = gr.id) WHERE (1 = 1) and (pr.pfrmat = \'TS\' or pr.pfrmat=\'AL\')  and (pr.target = \'%s\') %s ",
                        $target_name, $sub_query);
    #print $query;
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $groups_manager = new GroupsManager();
        my $predictors_manager = new PredictorsManager();
        my $predictions_manager = new PredictionsManager();
        my $accounts_manager = new Core::Account();
        
        while(my($id, $type, $pfrmat, $email, $target, $model, $date, $groups_id, $predictors_id, $group, $group_code) = $sth->fetchrow_array()) {
            push(@predictions, {
                    ID => $id,                    
                    INDEX => $index,
                    TARGET => $target,
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    EMAIL => $email,
                    TYPE => $type,
                    GROUP => $group,
                    GROUP_CODE => sprintf('%03d', $group_code)
                }
            );
            $index++;
        }
    }    
    return @predictions;
}


sub tarballs_predictions_selected {
#    my ($self, $target_name, $type, $pfrmat, $target_type) = @_;
    my ($self, $target_name, $type, $pfrmat) = @_;
    my $sub_query = '';
    if(defined($type) && $type ne '') {
        if($type eq 'server') {
            $sub_query .= sprintf(' AND gr.type > 0 AND gr.type < 3 ');
        } elsif ($type eq 'human') {
            $sub_query .= sprintf(' AND gr.type = 1');
        }    
    }
    
    if(defined($pfrmat) && $pfrmat ne '') {
    	if ($pfrmat eq 'TR') {
		$sub_query .= sprintf(" AND pr.pfrmat = \'TS\' AND pr.target like \'%s%%\'", $pfrmat);
		# needed as TR format contains TS predictions 	
    	} else {
		$sub_query .= sprintf(' AND pr.pfrmat = \'%s\'', $pfrmat);
    	}
    }

    if(defined($target_name) && $target_name ne '') {
    	$sub_query .= sprintf(' AND pr.target = \'%s\'', $target_name);
    }

    my @predictions = ();
    my $query = sprintf("SELECT pr.id, pr.type, pr.pfrmat, pr.email, pr.target, pr.model, pr.date, pr.groups_id, pr.predictors_id, gr.name, gr.code FROM casp13.predictions pr
                        JOIN casp13.groups gr ON (pr.groups_id = gr.id) WHERE (1 = 1) %s ", $sub_query);
    print $query;
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $groups_manager = new GroupsManager();
        my $predictors_manager = new PredictorsManager();
        my $predictions_manager = new PredictionsManager();
        my $accounts_manager = new Core::Account();
        
        while(my($id, $type, $pfrmat, $email, $target, $model, $date, $groups_id, $predictors_id, $group, $group_code) = $sth->fetchrow_array()) {
            push(@predictions, {
                    ID => $id,                    
                    INDEX => $index,
                    TARGET => $target,
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    EMAIL => $email,
                    TYPE => $type,
                    GROUP => $group,
                    GROUP_CODE => sprintf('%03d', $group_code)
                }
            );
            $index++;
        }
    }    
    return @predictions;
}



sub build_prediction_name {
    my ($self, $target_name, $type, $model, $group_code) = @_;
    
    my $result = '';
    
    $result = sprintf("%s%s%03d_%d", $target_name, $type, $group_code, $model);
    
    return $result;
}

sub build_prediction_name_ex {
    my ($self, $target_name, $domain_index, $type, $group_code, $index, $suffix, $part) = @_;
    
    my $result = '';
    
    $result = sprintf("%s%s%03d_%d%s%s%s", $target_name, $type, $group_code, $index, (($suffix ne '') ? $suffix : ''), (($part > 0) ? sprintf("\_%d", $part) : ''), (($domain_index > 0) ? sprintf("-D%d", $domain_index) : ''));
    
    return $result;
}

sub prediction_name {
	my($self, $prediction_id) = @_;
	
	my $result = '';
	
    my $query = sprintf("SELECT target, pfrmat, model, groups_id FROM casp13.predictions WHERE (id = %d) LIMIT 1", $prediction_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my($target, $pfrmat, $model, $groups_id) = $sth->fetchrow_array();
        
        my $groups_manager = new GroupsManager();
        $result = $self->build_prediction_name($target, $pfrmat, $model, $groups_manager->code($groups_id));
    }
	
	return $result;
}

sub add {
	my($self, $email, $type, $pfrmat, $target, $groups_id, $model_id, $predictor_id) = @_;
	
	my $result = 0;
	
	my $pid = $self->prediction_exist($target, $model_id, $pfrmat, $groups_id);
	
	if($pid > 0) {
		$result = $self->update($pid, $email, $type, $pfrmat, $target, $groups_id, $model_id, $predictor_id);
	} else {
		my $query = sprintf("INSERT INTO casp13.predictions (groups_id, type, email, pfrmat, target, model, predictors_id) VALUES (%d, '%s', '%s', '%s', '%s', %d, %d)", $groups_id, $type, $email, $pfrmat, $target, $model_id, $predictor_id);
		my $sth = $self->{_database}->query($query);
		
		if(defined($sth)) {
			my $query = sprintf("SELECT MAX(id) FROM casp13.predictions");
			my $sth = $self->{_database}->query($query);
			
			# add logger
			($result) = $sth->fetchrow_array();
		}
	}
	
	return $result;
}

sub update {
    my ($self, $id, $email, $type, $pfrmat, $target, $groups_id, $model_id, $predictor_id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("UPDATE casp13.predictions SET target = '%s', type = '%s', pfrmat = '%s', email = '%s', model = %d, groups_id = %d, predictors_id = %d, date=now() WHERE (id = %d)", $target, $type, $pfrmat, $email, $model_id, $groups_id, $predictor_id, $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = 1;
    }
    
    return $result;
}

sub delete {
	my($self, $prediction_id) = @_;
	
	my $result = 0;
	
	my $query = sprintf("DELETE FROM casp13.predictions WHERE (id = %d)", $prediction_id);
	my $sth = $self->{_database}->query($query);
	   
	if(defined($sth)) { $result = 1; }
	
	return $result;
}

sub groups_id {
	my($self, $prediction_id) = @_;
	
	my $result = 0;
	
    my $query = sprintf("SELECT groups_id FROM casp13.predictions WHERE (id = %d)", $prediction_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
	
	return $result;
}

sub predictor_id {
	my($self, $prediction_id) = @_;
	
	my $result = 0;
	
    my $query = sprintf("SELECT predictors_id FROM casp13.predictions WHERE (id = %d)", $prediction_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
	
	return $result;
}

sub prediction_exist {
	my($self, $target, $model, $pfrmat, $groups_id) = @_;
	
	my $result = 0;
	
    my $query = sprintf("SELECT id FROM casp13.predictions WHERE (target = '%s') AND (model = %d) AND (pfrmat = '%s') AND (groups_id = %d)", $target, $model, $pfrmat, $groups_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
	
	return $result;
}

# the method returns list of servers' codes that submitted predictions 
# for target
sub get_servers_for_target{
    my ($self, $target) = @_;
    my @result;
    my $query = "SELECT DISTINCT g.code FROM casp13.groups g join casp13.predictions p on p.groups_id=g.id WHERE g.type>0 AND p.target=\'$target\' ORDER BY g.code ASC";
    my $sth = $self->{_database}->query($query);
    my $code;
    while( ($code)=$sth->fetchrow_array()) {
         $code = "000".$code;
         $code = substr($code, -3);
         push(@result, $code);
    }
    return @result;
}

1;