package MultimerResultsManager;

use strict;
use warnings;

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

use lib qw(Core);

use Database;
use Configuration;
use LocalConfiguration;

my $results_manager = undef;
my $DEBUG = 0;

# constructor
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 => '',
########################## file name
        target => undef,
        gr_code => undef,
	pfrmat => undef,
        model => undef,
########################### results
	mm_size => '',
	stoich => '',
	symm => '',
	symm_size => '',
	symm_rmsd => '',
	no_conts => '',
	no_clashes => '',
	f1 => '',
	qs_glob => '',
	qs_best => '',
	oligo_lddt => '',
	w_lddt => '',
	chain_map => '',
	jaccard_d => '',
	align_size => '',
	align_length => '',
	orient => '',	
	global_rmsd => '',
	local_rmsd => '',
	iface_rmsd => '',
	rank_score => '',
	prec_iface => '',
	recall_iface => '',
	symmgr_rmsd => '',
	min_symmgr_rmsd => '',
	dens_corr => '',
	tm_score => '',
	gdt_ts => ''
    );
    return %model;
}

sub is_results_table_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	########################## file name
	$colunm_name eq 'target' ||
	$colunm_name eq 'gr_code' ||
	$colunm_name eq 'model' ||
	########################### results
	$colunm_name eq 'mm_size' ||
	$colunm_name eq 'stoich' ||
	$colunm_name eq 'symm' ||
	$colunm_name eq 'symm_size' ||
	$colunm_name eq 'symm_rmsd' ||
	$colunm_name eq 'no_conts' ||
	$colunm_name eq 'no_clashes' ||
	$colunm_name eq 'f1' ||
	$colunm_name eq 'qs_glob' ||
	$colunm_name eq 'qs_best' ||
	$colunm_name eq 'oligo_lddt' ||
	$colunm_name eq 'w_lddt' ||
	$colunm_name eq 'chain_map' ||
	$colunm_name eq 'jaccard_d' ||
	$colunm_name eq 'align_size' ||
	$colunm_name eq 'align_length' ||
	$colunm_name eq 'orient' ||
	$colunm_name eq 'global_rmsd' ||
	$colunm_name eq 'local_rmsd' ||
	$colunm_name eq 'iface_rmsd' ||
	$colunm_name eq 'rank_score' ||
	$colunm_name eq 'prec_iface' ||
	$colunm_name eq 'recall_iface' ||
	$colunm_name eq 'symmgr_rmsd' ||
	$colunm_name eq 'min_symmgr_rmsd' ||
	$colunm_name eq 'dens_corr' ||
	# TM multimer
	$colunm_name eq 'tm_score' ||
	$colunm_name eq 'gdt_ts'
  ) {
	$result = 1;
	}
return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;

    my $query = sprintf("SELECT COUNT(id) FROM casp13.multimer_results 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 re.id FROM casp13.multimer_results re 
	WHERE re.target=\'%s\' AND re.gr_code=%d AND re.model=%d ",
      	$model{target}, $model{gr_code}, $model{model});
    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_results {
    my ($self, %model) = @_;
    my $result = 0;
    my $set_query = '';
    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || $value eq '' || !defined($value)) {
		# do nothing
	}else{
		#list of columns which not updated
		if($self->is_results_table_column($key) == 1) {
			$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
		}
	}
    }
    return $result if(! $self->exist( $model{id}));
    my $query = sprintf("UPDATE casp13.multimer_results SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
	print "------------------------UPDATE: $model{id} \n" if $DEBUG;
	print $query . "\n" if $DEBUG;

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

    if(defined($sth)) {
        $result = 1;
    }
    return $result;
}

sub add_results {
    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' || !defined($value) || $value eq '') {
		# do nothing 
	}else{
		if($self->is_results_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.multimer_results ( %s ) VALUES ( %s ) RETURNING id", $column_names, $column_values);

    print $query."\n" if $DEBUG;

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        ($result) = $sth->fetchrow_array();
    }
	print "---------------------ADD: $result\n" if $DEBUG;
	if ($result == 0) {
	   print "\nPROBLEM !!!\n" . $query . "\n";
	}

    return $result;
}

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


sub exist_record_per_res{
    my ($self, $multimer_results_id, $chain_m) = @_;
    my $result = 0;
    my $query = sprintf("SELECT id FROM casp13.multimer_results_per_res WHERE multimer_results_id=%d AND chain_m=\'%s\' LIMIT 1", $multimer_results_id, $chain_m);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        ($result) = $sth->fetchrow_array();
    }
    return $result;
}

sub add_record_per_res{
    my ($self, $multimer_results_id, $chain_m, $chain_t, $lddt, $lddt_per_res) = @_;
    my $result = 0;
    my $query = sprintf("INSERT INTO casp13.multimer_results_per_res (multimer_results_id, chain_m, chain_t, lddt, lddt_per_res) 
		VALUES (%d, \'%s\', \'%s\', %.5f, \'%s\') RETURNING id", $multimer_results_id, $chain_m, $chain_t, $lddt, $lddt_per_res);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        ($result) = $sth->fetchrow_array();
    }
    return $result;
}

sub update_record_per_res{
    my ($self, $id, $multimer_results_id, $chain_m, $chain_t, $lddt, $lddt_per_res) = @_;
    my $result = 0;
    my $query = sprintf("UPDATE casp13.multimer_results_per_res SET multimer_results_id=%d, chain_m=\'%s\', chain_t=\'%s\', lddt=%.5f, lddt_per_res=\'%s\' 
                 WHERE id=%d", $multimer_results_id, $chain_m, $chain_t, $lddt, $lddt_per_res, $id);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = $id;
    }
    return $result;
}



sub get_targets{
    my ($self, $param) = @_;
    my @result = ();
    my $where = '';
    if (defined($param->{sub_type})){
	if ($param->{sub_type} =~ m/[RSNFAXL]/i){
		$where = " WHERE target SIMILAR TO '[RNSFAXLnsfaxl]%%' "; 
	} elsif ($param->{sub_type} =~ m/[H]/i) {
		$where = " WHERE target SIMILAR TO '[HOT]%%' AND target<>'T1006o_ASU' ";
	}
    }
    my $query = "SELECT DISTINCT target, substring(target for 100 from 2) AS targ2 FROM casp13.multimer_results $where ORDER BY targ2, target";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)){
	my $index = 1;
	while(my($target, $tmp) = $sth->fetchrow_array()) {
		push @result, {
			INDEX => $index++,
			_TARGET => $target,
		};
	}
    }
    return @result;
}

sub get_rows{
    my ($self, $param) = @_;
    my @result = ();
    my $subquery = "";
    if (defined($param->{target})){
	$subquery .= sprintf " WHERE target=\'%s\'", $param->{target};
    }
    my $query = "SELECT target, gr.name, gr.type, gr_code, model, 
		mm_size, stoich, symm,  symm_size, symm_rmsd, no_conts, no_clashes, f1, 
		qs_glob, qs_best, oligo_lddt, w_lddt, chain_map,
		jaccard_d, 
		align_size, align_length, orient, global_rmsd, local_rmsd, iface_rmsd, prec_iface, recall_iface,
		symmgr_rmsd, min_symmgr_rmsd, dens_corr,
		tm_score, gdt_ts
		FROM casp13.multimer_results mre 
		JOIN casp13.groups gr ON mre.gr_code=gr.code
		$subquery  ORDER BY qs_glob DESC NULLS LAST";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)){
	my $index = 1;
        while(my($target, $gr_name, $gr_type, $gr_code, $model, 
		 $mm_size, $stoich, $symm,  $symm_size, $symm_rmsd, $no_conts, $no_clashes, $f1, 
		 $qs_glob, $qs_best, $oligo_lddt, $w_lddt, $chain_map, 
		 $jaccard_d,
		 $align_size, $align_length, $orient, $global_rmsd, $local_rmsd, $iface_rmsd, $prec_iface, $recall_iface,
		 $symmgr_rmsd, $min_symmgr_rmsd, $dens_corr,
		 $tm_score, $gdt_ts
			) = $sth->fetchrow_array()) {
                push @result, {
			INDEX => $index,
                        TARGET => $target,
			GROUP_NAME => (defined($gr_name)? $gr_name : '-'),
			GROUP_TYPE => (defined($gr_type)? $gr_type : '0'),
			GROUP_CODE => sprintf ("%03d", $gr_code),
			MODEL_NAME => sprintf ("TS%03d_%d", $gr_code, $model),
			FULL_MODEL_NAME => sprintf("%sTS%03d_%d%s", ($target =~ m/o$/ ? substr($target, 0, rindex($target, 'o')) : $target), $gr_code, $model, ($target =~ m/o$/ ? 'o' : '')),
			MM_SIZE => (defined($mm_size)? $mm_size : '-'), 
			STOICH => (defined($stoich)? $stoich : '-'),
			SYMM => (defined($symm)? $symm : '-'),
			SYMM_SIZE => (defined($symm_size) ? $symm_size : '-'),
			SYMM_RMSD => (defined($symm_rmsd) ? sprintf("%.2f", $symm_rmsd) : '-'),
			NO_CONTS => (defined($no_conts) ? $no_conts : '-'),
			NO_CLASHES => (defined($no_clashes) ? $no_clashes : '-'),
			F1 => (defined($f1) ? sprintf("%.1f", $f1) : '-'),
			QS_GLOB => (defined($qs_glob) ? sprintf("%.3f", $qs_glob): '-'),
			QS_BEST => (defined($qs_best) ? sprintf("%.3f", $qs_best): '-'),
			OLIGO_LDDT => (defined($oligo_lddt) ? sprintf("%.3f", $oligo_lddt): '-'),
			W_LDDT => (defined($w_lddt) ? sprintf("%.3f", $w_lddt): '-'),
			CHAIN_MAP => (defined($chain_map) ? sprintf("%s", $chain_map): '-'),
			JACCARD_D => (defined($jaccard_d) ? sprintf("%.2f", $jaccard_d): '-'),
			ALIGN_SIZE => (defined($align_size) ? $align_size : '-'),
			ALIGN_LENGTH => (defined($align_length) ? $align_length : '-'),
			ORIENT => (defined($orient) ? sprintf("%.2f", $orient): '-'),
			GLOBAL_RMSD => (defined($global_rmsd) ? sprintf("%.2f", $global_rmsd): '-'),
			LOCAL_RMSD => (defined($local_rmsd) ? sprintf("%.2f", $local_rmsd): '-'),
			IFACE_RMSD => (defined($iface_rmsd) ? sprintf("%.2f", $iface_rmsd): '-'),
			PREC_IFACE => (defined($prec_iface) ? sprintf("%.2f", $prec_iface): '-'),
			RECALL_IFACE => (defined($recall_iface) ? sprintf("%.2f", $recall_iface): '-'),
			SYMMGR_RMSD => (defined($symmgr_rmsd)? $symmgr_rmsd : '-'),
			MIN_SYMMGR_RMSD => (defined($min_symmgr_rmsd)? $min_symmgr_rmsd : '-'),
			DENS_CORR => (defined($dens_corr)? sprintf("%.3f",$dens_corr) : '-'),
			TM_SCORE => (defined($tm_score)? sprintf("%.3f",$tm_score) : '-'),
			GDT_TS => (defined($gdt_ts)? sprintf("%.3f",$gdt_ts) : '-'),
                };
		$index++;
        }
    }
    return @result;
}

sub get_assissted_results{
   my ($self, $param) = @_;
   my @letters = qw/S N A X R x n/;
   my $reg_target;
   if (!defined($param->{target}) ){
	return;
   }
   if ($param->{target} =~ m/o$/) {
	$reg_target = 'T'.substr($param->{target}, 1);
   }  else {
	$reg_target = 'H'.substr($param->{target}, 1);
   }

   my @SCORES = qw/qs_best qs_glob oligo_lddt w_lddt jaccard_d f1 local_rmsd global_rmsd iface_rmsd/;
   my $subqueue = "";
   my @models = $self->getAssistedModels($param);
   my @results;
   foreach  my $m (@models){
     my $scores = '';
     foreach my $score (@SCORES){
       if ($score =~ m/rmsd/) {
           $scores .= "min(CASE WHEN $score>=0.0 THEN $score ELSE null END) as $score,";
       } else {
           $scores .= "max(CASE WHEN $score>=0.0 THEN $score ELSE null END) as $score,"
       }
     }
     $scores =~ s/,$//;
     my $where = '';
     if (defined $param->{comp2} && $param->{comp2} eq 'best_all'){
   	$where = sprintf ("target='%s'", $reg_target); 
     } elsif (defined $param->{comp2} && $param->{comp2} eq 'best') {
  	$where = sprintf ("target='%s' AND gr_code=%d ", $reg_target, $m->{_gr_code}); 
     } elsif (defined $param->{comp2} && $param->{comp2} eq 'first') {
	$where = sprintf ("target='%s' AND gr_code=%d  AND model=1 ", $reg_target, $m->{_gr_code});
     }
     my $query = "(SELECT target, $scores FROM casp13.multimer_results WHERE $where GROUP BY target) ";
     my $where2;
     foreach my $l (@letters) {
	$where2 = sprintf( " target='%s%s' AND gr_code=%d  AND model=%d ", $l, substr($param->{target}, 1), $m->{_gr_code}, $m->{_model});
   	$query .= "
		UNION
		(SELECT target, $scores FROM casp13.multimer_results WHERE $where2 GROUP BY target)
	";
     }
     my $sth = $self->{_database}->query($query);
        if (defined($sth) && ($sth->rows() > 0)){
                my $HASH;
                while(defined(my $hash_ref = $sth->fetchrow_hashref())) {
                        if ($hash_ref->{target} =~ m/^[TH]/){
                                $HASH->{T0} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^S/){
                                $HASH->{S} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^N/){
                                $HASH->{N} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^n/){
                                $HASH->{n} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^F/){
                                $HASH->{F} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^A/){
                                $HASH->{A} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^X/){
                                $HASH->{X} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^x/){
                                $HASH->{x} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^R/){
                                $HASH->{R} = $hash_ref;
                        }
                }
                foreach my $score (@SCORES){
                        if (defined($HASH->{"T0"}->{$score})){
                                $m->{"T0"."_$score"} = sprintf("%6.3f", $HASH->{"T0"}->{$score});
                        } else {
                                $m->{"T0"."_$score"} = '-';
                        }
			if (defined($param->{score}) && $param->{score} eq $score){
                                        $m->{"T0_SCORE"} = $m->{"T0_$score"};
                        }
                        foreach my $letter(@letters){
				my $_letter = $letter;
				if ($_letter =~ m/^[a-z]/){
					$_letter = '_'.$_letter;
				}
                                if (defined($HASH->{"$letter"}->{$score})){
                                        $m->{"$_letter"."_$score"} = sprintf("%6.3f", $HASH->{"$letter"}->{$score});
                                        if (defined($HASH->{"T0"}->{$score})){
                                                $m->{"$_letter"."_diff"."_$score"} = sprintf("%6.3f", $HASH->{"$letter"}->{$score} - $HASH->{"T0"}->{$score});
                                        } else {
                                                $m->{"$_letter"."_diff"."_$score"} = '-';
                                        }
                                } else {
                                        $m->{"$_letter"."_$score"} = '-';
                                        $m->{"$_letter"."_diff"."_$score"} = '-';
                                }
				if (defined($param->{score}) && $param->{score} eq $score){
					$m->{"$_letter"."_SCORE"} = $m->{"$_letter"."_$score"};
					$m->{"$_letter"."_SCORE_DIFF"} = $m->{"$_letter"."_diff_$score"};
				}
                        }
                }
                $m->{MODEL_NAME} = sprintf("TS%03d_%d",  $m->{_gr_code}, $m->{_model});
                push @results, $m;
                #print "\n";

        } # end if 
   } # end loop over models
   return @results;
}

# the method fetches the all models submitted to any of the assisted targets
# e.g. [SNXRsnx]1234
sub getAssistedModels{
    my ($self, $param) = @_;
    if (!defined($param->{target})){
        return;
    }
    my @results;
    my $query = sprintf("SELECT DISTINCT  gr_code, model  from casp13.multimer_results re 
        WHERE target SIMILAR TO '[SRNFAXLsnfaxl]%s' 
        ORDER BY gr_code, model", substr($param->{target}, 1));
    
#    print "=\n=\n=\n
#
#     $query\n=\n=\n=\n";
    my $sth = $self->{_database}->query($query);
    my $index = 1;
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($gr_code, $model) = $sth->fetchrow_array()) {
            push(@results,
                {_gr_code => $gr_code,
                _model => $model,
		INDEX => $index++,
                });
        }
    }
    return @results;
}


1;