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

my $NAME   = '';
my $CREATE = 0;
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,
            'l|list'        => \$LIST,
            'c|create'      => \$CREATE,
            'r|restore'     => \$RESTORE,
            '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)||($CREATE&&(!$NAME||!-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.="_variant";

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


###########################################################
# Creating variants table
###########################################################
  _print_time("*[".$NAME." variant]    begin");

  # Set chromosomes 
  my (@chrs) = _chrs();

  _dbexe("LOCK TABLES $TABLE WRITE, sys_snpEff READ;");
  _insertVar(@chrs);
  _dbexe("UNLOCK TABLES;");

  _print_time(" [".$NAME." variant] complete");


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


# Update variants at pos
sub _insertVar {

  my $INDEL = "INDEL";
  my (@chrs)= @_;

  open IN, '<', $VCF or die "file open error: $!";
  my $seek = tell IN;

  for (my $i=0; $i<=$#chrs; $i++ ) {
    seek IN, $seek, 0;
    while (<IN>) {

      if ($_ !~ /^#/i){

      my @line=split(/\t/, $_);
      my $CHR=$line[0];
      if ( $CHR ne $chrs[$i] ) {
        $seek = tell IN;
        $seek = $seek - length($_);
        last;
      }

      my $POS=int($line[1]);
      my $REF=$line[3];
      my @salt=split(/,/,$line[4]);
      my $ALT=$salt[0];
      my $QUAL=int($line[5]);
      my %arr=();

      my @spltinfo=split(/;/, $line[7]);
      my $indelPos=0;
      if ( $spltinfo[0] eq $INDEL ) { $indelPos=1; }
      for (my $i=$indelPos; $i < @spltinfo; $i++) {
        my @info=split(/=/, $spltinfo[$i]);
        $arr{$info[0]}[0] = $info[1] if $info[0] ne "";
      }

      # GT:PL:DP:SP:GQ
      my @sgt=split(/:/, $line[8]);
      my @sgtv=split(/:/, $line[9]);
      if ( $sgt[0] eq 'GT' ) {
        $arr{'GT'}[0] = $sgtv[0];
        $arr{'PL'}[0] = $sgtv[1];
      }

      #############################################################
      # Varation
      #############################################################
      my $variant;
      ## SNP
      if ( $spltinfo[0] ne $INDEL && $ALT ne "." ) {
        $variant="s";
      }
      ## Insertion
      if ( $spltinfo[0] eq $INDEL && length($REF) < length($ALT) ) {
        $variant="i";
      }
      ## Deletion (POS+1)
      if ( $spltinfo[0] eq $INDEL && ( $ALT eq "." || length($REF) > length($ALT)) ) {
        $variant="d";
        $POS=$POS+1;
      }

      #############################################################
      # Combining query
      #############################################################
      my $cQuery;
      my $vQuery;
      $cQuery="chr,pos,variant,ref,alt,qual";
      $vQuery="'$CHR',$POS,'$variant','$REF','$ALT',$QUAL";
      for my $key (sort keys %arr) {

        ## for numbers
        if ( $key eq "DP" || $key eq "VDB" || $key eq "AF1" || $key eq "AC1" || $key eq "MQ" || $key eq "FQ" || $key eq "SP" || $key eq "GQ" ) {
            $cQuery.=",$key";
            $vQuery.=",$arr{$key}[0]";
        }

        ## for string
        if ( $key eq "PV4" || $key eq "EFF" || $key eq "GT" || $key eq "PL" ) {
            $cQuery.=",$key";
            $vQuery.=",'$arr{$key}[0]'";
        }

        ## for EFF_GRP
        if ($key eq "EFF") {
          my @effArr = split(",", $arr{$key}[0]);
          my $arraylength = @effArr;
          if ($arraylength) {
            my $eQry="select max(eff_grp) from sys_snpEff where";
            for (my $i=0; $i<$arraylength; $i++){
              if ($effArr[$i] =~ /^(.+?)\(/) {
                $eQry.=" eff_name='$1'";
              }
              $eQry.=" or" if $i < $#effArr;
            }

            my $sth = $connect->prepare($eQry) or die "Cannot prepare: " . $connect->errstr();
            $sth->execute() or die "Cannot execute: " . $sth->errstr();
            my $field = $sth->fetchrow_array();
            $sth->finish();

            $cQuery.=",EFF_GRP";
            $vQuery.=",$field";

          }
        }

        ## for DP4
        if ( $key eq "DP4" ) { 
          my @dp4Arr = split(",", $arr{$key}[0]);
          my $arraylength = @dp4Arr;
          for (my $i=0; $i<$arraylength; $i++){
            $cQuery.=",DP".($i+1);
            $vQuery.=",$dp4Arr[$i]";
          }
        }
      }
      _dbexe("INSERT into $TABLE($cQuery) values($vQuery);");

      }
    }
  }
  close (IN);
}

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

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

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

### Execute DB ################################################
sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
}

sub _error_method {
  print "\nCreating variant db\n";
  print "tasuke_variant_vcf.pl -n [id] -f [depth_file(.vcf)] -db [database] -u [user] -p [password]\n\n";
  print "Restoring table\n";
  print "tasuke_variant_vcf.pl -r -n [name] -db [database] -u [user] -p [password]\n\n";
  print "Listing accession\n";
  print "tasuke_variant_vcf.pl -l -db [database] -u [user] -p [password]\n\n";
}

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

