#!/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( 
            '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";

###############################################################
# Checking default tables
# [1] sys_track
# [2] sys_snpEff
# [3] sys_snpblock
###############################################################
_check_default_tables();

###############################################################
# Installing Started 
###############################################################

### Starting chromosome & accession list tables
print "\n\n  ------------------------------------------------";
print "\n   Installing TASUKE multi genome browser started";
print "\n  ------------------------------------------------";
print "\n\n  Input csv file about chromosome's information below.";
if ( ! defined _check_chrs_table() ) {
  _first_chrs();
} else {
  print "  completed.";
}

#print "\n\n  2. Input csv file about list of accessions below.";
if ( ! defined _check_list_table() ) {
  _dbexe("DROP TABLE IF EXISTS  sys_accession");
  _dbexe("CREATE TABLE `sys_accession` (
           `accession_id` varchar(40) NOT NULL,
           `accession_db` varchar(6) DEFAULT NULL,
           `accession_name` varchar(40) DEFAULT NULL,
           `variety` varchar(40) DEFAULT NULL,
           `sub_variety` varchar(40) DEFAULT NULL,
           `origin` varchar(20) DEFAULT NULL,
           `origin_2` varchar(30) DEFAULT NULL,
           `type` varchar(40) DEFAULT NULL,
           PRIMARY KEY (`accession_id`)
         )");
}
  #} else {
#  print "  completed.\n\n";
#}

print "\n\n  -----------\n";
print "   Next step\n";
print "  -----------\n\n";
print "   1. Input Accession information\n";
print "     # tasuke_accession.pl -c -db $DB -u $USER -p $PASS\n\n";
print "   2. Input Reference fasta\n";
print "     # tasuke_ref.pl -c -f [file(.fa)] -db $DB -u $USER -p $PASS\n\n";
print "   3. 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 "   4. 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 "   5. 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 chromosomes
###############################################################
sub _first_chrs {
   my $F;
   print "\n\n    +----  CSV  -----------------------+";
   print "\n    | chr01,43270923,16610866,17243770 |";
   print "\n    | chr02,35937250,13872411,13541821 |";
   print "\n      ..";
   print "\n    | chr12,27531856,11761737,12103486 |";
   print "\n    +----------------------------------+";
   print "\n    [Formats] chrs, size of chrs, start of centromere, end of centromere\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_chrs_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
   _confirm_chrs($F);

   print "> Are you sure creating database?[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 creating database?[y|n] ";
     }
   }

   _create_chrs_table($F);

}

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


###############################################################
# Recognization csv file
###############################################################
sub _confirm_chrs {
  my ($f)= @_;
  my $e=0;
  print "\n   * Have you check the list you allocate.[".$f."]\n";
  print "   +----------------------------------------------------------------+\n";
  print "   | chrs  | size of chrs | start of centromere | end of centromere |\n";
  print "   +----------------------------------------------------------------+\n";
  open IN, '<', $f or die "file open error: $!";
  while (<IN>) {
    chomp($_);
    my @line=split(/\,/, $_);
    printf("   |%6s |%13d |%20s |%18s |\n",$line[0],$line[1],$line[2],$line[3]);
  }
  print "   +----------------------------------------------------------------+\n";
  print "\n   * WARNING\n";
  print "     Don't change column at [chrs], [size of chrs] on MySQL after any of this process.\n";
  print "     If you found miss some values, select [n] and again this process now.\n";
  print "     \n";
  close IN;
  return $e;
}


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

###############################################################
# Creating chromosome table
###############################################################
sub _create_chrs_table {
  my ($f)= @_;
  my $e=0;

  _dbexe("DROP TABLE iF EXISTS  sys_chromosome");
  _dbexe("CREATE TABLE sys_chromosome (
            seq SMALLINT AUTO_INCREMENT PRIMARY KEY,
            id VARCHAR(20) NOT NULL,
            size INT NOT NULL,
            centromere_s INT,  
            centromere_e INT
          )");
  open IN, '<', $f or die "file open error: $!";
  while (<IN>) {
    chomp($_);
    my @line=split(/,/, $_);
    $line[0] =~ s/^\s*(.*?)\s*$/$1/;
    $line[1] =~ s/^\s*(.*?)\s*$/$1/;
    $line[2] =~ s/^\s*(.*?)\s*$/$1/;
    $line[3] =~ s/^\s*(.*?)\s*$/$1/;
    _dbexe("INSERT INTO sys_chromosome(id,size,centromere_s,centromere_e)
             VALUES('".$line[0]."',".$line[1].",".$line[2].",".$line[3].")");
  }
  close IN;

  ## Creating table sys_fasta
  my @c=_chrs();
  if ( ! defined _check_exists_table("sys_fasta") ) {
    my $s="CREATE TABLE sys_fasta ( pos INT PRIMARY KEY AUTO_INCREMENT,";
    for ( my $i=0; $i<=$#c; $i++) {
      $s = $s . $c[$i] . "_f CHAR(1)";
      $s = $s . "," if ( $i < $#c);
    }
    $s=$s.")";
    _dbexe($s);
  }

  print "OK. ".int($#c+1)." chromosomes is setted.";
  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 default(base) tables  #########################################
sub _check_default_tables {
  my $val1 = $connect->selectrow_array("SHOW TABLES LIKE 'sys_snpEff'");
  if ( ! defined $val1 ) {
    _dbexe("CREATE TABLE `sys_snpEff` (
            `eff_id` smallint(6) NOT NULL AUTO_INCREMENT,
            `eff_name` varchar(100) DEFAULT NULL,
            `eff_grp` smallint(6) DEFAULT NULL,
            PRIMARY KEY (`eff_id`)
           )");

    _dbexe("INSERT INTO `sys_snpEff` VALUES
            (1,'INTERGENIC',0),(2,'UPSTREAM',1),(3,'DOWNSTREAM',1),(4,'UTR_5_PRIME',2),
            (5,'UTR_3_PRIME',2),(6,'REGULATION',2),(7,'INTRAGENIC',2),(8,'GENE',2),
            (9,'INTRON_CONSERVED',2),(10,'INTRON',2),(11,'INTERGENIC_CONSERVED',2),
            (12,'SYNONYMOUS_START',3),(13,'NON_SYNONYMOUS_START',3),(14,'START_GAINED',3),
            (15,'SYNONYMOUS_CODING',3),(16,'SYNONYMOUS_STOP',3),(17,'EXON',3),(18,'CDS',3),
            (19,'TRANSCRIPT',3),(20,'NON_SYNONYMOUS_CODING',4),(21,'CODON_CHANGE',4),
            (22,'CODON_INSERTION',4),(23,'CODON_CHANGE_PLUS_CODON_INSERTION',4),(24,'CODON_DELETION',4),
            (25,'CODON_CHANGE_PLUS_CODON_DELETION',4),(26,'UTR_5_DELETED',4),(27,'UTR_3_DELETED',4),
            (28,'SPLICE_SITE_ACCEPTOR',5),(29,'SPLICE_SITE_DONOR',5),(30,'START_LOST',5),(31,'EXON_DELETED',5),
            (32,'FRAME_SHIFT',5),(33,'STOP_GAINED',5),(34,'STOP_LOST',5),(35,'RARE_AMINO_ACID',5)");
  }

  my $val2 = $connect->selectrow_array("show tables like 'sys_track'");
  if ( ! defined $val2 ) {
    _dbexe("CREATE TABLE `sys_track` (
           `seq` int(11) NOT NULL AUTO_INCREMENT,
           `track` varchar(50) DEFAULT NULL,
           `chr` varchar(10) DEFAULT NULL,
           `id` varchar(50) DEFAULT NULL,
           `name` varchar(59) DEFAULT NULL,
           `parent` char(1) DEFAULT NULL,
           `type` varchar(40) DEFAULT NULL,
           `pos` int(11) DEFAULT NULL,
           `end` int(11) DEFAULT NULL,
           `strand` tinyint(4) DEFAULT NULL,
		   `note` text ,
		   `others` text ,
           PRIMARY KEY (`seq`),
           KEY `idx` (`id`)
         )");
  }

  my $val3 = $connect->selectrow_array("show tables like 'sys_snpblock'");
  if ( ! defined $val3 ) {
    _dbexe("CREATE TABLE `sys_snpblock` (
              `accession` varchar(40) DEFAULT NULL,
              `rate` float DEFAULT NULL,
              `peak` varchar(20) DEFAULT NULL
           )");
  }

}

### 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;
  return $res;
}

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

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

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

