#!/usr/bin/perl -w

use strict;
use DBI;
use Getopt::Long;

my $RESTORE = 0;
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $SAMPLE   = 'accession_ID';
my $ID = "";
my $RDBMS = '';
my $TABLESPACE = '';

################################################################
## Checking parameters and database connection
################################################################
GetOptions( 'r|restore'     => \$RESTORE,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            'b|backend=s'      => \$RDBMS,
            's|space=s'   => \$TABLESPACE,
            'p|password=s'  => \$PASS
);

if ( !$DB || !$USER ) {
    _error_method();
    exit;
}
if(!$RDBMS){
    $RDBMS = 'mysql';
}

my $connect;
if($RDBMS eq "oracle"){
    if(!$TABLESPACE){
        _error_method();
        exit;
    }
    $connect = DBI->connect('DBI:Oracle:host='.$HOST.';SERVICE_NAME='.$DB.';',$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
    if(!_check_exists_tablespace($TABLESPACE)){
        print "Tablespace: '$TABLESPACE' is not found.";
        _exit(1);
    }
}else{
    $connect= DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
}
my $val1 = $connect->selectrow_array("select table_name, column_name from information_schema.columns where column_name = 'other_1' and table_schema = '$DB'");
if (!defined $val1) {
	print "Updating sys_accession table...\n";
	_dbexe("alter table sys_accession add other_1 longtext");
	_dbexe("alter table sys_accession add other_2 longtext");
	print "Done!\n";
} else {
	print "sys_accession table has already updated !\n";
}
my $val3 =  $connect->selectrow_array("SHOW TABLES LIKE 'sys_snpEff'");
if (defined $val3 ) {
	print "Update snpEff information\n";
	_dbexe("truncate table sys_snpEff");
	_dbexe("INSERT INTO `sys_snpEff` VALUES
                (1,'INTERGENIC',0),(2,'UPSTREAM',0),(3,'DOWNSTREAM',0),(4,'UTR_5_PRIME',0),
                (5,'UTR_3_PRIME',0),(6,'REGULATION',0),(7,'INTRAGENIC',0),(8,'GENE',0),
                (9,'INTRON_CONSERVED',0),(10,'INTRON',0),(11,'INTERGENIC_CONSERVED',0),
                (12,'SYNONYMOUS_START',1),(13,'NON_SYNONYMOUS_START',1),(14,'START_GAINED',1),
                (15,'SYNONYMOUS_CODING',1),(16,'SYNONYMOUS_STOP',1),(17,'EXON',1),(18,'CDS',1),
                (19,'TRANSCRIPT',1),(20,'CHROMOSOME_LARGE_DUPLICATION',1),(21,'SPLICE_SITE_REGION',1),
                (22,'SPLICE_SITE_BRANCH',1),(23,'SPLICE_SITE_BRANCH_U12',1),(24,'MICRO_RNA',1),
                (25,'NON_SYNONYMOUS_STOP',1),(26,'NON_SYNONYMOUS_CODING',2),(27,'CODON_CHANGE',2),
                (28,'CODON_INSERTION',2),(29,'CODON_CHANGE_PLUS_CODON_INSERTION',2),(30,'CODON_DELETION',2),
                (31,'CODON_CHANGE_PLUS_CODON_DELETION',2),(32,'UTR_5_DELETED',2),(33,'UTR_3_DELETED',2),
                (34,'CHROMOSOME_LARGE_INVERSION',2),(35,'NEXT_PROT',2),(36,'SPLICE_SITE_ACCEPTOR',3),
                (37,'SPLICE_SITE_DONOR',3),(38,'START_LOST',3),(39,'EXON_DELETED',3),(40,'FRAME_SHIFT',3),
                (41,'STOP_GAINED',3),(42,'STOP_LOST',3),(43,'RARE_AMINO_ACID',3),
                (44,'CHROMOSOME_LARGEDELETION',3),(45,'EXON_DELETED_PARTIAL',3),(46,'EXON_DUPLICATION',3),
                (47,'EXON_DUPLICATION_PARTIAL',3),(48,'EXON_INVERSION',3),(49,'EXON_INVERSION_PARTIAL',3),
                (50,'GENE_DELETED',3),(51,'GENE_DUPLICATION',3),(52,'GENE_FUSION',3),
                (53,'GENE_FUSION_HALF',3),(54,'GENE_FUSION_REVERESE',3),(55,'GENE_REARRANGEMENT',3),
                (56,'PROTEIN_PROTEIN_INTERACTION_LOCUS',3),(57,'PROTEIN_STRUCTURAL_INTERACTION_LOCUS',3),(58,'TRANSCRIPT_DELETED',3)"
	);
	print "done!\n"
}
sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute() or warn "Excute error: $DBI::errstr\n";
}
