package QAAnalysisManager;
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 ResultsManager;
#use TargetsManager
my $r_m = new ResultsManager();
use CGI qw(:all);
use BO;
our @ISA = qw(BO);
sub new {
    my ($class) = @_;
    my $self = $class->SUPER::new("casp13.qa_analysis");
    
    bless $self, $class;	
    return $self;    
}

sub get_new_model {
    my ($self) = @_;    
    my %model = (
########################### system        
	id => '',
	predictions_id => '',        
########################## file name
        TARGET => '',	
	MODEL=> '',
	PFRMAT=> '',	
####################### 
################# SUMMARY
	model_count => '',
	pearson => '',
	fisher_zprime => '',
	spearman => '',
	t_probability => '',
	t_value => '',
	f_probability => '',
	f_value => '',
	df => '',
	mean => '',
	deviation =>'',
	pearson_z_score => '',
	fisher_zprime_z_score => '',	
	qa2_pearson => '',
	qa2_pearson_z_score => '',
	qa2_fisher_zprime => '',
	qa2_fisher_zprime_z_score => '',	
	INDEX => 0
    );
    return %model;
}

sub is_table_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	$colunm_name eq 'predictions_id' ||	
	########################### results	
	################# SUMMARY
	$colunm_name eq 'model_count' ||
	$colunm_name eq 'pearson' ||
	$colunm_name eq 'fisher_zprime' ||
	$colunm_name eq 'spearman' ||
	$colunm_name eq 't_probability' ||
	$colunm_name eq 't_value' ||
	$colunm_name eq 'f_probability' ||
	$colunm_name eq 'f_value' ||
	$colunm_name eq 'df' ||
	$colunm_name eq 'mean' ||	
	$colunm_name eq 'deviation' ||
	$colunm_name eq 'pearson_z_score' ||
	$colunm_name eq 'fisher_zprime_z_score' ||
	$colunm_name eq 'qa2_pearson' ||
	$colunm_name eq 'qa2_pearson_z_score' ||
	$colunm_name eq 'qa2_fisher_zprime' ||	
	$colunm_name eq 'qa2_fisher_zprime_z_score'
	
	
  ) {
	$result = 1;
	}
    return $result;
}

sub exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT qa.id FROM %s qa 
	JOIN casp13.predictions pr ON (pr.id = qa.predictions_id)
	JOIN casp13.groups gr ON (gr.id = pr.groups_id)	
	WHERE pr.target=\'%s\' and pr.model=\'%s\' and pr.pfrmat=\'%s\' and gr.code=\'%s\' and pr.id=%s ",
	$self->{_table}, $model{TARGET}, $model{MODEL}, $model{PFRMAT}, $model{CODE}, $model{predictions_id});
    my $sth = $self->{_database}->query($query);
    # print "EXIST SQL: " . $query ."\n";    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
	$result = $id;
    }    
    return $result;
}


sub get_rows {
    my ($self, $params, $not_rount) = @_;
    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 '')) ? 'qa.pearson' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####    
    my $domain = 0;
    my $sub_query = "";    
    
    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and rt.id=\'%s\' ", $params->{id});	
    }
    
    if(defined($params->{model}) && ($params->{model} ne '') && ($params->{model} ne 'all')) {
 	$sub_query .= sprintf(" and pr.model=\'%s\' ", $params->{model});	
    }

	if (defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
		$sub_query .= sprintf(" and gr.id='%d' ", $params->{groups_id});
	}
    
    ## NOTE DOMAINS NOT IMPLEMENTED FOR REFINEMENT TARGETS
    if(defined($params->{target}) && ($params->{target} ne '')) {
	if ($params->{target} =~ /^(\S+)-D(\S+)/) { 
	    $sub_query .= sprintf(" and tr.name=\'%s\' ", $1);
	    $domain = $2;
	} else {
	    $sub_query .= sprintf(" and tr.name=\'%s\' ", $params->{target});	    
	}
    }
    
    if(defined($params->{is_qa2_only}) && ($params->{is_qa2_only} ne '') && ($params->{is_qa2_only} eq 1)) {
	$sub_query .= sprintf(" and qa.qa2_pearson > 0 and qa.mean > 0 ");	
    }
    
    
    
    my @results = ();
    
    my $query = sprintf("SELECT qa.id, pr.id, tr.name, pr.pfrmat, gr.code, gr.name, pr.model,
		qa.model_count,	qa.pearson, qa.pearson_z_score, qa.fisher_zprime, qa.fisher_zprime_z_score, qa.spearman, qa.t_probability, qa.t_value, qa.f_probability, qa.f_value, qa.df, qa.mean, qa.deviation,
		qa.qa2_pearson, qa.qa2_pearson_z_score, qa.qa2_fisher_zprime, qa.qa2_fisher_zprime_z_score
					
		FROM %s qa  JOIN casp13.predictions pr ON (pr.id = qa.predictions_id)
		JOIN casp13.targets tr ON  (tr.name = pr.target)
		JOIN casp13.groups gr ON (gr.id = pr.groups_id)
		WHERE 1=1 %s ORDER BY %s %s LIMIT 1000", $self->{_table}, $sub_query, $field, $order);
    
		
    my $sth = $self->{_database}->query($query);

#    print $query . "\n";
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $predictions_id, $target, $pfrmat, $code, $name, $model,
		$model_count, $pearson, $pearson_z_score, $fisher_zprime, $fisher_zprime_z_score, $spearman, $t_probability, $t_value, $f_probability, $f_value, $df, $mean, $deviation,
		$qa2_pearson, $qa2_pearson_z_score, $qa2_fisher_zprime, $qa2_fisher_zprime_z_score) = $sth->fetchrow_array()) {
	    
	    
	    
	    if(defined($not_rount) && $not_rount eq 1) {
		push(@results, {
		INDEX => $index,
		ID => $id,		
		PREDICTIONS_ID => $predictions_id,
		MODEL_NAME => $r_m->build_model_name($target, $pfrmat, $code, $model, 0, 0),
		FULL_TARGET => $r_m->build_full_target_name($target, 0),
		TARGET => $target,
		CODE => sprintf('%03d', $code),
		NAME => $name,
		MODEL_NUMBER => $model,
		model_count => $model_count,
		pearson => $pearson,
		pearson_z_score => $pearson_z_score,
		fisher_zprime => $fisher_zprime,
		fisher_zprime_z_score => $fisher_zprime_z_score,
		spearman => $spearman,
		t_probability => $t_probability,#sprintf("%.4f", $t_probability),
		t_value => $t_value,
		f_probability => $f_probability, #sprintf("%.4f", $f_probability),
		f_value => $f_value,
		df => $df,
		mean => $mean,
		deviation => $deviation,
		qa2_pearson => $qa2_pearson,
		qa2_pearson_z_score => $qa2_pearson_z_score,
		qa2_fisher_zprime => $qa2_fisher_zprime,
		qa2_fisher_zprime_z_score => $qa2_fisher_zprime_z_score
		
		});		
	    } else {		
		push(@results, {
		INDEX => $index,
		ID => $id,		
		PREDICTIONS_ID => $predictions_id,
		MODEL_NAME => $r_m->build_model_name($target, $pfrmat, $code, $model, 0, 0),
		FULL_TARGET => $r_m->build_full_target_name($target, 0),
		TARGET => $target,
		CODE => sprintf('%03d', $code),
		NAME => $name,
		MODEL_NUMBER => $model,
		model_count => $model_count,
		pearson => sprintf("%.3f", $pearson),
		pearson_z_score => sprintf("%.3f", $pearson_z_score),
		fisher_zprime => sprintf("%.3f",$fisher_zprime),
		fisher_zprime_z_score=> sprintf("%.3f",$fisher_zprime_z_score),
		spearman => sprintf("%.3f", $spearman),
		t_probability => sprintf("%.2e",$t_probability),#sprintf("%.4f", $t_probability),
		t_value => sprintf("%.2f", $t_value),
		f_probability => $f_probability, #sprintf("%.4f", $f_probability),
		f_value => sprintf("%.2f", $f_value),
		df => $df,
		mean => sprintf("%.2f",$mean),
		deviation => sprintf("%.2f",$deviation),
		qa2_pearson => sprintf("%.3f",$qa2_pearson),
		qa2_pearson_z_score => sprintf("%.3f",$qa2_pearson_z_score),
		qa2_fisher_zprime => sprintf("%.3f",$qa2_fisher_zprime),
		qa2_fisher_zprime_z_score => sprintf("%.3f",$qa2_fisher_zprime_z_score)
		
		});
	    }
	    
	    
	    
	    
	    
	    $index++;
        }
    }
    return @results;
}


sub get_groups {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};	
    $field = (!defined($field) || ($field eq '')) ? 'gr.code' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    #### 
    my $sub_query = "";    
    
    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and gr.id=\'%s\' ", $params->{id});	
    }
    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
 	$sub_query .= sprintf(" and pr.pfrmat=\'%s\' ", $params->{pfrmat});	
    }
    
    my @results = ();
    
    my $query = sprintf("SELECT DISTINCT pr.groups_id, gr.code, gr.name  from %s qa
	join casp13.predictions pr on (pr.id = qa.predictions_id)
	join casp13.groups gr on (gr.id = pr.groups_id)
	WHERE 1=1 %s ORDER BY %s %s LIMIT 1000", $self->{_table}, $sub_query, $field, $order);
    
		
    my $sth = $self->{_database}->query($query);

### AK    print $query;
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $code, $name) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,
		ID => $id,
		CODE => $code,
		NAME => $name	    
	    });	    
	    $index++;
        }
    }
    return @results;
    
}


sub get_targets {
    
    #need to be tested!!!!!
    
    
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};	
    $field = (!defined($field) || ($field eq '')) ? 'gr.code' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    #### 
    my $sub_query = "";    
    my $sub_join = "";
    
    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and gr.id=\'%s\' ", $params->{id});
	$sub_join .= " join casp13.groups gr on (gr.id = pr.groups_id) ";
    }
    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
 	$sub_query .= sprintf(" and pr.pfrmat=\'%s\' ", $params->{pfrmat});	
    }
    
    my @results = ();
    
    my $query = sprintf("SELECT DISTINCT pr.target from %s qa
	join casp13.predictions pr on (pr.id = qa.predictions_id)
	
	WHERE 1=1 %s ORDER BY %s %s LIMIT 1000", $self->{_table}, $sub_query, $field, $order);
    
		
    my $sth = $self->{_database}->query($query);

# AK    print $query;
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $code, $name) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,
		ID => $id,
		CODE => $code,
		NAME => $name	    
	    });	    
	    $index++;
        }
    }
    return @results;
    
}


# get TS models that were released for QA predictions for a given target
sub getTSmodels{
    my ($self, $params) = @_;
    my @results = ();
    if (! defined($params->{target})){
	return @results;
    }
    my $query = sprintf("select distinct  pr.target, pr.pfrmat, gr.code, pr.model, re.alignment_4
	    from casp13.qa_results qar join casp13.qa_analysis qaa on qaa.id=qar.qa_analysis_id 
	    join casp13.results re on re.id=qar.results_id
	    join casp13.predictions pr on re.predictions_id=pr.id
	    join casp13.groups gr on gr.id=pr.groups_id
	    where pr.target='%s' and qar.distances not similar to 'X' order by gr.code, pr.model", $params->{target});
    my $sth = $self->{_database}->query($query);
    my $index = 0;
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($target, $pfrmat, $code, $model, $distances) = $sth->fetchrow_array()) {
		push(@results, {
		INDEX => $index,
		NAME => sprintf("%s%s%03d_%d", $target, $pfrmat, $code, $model),
                #TARGET => $target,
                #CODE => $code
		distances =>  $distances,
            });
	    $index++;
	}
    }
    return @results;
}


# 
sub getQAmodels{
    my ($self, $params) = @_;
    my @results = ();
    if (! defined($params->{target})){
        return @results;
    }
    my $query = sprintf("select prq.target, prq.pfrmat, grq.code, prq.model,  qar.distances from casp13.qa_results qar 
	join casp13.qa_analysis qaa on qaa.id=qar.qa_analysis_id
	join casp13.results re on re.id=qar.results_id
	join casp13.predictions pr on re.predictions_id=pr.id
	join casp13.groups gr on gr.id=pr.groups_id
	join casp13.predictions prq on qaa.predictions_id=prq.id
	join casp13.groups grq on grq.id=prq.groups_id
	where pr.target='%s' and gr.code=%d and pr.model=%d and qar.distances not similar to 'X' ", $params->{target}, $params->{code}, $params->{model});
    my $sth = $self->{_database}->query($query);
    my $index = 0;
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($target, $pfrmat, $code, $model, $distances) = $sth->fetchrow_array()) {
                push(@results, {
                NAME => sprintf("%s%s%03d_%d", $target, $pfrmat, $code, $model),
		INDEX => $index,
                #TARGET => $target,
                #CODE => $code
		distances =>  $distances,
            });
	    $index++;
        }
    }
    return @results;
}

sub get_diff_mqas_4scores{
    my ($self, $params) = @_;
    my @results = ();
    my $subquery = "";
    my $models = '1,2';
    if (defined($params->{model})){
	if ($params->{model} =~ m/^[12]$/){
		$models = $params->{model};
	}
    }
    if (defined($params->{target}) && $params->{target} =~ m/^T/){
	$subquery = sprintf("\'%s\'", $params->{target});
    } else {
	$subquery = " select
         case when max(re.gdt_ts_4)>0.0 then t.name
         end
         from casp13.targets t
           join casp13.predictions pr on pr.target=t.name
           join casp13.results re on re.predictions_id=pr.id
           where re.domain = 0 and re.parent= 0 and re.n1_4>19 and t.name similar to 'T%' and t.cancellation_qa = 0
           group by t.name order by t.name ";
    }
    my $query = "select gr.code as gr_code,  gr.name as name, pr.model as model,
    AVG(abs(100*qre.mqas_score - re.gdt_ts_4)) as avg_diff_mqa_gdt_ts,
    AVG(abs(100*(qre.mqas_score - re.lddt))) as avg_diff_mqa_lddt,
    AVG(abs(100*(qre.mqas_score - re.cad_aa))) as avg_diff_mqa_cad,
    AVG(abs(100*qre.mqas_score - re.sg_py)) as avg_diff_mqa_sg
    from casp13.qa_results qre
    join casp13.results re on re.id = qre.results_id
    join casp13.qa_analysis qaa on qaa.id = qre.qa_analysis_id
    join casp13.predictions pr on  pr.id = qaa.predictions_id
    join casp13.groups gr on gr.id = pr.groups_id
    join casp13.targets t on t.name = pr.target
    where
    pr.model in ($models)
    and gr.code not in (171)
    and re.parent = 0
    and re.domain = 0
    and re.n1_4 > 19
    and t.cancellation_qa = 0
    and t.name in (
	$subquery
        )
    group by gr.code, gr.name,  pr.model 
    order by  avg_diff_mqa_gdt_ts";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	my $index = 1;
	while(my($code, $gr_name, $model, $diff_gdt_ts, $diff_lddt, $diff_cad, $diff_sg) = $sth->fetchrow_array()) {
		push @results, {
			INDEX => $index,
			GROUP => sprintf("%sQA%03d_%d", (defined($params->{target}) && $params->{target} =~ m/^T/ ? $params->{target} : ''), $code, $model),
			GR_NAME => $gr_name,
			DIFF_GDT_TS =>  sprintf("%.3f", $diff_gdt_ts),
			DIFF_LDDT => sprintf("%.3f", $diff_lddt),
			DIFF_CAD => sprintf("%.3f", $diff_cad),
			DIFF_SG => sprintf("%.3f", $diff_sg),
		};
		$index++;
	}
    }
    return @results;
}

sub get_targets_2{
    my ($self) = @_;
    my $query = "select distinct name from casp13.targets where name similar to 'T%' and cancellation_qa=0 order by name";
    my @results = ();
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($target) = $sth->fetchrow_array()) {
		push @results, {
			_TARGET => $target,
		};
	}
    }
    return @results;
}


1;