package StatusManager;

use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Data::Dumper;

use lib qw(Core);

use Database;
use MembersManager;
use PredictionsManager;
use Configuration;
use LocalConfiguration;
use Dumpvalue;
use TargetsManager;

sub new {
    my ($class) = @_;
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})        
        
    };
    
    # check database connection if not connect throw an exception
    
    bless $self, $class;
    return $self;
}


sub add_predictions_log {    
    my ($self, $group_id, $group_name, $target_id, $target_name, $model, $pfrmat, $status, $file_name, $case_id, $recived_from, $return_name, $predictor_id, $submission_type) = @_;
    my $result = 0;
    
    #$flag - could be: original, correction
    #$pfrmat - could be: TS, AL, QA, DR, DP, RR, ...
    #$model - could be: 1, 2, 3, 4, 5
    #$status - coud be: recived, accepted, rejected
    
    my $query = sprintf("INSERT INTO casp13.predictions_log
        (date, group_id, group_name, target_id, target_name,
        model, pfrmat, status, file_name, case_id, recived_from, return, predictor_id, submission_type )
        VALUES ( now(), %s, '%s', %s, '%s', %s, '%s', '%s', '%s', '%s', '%s', '%s' , '%s', '%s' )",
        $group_id, $group_name, $target_id, $target_name, $model, $pfrmat, $status, $file_name, $case_id, $recived_from, $return_name, $predictor_id, $submission_type);
        
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "ADD <br> $query<br>";
    #TODO
    #ADD CODE TO CHECK IF STATUS IS ALREADY EXIST 

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

    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.predictions_log");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($prediction_log_id) = $sth->fetchrow_array();
    }
    
    $result = $prediction_log_id;    
    return $result;
}



sub get_id {
    my ($self) = @_;
    
    return $self->{_id};
}

sub set_id {
    my ($self, $id) = @_;
    
    $self->{_id} = (defined($id) && (scalar($id) > 0)) ? scalar($id) : undef;
    
    return;
}


sub info_public_target_list_all {
    my ($self, $page_number) = @_;
    my @result = ();
    my $sub_query = '';
    if(defined($page_number) && $page_number ne '') {
        $sub_query .= sprintf(' limit %s offset(%s) ', $LOCAL_CONFIG->{PAGE_STEP}, ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    }
    #select * from casp13.targets where (release_date + interval '17 hours' + interval '47 minutes') <=  now() 
    my $query = sprintf("select id, name, release_date from casp13.targets where (release_date + interval '%s hours\' + interval '%s minutes') <= now() order by release_date DESC, name DESC %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $sub_query);

    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "STATUS <br> $query<br>";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($target_id, $target_name, $release_date ) = $sth->fetchrow_array()) {
            push(@result, {
                    ID => $target_id,
                    NAME => $target_name,
                }
            );
            $index++;
        }
    }
    return @result;
}



sub info_public_target_list {
    my ($self, $page_number) = @_;
    my @result = ();
    my $sub_query = '';
    if(defined($page_number) && $page_number ne '') {
        $sub_query .= sprintf(' limit %s offset(%s) ', $LOCAL_CONFIG->{PAGE_STEP}, ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    }
    #select * from casp13.targets where (release_date + interval '17 hours' + interval '47 minutes') <=  now() 
    my $query = sprintf("select id, name, release_date from casp13.targets where (release_date + interval '%s hours\' + interval '%s minutes') <= now() and is_refinement=0 and (name similar to \'[HOT][0-9]%s\') order by release_date DESC, name DESC %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, '%', $sub_query);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "STATUS <br> $query<br>";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {                
        my $index = 1;
        while(my($target_id, $target_name, $release_date ) = $sth->fetchrow_array()) {
            push(@result, {
                    ID => $target_id,
                    NAME => $target_name,
                }
            );
            $index++;
        }        
    }    
    return @result;
}


sub is_some_public_targets_for_page {
    my ($self, $page_number) = @_;
    my $sub_query = '';
    if(defined($page_number) && $page_number ne '') {
        $sub_query .= sprintf(' limit %s offset(%s) ', $LOCAL_CONFIG->{PAGE_STEP}, ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    }    
    
    my $query = sprintf("select count(*) from (select * from casp13.targets where (release_date + interval '%s hours\' + interval '%s minutes') <= now() and is_refinement=0 and (name similar to \'[HOT][0-9]%s\') offset(%s)) as t",
    $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES},'%', ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "STATUS <br> $query<br>";
    
    my $sth = $self->{_database}->query($query);
    my $targets_count =0;
    if(defined($sth) && ($sth->rows() > 0)) {
        $targets_count = $sth->fetchrow_array();
    }    
    
    return $targets_count;
}


sub info_group_list {
    my ($self, $page_number) = @_;
    my $sub_query = '';
    if(defined($page_number) && $page_number ne '') {
        $sub_query .= sprintf(' limit %s offset(%s) ', $LOCAL_CONFIG->{PAGE_STEP}, ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    } 
    
    my @result = ();
    #my $query = sprintf("select DISTINCT gname from casp13.status_log order by gname DESC limit 41");
    my $query = sprintf("select id, name from casp13.groups where type>0 and type<3 order by name ASC %s", $sub_query);
    my $sth = $self->{_database}->query($query);

    #my $dumper = new Dumpvalue;
    #$dumper->dumpValue($sth);
    
    if(defined($sth) && ($sth->rows() > 0)) {        
        my $index = 1;
        my $header_step = 26;
        my $need_header = 0;        
        my $header_place = $header_step;
        
        while(my($group_id, $group_name) = $sth->fetchrow_array()) {
            
            if($index == $header_place) {
                $header_place = $header_place + $header_step;
                $need_header = 1;
            }        
            push(@result, {
                    ID => $group_id,
                    NAME => $group_name,
                    INDEX => $index,
                    WITH_HEADER => $need_header
                }
            );
            $index++;
            $need_header = 0;
        }        
    }    
    return @result;
}


sub info_all_group_list {
    my ($self) = @_;
    my @result = ();
    my $query = sprintf("select id, name, code from casp13.groups order by name ASC ");
    my $sth = $self->{_database}->query($query);

    #my $dumper = new Dumpvalue;
    #$dumper->dumpValue($sth);
    
    if(defined($sth) && ($sth->rows() > 0)) {        
        my $index = 1;
        while(my($group_id, $group_name, $code) = $sth->fetchrow_array()) {

            push(@result, {
                    ID => $group_id,
                    NAME => $group_name,
		    CODE => $code,
                    INDEX => $index,
                }
            );
            $index++;
        }
    }
    return @result;
}


sub info_status {
    my ($self, $group, $page_number) = @_;
    my $sub_query = '';
    if(defined($page_number) && $page_number ne '') {
        $sub_query .= sprintf(' limit %s offset(%s) ', $LOCAL_CONFIG->{PAGE_STEP}, ($page_number * $LOCAL_CONFIG->{PAGE_STEP}));
    }
    my @result = ();
    my $query = sprintf("select group_id, target_id, accepted, rejected, accepted_other, rejected_other, accepted_qa, rejected_qa, t_r.name
        from casp13.predictions_status p_s JOIN casp13.targets t_r ON (t_r.id = p_s.target_id) 
        where p_s.group_id='%s' and (t_r.release_date + interval '%s hours\' + interval '%s minutes') <= now() and is_refinement=0 and (t_r.name similar to \'[HOT][0-9]%s\') order by t_r.release_date DESC, t_r.name DESC %s",
        $group, $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES},'%', $sub_query);
    my $sth = $self->{_database}->query($query);   
    
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "STATUS <br> $query<br>";
    
    
    if(defined($sth) && ($sth->rows() > 0)) {                
        my $index = 1;
        while(my($group_id, $target_id, $accepted, $rejected, $accepted_other, $rejected_other, $accepted_qa, $rejected_qa, $target_name ) = $sth->fetchrow_array()) {
            push(@result, {
                    GROUP_ID => $group_id,       
                    TARGET_ID => $target_id,
                    ACCEPTED  => $accepted,
                    REJECTED => $rejected,                    
                    ACCEPTED_OTHER  => $accepted_other,
                    REJECTED_OTHER => $rejected_other,
                    ACCEPTED_QA  => $accepted_qa,
                    REJECTED_QA => $rejected_qa,
                    TARGET_NAME => $target_name,
                }
            );
            $index++;
        }        
    }    
    return @result; 
}


sub info_prediction_log {
    my ($self, $id) = @_;
    my %result = ( ID => '',
                   DATE => '',
                   GROUP_ID => '',
                   GROUP_NAME => '',                    
                   TARGET_ID => '',
                   TARGET_NAME => '',                    
                   MODEL => '',
                   PFRMAT => '',                    
                   STATUS => '',
                   RECIVED_FROM => '',
                   FILE_NAME => '',                    
                   CASE_ID => '',
                   RETURN => '',
                   INDEX => '',                    
                   PREDICTOR_ID => '',                    
                   SUBMISSION_TYPE => ''                  
                 );
    
    my $query = sprintf("SELECT id, date, group_id, group_name, target_id, target_name, model, pfrmat, status, recived_from, file_name, case_id, return, predictor_id, submission_type
                        FROM casp13.predictions_log WHERE id = %s ", $id);
    my $sth = $self->{_database}->query($query);   
        
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "STATUS <br> $query<br>";
        
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{ID}, $result{DATE}, $result{GROUP_ID}, $result{GROUP_NAME}, $result{TARGET_ID}, $result{TARGET_NAME}, 
        $result{MODEL}, $result{PFRMAT}, $result{STATUS}, $result{RECIVED_FROM}, $result{FILE_NAME},
        $result{CASE_ID}, $result{RETURN}, $result{PREDICTOR_ID}, $result{SUBMISSION_TYPE}) = $sth->fetchrow_array();        
        $result{DATE} = substr($result{DATE}, 0, 10);
    }    
    
    return %result; 
}



sub reset_predictions_from_log {
    my ($self) = @_;
    my $results = 0;
    my @predictions = $self->all_predictions_log("","", "","","",0,"accepted"); 
    my $predictions_manager = new PredictionsManager();
    
    for(my $p_count = 0; $p_count < scalar(@predictions); $p_count++) {
       #$predictions[$p_count]->{SUBMISSION_TYPE},
       $predictions_manager->add($predictions[$p_count]->{RECIVED_FROM}, 'email',
       $predictions[$p_count]->{PFRMAT}, $predictions[$p_count]->{TARGET_NAME}, $predictions[$p_count]->{GROUP_ID},
       $predictions[$p_count]->{MODEL}, $predictions[$p_count]->{PREDICTOR_ID});
    }
    #if($predictions_manager->add(
    #                             $self->{_email}, $self->{_type},
    #                             $self->{_pfrmat}, $self->{_target},
    #                             $self->{_groups_id}, $model_number, $predictor_id)) {
    return $results;
}


sub all_predictions_log {
    my ($self, $field, $order, $group_id, $target_id, $case_id, $prediction_type, $status, $date_from, $date_to, $group_name, $target_name ) = @_;
    
    #$group_id = (!defined($group_id) || ($group_id eq '')) ? -1 : $group_id;
    #$target_id = (!defined($target_id) || ($target_id eq '')) ? -1 : $target_id;
    #$case_id = (!defined($case_id) || ($case_id eq '')) ? -1 : $case_id;
    
    $field = (!defined($field) || ($field eq '')) ? 'date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
	
    my $sub_query = '';
    
    if(defined($case_id) && $case_id ne '') {
        $sub_query .= sprintf(' AND (case_id like \'%s\%\')', $case_id);
    } else {
    
        if(defined($group_id) && $group_id ne '') {
            $sub_query .= sprintf(' AND ((group_id = %d)) ', $group_id, $group_id);
        }
        if(defined($group_name) && $group_name ne '') {
	    my $group_name_for_search = "\%" . (lc $group_name) . "\%";
            $sub_query .= sprintf(' AND (lower(group_name) like \'%s\')', $group_name_for_search);
        }        
        if(defined($target_id) && $target_id ne '') {
            $sub_query .= sprintf(' AND (target_id = %d)', $target_id);
        }
	if(defined($target_name) && $target_name ne '') {
            $sub_query .= sprintf(' AND (target_name = \'%s\')', $target_name);
        }
        if(defined($case_id) && $case_id ne '') {
            $sub_query .= sprintf(' AND (case_id like \'%s\%\')', $case_id);
        }    
        if(defined($status) && $status ne '') {
            $sub_query .= sprintf(' AND (status = \'%s\')', $status);
        }    
        
        if(defined($prediction_type) && $prediction_type > 0 ) {
            if($prediction_type == 1) {
                # 3D predictions - TS and AL
                $sub_query .= sprintf(' AND (pfrmat=\'TS\' or pfrmat=\'AL\')');
            } else {
                # OTHER predictions - OTHERS=DP+DR+RR+FN+QA
                $sub_query .= sprintf(' AND (pfrmat<>\'TS\' and pfrmat<>\'AL\')');
            }        
        }
        
        ############# DATE
        
        if(defined($date_from) && $date_from ne '') {
            $sub_query .= sprintf(' AND (date >= \'%s\')', $date_from);
        }
        
        if(defined($date_to) && $date_to ne '') {
            $sub_query .= sprintf(' AND (date <= \'%s\')', $date_to);
        }
    
    }
    
    
    #$server_date->ymd
    
    ############# end 
    
    
    
    my @predictions_log = ();

    my $query = sprintf("SELECT id, date, group_id, group_name, target_id, target_name, model, pfrmat, status, recived_from, file_name, case_id, return, predictor_id, submission_type FROM casp13.predictions_log WHERE (1 = 1) %s ORDER BY %s %s", $sub_query, $field, $order);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
        
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        #my $groups_manager = new GroupsManager();
        #my $predictors_manager = new PredictorsManager();
        #my $predictions_manager = new PredictionsManager();
        #my $accounts_manager = new Core::Account();
        
        while(my($id, $date, $group_id, $group_name, $target_id, $target_name, $model, $pfrmat, $status, $recived_from, $file_name, $case_id, $return, $predictor_id, $submission_type) = $sth->fetchrow_array()) {
            
            my $color = "";
            if ($model ne 0 && $status eq 'accepted') {
                $color= "green";
            } elsif ($model ne 0 && $status eq 'rejected') {
                $color= "red";
            } elsif ($model eq 0 && $status eq 'rejected') {
                $color= "blue";
            }
            my $file_type = "";
            if ($status eq 'accepted') {
                $file_type= "accepted";
            } elsif ($status eq 'rejected') {
                $file_type= "submited";
            }
            
            my $file_recived = $file_name;
            
            if ($file_recived =~ /(\S*.prediction)_\s*/) {
               $file_recived = $1; 
            }
            
            push(@predictions_log, {
                    ID => $id,
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                    GROUP_ID => $group_id,
                    GROUP_NAME => $group_name,
                    
                    TARGET_ID => $target_id,
                    TARGET_NAME => $target_name,
                    
                    MODEL => $model,
                    PFRMAT => $pfrmat,
                    
                    STATUS => $status,
                    RECIVED_FROM => $recived_from,
                    FILE_NAME => $file_name,
                    FILE_RECIVED => $file_recived,
                    CASE_ID => $case_id,
                    RETURN => $return,
                    INDEX => $index,
                    COLOR => $color,
                    FILE_TYPE => $file_type,
                    PREDICTOR_ID => $predictor_id,                    
                    SUBMISSION_TYPE => $submission_type
                }
            );
            $index++;
        }
    }
    
    return @predictions_log;
}


sub accepted_predictions_log {
    my ($self, $field, $order, $group_id, $target_id, $prediction_type) = @_;
    
    $field = (!defined($field) || ($field eq '')) ? 'date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
	
    my $sub_query = ' AND (gr.type > 0) AND (gr.type < 3) '; # server groupsi only #BM

    if(defined($group_id) && $group_id ne '') {
        $sub_query .= sprintf(' AND (gr.id = %d)', $group_id);
    }
    if(defined($target_id) && $target_id ne '') {
        $sub_query .= sprintf(' AND (tr.id = %d)', $target_id);
    }    
    
    if(defined($prediction_type) && $prediction_type ne '' ) {
        if($prediction_type eq '3d') {
            # 3D predictions - TS and AL
            $sub_query .= sprintf(' AND (pr.pfrmat=\'TS\' or pr.pfrmat=\'AL\')');
        } elsif ($prediction_type eq 'other') {
            # OTHER predictions - OTHERS=DP+DR+RR+FN+
            $sub_query .= sprintf(' AND (pr.pfrmat<>\'TS\' and pr.pfrmat<>\'AL\' and pr.pfrmat<>\'QA\')');
        } elsif ($prediction_type eq 'qa') {
            # OTHER predictions - OTHERS=DP+DR+RR+FN+
            $sub_query .= sprintf(' AND pr.pfrmat =\'QA\' ');
        }        
    }
    
    my @predictions_log = ();

    my $query = sprintf("SELECT gr.name, gr.type, pr.target, pr.pfrmat, pr.model, pr.date, pr.id, tr.server_expiration_date
    FROM casp13.predictions pr JOIN casp13.groups gr ON (gr.id = pr.groups_id) JOIN casp13.targets tr ON (tr.name = pr.target)
    WHERE (1 = 1) %s ORDER BY %s %s", $sub_query, $field, $order);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
        
    my $sth = $self->{_database}->query($query);
    my $target_manager = new TargetsManager();
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;        
        while(my($group_name, $group_type, $target_name, $pfrmat, $model, $date, $id, $server_expiration_date) = $sth->fetchrow_array()) {            
            push(@predictions_log, {
                GROUP_NAME => $group_name,
		IS_SERVER_GROUP => (($group_type > 0 && $group_type < 3) ? 1 : 0),
                TARGET_NAME => $target_name,
                PFRMAT => $pfrmat,
                MODEL => $model,
                DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                ID => $id,
                IS_SERVER_TARGET_EXPIRED => $target_manager->is_date_expired($server_expiration_date),
                INDEX => $index                    
                }
            );
            $index++;
        }
    }
    
    return @predictions_log;
}


sub update_predictions_status {
    my ($self, $group_id, $target_id, $accepted, $rejected, $accepted_other, $rejected_other, $accepted_qa, $rejected_qa) = @_;
    my $result = 0;    
    my $query = sprintf("UPDATE casp13.predictions_status SET accepted=%d, rejected=%d, accepted_other=%d, rejected_other=%d, accepted_qa=%d, rejected_qa=%d WHERE (group_id=%d and target_id=%d)"
                        , $accepted, $rejected, $accepted_other, $rejected_other, $accepted_qa, $rejected_qa, $group_id, $target_id);    
    my $sth = $self->{_database}->query($query);    
    if(defined($sth)) {
        $result = 1;    
    }
    return $result;
}

sub update_calculated_status {
    my ($self, $prediction_log_id, $value_to_update) = @_;
    my $result = 0;    
    my $query = sprintf("UPDATE casp13.predictions_log SET calculated=%d WHERE id=%d "
                        , $value_to_update, $prediction_log_id);    
    my $sth = $self->{_database}->query($query);    
    if(defined($sth)) {
        $result = 1;    
    }
    return $result;
}


sub get_status_color {
    my ($self, $accepted, $rejected) = @_;
    my $background = "";    
    
    #all is rejected - red
    if ($rejected > 0 && $accepted <1) {
        $background = "red";
    }
    #all is accepted - #00FF00 (green)
    if ($rejected < 1 && $accepted > 0) {
        $background = "#00FF00";
    }
    #some rejected - blue
    if ($rejected > 0 && $accepted > 0  ) {
        $background = "blue";
    }
    #$predictions_count
    return $background;
}


sub update_targets_and_groups_list {
    my($self) = @_;
    my $results = 0;
    #0 Check new target and new groups
    
    my @group_list = $self->info_group_list();
    my @target_list = $self->info_public_target_list();
    #INSERT DEFAULT VALUES IF NEW TARGET OR SERVER GROUP EXIST
    for(my $g_count = 0; $g_count < scalar(@group_list); $g_count++) {
        for(my $t_count = 0; $t_count < scalar(@target_list); $t_count++) {
            my $query = sprintf("SELECT count(*) FROM casp13.predictions_status WHERE group_id=%d and target_id=%d", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});            
            my $sth = $self->{_database}->query($query);                        
            my $count = 0;
            if(defined($sth) && ($sth->rows() > 0)) {
                $count = $sth->fetchrow_array();
            }
            if ($count < 1) {                
                my $query = sprintf("INSERT INTO casp13.predictions_status
                (group_id, group_name, target_id, target_name, accepted, rejected, accepted_other, rejected_other, accepted_qa, rejected_qa)
                VALUES (%d, '%s', %d, '%s', %d, %d, %d, %d, %d, %d)",                        
                $group_list[$g_count]->{ID}, $group_list[$g_count]->{NAME}, $target_list[$t_count]->{ID}, $target_list[$t_count]->{NAME}, 0, 0, 0, 0, 0, 0);                
                my $sth = $self->{_database}->query($query);
                $results = 1;
            }
        }
    }
    #print "Group and targets list are updated \n";
    return $results;


}



sub recalculate_predictions_status_page {
    my ($self) = @_;
    my $results = 0;
    
    
    
    
    
    
    #1 Do select from prediction_status_log for all wher recalculate status is 0 and group_id <> 0
    my @predictions_log = ();
    my $query = sprintf("SELECT id, group_id, group_name, target_id, target_name, date FROM casp13.predictions_log
                        WHERE (1 = 1) and group_id <> 0 and calculated = 0  ORDER BY date");
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    print "Info $query /n \n";  
        
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;        
        while(my($id, $group_id, $group_name, $target_id, $target_name, $date) = $sth->fetchrow_array()) {          
            
            push(@predictions_log, {
                    ID => $id,
                    GROUP_ID => $group_id,
                    GROUP_NAME => $group_name,                    
                    TARGET_ID => $target_id,
                    TARGET_NAME => $target_name,
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-')            
                }
            );
            $index++;
        }
    }    
    
    #2 Calculate prediction just for new submissions
    for(my $count = 0; $count < scalar(@predictions_log); $count++) {
        print $predictions_log[$count]->{GROUP_ID} . "   ---   "   . $predictions_log[$count]->{TARGET_NAME} . "   ---   "   . $predictions_log[$count]->{TARGET_ID};
        $self->calculate_satus($predictions_log[$count]->{GROUP_ID}, $predictions_log[$count]->{TARGET_NAME}, $predictions_log[$count]->{TARGET_ID});
    #3 Update calculate status
        $self->update_calculated_status($predictions_log[$count]->{ID}, 1);
    }
    
    
    
    return $results;
}

sub calculate_satus {
    my ($self, $group_id, $target_name, $target_id) = @_;
    my $results = 0;
    
    my $predictions_count = 0;
    my $predictions_count_other = 0;
    my $predictions_count_qa = 0;
    my $rejected_count = 0;
    my $rejected_count_other = 0;
    my $rejected_count_qa = 0;
    
    #$target_list[$t_count]->{NAME}
    #$group_list[$g_count]->{ID}
    
    my $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and (pfrmat='TS' or pfrmat='AL') ", $group_id, $target_name);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        $predictions_count = $sth->fetchrow_array();
    }
    
    
    #$query = sprintf("SELECT count(*) FROM casp13.predictions_log WHERE group_id=%d and target_id='%s' and (pfrmat='TS' or pfrmat='AL') and status='rejected' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
    $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and pr_l.model <> 0 and (pr_l.pfrmat='TS' or pr_l.pfrmat='AL') and pr_l.status='rejected'
                    and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_id, $target_id);
    
    
    $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        $rejected_count = $sth->fetchrow_array();
    }
    
    
    #####OTHER  
    $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and (pfrmat<>'TS' and pfrmat<>'AL' and pfrmat<>'QA') ", $group_id, $target_name);            
    $sth = $self->{_database}->query($query);
    #print $query."\n";
    
    if(defined($sth) && ($sth->rows() > 0)) {
        $predictions_count_other = $sth->fetchrow_array();
    }
    #$query = sprintf("SELECT count(*) FROM casp13.predictions_log WHERE group_id=%d and target_id='%s' and (pfrmat<>'TS' and pfrmat<>'AL') and status='rejected' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
    $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and (pr_l.pfrmat<>'TS' and pr_l.pfrmat<>'AL' and pr_l.pfrmat<>'QA') and pr_l.model <> 0 and pr_l.status='rejected'
                    and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_id, $target_id);
    $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        $rejected_count_other = $sth->fetchrow_array();
    }           
    ##### end
    
    
    #####QA
    $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and pfrmat='QA' ", $group_id, $target_name);            
    $sth = $self->{_database}->query($query);
    
    
    if(defined($sth) && ($sth->rows() > 0)) {
        $predictions_count_qa = $sth->fetchrow_array();
    }
    
    $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and pr_l.pfrmat='QA' and pr_l.status='rejected' and pr_l.model <> 0
                    and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_id, $target_id);
    
    
    $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        $rejected_count_qa = $sth->fetchrow_array();
    }
    ###### end
    
    
    #$self->update_predictions_status($group_list[$g_count]->{ID}, $target_list[$t_count]->{ID}, $predictions_count, $status, $rejected_count, 0);
    $self->update_predictions_status($group_id, $target_id, $predictions_count, $rejected_count, $predictions_count_other, $rejected_count_other, $predictions_count_qa, $rejected_count_qa);        

    
    return $results;    

} 


sub regenerate_predictions_status {
    my ($self) = @_;
    my $results = 0;
    
    my @group_list = $self->info_group_list();
    my @target_list = $self->info_public_target_list();
    
    #INSERT DEFAULT VALUES IF NEW TARGET OR SERVER GROUP EXIST
    for(my $g_count = 0; $g_count < scalar(@group_list); $g_count++) {
        for(my $t_count = 0; $t_count < scalar(@target_list); $t_count++) {
            my $query = sprintf("SELECT count(*) FROM casp13.predictions_status WHERE group_id=%d and target_id=%d", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            
            my $sth = $self->{_database}->query($query);
                        
            my $count = 0;
            if(defined($sth) && ($sth->rows() > 0)) {
                $count = $sth->fetchrow_array();
            }
            if ($count < 1) {                
                my $query = sprintf("INSERT INTO casp13.predictions_status
                (group_id, group_name, target_id, target_name, accepted, rejected, accepted_other, rejected_other, accepted_qa, rejected_qa)
                VALUES (%d, '%s', %d, '%s', %d, %d, %d, %d, %d, %d)",                        
                $group_list[$g_count]->{ID}, $group_list[$g_count]->{NAME}, $target_list[$t_count]->{ID}, $target_list[$t_count]->{NAME}, 0, 0, 0, 0, 0, 0);                
                my $sth = $self->{_database}->query($query);            
            }
            
            
            my $predictions_count = 0;
            my $predictions_count_other = 0;
            my $predictions_count_qa = 0;
            my $rejected_count = 0;
            my $rejected_count_other = 0;
            my $rejected_count_qa = 0;
            
            $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and (pfrmat='TS' or pfrmat='AL') ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{NAME});
            #$query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and (pfrmat='TS' or pfrmat='AL') and status='accepted' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{NAME});
            $sth = $self->{_database}->query($query);
            #print $query."\n";
            
            if(defined($sth) && ($sth->rows() > 0)) {
                $predictions_count = $sth->fetchrow_array();
            }
            
            
            #$query = sprintf("SELECT count(*) FROM casp13.predictions_log WHERE group_id=%d and target_id='%s' and (pfrmat='TS' or pfrmat='AL') and status='rejected' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and (pr_l.pfrmat='TS' or pr_l.pfrmat='AL') and pr_l.status='rejected' and pr_l.model !<> 0
                            and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            
            
            $sth = $self->{_database}->query($query);
            if(defined($sth) && ($sth->rows() > 0)) {
                $rejected_count = $sth->fetchrow_array();
            }
            
            
            #####OTHER  
            $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and (pfrmat<>'TS' and pfrmat<>'AL' and pfrmat<>'QA') ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{NAME});            
            $sth = $self->{_database}->query($query);
            #print $query."\n";
            
            if(defined($sth) && ($sth->rows() > 0)) {
                $predictions_count_other = $sth->fetchrow_array();
            }
            #$query = sprintf("SELECT count(*) FROM casp13.predictions_log WHERE group_id=%d and target_id='%s' and (pfrmat<>'TS' and pfrmat<>'AL') and status='rejected' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and (pr_l.pfrmat<>'TS' and pr_l.pfrmat<>'AL' and pr_l.pfrmat<>'QA') and pr_l.status='rejected' and pr_l.model !<> 0
                            and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            $sth = $self->{_database}->query($query);
            if(defined($sth) && ($sth->rows() > 0)) {
                $rejected_count_other = $sth->fetchrow_array();
            }           
            ##### end
            
            
            #####QA
            $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE groups_id=%d and target='%s' and pfrmat='QA' ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{NAME});            
            $sth = $self->{_database}->query($query);
            
            
            if(defined($sth) && ($sth->rows() > 0)) {
                $predictions_count_qa = $sth->fetchrow_array();
            }
            
            $query = sprintf("SELECT count(*) FROM casp13.predictions_log pr_l WHERE pr_l.group_id=%s and pr_l.target_id=%s and pr_l.pfrmat='QA' and pr_l.status='rejected' and pr_l.model !<> 0
                            and not exists (Select * from casp13.predictions pr where pr_l.target_name = pr.target and  pr_l.pfrmat = pr.pfrmat and pr_l.model = pr.model and pr_l.group_id = pr.groups_id limit 1) ", $group_list[$g_count]->{ID}, $target_list[$t_count]->{ID});
            
            
            $sth = $self->{_database}->query($query);
            if(defined($sth) && ($sth->rows() > 0)) {
                $rejected_count_qa = $sth->fetchrow_array();
            }
            ###### end
            
            
            #$self->update_predictions_status($group_list[$g_count]->{ID}, $target_list[$t_count]->{ID}, $predictions_count, $status, $rejected_count, 0);
            $self->update_predictions_status($group_list[$g_count]->{ID}, $target_list[$t_count]->{ID}, $predictions_count, $rejected_count, $predictions_count_other, $rejected_count_other, $predictions_count_qa, $rejected_count_qa);        
            
        }
    }
    
    
    return $results;    
}







##THIS FUNCTION WAS USED ONLY ONCE FOR TESTING
#sub info_status_copy {
#    my ($self, $group, $target) = @_;
#    my $result = 0;
#    print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#    print "<br>!!<br>";
#    
#    #select DISTINCT gname from casp13.status_table order by gname DESC limit 41
#    my $query = sprintf("SELECT COUNT(id) FROM casp13.status_log WHERE (gname = '%s' and target = '%s')", $group, $target);
#    print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#    print "Info $query<br>";    
#    my $sth = $self->{_database}->query($query);    
#    if(defined($sth) && ($sth->rows() > 0)) {
#        $result = $sth->fetchrow_array();        
#        my $query = sprintf("INSERT INTO casp13.status_table (group_name, target, status, count) VALUES ('%s', '%s', %d, %d)", $group, $target, 0,$result);
#        print "Info $query<br>";    
#        my $sth = $self->{_database}->query($query);
#    }
#    return $result;    
#}
sub add_status {
    
    my ($self, $target, $model, $pfrmat, $group_name, $status, $flag, $message_id) = @_;
    my $result = 0;
    
    my $query = sprintf("INSERT INTO casp13.status_table
                        (date, target, model, pfrmat, group_name, status, flag, message_id)
                        VALUES (%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                        
                        "now()", $target, $model, $pfrmat, $group_name, $status, $flag, $message_id);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "ADD <br> $query<br>";

#TODO
#ADD CODE TO CHECK IF STATUS IS ALREADY EXIST 

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

    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.status_table");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($status_id) = $sth->fetchrow_array();
    }
    
    if($status_id > 0) {
        my $query = sprintf("INSERT INTO casp13.status_log
                        (status_id, date, target, model, pfrmat, group_name, status, flag, message_id)
                        VALUES (%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                        $status_id, "now()", $target, $model, $pfrmat, $group_name, $status, $flag, $message_id);
        
        my $sth = $self->{_database}->query($query);
        
        if(defined($sth)) {
            my $query = sprintf("SELECT MAX(id) FROM casp13.status_log");
            my $sth = $self->{_database}->query($query);
            # add logger
            ($result) = $sth->fetchrow_array();
        }
    }
    return $result;
}

sub resubmit_prediction {
    my ($self, $prediction_log_id) = @_;
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "RESUBMIT: $prediction_log_id <br>";
    
    my %prediction_info = $self->info_prediction_log($prediction_log_id);
    
    #if(open(VERIFIER, sprintf("cd %s; ./%s %s_%s |", $VERIFICATORS_DIR, $verifier, $self->{_prediction_file}, $model_number))) {
    ###if($prediction_info{STATUS} ne 'accepted') {     
    ### 
    ###    if($prediction_info{FILE_NAME} =~ /^(\S+)\.prediction/) {
    ###       my $uid = $1;
    ### #      print sprintf("cd ../scripts; ./submissions.pl --file=%s/%s.message", $LOCAL_CONFIG->{MESSAGES_PREDICTIONS_DIR}, $uid);
    ###       system(sprintf("cd ../scripts; ./submissions.pl --file=%s/%s.message --skip=target_validation ", $LOCAL_CONFIG->{MESSAGES_PREDICTIONS_DIR}, $uid));
    ###    }
    ###}
    
 #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print sprintf("cd ../scripts; ./submissions.pl --file=%s/%s.message --skip=target_validation ", $LOCAL_CONFIG->{MESSAGES_PREDICTIONS_DIR}, $prediction_info{CASE_ID});
#print "<br>". $prediction_info{SUBMISSION_TYPE};
# 
# ID => '',
#                    DATE => '',
#                    GROUP_ID => '',
#                    GROUP_NAME => '',                    
#                    TARGET_ID => '',
#                    TARGET_NAME => '',                    
#                    MODEL => '',
#                    PFRMAT => '',                    
#                    STATUS => '',
#                    RECIVED_FROM => '',
#                    FILE_NAME => '',                    
#                    CASE_ID => '',
#                    RETURN => '',
#                    INDEX => '',                    
#                    PREDICTOR_ID => '',                    
#                    SUBMISSION_TYPE => ''                      
#TODO TO IMPROVE FOR 'webform'!!
#if ($prediction_info{SUBMISSION_TYPE} ne 'webform') {
	my $filename = sprintf("%s/%s.message", $LOCAL_CONFIG->{MESSAGES_PREDICTIONS_DIR}, $prediction_info{CASE_ID});
	if (-e $filename) {
		system(sprintf("cd ../scripts; ./submissions.pl --file=%s --skip=target_validation ", $filename));
	} else {
		$filename = sprintf("%s.prediction", $prediction_info{CASE_ID});
		system(sprintf("cd ../scripts; ./resubmissions.pl --file=%s --sender=%s --uid=%s --skip=target_validation ", $filename, $prediction_info{RECIVED_FROM}, $prediction_info{CASE_ID}));
	}
  
#} else {
	#TODO TO IMPROVE FOR 'webform'!! - done up ^ :-P M. Wojciechowski
#}
    #print "<br>DONE!!!<br>";
    return 1;        
}

#sub update {
#    my ($self, $id, $name, $code, $is_server, $url, $pin) = @_;
#    
#    my $result = 0;
#    
#    return $result if(! $self->exist($id));
#    
#    my $query = sprintf("UPDATE casp13.groups SET name = '%s', code = %d, is_server = %d, url = '%s', pin = '%s' WHERE (id = %d)", $name, $code, $is_server, $url, $pin, $id);
#    my $sth = $self->{_database}->query($query);
#    
#    if(defined($sth)) {
#        # add logger
#        $result = 1;
#    }
#    
#    return $result;
#}

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

my $status_manager = new StatusManager();

#$status_manager->regenerate_predictions_status();
#$status_manager->reset_predictions_from_log();
#$status_manager->update_targets_and_groups_list();
#$status_manager->recalculate_predictions_status_page();

1;