#!/usr/bin/perl -w
use strict;
use DBI;
use Getopt::Long;

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

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

if ( !$DB || !$USER ) {
  _error_method();
  exit;
}

my $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

if (  defined _check_list_table() ) {
	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 multi 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();
}

print "\n\n  -----------\n";
print "   Next step\n";
print "  -----------\n\n";
print "   1. Input Reference fasta\n";
print "     # tasuke_ref.pl -c -f [file(.fa)] -db $DB -u $USER -p $PASS\n\n";
print "   2. Input DEPTH file as count of accessions you inputted.\n";
print "     # tasuke_depth_db.pl -n [$SAMPLE] -f [file(.depth)] -db $DB -u $USER -p $PASS\n\n";
print "   3. 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 "   4. Input gene tracks\n";
print "     # tasuke_track.pl -c -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);
   }
}


sub _exit {
  $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
  exit;
}

###############################################################
# 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 MySQL 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]."' ");
					_dbexe("drop TABLE d".$ex_cnt."_variant ");
					_dbexe("drop TABLE d".$ex_cnt."_accession ");
					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 database.\n";
			#print "\n\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;
				## 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: 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 database.\n";
				$crcount++;
		}
	}
  }
  close IN;
  if($crcount!=0){
	print "OK ".$crcount." accession table is added.\n";
  }
  if($upcount!=0){
	print "OK ".$upcount." accession table is updated.\n";
  }
  if($delcount!=0){
	print "OK ".$delcount." accession table is 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;
	#print $val."\n";
  }
  return \@id;
	
}

### Checking list table  #########################################
sub _check_list_table {
  my $val = $connect->selectrow_array("show tables like 'sys_accession'");
  #my $res = $connect->selectrow_array("select * from sys_accession") if defined $val;
  #print STDOUT $val;
  return $val;
}

### Checking chromosome table ####################################
sub _check_chrs_table {
  my $val = $connect->selectrow_array("show tables like 'sys_chromosome'");
  my $res = $connect->selectrow_array("select * from sys_chromosome") if defined $val;
  return $res;
}

### Checking chromosome table ####################################
sub _check_exists_table {
  my ($t) = @_;
  my $val = $connect->selectrow_array("show tables like '$t'");
  return $val;
}

### Checking chromosome table ####################################
sub _check_exists_accession {
  my ($t) = @_;
  my $val = $connect->selectrow_array("show tables like 'sys_accession'");
  my $res = $connect->selectrow_array("select accession_id from sys_accession where accession_id like '$t'") if defined $val;
  return $res;
}

sub _get_exists_accession {
  my ($t) = @_;
  my $val = $connect->selectrow_array("show tables like 'sys_accession'");
  my $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";
}

### Checking table num ###########################################
sub _check_table_num {
  my $val = $connect->selectrow_array("show tables like 'sys_accession'");
  my $res = $connect->selectrow_array("select max(cast(substring(accession_db, 2) as unsigned)) from sys_accession") if defined $val;
  $res++;
  return $res;
}

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

sub _error_method {
  print "\ntasuke_init.pl -db [database] -u [user] -p [password]\n\n";
}

