package MultimerResultsEmmManager;

use strict;
use warnings;

use DBI;

use lib qw(Core);

use Database;


use Configuration;
use LocalConfiguration;

my $results_manager = undef;

sub new {
    my ($class) = @_;
    return $results_manager if(defined($results_manager));
    my %model = (ID => '');

    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}),
        _model => \%model
    };

    $results_manager = bless $self, $class;
    return $results_manager;
}

sub get_new_model {
    my ($self) = @_;
    my %model = (
########################### system
	id => '',
	date => '',
########################## file name
	MODEL_NAME => '',
        TARGET => '',
        PFRMAT => '',
        CODE => '',
	map_emd_no => '', # emd number : emd_6422
	resol => '',
########################### TemPy
	env => '',
	mi => '',
	lap => '',
	ccc => '',
	ccc_per_chain => '',
	ccc_ov => '',
	mi_ov => '',
	smoc_d => '',
	smoc_d_per_chain => '',
	smoc => '',
	ccc_mask => '',
########################## PHENIX
	fsc => '',
	fsc_bins => '', 
	box_cc => '',
	box_cc_per_chain => '',
	cc_mask =>'',
	cc_volume => '',
	cc_peak => '',
######################### MODEL STATS from PHENIX output
	bond_rmsd => '',
	bond_max => '',
	bond_count => '',
	angle_rmsd => '',
	angle_max => '',
	angle_count => '',
	chiral_rmsd => '',
	chiral_max => '',
	chiral_count => '',
	planar_rmsd => '',
	planar_max => '',
	planar_count => '',
	dihedr_rmsd => '',
	dihedr_max => '',
	dihedr_count => '',
	mp_clash => '',
	mp_ram_out => '',
	mp_ram_fv => '',
	mp_rot_out => '',
	dev_bonds => '',
	dev_angles => '',
	dev_dists => '',
	dev_adps => '',
	resol_fsc05 => '',
	# emringer
	emringer => '',
	# model/map match
	n_close => '',
	n_far => '',
	ca_score => '',
	seq_match => ''
	
    );
    return %model;
}

sub is_results_table_column {
my ($self, $column_name) = @_;
my $result = 0;
if(	$column_name eq 'id' ||
	$column_name eq 'date' ||
	########################## file name
	$column_name eq 'MODEL_NAME' ||
	$column_name eq 'PFRMAT' ||
	$column_name eq 'CODE' ||
	$column_name eq 'TARGET' ||
	$column_name eq 'map_emd_no' ||
	$column_name eq 'resol' ||
	########################### results
	########################### TemPy
	$column_name eq 'env' ||
	$column_name eq 'mi' ||
	$column_name eq 'lap' ||
	$column_name eq 'ccc' ||
	$column_name eq 'ccc_per_chain' ||
	$column_name eq 'smoc_d' ||
        $column_name eq 'smoc_d_per_chain' ||
	$column_name eq 'smoc' ||
	$column_name eq 'ccc_mask' ||
	########################### Phenix
	$column_name eq 'fsc' ||
	$column_name eq 'fsc_bins' ||
        $column_name eq 'box_cc' ||
	$column_name eq 'cc_mask' ||
	$column_name eq 'cc_volume' ||
	$column_name eq 'cc_peak' ||
	$column_name eq 'ccc_ov' ||
	$column_name eq 'mi_ov' ||
	########################### model's stat from Phenix
	$column_name eq 'box_cc_per_chain' ||
	$column_name eq 'bond_rmsd' ||
        $column_name eq 'bond_max' ||
        $column_name eq 'bond_count' ||
        $column_name eq 'angle_rmsd' ||
        $column_name eq 'angle_max' ||
        $column_name eq 'angle_count' ||
        $column_name eq 'chiral_rmsd' ||
        $column_name eq 'chiral_max' ||
        $column_name eq 'chiral_count' ||
        $column_name eq 'planar_rmsd' ||
        $column_name eq 'planar_max' ||
        $column_name eq 'planar_count' ||
        $column_name eq 'dihedr_rmsd' ||
        $column_name eq 'dihedr_max' ||
        $column_name eq 'dihedr_count' ||
        $column_name eq 'mp_clash' ||
        $column_name eq 'mp_ram_out' ||
        $column_name eq 'mp_ram_fv' ||
        $column_name eq 'mp_rot_out' ||
        $column_name eq 'dev_bonds' ||
        $column_name eq 'dev_angles' ||
        $column_name eq 'dev_dists' ||
        $column_name eq 'dev_adps' ||
	$column_name eq 'resol_fsc05' ||
	# emringer
	$column_name eq 'emringer' ||
	# model/map match
	$column_name eq 'n_close' ||
	$column_name eq 'n_far' ||
	$column_name eq 'ca_score' ||
	$column_name eq 'seq_match' 
  ) {
	$result = 1;
	}
return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;

    my $query = sprintf("SELECT COUNT(id) FROM casp13.multimer_results_vs_emm 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 exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
#    if (!defined($model{resol}) || $model{resol} eq ''){
#	return $result;
#    }
    my $query = sprintf("SELECT re.id FROM casp13.multimer_results_vs_emm re ".
	"WHERE re.model_name=\'%s\' ", 
#	"WHERE re.model_name=\'%s\' AND re.map_emd_no=\'%s\' ",
	$model{MODEL_NAME}
#	$model{map_emd_no}
#	$model{resol}
	);

    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_results {
    my ($self, %model) = @_;

    my $result = 0;
    my $set_query = '';

    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || !defined($value) || $value eq '') {
		#print "NE---$key---------->$value<----------\n";
	}else{
		#list of columns which not updated
		if($self->is_results_table_column($key) == 1) {
			$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
		}
	}
    }

    return $result if(! $self->exist( $model{id}));
    my $query = sprintf("UPDATE casp13.multimer_results_vs_emm SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
	print "------------------------UPDATE: $model{id} \n";
	print $query . "\n";

    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_results_table_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.multimer_results_vs_emm ( %s ) VALUES ( %s ) RETURNING id", $column_names, $column_values);

    my $tmp = $query;

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        ($result) = $sth->fetchrow_array();
    }
	print "---------------------ADD: $result";
	if ($result == 0) {
	print "\n" . $tmp . "\n";
	}

    return $result;
}

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


sub get_em_results {
    my ($self, $param) = @_;
    my $where = '';
    if (defined($param->{_target})){
	$where = sprintf(" target=\'%s\' ", $param->{_target});
    }
    if (defined($param->{_resol})){
	if ($where ne '') {
		$where .= " AND ";
	}
        $where .= sprintf(" resol=%.1f ", $param->{_resol});
    }
    if (defined($param->{_map_emd_no})){
	if ($where ne '') {
                $where .= " AND ";
        }
	$where .= sprintf(" map_emd_no = \'%s\' ", $param->{_map_emd_no});
    }
    if (defined($param->{_model}) && $param->{_model} ne '' && $param->{_model} ne 'all'){
	if ($where ne '') {
                $where .= " AND ";
        }
	$where .= sprintf(" model_name = \'%s\'", $param->{_model});
    }
#    if (defined($param->{_methods})){
#	if ($where ne '') {
#                $where .= " AND ";
#        }
#	$where .= sprintf(" (m.modelmethods_id IS NULL OR m.modelmethods_id IN (%s)) ", $param->{_methods}); 
#    }
    if ($where ne ''){
	$where = "WHERE ".$where;
    }
    my $query = sprintf("SELECT re.model_name, 
			re.env, re.lap, re.mi, re.ccc, re.resol, 
				re.fsc, re.box_cc, re.resol_fsc05, re.emringer,
				re.n_close, re.n_far, re.ca_score, re.seq_match,
				re.cc_mask, re.cc_volume, re.cc_peak, 
				re.ccc_ov, re.mi_ov, re.smoc, re.smoc_d, re.ccc_mask, 
				g.eval_capri
			FROM casp13.multimer_results_vs_emm re 
			LEFT JOIN casp13.groups g ON re.code=g.code
			%s ORDER BY %s %s NULLS LAST", $where, 
	(defined($param->{field})? $param->{field} : 're.ccc'), (defined($param->{order})? $param->{order} : 'DESC')
    );
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
	my $index = 0;
        while(my($model_name, $env, $lap, $mi, $ccc, $resol, 
			#	$map_emd_no, 
			$fsc, $box_cc, $resol_fsc05, $emringer,
			$n_close, $n_far, $ca_score, $seq_match,
			$cc_mask, $cc_volume, $cc_peak, 
			$ccc_ov, $mi_ov, 
			$smoc, $smoc_d, 
			$ccc_mask,
			$eval_capri
				#, $method, $author
			) = $sth->fetchrow_array()){
 		push @results, {
			INDEX => ++$index,
			MODEL_NAME => $model_name,
			IS_REF_MODEL => ($model_name =~ m/EM\d{3,3}_\d/ ? 0 : 1),
			ENV => ((defined($env) && $env ne '') ? sprintf("%.5f", $env) : '-'), 
			LAP => ((defined($lap) && $lap ne '') ? sprintf("%.5f", $lap) : '-'), 
			MI => ((defined($mi) && $mi ne '') ? sprintf("%.5f", $mi) : '-'), 
			CCC => ((defined($ccc) && $ccc ne '') ? sprintf("%.5f", $ccc) : '-'), 
			RESOL => $resol,
			#MAP_EMD_NO => $map_emd_no,
			FSC => ((defined($fsc) && $fsc ne '') ? sprintf("%.5f", $fsc) : '-'),
			BOX_CC => ((defined($box_cc) && $box_cc ne '') ? sprintf("%.5f", $box_cc) : '-'),
			RESOL_FSC05 => ((defined($resol_fsc05) && $resol_fsc05 ne '') ? sprintf("%s", $resol_fsc05) : '-'),
			EMRINGER => ((defined($emringer) && $emringer ne '') ? sprintf("%.5f", $emringer) : '-'),
			N_CLOSE => ((defined($n_close) && $n_close ne '') ? sprintf("%d", $n_close) : '-'),
			N_FAR => ((defined($n_far) && $n_far ne '') ? sprintf("%d", $n_far) : '-'),
			CA_SCORE => ((defined($ca_score) && $ca_score ne '') ? sprintf("%.1f", $ca_score) : '-'),
			SEQ_MATCH => ((defined($seq_match) && $seq_match ne '') ? sprintf("%.1f", $seq_match) : '-'),
			CC_MASK => ((defined($cc_mask) && $cc_mask ne '') ? sprintf("%.5f", $cc_mask) : '-'),
			CC_VOLUME => ((defined($cc_volume) && $cc_volume ne '') ? sprintf("%.5f", $cc_volume) : '-'),
			CC_PEAK => ((defined($cc_peak) && $cc_peak ne '') ? sprintf("%.5f", $cc_peak) : '-'),
			CCC_OV => ((defined($ccc_ov) && $ccc_ov ne '') ? sprintf("%.5f", $ccc_ov) : '-'),
			MI_OV => ((defined($mi_ov) && $mi_ov ne '') ? sprintf("%.5f", $mi_ov) : '-'),
			SMOC => ((defined($smoc) && $smoc ne '') ? sprintf("%.5f", $smoc) : '-'),
			SMOC_D => ((defined($smoc_d) && $smoc_d ne '') ? sprintf("%.5f", $smoc_d) : '-'),
			CCC_MASK => ((defined($ccc_mask) && $ccc_mask ne '') ? sprintf("%.5f", $ccc_mask) : '-'),
			IS_CAPRI => (defined($eval_capri) ? $eval_capri : 0),
			IS_TARGET => ($model_name =~ m/\.pdb/ ? '1' : '0'),
			#METHOD => ((defined($method)) ? $method : ''),
			#AUTHOR => ((defined($author)) ? $author : ''),

		};
	}
    }
    return @results;
}

sub get_models_stat {
    my ($self, $param) = @_;
    my $where = '';
    if (defined($param->{_target})){
        $where = sprintf(" target=\'%s\' ", $param->{_target});
    }
    if (defined($param->{_resol})){
        if ($where ne '') {
                $where .= " AND ";
        }
        $where .= sprintf(" resol=%.1f ", $param->{_resol});
    }
    if (defined($param->{_map_emd_no})){
        if ($where ne '') {
                $where .= " AND ";
        }
        $where .= sprintf(" map_emd_no = \'%s\' ", $param->{_map_emd_no});
    }
#    if (defined($param->{_methods})){
#        if ($where ne '') {
#                $where .= " AND ";
#        }
#        $where .= sprintf(" (m.modelmethods_id IS NULL OR m.modelmethods_id IN (%s)) ", $param->{_methods});
#    }
    if ($where ne ''){
        $where = "WHERE ".$where;
    }
    my $query = sprintf("SELECT 
	model_name, 
	bond_rmsd,
        bond_max,
        bond_count,
        angle_rmsd,
        angle_max,
        angle_count,
        chiral_rmsd,
        chiral_max,
        chiral_count,
        planar_rmsd,
        planar_max,
        planar_count,
        dihedr_rmsd,
        dihedr_max,
        dihedr_count,
        mp_clash,
        mp_ram_out,
        mp_ram_fv,
        mp_rot_out,
--	mm.description AS method,
--	p.name AS author
        FROM casp13.multimer_results_vs_emm re
--	LEFT JOIN casp13.em_models m ON m.name::text=re.model_name::text
--	LEFT JOIN casp13.participants p ON p.gr_code=re.code
--      LEFT JOIN casp13.modelmethods mm ON mm.id=m.modelmethods_id
	%s ORDER BY %s %s ", $where,
        (defined($param->{field})? $param->{field} : 'model_name'), (defined($param->{order})? $param->{order} : 'ASC')
    );
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 0;
        while(my($model_name, 
	$bond_rmsd, 
	$bond_max,
        $bond_count,
        $angle_rmsd,
        $angle_max,
        $angle_count,
        $chiral_rmsd,
        $chiral_max,
        $chiral_count,
        $planar_rmsd,
        $planar_max,
        $planar_count,
        $dihedr_rmsd,
        $dihedr_max,
        $dihedr_count,
        $mp_clash,
        $mp_ram_out,
        $mp_ram_fv,
        $mp_rot_out
#	,$method,
#	$author
	) = $sth->fetchrow_array()){
                push @results, {
                        INDEX => ++$index,
                        MODEL_NAME => $model_name,
#			IS_REF_MODEL => ($model_name =~ m/EM\d{3,3}_\d/ ? 0 : 1),
			bond_rmsd => ((defined($bond_rmsd) && $bond_rmsd ne '') ? sprintf("%.3f", $bond_rmsd) : '-'),
			bond_max => ((defined($bond_max) && $bond_max ne '') ? sprintf("%.3f", $bond_max) : '-'),
			bond_count => ((defined($bond_count) && $bond_count ne '') ? sprintf("%d", $bond_count) : '-'),
			angle_rmsd => ((defined($angle_rmsd) && $angle_rmsd ne '') ? sprintf("%.3f", $angle_rmsd) : '-'),
		        angle_max => ((defined($angle_max) && $angle_max ne '') ? sprintf("%.3f", $angle_max) : '-'),
		        angle_count => ((defined($angle_count) && $angle_count ne '') ? sprintf("%d", $angle_count) : '-'),
		        chiral_rmsd =>((defined($chiral_rmsd) && $chiral_rmsd ne '') ? sprintf("%.3f", $chiral_rmsd) : '-'),
		        chiral_max => ((defined($chiral_max) && $chiral_max ne '') ? sprintf("%.3f", $chiral_max) : '-'),
		        chiral_count => ((defined($chiral_count) && $chiral_count ne '') ? sprintf("%d", $chiral_count) : '-'),
		        planar_rmsd => ((defined($planar_rmsd) && $planar_rmsd ne '') ? sprintf("%.3f", $planar_rmsd) : '-'),
		        planar_max => ((defined($planar_max) && $planar_max ne '') ? sprintf("%.3f", $planar_max) : '-'),
		        planar_count => ((defined($planar_count) && $planar_count ne '') ? sprintf("%d", $planar_count) : '-'),
		        dihedr_rmsd => ((defined($dihedr_rmsd) && $dihedr_rmsd ne '') ? sprintf("%.3f", $dihedr_rmsd) : '-'),
		        dihedr_max => ((defined($dihedr_max) && $dihedr_max ne '') ? sprintf("%.3f", $dihedr_max) : '-'),
		        dihedr_count => ((defined($dihedr_count) && $dihedr_count ne '') ? sprintf("%d", $dihedr_count) : '-'),
		        mp_clash => ((defined($mp_clash) && $mp_clash ne '') ? sprintf("%.3f", $mp_clash) : '-'),
		        mp_ram_out => ((defined($mp_ram_out) && $mp_ram_out ne '') ? sprintf("%.3f", $mp_ram_out) : '-'),
		        mp_ram_fv => ((defined($mp_ram_fv) && $mp_ram_fv ne '') ? sprintf("%.3f", $mp_ram_fv) : '-'),
		        mp_rot_out => ((defined($mp_rot_out) && $mp_rot_out ne '') ? sprintf("%.3f", $mp_rot_out) : '-'),
#			METHOD => ((defined($method)) ? $method : ''),
#                       AUTHOR => ((defined($author)) ? $author : ''),
                };
        }
    }
    return @results;
}

# get data for histogram
sub get_data_histogram {
    my ($self, $params) = @_;
    my $subquery = "";
    my $column = undef;
    if ( defined $params->{_model_name}) {
	$subquery = sprintf(" WHERE model_name='%s' ", $params->{_model_name});
    } else {
 	return undef;
    }
    if ( !defined $params->{_measure} ) {
	$params->{_measure} = 'bond';
    }
    if ($params->{_measure} =~ m/bond/i){
	$column = "dev_bonds";
    } elsif ($params->{_measure} =~ m/angle/i) {
	$column = "dev_angles";
    } elsif ($params->{_measure} =~ m/dist/i){
	$column = "dev_dists";
    } elsif ($params->{_measure} =~ m/adp/i) {
	$column = "dev_adps";
    }
    my $query = "SELECT $column FROM casp13.multimer_results_vs_emm $subquery LIMIT 1" ;
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
	if(my($bin_value) = $sth->fetchrow_array()){
                push @results, {
			MODEL_NAME => $params->{_model_name},
                        bin_value => $bin_value
                };
        }
    }
    return @results;
}

sub get_em_targets {
    my ($self, $params) = @_;
    my $query = " SELECT DISTINCT target
		  FROM casp13.multimer_results_vs_emm
		  WHERE target IS NOT NULL 
		  ORDER BY target ";
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($target) = $sth->fetchrow_array()){
                push @results, {
			_TARGET => $target,
		};
        }
    }
    return @results;
}

sub get_em_models {
    my ($self, $params) = @_;
    my $where = 'WHERE 1=1 ';
    if (defined($params->{_target})){
	$where .= sprintf(" AND target = \'%s\' ", $params->{_target});
    }

    my $query =  sprintf (" SELECT DISTINCT model_name, g.eval_capri FROM casp13.multimer_results_vs_emm re
	LEFT JOIN casp13.groups g ON re.code=g.code
			    %s 
        ORDER BY model_name", $where);
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($model_name, $eval_capri) = $sth->fetchrow_array()){
                push @results, {
                        MODEL_NAME => $model_name,
			IS_CAPRI => (defined($eval_capri) ? $eval_capri : 0),
			
                };
        }
    }
    return @results;
}

sub get_em_maps {
    my ($self, $params) = @_;
    my $where = " WHERE 1=1 ";
    if (defined($params->{_target}) && $params->{_target} ne '') {
	$where .= sprintf(" AND target = \'%s\' ", $params->{_target});
    }
    if (defined($params->{_model}) && $params->{_model} ne '') {
	$where .= sprintf(" AND model_name = \'%s\' ", $params->{_model});
    }
    my $query =  sprintf (" SELECT DISTINCT map_emd_no FROM casp13.multimer_results_vs_emm 
                            %s
        ORDER BY map_emd_no", $where);
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($emd_no) = $sth->fetchrow_array()){
                push @results, {
                        _MAP_EMD_NO => $emd_no,
                };
        }
    }
    return @results;
}



sub get_fsc_bins{
    my ($self, $param) = @_;
    if (! defined($param->{_target}) || $param->{_target} eq ''){
	return undef;
    }
    my $where = 'WHERE 1=1 ';
    if (defined($param->{_target})){
        $where .= sprintf(" AND target = \'%s\' ", $param->{_target});
    }
    if (defined($param->{_emd_no})){
        $where .= sprintf(" AND map_emd_no = \'%s\'", $param->{_emd_no});
    }
    if (defined($param->{_methods})){
        if ($where ne '') {
                $where .= " AND ";
        }
        $where .= sprintf(" (m.modelmethods_id IS NULL OR m.modelmethods_id IN (%s)) ", $param->{_methods});
    }

    my $query = sprintf("SELECT model_name, fsc_bins, resol, map_emd_no, target FROM casp13.multimer_results_vs_emm re
			LEFT JOIN casp13.em_models m ON m.name::text=re.model_name::text
			%s ", $where);
    my $sth = $self->{_database}->query($query);
    my @results = ();
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($model_name, $fsc_bins, $resol, $map_emd_no, $target) = $sth->fetchrow_array()){
                push @results, {
                        MODEL_NAME => $model_name,
                        FSC_BINS => $fsc_bins,
			RESOL => $resol,
			MAP_EMD_NO => $map_emd_no,
			TARGET => $target
                };
        }
    }
    return @results;
}

sub add_alignment{
    my ($self, $results_id, $chain, $alignment, $metric, $score) = @_;
    my $id = $self->exist_alignment($results_id, $chain);
    my $column = undef;
    my $column_score = undef;
    if ($metric eq 'phenix') {
	$column = 'alignment_phenix';
	$column_score = 'box_cc';
    } elsif ($metric eq 'tempy') {
	$column = 'alignment_tempy';
	$column_score = 'smoc';
    } elsif ($metric eq 'smoc_d') {
        $column = 'alignment_smoc_d';
        $column_score = 'smoc_d';
    } elsif ($metric eq 'emringer') {
	$column_score = 'emringer';
        $column = 'alignment_emringer';
    } else {
	# do nothing
	return;
    }
    if (!defined($id)) { # insert into
      my $query = sprintf("INSERT INTO casp13.multimer_results_vs_emm_alignment (multimer_results_vs_emm_id, chain, $column, $column_score) VALUES (%s, \'%s\', \'%s\', \'%s\')", $results_id, $chain, $alignment, $score);
      my $sth = $self->{_database}->query($query);
    } else { # update results
      my $query = sprintf("UPDATE casp13.multimer_results_vs_emm_alignment SET  $column=\'%s\', $column_score=\'%s\' WHERE id=%d", $alignment, $score, $id);
      my $sth = $self->{_database}->query($query);
    }
}

sub exist_alignment {
    my ($self, $results_id, $chain) = @_;
    my $query = sprintf("SELECT id FROM casp13.multimer_results_vs_emm_alignment WHERE multimer_results_vs_emm_id=%d AND chain=\'%s\'", $results_id, $chain);
    my $sth = $self->{_database}->query($query);
    my ($id) = $sth->fetchrow_array();
    if (!defined($id) || $id eq '' || $id <= 0) {
        return undef;
    }
    return $id;
}

sub get_alignment{
    my ($self, $param) = @_;
    my $column = undef;
    my $column_score = undef;
    if ($param->{_metric} eq 'phenix'){
	$column = 'alignment_phenix';
	$column_score = 'box_cc';
    } elsif ($param->{_metric} eq 'tempy') {
	$column = 'alignment_tempy';
	$column_score = 'smoc';
    } elsif ($param->{_metric} eq 'smoc_d') {
        $column = 'alignment_smoc_d';
        $column_score = 'smoc_d';
    } elsif ($param->{_metric} eq 'emringer') {
        $column = 'alignment_emringer';
	$column_score = 'emringer';
    }
    my @result = ();
    if ($param->{_model} ne 'all'){	
	my $query = sprintf(
		"SELECT re.model_name, re.target, al.chain, al.$column , g.eval_capri
		FROM casp13.multimer_results_vs_emm_alignment al JOIN casp13.multimer_results_vs_emm re ON re.id=al.multimer_results_vs_emm_id
		LEFT JOIN casp13.groups g ON g.code=re.code
		WHERE re.target=\'%s\'  AND re.model_name=\'%s\'  ORDER BY  al.chain ", $param->{_target}, $param->{_model});
	my $sth = $self->{_database}->query($query);
	if(defined($sth) && ($sth->rows() > 0)) {
	    while(my($model_name, $target, $chain, $align, $eval_capri) = $sth->fetchrow_array()){
		push @result, {
		  MODEL_NAME => $model_name,
		  TARGET => $target,
		  CHAIN => $chain,
		  ALIGN => $align,
		  IS_CAPRI => (defined($eval_capri) ? $eval_capri : 0)
		};
	    }
	}
     } 
     if ($param->{_model} eq 'all') {
	my $query = sprintf(
		"SELECT x.*, g.eval_capri FROM (SELECT re.model_name as model_name, re.code, re.target as target, al.chain as chain , al.$column as alignemnt, al.box_cc AS box_cc, al.smoc AS smoc, al.emringer AS emringer, al.smoc_d AS smoc_d
                FROM casp13.multimer_results_vs_emm_alignment al JOIN casp13.multimer_results_vs_emm re ON re.id=al.multimer_results_vs_emm_id
                WHERE re.target=\'%s\' ) AS x
		  JOIN 
		(SELECT re.model_name as model_name, max(al.$column_score) AS max_$column_score FROM casp13.multimer_results_vs_emm_alignment al
			JOIN casp13.multimer_results_vs_emm re ON re.id=al.multimer_results_vs_emm_id
                WHERE re.target=\'%s\' AND al.$column IS NOT NULL 
			GROUP BY re.model_name) AS y
                ON x.model_name=y.model_name AND x.$column_score=y.max_$column_score 
		LEFT JOIN casp13.groups g ON x.code=g.code
		ORDER BY x.$column_score DESC, x.model_name ASC LIMIT 60", $param->{_target}, $param->{_target});
	
        my $sth = $self->{_database}->query($query);
        my %hash_done;
 	my $i = 1;
        if(defined($sth) && ($sth->rows() > 0)) {
            while(my($model_name, $gr_code, $target, $chain, $align, $box_cc, $smoc, $emringer, $smoc_d, $eval_capri) = $sth->fetchrow_array()){
		if (exists $hash_done{$model_name}){
			next;
		}
		$hash_done{$model_name} = 1;
                push @result, {
		  INDEX => $i,
                  MODEL_NAME => $model_name,
                  TARGET => $target,
                  CHAIN => sprintf("%s%s%s", $model_name, ($eval_capri==1 ? '*' : ''),  ($chain eq '-' ? '' : "($chain)")),
                  ALIGN => $align,
		  BOX_CC => sprintf("%.3f",$box_cc),
		  SCORE2 => sprintf("%.3f", $box_cc),
		  SMOC => sprintf("%.3f", $smoc),
		  SCORE => sprintf("%.3f", $smoc),
		  EMRINGER => sprintf("%.3f", $emringer),
	          SMOC_D => (defined($smoc_d) ? sprintf("%.3f", $smoc_d) : '-') ,
		  IS_CAPRI => (defined($eval_capri) ? $eval_capri : 0),
                };
		$i++;
		if ($i == 13){last;}
            }
        }
	#print "==========sprintf("%.3f", =\n$query\n==============\n"
     }
     return @result;
}

sub get_results_per_chain{
    my ($self, $param) = @_;
    my @results = ();
    my $where = '';
    if (!defined($param->{_metric}) || ($param->{_metric} ne 'phenix' && $param->{_metric} ne 'tempy' && $param->{_metric} ne 'smoc_d')){
	return @results;
    }
    if (! defined($param->{_target}) ){
	return @results;
    } else {
	$where = sprintf " WHERE target=\'%s\' ", $param->{_target};
    }
    if (defined($param->{_model})){
	$where .= sprintf " AND model_name = \'%s\' ", $param->{_model};
    }
    my $column = undef;
    if ($param->{_metric} eq 'phenix') {
	$column = 'box_cc_per_chain';
    } elsif ($param->{_metric} eq 'tempy'){
	$column = 'ccc_per_chain';
    } elsif ($param->{_metric} eq 'smoc_d'){
        $column = 'smoc_d_per_chain';
    }
    my $query = sprintf("SELECT re.model_name, re.resol, re.$column FROM casp13.multimer_results_vs_emm re %s ", $where);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	my $index = 1;
        while(my($model_name, $resol, $score_per_chain) = $sth->fetchrow_array()){
		my @arr = split(/\s+/, $score_per_chain);
		foreach my $el (@arr) {
		   # A:0445 B:0.55
		   if ($el =~ m/(\S+):(\S+)/){
		     my $chain = $1; my $score = $2;
                     push @results, {
			INDEX => $index++, 
                        MODEL_NAME => $model_name,
			RESOL => $resol,
                        TARGET => $param->{_target},
			CHAIN => $chain,
			SCORE => (defined($score) ? sprintf("%.3f", $score) : "-")
                     };
		   }
		}
        }
	# sort by boxCC in descending order
	$index = 1;
	@results = sort {$b->{SCORE}<=>$a->{SCORE}} @results;
	foreach my $el (@results){
		$el->{INDEX} = $index++;
	}
    }
    return @results;
}

sub get_results_per_chain_all {
    my ($self, $param) = @_;
    my @results = ();
    my $where = ' WHERE 1=1 ';

#    if (! defined($param->{_target}) ){
#        return @results;
#    } else {
#	if ($where ne '') {
#                $where .= " AND ";
#        }
#        $where = sprintf " target=\'%s\' ", $param->{_target};
#    }
    if (defined($param->{_map_emd_no})){
        if ($where ne '') {
                $where .= " AND ";
        }
        $where .= sprintf(" map_emd_no = \'%s\' ", $param->{_map_emd_no});
    }
    if (defined($param->{_methods})){
        if ($where ne '') {
                $where .= " AND ";
        }
        $where .= sprintf(" (m.modelmethods_id IS NULL OR m.modelmethods_id IN (%s)) ", $param->{_methods});
    }

    my $query = sprintf("SELECT re.target, re.model_name, re.map_emd_no, re.resol, re.box_cc_per_chain, re.ccc_per_chain, 
			mm.description AS method,
		        p.name AS author
			FROM casp13.multimer_results_vs_emm re 
			LEFT JOIN casp13.em_models m ON m.name::text=re.model_name::text
			LEFT JOIN casp13.participants p ON p.gr_code=re.code
		        LEFT JOIN casp13.modelmethods mm ON mm.id=m.modelmethods_id
			%s ", $where);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	my $index = 1;
	while( my($target, $model_name, $map_emd_no, $resol, $box_cc, $ccc, $method, $author) = $sth->fetchrow_array()){
		my %hash1 = $self->string2hash($box_cc);
		my %hash2 = $self->string2hash($ccc);
		if (scalar(keys %hash1) >= scalar(keys %hash2)) {
			foreach my $chain (sort keys %hash1) {
				push @results, {
					INDEX => $index++,
					TARGET => $target,
					MODEL_NAME => $model_name,
					IS_REF_MODEL => ($model_name =~ m/EM\d{3,3}_\d/ ? 0 : 1),
					MAP_EMD_NO => $map_emd_no,
					CHAIN => $chain,
					BOX_CC => sprintf("%.3f", $hash1{$chain}),
					CCC => (exists $hash2{$chain}? sprintf("%.3f", $hash2{$chain}) : '-'),
					METHOD => (defined($method) ? $method : ''),
					AUTHOR => (defined($author) ? $author : '') 
				};
			}
		} else {
			foreach my $chain (sort keys %hash2) {
                                push @results, {
                                        INDEX => $index++,
                                        TARGET => $target,
                                        MODEL_NAME => $model_name,
					IS_REF_MODEL => ($model_name =~ m/EM\d{3,3}_\d/ ? 0 : 1),
                                        MAP_EMD_NO => $map_emd_no,
                                        CHAIN => $chain,
                                        BOX_CC => (exists $hash1{$chain}? sprintf("%.3f",$hash1{$chain}) : '-'),
                                        CCC => sprintf ("%.3f", $hash2{$chain}),
					METHOD => (defined($method) ? $method : ''),
                                        AUTHOR => (defined($author) ? $author : '')
                                };
			}
		}
	}
    }
    return @results;
}

sub string2hash{
    my ($self, $str) = @_;
    my %res;
    foreach my $el (split(/\s+/, $str)){
	if ( $el =~ m/(\S+):(\S+)/ ){
	   $res{$1} = $2;
	}
    }
    return %res;
}


1;