package MembersManager;

use strict;
use warnings;

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

use lib qw(Core);

use Database;

use Configuration;

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 {
    my ($self, $group_id, $id) = @_;
    
    my $result = 0;
    
 	my $query = sprintf("INSERT INTO casp13.groups_predictors_relations (predictors_id, groups_id, roles_id) VALUES (%d, %d, %d)", $id, $group_id, 3);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.groups_predictors_relations");
        my $sth = $self->{_database}->query($query);
        
        # add logger
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

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

sub members {
    my ($self, $group_id) = @_;
    
    my @group_members = ();
    
    my $query = sprintf("SELECT a.id, p.id, gpr.id, a.firstname, a.lastname, a.email, r.name, r.id FROM casp13.groups_predictors_relations gpr JOIN casp13.groups g ON (gpr.groups_id = g.id) JOIN casp13.predictors p ON (gpr.predictors_id = p.id) JOIN accounts a ON (a.id = p.accounts_id) JOIN casp13.roles r ON (r.id = gpr.roles_id) WHERE (g.id = %d) ORDER BY r.id ASC", $group_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($accout_id, $predictor_id, $member_id, $firstname, $lastname, $email, $role, $role_id) = $sth->fetchrow_array()) {
	        push(@group_members, {
					INDEX => $index,
                	ACCOUNT_ID => $accout_id,
					ID => $predictor_id,
                	FIRSTNAME => $firstname,
                    LASTNAME => $lastname,
					EMAIL => $email,
					ROLE => $role,
					ROLE_ID => $role_id
				}
	    	);
	        $index++;
        }
    }
    
    return @group_members;
}


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

# leader or submitter member
sub is_submitter_member {
    my ($self, $group_id, $id) = @_;
    
    my $result = 0;
    
    my $role_id = $self->role_id($group_id, $id);
    
    if(($role_id == 1) || ($role_id == 2)) {
        $result = 1;
    }
    
    return $result;
}

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

sub is_leader {
	my ($self, $group_id, $predictor_id) = @_;
	
	my $result = 0;
	
	my $query = sprintf("SELECT r.id FROM casp13.groups_predictors_relations gpr JOIN casp13.groups g ON (gpr.groups_id = g.id) JOIN casp13.predictors p ON (gpr.predictors_id = p.id) JOIN casp13.roles r ON (gpr.roles_id = r.id) JOIN accounts a ON (a.id = p.accounts_id) WHERE (g.id = %d) AND (p.id = %d)", $group_id, $predictor_id);
	my $sth = $self->{_database}->query($query);
	
    if(defined($sth) && ($sth->rows() > 0)) {
        my ($role) = $sth->fetchrow_array();
		
		$result = ($role == 1) ? 1 : 0;
    }
    
    return $result;
}

sub delete {
	my($self, $group_id, $predictor_id) = @_;
	
	my $result = 0;
	
	my $query = sprintf("DELETE FROM casp13.groups_predictors_relations WHERE (groups_id = %d) AND (predictors_id = %d)", $group_id, $predictor_id);
	my $sth = $self->{_database}->query($query);
    
	if(defined($sth)) { $result = 1; }
	
	return $result;
}

sub set_submitter_permissions {
	my($self, $group_id, $predictor_id) = @_;

	my $result = 0;
	
	my $query = sprintf("UPDATE casp13.groups_predictors_relations SET roles_id = 2 WHERE (groups_id = %d) AND (predictors_id = %d) AND (roles_id = 3)", $group_id, $predictor_id);
	my $sth = $self->{_database}->query($query);
    
	if(defined($sth)) { $result = 1; }
	
	return $result;
}

sub remove_submitter_permissions {
	my($self, $group_id, $predictor_id) = @_;
	
	my $result = 0;
	
	my $query = sprintf("UPDATE casp13.groups_predictors_relations SET roles_id = 3 WHERE (groups_id = %d) AND (predictors_id = %d) AND (roles_id = 2)", $group_id, $predictor_id);
	my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) { $result = 1; }
	
	return $result;
}

1;