package ResultsTemplatesManager;

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 TargetsManager;
use ResultsManager;
my $r_m = new ResultsManager();	

my $templates_manager = undef;

sub new {
    my ($class) = @_;    
    return $templates_manager if(defined($templates_manager));
    my %model = (ID => '');
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}),
        _model => \%model
    };
    
    $templates_manager = bless $self, $class;
    return $templates_manager;
}


sub get_new_model {
    my ($self) = @_;    
    my %model = (
########################### system        
	id => '',
	name =>'',
        targets_id =>'',
        lga_s_5 => '',
	seq_id_5 =>'',
        lga_values_5 =>'',
	rmsd_5 =>'',
        domain => '',
	result_file_name => '',
	n  => '',
	n1 => '',
	n2 => '',
	is_selected => 0,
########################## other fields
        TARGET => ''
    );
    return %model;
}
sub is_templates_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	$colunm_name eq 'name' ||
	$colunm_name eq 'targets_id' ||
	$colunm_name eq 'lga_s_5' ||
	$colunm_name eq 'seq_id_5' ||
	$colunm_name eq 'lga_values_5' ||
	$colunm_name eq 'rmsd_5' ||
	$colunm_name eq 'domain' ||
	$colunm_name eq 'result_file_name' ||
	$colunm_name eq 'n' ||
	$colunm_name eq 'n1' ||
	$colunm_name eq 'n2' ||
	$colunm_name eq 'is_selected'
	
  ) {
	$result = 1;
}
return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM casp13.templates 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 $result = 0;
    my $query = sprintf("SELECT tm.id FROM casp13.templates tm JOIN casp13.targets tr ON (tm.targets_id = tr.id)
	WHERE tr.name=\'%s\' and tm.domain=%d and tm.name=\'%s\' limit 1",
      	$model{TARGET}, $model{domain}, $model{name});
    my $sth = $self->{_database}->query($query);
    #print $query ."\n";
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
	$result = $id;
    }
    return $result;
}

sub update {
    my ($self, %model) = @_;
    
    my $result = 0;
    my $set_query = '';

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

    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, %model) = @_;
    my $result = 0;
print "\n\nadd start \n";
    my $column_names = '';
    my $column_values = '';
    my $values_count = 0;

    while ( my ($key, $value) = each(%model) ) {	
	if($key eq 'id' || $key eq 'date' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{ 
		if($self->is_templates_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 casp13.templates ( %s ) VALUES ( %s )", $column_names, $column_values);

    my $tmp = $query;
	print $query . "\n";

    my $sth = $self->{_database}->query($query);
print "execute query \n";
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.templates");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    } 
	print "---------------------ADD: $result ";
	if ($result == 0) {
		print "\n" . $tmp . "\n";
	}

print "add end \n";

    return $result;
}



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

sub get_templates {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};	
    	
    $field = (!defined($field) || ($field eq '')) ? 'tm.lga_s_5' : $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 $domain = 0;
    my $target_name = ""; 


    if(defined($params->{target}) && ($params->{target} ne '')) {
 	$domain = $r_m->get_domain_from_full_target_name($params->{target});
 	$target_name = $r_m->get_target_from_full_target_name($params->{target});
 	$sub_query .= sprintf(" and tr.name=\'%s\' ", $target_name);
	
	if(defined($params->{domain}) && ($params->{domain} ne '') && $domain == 0) {
	    $sub_query .= sprintf(" and tm.domain=\'%s\' ", $params->{domain});	
	} else {
	    $sub_query .= sprintf(" and tm.domain=\'%s\' ", $domain);
	}	
    }

    if(defined($params->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and tm.id=\'%s\' ", $params->{id});	
    }


    my @results = ();

    my $query = sprintf("SELECT tm.id, tm.name, tm.targets_id, tm.domain, tr.name, tm.lga_s_5, tm.seq_id_5, tm.lga_values_5, tm.rmsd_5, tm.result_file_name, tm.is_selected FROM casp13.templates tm JOIN casp13.targets tr ON  (tr.id = tm.targets_id)
		WHERE 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);

#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print $query; 

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

    #print $query;

#$dbquery = "SELECT name, lga_s, rmsd, lga_values FROM templates WHERE (target = '" . $target . "') AND (domain = " . $PARAMS{domain} . ")ORDER BY lga_s DESC";
#dbPrepareAndExecute( query => $dbquery );
#my $counter = 0;
#while(my ($name, $lga_s, $rmsd, $lga_values) = $APPENV{sth}->fetchrow_array) {
#	push(@templates, {'name' => $name, 'lga_s' => $lga_s, 'rmsd' => $rmsd, 'distances' => $lga_values});
#

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $name, $targets_id, $domain, $target_name, $lga_s_5, $seq_id_5, $lga_values_5, $rmsd_5, $result_file_name, $is_selected) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,
		id => $id,
		name => $name,
		targets_id => $targets_id,
		domain => $domain,
		TARGET => $target_name,
		lga_s => $lga_s_5,
		seq_id => $seq_id_5,
		distances => $lga_values_5,
		rmsd => $rmsd_5,
		result_file_name => $result_file_name,
		IS_SELECTED => $is_selected
		}
	    );	
	    $index++;
        }
    }
    return @results;
}


sub get_models {
    my ($self, $params) = @_;
    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 '')) ? 're.gdt_ts_4' : $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 re.id=\'%s\' ", $params->{id});	
    }
    
    if(defined($params->{target}) && ($params->{target} ne '')) {
	if ($params->{target} =~ /^(\S+)-D(\S+)/) { 
	    $sub_query .= sprintf(" and pr.target=\'%s\' ", $1);
	    $domain = $2;
	} else {
	    $sub_query .= sprintf(" and pr.target=\'%s\' ", $params->{target});	    
	}
    }
    ## domain defenition
    if(defined($params->{domain}) && ($params->{domain} ne '')) {	
	$sub_query .= sprintf(" and re.domain=\'%s\' ", $params->{domain});
    } else{
	$sub_query .= sprintf(" and re.domain=\'%s\' ", $domain);
    }
    
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
	$sub_query .= sprintf(" and pr.groups_id=\'%s\' ", $params->{groups_id});
    }
    if(defined($params->{model}) && ($params->{model} ne '') && ($params->{model} ne 'all')) {
	$sub_query .= sprintf(" and pr.model=\'%s\' ", $params->{model});
    }

    my @results = ();
    my $query = sprintf("SELECT gr.id, pr.target, pr.pfrmat, gr.code, pr.model, re.id, re.parent, re.domain, re.gdt_ts_4, re.rmsd_5, re.lga_s_5, re.alignment_4 FROM casp13.results re
		JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) JOIN casp13.targets tr ON  (pr.target = tr.name) JOIN casp13.groups gr ON (pr.groups_id = gr.id) 
		WHERE 1=1 %s ORDER BY %s %s LIMIT 2500", $sub_query, $field, $order);
    my $sth = $self->{_database}->query($query);

    #print $query;

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($group_id, $target, $pfrmat, $code, $model, $id, $parent, $domain, $gdt_ts_4, $rmsd_5, $lga_s_5, $alignment_4) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,				
		GROUP_ID => $group_id,
		TARGET => $target,
		PFRMAT => $pfrmat,
		CODE => sprintf("%03d",$code),
		MODEL => $model,
		ID => $id,				
		PARENT => $parent,
		DOMAIN => $domain,
		gdt_ts => sprintf("%.2f", $gdt_ts_4),
		rmsd => sprintf("%.2f", $rmsd_5),
		lga_s => $lga_s_5,				
		name => $r_m->build_model_name($target, $pfrmat, $code, $model, $parent, $domain),
		distances => $alignment_4
		}
	    );	
	    $index++;
        }
    }
    return @results;
}


sub get_domain_by_specific_range {
	my ($self, $target_name, $range) = @_;
	my $domain = 999;	
	#example: T0418_2_16_86_211
	$range =~ tr/_/%/;
	my $query = sprintf("SELECT dm.index FROM casp13.domains dm JOIN casp13.targets tr ON  (dm.targets_id = tr.id)
		WHERE  dm.range like \'%s\' and tr.name='%s' ", $range, $target_name);
	my $sth = $self->{_database}->query($query);
	
	if(defined($sth) && ($sth->rows() > 0)) {
		$domain = $sth->fetchrow_array();
	}	
	return $domain;
}

sub delete_results_for_all_domains_for_target {
    my ($self, $target_name) = @_;
    my $result = 0;
    my $query = sprintf("DELETE FROM casp13.templates WHERE targets_id IN (SELECT id FROM casp13.targets WHERE name='%s') and domain<>0 ",$target_name);
    
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub delete_results_for_target {
    my ($self, $target) = @_;
    my $result = 0;
    my $where;
    if($target =~ m/^(T[0-9]{4}(s[1-9]){0,1})-D([1-9]+)$/ ){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domain=%s ", $1, $3);
    } elsif ($target =~ m/^[TRSNFAXLnsfaxl][0-9]{4}(s[1-9]){0,1}$/ || $target =~ m/^(R[0-9]{4}(s[1-9]){0,1})-D([1-9]+)$/){
        $where = sprintf("rt.targets_id in (select t.id from casp13.targets t where t.name = \'%s\') and  rt.domain=0 ",$target);
    }else{
        return;
    }
    my $query = sprintf("DELETE FROM casp13.templates rt WHERE %s", $where);
#    print $query."\n";
#    return; 

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}


sub ToString {
    my ($self, $PS, %model) = @_;
    # $PS -   print separator could be "\n" for text or "<br>" for HTML    
    while ( my ($key, $value) = each(%model) ) {
        print sprintf("%s => %s  %s ", $key, $value, $PS);
    }    
    return 0; 
}

1;