package GroupsAnalysisManager;
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 CGI qw(:all);
use BO;
our @ISA = qw(BO);
sub new {
    my ($class) = @_;
    my $self = $class->SUPER::new("casp9.groups_analysis");

    bless $self, $class;
    return $self;
}

sub get_new_model {
    my ($self) = @_;
    my %model = (
########################### system
	id => '',
	groups_id => '',
    domain_classifications_id => '',
	target_type => '',
########################## data
    sum_z_gdt_ts_all_pos => '',
	sum_z_gdt_ts_server_pos => '',
	sum_z_gdt_ha_all_pos => '',
	sum_z_gdt_ha_server_pos => '',
	sum_z_gdt_al0p_all_pos => '',
	sum_z_gdt_al0p_server_pos => '',

	avg_gdt_ts_all => '',
	avg_gdt_ts_server => '',
	avg_gdt_ha_all => '',
	avg_gdt_ha_server => '',
	avg_gdt_al0p_all => '',
	avg_gdt_al0p_server => '',

	avg_mom_z => '',
	avg_dali_z => '',
	avg_dal_4 => '',

	domains_count => '',
######################### OTHER
	INDEX => 0
    );
    return %model;
}


sub is_table_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	$colunm_name eq 'groups_id' ||
	$colunm_name eq 'domain_classifications_id' ||
	$colunm_name eq 'target_type' ||
	########################### data
	$colunm_name eq 'sum_z_gdt_ts_all_pos' ||
	$colunm_name eq 'sum_z_gdt_ts_server_pos' ||
	$colunm_name eq 'sum_z_gdt_ha_all_pos' ||
	$colunm_name eq 'sum_z_gdt_ha_server_pos' ||
	$colunm_name eq 'sum_z_gdt_al0p_all_pos' ||
	$colunm_name eq 'sum_z_gdt_al0p_server_pos' ||

	$colunm_name eq 'avg_gdt_ts_all' ||
	$colunm_name eq 'avg_gdt_ts_server' ||
	$colunm_name eq 'avg_gdt_ha_all' ||
	$colunm_name eq 'avg_gdt_ha_server' ||
	$colunm_name eq 'avg_gdt_al0p_all' ||
	$colunm_name eq 'avg_gdt_al0p_server' ||

	$colunm_name eq 'avg_mom_z' ||
	$colunm_name eq 'avg_dali_z' ||
	$colunm_name eq 'avg_dal_4' ||

	$colunm_name eq 'domains_count'

  ) {
	$result = 1;
	}
    return $result;
}

sub exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT id FROM %s WHERE groups_id=%d and domain_classifications_id=%d and target_type=%d",
	$self->{_table}, $model{groups_id}, $model{domain_classifications_id}, $model{target_type});
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
	$result = $id;
    }
    return $result;
}

sub get_rows {
    my ($self,  $type, $tbm, $tbmfm, $fm, $exclude) = @_;

    my @results = ();
	my @types = ();
	if ($tbm eq 'on' ) {  push (@types, 2); }
	if ($tbmfm eq 'on') {push (@types, 4); }
 	if ($fm eq 'on') { push (@types, 3); }
	if (!@types) { push (@types, 2) ; }
	my $model = join(',',@types);

 	my $q = "
 	  select
 		gr_id,
 		count(domains_count) as domains_count,
 		gr_name as gr_name,
 		type,
		sum(case when sum_gdt_ts_z>0 then sum_gdt_ts_z else 0 end) as sum_gdt_ts_z,
		avg(case when sum_gdt_ts_z>0 then sum_gdt_ts_z else 0 end) as avg_gdt_ts_z,
		avg(avg_gdt_ts_z) as avg_gdt_ts_z,
		avg(case when sum_al0p_z>0 then sum_al0p_z else 0 end) as sum_al0p_z,
		avg(avg_al0p) as avg_al0p,
		avg(case when avg_gdt_ha_4>0 then avg_gdt_ha_4 else 0 end) as avg_gdt_ha_4,
		avg(case when avg_mammoth>0 then avg_mammoth else 0 end ) as avg_mammoth, avg(mean) as ca, avg(avg_dali) as dali,
		median(response_time) as response_time
		from "  .(($exclude eq 'old') ? "groups_analysis_distinct_old": "groups_analysis_distinct" )."
 	  where dci in (".$model.") and ".(($type eq 'all') ? " is_server_only=0 and group_type='h/s'" : " type>0  and group_type='s'")."
 	  group by gr_id, gr_name, type
 	  order by  sum_gdt_ts_z desc";

    my $sth = $self->{_database}->query($q);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 0;
        while(my($gr_id, $domains_count, $gr_name, $gr_type, $sum_gdt_ts_z, $avg_gdt_ts_z, $avg_gdt_ts, $sum_al0p_z, $avg_al0p,$avg_gdt_ha_4, $avg_mammoth_z, $ca, $dali, $response_time) = $sth->fetchrow_array()) {

		push(@results, {
		INDEX => ++$index,
		NAME => $gr_name,
		TYPE => $gr_type,
		CODE => sprintf('%03d', $gr_id),
		sum_gdt_ts_z 	=> sprintf("%.3f", $sum_gdt_ts_z),
		avg_gdt_ts_z 	=> sprintf("%.3f", $avg_gdt_ts_z),
 		avg_gdt_ts 		=> sprintf("%.3f", $avg_gdt_ts),
		sum_z_gdt_al0p 	=> sprintf("%.3f", $sum_al0p_z),
 		avg_gdt_al0p 	=> sprintf("%.3f", $avg_al0p),
 		avg_gdt_ha 		=> sprintf("%.3f", $avg_gdt_ha_4),
 		avg_mom_z 		=> sprintf("%.3f", $avg_mammoth_z),
		avg_dal_4		=> sprintf("%.3f", $ca),
		dali 			=> sprintf("%.3f", $dali),
		response_time   => sprintf("%.3f", $response_time),
		domains_count => $domains_count
		}
	    );
        }
    }
    return @results;
}

1;