package MultimerCapriResultsManager;

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_name => undef,
        gr_code => undef,
	pfrmat => undef,
        model => undef,
########################### results
	mm_size => '',
	stoich => '',
	symm => '',
	symm_size => '',
	symm_rmsd => '',
	no_conts => '',
	no_clashes => '',
	f1 => '',
	qs_score => '',
	lddt => '',
	jaccard_d => '',
	align_size => '',
	align_length => '',
	orient => '',	
	global_rmsd => '',
	local_rmsd => '',
	iface_rmsd => '',
	rank_score => '',
	prec_iface => '',
	recall_iface => ''
    );
    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_name' ||
	$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_score' ||
	$colunm_name eq 'lddt' ||
	$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' 
  ) {
	$result = 1;
	}
return $result;
}

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

    my $query = sprintf("SELECT COUNT(id) FROM casp13.multimer_capri_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_capri_results re 
	WHERE re.target=\'%s\' AND re.gr_code=\'%s\' 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;
    if (defined($model{model}) && $model{model} > 5){
        return $result; # do nothing
    }
    my $set_query = '';
    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || !defined($value) || $value eq '') {
		# 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_capri_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;

    if (defined($model{model}) && $model{model} > 5){
	return $result; # do nothing
    }

    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_capri_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_capri_results WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub update_group_name{
    my ($self, $gr_code, $gr_name, $target) = @_;
    if (!defined($target) || !defined($gr_code) || !defined($gr_name)){return 0;}
    my $query = sprintf("UPDATE casp13.multimer_capri_results SET gr_name = \'%s\' WHERE gr_code = \'%s\' AND target = \'%s\'", $gr_name, $gr_code, $target);
    my $sth = $self->{_database}->query($query);
    my $result;
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;

}

sub get_targets{
    my ($self, $param) = @_;
    my @result = ();
    my $query = "SELECT DISTINCT target FROM casp13.multimer_capri_results ORDER BY target";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)){
	my $index = 1;
	while(my($target) = $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_code, model, 
		mm_size, stoich, symm, symm_size, symm_rmsd, no_conts, no_clashes, f1, qs_score, lddt, jaccard_d, 
		align_size, align_length, orient, global_rmsd, local_rmsd, iface_rmsd, prec_iface, recall_iface, qs_score2
		FROM casp13.multimer_capri_results mre 
		$subquery  ORDER BY qs_score DESC NULLS LAST";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)){
	my $index = 1;
        while(my($target, $gr_name, $gr_code, $model, 
		 $mm_size, $stoich, $symm,  $symm_size, $symm_rmsd, $no_conts, $no_clashes, $f1, $qs_score, $lddt,  $jaccard_d,
		 $align_size, $align_length, $orient, $global_rmsd, $local_rmsd, $iface_rmsd, $prec_iface, $recall_iface,
		 $qs_score2
			) = $sth->fetchrow_array()) {
                push @result, {
			INDEX => $index,
                        TARGET => $target,
			GROUP_NAME => (defined($gr_name)? $gr_name : '-'),,
			MODEL_NAME => sprintf ("%s_%d", $gr_code, $model),
			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_SCORE => (defined($qs_score) ? sprintf("%.3f", $qs_score): '-'),
			LDDT => (defined($lddt) ? sprintf("%.3f", $lddt): '-'),
			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): '-'),
			QS_SCORE_PREV => (defined($qs_score2) ? sprintf("%.3f", $qs_score2): '-'), # previous version of qs_score
                };
		$index++;
        }
    }
    return @result;
}


1;