package RRCResultsManager;
use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use lib qw(Core);
use Database;
use Configuration;
use LocalConfiguration;
use BO;
our @ISA = qw(BO);

my $casp_schema = "\L$LOCAL_CONFIG->{CASP_VERSION}";
my $DEBUG = 0;

sub new {
    my ($class) = @_;
    my $self = $class->SUPER::new("$casp_schema.rrc_results");
    bless $self, $class;
    return $self;
}	

sub is_table_column{
my ($self, $column_name) = @_;
my $result = 0;
if(	$column_name eq 'id' ||
	$column_name eq 'predictions_id' ||
        $column_name eq 'domains_id' ||
	$column_name eq 'domain' ||
        $column_name eq 'rr_range' ||
	$column_name eq 'list_size' ||
	$column_name eq 'prob_filter' ||
        $column_name eq 'auc_pr' ||
	$column_name eq 'f1' ||
	$column_name eq 'mcc' ||
	$column_name eq 'prec' ||
	$column_name eq 'recall' ||
        $column_name eq 'rrces' ||
        $column_name eq 'mcc_ss' ||
	$column_name eq 'prec_ss' ||
        $column_name eq 'recall_ss' ||
	$column_name eq 'f1_ss' ||
	$column_name eq 'mcc_ss_tp' ||
        $column_name eq 'prec_ss_tp' ||
        $column_name eq 'recall_ss_tp' ||
	$column_name eq 'no_conts_m' ||
        $column_name eq 'no_conts_t' ||
        $column_name eq 'prec_prob' ||
	$column_name eq 'recall_prob' ||
	$column_name eq 'f1_prob' ||
        $column_name eq 'rrces_ext' ||
	$column_name eq 'pr_x' ||
        $column_name eq 'pr_y' ||
	$column_name eq 'auc_pr_java' ||
	$column_name eq 'prec_FDR_pw' ||
	$column_name eq 'prec_pwa' ||
	$column_name eq 'prec_FDR_pwa'
	
){
		$result = 1;
	}
return $result;
}

sub get_new_model{
    my $self = @_;
    my %model = (
        id => '',
        predictions_id => '',
	domains_id => undef,
	domain => '',
        rr_range => '',
	list_size => '',
	prob_filter => '',
        auc_pr => '',
        mcc => '',
	f1 => '',
	prec => '',
	recall => '',
	rrces => '',
        mcc_ss => '',
        prec_ss => '',
        recall_ss => '',
        mcc_ss_tp => '',
	f1_ss => '',
        prec_ss_tp => '',
        recall_ss_tp => '',
        no_conts_m => '',
        no_conts_t => '',
        seq_len_t => '',
        prec_prob => '',
        recall_prob => '',
        f1_prob => '',
        rrces_ext => '',
        pr_x => '',
        pr_y => '',
	auc_pr_java => '',
	prec_FDR_pw => '',
	prec_pwa => '',
	prec_FDR_pwa => '',

        GROUP_CODE => undef,
	MODEL => undef,
	PFRMAT => undef,
        TARGET => undef,
    );
return %model;
}

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

    my $query = sprintf("SELECT COUNT(id) FROM $casp_schema.rrc_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 $query= sprintf(" SELECT re.id FROM $casp_schema.rrc_results re join $casp_schema.predictions p on re.predictions_id=p.id join $casp_schema.groups g on p.groups_id=g.id WHERE (p.target=\'%s\' AND re.domain=%d AND re.rr_range=\'%s\' AND p.model=%d AND g.code=%d AND re.list_size=\'%s\' AND re.prob_filter=\'%s\')",$model{TARGET}, $model{domain}, $model{rr_range}, $model{MODEL}, $model{GROUP_CODE}, $model{list_size}, $model{prob_filter});
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) && ($sth->rows() == 1) ) {
	my ($id) = $sth->fetchrow_array();
	return $id;
    } else {
	return undef;
    }
}

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

    $model{predictions_id} = $self->getPredictionsID(%model);
    if (!defined($model{predictions_id})){
        # do nothing 
        return 0;
    }

    $model{domains_id} = $self->getDomainsID(%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 '') {
                #print "NE---$key---------->$value<----------\n";
        }else{
                if($self->is_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 $casp_schema.rrc_results ( %s ) VALUES ( %s ) RETURNING id ", $column_names, $column_values);
    if ($DEBUG){
	print $query."\n"; return undef;
    }
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        # add logger
        ($result) = $sth->fetchrow_array();
    }
       # print "---------------------ADD: $result";
        if ($result == 0) {
		#print "\n" . $query . "\n";
        }

    return $result;
}

sub update{
    my ($self, %model) = @_;
    $model{predictions_id} = $self->getPredictionsID(%model);
    if (!defined($model{predictions_id})){
	# do nothing 
	return 0;
    }

    $model{domains_id} = $self->getDomainsID(%model);

    my $result = 0;
    my $set_query = '';
    while ( my ($key, $value) = each(%model) ) {
        if($key eq 'id' || !defined($value) || $value eq '') {
                #print "NE---$key---------->$value<----------\n";
        }else{
                #list of columns which not updated
                if($self->is_table_column($key) == 1) {
                        $set_query .= sprintf(" %s = \'%s\', ", $key, $value);
                }
        }
    }
    return $result if(! $self->exist( $model{id}));
    my $query = sprintf("UPDATE $casp_schema.rrc_results SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
        #print "------------------------UPDATE: $model{id} \n";
    if ($DEBUG){
	print $query . "\n";
	return undef;
    }
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = 1;
    }
    return $result;
}


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

sub getPredictionsID {
    my ($self, %model) = @_;
    my $query = sprintf("SELECT p.id FROM casp13.predictions p JOIN casp13.groups g ON g.id=p.groups_id
		WHERE g.code=%d AND p.target=\'%s\' AND PFRMAT=\'%s\' AND p.model=%d", $model{GROUP_CODE}, $model{TARGET}, $model{PFRMAT}, $model{MODEL});
    if ($DEBUG){
	print $query."\n"; return undef;
    }
    my $sth = $self->{_database}->query($query);
    if (defined($sth) and $sth->rows() == 1) {
	my ($id) = $sth->fetchrow_array();
	return $id;
    } else {
	#print $query."\n";
	return undef;
    }
}

sub getDomainsID {
    my ($self, %model) = @_;
    my $query = sprintf("SELECT d.id FROM casp13.domains d JOIN casp13.targets t ON t.id=d.targets_id 
                WHERE t.name=\'%s\' AND d.index=%d ", $model{TARGET}, $model{domain});
    if ($DEBUG){
        print $query."\n"; return undef;
    }
    my $sth = $self->{_database}->query($query);
    if (defined($sth) and $sth->rows() == 1) {
        my ($id) = $sth->fetchrow_array();
        return $id;
    } else {
        #print $query."\n";
        return undef;
    }
}

sub get_results{
    my ($self, $params) = @_;
    my $subquery = " WHERE 1=1 ";
    my $rr_range;
    if (defined($params->{_range})) {
	$rr_range = $params->{_range};
    } else{
	$rr_range = "L";
    }
    $subquery .= sprintf " AND re.rr_range=\'%s\'", $rr_range;
    my $list_size;
    if (defined($params->{_list_size})) {
        $list_size = $params->{_list_size};
    } else{
        $list_size = "FL";
    }
    $subquery .= sprintf " AND re.list_size=\'%s\'", $list_size;
    my $prob_filter;
    if (defined($params->{_prob_filter})) {
        $prob_filter = $params->{_prob_filter};
    } else{
        $prob_filter = "0";
    }
    $subquery .= sprintf " AND re.prob_filter=\'%s\'", $prob_filter;
    if (defined($params->{_full_target}) && $params->{_full_target} ne ''){
	my $target; my $dom = 0;
 	if ($params->{_full_target} =~ m/(\S+)-D(\d+)/){
	   $target = $1; $dom = $2;
	} else {
	   $target = $params->{_full_target};
	}
	$subquery .= sprintf " AND p.target=\'%s\' AND re.domain=%d ", $target, $dom;
    }
    if ( defined($params->{_gr_code}) && $params->{_gr_code} ne '') {
	 $subquery .= sprintf " AND g.code=%d ", $params->{_gr_code};
    }
    # TODO: hard code skip 4 groups : (223, 112, 342, 48)
    #$subquery .= " AND g.code not in (223, 112, 342, 48)";
    $subquery .= "";
    my $query = "
	SELECT  re.rr_range, re.domain, 
		p.target, p.pfrmat, g.code, p.model,
		re.auc_pr, re.mcc,  
		re.prec, re.recall, re.f1, re.rrces, 
		re.mcc_ss, re.prec_ss, re.recall_ss,
		re.prec_ss_tp, re.recall_ss_tp, no_conts_m, re.f1_ss,
		re.rrces_ext, re.prec_prob, re.recall_prob, re.f1_prob, 
		re.auc_pr_java, re.prec_FDR_pw,
		re.prec_pwa, re.prec_FDR_pwa
	FROM casp13.rrc_results re 
		JOIN casp13.predictions p ON re.predictions_id=p.id
		JOIN casp13.groups g ON g.id=p.groups_id
	$subquery 
		ORDER BY re.prec DESC NULLS LAST";
    
    my $sth = $self->{_database}->query($query);
    my $index = 1;
    my @result = ();
    if (defined($sth) and $sth->rows()>0){
	while(my ($range, $domain, 
                $target, $pfrmat, $code, $model,
                $auc_pr, $mcc, 
                $prec, $recall, $f1, $rrces, 
		$mcc_ss, $prec_ss, $recall_ss,
		$prec_ss_tp, $recall_ss_tp, $no_conts_m, $f1_ss,
		$rrces_ext, $prec_prob, $recall_prob, $f1_prob,
		$auc_pr_java, $prec_FDR_pw,
		$prec_pwa, $prec_FDR_pwa) = $sth->fetchrow_array()){
		push @result, {
		  INDEX => $index++,
		  IMAGE_PATH => sprintf("PLOTS/RRC_CONTS/%s%s_figs/%s%s_%s%s%03d_%d_%s_%s_TPFPmap%s.png", $target, ($domain == 0 ? '' : '-D'.$domain), $target, ($domain == 0 ? '' : '-D'.$domain), $target, $pfrmat, $code, $model, $params->{_range}, ($params->{_list_size} =~ m/top10/i ? '10' : $params->{_list_size}), ($params->{_prob_filter} eq '0.5' ? "05" : "")),
		  IMAGE_PATH2 => sprintf("PLOTS/RRC_CONTS/%s%s_figs/%s%s_%s%s%03d_%d_%s_probTP.png", $target, ($domain == 0 ? '' : '-D'.$domain), $target, ($domain == 0 ? '' : '-D'.$domain), $target, $pfrmat, $code, $model, $params->{_range} ),
		  MODEL_NAME => sprintf("%s%s%03d_%d%s", $target, $pfrmat, $code, $model, ($domain == 0 ? '' : '-D'.$domain)),
		  AUC_PR => (defined($auc_pr) && $auc_pr ne ''? sprintf("%.3f", $auc_pr): "-"),
		  MCC => (defined($mcc) && $mcc ne ''? sprintf("%.3f", $mcc): "-"),
		  F1 => (defined($f1) && $f1 ne ''? sprintf("%.3f", $f1): "-"),
		  PREC => (defined($prec) && $prec ne ''? sprintf("%.3f", $prec): "-"),
		  RECALL => (defined($recall) && $recall ne ''? sprintf("%.3f", $recall): "-"),
		  RRCES => (defined($rrces) && $rrces ne ''? sprintf("%.3f", $rrces): "-"),
		  MCC_SS => (defined($mcc_ss) && $mcc_ss ne ''? sprintf("%.3f", $mcc_ss): "-"),
		  PREC_SS => (defined($prec_ss) && $prec_ss ne ''? sprintf("%.3f", $prec_ss): "-"),
                  RECALL_SS => (defined($recall_ss) && $recall_ss ne ''? sprintf("%.3f", $recall_ss): "-"),
		  PREC_SS_TP => (defined($prec_ss_tp) && $prec_ss_tp ne ''? sprintf("%.3f", $prec_ss_tp): "-"),
		  RECALL_SS_TP => (defined($recall_ss_tp) && $recall_ss_tp ne ''? sprintf("%.3f", $recall_ss_tp): "-"),
		  NO_CONTS_M => (defined($no_conts_m) && $no_conts_m ne ''? sprintf("%d", $no_conts_m): "-"),
		  F1_SS => (defined($f1_ss) && $f1_ss ne ''? sprintf("%.3f", $f1_ss): "-"),
		  RRCES_EXT => (defined($rrces_ext) && $rrces_ext ne ''? sprintf("%.3f", $rrces_ext): "-"),
		  F1_PROB => (defined($f1_prob) && $f1_prob ne ''? sprintf("%.3f", $f1_prob): "-"),
                  PREC_PROB => (defined($prec_prob) && $prec_prob ne ''? sprintf("%.3f", $prec_prob): "-"),
                  RECALL_PROB => (defined($recall_prob) && $recall_prob ne ''? sprintf("%.3f", $recall_prob): "-"),
		  AUC_PR_JAVA => (defined($auc_pr_java) && $auc_pr_java ne ''? sprintf("%.3f", $auc_pr_java): "-"),
		  PREC_FDR_PW => (defined($prec_FDR_pw) && $prec_FDR_pw ne ''? sprintf("%.3f", $prec_FDR_pw): "-"),
		  PREC_PWA => (defined($prec_pwa) && $prec_pwa ne ''? sprintf("%.3f", $prec_pwa): "-"),
		  PREC_FDR_PWA => (defined($prec_FDR_pwa) && $prec_FDR_pwa ne ''? sprintf("%.3f", $prec_FDR_pwa): "-"),
		};
	}
    }
    return @result;
}

sub get_avrg_results{
    my ($self, $params) = @_;
    my $subquery = " WHERE 1=1 ";
    my $rr_range;
    if (defined($params->{_range})) {
        $rr_range = $params->{_range};
    } else{
        $rr_range = "L";
    }
    $subquery .= sprintf " AND re.rr_range=\'%s\'", $rr_range;
    my $list_size;
    if (defined($params->{_list_size})) {
        $list_size = $params->{_list_size};
    } else{
        $list_size = "FL";
    }
    $subquery .= sprintf " AND re.list_size=\'%s\'", $list_size;
    my $prob_filter;
    if (defined($params->{_prob_filter})) {
        $prob_filter = $params->{_prob_filter};
    } else{
        $prob_filter = "0";
    }
    $subquery .= sprintf " AND re.prob_filter=\'%s\'", $prob_filter;
    my $domclass = "";
    if (defined($params->{_tbm}) && $params->{_tbm} eq 'on'){
	$domclass .= '2,';
    }
    if (defined($params->{_fm}) && $params->{_fm} eq 'on'){
        $domclass .= '3,';
    }
    if (defined($params->{_tbmfm}) && $params->{_tbmfm} eq 'on'){
        $domclass .= '4,';
    }
    $domclass =~ s/,$//;
    if ($domclass ne ''){
	$subquery .= " AND d.domain_classifications_id in ($domclass) ";
    }
    # query to get total no_domains
    my $query = "SELECT COUNT(DISTINCT d.id) FROM casp13.rrc_results re
			JOIN casp13.domains d ON re.domains_id=d.id 
			$subquery ";
    my $sth = $self->{_database}->query($query);
    my $no_domains;
    if (defined($sth) && ($sth->rows() > 0)){
        ($no_domains) = $sth->fetchrow_array();
    }
    # TODO : hard code skip 4 groups : (223, 112, 342, 48)
    # $subquery .= " AND g.code not in (223, 112, 342, 48) ";
    $subquery .= "";
    
    $query = "
        SELECT  g.code, g.name, 
                avg(re.auc_pr) as auc_pr, avg(re.mcc) as mcc,  
                avg(re.prec) as prec, avg(re.recall) as recall, avg(re.f1) as f1, 
		avg(re.rrces) as rrces, avg(re.rrces_ext) as rrces_ext,
                avg(re.mcc_ss) as mcc_ss, avg(re.prec_ss) as prec_ss, avg(re.recall_ss) as recall_ss,
                avg(re.prec_ss_tp) as prec_ss_tp, avg(re.recall_ss_tp) as recall_ss_tp, avg(re.f1_ss) as f1_ss,
		avg(re.prec_prob) as prec_prob, avg(re.recall_prob) as recall_prob, avg(re.f1_prob) as f1_prob, 
		avg(re.auc_pr_java) as auc_pr_java, avg(re.prec_FDR_pw) as prec_FDR_pw,
		avg(re.prec_pwa) as prec_pwa, avg(re.prec_FDR_pwa) as prec_FDR_pwa,
	        count(re.auc_pr) as no_auc_pr, count(re.mcc) as no_mcc,  
                count(re.prec) as no_prec, count(re.recall) as no_recall, count(f1) as no_f1, 
		count(re.rrces) as no_rrces, count(re.rrces_ext) as no_rrces_ext,
                count(re.mcc_ss) as no_mcc_ss, count(re.prec_ss) as no_prec_ss, count(re.recall_ss) as no_recall_ss,
                count(re.prec_ss_tp) as no_prec_ss_tp, count(re.recall_ss_tp) as no_recall_ss_tp, count(re.f1_ss) as no_f1_ss,
		count(re.prec_prob) as no_prec_prob, count(re.recall_prob) as no_recall_prob, count(re.f1_prob) as no_f1_prob, 
                count(re.auc_pr_java) as no_auc_pr_java
        FROM casp13.rrc_results re 
                JOIN casp13.predictions p ON re.predictions_id=p.id
                JOIN casp13.groups g ON g.id=p.groups_id
		JOIN casp13.domains d ON re.domains_id=d.id
        $subquery 
		GROUP BY g.code, g.name
                ORDER BY prec DESC NULLS LAST";

    $sth = $self->{_database}->query($query);
    my $index = 1;
    my @result = ();
    if (defined($sth) and $sth->rows()>0){
        while(my ($code, $gr_name,
                $auc_pr, $mcc,
                $prec, $recall, $f1,
		$rrces, $rrces_ext,
		$mcc_ss, $prec_ss, $recall_ss,
                $prec_ss_tp, $recall_ss_tp, $f1_ss,
		$prec_prob, $recall_prob, $f1_prob, 
                $auc_pr_java, $prec_FDR_pw,
		$prec_pwa, $prec_FDR_pwa,
		$no_auc_pr, $no_mcc,
                $no_prec, $no_recall, $no_f1, 
		$no_rrces, $no_rrces_ext,
		$no_mcc_ss, $no_prec_ss, $no_recall_ss,
                $no_prec_ss_tp, $no_recall_ss_tp, $no_f1_ss,
		$no_prec_prob, $no_recall_prob, $no_f1_prob, 
                $no_auc_pr_java
		) = $sth->fetchrow_array()){
                push @result, {
                  INDEX => $index++,
                  GR_CODE => sprintf("%03d", $code),
		  GR_NAME => $gr_name,
                  AUC_PR => (defined($auc_pr) && $auc_pr ne ''? sprintf("%.3f", $auc_pr): "-"),
                  MCC => (defined($mcc) && $mcc ne ''? sprintf("%.3f", $mcc): "-"),
		  F1 => (defined($f1) && $f1 ne ''? sprintf("%.3f", $f1): "-"),
                  PREC => (defined($prec) && $prec ne ''? sprintf("%.3f", $prec): "-"),
                  RECALL => (defined($recall) && $recall ne ''? sprintf("%.3f", $recall): "-"),
                  RRCES => (defined($rrces) && $rrces ne ''? sprintf("%.3f", $rrces): "-"),
		  RRCES_EXT => (defined($rrces_ext) && $rrces_ext ne ''? sprintf("%.3f", $rrces_ext): "-"),
                  MCC_SS => (defined($mcc_ss) && $mcc_ss ne ''? sprintf("%.3f", $mcc_ss): "-"),
                  PREC_SS => (defined($prec_ss) && $prec_ss ne ''? sprintf("%.3f", $prec_ss): "-"),
                  RECALL_SS => (defined($recall_ss) && $recall_ss ne ''? sprintf("%.3f", $recall_ss): "-"),
                  PREC_SS_TP => (defined($prec_ss_tp) && $prec_ss_tp ne ''? sprintf("%.3f", $prec_ss_tp): "-"),
                  RECALL_SS_TP => (defined($recall_ss_tp) && $recall_ss_tp ne ''? sprintf("%.3f", $recall_ss_tp): "-"),
		  F1_SS => (defined($f1_ss) && $f1_ss ne ''? sprintf("%.3f", $f1_ss): "-"),
		  PREC_PROB => (defined($prec_prob) && $prec_prob ne ''? sprintf("%.3f", $prec_prob): "-"),
                  RECALL_PROB => (defined($recall_prob) && $recall_prob ne ''? sprintf("%.3f", $recall_prob): "-"),
		  F1_PROB => (defined($f1_prob) && $f1_prob ne ''? sprintf("%.3f", $f1_prob): "-"),
		  AUC_PR_JAVA => (defined($auc_pr_java) && $auc_pr_java ne ''? sprintf("%.3f", $auc_pr_java): "-"),
		  PREC_FDR_PW => (defined($prec_FDR_pw) && $prec_FDR_pw ne ''? sprintf("%.3f", $prec_FDR_pw): "-"),
		  PREC_PWA => (defined($prec_pwa) && $prec_pwa ne ''? sprintf("%.3f", $prec_pwa): "-"),
		  PREC_FDR_PWA => (defined($prec_FDR_pwa) && $prec_FDR_pwa ne ''? sprintf("%.3f", $prec_FDR_pwa): "-"),

                  AUC_PR_ => (defined($auc_pr) && $auc_pr ne ''? sprintf("%.3f", $auc_pr*$no_prec/$no_domains): "-"),
                  MCC_ => (defined($mcc) && $mcc ne ''? sprintf("%.3f", $mcc*$no_prec/$no_domains): "-"),
                  F1_ => (defined($f1) && $f1 ne ''? sprintf("%.3f", $f1*$no_prec/$no_domains): "-"),
                  PREC_ => (defined($prec) && $prec ne ''? sprintf("%.3f", $prec*$no_prec/$no_domains): "-"),
                  RECALL_ => (defined($recall) && $recall ne ''? sprintf("%.3f", $recall*$no_prec/$no_domains): "-"),
                  RRCES_ => (defined($rrces) && $rrces ne ''? sprintf("%.3f", $rrces*$no_prec/$no_domains): "-"),
		  RRCES_EXT_ => (defined($rrces_ext) && $rrces_ext ne ''? sprintf("%.3f", $rrces_ext*$no_prec/$no_domains): "-"),
                  MCC_SS_ => (defined($mcc_ss) && $mcc_ss ne ''? sprintf("%.3f", $mcc_ss*$no_prec/$no_domains): "-"),
                  PREC_SS_ => (defined($prec_ss) && $prec_ss ne ''? sprintf("%.3f", $prec_ss*$no_prec/$no_domains): "-"),
                  RECALL_SS_ => (defined($recall_ss) && $recall_ss ne ''? sprintf("%.3f", $recall_ss*$no_prec/$no_domains): "-"),
                  PREC_SS_TP_ => (defined($prec_ss_tp) && $prec_ss_tp ne ''? sprintf("%.3f", $prec_ss_tp*$no_prec/$no_domains): "-"),
                  RECALL_SS_TP_ => (defined($recall_ss_tp) && $recall_ss_tp ne ''? sprintf("%.3f", $recall_ss_tp*$no_prec/$no_domains): "-"),
		  F1_SS_ => (defined($f1_ss) && $f1_ss ne ''? sprintf("%.3f", $f1_ss*$no_prec/$no_domains): "-"),
		  PREC_PROB_ => (defined($prec_prob) && $prec_prob ne ''? sprintf("%.3f", $prec_prob*$no_prec/$no_domains): "-"),
                  RECALL_PROB_ => (defined($recall_prob) && $recall_prob ne ''? sprintf("%.3f", $recall_prob*$no_prec/$no_domains): "-"),
                  F1_PROB_ => (defined($f1_prob) && $f1_prob ne ''? sprintf("%.3f", $f1_prob*$no_prec/$no_domains): "-"),
                  AUC_PR_JAVA_ => (defined($auc_pr_java) && $auc_pr_java ne ''? sprintf("%.3f", $auc_pr_java*$no_prec/$no_domains): "-"),
		  PREC_FDR_PW_ => (defined($prec_FDR_pw) && $prec_FDR_pw ne ''? sprintf("%.3f", $prec_FDR_pw*$no_prec/$no_domains): "-"),
		  PREC_PWA_ => (defined($prec_pwa) && $prec_pwa ne ''? sprintf("%.3f", $prec_pwa*$no_prec/$no_domains): "-"),
		  PREC_FDR_PWA_ => (defined($prec_FDR_pwa) && $prec_FDR_pwa ne ''? sprintf("%.3f", $prec_FDR_pwa*$no_prec/$no_domains): "-"),

		  NO_AUC_PR => (defined($no_auc_pr) && $no_auc_pr ne ''? sprintf("%d", $no_auc_pr): "-"),
                  NO_MCC => (defined($no_mcc) && $no_mcc ne ''? sprintf("%d", $no_mcc): "-"),
		  NO_F1 => (defined($no_f1) && $no_f1 ne ''? sprintf("%d", $no_f1): "-"),
                  NO_PREC => (defined($no_prec) && $no_prec ne ''? sprintf("%d", $no_prec): "-"),
                  NO_RECALL => (defined($no_recall) && $no_recall ne ''? sprintf("%d", $no_recall): "-"),
                  NO_RRCES => (defined($no_rrces) && $no_rrces ne ''? sprintf("%d", $no_rrces): "-"),
                  NO_MCC_SS => (defined($no_mcc_ss) && $no_mcc_ss ne ''? sprintf("%d", $no_mcc_ss): "-"),
                  NO_PREC_SS => (defined($no_prec_ss) && $no_prec_ss ne ''? sprintf("%d", $no_prec_ss): "-"),
                  NO_RECALL_SS => (defined($no_recall_ss) && $no_recall_ss ne ''? sprintf("%d", $no_recall_ss): "-"),
                  NO_PREC_SS_TP => (defined($no_prec_ss_tp) && $no_prec_ss_tp ne ''? sprintf("%d", $no_prec_ss_tp): "-"),
                  NO_RECALL_SS_TP => (defined($no_recall_ss_tp) && $no_recall_ss_tp ne ''? sprintf("%d", $no_recall_ss_tp): "-"),
		  NO_F1_SS => (defined($no_f1_ss) && $no_f1_ss ne ''? sprintf("%d", $no_f1_ss): "-"),
		  NO_DOMS_TOTAL => $no_domains
                };
        }
    }
    return @result;
}

sub get_avrg_per_target_results{
    my ($self, $params) = @_;
    my $subquery = " WHERE 1=1 ";
    my $rr_range;
    if (defined($params->{_range})) {
        $rr_range = $params->{_range};
    } else{
        $rr_range = "L";
    }
    $subquery .= sprintf " AND re.rr_range=\'%s\'", $rr_range;
    my $list_size;
    if (defined($params->{_list_size})) {
        $list_size = $params->{_list_size};
    } else{
        $list_size = "FL";
    }
    $subquery .= sprintf " AND re.list_size=\'%s\'", $list_size;
    my $prob_filter;
    if (defined($params->{_prob_filter})) {
        $prob_filter = $params->{_prob_filter};
    } else{
        $prob_filter = "0";
    }
    $subquery .= sprintf " AND re.prob_filter=\'%s\'", $prob_filter;
    my $domclass = "";
    if (defined($params->{_tbm}) && $params->{_tbm} eq 'on'){
        $domclass .= '2,';
    }
    if (defined($params->{_fm}) && $params->{_fm} eq 'on'){
        $domclass .= '3,';
    }
    if (defined($params->{_tbmfm}) && $params->{_tbmfm} eq 'on'){
        $domclass .= '4,';
    }
    $domclass =~ s/,$//;
    if ($domclass ne ''){
        $subquery .= " AND d.domain_classifications_id in ($domclass) ";
    }
    # TODO : hard code skip 4 groups : (223, 112, 342, 48)
    #$subquery .= " AND g.code not in (223, 112, 342, 48) ";

    my $query = "
        SELECT  p.target || CASE WHEN re.domain=0 THEN '' ELSE '-D' || re.domain END as target_name, 
		d.domain_classifications_id as dci,
                avg(re.auc_pr) as auc_pr, avg(re.mcc) as mcc,  
                avg(re.prec) as prec, avg(re.recall) as recall, avg(re.f1) as f1, 
		avg(re.rrces) as rrces, avg(re.rrces_ext) as rrces_ext,
                avg(re.mcc_ss) as mcc_ss, avg(re.prec_ss) as prec_ss, avg(re.recall_ss) as recall_ss,
                avg(re.prec_ss_tp) as prec_ss_tp, avg(re.recall_ss_tp) as recall_ss_tp, avg(re.f1_ss) as f1_ss,
		avg(re.prec_prob) as prec_prob, avg(re.recall_prob) as recall_prob, avg(re.f1_prob) as f1_prob,
		avg(re.auc_pr_java) as auc_pr_java, avg(re.prec_FDR_pw) as prec_FDR_pw,
		avg(re.prec_pwa) as prec_pwa, avg(re.prec_FDR_pwa) as prec_FDR_pwa,
                count(re.auc_pr) as no_auc_pr, count(re.mcc) as no_mcc,  
                count(re.prec) as no_prec, count(re.recall) as no_recall, count(f1) as no_f1, 
		count(re.rrces) as no_rrces, count(re.rrces_ext) as no_rrces_ext,
                count(re.mcc_ss) as no_mcc_ss, count(re.prec_ss) as no_prec_ss, count(re.recall_ss) as no_recall_ss,
                count(re.prec_ss_tp) as no_prec_ss_tp, count(re.recall_ss_tp) as no_recall_ss_tp, count(re.f1_ss) as no_f1_ss,
		count(re.prec_prob) as no_prec_prob, count(re.recall_prob) as no_recall_prob, count(re.f1_prob) as no_f1_prob
        FROM casp13.rrc_results re 
                JOIN casp13.predictions p ON re.predictions_id=p.id
                JOIN casp13.groups g ON g.id=p.groups_id
                JOIN casp13.domains d ON re.domains_id=d.id
        $subquery 
                GROUP BY target_name, dci
                ORDER BY f1 DESC NULLS LAST";

    my $sth = $self->{_database}->query($query);
    my $index = 1;
    my @result = ();
    if (defined($sth) and $sth->rows()>0){
        while(my ($target_name, $dci,
                $auc_pr, $mcc,
                $prec, $recall, $f1, 
		$rrces, $rrces_ext,
                $mcc_ss, $prec_ss, $recall_ss,
                $prec_ss_tp, $recall_ss_tp, $f1_ss,
		$prec_prob, $recall_prob, $f1_prob,
		$auc_pr_java, $prec_FDR_pw,
		$prec_pwa, $prec_FDR_pwa,
                $no_auc_pr, $no_mcc,
                $no_prec, $no_recall, $no_f1,
		$no_rrces, $no_rrces_ext,
                $no_mcc_ss, $no_prec_ss, $no_recall_ss,
                $no_prec_ss_tp, $no_recall_ss_tp, $no_f1_ss,
		$no_prec_prob, $no_recall_prob, $no_f1_prob
                ) = $sth->fetchrow_array()){
		if ($dci == 2){
			$dci = 'TBM';
		} elsif ($dci == 3) {
			$dci = 'FM';
		} elsif ($dci == 4){
			$dci = 'FM/TBM';
		}
                push @result, {
                  INDEX => $index++,
                  TARGET_NAME => sprintf("%s", $target_name),
		  DCI => $dci,
                  AUC_PR => (defined($auc_pr) && $auc_pr ne ''? sprintf("%.3f", $auc_pr): "-"),
                  MCC => (defined($mcc) && $mcc ne ''? sprintf("%.3f", $mcc): "-"),
                  F1 => (defined($f1) && $f1 ne ''? sprintf("%.3f", $f1): "-"),
                  PREC => (defined($prec) && $prec ne ''? sprintf("%.3f", $prec): "-"),
                  RECALL => (defined($recall) && $recall ne ''? sprintf("%.3f", $recall): "-"),
                  RRCES => (defined($rrces) && $rrces ne ''? sprintf("%.3f", $rrces): "-"),
		  RRCES_EXT => (defined($rrces_ext) && $rrces_ext ne ''? sprintf("%.3f", $rrces_ext): "-"),
                  MCC_SS => (defined($mcc_ss) && $mcc_ss ne ''? sprintf("%.3f", $mcc_ss): "-"),
                  PREC_SS => (defined($prec_ss) && $prec_ss ne ''? sprintf("%.3f", $prec_ss): "-"),
                  RECALL_SS => (defined($recall_ss) && $recall_ss ne ''? sprintf("%.3f", $recall_ss): "-"),
                  PREC_SS_TP => (defined($prec_ss_tp) && $prec_ss_tp ne ''? sprintf("%.3f", $prec_ss_tp): "-"),
                  RECALL_SS_TP => (defined($recall_ss_tp) && $recall_ss_tp ne ''? sprintf("%.3f", $recall_ss_tp): "-"),
                  F1_SS => (defined($f1_ss) && $f1_ss ne ''? sprintf("%.3f", $f1_ss): "-"),
		  PREC_PROB => (defined($prec_prob) && $prec_prob ne ''? sprintf("%.3f", $prec_prob): "-"),
                  RECALL_PROB => (defined($recall_prob) && $recall_prob ne ''? sprintf("%.3f", $recall_prob): "-"),
		  F1_PROB => (defined($f1_prob) && $f1_prob ne ''? sprintf("%.3f", $f1_prob): "-"),
		  AUC_PR_JAVA => (defined($auc_pr_java) && $auc_pr_java ne ''? sprintf("%.3f", $auc_pr_java): "-"),
		  PREC_FDR_PW => (defined($prec_FDR_pw) && $prec_FDR_pw ne '' ? sprintf("%.3f", $prec_FDR_pw): "-"),
		  PREC_PWA => (defined($prec_pwa) && $prec_pwa ne '' ? sprintf("%.3f", $prec_pwa): "-"),
		  PREC_FDR_PWA => (defined($prec_FDR_pwa) && $prec_FDR_pwa ne '' ? sprintf("%.3f", $prec_FDR_pwa): "-"),

                  NO_AUC_PR => (defined($no_auc_pr) && $no_auc_pr ne ''? sprintf("%d", $no_auc_pr): "-"),
                  NO_MCC => (defined($no_mcc) && $no_mcc ne ''? sprintf("%d", $no_mcc): "-"),
                  NO_F1 => (defined($no_f1) && $no_f1 ne ''? sprintf("%d", $no_f1): "-"),
                  NO_PREC => (defined($no_prec) && $no_prec ne ''? sprintf("%d", $no_prec): "-"),
                  NO_RECALL => (defined($no_recall) && $no_recall ne ''? sprintf("%d", $no_recall): "-"),
                  NO_RRCES => (defined($no_rrces) && $no_rrces ne ''? sprintf("%d", $no_rrces): "-"),
                  NO_MCC_SS => (defined($no_mcc_ss) && $no_mcc_ss ne ''? sprintf("%d", $no_mcc_ss): "-"),
                  NO_PREC_SS => (defined($no_prec_ss) && $no_prec_ss ne ''? sprintf("%d", $no_prec_ss): "-"),
                  NO_RECALL_SS => (defined($no_recall_ss) && $no_recall_ss ne ''? sprintf("%d", $no_recall_ss): "-"),
                  NO_PREC_SS_TP => (defined($no_prec_ss_tp) && $no_prec_ss_tp ne ''? sprintf("%d", $no_prec_ss_tp): "-"),
                  NO_RECALL_SS_TP => (defined($no_recall_ss_tp) && $no_recall_ss_tp ne ''? sprintf("%d", $no_recall_ss_tp): "-"),
                  NO_F1_SS => (defined($no_f1_ss) && $no_f1_ss ne ''? sprintf("%d", $no_f1_ss): "-"),
                };
        }
    }
    return @result;

}

sub get_PR_coords{
    my ($self, $param) = @_;
    # TODO : hard code skip 4 groups : (223, 112, 342, 48)
    #my $subquery = " AND gr.code not in (223, 112, 342, 48) ";
    my $subquery = '';
    my $query = sprintf "
	SELECT gr.code, pr.pfrmat, pr.model, re.auc_pr_java, re.pr_x, re.pr_y 
	FROM casp13.rrc_results re
	JOIN casp13.predictions pr ON pr.id=re.predictions_id 
	JOIN casp13.groups gr ON pr.groups_id = gr.id
	WHERE pr.target=\'%s\' AND re.domain=%d AND rr_range=\'%s\' 
		AND list_size='FL' AND prob_filter='0' 
		$subquery
	ORDER BY re.auc_pr_java DESC", 
	$param->{_target}, $param->{_domain}, $param->{_rr_range};
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if (defined($sth) and $sth->rows() > 0){
	my $index = 1;
	while(my ($gr_code, $pfrmat, $model, $auc_pr_java, $pr_x, $pr_y) = $sth->fetchrow_array()){
		push @results, {
			INDEX => $index++,
			GR_CODE => sprintf ("%03d ", $gr_code),
			AUC_PR_JAVA => sprintf("%.3f", $auc_pr_java),
			PR_X => $pr_x,
			PR_Y => $pr_y
		};
	}
    }
    return @results;
}

sub get_targets{
    my ($self) = @_;
    my $query = "SELECT DISTINCT CASE WHEN re.domain<>0 THEN p.target || '-D' || re.domain ELSE p.target END AS target_name 
		 FROM casp13.rrc_results re JOIN casp13.predictions p ON re.predictions_id=p.id 
		 JOIN casp13.targets t ON t.name::text=p.target::text
		 JOIN casp13.domains d ON d.targets_id=t.id AND re.domain=d.index
		 WHERE d.domain_classifications_id in (3,4)
		 ORDER BY target_name";
    my @result = ();
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) and $sth->rows() > 0 ){
	while((my $target_name) = $sth->fetchrow_array()){
		push @result, {
			NAME => $target_name,
		};
	}
    }
    return @result;
}

sub get_rrranges{
    my ($self) = @_;
    my $query = "SELECT DISTINCT rr_range FROM casp13.rrc_results  ORDER BY rr_range";
    my @result = ();
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) and $sth->rows() > 0 ){
        while((my $rr_range) = $sth->fetchrow_array()){
                push @result, {
                        NAME => $rr_range,
                };
        }
    }
    return @result;
}

sub get_listsizes{
    my ($self) = @_;
    my $query = "SELECT DISTINCT list_size FROM casp13.rrc_results  ORDER BY list_size";
    my @result = ();
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) and $sth->rows() > 0 ){
        while((my $list_size) = $sth->fetchrow_array()){
                push @result, {
                        NAME => $list_size,
                };
        }
    }
    return @result;
}

sub get_prob_filters{
    my ($self) = @_;
    my $query = "SELECT DISTINCT prob_filter FROM casp13.rrc_results  ORDER BY prob_filter";
    my @result = ();
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) and $sth->rows() > 0 ){
        while((my $prob) = $sth->fetchrow_array()){
                push @result, {
                        NAME => $prob,
                };
        }
    }
    return @result;
}

sub get_groups{
    my ($self) = @_;
    # TODO : hard code skip 4 groups : (223, 112, 342, 48)
    #my $subquery = " AND g.code not in (223, 112, 342, 48) ";
    my $subquery = "";
    my $query = "SELECT DISTINCT g.id, g.name, g.code 
                  FROM casp13.rrc_results re JOIN casp13.predictions p 
		  ON p.id=re.predictions_id JOIN casp13.groups g ON p.groups_id=g.id 
			WHERE 1=1 $subquery
		  ORDER BY g.code";
    my @result = ();
    my $sth = $self->{_database}->query($query);
    if ( defined($sth) and $sth->rows() > 0 ){
        while( my($gr_id, $gr_name, $gr_code) = $sth->fetchrow_array()){
                push @result, {
                        GR_ID => $gr_id,
			GR_CODE => sprintf("%03d", $gr_code),
			GR_NAME => $gr_name
                };
        }
    }
    return @result;

}


1;