#!/usr/bin/perl -w

use strict;
use Getopt::Long;
use DBI;
use FindBin qw($Bin);
use lib "$Bin";
use CommonLibTASUKE;
use Data::Dumper;

my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $DO_ALL     = 0;
my $DO_VARIANT = 0;
my $DO_DEPTH   = 0;
my $DO_CUSTOM  = 0;
my $DO_SYSTEM  = 0;
my $CHECKONLY  = 0;

GetOptions(
	'db|database=s' => \$DB,
	'h|host=s'      => \$HOST,
	'u|user=s'      => \$USER,
	'p|password=s'  => \$PASS,
	'a|all'         => \$DO_ALL,
	'v|variant'     => \$DO_VARIANT,
	'd|depth'       => \$DO_DEPTH,
	'c|custom'      => \$DO_CUSTOM,
	's|system'      => \$DO_SYSTEM,
	'k|checkonly'   => \$CHECKONLY
);

if ( !$DB || !$USER ) {
        usage();
        _exit(1);
}

my $connect = DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS) or die "Can't connect to database: $DBI::errstr\n";

if(!$DO_ALL && !$DO_VARIANT && !$DO_DEPTH && !$DO_CUSTOM && !$DO_SYSTEM) {
	print STDERR "Please specify the target to be optimized(a/v/d/c/s)\n";
	_exit(1);
} elsif($DO_ALL) {
	$DO_VARIANT = 1;
	$DO_DEPTH   = 1;
	$DO_CUSTOM  = 1;
	$DO_SYSTEM  = 1;
}

my $lib = new CommonMysql();

my $accArrayref = getAccessions();
optimizeVariant($accArrayref) if($DO_VARIANT);
optimizeDepth($accArrayref) if($DO_DEPTH);
optimizeDepth($accArrayref, {'custom' => 1}) if($DO_CUSTOM);
optimizeSystem() if($DO_SYSTEM);

if($CHECKONLY) {
	print STDERR "> [This is CHECK-ONLY mode] Table optimization is not executed, only SQL was output.\n\n";
}
_exit(0);

sub getAccessions {
	my $sth = $connect->prepare("select * from sys_accession");
	$sth->execute();
	my @arrs;
	while(my $hashref = $sth->fetchrow_hashref()) {
		push @arrs, $hashref;
	}
	return \@arrs;
}
sub optimizeVariant {
	my ($accArrayref) = @_;

	my $commonMysql = new CommonMysql();
	if($commonMysql->modeIsPlusMultiVcf($connect)) {
		my $sql = "optimize table sys_variant";
		printf STDERR "0:\t%s\n", $sql;
		if(!$CHECKONLY) {
			my $sth = $connect->prepare($sql);
			$sth->execute();
			my $res = $sth->fetchrow_arrayref();
			print STDERR join("\t", @{$res}). "\n";
		}
	}

	my $loop = 1;
	my @tables = sort {$a->{'accession_db'} cmp $b->{'accession_db'}} @{$accArrayref};
	foreach my $hashref (sort {$a->{'accession_db'} cmp $b->{'accession_db'}} @{$accArrayref}) {
		my $sql = sprintf("optimize table %s_variant", $hashref->{'accession_db'});
		printf STDERR "%d:\t%s\n", $loop, $sql;
		if(!$CHECKONLY) {
			my $sth = $connect->prepare($sql);
			$sth->execute();
			my $res = $sth->fetchrow_arrayref();
			print STDERR join("\t", @{$res}). "\n";
		}
		$loop++;
	}
}
sub optimizeDepth {
	my ($accArrayref, $opt) = @_;

	my $tableSufifix = "_accession";
	if($opt) {
		$tableSufifix = "_accession_cstm" if(exists($opt->{'custom'}) && $opt->{'custom'});
	}

	my $loop = 1;
	foreach my $hashref (sort {$a->{'accession_db'} cmp $b->{'accession_db'}} @{$accArrayref}) {
		my $baseTable = sprintf("%s%s", $hashref->{'accession_db'}, $tableSufifix);
		optimizeSerialTable($baseTable, $loop);
		$loop++;
	}
}		
sub optimizeSerialTable {
	my($baseTable, $loop) = @_;

	my $table2ColMap = $lib->getTable2ColMap($connect, $DB, $baseTable);
	foreach my $table (sort {$a cmp $b} keys(%{$table2ColMap})) {
		my $sql = sprintf("optimize table %s", $table);
		printf STDERR "%d:\t%s\n", $loop, $sql;
		if(!$CHECKONLY) {
			my $sth = $connect->prepare($sql);
			$sth->execute();
			my $res = $sth->fetchrow_arrayref();
			print STDERR join("\t", @{$res}). "\n";
		}
	}
}
sub optimizeSystem {
	my @tables = (
		'id2eff',
		'sys_accession',
		'sys_chromosome',
		'sys_condition',
		'sys_phenotype',
		'sys_phenotype_expression',
		'sys_phenotype_gene',
		'sys_phenotype_prob',
		'sys_snpEff',
		'sys_snpblock',
		'sys_track'
	);
	my $loop = 1;
	foreach my $table (@tables) {
		my $sql = sprintf("optimize table %s", $table);
		printf STDERR "%d:\t%s\n", $loop, $sql;
		if(!$CHECKONLY) {
			my $sth = $connect->prepare($sql);
			$sth->execute();
			my $res = $sth->fetchrow_arrayref();
			print STDERR join("\t", @{$res}). "\n";
		}
		$loop++;
	}

	#sys_chromosome
	optimizeSerialTable('sys_fasta', $loop);
} 
sub _exit {
    my ($arg) = @_;
    my $code = 0;
    if(defined $arg){$code = $arg;}
    $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
    exit($code);
}
sub usage {
	print STDERR "Usage: tasuke_optimize_tables.pl -db [database] -u [user] -p [password] -a/-v/-d/-c/-s\n";
	print STDERR "Either required:\n";
	print STDERR "  -a : optimize all tables(Same as specifying \"-v -d -c -s\")\n";
	print STDERR "  -v : Optimize variant information tables\n";
	print STDERR "  -d : Optimize depth information tables\n";
	print STDERR "  -c : Optimize  general purpose track tables\n";
	print STDERR "  -s : Optimize other system-related tables\n";
	print STDERR "Other options:\n";
	print STDERR "  -h <remote host> : (default: localhost)\n";
	print STDERR "  -k : TEST mode. Check AccessionIDs, and output SQLs, but do not execute it.\n";
	print STDERR "\n";
}
