<?php
///////////////////////////////////////////////////////////////////////////////////////////////////////
// BotBanish 7.1.06
// Randem Systems: https://randemsystems.support
// BotBanish Database Change Structure Subroutines
// Date: 04/09/2026
//
///////////////////////////////////////////////////////////////////////////////////////////////////////

	// If we are called via a POST / GET, perform database or table dump operations otherwise we were called by an INCLUDE / REQUIRE call
	// in that case we just load the functions that will be needed so they can be called

	if (isset($_SERVER['REQUEST_URI']) && strpos($_SERVER['REQUEST_URI'], 'BotBanish_AdjustDatabase.php') !== false)
		BotBanish_DumpIt();

function BotBanish_DumpIt() {

	global $BotBanishSettings;

	if (isset($BotBanishSettings['BOTBANISH_SERVER']) && ($BotBanishSettings['BOTBANISH_SERVER'])) {

		$self = substr($_SERVER['PHP_SELF'], strripos($_SERVER['PHP_SELF'], '/') + 1);
		$filename = realpath(str_ireplace($self, '../Settings_Client.php', $_SERVER['SCRIPT_FILENAME']));
		require_once $filename;

		if (isset($_POST['root'])) {

			chdir($_POST['root']);
			$boarddir = $_POST['root'];
		}

		if (isset($_POST['include']))
			require_once urldecode($_POST['include']);

	} else {

		$self = substr($_SERVER['PHP_SELF'], strripos($_SERVER['PHP_SELF'], '/') + 1);
		$filename = realpath(str_ireplace($self, '../Settings_Client.php', $_SERVER['SCRIPT_FILENAME']));
		require_once $filename;
	}

	$row = BotBanishGetPassedData();

	if (!isset($row['location']))
		return;

	$row['prefix'] = isset($row['prefix']) ? $row['prefix'] : 'botbanish';
	$row['location'] = isset($row['sqlpath']) ? rtrim($row['sqlpath'], '/') . '/' : rtrim($row['location'], '/');

	$info = 'info=' . BotBanishSafeSerialize(array(
		'prefix' => $row['prefix'],
		'data' => false,
		'structure' => true,
		'indexes' => true,
		'compress' => false,
		'type' => 'Before',
		'location' => $row['location'],
		)
	);

	$include = '&include=' . urlencode($boarddir . '/BotBanish/bot/BotBanish_Settings.php');
	$root = '&root=' . urlencode($boarddir);

	$download_link_Before = '';

	BotBanishCreateDatabase();

	$info = 'info=' . BotBanishSafeSerialize(array(
		'prefix' => 'botbanish',
		'data' => false,
		'structure' => true,
		'indexes' => true,
		'compress' => false,
		'type' => 'After',
		'location' => $row['location'],
		)
	);

	$download_link_After = BotBanishGetCorrectTime() . ' - ' . BotBanishSendPostData($url, $info . $include . $root);
	$data = BotBanishGetDatabaseInfo();

	echo 'Database Conversion: ' . $data['database'] . '<br />';
	echo $download_link_Before . '<br />';
	echo $download_link_After . '<br />';
	echo BotBanishGetCorrectTime() . ' - ' . 'Done';
}

function BotBanishCreateDatabase() {

	global $BotBanishSettings;

	BotBanishSaveOverrideState();

	if (!function_exists('BotBanishExecuteSQLAsync'));
		require_once $BotBanishSettings['BOTBANISH_SUBS_DIR'] . 'BotBanish_SplitSQLAsync.php';

	BotBanishRemoveOldVersion();

	// We need all the time we can get

	BotBanishSetExecutionLimit(0);
	BotBanishSetTableCollation();

	//*******************************************************************************************************
	// Create all the tables that we will need from our mySQL exported file.
	// If the table already exist the table will only be updated with any new information.
	//*******************************************************************************************************

	$system = BotBanishCheckSystem();

	switch (strtoupper($system['install_type'])) {

		case 'SERVER':

			// Make sure table that contains table creation SQL data is created and populated in every case

			$tableInfoFile = $BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Server/botbanishserver_tables.sql';
			BotBanishExecuteSQLAsync($tableInfoFile, true, true);

			if (!BotBanishTableExist('botbanishserver_tables'))
				BotBanishCreateTablesFromSQLFile($tableInfoFile);

			BotBanishPopulate($BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Server/', 'botbanishserver_tables.php', 'bbs_');

		default:
		case 'CLIENT':

			// Make sure table that contains table creation SQL data is created and populated in every case

			$tableInfoFile = $BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Client/botbanishclient_tables.sql';
			BotBanishExecuteSQLAsync($tableInfoFile, true, true);

			if (!BotBanishTableExist('botbanishclient_tables'))
				BotBanishCreateTablesFromSQLFile($tableInfoFile);

			BotBanishPopulate($BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Client/', 'botbanishclient_tables.php', 'bbc_');

			break;
	}

	// Create any tables that do not exist that are supposed to exist.
	// This is just incase of an upgrade or accidental table deletion.
	// If there is default table data, the table will be repopulated after creation.

	$tables = BotBanishCreateMissingTables();

	// Populate data to tables with missing data

	BotBanishPopulateMissingTableData();
	BotBanishReplaceSettings();

	BotBanishCreateSpecialSettings();
	BotBanishCheckForLicense();

	$sql = BotBanishUpdateIPTable();
	BotBanishExecuteSQLAsync($sql);

	BotBanishRestoreOverrideState();
	return array('ip' => 0, 'bot' => 0, 'filecount' => 0, 'updated' => 0);
}

function BotBanishReplaceSettings() {

	global $BotBanishSettings;

	$BotBanishSettings_saved = $BotBanishSettings;

	// If the saved tables exist replace the new values with the saved ones.

	$tables_saved = BotBanishGetTableNames('_save');
	$update = false;

	foreach ($tables_saved as $table_saved) {

		$org_table = str_replace("_save", "", $table_saved);

		// ONLY merge and update the settings table

		if (stripos($org_table, '_settings') !== false) {

			$update = true;
			$Settings_org = BotBanishSettingsTableGetValues();
		}

		$sql = 'DROP TABLE IF EXISTS `' . $org_table . '`' . PHP_EOL;
		BotBanishExecuteSQL($sql);

		$sql = 'ALTER TABLE `' . $table_saved . '` RENAME TO `' . $org_table . '`';
		BotBanishExecuteSQL($sql);

		// ONLY merge and update the settings table

		if ($update) {

			$Settings_saved = BotBanishSettingsTableGetValues();

			$rows = array_merge($BotBanishSettings_saved, $Settings_org, $Settings_saved);
			BotBanishSettingsTablePutValues($rows);
		}
	}
}

function BotBanishAdjustSettings($rows) {

	$data = array();

	if (isset($rows) && is_array($rows)) {

		foreach ($rows as $row)
			$data[$row['name']] = $row['value'];
	}

	return $data;
}

function BotBanishCheckForLicense() {

	global $BotBanishSettings;

	if (isset($BotBanishSettings['BOTBANISH_CLIENT']) && ($BotBanishSettings['BOTBANISH_CLIENT'])) {

		$table = 'bbc_botbanishclient_settings';
		$tables = BotBanishGetTableNames($table);

		if (!empty($tables)) {

			$sql = 'SELECT `value` FROM `' . $table . '` WHERE `name` = "BOTBANISH_APIKEY"';
			$rows = BotBanishExecuteSQL($sql, true, true);
			$row = BotBanishLimitReturn($rows);

			if (is_array($row) && !empty($row))

				BotBanishDefineGlobals('BOTBANISH_APIKEY', $row['value']);
			else
				BotBanishDefineGlobals('BOTBANISH_APIKEY', 'Free');
		}
	}
}

function BotBanishRemoveOldVersion() {

	global $BotBanishSettings;

	$tables = array('bbs_botbanishserver_settings',
					'bbc_botbanishclient_settings',
					'bbs_botbanishserver_settings_save',
					'bbc_botbanishclient_settings_save',
					);
	$sql = '';

	// Get rid of old global locations in the settings table and updated them to the new locations
	// We will build all the SQL statement then execute all at once.

	foreach ($tables as $table) {

		$rows = BotBanishGetTableNames($table, true);
		$row = BotBanishLimitReturn($rows);

		if (empty($row)) continue;

		$deletes = array(
						'BOTBANISH_HIDDEN_HONEYPOT_LINK',
						'BOTBANISH_PAGE_HEADER',
						'BOTBANISH_LOCATION',
						'BOTBANISH_HTACCESS_ARRAY',
					);

		$deletes = array_merge($deletes, $BotBanishSettings['BOTBANISH_EXCLUDE_VARS']);

		// Find all names and add to deletes array

		$removes = array('_URL',
						'_DIR',
						'_HOST',
						'_DB_',
						'_HTML',
						'_VERSION',
						'_STATIC_DATA_',
					);

		foreach ($removes as $remove) {

			$request = 'SELECT `name` FROM `' . $table . '` WHERE `name` LIKE "%' . $remove . '%"';
			$rows = BotBanishExecuteSQL($request, true, true);

			if (is_array($rows) && !empty($rows)) {

				foreach ($rows as $row)
					$deletes[] = $row['name'];
			}
		}

		foreach ($deletes as $delete)
			$sql .= 'DELETE FROM `' . $table . '` WHERE `name` = "' . $delete . '";' . PHP_EOL;
	}

	// Get rid of session table pointers to the BotBanish Server. They will be recreated with the new session information

	$tables = array('bbc_botbanishclient_session', 'bbs_botbanishserver_session');

	$deletes = array('_transient_BotBanish_WEBSERVER',
					'_transient_BotBanish_SERVER_URL',
					);

	foreach ($tables as $table) {

		$tables = BotBanishGetTableNames($table, true);

		if (empty($tables)) continue;

		foreach ($deletes as $delete)
			$sql .= 'DELETE FROM `' . $table . '` WHERE `option_name` = "' . $delete . '";' . PHP_EOL;
	}

	// Delete from original and backup tables if they exist.
	
	$tables = BotBanishGetTableNames('bbc_botbanishclient_url_bad');
	
	foreach ($tables as $table) {
		
		$sql .= 'DELETE FROM `' . $table . '` WHERE `' . $table . '`.`url_part` = ".js";' . PHP_EOL;
		$sql .= 'DELETE FROM `' . $table . '` WHERE `' . $table . '`.`url_part` = "async";' . PHP_EOL;
	}

	// If in "Redirect Mode", delete all ip information from the tables
	
	if (isset($BotBanishSettings['BOTBANISH_REDIRECT_ONLY_ACTIVE']) && $BotBanishSettings['BOTBANISH_REDIRECT_ONLY_ACTIVE']) {
		
		$tables_to_empty = array('bbc_botbanishclient_ip_bad', 'bbc_botbanishclient_session');

		foreach ($tables_to_empty as $empty) {

			$tables = BotBanishGetTableNames($empty);
			if (empty($tables)) continue;
			
			foreach ($tables as $table)
				$sql .= 'TRUNCATE TABLE `' . $table . '`;' . PHP_EOL;
		}
	}
	
//	$sql .= BotBanishUpdateIPTable();

	BotBanishExecuteSQLAsync($sql);
}

function BotBanishUpdateIPTable() {

	global $BotBanishSettings;

	$tables = array();
	$type = array();
	$sql = '';

	$system = BotBanishCheckSystem();

	switch (strtoupper($system['install_type'])) {

		case 'SERVER':

			$tables[] = 'bbs_botbanishserver_ip';
			$type[] = 'bbs_botbanishserver';

		case 'CLIENT':

			$tables[] ='bbc_botbanishclient_ip';
			$type[] = 'bbc_botbanishclient';
			break;
	}

	// Copy old IP data to new table, then delete old table

	foreach ($tables as $key => $table) {

		$rows = BotBanishGetTableNames($table, true);
		$row = BotBanishLimitReturn($rows);

		// If ip table does not exist then we cannot update data from it.

		if (!empty($row)) {

			$sql .= 'INSERT IGNORE INTO `' . $table . '_bad` (`bot_ip`, `hit_count`, `first_hit`, `last_hit`, `forcelockout`, `domain_name`, `user_agent`, `domain`, `country`, `country_code`, `geo_info`, `updated`)
						SELECT `bot_ip`, `hit_count`, `first_hit`, `last_hit`, `forcelockout`, `domain_name`, `user_agent`, `domain`, `country`, `country_code`, `geo_info`, `updated`
							FROM `' . $table . '`
								WHERE 1;' . PHP_EOL;
		}

		$name = $type[$key];

		// Update database versions

		$sql .= "UPDATE `" . $name . "_settings` SET `value` = '" . $BotBanishSettings['BOTBANISH_VERSION_DATABASE'] . "' WHERE `" . $name . "_settings`.`name` = 'BOTBANISH_VERSION_DATABASE';" . PHP_EOL;
		$sql .= "UPDATE `" . $name . "_settings` SET `value` = '" . $BotBanishSettings['BOTBANISH_VERSION_SERVER'] . "' WHERE `" . $name . "_settings`.`name` = 'BOTBANISH_VERSION_SERVER';" . PHP_EOL;

		$sql .= "UPDATE `" . $name . "_settings` SET `value` = '" . $BotBanishSettings['BOTBANISH_VERSION_CLIENT'] . "' WHERE `" . $name . "_settings`.`name` = 'BOTBANISH_VERSION_CLIENT';" . PHP_EOL;
		$sql .= "UPDATE `" . $name . "_settings` SET `value` = '" . $BotBanishSettings['BOTBANISH_VERSION_NO'] . "' WHERE `" . $name . "_settings`.`name` = 'BOTBANISH_VERSION_NO';" . PHP_EOL;
		$sql .= "UPDATE `" . $name . "_settings` SET `value` = '" . $BotBanishSettings['BOTBANISH_VERSION'] . "' WHERE `" . $name . "_settings`.`name` = 'BOTBANISH_VERSION';" . PHP_EOL;

		$Droptables = array($name . '_ip',
						$name . '_ip_dnb',
						$name . '_spiders_bad',
						$name . '_spiders_good',
						$name . '_url_block',
						$name . '_url_dnc',
						$name . '_spiders',
		);

		foreach ($Droptables as $Droptable)
			$sql .= 'DROP TABLE IF EXISTS `' . $Droptable . '`;' . PHP_EOL;
	}

	return $sql;
}

function BotBanishPopulate($folder, $name, $table_prefix) {

	$files = BotBanishGetFileNames($folder . $name);

	if (empty($files)) return;

	foreach ($files as $file) {

		$table = $table_prefix . str_ireplace('.php', '', basename($file));

		if (BotBanishTableHasData($table) === false)
			BotBanishImportTableData($table, $file, false);
	}
}

function BotBanishGetFileNames($search) {

	// Get all sql files to load

	$files = glob($search);

	if (is_array($files) && count($files) > 0)
		return $files;
	else
		return false;
}

function BotBanishTableHasData($table) {

	$data = BotBanishGetDatabaseInfo();
	if (empty($data['server'])) return false;

	$sql = 'SELECT * FROM information_schema.tables WHERE table_schema = "' . $data['database'] . '" AND table_name = "' . $table . '"';
	$rows = BotBanishExecuteSQL($sql, false, true);

	if (!empty($rows) && is_array($rows)) {

		$sql = 'SELECT * FROM `' . $table . '` WHERE 1 LIMIT 2';
		$rows = BotBanishExecuteSQL($sql, false, false);

		if (!empty($rows) && is_array($rows))
			return true;
		else
			return false;

	} else {

		return false;
	}
}

function BotBanishImportTableData($tablename, $filename, $change = false) {

	if (!file_exists($filename))
		return;

	$numerics = array('integer', 'int', 'bigint', 'mediumint', 'smallint', 'tinyint', 'bit', 'float', 'double', 'decimal', 'numeric');
	$data = BotBanishGetDatabaseInfo();

	// Find all the columns in the destination table in order to eliminate the missing columns from the source

	$sql = 'SELECT `COLUMN_NAME`, `DATA_TYPE` FROM information_schema.COLUMNS WHERE (`TABLE_SCHEMA` = "' . $data['database'] . '") AND (`TABLE_NAME` = "' . $tablename . '")';
	$rows = BotBanishExecuteSQL($sql, true, false);

	if (!empty($rows) && is_array($rows)) {

		$columns = array();
		$datatype = array();
		$sql = '';

		foreach ($rows as $row) {

			$columns[] = $row['COLUMN_NAME'];
			$datatype[$row['COLUMN_NAME']] = $row['DATA_TYPE'];
		}

		$items = include ($filename);

		// Create the insert for the destination table.

		if (empty($items)) return;

		foreach ($items as $item) {

			$row = array();

			foreach ($item as $key => $value) {

				// Make sure only the columns that exist in our destination table are used
				// If a datetime field's value is zero, remove the field and let the database fill in the field

				if (in_array($key, $columns)) {

					if (isset($datatype[$key]) && ($datatype[$key] == 'datetime')) {

						if ($value == '0')
							unset($row[$key]);
						else
							$row[$key] = is_numeric($value) ? date('Y-m-d H:i:s', $value) : $value;

					} else {

						$row[$key] = in_array($datatype[$key], $numerics) ? intval($value) : $value;
					}
				}
			}

			$params = BotBanishBuildSQLStatementInsert($row, $tablename);

			$sql .= 'INSERT IGNORE INTO `' . $tablename . '` (' . $params['columns'] . ') VALUES (' . $params['values'] . ');' . PHP_EOL;
		}

		BotBanishExecuteSQLAsync($sql, false, $change);
	}
}

function BotBanishUpdateTableAsync($sql_filename, $table = '', $find = 'INSERT IGNORE INTO ') {

	$ret = BotBanishCreateTablesFromSQLFileAsync($sql_filename, $table, $find);
	return $ret;
}

function BotBanishCreateTablesFromSQLFileAsync($sql_filename, $table = '', $find = '') {

	if (!file_exists($sql_filename)) return false;

	global $BotBanishSettings;

	clearstatcache();	// Needs this to get proper filesize after a recent write operation
	$size = filesize($sql_filename);

	$size =  ($size > 32767) ? 32767 : intval($size);

	if (!function_exists('BotBanishExecuteSQLFileByLines'))
		require_once $BotBanishSettings['BOTBANISH_SUBS_DIR'] . 'BotBanish_SplitSQLAsync.php';

	$ret = BotBanishExecuteSQLFileByLines($sql_filename, $table, $find, $size);

	return $ret;
}

function BotBanishCreateTablesFromSQLFileSync($sql_filename) {

	if (!file_exists($sql_filename)) return false;

	BotBanishExecuteSQLSync($sql_filename);
	return true;
}

function BotBanishExecuteSQLSync($sql_filename, $file = true) {

	// This function happens syncronously!!!
	// We can use SQL from a file or straight SQL

	if ($file)

		$sql = file_get_contents($sql_filename);
	else
		$sql = $sql_filename;

	$sql = BotBanishChangeTableCollation($sql);

	$conn = BotBanishDatabaseOpen();
	$conn->query($sql);

	if (property_exists($conn, 'close'))
		$conn->close;
}

function BotBanishCreateTablesFromSQLFile($sql_filename) {

	global $BotBanishSettings;

	if (!file_exists($sql_filename)) return false;

	$conn = BotBanishDatabaseOpen();

	if (!is_object($conn))
		return false;

	$sql = '';
	$lines = file($sql_filename);

	if (!empty($lines)) {

		foreach ($lines as $line) {

			//Remove Comment Inside SQL FILE

			if (substr($line, 0, 2) == '--' || $line == '')
				continue;

			$sql .= $line;

			//Break Line Up To ';' NEW QUERY

			if (substr(trim($line), -1, 1) == ';') {

				$sql = BotBanishChangeTableCollation($sql);
				$conn->query($sql);
				$sql = '';
			}
		}
	}

	if (property_exists($conn, 'close'))
		$conn->close;

	return true;
}

function BotBanishTableExist($tableInfoFile) {

	$tables = BotBanishGetTableNames('botbanish');

	if (empty($tables))
		return false;

	return true;
}

function BotBanishCreateMissingTables() {

	global $BotBanishSettings;

	$system = BotBanishCheckSystem();
	$type = strtoupper($system['install_type']);
	$diff_client = array();
	$diff_server = array();

	switch ($type) {

		case 'SERVER':

			$tableName = 'bbs_botbanishserver_tables';
			$rows = BotBanishGetTableData($tableName);
			$tables = BotBanishGetTableNames('botbanishserver_');
			$diff_server = array_diff($rows, $tables);
			BotBanishCreateTablesFromText($diff_server, $type, $tableName);

		default:

			$tableName = 'bbc_botbanishclient_tables';
			$rows = BotBanishGetTableData($tableName);
			$tables = array_merge(BotBanishGetTableNames('botbanishclient_'), BotBanishGetTableNames('botbanish_'));
			$diff_client = array_diff($rows, $tables);
			BotBanishCreateTablesFromText($diff_client, $type, $tableName);
			break;
	}

	return array_merge($diff_client, $diff_server);
}

function BotBanishCreateTablesFromText($tables, $type, $tableName) {

	if (empty($tables) || !is_array($tables)) return;

	global $BotBanishSettings;

	foreach ($tables as $table) {

		$sql = 'SELECT `text` FROM ' . $tableName . ' WHERE `table_name` = "' . $table . '"';
		$rows = BotBanishExecuteSQL($sql);
		$row = BotBanishLimitReturn($rows);
		BotBanishExecuteSQLAsync($row['text'], false, true);
	}
}

function BotBanishPopulateMissingTableData() {

	global $BotBanishSettings;

	$folders = array(
						array('loc' => $BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Language/', 'prefix' => ''),
						array('loc' => $BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Server/', 'prefix' => 'bbs_'),
						array('loc' => $BotBanishSettings['BOTBANISH_DATA_DIR'] . 'Client/', 'prefix' => 'bbc_'),
					);

	foreach ($folders as $folder)
		BotBanishPopulate($folder['loc'], '*.php', $folder['prefix']);
}

function BotBanishGetTableData($tableName) {

	$sql = 'SELECT `table_name` FROM `' . $tableName . '` WHERE 1';
	$rows = BotBanishExecuteSQL($sql);
	$hosted = array();

	foreach ($rows as $row)
		$hosted[] = $row['table_name'];

	return $hosted;
}

function BotBanishResequenceTable($tables) {

	if (!is_array($tables)) $tables = array($tables);

	BotBanishSaveOverrideState();

	foreach ($tables as $table) {

		BotBanishLogInfo('Resequencing Table' . $table, $table, true, 'Truncate');

		$table_columns = BotBanishGetTableColumns($table);

		$table_saved = $table . '_saved';

		$sql = 'ALTER TABLE `' . $table . '` RENAME TO `' . $table_saved . '`';
		BotBanishExecuteSQL($sql);

		BotBanishCreateMissingTables();

		// Remove auto_increment key for re-insertion

		$auto = BotBanishGetTableAutoIncrement($table);

		foreach($table_column as $key => $column) {

			if (isset($auto[$column]))
				unset($table_columns[$key]);
		}

		$columns = BotBanishBuildSQLStatementSelect($table_columns);

		$sql = 'ALTER TABLE `' . $table . '` AUTO_INCREMENT = 1;';
		BotBanishExecuteSQL($sql);
				
		$sql = 'INSERT INTO `' . $table . '` (' . $columns . ') SELECT ' . $columns . ' FROM `' . $table . '`';
		BotBanishExecuteSQL($sql);

		$sql = 'DELETE IF EXISTS `' . $table_saved . '`';
		BotBanishExecuteSQL($sql);
	}

	BotBanishRestoreOverrideState();
}
?>