package EMAEstimateManager;

use strict;
use warnings;


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

my $DEBUG = 0;

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

sub get_new_model{
    my ($self) = @_;
    my %model = (
	id => '',
	gr_code => '',
	gr_name => '',
	model => '',
	target => '',
	ts_model => '',
	mqas => '',
	distances => '',
	qa_predictions_id => '',
	ts_predictions_id => '',
    );
    return %model;
}

sub is_table_column {
    my ( $self, $column_name) = @_;
    my $result = 0;
    if ( $column_name eq 'id' ||
	 $column_name eq 'qa_predictions_id' ||
	 $column_name eq 'ts_predictions_id' ||
	 $column_name eq 'mqas' ||
         $column_name eq 'distances' 
	 ) {
	$result = 1;
    } 
    return $result;
}

sub exist_by_parameters {
    my ($self, %model) = @_;
    if ($model{qa_predictions_id} eq ''){
      my $qa_id = $self->get_qa_predictions_id(%model);
      if (defined($qa_id) && $qa_id > 0){
        $model{qa_predictions_id} = $qa_id;
      } else {
	# qa_prediction is not registered in database
	return -1;
      }
    }
    if ($model{ts_predictions_id} eq ''){
      my $ts_id = $self->get_ts_predictions_id(%model);    
      if (defined($ts_id) && $ts_id > 0){
  	$model{ts_predictions_id} = $ts_id;
      } else {
  	# ts_prediction is not registered in database
  	return -1;
      }
    }
    my $query = sprintf(" SELECT id FROM %s 
	WHERE qa_predictions_id=%d AND ts_predictions_id=%d  
	", $self->{_table}, $model{qa_predictions_id}, $model{ts_predictions_id}
	);
    my $sth = $self->{_database}->query($query);
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
        return $id;
    }
    # return 0 if there is no record in database
    return 0;
}

sub get_ts_predictions_id {
    my ($self, %model) = @_;
    my $result = 0;
    if ($model{ts_model} =~ m/^(\S+)TS(\d+)_(\d+)$/) {
	my $target = $1; my $gr_code = $2; my $model_ = $3;
        my $query = sprintf("SELECT p.id FROM caspcommons.predictions p
		JOIN caspcommons.groups g ON p.groups_id=g.id
		WHERE p.target=\'%s\' AND p.model=%d AND g.code=%d ",
		$target, $model_, $gr_code);
        print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
	my $sth = $self->{_database}->query($query);
	if(defined($sth) && ($sth->rows() > 0)) {
		my ($id) = $sth->fetchrow_array();
		$result = $id;
		print ">>get_ts_predictions_id: $result\n" if $DEBUG==1;
	}
    }
    return $result;
}

sub get_qa_predictions_id {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT p.id FROM caspcommons.predictions p
                JOIN caspcommons.groups g ON p.groups_id=g.id
                WHERE p.target=\'%s\' AND p.model=%d AND g.code=%d ",
                $model{target}, $model{model}, $model{gr_code});
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	my ($id) = $sth->fetchrow_array(); 
	$result = $id;
	print ">>get_qa_predictions_id: $result\n" if $DEBUG==1;
    }
    return $result;
}

sub get_qa_groups {
    my ($self, $param) = @_;
    my $where = '';
    if (defined($param->{_target})){
	$where = sprintf " WHERE p.target=\'%s\' ", $param->{_target};
    }
    my @results = ();
    my $query = "SELECT DISTINCT g.name AS gr_name, g.code AS gr_code 
		FROM caspcommons.groups g 
		JOIN caspcommons.predictions p ON p.groups_id=g.id
		JOIN caspcommons.ema_estimates ee ON ee.qa_predictions_id=p.id 
		$where
		ORDER BY g.name";
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my ($gr_name, $gr_code) = $sth->fetchrow_array()){
		push @results, {
			_QA_GR_NAME => $gr_name,
			_QA_GR_CODE => sprintf("%03d", $gr_code),
		};
	}
    }
    return @results;
}


sub get_ts_targets {
    my ($self) = @_;
    my @results = ();
    my $query = "SELECT DISTINCT p.target AS target, ss.protein_name  
		FROM caspcommons.predictions p
		JOIN caspcommons.ema_estimates ee ON ee.ts_predictions_id=p.id 
		JOIN caspcommons.targets t ON t.name::text=p.target::text
		JOIN caspcommons.submitted_sequences ss ON ss.id=t.submitted_sequences_id
		ORDER BY p.target";
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my ($target, $protein_name) = $sth->fetchrow_array()){
		push @results, {
			_TARGET => $target,
			_PROTEIN_NAME => $protein_name,
		};
	}
    }
    return @results;
}

sub get_ts_targets_selected {
    my ($self) = @_;
    my @results = ();
    my $where = " WHERE p.target = 'C1905' or p.target = 'C1908' ";
    my $query = "SELECT DISTINCT p.target AS target, ss.protein_name  
		FROM caspcommons.predictions p
		JOIN caspcommons.ema_estimates ee ON ee.ts_predictions_id=p.id 
		JOIN caspcommons.targets t ON t.name::text=p.target::text
		JOIN caspcommons.submitted_sequences ss ON ss.id=t.submitted_sequences_id
		$where
		ORDER BY p.target";
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my ($target, $protein_name) = $sth->fetchrow_array()){
		push @results, {
			_TARGET => $target,
			_PROTEIN_NAME => $protein_name,
		};
	}
    }
    return @results;
}

sub get_ts_models {
    my ($self, $param) = @_;
    my @results = ();
    my $where = '';
    if (defined($param->{_target})){
	$where .= sprintf(" WHERE p.target=\'%s\' ", $param->{_target});
    }
    my $query = "SELECT DISTINCT p.target AS target, g.code AS gr_code, p.model AS model  
		FROM caspcommons.predictions p
		JOIN caspcommons.ema_estimates ee ON ee.ts_predictions_id=p.id 
		JOIN caspcommons.groups g ON p.groups_id=g.id
		$where
		ORDER BY p.target, g.code, p.model";
    print "EXIST SQL: " . $query ."\n" if $DEBUG==1;    
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my ($target, $gr_code, $model) = $sth->fetchrow_array()){
		push @results, {
			_TS_MODEL_NAME => sprintf("%sTS%03d_%d", $target, $gr_code, $model)
		};
	}
    }
    return @results;
}

sub get_results {
    my ($self, $param) = @_;
    my @results = ();
    my $where = " WHERE 1=1 ";#" WHERE ee.distances IS NOT NULL AND ee.distances<>\'\' ";
    if (defined($param->{_target})) {
	$where .= sprintf(" AND pts.target=\'%s\' AND pqa.target=\'%s\' ", $param->{_target}, $param->{_target});
    }
    if (defined($param->{_qa_gr_code}) && $param->{_qa_gr_code} =~ m/^\d+$/) {
	$where .= sprintf(" AND gqa.code=%d ", $param->{_qa_gr_code});
    }
    if(defined($param->{_ts_model_name}) && $param->{_ts_model_name} ne 'all'){
	if ($param->{_ts_model_name} =~ m/(\S+)TS(\d+)_(\d+)/) {
		my $target = $1; my $ts_g_code = $2; my $ts_model = $3;
		$where .= sprintf(" AND pqa.target=\'%s\' AND pts.target=\'%s\' AND gts.code=%d AND pts.model=%d ",
				$target, $target, $ts_g_code, $ts_model);
	} 
    }
    my $query = 
    "SELECT pts.target AS target,
	    gqa.name AS G_QA_NAME, gqa.code AS G_QA_CODE, pqa.model AS QA_MODEL,
	    gts.name AS G_TS_NAME, gts.code AS G_TS_CODE, pts.model AS TS_MODEL,
	    ee.mqas AS MQAS, ee.distances,
	    res.lddt AS LDDT, res.gdt_ts_4 as GDT_TS
     FROM caspcommons.ema_estimates ee
     JOIN caspcommons.predictions pqa ON ee.qa_predictions_id=pqa.id
     JOIN caspcommons.groups gqa ON pqa.groups_id=gqa.id
     JOIN caspcommons.predictions pts ON ee.ts_predictions_id=pts.id
     JOIN caspcommons.groups gts ON pts.groups_id=gts.id
     LEFT JOIN caspcommons.results res ON res.predictions_id=pts.id
     $where
     ORDER BY ee.mqas DESC ";
    print "===\n$query\n" if $DEBUG==1;
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       my $index = 1;
       while(my ($target, $g_qa_name, $g_qa_code, $qa_model,
		$g_ts_name, $g_ts_code, $ts_model, $mqas, $dist, $lddt, $gdt_ts ) = $sth->fetchrow_array()){
             push @results, {   
		  INDEX => $index++,           
                  _TARGET => $target,
		  _QA_MODEL_NAME => sprintf("%sQA%03d_%d", $target, $g_qa_code, $qa_model),
		  _QA_GR_CODE => $g_qa_code,
		  _QA_GR_NAME => $g_qa_name,
		  _TS_MODEL_NAME => sprintf("%sTS%03d_%d", $target, $g_ts_code, $ts_model),
		  _TS_GR_NAME => $g_ts_name,
   		  _LDDT => $lddt,
                  _GDT_TS => $gdt_ts,
		  _MQAS => $mqas,
		  _DIST => $dist
             };
        }
    }
    return @results;
}

1;