#!/usr/bin/perl 

use strict;
use warnings;

package ResultsUploadOligomericManager;

use lib qw(Core);
use lib qw(Classes);

use Database;
use Configuration;
use LocalConfiguration;


my $manager = undef;
my $SCORES  = {
	# lga sda
	gdt_ts_4 => {
	  n1_4 => undef,
          n2_4 => undef,
          dist_4=> undef,
          nres_4 => undef,
          rmsd_4 => undef,
          gdt_ts_4 => undef,
          lga_s_4 => undef,
          lga_q_4 => undef,
          gdt_ca_percentage_4 => undef,
          asgn_4 => undef,
          np_p_4 => undef,
          gdt_ha_4 => undef,
	  alignment_4 => undef,
	  ace_rms_ca => undef,
	  ace_rms_all => undef,
	  lga_4_tr =>undef, # gdc_sc
          gdc_all =>undef,
	  # CACA
	  errors_caca => undef,
	  mean => undef,
	  standard_dev => undef,
	  # z-scores
	  gdt_4_z_score => undef,
          gdt_4_z_score_server => undef,
          gdt_4_z_score_all => undef,
          gdt_4_z_score_server_all => undef,
          gdt_ha_4_z_score_server => undef,
          gdt_ha_4_z_score_all => undef,
	},
	# lga sia
	lga_s_5 => {
	  n1_5 => undef,
          n2_5 => undef,
          dist_5 => undef,
          nres_5 => undef,
          rmsd_5 => undef,
          gdt_ts_5 => undef,
          lga_s_5 => undef,
          lga_q_5 => undef,
	  eqv_alignment => undef,
          al0 => undef,
          al4 => undef,
          al4x => undef,
          al0p => undef,
          al4p => undef,
          al4xp => undef,
          eqv0 => undef,
         # eqv4 => undef,
          eqvi => undef,
	  # z-scores
          al0p_5_z_score => undef,
          al0p_5_z_score_server => undef,
          al0p_5_z_score_all => undef,
          al0p_5_z_score_server_all => undef,
	},
	# mammoth
	z_score_m => {
	  z_score_m => undef,
	  ln_e_m => undef,
	  mammoth_p_align => undef
	},
	# dali
	dali_z_score => {
	  dali_z_score => undef,
	  dali_align_residues => undef,
	  dali_rmsd => undef,
	  dali_raw => undef,
	  dali_n_align => undef
	},
	# molprobity
	mp_score => {
	  mp_score => undef,
	  mp_rot_out => undef,
	  mp_clash => undef,
	  mp_ram_out => undef,
	  mp_ram_fv => undef
	},
	lddt => {
	  lddt => undef,
	},
	cad_aa => {
	  cad_aa => undef,
	  cad_ss => undef
	},
	rpf => {
	   rpf => undef
	},
	sg_lvr_6_2 => {
	  sg_lvr_6_2 => undef
	},
	codm => {
	   codm => undef,
	   codm_ratio => undef
	},
	dfm => {
	   dfm => undef
	},
	handedness => {
	   handedness => undef,
	   handedness_ratio => undef
	},
	sov => {
	   sov => undef
	},
	ce => {
	   ce => undef,
	   ce_p_align => undef
	},
	qcs => {
	   qcs => undef,  
	   conts => undef,
	   contsintra => undef,
	},
	tm_score => {
	   tm_score => undef,
	   tm_n_align => undef
	},
	flexe => {
	   flexe => undef
	},

	qse =>{
	    qse => undef
	}
   };



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

# set all values of all scores to undef
sub cleanSCORES{
    my ($self) = @_;
    foreach my $score ( sort keys %$SCORES){
        foreach my $sc ( sort keys %{$SCORES->{$score}}){
            $SCORES->{$score}->{$sc} = undef;
        }
    }
}

# e.g.: dombase = 70
# the models are slected from domains 71, 72 ... 79
sub getBestScorePerBatch{
    my($self, $target, $dombase, $score) = @_;
    my $sub_query = " WHERE pr.target='$target' AND re.domain>=($dombase+1) AND re.domain<=($dombase+9) ";
    my $minmax = "MAX";
    if ($score eq 'mp_score' ||  $score eq 'flexe' || $score eq 'dfm'){
	$minmax = "MIN";
	$sub_query .= " AND re.$score>=0 ";
    }
    my $query = sprintf "SELECT predictions_id, %s(%s) FROM casp13.results re 
	JOIN casp13.predictions pr ON re.predictions_id=pr.id 
	%s GROUP BY re.predictions_id ", $minmax, $score, $sub_query ;
    my $sth = $self->{_database}->query($query);
    # print $query."\n";
    my @result;
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($predictions_id, $score_value) = $sth->fetchrow_array()){
	  my $record = {
		predictions_id => $predictions_id,
		domain => $dombase,
		$score => $score_value,
	  };
	  push @result, $record;
	  
	}
    }
    return @result;
}

# get record which correspnds to the best score
sub getRecordBestScore{
    my ($self, $record, $score) = @_;
    my $sub_query = "";
    foreach my $sc (sort keys %{$SCORES->{$score}}){
	$sub_query .= "$sc,";
    }
    $sub_query =~ s/,$//;
    my $order = 'DESC';
    my $positive_restrain = '';
    if ($score eq 'mp_score' ||  $score eq 'flexe' || $score eq 'dfm'){
	$order = 'ASC';
	$positive_restrain = " AND re.$score>=0 ";
    }
    my $query = sprintf("SELECT %s FROM casp13.results re WHERE re.predictions_id=%d AND re.domain>=(%s+1) AND re.domain<=(%s+9) %s ORDER BY re.$score %s LIMIT 1", $sub_query, $record->{predictions_id}, $record->{domain}, $record->{domain}, $positive_restrain, $order);
#    print $query."\n";
    my $sth = $self->{_database}->query($query);
    if (defined($sth) && ($sth->rows() > 0)){
	my $hashref = $sth->fetchrow_hashref();
	foreach my $field (sort keys %{$hashref}){
		$record->{lc($field)} = $hashref->{$field};
	}
    }
    return $record;
}

sub upload {
    my ($self, $record) = @_;
    my $id = $self->getid($record);
    if ($id > 0){
	$self->update($record);
    } else {
	$self->add($record);
    }
}

# 
sub getid {
    my ($self, $record) = @_;
    my $query = sprintf "SELECT id FROM casp13.results WHERE predictions_id=%d AND domain=%d", $record->{predictions_id}, $record->{domain};
    # print $query."\n";
    my $sth = $self->{_database}->query($query);
    my $result = 0;
    if (defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
	if ($result > 0){
		$record->{id} = $result;
	}
    }
    return $result;
}

sub update {
    my ($self, $record) = @_;
    my $values = "";
    foreach my $key (sort keys %{$record}){
	if ($key ne 'id'){
		$values .= sprintf "$key='%s',", $record->{$key};
	}
    }
    $values =~ s/,$//;
    if ($values eq ""){
	return;
    }
    my $query = sprintf "UPDATE casp13.results SET %s WHERE id=%d", $values, $record->{id};
    my $sth = $self->{_database}->query($query);
    print $query."\n";
}

sub add {
    my ($self, $record) = @_;
    my $fields = "";
    my $values = "";
    foreach my $key (sort keys %{$record}){
        if ($key ne 'id'){
                $fields .= "$key,";
                $values .= sprintf "'%s',", $record->{$key};
        }
    }
    $fields =~ s/,$//;
    $values =~ s/,$//;
    if ($values eq ""){
	return; 
    }
    my $query = sprintf "INSERT INTO casp13.results (%s) VALUES (%s) ", $fields, $values;
    my $sth = $self->{_database}->query($query);
    print $query."\n";
}


$manager = new ResultsUploadOligomericManager();

foreach my $score (sort keys %{$SCORES}){
	my @records = $manager->getBestScorePerBatch("T0797", 90, $score);
	foreach my $record (@records){
	  $record = $manager->getRecordBestScore($record, $score);
	  #foreach my $key (sort keys %{$record}){
	  #   printf "$key\t%s\n", $record->{$key};
	  #}
	  $manager->upload($record);
	}
}


# upload to casp13.targets_results table
#system("perl /local/Projects/Perl/casp13/src/scripts/evaluation_scripts/upload_results.pl --targets T0787-D80 --actions='targets,images'");