package TargetSequencesManager;

use strict;
use warnings;

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

use lib qw(Core);
use CGI::Carp qw(fatalsToBrowser);
use Database;

use PDBUtils;
use Configuration;

### METHODS (last update 05/25/2010 M. Wojciechowski):
#   add (adds sequence to submitted_sequences table)
#   target_sequences (returns all submitted sequences info containing 
#                    submitter info)
#   update (changes sequence data)
#   update2 (changes only comments and target name in sequence)
#   delete (removes submitted sequence)
#   sequence (returns full info about the sequence)
#   exist (returns 1 if the sequence with given id exists)
#   get_all_sgi_centers (returns all sgi centers registered in database)
#   get_sgi_center (returns sgi center by given id)
######################################################

my $target_sequences_manager = undef;

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

sub add {
    my ($self, $accounts_id, $protein_name, $organism_name, $number_of_aa, $sequence, $binding_site, $sgi_center_id, $sequence_family, $is_nmr, $has_map_or_spectra, $current_state_description, $chain_tracing_date, $release_date) = @_;
    
    my $result = 0;
    
    my $query = sprintf("INSERT INTO casp9.submitted_sequences (
                        accounts_id, protein_name, organism_name, number_of_aa, sequence, binding_site, sgi_center_id, sequence_family, is_nmr, has_map_or_spectra, current_state_description, chain_tracing_date, release_date)
                        VALUES (%d, '%s', '%s', %d, '%s', '%s', %d, '%s', %d, %d, '%s', '%s', '%s')",
                        $accounts_id, quotemeta($protein_name), quotemeta($organism_name), $number_of_aa, quotemeta($sequence), quotemeta($binding_site), $sgi_center_id, quotemeta($sequence_family), (($is_nmr eq 'YES') ? 1 : 0), (($has_map_or_spectra eq 'YES') ? 1 : 0), quotemeta($current_state_description), quotemeta($chain_tracing_date), quotemeta($release_date));
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp9.submitted_sequences");
        my $sth = $self->{_database}->query($query);
        
        # add logger
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub target_sequences {
    my ($self, $field, $order) = @_;

    my @sequences = ();
    
    $field = (!defined($field) || ($field eq '')) ? 'ss.date' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    
    my $query = sprintf("SELECT ss.id, ss.protein_name, ss.organism_name, ss.number_of_aa, ss.sequence, ss.binding_site, sc.name, ss.sequence_family, ss.is_nmr, ss.has_map_or_spectra, ss.current_state_description, ss.chain_tracing_date, ss.release_date, ss.comments, ss.target, ss.date, a.lastname, a.firstname, a.institution, sc.id FROM casp9.submitted_sequences ss JOIN public.accounts a ON (a.id = ss.accounts_id) LEFT JOIN casp9.sgi_center sc ON (ss.sgi_center_id = sc.id) ORDER BY %s %s", $field, $order);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $protein_name, $organism_name, $number_of_aa, $sequence, $binding_site, $sgi_center, $sequence_family, $is_nmr, $has_map_or_spectra, $current_state_description, $chain_tracing_date, $release_date, $comments, $target, $date, $lastname, $firstname, $institution, $sgi_center_id) = $sth->fetchrow_array()) {
            if($date =~ /(\d+)-(\d+)-(\d+)/) {
                $date = sprintf("%d-%d-%d", $1, $2, $3);
            }
            
            if($release_date =~ /(\d+)-(\d+)-(\d+)/) {
                $release_date = sprintf("%d-%d-%d", $1, $2, $3);
            }
            
            my $real_number_of_aa = sequence_length($sequence);
            
            push(@sequences, {
                    ID => $id,
                    INDEX => $index,
                    PROTEIN_NAME => $protein_name,
                    ORGANISM_NAME => $organism_name,
                    NUMBER_OF_AA => $number_of_aa,
                    REAL_NUMBER_OF_AA => $real_number_of_aa,
                    SEQUENCE => $sequence,
                    SGI_CENTER => $sgi_center,
                    BINDING_SITE => $binding_site,
                    SEQUENCE_FAMILY => $sequence_family,
                    IS_NMR => $is_nmr,
                    HAS_MAP_OR_SPECTRA => $has_map_or_spectra,
                    CURRENT_STATE_DESCRIPTION => $current_state_description,
                    CHAIN_TRACING_DATE => $chain_tracing_date,
                    RELEASE_DATE => $release_date,
                    COMMENTS => $comments,
                    TARGET => $target,
                    DATE => $date,
                    ACCOUNT_NAME => sprintf("%s %s", $lastname, $firstname),
                    INSTITUTION => $institution,
		    SGI_CENTER_ID => $sgi_center_id
            	}
            );
            $index++;
        }
    }
    
    return @sequences;
}

sub update {
    my ($self, $id, $protein_name, $organism_name, $number_of_aa, $sequence, $binding_site, $sgi_center_id, $sequence_family, $is_nmr, $has_map_or_spectra, $current_state_description, $chain_tracing_date, $release_date, $comments, $target) = @_;
    
    my $result = 0;
    
    my $query = sprintf("UPDATE casp9.submitted_sequences SET protein_name = '%s', organism_name = '%s', number_of_aa = %d, sequence = '%s', binding_site = '%s', sgi_center_id = %d, sequence_family = '%s', is_nmr = %d, has_map_or_spectra = %d, current_state_description = '%s', chain_tracing_date = '%s', release_date = '%s', comments = '%s', target = '%s' WHERE (id = %d)", $protein_name, $organism_name, $number_of_aa, $sequence, $binding_site, $sgi_center_id, $sequence_family, $is_nmr, $has_map_or_spectra, $current_state_description, $chain_tracing_date, $release_date, $comments, $target, $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub update2 {
    my ($self, $id, $comments, $target) = @_;
    
    my $result = 0;
    
    my $query = sprintf("UPDATE casp9.submitted_sequences SET comments = '%s', target = '%s' WHERE (id = %d)", $comments, $target, $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

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

sub exist {
    my ($self, $id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT COUNT(id) FROM casp9.submitted_sequences 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 get_all_sgi_centers {
    my ($self) = @_;
    my @result = ();
   
    my $query = sprintf("SELECT sc.id, sc.name, sc.full_name FROM casp9.sgi_center sc");
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($id, $name, $full_name) = $sth->fetchrow_array()) {
                       
            push(@result, {
                     ID => $id,
        	     NAME => $name,
	             FULL_NAME => $full_name
            	}
            );
        }       
    }
    
    return @result;
}

sub get_sgi_center {
	my ($self, $id) = @_;

	my %result = (
		ID => '',
		NAME => '',
		FULL_NAME => ''
	);
    
	return %result if(! $self->exist($id));
	my $query = sprintf("SELECT sc.id, sc.name, sc.full_name FROM casp9.sgi_center sc WHERE sc.id = %d", $id);
	
	my $sth = $self->{_database}->query($query);
    
	if(defined($sth) && ($sth->rows() > 0)) {
		(
			$result{ID},
			$result{NAME},
			$result{FULL_NAME}
		) = $sth->fetchrow_array();
        
		$result{REAL_NUMBER_OF_AA} = sequence_length($result{SEQUENCE});
	}
    
	return %result;
}

sub sequence {
    my ($self, $id) = @_;
    
    my %result = (
        ID => '',
        TARGET => '',
        PROTEIN_NAME => '',
        ORGANISM_NAME => '',
        NUMBER_OF_AA => '',
        REAL_NUMBER_OF_AA => '',
        SEQUENCE => '',
        BINDING_SITE => '',
        SGI_CENTER => '',
        SEQUENCE_FAMILY => '',
        IS_NMR => '',
        CURRENT_STATE_DESCRIPTION => '',
        HAS_MAP_OR_SPECTRA => '',
        CHAIN_TRACING_DATE => '',
        RELEASE_DATE => '',
        DATE => '',
        COMMENTS => '',
        ACCOUNTS_ID => '',
        TARGET => '',
	SGI_CENTER_ID => ''
    );
    
    return %result if(! $self->exist($id));
    
    my $query = sprintf("SELECT ss.id, ss.protein_name, ss.organism_name, ss.number_of_aa, ss.sequence, ss.binding_site, sc.name, ss.sequence_family, ss.is_nmr, ss.has_map_or_spectra, ss.current_state_description, ss.chain_tracing_date, ss.release_date, ss.date, ss.comments, ss.accounts_id, ss.target, sc.id FROM casp9.submitted_sequences ss LEFT JOIN casp9.sgi_center sc ON (ss.sgi_center_id = sc.id) WHERE ss.id = %d", $id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        (
        $result{ID},
        $result{PROTEIN_NAME},
        $result{ORGANISM_NAME},
        $result{NUMBER_OF_AA},
        $result{SEQUENCE},
        $result{BINDING_SITE},
        $result{SGI_CENTER},
        $result{SEQUENCE_FAMILY},
        $result{IS_NMR},
        $result{HAS_MAP_OR_SPECTRA},
        $result{CURRENT_STATE_DESCRIPTION},
        $result{CHAIN_TRACING_DATE},
        $result{RELEASE_DATE},
        $result{DATE},
        $result{COMMENTS},
        $result{ACCOUNTS_ID},
        $result{TARGET},
	$result{SGI_CENTER_ID}
        ) = $sth->fetchrow_array();
        
        $result{REAL_NUMBER_OF_AA} = sequence_length($result{SEQUENCE});
    }
    
    return %result;
}

1;