#!/usr/bin/env perl
################################################################################
# WeBWorK Online Homework Delivery System
# Copyright © 2000-2006 The WeBWorK Project, http://openwebwork.sf.net/
# $CVSHeader: webwork2/bin/wwdb_upgrade,v 1.11 2007/03/27 17:04:01 glarose Exp $
# 
# This program is free software; you can redistribute it and/or modify it under
# the terms of either: (a) the GNU General Public License as published by the
# Free Software Foundation; either version 2, or (at your option) any later
# version, or (b) the "Artistic License" which comes with this package.
# 
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See either the GNU General Public License or the
# Artistic License for more details.
################################################################################

use strict;
use warnings;
use Getopt::Std;
use DBI;
use Data::Dumper;

BEGIN {
	die "WEBWORK_ROOT not found in environment.\n"
		unless exists $ENV{WEBWORK_ROOT};
}

use lib "$ENV{WEBWORK_ROOT}/lib";
use WeBWorK::CourseEnvironment;
use WeBWorK::Utils qw/runtime_use/;
use WeBWorK::Utils::CourseManagement qw/listCourses/;

our ($opt_v);
getopts("v");

if ($opt_v) {
	$| = 1;
	*verbose = sub { print STDERR @_ };
} else {
	*verbose = sub {};
}

# global variables, hah hah.
my ($dbh, %sql_tables);

################################################################################

my $i = -1;
our @DB_VERSIONS;

$DB_VERSIONS[++$i]{desc} = "is the initial version of database, identical to database structure in WeBWorK 2.2.x.";

$DB_VERSIONS[++$i]{desc} = "adds dbupgrade table to facilitate automatic database upgrades.";
$DB_VERSIONS[  $i]{global_code} = sub {
	$dbh->do("CREATE TABLE `dbupgrade` (`name` VARCHAR(255) NOT NULL PRIMARY KEY, `value` TEXT)");
	$dbh->do("INSERT INTO `dbupgrade` (`name`, `value`) VALUES (?, ?)", {}, "db_version", 1);
	$sql_tables{dbupgrade} = ();
};

$DB_VERSIONS[++$i]{desc} = "adds problems_per_page field to set and set_user tables of each course.";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `problems_per_page` INT")
		if exists $sql_tables{"${course}_set"};
	$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `problems_per_page` INT")
		if exists $sql_tables{"${course}_set_user"};
};

$DB_VERSIONS[++$i]{desc} = "adds depths table to keep track of dvipng depth information.";
$DB_VERSIONS[  $i]{global_code} = sub {
	$dbh->do("CREATE TABLE depths (md5 CHAR(33) NOT NULL, depth SMALLINT, PRIMARY KEY (md5))");
	$sql_tables{depths} = ();
};

$DB_VERSIONS[++$i]{desc} = "changes type of key timestamp field to BIGINT";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_key"};
	$dbh->do("ALTER TABLE `${course}_key` CHANGE COLUMN `timestamp` `timestamp` BIGINT");
};

$DB_VERSIONS[++$i]{desc} = "changes type of problem_user status field to FLOAT";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_problem_user"};
	$dbh->do("UPDATE `${course}_problem_user` SET `status`=NULL WHERE `status`=''");
	$dbh->do("ALTER TABLE `${course}_problem_user` CHANGE COLUMN `status` `status` FLOAT");
};

$DB_VERSIONS[++$i]{desc} = "changes types of alphanumeric keyfields to TINYBLOB NOT NULL";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	$dbh->do("ALTER TABLE `${course}_user` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_user"};
	$dbh->do("ALTER TABLE `${course}_password` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_password"};
	$dbh->do("ALTER TABLE `${course}_permission` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_permission"};
	$dbh->do("ALTER TABLE `${course}_key` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_key"};
	$dbh->do("ALTER TABLE `${course}_set` CHANGE COLUMN `set_id` `set_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_set"};
	$dbh->do("ALTER TABLE `${course}_problem` CHANGE COLUMN `set_id` `set_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_problem"};
	$dbh->do("ALTER TABLE `${course}_set_user` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_set_user"};
	$dbh->do("ALTER TABLE `${course}_set_user` CHANGE COLUMN `set_id` `set_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_set_user"};
	$dbh->do("ALTER TABLE `${course}_problem_user` CHANGE COLUMN `user_id` `user_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_problem_user"};
	$dbh->do("ALTER TABLE `${course}_problem_user` CHANGE COLUMN `set_id` `set_id` TINYBLOB NOT NULL")
		if exists $sql_tables{"${course}_problem_user"};
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for user table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_user"};
	$dbh->do("ALTER TABLE `${course}_user` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_user` ADD UNIQUE KEY (`user_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for password table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_password"};
	$dbh->do("ALTER TABLE `${course}_password` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_password` ADD UNIQUE KEY (`user_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for permission table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_permission"};
	$dbh->do("ALTER TABLE `${course}_permission` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_permission` ADD UNIQUE KEY (`user_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for key table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_key"};
	$dbh->do("ALTER TABLE `${course}_key` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_key` ADD UNIQUE KEY (`user_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for set table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_set"};
	$dbh->do("ALTER TABLE `${course}_set` DROP KEY `set_id`");
	$dbh->do("ALTER TABLE `${course}_set` ADD UNIQUE KEY (`set_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for problem table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_problem"};
	$dbh->do("ALTER TABLE `${course}_problem` DROP KEY `set_id`");
	$dbh->do("ALTER TABLE `${course}_problem` ADD UNIQUE KEY (`set_id`(255), `problem_id`)");
	$dbh->do("ALTER TABLE `${course}_problem` DROP KEY `problem_id`");
	$dbh->do("ALTER TABLE `${course}_problem` ADD KEY (`problem_id`)");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for set_user table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_set_user"};
	$dbh->do("ALTER TABLE `${course}_set_user` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_set_user` ADD UNIQUE KEY (`user_id`(255), `set_id`(255))");
	$dbh->do("ALTER TABLE `${course}_set_user` DROP KEY `set_id`");
	$dbh->do("ALTER TABLE `${course}_set_user` ADD KEY (`set_id`(255))");
};

$DB_VERSIONS[++$i]{desc} = "fixes KEY length, adds UNIQUE KEY for problem_user table";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_problem_user"};
	$dbh->do("ALTER TABLE `${course}_problem_user` DROP KEY `user_id`");
	$dbh->do("ALTER TABLE `${course}_problem_user` ADD UNIQUE KEY (`user_id`(255), `set_id`(255), `problem_id`)");
	$dbh->do("ALTER TABLE `${course}_problem_user` DROP KEY `set_id`");
	$dbh->do("ALTER TABLE `${course}_problem_user` ADD KEY (`set_id`(255), `problem_id`)");
	$dbh->do("ALTER TABLE `${course}_problem_user` DROP KEY `problem_id`");
	$dbh->do("ALTER TABLE `${course}_problem_user` ADD KEY (`problem_id`)");
};

$DB_VERSIONS[++$i]{desc} = "changes psvn index from PRIMARY KEY to UNIQUE KEY";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	return unless exists $sql_tables{"${course}_set_user"};
	$dbh->do("ALTER TABLE `${course}_set_user` ADD UNIQUE KEY (`psvn`)");
	$dbh->do("ALTER TABLE `${course}_set_user` DROP PRIMARY KEY");
};

$DB_VERSIONS[++$i]{desc} = "adds hide_score and hide_work fields to set and set_user";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `hide_score` ENUM('0','1')");
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `hide_work` ENUM('0','1')");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `hide_score` ENUM('0','1')");
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `hide_work` ENUM('0','1')");
	}
};

$DB_VERSIONS[++$i]{desc} = "updates hide_score and hide_work in set and set_user tables to allow more (and more descriptive) possible values";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` MODIFY COLUMN `hide_score` ENUM('0','1','2')");
		$dbh->do("ALTER TABLE `${course}_set` MODIFY COLUMN `hide_work` ENUM('0','1','2')");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_score` ENUM('0','1','2')");
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_work` ENUM('0','1','2')");
	}
};

$DB_VERSIONS[++$i]{desc} = "adds time_limit_cap field to set and set_user tables";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `time_limit_cap` ENUM('0','1')");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `time_limit_cap` ENUM('0','1')");
	}
};

$DB_VERSIONS[++$i]{desc} = "updates hide_score and hide_work in set and set_user tables to have more descriptive values, set default values";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` MODIFY COLUMN `hide_score` ENUM('N','Y','BeforeAnswerDate') DEFAULT 'N'");
		$dbh->do("ALTER TABLE `${course}_set` MODIFY COLUMN `hide_work` ENUM('N','Y','BeforeAnswerDate') DEFAULT 'N'");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_score` ENUM('N','Y','BeforeAnswerDate') DEFAULT 'N'");
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_work` ENUM('N','Y','BeforeAnswerDate') DEFAULT 'N'");
	}
};

$DB_VERSIONS[++$i]{desc} = "adds locations, location_addresses, set_locations and set_locations_user tables to database, and add restrict_ip to set and set_user.";
$DB_VERSIONS[  $i]{global_code} = sub {
	$dbh->do("CREATE TABLE locations (location_id TINYBLOB NOT NULL, description TEXT, PRIMARY KEY (location_id(1000)))");
	$dbh->do("CREATE TABLE location_addresses (location_id TINYBLOB NOT NULL, ip_mask TINYBLOB NOT NULL, PRIMARY KEY (location_id(500),ip_mask(500)))");
};
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;
	
	$dbh->do("CREATE TABLE `${course}_set_locations` (set_id TINYBLOB NOT NULL, location_id TINYBLOB NOT NULL, PRIMARY KEY (set_id(500),location_id(500)))");
	$dbh->do("CREATE TABLE `${course}_set_locations_user` (set_id TINYBLOB NOT NULL, user_id TINYBLOB NOT NULL, location_id TINYBLOB NOT NULL, PRIMARY KEY (set_id(300),user_id(300),location_id(300)))");

	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `restrict_ip` enum('No','RestrictTo','DenyFrom') DEFAULT 'No'");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `restrict_ip` enum('No','RestrictTo','DenyFrom')");
	}
};

$DB_VERSIONS[++$i]{desc} = "updates defaults for hide_work and hide_score in set_user tables.";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;

	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_score` ENUM('N','Y','BeforeAnswerDate')");
		$dbh->do("ALTER TABLE `${course}_set_user` MODIFY COLUMN `hide_work` ENUM('N','Y','BeforeAnswerDate')");
	}
};

$DB_VERSIONS[++$i]{desc} = "adds relax_restrict_ip, hide_problem_score columns to set and set_user tables.";
$DB_VERSIONS[  $i]{course_code} = sub {
	my $course = shift;

	if ( exists $sql_tables{"${course}_set"} ) {
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `relax_restrict_ip` ENUM('No','AfterAnswerDate','AfterVersionAnswerDate') DEFAULT 'No'");
		$dbh->do("ALTER TABLE `${course}_set` ADD COLUMN `hide_score_by_problem` ENUM('N','Y') DEFAULT 'N'");
	}
	if ( exists $sql_tables{"${course}_set_user"} ) {
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `relax_restrict_ip` ENUM('No','AfterAnswerDate','AfterVersionAnswerDate')");
		$dbh->do("ALTER TABLE `${course}_set_user` ADD COLUMN `hide_score_by_problem` ENUM('N','Y')");
	}
};

our $THIS_DB_VERSION = $i;

################################################################################

my $ce = WeBWorK::CourseEnvironment->new({
	webwork_dir => $ENV{WEBWORK_ROOT},
});

my @ww_courses = listCourses($ce);

$dbh = DBI->connect(
	$ce->{database_dsn},
	$ce->{database_username},
	$ce->{database_password},
	{
		PrintError => 0,
		RaiseError => 1,
	},
);

{
	verbose("Obtaining dbupgrade lock...\n");
	my ($lock_status) = $dbh->selectrow_array("SELECT GET_LOCK('dbupgrade', 10)");
	if (not defined $lock_status) {
		print "Couldn't obtain lock because an error occurred.\n";
		exit 2;
	}
	if ($lock_status) {
		verbose("Got lock.\n");
	} else {
		print "Timed out while waiting for lock.\n";
		exit 2;
	}
}

%sql_tables = get_sql_tables();

my $db_version = exists $sql_tables{dbupgrade} ? get_db_version() : 0;

if (not defined $db_version) {
	print "Failed to get db_version -- can't continue.\n";
	exit 1;
}

verbose("Initial db_version is $db_version\n");

if ($db_version > $THIS_DB_VERSION) {
	print "db_version is $db_version, but the current database version is only $THIS_DB_VERSION. This database was probably used with a newer version of WeBWorK.\n";
	exit;
}

while ($db_version < $THIS_DB_VERSION) {
	$db_version++;
	unless (upgrade_to_version($db_version)) {
		print "\nUpgrading from version ".($db_version-1)." to $db_version failed.\n\n";
		unless (ask_permission("Ignore this error and go on to the next version?", 0)) {
			exit 3;
		}
	}
	set_db_version($db_version);
}

print "\nDatabase is up-to-date at version $db_version.\n";

END {
	verbose("Releasing dbupgrade lock...\n");
	my ($lock_status) = $dbh->selectrow_array("SELECT RELEASE_LOCK('dbupgrade')");
	if (not defined $lock_status) {
		print "Couldn't release lock because the lock does not exist.\n";
		exit 2;
	}
	if ($lock_status) {
		verbose("Released lock.\n");
	} else {
		print "Couldn't release lock because the lock is not held by this thread.\n";
		exit 2;
	}
}

################################################################################

sub get_sql_tables {
	my $sql_tables_ref = $dbh->selectcol_arrayref("SHOW TABLES");
	my %sql_tables; @sql_tables{@$sql_tables_ref} = ();
	
	return %sql_tables;
}

sub get_db_version {
	my $vers_value_should_be = "This value should always be a positive integer.";
	my $vers_stop_now = "You should stop now and take a closer look.";
	
	my @record = $dbh->selectrow_array("SELECT `value` FROM `dbupgrade` WHERE `name`='db_version'");
	if (@record) {
		my $db_version = $record[0];
		if (not defined $db_version) {
			print "'db_version' exists, but it has a NULL value. $vers_value_should_be $vers_stop_now\n";
			return;
		} elsif ($db_version !~ /^-?\d+$/) {
			print "'db_version' is set to the non-numeric value '$db_version'. $vers_value_should_be $vers_stop_now\n";
			return;
		} elsif ($db_version < 0) {
			print "'db_version' is set to the negative value '$db_version'. $vers_value_should_be $vers_stop_now\n";
			return;
		} elsif ($db_version == 0) {
			print "'db_version' is set 0, which is reserved to indicate a pre-automatic-upgrade version. $vers_value_should_be $vers_stop_now\n";
			return;
		} else {
			# db_version is positive! yay!
			return $db_version;
		}
	} else {
		print "The 'dbupgrade' table exists, but doesn't contain a 'db_version' setting. $vers_stop_now\n";
		return;
	}
}

sub set_db_version {
	my $vers = shift;
	$dbh->do("UPDATE `dbupgrade` SET `value`=? WHERE `name`='db_version'", {}, $vers);
}

sub upgrade_to_version {
	my $vers = shift;
	my %info = %{$DB_VERSIONS[$vers]};
	
	print "\nUpgrading database from version " . ($vers-1) . " to $vers...\n";
	my $desc = $info{desc} || "has no description.";
	print "(Version $vers $desc)\n";
	
	if (exists $info{global_code}) {
		eval { $info{global_code}->() };
		if ($@) {
			print "\nAn error occured while running the system upgrade code for version $vers:\n";
			print "$@";
			return 0 unless ask_permission("Ignore this error and keep going?", 0);
		}
	}
	
	if (@ww_courses and exists $info{course_code}) {
		foreach my $curr_course (@ww_courses) {
			eval { $info{course_code}->($curr_course) };
			if ($@) {
				print "\nAn error occured while running the course upgrade code for version $vers on course $curr_course:\n";
				print "$@";
				next if ask_permission("Ignore this error and go on to the next course?", 0);
			}
		}
	}
	
	print "Done.\n";
	return 1;
}

################################################################################

sub ask_permission {
	my ($prompt, $default) = @_;
	
	$default = 1 if not defined $default;
	my $options = $default ? "[Y/n]" : "[y/N]";
	
	while (1) {
		print "$prompt $options ";
		my $resp = <STDIN>;
		chomp $resp;
		return $default if $resp eq "";
		return 1 if lc $resp eq "y";
		return 0 if lc $resp eq "n";
		$prompt = 'Please enter "y" or "n".';
	}
}