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

my $NAME   = '';
my $RESTORE= 0;
my $LIST   = 0;
my $VCF    = '';
my $DB     = '';
my $HOST   = 'localhost';
my $USER   = '';
my $PASS   = '';

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 'n|name=s'      => \$NAME,
            'r|restore'     => \$RESTORE,
            'l|list'        => \$LIST,
            'f|file=s'      => \$VCF,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            'p|password=s'  => \$PASS
);

if ($DB eq ''||($RESTORE&&!$NAME)||(!$RESTORE&&!-e$VCF)) {
  _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 base table
###########################################################
if ( ! defined _check_exists_table("sys_accession") ) {
  print "Some basic table does not exists.\n";
  print "Did you excute 'tasuke_init.pl' at the first?\n";
  exit;
}

###########################################################
# Listing track
###########################################################
if ($LIST) {
  print "[List of accessions] :\n";
  foreach my $l (_lists()) {
    print "$l\n";
  }
  exit;
}

###########################################################
# Checking accession id
###########################################################
my $TABLE =_check_db();
if ( ! defined $TABLE ) {
  print "'$NAME' is not exists on list.\n\n";
  print "- Here is list you loaded\n";
  foreach my $l (_lists()) {
    print "$l\n";
  }
  exit;
}
$TABLE.="_accession";

###########################################################
# Restoring depth table
###########################################################
if ($RESTORE) {
  _dbexe("TRUNCATE TABLE $TABLE;");
  print "DEPTH $NAME table is restored.\n";
  exit;
}


###########################################################
# Creating depth table
###########################################################

  # Set chromosomes 
  my (@chrs) = _chrs();
  my ($max) = _max();

  _dbexe("LOCK TABLES $TABLE WRITE, sys_chromosome READ;");
  _print_time(" [".$NAME." depth]    begin create table");
  _createDepth($max);
  _print_time(" [".$NAME." depth] complete create table");

  _updateDepth();

  _print_time(" [".$NAME." depth]    begin kb");
  _updateDepthKB(@chrs);
  _print_time(" [".$NAME." depth] complete kb");
  _dbexe("UNLOCK TABLES;");


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



### Checking id on the list ####################################
sub _check_db {
  my $val = $connect->selectrow_array("select accession_db from sys_accession where accession_id='$NAME'");
  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 _error_method {
  print "\nCreating depth db\n";
  print "tasuke_depth_vcf.pl -n [id] -f [depth_file(.vcf)] -db [database] -u [user] -p [password]\n\n";
  print "Restoring table\n";
  print "tasuke_depth_vcf.pl -r -n [name] -db [database] -u [user] -p [password]\n\n";
  print "Listing accession\n";
  print "tasuke_depth_vcf.pl -l -db [database] -u [user] -p [password]\n\n";
}

### Create empty depth table #################################
sub _createDepth {
  my ($max) = @_;
  my $str="";
  for (my $i=1;$i<=$max;$i++) {
    if ( $i == $max || !($i%3000) ) {
      $str.="($i)";
      _dbexe("INSERT into $TABLE(pos) values $str;");
      $str="";
    } else {
      $str.="($i),";
    }
  }
}

### Create empty depth table #################################
sub _updateDepth {

  my (@chrs) = _chrs();
  my $volume=80;
  open IN, '<', $VCF or die "file open error: $!";
  my $seek = tell IN;

  for (my $i=0; $i<=$#chrs; $i++ ) {
    _print_time("- ".$chrs[$i]);
    seek IN, $seek, 0;
    my $qry1="";
    my $qry2="";
    my $count=0;
    while (<IN>) {
        my @line=split(/\t/, $_);
        if ($line[0] ne $chrs[$i]){
          if ( $qry1 ne "" ) {
            $qry2=~s/,$//;
            _dbexe("UPDATE ".$TABLE." SET ".$chrs[$i]."_b=case pos ".$qry1." END WHERE pos IN(".$qry2.");");
          }
          $seek = tell IN;
          $seek = $seek - length($_);
          last;
        }
        $qry1.=" WHEN ".$line[1]." THEN ".$line[2];
        $qry2.=$line[1].",";
        $count++;
        if ($count==$volume || eof){
          $qry2=~s/,$//;
          _dbexe("UPDATE ".$TABLE." SET ".$chrs[$i]."_b=case pos ".$qry1." END WHERE pos IN(".$qry2.");");
          $qry1="";
          $qry2="";
          $count=0;
        }
    }
  }
  close IN;

}

# max
sub _max {
  my $val;
  my $query = "SELECT max(size) FROM sys_chromosome";
  my $query_handle = $connect->prepare($query);
  $query_handle->execute();
  $val = $query_handle->fetchrow_hashref;
  return $val->{'max(size)'};
}

# chromosomes
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 _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";
}

# Accession list
sub _lists {
  my $val;
  my @lists;
  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 @lists, $val;
  }
  return @lists;
}

### Convert B to KB ###########################################
sub _updateDepthKB {

  my (@c) = @_;
  my ($m) = _max();
  my $last = int($m/1000);
  $last++ if $m%1000;

  my $sQry="";
  for ( my $i=0; $i<=$#c; $i++) {
    $sQry.="sum(".$c[$i]."_b)/1000";
    $sQry.="," if $i < $#c;
  }

  for ( my $i=1; $i<=$last; $i++) {
    my $e=$i*1000;
    my $s=$e-999;

    my (@avg) = $connect->selectrow_array("SELECT $sQry from $TABLE where pos>=$s and pos<=$e");

    my $query="";
    for (my $j=0; $j<=$#c; $j++) {
      $query.=$c[$j]."_k=";
      if( defined $avg[$j] )
           {$query.=int($avg[$j]);}
      else {$query.="null";}
      $query.="," if $j<$#c;
    }
    _dbexe("UPDATE $TABLE set $query where pos=$i");
  }
}

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

