#!/usr/bin/perl -w

#
# tasuke_add_condition.pl
# http://tasuke.dna.affrc.go.jp/
#
# Author: Ryutaro Itoh
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#

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

my $RESTORE = 0;
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $CONDITION   = 'cond';
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,
            'c|condition=s'      => \$CONDITION,
            's|space=s'   => \$TABLESPACE,
            'p|password=s'  => \$PASS
);

if ( !$DB || !$USER || !$CONDITION) {
    _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";
}

$SIG{'INT'} = '_SIGINThandler';
if(!defined _check_exists_table("sys_accession")){
     print " \n   [Error] Your database must be initialized by using tasuke_init.pl.\n\n";
    _exit();
}
if(!defined _check_exists_table("sys_condition")){
     print " \n   [Error] Your database must be upgraded to version 1.5.0 or later by using tasuke_db_upgrade.pl.\n\n";
    _exit();
}
_cond_table();
_exit();

sub _cond_table { 
    my ($f)= @_;
    my @c = _chrs();
    my $e=0;
    my $id="";
    my @tables = _get_tables();    
    
    if ($RESTORE) {
        if(defined _check_exists_condition($CONDITION)){
            print "> Are you sure delete condition of ".$CONDITION." ?[y|n] ";
            while (defined(my $ln = <STDIN>)){
                chomp($ln);
                if ( $ln eq "y" || $ln eq "Y" ) {
                    my $dest_tb_name = _check_exists_condition($CONDITION);
                    foreach my $x(@tables){
                        _dbexe("delete from sys_condition where condition_id = '".$CONDITION."' ");
                        if($RDBMS eq "oracle"){
                            $x = uc($x);
                            if (defined _check_exists_table($x."_accession_cstm_$dest_tb_name") ) {
                                _dbexe("drop TABLE ".$x."_accession_cstm_$dest_tb_name cascade constraint");
                            }
                        }else{
                            if (defined _check_exists_table($x."_accession_cstm_$dest_tb_name") ) {
                                _dbexe("drop TABLE ".$x."_accession_cstm_$dest_tb_name");
                            }
                        }
                    }
                    print $CONDITION." was deleted from the database.\n";
                    last;
                } elsif ( $ln eq "n" || $ln eq "N" ) {
                    print $CONDITION." is skipped.\n";
                    last;
                } else {
                    print "> Are you sure delete accession of ".$CONDITION." ?[y|n] ";
                    print "\n   * WARNING\n";
                    print "     This process delete not only accession information but also TSV data information.\n";
                }
            }
        }else{
            print $CONDITION." does not exist.\n";
        }
    }else{
        if(defined _check_exists_condition($CONDITION)){
            print "\n   [Caution] ".$CONDITION." already exist in the database.\n\n";
            _exit();
        }else{
            my $num = _get_empty_table_id();
            _dbexe("insert into sys_condition(condition_id, table_id) values('".$CONDITION."', $num)");
            my $dest_tb_name = _check_exists_condition($CONDITION);
            foreach my $x(@tables){
                if($RDBMS eq "oracle"){
                    ## Table: Custom track
                    if ( ! defined _check_exists_table($x."_accession_cstm_$dest_tb_name") ) {
                        my $s="CREATE TABLE ".$x."_accession_cstm_$dest_tb_name ( 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 ".$x."_accession_cstm_$dest_tb_name FOR ".$USER.".".$x."_accession_cstm_$dest_tb_name");
                    }
                }else{
                    ## Table: Custom track
                    if ( ! defined _check_exists_table($x."_accession_cstm_$dest_tb_name") ) {
                        my $s="CREATE TABLE IF NOT EXISTS ".$x."_accession_cstm_$dest_tb_name ( 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);
                    }
                }
            }
            print $CONDITION." was added to the database.\n";
        }
    }
    return $e;
}

### Functions ################################################

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

sub _get_tables {
    my $val="";
    my @c;
    my $query = "select accession_db from sys_accession";
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @c, $val;
    }
    return @c;
}

sub _check_exists_condition {
    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_CONDITION'");
        $res = $connect->selectrow_array("select table_id from sys_condition where condition_id like '$t'") if defined $val;
    }else{
        $val = $connect->selectrow_array("show tables like 'sys_condition'");
        $res = $connect->selectrow_array("select table_id from sys_condition where condition_id like '$t'") if defined $val;
    }
    return $res;
}

sub _get_empty_table_id {
    my $res;
    if($RDBMS eq "oracle"){
        $res = $connect->selectrow_array("select NVL2(max(table_id)+1,max(table_id),1) from sys_condition");
    }else{
        $res = $connect->selectrow_array("select ifnull(max(table_id)+1,1) from sys_condition");
    }
    return $res;
}


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

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
}

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

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

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

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