#!/usr/bin/perl -w

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

my $RESTORE = 0;
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $SAMPLE   = 'accession_ID';
my $ID = "";
my $RDBMS = '';
my $TABLESPACE = '';
my $FORCE = 0;

################################################################
## Checking parameters and database connection
################################################################
GetOptions( 'r|restore'     => \$RESTORE,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            'b|backend=s'      => \$RDBMS,
            's|space=s'   => \$TABLESPACE,
            'p|password=s'  => \$PASS,
            'c|force'       => \$FORCE
);

if ( !$DB || !$USER ) {
    _error_method();
    exit;
}
if(!$RDBMS){
    $RDBMS = 'mysql';
}

my $connect;
my $sqlGen = {};
if($RDBMS eq "oracle"){
    if(!$TABLESPACE){
        _error_method();
        exit;
    }
    $connect = DBI->connect('DBI:Oracle:host='.$HOST.';SERVICE_NAME='.$DB.';',$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
    if(!_check_exists_tablespace($TABLESPACE)){
        print "Tablespace: '$TABLESPACE' is not found.";
        _exit(1);
    }
    $sqlGen = new CommonSqlOracle();
}else{
    $connect= DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST, $USER, $PASS, {AutoCommit => 0})
    or die "Can't connect to database: $DBI::errstr\n";
    $sqlGen = new CommonMysql();
}

$| = 1;	# AutoFlush:On

if(!$sqlGen->modeIsLegacy($connect)) {
	print "DB has already been updated. ";
	if($FORCE) {
		print "Force update.\n";
	} else {
		print "If you want to force a re-update, add the \"-c\" option.\n";
		exit(0);
	}
}

my $val1 = $connect->selectrow_array("select table_name,column_name from information_schema.columns where column_name='other_1' and table_schema='$DB'");
if (!defined $val1) {
	print "Updating sys_accession table...\n";
	_dbexe("alter table sys_accession add other_1 longtext");
	_dbexe("alter table sys_accession add other_2 longtext");
	print "Done!\n";
} else {
	print "sys_accession table has already updated !\n";
}

my $val3 =  $connect->selectrow_array("SHOW TABLES LIKE 'sys_snpEff'");
if (defined $val3 ) {
	print "Updating snpEff information\n";
	_dbexe("truncate table sys_snpEff");

	my $sthCheckCol = $connect->prepare("select 1 from information_schema.columns where TABLE_NAME='sys_snpEff' and COLUMN_NAME='eff_type' and TABLE_SCHEMA='$DB'");
	$sthCheckCol->execute();
	my $colExist = $sthCheckCol->fetchrow_arrayref();
	if(!$colExist) {
		my $sql = "alter table sys_snpEff add eff_type varchar(8)";
		print "$sql\n";
		_dbexe($sql);
	} else {
		print "sys_snpEff table has already updated !\n";
	}
	if(!$colExist || $FORCE) {
		_dbexe($sqlGen->getInsertSysSnpeff());
	}
	print "done!\n"
}

my $val5 = $connect->selectrow_array("show tables like 'sys_phenotype'");
if ( ! defined $val5 ) {
	my $sqls = $sqlGen->getCreateTablesGwas("");
	foreach my $table (keys(%{$sqls})) {
		print "Create table $table\n";
		_dbexe($sqls->{$table});
		print "Done!\n";
	}
}

my $y;
if($FORCE) {
 $y = 1;
} else {
 print "> Update the max impact level of SnpEFF in Variant for each accession? [y|n] ";
 $y = confirmYN();
}
if($y) {
	my $commitUnit = 10000; 
	my $dbMode = $sqlGen->getDbMode($connect);

	my $colGrpBackup = "EFF_GRP_BACKUP";

	my $sqlGetAccs = "select accession_id,accession_db from sys_accession";
	my $sthGetAccs = $connect->prepare($sqlGetAccs);

	my $sqlCheckCol = "select 1 from information_schema.columns where TABLE_NAME=? and COLUMN_NAME='$colGrpBackup' and TABLE_SCHEMA='$DB'";
	my $sthCheckCol = $connect->prepare($sqlCheckCol);

	$sthGetAccs->execute();
	my $i = 0;
	while(my $arrrefGetAccs = $sthGetAccs->fetchrow_arrayref()) {
		$i++;
		my $accId = $arrrefGetAccs->[0];
		my $accDb = $arrrefGetAccs->[1];
		my $table = $accDb. "_variant";
		$sthCheckCol->execute($table);
		if($sthCheckCol->fetchrow_arrayref()) {
			print "$i:$accId($accDb) has already been updated. ";
			if(!$FORCE) {
				print "Skip.\n";
				next;
			} else {
				print "Force an update.\n";
			}
		} else {
			print "$i:$accId($accDb) Add a column: $colGrpBackup\n";
			_dbexe("alter table $table add $colGrpBackup tinyint(4)");
		}
		my $sthGet;
		if($dbMode eq 'mvcf') {
			# Forcing an EFF_GRP update in MVCF mode (rather than updating the legacy DB schema).
			$sthGet = $connect->prepare("SELECT v.seq,EFF,ALT,EFF_GRP,$colGrpBackup FROM sys_variant s,$table v where s.chr=v.chr and s.pos_vcf=v.pos_vcf");
		} else {
			$sthGet = $connect->prepare("SELECT seq,EFF,ALT,EFF_GRP,$colGrpBackup FROM $table");
		}
		my $sthUpdate = $connect->prepare("Update $table set EFF_GRP=?,$colGrpBackup=? where seq=?");

		my $progress = 0;
		$sthGet->execute();
		while(my $arrrefGet = $sthGet->fetchrow_arrayref()) {
			my ($seq, $eff, $alt, $oldLevel, $bkLevel) = @{$arrrefGet};
			my $level = $sqlGen->getMaxEffGrp($eff, $alt);
#printf STDERR "%d %s\t%d -> %d\n", $seq, $accId, $oldLevel, $level;
			$oldLevel = $bkLevel if(defined($bkLevel));	# It has already been backed up before so it will not be changed. 
			$sthUpdate->execute($level, $oldLevel, $seq);
			$progress++;
			if($progress % $commitUnit == 0) {
				printf "[%s] Updated records: %s\n", $accId, $progress;
				$connect->commit();
			}
		}
		printf "[%s] Updated records: %s\n", $accId, $progress;
		$connect->commit();
		$sthGet->finish();
		$sthUpdate->finish();
	}
	$sthCheckCol->finish();
	$sthGetAccs->finish();
}

$| = 0;	# AutoFlush:Off

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

sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute() or warn "Excute error: $DBI::errstr\n";
}
sub confirmYN {
	my $y = 0;
                while (defined(my $line = <STDIN>)){
                    chomp($line);
                    if ( $line eq "y" || $line eq "Y" ) {
			$y = 1;
                        last;
                    } elsif ( $line eq "n" || $line eq "N" ) {
                        print "finished.\n";
                        last;
                    } else {
                        print "> [y|n] ";
                    }
                }
	return $y;
}
sub _error_method {}
