package AbstractsManager;

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;

my $abstracts_manager = undef;

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

sub get_new_model {
    my ($self) = @_;    
    my %model = (
########################### system        
	id => '',
	accounts_id =>'',
	file_name => '',
	submission_type => '',
	poster_presention => '',
	software_demonstration => '',
	date => ''	
    );
    return %model;
}
sub is_column {
    my ($self, $colunm_name) = @_;
    my $result = 0;
    if(	$colunm_name eq 'id' ||
	$colunm_name eq 'accounts_id' ||
	$colunm_name eq 'file_name' ||
	$colunm_name eq 'submission_type' ||
	$colunm_name eq 'poster_presention' ||
	$colunm_name eq 'software_demonstration' ||
	$colunm_name eq 'date'	
      ) {
	$result = 1;
    }
    return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;
    my $query = sprintf("SELECT COUNT(id) FROM casp13.abstracts WHERE (id = %s)", $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 ab.id FROM casp13.abstracts ab JOIN public.accounts ac ON (ab.accounts_id = ac.id) WHERE ab.accounts_id=\'%s\' limit 1", $model{accounts_id});
    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_column($key) == 1) {
		$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
	    }
	}
    }
    return $result if(! $self->exist( $model{accounts_id}));
    my $query = sprintf("UPDATE casp13.abstracts SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
#	print "------------------------UPDATE: $model{id} \n";

#print  $query .  "<br>";
    my $sth = $self->{_database}->query($query);    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add {
    my ($self, %model) = @_;
    my $result = 0;
    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_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.abstracts ( %s ) VALUES ( %s )", $column_names, $column_values);
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print  $query;

    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.abstracts");
        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.abstracts WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub get_abstracts {
    my ($self, $params) = @_;
#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#print "<br>" . $params->{field} . "<br>";

    my $field = $params->{field};
    my $order = $params->{order};	
    	
    $field = (!defined($field) || ($field eq '')) ? 'mr.date' : $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->{id}) && ($params->{id} ne '')) {
 	$sub_query .= sprintf(" and ab.id=\'%s\' ", $params->{id});	
    }
    

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

    my @results = ();

    my $query = sprintf("SELECT ab.id, ab.accounts_id, ab.file_name, ab.submission_type, ab.poster_presention, ab.software_demonstration, ab.date
	FROM public.accounts ac LEFT JOIN casp13.abstracts ab on (ab.accounts_id = ac.id)
	JOIN public.countries co on (co.id = ac.country_id)
	WHERE 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);
#print $query; 
    my $sth = $self->{_database}->query($query);
    #print $query;
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $accounts_id, $file_name, $submission_type, $poster_presention, $software_demonstration, $date) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,
		id => $id,
		accounts_id => $accounts_id,
		file_name => $file_name,
		submission_type =>$submission_type,
		poster_presention => $poster_presention,
		software_demonstration => $software_demonstration,
		date => $date
		}
	    );	
	    $index++;
        }
    }
    return @results;
}

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;