package QADiff2bestManager;

use strict;
use warnings;


use DBI;
use lib qw(Core);
use Database;
use Configuration;
use LocalConfiguration;
use BO;
our @ISA = qw(BO);

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

sub get_new_model{
    my ($self) = @_;
    my %model = (
	id => '',
	gr_name => '',
	gr_code => '',
	target => '',
	model => '',
	mqas => '',
	diff_gdt_ts => '',
	diff_cad_aa => '',
	diff_cad_ss => '',
	diff_lddt => '',
	diff_sg => ''
    );
    return %model;
}

sub is_table_column {
    my ( $self, $column_name) = @_;
    my $result = 0;
    if ( $column_name eq 'id' ||
	 $column_name eq 'gr_name' ||
	 $column_name eq 'gr_code' ||
	 $column_name eq 'target' ||
	 $column_name eq 'model' ||
	 $column_name eq 'mqas' ||
	 $column_name eq 'diff_gdt_ts' ||
	 $column_name eq 'diff_cad_aa' ||
	 $column_name eq 'diff_cad_ss' ||
	 $column_name eq 'diff_lddt' ||
	 $column_name eq 'diff_sg') {
	$result = 1;
    } 
    return $result;
}

sub exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf(" SELECT id FROM %s 
	WHERE gr_name=\'%s\' AND gr_code=%d AND target=\'%s\' AND model=%d 
	", $self->{_table}, $model{gr_name}, $model{gr_code}, $model{target}, $model{model}
	);
    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 getAVRGrows {
    my ($self, $params) = @_;
    my $where = " WHERE 1=1 ";
    if (defined($params->{model}) && $params->{model} =~ m/^[12]{1}$/){
	$where .= sprintf (" AND model in (%s) ", $params->{model});
    }
    if (defined($params->{target}) && $params->{target} =~ m/^T/){
        $where .= sprintf (" AND target = \'%s\' ", $params->{target});
    }
    my $query = "SELECT gr_name, gr_code, model,
                count(diff_gdt_ts) as no_gdt, avg(diff_gdt_ts) as mean_gdt, 
                count(diff_cad_aa) as no_cad, avg(diff_cad_aa) as mean_cad, 
                count(diff_lddt) as no_lddt, avg(diff_lddt) as mean_lddt, 
                count(diff_sg) as no_sg, avg(diff_sg) as mean_sg
	        FROM casp13.qa_diff2best 
		$where
		GROUP BY gr_name, gr_code, model
        	ORDER BY mean_gdt ASC ";
    my @results = ();
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	my $index = 1;
        while(my($gr_name, $gr_code, $model, 
		$no_gdt, $mean_gdt,
		$no_cad, $mean_cad,
		$no_lddt, $mean_lddt,
		$no_sg, $mean_sg
		) = $sth->fetchrow_array()) {
                push @results, {
			INDEX => $index, 
                        GR_NAME => $gr_name,
			GR_MODEL => sprintf("QA%03d_%d", $gr_code, $model),
			NO_GDT => $no_gdt,
			MEAN_GDT => (defined($mean_gdt) ? sprintf("%.3f", $mean_gdt) : '-'),
			NO_CAD => $no_cad,
                        MEAN_CAD => (defined($mean_cad) ? sprintf("%.3f", $mean_cad) : '-'),
			NO_LDDT => $no_lddt,
                        MEAN_LDDT => (defined($mean_lddt) ? sprintf("%.3f", $mean_lddt) : '-'),
			NO_SG => $no_sg,
                        MEAN_SG => (defined($mean_sg) ? sprintf("%.3f", $mean_sg) : '-')
                };
		$index++;
        }
    }
    return @results;
}

sub get_targets {
    my ($self) = @_;
    my $query = "SELECT DISTINCT target FROM casp13.qa_diff2best ORDER BY target";
    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; 
}


sub get_perc_bins{
    my ($self, $params) = @_;
    my $field = "diff_gdt_ts";
    if (defined($params->{score})){
	if ($params->{score} =~ m/gdt/){
		$field = 'diff_gdt_ts';
	} elsif ($params->{score} =~ m/lddt/){
		$field = 'diff_lddt';
	} elsif ($params->{score} =~ m/cad/){
                $field = 'diff_cad_aa';
        } elsif ($params->{score} =~ m/sg/){
                $field = 'diff_sg';
        }
    }
    my $mod = 1;
    if (defined($params->{model}) && $params->{model} == 2){
	$mod = 2;
    }
    my $where = " WHERE model=$mod ";
    my $query = sprintf("
	 select gr_name, gr_code, model, count(%s) as no_targ,
	     sum(case when %s <= 2.0 then 1 else 0 end) as no_lt2,
	     sum(case when %s > 10.0 then 1 else 0 end) as no_gt10
	     from casp13.qa_diff2best
	     $where
	     group by gr_name, gr_code, model
	     order by  no_lt2 desc ",  $field,  $field,  $field);
    my @results = ();
    my $sth = $self->{_database}->query($query);
    if (defined($sth) && ($sth->rows() > 0)) {
        while (my ($gr_name, $gr_code, $model,
                $no_targ, $no_lt2, $no_gt10) = $sth->fetchrow_array()) {
          push @results, {
                GR_NAME => $gr_name,
		GR_MODEL => sprintf("QA%03d_%d", $gr_code, $model),
		PERC_LT2 => sprintf("%.3f", 100.0*$no_lt2/$no_targ),
		PERC_210 => sprintf("%.3f", 100.0*(1 - ($no_lt2+$no_gt10)/$no_targ)),
		PERC_GT10 => sprintf("%.3f", 100.0*$no_gt10/$no_targ),
		PERC_DIFF => sprintf("%.3f", (100.0*$no_lt2-100.0*$no_gt10)/$no_targ)
          }
        }
    }
    @results = sort{$b->{PERC_DIFF}<=>$a->{PERC_DIFF}} @results;
    return @results;
}


1;