package PredictorsManager;

use strict;
use warnings;

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

use lib qw(Core);

use Account;
use GroupsManager;
use Database;

use Configuration;

my $predictors_manager = undef;

sub new {
    my ($class) = @_;
    
    return $predictors_manager if(defined($predictors_manager));
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})
    };
    
    $predictors_manager = bless $self, $class;
    return $predictors_manager;
}

sub get_id_by_email {
    my ($self, $email) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT id FROM casp13.groups WHERE (email = '%s')", $email);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub predictor_id {
    my ($self, $account_id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT id FROM casp13.predictors WHERE (accounts_id = %d)", $account_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub server_predictor_id {
    my ($self, $groups_id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT predictors_id FROM casp13.groups_predictors_relations WHERE (groups_id = %d)", $groups_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}


sub account_id {
    my ($self, $predictor_id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT accounts_id FROM casp13.predictors WHERE (id = %d)", $predictor_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

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

sub registered_groups {
	my ($self, $id, $type) = @_;
	
    $type = defined($type) ? scalar($type) : 0;
    
	my $result = 0;
	
	my $query = sprintf("SELECT COUNT(gpr.id) FROM casp13.groups_predictors_relations gpr JOIN casp13.groups g ON (gpr.groups_id = g.id) WHERE (gpr.predictors_id = %d) AND (gpr.roles_id = 1) AND (g.type = %d) LIMIT 1", $id, $type);
	my $sth = $self->{_database}->query($query);
	
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub allowed_group_registrations {
	my ($self, $id) = @_;
	
	my $result = 0;
	
	my $query = sprintf("SELECT allowed_groups_registration FROM casp13.predictors WHERE (id = %d) LIMIT 1", $id);
	my $sth = $self->{_database}->query($query);
	
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub add {
    my ($self, $account_id, $allowed_groups_registration) = @_;
    
    $allowed_groups_registration = defined($allowed_groups_registration) ? scalar($allowed_groups_registration) : 1;
    
    my $result = 0;
    
    my $query = sprintf("INSERT INTO casp13.predictors (accounts_id, allowed_groups_registration) VALUES (%d, %d)", $account_id, $allowed_groups_registration);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.predictors");
        my $sth = $self->{_database}->query($query);
        
        # add logger
        ($result) = $sth->fetchrow_array();
    } else {
		print "not defined";
	}
    
    return $result;
}

sub update {
    my ($self, $id, $allowed_groups_registration) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("UPDATE casp13.predictors SET allowed_groups_registration = %d WHERE (id = %d)", $allowed_groups_registration, $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.predictors WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}

sub increase_allowed_group {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    return $result if(! $self->exist($id));
    
    my $query = sprintf("UPDATE casp13.predictors SET allowed_groups_registration = allowed_groups_registration + 1 WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        # add logger
        $result = defined($sth) ? 1 : 0;
    }
    
    return $result;
}

sub predictors {
    my ($self, $field, $order) = @_;
    
    $field = (!defined($field) || ($field eq '')) ? 'pr.date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
	
	my @predictors = ();
    
 	my $query = sprintf("SELECT a.firstname, a.lastname, a.email, pr.id, pr.accounts_id, pr.allowed_groups_registration, pr.date FROM casp13.predictors pr JOIN accounts a ON (pr.accounts_id = a.id) ORDER BY %s %s", $field, $order);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        my $accounts_manager = new Core::Account();
        my $groups_manager = new GroupsManager();
        
        while(my($firstname, $lastname, $email, $id, $accounts_id, $allowed_groups_registration, $date) = $sth->fetchrow_array()) {
            my @groups = $groups_manager->groups($id);
            
            push(@predictors, {
                    ID => $id,
                    INDEX => $index,
                    NAME => sprintf("%s %s", $firstname, $lastname),
                    FIRSTNAME => $firstname,
                    LASTNAME => $lastname,
                    EMAIL => $email,
                    ALLOWED_GROUPS_REGISTRATION => $allowed_groups_registration,
                    GROUPS => [@groups],
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                }
            );
            $index++;
        }
    }
    
    return @predictors;
}

sub predictors_by_group {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};
    
    $field = (!defined($field) || ($field eq '')) ? 'pr.date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
	
    my @predictors = ();
    
    my $sub_query = "";
    
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
 	$sub_query .= sprintf(" and gpr.groups_id=\'%s\' ", $params->{groups_id});	
    }
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    
    my $query = sprintf("SELECT a.firstname, a.lastname, a.email, pr.id, pr.accounts_id, pr.allowed_groups_registration, pr.date
                        FROM casp13.predictors pr JOIN accounts a ON (pr.accounts_id = a.id)
                        JOIN casp13.groups_predictors_relations gpr ON (gpr.predictors_id = pr.id)
                        where 1=1 %s ORDER BY %s %s"
                        , $sub_query, $field, $order);
    
    my $sth = $self->{_database}->query($query);
    #print $query . "<br>";
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($firstname, $lastname, $email, $id, $accounts_id, $allowed_groups_registration, $date) = $sth->fetchrow_array()) {
            push(@predictors, {
                    ID => $id,
                    INDEX => $index,
                    NAME => sprintf("%s %s", $firstname, $lastname),
                    FIRSTNAME => $firstname,
                    LASTNAME => $lastname,
                    EMAIL => $email,
                    ALLOWED_GROUPS_REGISTRATION => $allowed_groups_registration,
                    
                    DATE => (defined($date) ? ((substr($date, 0, 10) . "<br>" . substr($date, 11, 8))) : '-'),
                }
            );
            $index++;
        }
    }
    
    return @predictors;
}



1;