#!/usr/bin/perl -w

#
# tasuke_accession.pl
# http://tasuke.dna.affrc.go.jp/
#
# Author: Jungsok Kim, Ryutaro Itoh
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#
# This software is released under the MIT License.
#
# http://opensource.org/licenses/mit-license.php
#

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";
}

###############################################################
# Installing Started 
###############################################################
### Starting chromosome & accession list tables
$SIG{'INT'} = '_SIGINThandler';
if (  defined _check_exists_table("sys_accession") ) {
    if ($RESTORE) {
        print "\n\n   -----------------------------------";
        print "\n    Deleting accession from database";
        print "\n   -----------------------------------";
        print "\n\n   Input csv file about list of accessions below.";
    }else{
        print "\n\n   ------------------------------------------------";
        print "\n    Installing TASUKE multiple genome browser started";
        print "\n   ------------------------------------------------";
        print "\n\n   Input csv file about list of accessions below.";
    }
    _first_list();
    print "finished.\n";
} else {
    print " \n   [Error] Your database must be initialized by using tasuke_init.pl.\n\n";
    _exit(1);
}

###############################################################
# Finished
###############################################################
print "\n\n  -----------\n";
print "   Next step\n";
print "  -----------\n\n";
print "   1. Input Reference fasta\n";
print "     # tasuke_ref.pl -f [file(.fa)] -db $DB -u $USER -p $PASS\n\n";
print "   2. Input VARIANT file as count of accessions you inputted.\n";
print "     # tasuke_variant_vcf.pl -n [$SAMPLE] -f [file(.vcf)] -db $DB -u $USER -p $PASS\n\n";
print "   3. Input DEPTH file as count of accessions you inputted. (Optional)\n";
print "     # tasuke_tsv_db.pl -n [$SAMPLE] -f [file(.tsv)] -db $DB -u $USER -p $PASS\n\n";
print "   4. Input tsv file for the custom track as count of accessions you inputted. (Optional)\n";
print "     # tasuke_tsv_db.pl -c -n [$SAMPLE] -f [file(.tsv)] -db $DB -u $USER -p $PASS\n\n";
print "   5. Input gene tracks (Optional)\n";
print "     # tasuke_track.pl -t [track_name] -f [file(.gff)] -db $DB -u $USER -p $PASS\n\n";
print "\n#######################################################################\n";
_exit();

###############################################################
# Set csv file for list of accessions
###############################################################
sub _first_list {
   my $F;
   
   print "\n    +------------------------  CSV  -----------------------+";
   print "\n    | IRGC12793,Kitrana 508,japonica,ARO,Madagascar,,Elite |";
   print "\n    | IRGC30416,IR 36,indica,IND,Bangladesh,,Landrace      |";
   print "\n      ..";
   print "\n    | P46,P46,wild,,,,,                                    |";
   print "\n    +------------------------------------------------------+";
   print "\n    [Formats] ID, NAME, Variety, Sub variety, Origin, Origin 2, Type\n\n";
   ## Require csv file
   print "> Where is the csv file? # ";
   while (defined(my $line = <STDIN>)){
        chomp($line);
        $line=~s/^\s*(.*?)\s*$/$1/;
        $F=$line;
        if ( $F ne "" && -e $F ) {
            if ( _check_list_csv($F) ) {
                print "It is wrong format.\n";
            } else {
                last;
            }
        } elsif ( $F ne "" && ! -e $F ) {
            print "File does not exists.\n";
        }
        print "> Where is the csv file? # ";
   }

   ## Confirmation
   if ($RESTORE) {
        _confirm_list_del($F);
        print "> Are you sure deleting above accession?[y|n] ";
        while (defined(my $line = <STDIN>)){
            chomp($line);
            if ( $line eq "y" || $line eq "Y" ) {
                last;
            } elsif ( $line eq "n" || $line eq "N" ) {
                print "finished.\n";
                exit;
            } else {
                print "> Are you sure deleting above accession?[y|n] ";
            }
        }
        _create_list_table($F);
   }else{
        _confirm_list($F);
        print "> Are you sure adding or updating accession?[y|n] ";
        while (defined(my $line = <STDIN>)){
            chomp($line);
            if ( $line eq "y" || $line eq "Y" ) {
                last;
            } elsif ( $line eq "n" || $line eq "N" ) {
                print "finished.\n";
                exit;
            } else {
                print "> Are you sure adding or updating accession?[y|n] ";
            }
        }
        _create_list_table($F);
   }
}



###############################################################
# Recognization csv file
###############################################################
sub _confirm_list {
    my ($f)= @_;
    my $e=0;
    
    print "\n   * Have you check the accession list you allocate.[".$f."]\n";
    print "   +--------------------------------------------------------------------------------------------+\n";
    print "   |     ID     |     NAME     |  Variety  | Sub variety |   Origin   |  Origin 2  |    Type    |\n";
    print "   +--------------------------------------------------------------------------------------------+\n";
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/\,/, $_, -1);
        printf("   |%11s |%13s |%10s |%12s |%11s |%11s |%11s |\n",$line[0],$line[1],$line[2],$line[3],$line[4],$line[5],$line[6]);
        $SAMPLE=$line[0];
    }
    print "   +--------------------------------------------------------------------------------------------+\n";
    print "\n   * WARNING\n";
    print "     Don't change column at [ID] on the database after any of this process.\n";
    print "     If you found miss at ID values, select [n] and again this process now.\n\n";
    close IN;
    return $e;
}

###############################################################
# Recognization csv file (when restoreing)
###############################################################

sub _confirm_list_del {
    my ($f)= @_;
    my $e=0;
    
    print "\n   * Have you check the accession list you allocate.[".$f."]\n";
    print "   +--------------------------------------------------------------------------------------------+\n";
    print "   |     ID     |     NAME     |  Variety  | Sub variety |   Origin   |  Origin 2  |    Type    |\n";
    print "   +--------------------------------------------------------------------------------------------+\n";
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/\,/, $_, -1);
        printf("   |%11s |%13s |%10s |%12s |%11s |%11s |%11s |\n",$line[0],$line[1],$line[2],$line[3],$line[4],$line[5],$line[6]);
        $SAMPLE=$line[0];
    }
    print "   +--------------------------------------------------------------------------------------------+\n";
    print "\n   * WARNING\n";
    print "     If you found miss at ID values, select [n] and again this process now.\n";
    print "     This process deletes not only accession information but also depth and variant data.\n\n";
    close IN;
    return $e;
}

###############################################################
# Checking accession list file
###############################################################
sub _check_list_csv {
    my ($f)= @_;
    my $e=0;
    
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/,/, $_, -1);
        $e=1 if $#line != 6; 
    }
    close IN;
    return $e;
}

###############################################################
# Creating chromosome table
###############################################################
sub _create_list_table {
    my ($f)= @_;
    my @c = _chrs();
    my $e=0;
    my $id="";
    my $ex_cnt=0;
    my $cnt=1;
    my $upcount=0;
    my $crcount=0;
    my $delcount=0;
    
    if(defined _check_exists_accession("%")){
        $cnt=_check_table_num();
    }
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/,/, $_, -1);
        $line[0] =~ s/^\s*(.*?)\s*$/$1/;
        $line[1] =~ s/^\s*(.*?)\s*$/$1/;
        $line[2] =~ s/^\s*(.*?)\s*$/$1/;
        $line[3] =~ s/^\s*(.*?)\s*$/$1/;
        $line[4] =~ s/^\s*(.*?)\s*$/$1/;
        $line[5] =~ s/^\s*(.*?)\s*$/$1/;
        $line[6] =~ s/^\s*(.*?)\s*$/$1/;
        if ($RESTORE) {
            if(defined _check_exists_accession($line[0])){
                print "> Are you sure delete accession of ".$line[0]." ?[y|n] ";
                while (defined(my $ln = <STDIN>)){
                    chomp($ln);
                    if ( $ln eq "y" || $ln eq "Y" ) {
                        $ex_cnt=_get_exists_accession($line[0]);
                        _dbexe("delete from sys_accession where accession_id = '".$line[0]."' ");
                        if($RDBMS eq "oracle"){
                            if (defined _check_exists_table("D".$ex_cnt."_variant") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_variant cascade constraint");
                                #_dbexe("drop sequence SEQ_D".$ex_cnt."_VAR");
                            }
                            if (defined _check_exists_table("D".$ex_cnt."_accession") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_accession cascade constraint");
                            }
                            if (defined _check_exists_table("D".$ex_cnt."_accession_cstm") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_accession_cstm cascade constraint");
                            }
                        }else{
                            if (defined _check_exists_table("d".$ex_cnt."_variant") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_variant");
                            }
                            if (defined _check_exists_table("d".$ex_cnt."_accession") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_accession");
                            }
                            if (defined _check_exists_table("d".$ex_cnt."_accession_cstm") ) {
                                _dbexe("drop TABLE d".$ex_cnt."_accession_cstm");
                            }
                        }
                        print $line[0]." was deleted from detabase.\n";
                        $delcount++;
                        last;
                    } elsif ( $ln eq "n" || $ln eq "N" ) {
                        print $line[0]." is skipped.\n";
                        last;
                    } else {
                        print "> Are you sure delete accession of ".$line[0]." ?[y|n] ";
                        print "\n   * WARNING\n";
                        print "     This process delete not only accession information but also depth and variant information.\n";
                    }
                }
            }else{
                print $line[0]." does not exist.\n";
            }
        }else{
            if(defined _check_exists_accession($line[0])){
                print "\n   [Caution] ".$line[0]." already exist in the database.\n";
                print "> Are you sure updating accession information of ".$line[0]." ?[y|n] ";
                while (defined(my $ln = <STDIN>)){
                    chomp($ln);
                    if ( $ln eq "y" || $ln eq "Y" ) {
                        _dbexe("update sys_accession set accession_name='".$line[1]."', variety='".$line[2]."', sub_variety='".$line[3]."', origin='".$line[4]."', origin_2='".$line[5]."', type='".$line[6]."' where accession_id='".$line[0]."'");
                        print $line[0]." was updated.\n";
                       $upcount++;
                        last;
                    } elsif ( $ln eq "n" || $ln eq "N" ) {
                        print $line[0]." is skipped.\n";
                        last;
                    } else {
                        print "> Are you sure updating accession information of ".$line[0]." ?[y|n] ";
                    }
                }
            }else{
                _dbexe("INSERT INTO sys_accession(
                    accession_id,accession_db,accession_name,variety,sub_variety,origin,origin_2,type) 
                    values('".$line[0]."','d".$cnt."','".$line[1]."','".$line[2]."','".$line[3]."','"
                    .$line[4]."','".$line[5]."','".$line[6]."')");
                my $table="d".$cnt;
                
                if($RDBMS eq "oracle"){
                    ## Table: DEPTH
                    if ( ! defined _check_exists_table($table."_accession") ) {
                        my $s="CREATE TABLE ".$table."_accession ( pos number(11,0) primary key,";
                        for ( my $i=0; $i<=$#c; $i++) {
                            $s = $s . $c[$i] . "_b number(6,0),";
                            $s = $s . $c[$i] . "_k number(6,0)";
                            $s = $s . "," if ( $i < $#c);
                        }
                        $s=$s.")";
                        _dbexe($s);
                        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM ".$table."_accession FOR ".$USER.".".$table."_accession");
                    }
                    ## Table: Custom track
                    if ( ! defined _check_exists_table($table."_accession_cstm") ) {
                        my $s="CREATE TABLE ".$table."_accession_cstm ( pos number(11,0) primary key,";
                        for ( my $i=0; $i<=$#c; $i++) {
                            $s = $s . $c[$i] . "_b number(6,0),";
                            $s = $s . $c[$i] . "_k number(6,0)";
                            $s = $s . "," if ( $i < $#c);
                        }
                        $s=$s.")";
                        _dbexe($s);
                        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM ".$table."_accession_cstm FOR ".$USER.".".$table."_accession_cstm");
                    }
                    ## Table: Variant
                    if ( ! defined _check_exists_table($table."_variant") ) {
                        _dbexe("CREATE TABLE ".$table."_variant(seq number(11,0), chr varchar2(50) not null,
                            pos number(11,0) not null, variant varchar2(1) not null, REF varchar2(1000) not null, ALT varchar2(1000) not null, QUAL number,
                            DP number, VDB number, AF1 number, AC1 number, DP1 number(6,0), DP2 number(6,0), DP3 number(6,0), DP4 number(6,0),
                            MQ number, FQ number, PV4 varchar2(200), EFF varchar2(4000), EFF_GRP number(6,0), GT varchar2(10), PL varchar2(200),
                            SP number, GQ number, CONSTRAINT KEY_".$table."_VAR PRIMARY KEY (seq))");
                        _dbexe("CREATE INDEX ".$table."_VAR_IDX on ".$table."_variant(chr,pos,variant) TABLESPACE $TABLESPACE");
                        #if(! _check_exists_seq("SEQ_".$table."_VAR")){
                        #    _dbexe("CREATE SEQUENCE SEQ_".$table."_VAR INCREMENT BY 1 START WITH 1");
                        #}
                        #_dbexeIgnNamePh("CREATE OR REPLACE TRIGGER TRG_".$table."_VAR
                        #                BEFORE INSERT ON ".$table."_variant FOR EACH ROW 
                        #                BEGIN SELECT SEQ_".$table."_VAR.NEXTVAL INTO :NEW.seq FROM DUAL; 
                        #                END; 
                        #                ");
                        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM ".$table."_variant FOR ".$USER.".".$table."_variant");
                    }
                }else{
                    ## Table: DEPTH
                    if ( ! defined _check_exists_table($table."_accession") ) {
                        my $s="CREATE TABLE IF NOT EXISTS ".$table."_accession ( pos int primary key,";
                        for ( my $i=0; $i<=$#c; $i++) {
                            $s = $s . $c[$i] . "_b smallint,";
                            $s = $s . $c[$i] . "_k smallint";
                            $s = $s . "," if ( $i < $#c);
                        }
                        $s=$s.")ENGINE MyISAM";
                        _dbexe($s);
                    }
                    ## Table: Custom track
                    if ( ! defined _check_exists_table($table."_accession_cstm") ) {
                        my $s="CREATE TABLE IF NOT EXISTS ".$table."_accession_cstm ( pos int primary key,";
                        for ( my $i=0; $i<=$#c; $i++) {
                            $s = $s . $c[$i] . "_b smallint,";
                            $s = $s . $c[$i] . "_k smallint";
                            $s = $s . "," if ( $i < $#c);
                        }
                        $s=$s.")ENGINE MyISAM";
                        _dbexe($s);
                    }
                    ## Table: Variant
                    if ( ! defined _check_exists_table($table."_variant") ) {
                        _dbexe("CREATE TABLE IF NOT EXISTS ".$table."_variant(seq mediumint auto_increment, chr varchar(20) not null,
                            pos int not null, variant char(1) not null, REF TINYTEXT not null, ALT TINYTEXT, QUAL float,
                            DP float, VDB float, AF1 float, AC1 float, DP1 tinyint, DP2 tinyint, DP3 tinyint, DP4 tinyint,
                            MQ float, FQ float, PV4 varchar(30), EFF text, EFF_GRP tinyint, GT varchar(10), PL varchar(30),
                            SP float, GQ float, PRIMARY KEY (seq), INDEX idx (chr,pos,variant))CHARACTER SET utf8;");
                    }
                }
                $cnt++;
                print $line[0]." was added to the database.\n";
                $crcount++;
            }
        }
    }
    close IN;
    if($crcount!=0){
        print "OK ".$crcount." accession tables are added.\n";
    }
    if($upcount!=0){
        print "OK ".$upcount." accession tables are updated.\n";
    }
    if($delcount!=0){
        print "OK ".$delcount." accession tables are deleted.\n";
    }
    return $e;
}

### Set chromosome ################################################
sub _chrs {
    my $val="";
    my @c;
    my $query = "SELECT id FROM sys_chromosome";
    my $query_handle = $connect->prepare($query);
    
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @c, $val;
    }
    return @c;
}

### Checking list table  #########################################
sub _get_list{
    my $val="";
    my @id;
    my $query = "SELECT accession_id FROM sys_accession";
    my $query_handle = $connect->prepare($query);
    
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @id, $val;
    }
    return \@id;
}

### Checking table ####################################
sub _check_exists_table {
    my ($t) = @_;
    my $val;
    if($RDBMS eq "oracle"){
        $t = uc($t);
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = '$t'");
    }else{
        $val = $connect->selectrow_array("show tables like '$t'");
    }
    return $val;
}

### Checking oracel sequence ####################################
sub _check_exists_seq {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select sequence_name from user_sequences where sequence_name = '$t'");
    return $val;
}

### Checking accessions ####################################
sub _check_exists_accession {
    my ($t) = @_;
    my $val;
    my $res;
    if($RDBMS eq "oracle"){
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_ACCESSION'");
        $res = $connect->selectrow_array("select accession_id from sys_accession where accession_id like '$t'") if defined $val;
    }else{
        $val = $connect->selectrow_array("show tables like 'sys_accession'");
        $res = $connect->selectrow_array("select accession_id from sys_accession where accession_id like '$t'") if defined $val;
    }
    return $res;
}

### Getting accessions ####################################
sub _get_exists_accession {
    my ($t) = @_;
    my $val;
    my $res;
    if($RDBMS eq "oracle"){
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_ACCESSION'");
        $res = $connect->selectrow_array("select substr(accession_db, 2) from sys_accession where accession_id like '$t'") if defined $val;
    }else{
        $val = $connect->selectrow_array("show tables like 'sys_accession'");
        $res = $connect->selectrow_array("select substring(accession_db, 2) from sys_accession where accession_id like '$t'") if defined $val;
    }
    return $res;
}

### Execute DB ###################################################
sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute() or warn "Excute error: $DBI::errstr\n";
}

sub _dbexeIgnNamePh{
    my ($query) = @_;
    my $query_handle = $connect;
    $query_handle->{odbc_ignore_named_placeholders} = 1;
    $query_handle->do($query) or warn "Excute error: $DBI::errstr\n";#odbc_ignore_named_placeholders
}

### Checking table num ###########################################
sub _check_table_num {
    my $val;
    my $res;
    if($RDBMS eq "oracle"){
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_ACCESSION'");
        $res = $connect->selectrow_array("select max(TO_NUMBER(substr(accession_db, 2))) from sys_accession") if defined $val;
    }else{
        $val = $connect->selectrow_array("show tables like 'sys_accession'");
        $res = $connect->selectrow_array("select max(cast(substring(accession_db, 2) as unsigned)) from sys_accession") if defined $val;
    }
    $res++;
    return $res;
}

### Checking tablespace ###########################################
sub _check_exists_tablespace {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select tablespace_name from user_tablespaces where tablespace_name = '$t'");
    return $val;
}

#### Disconnection and exit #######################################
sub _exit {
    my ($arg) = @_;
    my $code = 0;
    if(defined $arg){$code = $arg;}
    $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
    exit($code);
}

#### Time #######################################
sub _print_time {
    my ($str) = @_;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
    $year += 1900;
    $mon += 1;
    my $datetime = sprintf "%04d-%02d-%02d %02d:%02d:%02d", $year, $mon, $mday, $hour, $min, $sec;
    print "$str : $datetime\n";
}

#### Usage #######################################
sub _error_method {
    print "\nFor MySQL database:\n";
    print "tasuke_accession.pl -db [database] -u [user] -p [password]\n\n";
    print "For Oracle database:\n";
    print "tasuke_accession.pl -db [sid or service_name] -b oracle -s [table_space_name] -u [user] -p [password]\n\n";
}

### SIGINT ###
sub _SIGINThandler{
    print "\n\nFinished.\n";
    _exit(1);
}
