package ResultsTargetManager;

use strict;
use warnings;

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

use lib qw(Core);

use Database;

use PDBUtils;

use Configuration;
use LocalConfiguration;
use TargetsManager;

my $results_manager = undef;

sub new {
    my ($class) = @_;    
    return $results_manager if(defined($results_manager));
    my %model = (ID => '');
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}),
        _model => \%model
    };
    
    $results_manager = bless $self, $class;
    return $results_manager;
}

sub get_new_model {
    my ($self) = @_;    
    my %model = (
########################### system        
	id => '',
	targets_id =>'',
        domains_index =>'',
	domains_real_index =>'',
        is_public => '',
	access_type => '',
	avr_gdt_ts_20s => '',
########################## other fields
        TARGET => '',
	FULL_TARGET => '',
        RANGE => '',
	NAME => ''
    );
    return %model;
}
sub is_results_target_table_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	$colunm_name eq 'targets_id' ||
	$colunm_name eq 'domains_index' ||
	$colunm_name eq 'domains_real_index' ||	
	$colunm_name eq 'is_public' ||
	$colunm_name eq 'access_type' ||
	$colunm_name eq 'avr_gdt_ts_20s'
	
  ) {
	$result = 1;
}
return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM casp13.results_targets WHERE (id = %d)", $id);
    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 exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT rt.id FROM casp13.results_targets rt JOIN casp13.targets tr ON (rt.targets_id = tr.id)
	WHERE tr.name=\'%s\' and rt.domains_real_index=%d",
      	$model{TARGET}, $model{domains_real_index});
    my $sth = $self->{_database}->query($query);
    #print $query ."\n";
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
	$result = $id;
    }
    
    return $result;
}

sub update {
    my ($self, %model) = @_;
    
    my $result = 0;
    my $set_query = '';

    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{ 
		#list of columns which not updated
		if($self->is_results_target_table_column($key) == 1) {
			$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
		}
	}
    }

    return $result if(! $self->exist( $model{id}));
    my $query = sprintf("UPDATE casp13.results_targets SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
	print "------------------------UPDATE: $model{id} \n";

    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, %model) = @_;
    my $result = 0;

    my $column_names = '';
    my $column_values = '';
    my $values_count = 0;

    while ( my ($key, $value) = each(%model) ) {	
	if($key eq 'id' || $key eq 'date' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{ 
		if($self->is_results_target_table_column($key)==1) {
			$column_names .= sprintf(" %s%s ",($values_count == 0)?'':',', $key);
			$column_values .= sprintf(" %s\'%s\' ",($values_count == 0)?'':',', $value);;
			$values_count++;
		}
	}
    }

    my $query = sprintf("INSERT INTO casp13.results_targets ( %s ) VALUES ( %s )", $column_names, $column_values);

    my $tmp = $query;
print $query . "\n";

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.results_targets");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    } 
	print "---------------------ADD: $result ";
	if ($result == 0) {
		print "\n" . $tmp . "\n";
	}

    return $result;
}



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

sub delete_results_for_all_domains_for_target {
    my ($self, $target) = @_;
    my $result = 0;
    my $where;
    if($target =~ m/^[TNSFAXLnsfaxl][0-9]{4}(s\d){0,1}$/ || $target =~ m/^R[0-9]{4}-D([1-9])$/ || $target =~ m/^R[0-9]{4}$/){
	$where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domains_index<>0 ",$target);
    }else{
	return;
    }
    my $query = sprintf("DELETE FROM casp13.results_targets rt WHERE %s", $where);
    #print $query."\n";
    #return; 

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub delete_results_for_target {
    my ($self, $target) = @_;
    my $result = 0;
    my $where;
    if($target =~ m/^(N0981-D[1-9]+)$/ ){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domains_index=0 ", $1 );
    } elsif($target =~ m/^([TNSFAXLnsfaxl][0-9]{4}(s\d){0,1})-D([1-9]+)$/ ){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domains_index=%s ", $1, $3);
    } elsif($target =~ m/^(R0957s2)-D([1-9]+)$/ ){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domains_index=%s ", $1, $2);
    } elsif ($target =~ m/^[TRNSFAXLnsfaxl][0-9]{4}(s\d){0,1}$/ || $target =~ m/^[R][0-9]{4}-D([1-9])$/){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domains_index=0 ",$target);
    }else{
        return;
    }
    my $query = sprintf("DELETE FROM casp13.results_targets rt WHERE %s", $where);
    #print $query."\n";
    #return; 

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}


sub get_range {
	my ($self) = @_;
	my $sth = $self->{_database}->query("SELECT min(name) as min, max(name) as max FROM casp13.targets WHERE name similar to '(R|T)([0-9])%'");
	my ($min, $max) = $sth->fetchrow_array();
	return (min => int(substr($min, 1)), max => 1023); # int(substr($max, 1)));
}

sub get_result_targets {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};	
    #my $view_targets = $params->{view_targets};
    #my $view = $params->{view};
   #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #foreach my $parameter (keys %{$params}) {
    #	print $parameter . " => " . $params->{$parameter}. "<br>";
    #}	
    $field = (!defined($field) || ($field eq '')) ? 'tr.name, rt.domains_index' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####
    
#    my $targets_manager = new TargetsManager();

    my $sub_query = "";

    my $domain = 0;
    my $target_name = ""; 

    if(defined($params->{is_public}) && ($params->{is_public} ne '')) {
	$sub_query .= sprintf(" and rt.is_public=\'%s\' ", $params->{is_public});	
    }
    
    if(defined($params->{access_type}) && ($params->{access_type} ne '')) {
	$sub_query .= sprintf(" and rt.access_type=\'%s\' ", $params->{access_type});	
    }
        
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '') && ($params->{groups_id} =~ /^\d+$/)) {
	$sub_query .= sprintf(" and (select count(pred.id) from casp13.predictions pred join casp13.results results on (results.predictions_id = pred.id) where pred.target=tr.name and pred.groups_id=%d and pred.pfrmat IN (\'TS\',\'AL\') and results.n1_4>19 limit 1) > 0  ", $params->{groups_id});	
    }

#if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
#	$sub_query .= sprintf(" and exists (select pred.id from casp13.predictions pred join casp13.results results on (results.predictions_id = pred.id) where pred.target=tr.name and pred.groups_id=%d and pred.pfrmat IN (\'TS\',\'AL\') and pred.target like \'(T|R)([0-9])\%\' and results.n1_4>19 limit 1)  ", $params->{groups_id});	
#    }    


  
    if(defined($params->{target}) && ($params->{target} ne '')) {
	$domain = $self->get_domain_from_full_target_name($params->{target});
	$target_name = $self->get_target_from_full_target_name($params->{target});
	$sub_query .= sprintf(" and tr.name=\'%s\' ", $target_name);	
	$sub_query .= sprintf(" and rt.domains_index=\'%s\' ", $domain);	
    }
    
    if(defined($params->{tr_type}) && ($params->{tr_type} ne '')) {
	if($params->{tr_type} eq 's') {
	    $sub_query .= sprintf(" and tr.is_server_only > 0 ");
	}
	if($params->{tr_type} eq 'hs') {
	    $sub_query .= sprintf(" and tr.is_server_only = 0 ");
	}
	if($params->{tr_type} eq 'refinement'){
	    $sub_query .= sprintf(" and tr.is_refinement = 1 ");
	}
        if($params->{tr_type} eq 'others'){
            $sub_query .= sprintf(" and tr.name similar to '[NSFAXLnsfaxl]%%' ");
	    if (defined($params->{sub_type}) && $params->{sub_type} =~ m/^[Ss]$/ ) {
		$sub_query .= sprintf(" and tr.name similar to '[Ss]%%' ");
	    } elsif (defined($params->{sub_type}) && $params->{sub_type} =~ m/^[Nn]$/ ) {
                $sub_query .= sprintf(" and tr.name similar to '[Nn]%%' ");
            } elsif (defined($params->{sub_type}) && $params->{sub_type} =~ m/^[Aa]$/ ) {
                $sub_query .= sprintf(" and tr.name similar to '[Aa]%%' ");
            } elsif (defined($params->{sub_type}) && $params->{sub_type} =~ m/^[Xx]$/ ) {
                $sub_query .= sprintf(" and tr.name similar to '[Xx]%%' ");
            } elsif (defined($params->{sub_type}) && $params->{sub_type} =~ m/^[Ff]$/ ) {
                $sub_query .= sprintf(" and tr.name similar to '[Ff]%%' ");
            }
        }
	if($params->{tr_type} eq 'multimer'){
            $sub_query .= sprintf(" and rt.domains_index>=70 and rt.domains_index<=99 ");
        }

    }
    
    if(defined($params->{dm_class}) && ($params->{dm_class} ne '')) {
	if($params->{dm_class} eq 'tbm') {
	    $sub_query .= sprintf(" and dm.domain_classifications_id IN (1,2,4) ");
	}
	if($params->{dm_class} eq 'fm') {
	    $sub_query .= sprintf(" and dm.domain_classifications_id IN (3,4) ");
	}
    }
    
        
    if(defined($params->{results}) && ($params->{results} ne '')) {
	if($params->{results} eq 'intermediate') {
	    $sub_query .= sprintf(" and rt.domains_index = 0 ");
	}
	if($params->{results} eq 'final') {
	    $sub_query .= sprintf(" and rt.domains_index <> 0 ");
	}
    }

    if(defined($params->{tr_type}) && ($params->{tr_type} ne '')  && ($params->{tr_type} ne 'refinement') && ($params->{tr_type} ne 'others') && ($params->{tr_type} ne 'multimer')){
	if (defined($params->{offset}) && ($params->{offset} > 0)) {
		my $offset = int($params->{offset});
		$sub_query .= sprintf(" AND (tr.name BETWEEN 'T%04i' AND 'T%04i') AND rt.domains_index<70 ", $offset, $offset + 29);
	}
    }
	if (defined($params->{domain})) {
		$sub_query .= sprintf(" AND rt.domains_index = %d ", $params->{domain});
	}


    my @results = ();

    my $query = sprintf("SELECT rt.id, rt.targets_id, rt.domains_index, rt.domains_real_index, rt.is_public, rt.access_type, tr.name, dm.range, dm.length, ss.number_of_aa, tr.is_server_only, dmc.name, dm.domain_classifications_id, ss.sequence, rt.avr_gdt_ts_20s
	FROM casp13.results_targets rt JOIN casp13.targets tr ON  (tr.id = rt.targets_id)
	LEFT JOIN casp13.domains dm ON (dm.targets_id = rt.targets_id and  dm.\"index\" = rt.domains_index)
	LEFT JOIN casp13.domain_classifications dmc ON (dmc.id = dm.domain_classifications_id)
	JOIN casp13.submitted_sequences ss ON (ss.id = tr.submitted_sequences_id)
	WHERE 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);
    
    #print $query;
    
#SELECT rt.id, rt.targets_id, rt.domains_index, rt.domains_real_index, rt.is_public, tr.name, dm.range, dm.length, ss.number_of_aa
#FROM casp13.results_targets rt JOIN casp13.targets tr ON (tr.id = rt.targets_id)
#LEFT JOIN casp13.domains dm ON (dm.targets_id = rt.targets_id and  dm."index" = rt.domains_index) 
#JOIN casp13.submitted_sequences ss ON (ss.id = tr.submitted_sequences_id)
#
# WHERE 1=1 and rt.is_public='1'
#ORDER BY tr.name ASC
    
    
    #print $query; 

    my $sth = $self->{_database}->query($query);

    #print $query;

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $targets_id, $domains_index, $domains_real_index, $is_public, $access_type, $target_name, $range, $length, $number_of_aa, $is_server_only, $classifications, $classifications_id, $sequence, $avr_gdt_ts) = $sth->fetchrow_array()) {
	    my $spice_name = $self->build_name($target_name, $domains_real_index);
	    $spice_name=~ tr/-/_/;
	    my $subunit = undef;
	    if ($target_name =~ m/s([1-9])$/) {
		$subunit = $1;
	    }
#	    my $are_expired_all_associated_targets = $targets_manager->are_expired_all_associated_targets($target_name,'human');
	    push(@results, {
		INDEX => $index,
		id => $id,
		targets_id => $targets_id,
		domains_index => $domains_index,
		domains_real_index => $domains_real_index,
		is_public => $is_public,
		access_type => $access_type,
		target_name => $target_name,
		NAME => $self->build_name($target_name, $domains_real_index),
		SPICE_NAME => $spice_name,
		RANGE => $range,
		LENGTH => $length,
		NUMBER_OF_AA => sequence_length($sequence, $subunit),
		is_server_only => $is_server_only,
		classifications => $classifications,
		classifications_id => $classifications_id,
		AVR_GDT_TS => ((defined($avr_gdt_ts) && $avr_gdt_ts ne '') ? sprintf("%.2f", $avr_gdt_ts) : ''),
#		are_expired_all_associated_targets => $are_expired_all_associated_targets
		}
	    );	
	    $index++;
        }
    }
    return @results;
}


sub get_targets_count {
    my ($self, $params) = @_;
    my $sub_query = "";
    my $domain = 0;
    my $target_name = ""; 

    if(defined($params->{is_public}) && ($params->{is_public} ne '')) {
	$sub_query .= sprintf(" and rt.is_public=\'%s\' ", $params->{is_public});	
    }
    if(defined($params->{results}) && ($params->{results} ne '')) {
	if($params->{results} eq 'intermediate') {
	    $sub_query .= sprintf(" and rt.domains_index = 0 ");
	}
	if($params->{results} eq 'final') {
	    $sub_query .= sprintf(" and rt.domains_index <> 0 ");
	}
    }

    my $result = ();
    my $query = sprintf("SELECT count(DISTINCT rt.targets_id)
	FROM casp13.results_targets rt JOIN casp13.targets tr ON  (tr.id = rt.targets_id)
	LEFT JOIN casp13.domains dm ON (dm.targets_id = rt.targets_id and  dm.\"index\" = rt.domains_index)
	JOIN casp13.submitted_sequences ss ON (ss.id = tr.submitted_sequences_id)
	WHERE 1=1 %s ", $sub_query);
#printf("Content-Type: text/html; charset=ISO-8859-1\n\n");    
#print $query; 

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

sub is_target_evaluated {
    my ($self, $target_id, $mode) = @_;
    my $sub_query = "";
    if($mode eq 'intermediate') {
	$sub_query .= sprintf(" and rt.domains_index = 0 ");
    }
    if($mode eq 'final') {
	$sub_query .= sprintf(" and rt.domains_index <> 0 ");
    }
    if($mode eq 'final_with_domains') {
	$sub_query .= sprintf(" and rt.domains_index <> 0  and t.is_final_eval = 1 ");
    }

    my $result = ();
    my $query = sprintf("SELECT count(DISTINCT rt.targets_id)
	FROM casp13.results_targets rt JOIN casp13.targets t on rt.targets_id=t.id WHERE rt.targets_id = %d %s ", $target_id, $sub_query);
#printf("Content-Type: text/html; charset=ISO-8859-1\n\n");    
#print $query; 

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

sub build_name {
    my ($self, $target_name, $domain_index) = @_;
    my $result = '';
    $result = sprintf("%s%s", $target_name, (($domain_index > 0) ? sprintf("-D%d", $domain_index) : ''));
    return $result;
}

sub get_domain_from_full_target_name {
    my ($self, $target_name) = @_;
    my $result = 0;
    if(defined($target_name) && ($target_name ne '')) {
	if ($target_name =~ /^R/ && $target_name !~ m/^R0957s2/) {
		$result = 0;
	} elsif ($target_name =~ m/^N0981-D\d+/) {
                $result = 0;
        } elsif ($target_name =~ /^(\S+)-D(\S+)/) {
		$result = $2;
	}
    }
    return $result;
}

sub get_target_from_full_target_name {
    my ($self, $target_name) = @_;
    my $result = $target_name;
    if(defined($target_name) && ($target_name ne '')) {
	if ($target_name =~ /^R/ && $target_name !~ m/^R0957s2/ ) {
                $result = $target_name;
        } elsif ($target_name =~ m/^N0981-D\d+/ ) {
                $result = $target_name;
        } elsif ($target_name =~ /^(\S+)-D(\S+)/) {
		$result = $1;
	}
    }
    return $result;
}


sub get_ranges_array {
    my ($self, $full_target) = @_;
    my $range_str = "";
    
    my $param = {'target'=>$full_target};
    my @targets = $self->get_result_targets($param);
    
    for (my $i = 0; $i < scalar(@targets); $i++) {	
	if(defined($targets[$i]->{RANGE}) && $targets[$i]->{RANGE} ne '' && $targets[$i]->{RANGE} ne '000' && $targets[$i]->{LENGTH} ne '0' ) {
            $range_str = $targets[$i]->{RANGE};
        } else {
	    $range_str = sprintf("1-%s", $targets[$i]->{NUMBER_OF_AA})	
	}
    }
    
    my @ranges = ();
    my @subdomains = split(/,/, $range_str);
    #foreach my $subdomain (@subdomains) {
#	    $subdomain =~ /^(\d+)[-](\d+)$/;
#	    for (my $j = int($1); $j <= int($2); $j++) {
#		    push @ranges, $j;
#	    }
#    }
    foreach my $subdomain (@subdomains) {
	my $from = 0;
	my $to = 0;
	if ($subdomain =~ /^(\d+)[-](\d+)$/) {
	    $from = int($1);
	    $to = int($2);
	} elsif($subdomain =~ /^(\d+)$/) {
	    $from = int($1);
	    $to = $from;
	}
	for (my $j = $from; $j <= $to; $j++) {
	    push @ranges, $j;
	}
    }
    return @ranges;
    
}

sub get_range_array_from_pdbfile{
    my ($self,$full_target) = @_;
    my %hash;
    my $file = sprintf("%s/%s.pdb", $LOCAL_CONFIG->{DATA_TARGETS_DIR}, $full_target);
    if (! -f $file) {
        return ;
    }
    open (F, "grep \"^ATOM\" $file | ");
    while(defined(my $line = <F>)){
	my $res = substr($line,22,4);
	$res =~ s/^\s+//;
	$res =~ s/\s+$//;
	$hash{$res} = 1;
    }
    close(F); 
    my @resNos = keys %hash;
    @resNos = sort {$a<=>$b} @resNos;
    return @resNos;
}

sub get_range_from_pdbfile {
    my ($self,$full_target) = @_;
    my $start;    
    my $end;
    my $file = sprintf("%s/%s.pdb", $LOCAL_CONFIG->{DATA_TARGETS_DIR}, $full_target);
    if (! -f $file) {
	return ;
    }
    open (FIRST, "grep \"^ATOM\" $file | head -n 1 | ");
    if (defined(my $line = <FIRST>)) {
        $start = substr($line,22,4);
        $start =~ s/^\s+//;
        $start =~ s/\s+$//;
    }
    close(FIRST);

    open (LAST, "grep \"^ATOM\" $file | tail -n 1 | ");
    if (defined(my $line = <LAST>)) {
       $end = substr($line,22,4);
       $end =~ s/^\s+//;
       $end =~ s/\s+$//;
    }
    close(LAST);
    return ($start, $end);
}

sub ToString {
    my ($self, $PS, %model) = @_;
    # $PS -   print separator could be "\n" for text or "<br>" for HTML    
    while ( my ($key, $value) = each(%model) ) {
        print sprintf("%s => %s  %s ", $key, $value, $PS);
    }    
    return 0; 
}

sub getTargets4SkipImage {
    my ($self) = @_;
    my @targets = ();
    my $query = "SELECT target_code FROM casp13.targets_access WHERE q4=0 ORDER BY target_code";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($code) = $sth->fetchrow_array()){
                push(@targets, $code);
        }
    }
    return @targets;
}

sub getTargets4SkipStr {
    my ($self) = @_;
    my @targets = ();
    my $query = "SELECT target_code FROM casp13.targets_access WHERE q4=0 OR q1=0 ORDER BY target_code";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($code) = $sth->fetchrow_array()){
                push(@targets, $code);
        }
    }
    return @targets;
}


1;