package CommonMysql;

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

sub new { 
    my $class = shift;
    my $self = {};
    return bless $self, $class;
}
sub getCreateTableSysSnpeff {
	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`))";
}
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',1,'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;
}

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 getCreateTableSysSnpeff {return ""; }
sub getInsertSysSnpeff { return ""; }
sub getCol2TableMap { return {}; }
sub getTable2ColMap { return {}; }

1;

