#!/usr/bin/perl -w

#
# tasuke_tsv_db.pl
# http://tasuke.dna.affrc.go.jp/
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#
# This software is released under the MIT License.
#
# http://opensource.org/licenses/mit-license.php
#

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   = '';
my $READS = 0;
my $CUSTOMTRACK = 0;

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 'n|name=s'      => \$NAME,
            'r|restore'     => \$RESTORE,
            'l|list'        => \$LIST,
            'f|file=s'      => \$VCF,
            'c|custom'      => \$CUSTOMTRACK,
            '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.';mysql_local_infile=1;',$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();
}
if($CUSTOMTRACK){
    $TABLE.="_accession_cstm";
}else{
    $TABLE.="_accession";
}


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

###########################################################
#  Checking DEPTH file
###########################################################
 my $num=0;
 _print_time("File checking");
 open IN, '<', $VCF or die "file open error: $!";
 while (<IN>) {
    if($num==0){
        $num++;
        my @line = split(/\t/, $_);
        my @column = split(/=/, $line[0]);
        if($CUSTOMTRACK){
            if(!defined $column[1]){
                print " Tasuke_depth_db.pl can not get number of reads from this TSV file.\n Try to make TSV file using TASUKE release 1.3.0 or later.\n Exit. \n";
                _exit();
            }
            if($column[1] =~ /\d+/){
                $READS=$column[1];
            }else{
                $READS=0;
            }
        }
    }else{
        $num++;
        my @line = split(/\t/, $_);
        foreach my $x(@line){
            if($x eq ""){
                print "The DEPTH file is probably truncated. near:$num \n";
                print $_."\n";
                print "Exit.\n";
                _exit();
            }
        }
    }
 }
 $num--;
 my $chk_max = _max();
 if($chk_max != $num){
    print "The DEPTH file is probably truncated. near:$num\n";
    print "Exit.\n";
    _exit();
 }
 
###########################################################
# Creating depth table
###########################################################
# Set chromosomes 
my (@chrs) = _chrs();
my ($max) = _max();
  
### Adding a column for number of reads when you use old table(~1.2.0) ########
if(!defined _check_exists_cstm()){
    _dbexe("ALTER TABLE sys_accession add column num_reads bigint DEFAULT 0");
}
if($CUSTOMTRACK){
    my @tables = _get_tables();
    foreach my $x(@tables){
        if (!defined _check_exists_table($x."_cstm")){
            _dbexe("CREATE TABLE ".$x."_cstm like ".$x);
        }
    }
}

_dbexe("LOCK TABLES $TABLE WRITE, sys_accession WRITE, sys_chromosome READ;");
_print_time(" [".$NAME." depth] begin create table");
_LoadDepth($VCF, $TABLE, $NAME, $READS);
_dbexe("UNLOCK TABLES;");
_print_time(" [".$NAME." depth] complete create table");
_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;
}

### Checking for number of reads column ####################################
sub _check_exists_cstm {
    my $val = $connect->selectrow_array("show columns from sys_accession where Field = 'num_reads'");
    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 table\n";
    print "tasuke_tsv_db.pl -n [id] -f [depth_file] -db [database] -u [user] -p [password]\n\n";
    print "Creating general-purpose table\n";
    print "tasuke_tsv_db.pl -c -n [id] -f [depth_file] -db [database] -u [user] -p [password]\n\n";
    print "Restoring table\n";
    print "tasuke_tsv_db.pl -r -n [name] -db [database] -u [user] -p [password]\n\n";
    print "Listing accession\n";
    print "tasuke_tsv_db.pl -l -db [database] -u [user] -p [password]\n\n";
}

########LOAD DEPTH DATA
sub _LoadDepth{
    my $DEPTH = shift;
    my $TABLE = shift;
    my $NAME = shift;
    my $READS = shift;
    if($CUSTOMTRACK){
        _dbexe("UPDATE sys_accession SET num_reads=".$READS." where accession_id='".$NAME."'");
    }
    _dbexe("LOAD DATA LOCAL INFILE "."\'".$DEPTH."\'"." replace INTO TABLE ".$TABLE." IGNORE 1 LINES ;");
}

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

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

sub _get_tables{
    my $val="";
    my @id;
    my $query = "show tables like 'd%_accession'";
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @id, $val;
    }
    return @id;
}

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