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

my $TABLE="sys_track";

my $TRACK   = '';
my $CREATE  = 0;
my $REMOVE  = 0;
my $LIST    = 0;
my $FILE    = '';
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 't|track=s'     => \$TRACK,
            'c|create'      => \$CREATE,
            'r|remove'      => \$REMOVE,
            'l|list'        => \$LIST,
            'f|file=s'      => \$FILE,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            'p|password=s'  => \$PASS
);

if (!$LIST&&($TRACK eq ''||(!$CREATE&&!$REMOVE)||($CREATE&&$REMOVE)||$DB eq ''||($CREATE&&!-e$FILE))) {
  _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_track") ) {
  print "Track table does not exists.\n";
  print "Did you excute 'tasuke_init.pl' at the first?\n";
  exit;
}

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

###########################################################
# Removing track
###########################################################
if ($REMOVE) {
  _dbexe("DELETE FROM $TABLE WHERE track='$TRACK';");
  print "$TRACK track is deleted\n";
  exit;
}

###########################################################
# Creating track
###########################################################
if ($CREATE) {

  _print_time("begining");

  if ( my @dup = grep { /$TRACK/i } _lists() ) {
    print "$TRACK is exists\n";
    exit;
  }


  open IN, '<', $FILE or die "file open error: $!";

    while (<IN>) {

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

      chomp($_);
      my @line=split(/\t/, $_);
      my $CHR=$line[0];
      my $TYPE=$line[2];
      my $START=int($line[3]);
      my $END=int($line[4]);
      my $STRAND="null";
      $STRAND="1" if $line[6] eq "+";
      $STRAND="0" if $line[6] eq "-";

      my $PARENT="1";
      my $ID="";
      my $NAME="";
      if ($line[8]) {
        my @attr=split(/;/, $line[8]);
        if (my @p = grep { /parent=/i } @attr) {
          if ( length(@attr) == 1 ) {
            $PARENT="0";
            my @tmp=split(/=/, $p[0]);
            $ID=$tmp[1];
            $NAME="null";
          }
        } else {
          my @i = grep { /id=/i } @attr;
          my @tmp=split(/=/,$i[0]);
          $ID=$tmp[1];
          if (my @n = grep { /name=/i } @attr) {
            my @tmp=split(/=/,$n[0]);
            $NAME=$tmp[1];
          } else {
            $NAME=$ID;
          }
        }
      }
      _dbexe("INSERT INTO $TABLE(track,chr,id,name,parent,type,pos,end,strand) VALUES('$TRACK','$CHR','$ID','$NAME','$PARENT','$TYPE',$START,$END,$STRAND)");
      }
    }

  close (IN);

  $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
  _print_time("complete");
}
exit;


### 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 track\n";
  print "tasuke_track.pl -c -t [track(name)] -f [GFF] -db [database] -u [user] -p [password]\n\n";
  print "Removing track\n";
  print "tasuke_track.pl -r -t [track(name)] -db [database] -u [user] -p [password]\n\n";
  print "Listing track\n";
  print "tasuke_track.pl -l -db [database] -u [user] -p [password]\n\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";
}

### Listing tracks ####################################
sub _lists {
  my $val;
  my @tracks;
  my $query = "SELECT distinct track FROM $TABLE order by seq";
  my $query_handle = $connect->prepare($query);
  $query_handle->execute();
  $query_handle->bind_columns(undef, \$val);
  while($query_handle->fetch()) {
    push @tracks, $val;
  }
  return @tracks;
}

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

