package CommonMysql;

use Exporter 'import';
@ISA = qw(Exporter);
@EXPORT = qw(getCreateTableSysSnpeff getInsertSysSnpeff getCol2TableMap);
use strict;
#use Data::Dumper;

our %EFFGRP = ('MODIFIER' => 0, 'LOW' => 1, 'MODERATE' => 2, 'HIGH' => 3);

sub new { 
    my $class = shift;
    my $self = {'INNODB_DATA_DIR' => ''};
    return bless $self, $class;
}
sub getSnpEffInfo {
	my $self = shift;
	my($connect) = @_;

	my %hash;
	my $query = "SELECT eff_name, eff_grp FROM sys_snpEff";
	my $sth = $connect->prepare($query);
	$sth->execute();
	while(my $arrayref = $sth->fetchrow_arrayref()) {
		$hash{$arrayref->[0]} = $arrayref->[1];
	}
	$sth->finish();
	undef $sth;
	return \%hash;
}
sub hitAlt {
	my $self = shift;
	my($altsStr, $eff_allele) = @_;

	my @alts = split /,/, $altsStr;	# if GT is "1/2", for example, ALT is "G,T".
	my $hit = 0;
	foreach my $alt (@alts) {
		if($alt eq $eff_allele) {
			$hit = 1;
			last;
		}
	}
	return $hit;
}
sub getMaxEffGrp {
	my $self = shift;
	my ($EffStr, $altsStr) = @_;

	return 0 if(!$EffStr);

	my $level = 0;
	if($EffStr =~ m/^([^\|\(]+)\((.+)$/) {
		# EFF
		$level = $self->getMaxEffGrpEff($EffStr, $altsStr); 
	} else {
		# ANN
		$level = $self->getMaxEffGrpAnn($EffStr, $altsStr);
	}
	return $level;
}
sub getMaxEffGrpEff {
	my $self = shift;
	my ($str, $altsStr) = @_;

	return 0 if(!$str || !$altsStr);	# GT:./.

	my @effArr = split(",", $str);
	my $arraylength = @effArr;
	my $level = 0;
	my $levelWhole = 0;
	my $altHit = 0;
	for (my $i=0; $i<$arraylength; $i++){
		my @eff = split(/\+/, $effArr[$i]);
		my $efflength = @eff;
		for (my $a=0; $a<$efflength; $a++){
			if ($eff[$a] =~ /^(.+?)\((.+)\)/) {
				my $effname=$1;
				my $eff_body = $2;
				my @eff_body_array = split(/\|/, $eff_body);

				my $eff_level = $EFFGRP{$eff_body_array[0]};
				$levelWhole = $eff_level if($levelWhole < $eff_level);
				my $eff_allele = $eff_body_array[$#eff_body_array];
				if($self->hitAlt($altsStr, $eff_allele)) {
					$altHit = 1;
					$level = $eff_level if($level < $eff_level);
				}
=head
				if($altsStr) {
					my $eff_allele = $eff_body_array[$#eff_body_array];
					next if(! $self->hitAlt($altsStr, $eff_allele));
				}
				my $eff_level = $eff_body_array[0];
				if (exists $EFFGRP{$eff_level} && $level < $EFFGRP{$eff_level}) {
					$level = $EFFGRP{$eff_level};
				}
=cut
			}
		}
	}
	$level = $levelWhole if(!$altHit);

	return $level;
}
sub getMaxEffGrpEffLegacy {
	my $self = shift;
	my ($effHash, $str, $altsStr) = @_;

	return 0 if(!$str || !$altsStr);	# GT:./.

	my @effArr = split(",", $str);
	my $arraylength = @effArr;
	my $level = 0;
	for (my $i=0; $i<$arraylength; $i++){
		my @eff = split(/\+/, $effArr[$i]);
		my $efflength = @eff;
		for (my $a=0; $a<$efflength; $a++){
			if ($eff[$a] =~ /^(.+?)\((.+)\)/) {
				my $effname=$1;
				my $eff_body = $2;
				if($altsStr) {
					my @eff_body_array = split(/\|/, $eff_body);
					my $eff_allele = $eff_body_array[$#eff_body_array];
					next if(! $self->hitAlt($altsStr, $eff_allele));
				}
				if (exists($effHash->{$effname}) && $level < $effHash->{$effname}) {
					$level = $effHash->{$effname};
				}
			} elsif($efflength>1 && $a<$efflength) {
				if (exists($effHash->{$eff[$a]})) {
					$level = $effHash->{$eff[$a]};
				}
			}
		}
	}
	return $level;
}
sub getMaxEffGrpAnn {
	my $self = shift;
	my ($str, $altsStr) = @_;

	return 0 if(!$str || !$altsStr);	# GT:./.

	my @effArr = split(",", $str);
	my $level = 0;
	my $arraylength = @effArr;
	for (my $i=0; $i<$arraylength; $i++){
		my @eff = split(/\|/, $effArr[$i]);
		next if(! $self->hitAlt($altsStr, $eff[0]));
		my $eff_level = $eff[2];
		if (exists $EFFGRP{$eff_level} && $level < $EFFGRP{$eff_level}) {
			$level = $EFFGRP{$eff_level};
		}
	}
	return $level;
}
sub getCreateTablesGwas {
	my $self = shift;
	my ($INNODB_DATA_DIR) = @_;

	return {
	"sys_phenotype" => "CREATE TABLE `sys_phenotype` (
		`phenotype_id` varchar(40) NOT NULL,
		`phenotype` varchar(40) NOT NULL,
		PRIMARY KEY (`phenotype_id`)
		) $INNODB_DATA_DIR",
	"sys_phenotype_expression" => "CREATE TABLE `sys_phenotype_expression` (
		`accession_id` varchar(40) NOT NULL,
		`phenotype_id` varchar(40) NOT NULL,
		`expression` varchar(100) NOT NULL,
		PRIMARY KEY (`accession_id`,`phenotype_id`)
		) $INNODB_DATA_DIR",
	"sys_phenotype_prob" => "CREATE TABLE `sys_phenotype_prob` (
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`phenotype_id` varchar(40) NOT NULL DEFAULT '',
		`chr` varchar(50) NOT NULL DEFAULT '',
		`pos` int(11) NOT NULL DEFAULT '0',
		`prob` float DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `id` (`id`),
		KEY `phenotype_id` (`phenotype_id`,`chr`,`pos`),
		KEY `pos_idx` (`pos`),
		KEY `chr_pos_idx` (`chr`,`pos`)
		) $INNODB_DATA_DIR",
	"sys_phenotype_gene" => "CREATE TABLE `sys_phenotype_gene` (
		`id` bigint(20) unsigned NOT NULL,
		`phenotype_prob_id` int(10) NOT NULL,
		`phenotype_id` varchar(40) DEFAULT NULL,
		`chr` varchar(50) DEFAULT NULL,
		`pos` int(11) DEFAULT NULL,
		`prob` float DEFAULT NULL,
		`Effect` text DEFAULT NULL,
		`note` text DEFAULT NULL,
		UNIQUE KEY `id` (`id`),
		KEY `phenotype_id` (`phenotype_id`),
		KEY `prob` (`prob`)
		) $INNODB_DATA_DIR",
	"id2eff" => "CREATE TABLE `id2eff` (
		`id` bigint(20) NOT NULL,
		`eff_grp` smallint(6) NOT NULL,
		PRIMARY KEY (`id`,`eff_grp`)
		) $INNODB_DATA_DIR"
	};
}
sub getCreateTableSysSnpeff {
	my $self = shift;
	my ($innodb_data_dir) = @_;
	return "CREATE TABLE `sys_snpEff` (
		`eff_id` smallint(6) NOT NULL AUTO_INCREMENT,
		`eff_name` varchar(100) DEFAULT NULL,
		`eff_grp` smallint(6) DEFAULT NULL,
		`eff_type` varchar(8) DEFAULT NULL,
		PRIMARY KEY (`eff_id`)) ". $innodb_data_dir;
}
sub getInsertSysSnpeff {
	return "INSERT INTO `sys_snpEff` VALUES
               (null,'INTERGENIC',0,'EFF'),(null,'UPSTREAM',0,'EFF'),(null,'DOWNSTREAM',0,'EFF'),(null,'UTR_5_PRIME',0,'EFF'),
               (null,'UTR_3_PRIME',0,'EFF'),(null,'REGULATION',0,'EFF'),(null,'INTRAGENIC',0,'EFF'),(null,'GENE',0,'EFF'),
               (null,'INTRON_CONSERVED',0,'EFF'),(null,'INTRON',0,'EFF'),(null,'INTERGENIC_CONSERVED',0,'EFF'),
               (null,'SYNONYMOUS_START',1,'EFF'),(null,'NON_SYNONYMOUS_START',1,'EFF'),(null,'START_GAINED',1,'EFF'),
               (null,'SYNONYMOUS_CODING',1,'EFF'),(null,'SYNONYMOUS_STOP',1,'EFF'),(null,'EXON',0,'EFF'),(null,'CDS',1,'EFF'),
               (null,'TRANSCRIPT',1,'EFF'),(null,'CHROMOSOME_LARGE_DUPLICATION',1,'EFF'),(null,'SPLICE_SITE_REGION',1,'EFF'),
               (null,'SPLICE_SITE_BRANCH',1,'EFF'),(null,'SPLICE_SITE_BRANCH_U12',1,'EFF'),(null,'MICRO_RNA',1,'EFF'),
               (null,'NON_SYNONYMOUS_STOP',1,'EFF'),(null,'NON_SYNONYMOUS_CODING',2,'EFF'),(null,'CODON_CHANGE',2,'EFF'),
               (null,'CODON_INSERTION',2,'EFF'),(null,'CODON_CHANGE_PLUS_CODON_INSERTION',2,'EFF'),(null,'CODON_DELETION',2,'EFF'),
               (null,'CODON_CHANGE_PLUS_CODON_DELETION',2,'EFF'),(null,'UTR_5_DELETED',2,'EFF'),(null,'UTR_3_DELETED',2,'EFF'),
               (null,'CHROMOSOME_LARGE_INVERSION',2,'EFF'),(null,'NEXT_PROT',2,'EFF'),(null,'SPLICE_SITE_ACCEPTOR',3,'EFF'),
               (null,'SPLICE_SITE_DONOR',3,'EFF'),(null,'START_LOST',3,'EFF'),(null,'EXON_DELETED',3,'EFF'),(null,'FRAME_SHIFT',3,'EFF'),
               (null,'STOP_GAINED',3,'EFF'),(null,'STOP_LOST',3,'EFF'),(null,'RARE_AMINO_ACID',3,'EFF'),
               (null,'CHROMOSOME_LARGEDELETION',3,'EFF'),(null,'EXON_DELETED_PARTIAL',3,'EFF'),(null,'EXON_DUPLICATION',3,'EFF'),
               (null,'EXON_DUPLICATION_PARTIAL',3,'EFF'),(null,'EXON_INVERSION',3,'EFF'),(null,'EXON_INVERSION_PARTIAL',3,'EFF'),
               (null,'GENE_DELETED',3,'EFF'),(null,'GENE_DUPLICATION',3,'EFF'),(null,'GENE_FUSION',3,'EFF'),
               (null,'GENE_FUSION_HALF',3,'EFF'),(null,'GENE_FUSION_REVERESE',3,'EFF'),(null,'GENE_REARRANGEMENT',3,'EFF'),
               (null,'PROTEIN_PROTEIN_INTERACTION_LOCUS',3,'EFF'),(null,'PROTEIN_STRUCTURAL_INTERACTION_LOCUS',3,'EFF'),(null,'TRANSCRIPT_DELETED',3,'EFF'),
               (null,'coding_sequence_variant',0,'ANN'),(null,'chromosome',3,'ANN'),(null,'duplication',3,'ANN'),(null,'inversion',3,'ANN'),
               (null,'inframe_insertion',2,'ANN'),(null,'disruptive_inframe_insertion',2,'ANN'),(null,'inframe_deletion',2,'ANN'),(null,'disruptive_inframe_deletion',2,'ANN'),
               (null,'downstream_gene_variant',0,'ANN'),(null,'exon_variant',0,'ANN'),(null,'exon_loss_variant',3,'ANN'),(null,'frameshift_variant',3,'ANN'),
               (null,'gene_variant',0,'ANN'),(null,'feature_ablation',3,'ANN'),(null,'gene_fusion',3,'ANN'),(null,'bidirectional_gene_fusion',3,'ANN'),
               (null,'rearranged_at_DNA_level',3,'ANN'),(null,'intergenic_region',0,'ANN'),(null,'conserved_intergenic_variant',0,'ANN'),(null,'intragenic_variant',0,'ANN'),
               (null,'intron_variant',0,'ANN'),(null,'conserved_intron_variant',0,'ANN'),(null,'miRNA',0,'ANN'),(null,'missense_variant',2,'ANN'),
               (null,'initiator_codon_variant',1,'ANN'),(null,'stop_retained_variant',1,'ANN'),(null,'protein_protein_contact',3,'ANN'),(null,'structural_interaction_variant',3,'ANN'),
               (null,'rare_amino_acid_variant',3,'ANN'),(null,'splice_acceptor_variant',3,'ANN'),(null,'splice_donor_variant',3,'ANN'),(null,'splice_region_variant',1,'ANN'),
               (null,'stop_lost',3,'ANN'),(null,'5_prime_UTR_prematurestart_codon_gain_variant',1,'ANN'),(null,'start_lost',3,'ANN'),(null,'stop_gained',3,'ANN'),
               (null,'synonymous_variant',1,'ANN'),(null,'start_retained',1,'ANN'),(null,'transcript_variant',0,'ANN'),(null,'regulatory_region_variant',0,'ANN'),
               (null,'upstream_gene_variant',0,'ANN'),(null,'3_prime_UTR_variant',0,'ANN'),(null,'3_prime_UTR_truncation&exon_loss',2,'ANN'),(null,'5_prime_UTR_variant',0,'ANN'),
               (null,'5_prime_UTR_truncation&exon_loss_variant',2,'ANN'),(null,'sequence_feature&exon_loss_variant',0,'ANN'),
               (null,'non_coding_transcript_exon_variant',0,'ANN'),
               (null,'5_prime_UTR_premature_start_codon_gain_variant',1,'ANN'),
               (null,'conservative_inframe_deletion',2,'ANN'),
               (null,'conservative_inframe_insertion',2,'ANN'),
               (null,'non_coding_transcript_variant',0,'ANN')";
}
sub getCol2TableMap {
	my $self = shift;
	my($connect, $dbname, $baseTableName) = @_;

	my $sql = sprintf "SELECT c.TABLE_NAME,c.COLUMN_NAME,t.TABLE_TYPE FROM information_schema.columns c,information_schema.tables t ".
			"WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND ".
			"c.COLUMN_NAME!='pos' AND c.TABLE_SCHEMA='%s' AND c.TABLE_NAME LIKE '%s%s'",
		$dbname, $baseTableName, '%';
	my $sth = $connect->prepare($sql);
	$sth->execute() or die $!;
	my $detectCompMatch = 0;
	my %compMatch;
	my %serialMatch;
	my $serialPat = '^'. $baseTableName. '\d+$';
	while(my $hashref = $sth->fetchrow_hashref()) {
		if($hashref->{'TABLE_NAME'} eq $baseTableName) {
			$detectCompMatch = 1;
			$compMatch{$hashref->{'COLUMN_NAME'}} = $hashref->{'TABLE_NAME'};
		} elsif($hashref->{'TABLE_NAME'} =~ m/$serialPat/) {
			$serialMatch{$hashref->{'COLUMN_NAME'}} = $hashref->{'TABLE_NAME'};
		}
	}
	if($detectCompMatch) {
#print Dumper(%compMatch);
		return \%compMatch;
	} else {
#print Dumper(%serialMatch);
		return \%serialMatch;
	}
}
sub getTable2ColMap {
	my $self = shift;
	my($connect, $dbname, $baseTableName) = @_;

	my $col2table = $self->getCol2TableMap($connect, $dbname, $baseTableName);
	my %table2col;
	foreach my $col (keys(%{$col2table})) {
		my $table = $col2table->{$col};
		$table2col{$table} = [] if(!exists($table2col{$table}));
		push @{$table2col{$table}}, $col;
	}
	return \%table2col;
}
sub getDbMode {
	my $self = shift;
	my($connect) = @_;

	my $dbmode = "";
	my $sth = $connect->prepare("SHOW TABLES like 'sys_phenotype'");
	$sth->execute() or die $!;
	my $arrayref = $sth->fetchrow_arrayref();
	$sth->finish();
	if($arrayref) {
		$sth = $connect->prepare("SHOW TABLES like 'sys_variant'");
		$sth->execute() or die $!;
		$arrayref = $sth->fetchrow_arrayref();
		$sth->finish();
		if($arrayref) {
			$dbmode = "mvcf";
		} else {
			$dbmode = "svcf";
		}
	} else {
		$dbmode = "legacy";
	}

#print STDERR "$dbmode\n";
	return $dbmode;
}
sub modeIsPlus {
	my $self = shift;
	my($connect) = @_;
	return ($self->getDbMode($connect) eq 'svcf') ? 1:0;
}
sub modeIsPlusMultiVcf {
	my $self = shift;
	my($connect) = @_;
	return ($self->getDbMode($connect) eq 'mvcf') ? 1:0;
}
sub modeIsLegacy {
	my $self = shift;
	my($connect) = @_;
	return ($self->getDbMode($connect) eq 'legacy') ? 1:0;
}
1;

package CommonOracle;

use Exporter 'import';
my @ISA = qw(Exporter);
my @EXPORT = qw(getCreateTableSysSnpeff getInsertSysSnpeff getCol2TableMap);
use strict;

sub new { 
    my $class = shift;
    my $self = {};
    return bless $self, $class;
}
sub getCreateTableGwas {return ""; }
sub getCreateTableSysSnpeff {return ""; }
sub getInsertSysSnpeff { return ""; }
sub getCol2TableMap { return {}; }
sub getTable2ColMap { return {}; }

1;

