# FILE: adm-db.pl
# DESCRIPTION: Administrator's Interface to Manage Database
#-------------------------------------------------------------------------------
# DISCUS COPYRIGHT NOTICE
#
# Discus is copyright (c) 2002 by DiscusWare, LLC, all rights reserved.
# The use of Discus is governed by the Discus License Agreement which is
# available from the Discus WWW site at:
#    http://www.discusware.com/discus/license
#
# Pursuant to the Discus License Agreement, this copyright notice may not be
# removed or altered in any way.
#-------------------------------------------------------------------------------

use strict;
use vars qw($GLOBAL_OPTIONS $DCONF $PARAMS);

###
### DATABASE_admin
###
### Runs the Database Manager
###

sub DATABASE_admin {
	my ($FORMref) = @_;
	my $result = check_password($FORMref->{username}, undef, { type_required => 'moderator' }, $FORMref->{'COOKIE'});
	bad_login( { bad_database => 1 } ) if scalar(@{ $result }) == 0;
	bad_login( { superuser_required => 1 } ) if $result->[0]->{user} ne $DCONF->{superuser};
	$PARAMS->{no_db_connect} = 1;
	dreq("dbint");
	$PARAMS->{no_db_connect} = 0;
	touch_createfile("$DCONF->{admin_dir}/data/db.txt");
	if ($FORMref->{'action'} eq "database-import") {
		database_import($FORMref->{imports}, $FORMref, $result);
		database_manager($FORMref, $result->[0]->{user}, { what => 2, menu => 2 });
	}
	if ($FORMref->{action} eq "database-imp") {
		dreq("sql-imex-PRO");
		sql_import_handler($FORMref, $result, undef);
	}
	if ($FORMref->{'action'} eq "database-export") {
		database_export($FORMref->{exports}, $FORMref, $result);
		database_manager($FORMref, $result->[0]->{user}, { what => 4, menu => 3 });
	}
	if ($FORMref->{action} eq "database-exp") {
		dreq("sql-imex-PRO");
		sql_export_handler($FORMref, $result, undef);
	}
	if ($FORMref->{'action'} eq "database-setup") {
		database_save_settings($FORMref->{database}, $FORMref->{dbusername}, $FORMref->{dbpassword}, $FORMref->{dbprefix}, $FORMref->{dbsocket});
		database_manager($FORMref, $result->[0]->{user}, { what => 1 });
	}
	if ($FORMref->{'action'} eq "database-init") {
		my $success = database_initialize($FORMref->{init});
		database_manager($FORMref, $result->[0]->{user}, { what => $success, menu => 1 });
	}
	if ($FORMref->{'action'} eq "database") {
		database_manager($FORMref, $result->[0]->{user});
	}
	if ($FORMref->{'action'} eq "database-toggle") {
		dreq("adm-opts");
		if ($FORMref->{source} == 1) {
			$PARAMS->{dbh} = db_connect();
		}
		options_save({ database => 0+$FORMref->{source} });
		database_manager($FORMref, $result->[0]->{user}, { toggle => 1 });
	}
}

###
### database_get_tables
###
### Methods to work around broken $dbh->tables() on some platforms.  Sigh.
###

sub database_get_tables {
	my ($dbh, $prefix) = @_;
	my %found = map { $_, 1 } $dbh->tables;
	my @out = ();
	my @possible = map { join("", $prefix, $_) } database_possible_tables();
	foreach my $poss (@possible) {
		if (defined $found{$poss}) {
			push @out, $poss;
			next;
		}
		my $str = "SELECT COUNT(*) FROM $poss;";
		my $sth = $dbh->prepare($str) or do { next; };
		$sth->execute() or do { next; };
		push @out, $poss;
	}
	return @out;
}

###
### database_stats
###
### Gets stats from the MySQL database
###

sub database_stats {
	my ($subst, $dbh) = @_;
	return undef if ! defined $dbh;
	my $query02 = "SHOW TABLE STATUS";
	my @k = ();
	if (my $sth2 = $dbh->prepare($query02)) {
		$sth2->execute();
		while (my $k = $sth2->fetchrow_hashref()) {
			my $pct = $k->{Max_data_length} == 0 ? 1 : $k->{Data_length} / $k->{Max_data_length};
			$pct = sprintf("%2.4f", $pct*100);
			$k->{percent} = $pct;
			push @k, \%{ $k };
		}
		$sth2->finish();
	}
	@k = sort { case_lower($a->{Name}) cmp case_lower($b->{Name}) } @k;
	$subst->{stables} = \@k;
	my $query03 = "SHOW VARIABLES;";
	if (my $sth3 = $dbh->prepare($query03)) {
		$sth3->execute();
		while (my $k = $sth3->fetchrow_arrayref()) {
			next if $k->[0] ne "version";
			$subst->{general}->{mysql_version} = $k->[1];
			last;
		}
		$sth3->finish();
	}
	return $subst;
}

###
### database_initialize
###
### Initializes a database following request from Database Manager.
###

sub database_initialize {
	my ($init) = @_;
	undef my $hashref;
	foreach my $xz (split(/,/, $init)) {
		$hashref->{$xz} = 1;
	}
	if ($GLOBAL_OPTIONS->{'database'} == 1) {
		error_message("Initialization Error", "Cannot initialize table &quot;passwd&quot; when database is being used for authentication!  Deselect this initialization or toggle off the database!", 0, 1) if $hashref->{passwd};
	}
	my ($dbh2, $error) = db_connect(2);
	database_create_tables($dbh2, $hashref);
	$dbh2->disconnect();
	return 3;
}

###
### database_import
###
### Imports values from flat text files into database.
###

sub database_import {
	my ($import_list, $FORMref, $result) = @_;
	my %imp = map { $_, 1 } split(/,/, $import_list);
	my $dbh = db_connect();
	if ($imp{counters}) {
		dreq("posting");
		db_sql_query({ ID => 1, value => 100+get_number() }, "counters", "replace", undef, $dbh);
		db_sql_query({ ID => 2, value => 100+get_postindex() }, "counters", "replace", undef, $dbh);
		delete $imp{counters};
	}
	if (scalar(keys(%imp)) == 0) {
		$dbh->disconnect();
		return 1;
	}
	dreq("sql-imex-PRO");
	return sql_import_handler($FORMref, $result, $dbh, \%imp);
}

###
### database_export
###
### Exports values to flat text files from the database.
###

sub database_export {
	my ($import_list, $FORMref, $result) = @_;
	my %imp = map { $_, 1 } split(/,/, $import_list);
	if ($imp{counters}) {
		my $r1 = db_sql_query({}, "counters", "select", { ID => 1 });
		my @z1 = (); $z1[0] = join("", 100 + $r1->[0]->{value}, "\n");
		writefile("$DCONF->{admin_dir}/data.txt", \@z1, "database_export", { no_lock => 1, no_unlock => 1 });
		my $r2 = db_sql_query({}, "counters", "select", { ID => 2 });
		my @z2 = (); $z2[0] = join("", 100 + $r2->[0]->{value}, "\n");
		writefile("$DCONF->{admin_dir}/postindex.txt", \@z2, "database_export", { no_lock => 1, no_unlock => 1 });
		delete $imp{counters};
	}
	return 1 if scalar(keys(%imp)) == 0;
	dreq("sql-imex-PRO");
	return sql_export_handler($FORMref, $result, \%imp);
}
###
### database_db_param
###
### Reads database parameters
###

sub database_db_param {
	undef my $db_param;
	my $db = readfile("$DCONF->{admin_dir}/data/db.txt", "DATABASE_admin", { no_lock => 1, no_unlock => 1, zero_ok => 1} );
	foreach my $x (@{ $db }) {
		if ($x =~ m|^(\w+)=(.*)|) {
			$db_param->{$1} = $2;
		}
	}
	return $db_param;
}

###
### database_possible_tables
###
### Tables that Discus uses
###

sub database_possible_tables {
	my @possible = ('counters', 'locks');
	push @possible, ('users', 'log', 'passwd', 'search') if $DCONF->{pro};
	push @possible, ('tree') if $DCONF->{pro} && $DCONF->{tree_as_sql};
	return @possible;
}

###
### database_manager
###
### Database Manager interface

sub database_manager {
	my ($FORMref, $username, $stuff) = @_;
	undef my $subst;
	$subst->{stuff} = $stuff;
	$subst->{db_param} = database_db_param();
	$subst->{general}->{username} = $username;
	my ($dbh, $error) = db_connect(2);
	my @tables = map { join("", $PARAMS->{db_prefix}, $_) } database_possible_tables();
	my %h = map { $_, 1 } @tables;
	if (defined($dbh) && $subst->{db_param}->{database} =~ /\S/) {
		$subst->{'connect'}->{'status'} = 1;
		my $table_ctr = 0;
		my $q =quotemeta(trim($subst->{db_param}->{prefix}));
		foreach my $xy (database_get_tables($dbh, $PARAMS->{db_prefix})) {
			next if ! $h{$xy};
			my $xy2 = $xy =~ m|^$q\_?(.+)| ? $1 : $xy;
			$table_ctr ++;
			$subst->{'exists'}->{$xy2} = 1;
			my $str = "SELECT COUNT(*) FROM $xy;";
			my $sth = $dbh->prepare($str) || error_message("SQL error", "Could not prepare [$str]:" . $dbh->errstr);
			$sth->execute() || error_message("SQL error", "Could not execute [$str]: " . $dbh->errstr);
			my $rf = $sth->fetchrow_hashref();
			$subst->{'count'}->{$xy2} = $rf->{'COUNT(*)'};
		}
		$subst = database_stats($subst, $dbh) if $FORMref->{menu} == 5;
		$subst->{'general'}->{'ok_to_toggle_on'} = 0;
		if ($table_ctr == scalar @tables) {
			$subst->{'general'}->{'ok_to_toggle_on'} = 1;
			if ($DCONF->{pro}) {
				my $stha = $dbh->prepare("SELECT pass FROM $PARAMS->{db_prefix}passwd WHERE user = ?");
				if ($stha->execute($DCONF->{superuser})) {
					my $j = $stha->fetchrow_hashref();
					$subst->{'general'}->{'ok_to_toggle_on'} = 2 if $j->{pass} eq "";
					$stha->finish();
				} else {
					$subst->{'general'}->{'ok_to_toggle_on'} = 2;
				}
			}
		}
		$dbh->disconnect();
	} elsif (defined $dbh) {
		$subst->{'general'}->{'ok_to_toggle_on'} = 0;
		$subst->{'connect'}->{'status'} = 2;
	} else {
		$subst->{'general'}->{'ok_to_toggle_on'} = 0;
		$subst->{'connect'}->{'status'} = 0;
		$subst->{'connect'}->{'message'} = $error;
	}
	$subst->{'general'}->{'url'} = "$PARAMS->{cgiurl}?action=database&username=$username";
	$subst->{'general'}->{'menu'} = (defined $stuff->{menu} ? $stuff->{menu} : $FORMref->{'menu'});
	screen_out("database", $subst);
}


###
### database_create_tables
###
### Creates tables in a database, initializing it for first use.
###

sub database_create_tables {
	my ($dbh, $tables_do) = @_;
	my @tables = database_get_tables($dbh, $PARAMS->{db_prefix});
	my $existing_table = {};
	my $m = quotemeta($PARAMS->{db_prefix});
	foreach my $x (@tables) {
		my $k = $x;
		$k =~ s/^$m//;
		$existing_table->{$k} = 1;
	}
	if ($tables_do->{users} || $tables_do->{passwd}) {
		my $statement = "CREATE TABLE $PARAMS->{db_prefix}users (
			user VARCHAR(100) PRIMARY KEY,
			pass TEXT,
			email TEXT,
			fullname TEXT,
			edit INT,
			notify TEXT,
			ctime INT,
			stime VARCHAR(40),
			atime VARCHAR(40),
			groups TEXT,
			picture TEXT,
			prefs TEXT,
			favorites TEXT,
			signature TEXT,
			status INT,
			posts INT,
			idnum INT,";
		for (my $i = 1; $i <= 20; $i++) {
			$statement .= "personal$i TEXT,\n";
		}
		for (my $i = 1; $i <= 10; $i++) {
			$statement .= "custom$i TEXT,\n";
			$statement .= "customdesc$i TEXT,\n";
		}
		$statement =~ s/,\n$//;
		$statement .= ");";
		if ($tables_do->{users}) {
			db_query("DROP TABLE $PARAMS->{db_prefix}users;", $dbh, 1) if ($existing_table->{users});
			db_query($statement, $dbh, 0);
		}
		$statement =~ s/CREATE TABLE $PARAMS->{db_prefix}users/CREATE TABLE $PARAMS->{db_prefix}passwd/;
		if ($tables_do->{passwd}) {
			db_query("DROP TABLE $PARAMS->{db_prefix}passwd;", $dbh, 1) if ($existing_table->{passwd});
			db_query($statement, $dbh, 0);
		}
	}
	if ($tables_do->{tree}) {
		my $lstatement = "CREATE TABLE $PARAMS->{db_prefix}tree (
			page INT PRIMARY KEY NOT NULL,
			level INT,
			topic INT,
			parent INT,
			name TEXT,
			param TEXT,
			islink INT,
			url TEXT,
			posts INT,
			subs INT,
			properties TEXT,
			originator TEXT,
			last_poster TEXT,
			lastmod INT,
			post_list TEXT,
			onpage_order INT,
			overall_order INT
		);";
		db_query("DROP TABLE $PARAMS->{db_prefix}tree;", $dbh, 1) if ($existing_table->{tree});
		db_query($lstatement, $dbh, 0);
	}
	if ($tables_do->{log}) {
		my $lstatement = "CREATE TABLE $PARAMS->{db_prefix}log (
			postnum INT PRIMARY KEY NOT NULL,
			postby TEXT,
			posttime INT,
			posttopic INT,
			postpage INT,
			postip VARCHAR(20),
			posthost VARCHAR(100),
			firstfew TEXT,
			author TEXT);";
		db_query("DROP TABLE $PARAMS->{db_prefix}log;", $dbh, 1) if ($existing_table->{log});
		db_query($lstatement, $dbh, 0);
	}
	if ($tables_do->{search}) {
		my $sstatement = "CREATE TABLE $PARAMS->{db_prefix}search (
			postnum INT PRIMARY KEY NOT NULL,
			posttopic INT,
			postpage INT,
			posttime INT,
			text TEXT);";
		db_query("DROP TABLE $PARAMS->{db_prefix}search;", $dbh, 1) if ($existing_table->{search});
		db_query($sstatement, $dbh, 0);
	}
	if ($tables_do->{counters}) {
		my $sstatement = "CREATE TABLE $PARAMS->{db_prefix}counters (
			ID INT PRIMARY KEY NOT NULL,
			value INT);";
		db_query("DROP TABLE $PARAMS->{db_prefix}counters;", $dbh, 1) if ($existing_table->{counters});
		db_query($sstatement, $dbh, 0);
		db_sql_query([{ ID => 1, value => 1 }, { ID => 2, value => 1 }], "counters", "insert", undef, $dbh);
	}
	if ($tables_do->{locks}) {
		my $sstatement = "CREATE TABLE $PARAMS->{db_prefix}locks (
			filename VARCHAR(100) PRIMARY KEY NOT NULL,
			processid VARCHAR(20),
			timecreate INT);";
		db_query("DROP TABLE $PARAMS->{db_prefix}locks;", $dbh, 1) if ($existing_table->{locks});
		db_query($sstatement, $dbh, 0);
	}
}

###
### database_save_settings
###
### Saves your database name, username, and password into a file.
###

sub database_save_settings {
	my ($database, $username, $password, $prefix, $sockets) = trim( @_ );
	$prefix =~ s/\s+/_/g;
	$PARAMS->{db_prefix} = join("", $prefix, "_");
	my @db = ("database=$database\n", "username=$username\n", "password=$password\n", "prefix=$prefix\n", "force_socket=$sockets\n");
	writefile("$DCONF->{admin_dir}/data/db.txt", \@db, "save_db_settings");
}

1;
