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

my $RESTORE = '';
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $SAMPLE   = 'abc';

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

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

###############################################################
# Installing Started 
###############################################################
print "#######################################################################\n";

### Restoring chromosome & accession list tables
if ($RESTORE) {
  print "\n\n  ----------------------------------------------------------------\n";
  print "   Restoring initialization table for TASUKE multi genome browser\n";
  print "  ----------------------------------------------------------------\n\n";
  print "> Are you sure restore 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 restore database?[y|n] ";
     }
   }
  _dbexe("TRUNCATE TABLE sys_chromosome") if defined _check_exists_table("sys_chromosome");
  _dbexe("TRUNCATE TABLE sys_accession") if defined _check_exists_table("sys_accession");
  print "\n  completed.\n\n";
}

### Starting chromosome & accession list tables
print "\n\n  ------------------------------------------------";
print "\n   Installing TASUKE multi genome browser started";
print "\n  ------------------------------------------------";
print "\n\n  1. 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() ) {
  _first_list();
} else {
  print "  completed.\n\n";
}

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 DEPTH file as count of accessions you inputted.\n";
print "     # tasuke_depth_vcf.pl -n [$SAMPLE] -f [file(.vcf)] -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
   _confirm_list($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_list_table($F);

}

###############################################################
# 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_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(/\,/, $_);
    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";
  print "     Despite of selected [y] in missed, you should restore with 'tasuke_init.pl -r -db ...'.\n\n";
  close IN;
  return $e;
}

###############################################################
# 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 "     Despite of selected [y] in missed, you should restore with 'tasuke_init.pl -r -db ...'.\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(/,/, $_);
    $e=1 if $#line != 6; 
  }
  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_list_table {
  my ($f)= @_;
  my @c = _chrs();
  my $e=0;

  _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`)
         )");

  open IN, '<', $f or die "file open error: $!";
  my $cnt=1;
  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/;
    $line[4] =~ s/^\s*(.*?)\s*$/$1/;
    $line[5] =~ s/^\s*(.*?)\s*$/$1/;
    $line[6] =~ s/^\s*(.*?)\s*$/$1/;
    _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;
    ## Talbe: 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);
    }

    ## Talbe: 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(10),
               SP float, GQ float, PRIMARY KEY (seq), INDEX idx (chr,pos,variant))CHARACTER SET utf8;");
    }

    $cnt++;
  }
  close IN;
  print "OK ".int($cnt-1)." accession table is created.";
  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,
           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 _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";
}

