<?php

///////////////////////////////////////////////////////////////////////////////////////////////////////
// BotBanish 7.0.10
// Randem Systems: https://randemsystems.support
// BotBanish SQL to SMF Conversion Subroutines
// Date: 12/28/2025
// Usage:
//
// Some native MySQL SQL Statements cannot be directly translated to SMF calls. Some may need to be
// separated into multiple calls and some may not be able to be executed thru the SMF engine at all.
//
// 'security_override' => true,
///////////////////////////////////////////////////////////////////////////////////////////////////////

function BotBanishCreateSMFStatement($sqlParsed, $sql_statement) {
	global $smcFunc;

	if (!is_array($sqlParsed)) {

		BotBanishLogSQL(' Created: ' . $sqlParsed);
		return;
	}

	// SMF will not process request that can contain comments
	// This is done to allow flow thru statements

	$sql_statement = rtrim($sql_statement, ';');

	if (!defined('SMF_SQL_CREATE')) define('SMF_SQL_CREATE', 1);
	if (!defined('SMF_SQL_UPDATE')) define('SMF_SQL_UPDATE', 2);
	if (!defined('SMF_SQL_DELETE')) define('SMF_SQL_DELETE', 4);
	if (!defined('SMF_SQL_DROP')) define('SMF_SQL_DROP', 8);
	if (!defined('SMF_SQL_SHOW')) define('SMF_SQL_SHOW', 16);
	if (!defined('SMF_SQL_RENAME')) define('SMF_SQL_RENAME', 32);
	if (!defined('SMF_SQL_INSERT')) define('SMF_SQL_INSERT', 64);
	if (!defined('SMF_SQL_SELECT')) define('SMF_SQL_SELECT', 128);
	if (!defined('SMF_SQL_TRIGGER')) define('SMF_SQL_TRIGGER', 256);
	if (!defined('SMF_SQL_WHERE')) define('SMF_SQL_WHERE', 512);
	if (!defined('SMF_SQL_LIMIT')) define('SMF_SQL_LIMIT', 1024);
	if (!defined('SMF_SQL_DUPLICATE')) define('SMF_SQL_DUPLICATE', 2048);
	if (!defined('SMF_SQL_OPTIONS')) define('SMF_SQL_OPTIONS', 4096);
	if (!defined('SMF_SQL_VALUES')) define('SMF_SQL_VALUES', 8192);
	if (!defined('SMF_SQL_ALTER')) define('SMF_SQL_ALTER', 16384);
	if (!defined('SMF_SQL_DESCRIBE')) define('SMF_SQL_DESCRIBE', 32768);
	if (!defined('SMF_SQL_ORDER')) define('SMF_SQL_ORDER', 65536);
	if (!defined('SMF_SQL_LIKE')) define('SMF_SQL_LIKE', 131072);

	$operation = 0;

	$sql = '';
	$table = '';
	$table_like = '';
	$error = 'fatal';
	$params = array();
	$columns = array();
	$values = array();
	$indexes = array();
	$arr = array();
	$defn = array();
	$parameters = array();
	$statements = array();
	$duplicates = array();

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

		switch($key) {

			case 'SELECT':

				// Returns a partial SQL Statement for the select.
				// Must go thru other cases to complete the statement

				$operation = $operation | SMF_SQL_SELECT;
				BotBanishSMFBuildSelectClause($value, $sql);
				break;

			case 'INSERT':

				// Returns the columns involved in the insert

				$operation = $operation | SMF_SQL_INSERT;
				BotBanishSMFBuildInsertClause($value, $sql, $columns, $table, $key);
				break;


			case 'IGNORE':

				// Returns the columns involved in the insert

//				$operation = $operation | SMF_SQL_IGNORE;
				BotBanishSMFBuildIgnoreClause($value, $sql, $key);
				break;

			case 'DELETE':

				// Starts the delete statement

				$operation = $operation | SMF_SQL_DELETE;
				BotBanishSMFBuildDeleteClause($value, $sql, $table);
				break;

			case 'UPDATE':

				// Starts the update statement

				$operation = $operation | SMF_SQL_UPDATE;
				BotBanishSMFBuildUpdateClause($value, $sql, $table);
				break;

			case 'CREATE':

				// Starts the create statement

				$operation = $operation | SMF_SQL_CREATE;
				BotBanishSMFBuildCreateClause($value, $sql);
				break;

			case 'SHOW':

				// Starts the show statement

				$operation = $operation | SMF_SQL_SHOW;
				BotBanishSMFBuildShowClause($value, $sql, $defn, $table);
				break;

			case 'DROP':

				// Starts the drop statement

				$operation = $operation | SMF_SQL_DROP;
				BotBanishSMFBuildDropClause($value, $sql, $table);
				break;

			case 'RENAME':

				// Starts the rename statement

				$operation = $operation | SMF_SQL_RENAME;
				BotBanishSMFBuildRenameClause($value, $sql, $defn);
				break;

			case 'VALUES':

				$operation = $operation | SMF_SQL_VALUES;
				BotBanishSMFBuildValuesClause($value, $sql, $columns, $values, $params, $defn, $table, $operation, $sqlParsed);
				break;

			case 'SET':

				// Part of the update statement?

				BotBanishSMFBuildSetClause($value, $sql, $defn, $operation);
				break;

			case 'INTO':

				BotBanishSMFBuildIntoClause($value, $sql, $columns, $table, $indexes);
				break;

			case 'FOR':

				BotBanishSMFBuildForClause($value, $sql, $columns, $table, $key);
				break;

			case 'FROM':

				BotBanishSMFBuildFromClause($value, $sql, $table);
				break;

			case 'WHERE':

				$operation = $operation | SMF_SQL_WHERE;
				BotBanishSMFBuildWhereClause($value, $sql, $defn, $operation);
				break;

			case 'TRIGGER':

				$operation = $operation | SMF_SQL_TRIGGER;
				BotBanishSMFBuildTriggerClause($value, $sql, $key);
				break;

			case 'LIMIT':

				$operation = $operation | SMF_SQL_LIMIT;
				BotBanishSMFBuildLimitClause($value, $sql);
				break;

			case 'ORDER':

				$operation = $operation | SMF_SQL_ORDER;
				BotBanishSMFBuildOrderClause($value, $sql);
				break;

			case 'DESCRIBE':

				$operation = $operation | SMF_SQL_DESCRIBE;
				BotBanishSMFBuildDescribeClause($value, $sql, $sql_statement);
				break;

			case 'TABLE':

				if (isset($sqlParsed['LIKE']))
					$operation = $operation | SMF_SQL_LIKE;

				if ($operation & SMF_SQL_CREATE) {

					BotBanishSMFBuildTableClause($value, $sql, $columns, $parameters, $table, $indexes, $statements, $operation);

					if (isset($sqlParsed['LIKE'])) {

						$table_like = BotBanishSMFBuildLikeClause($sqlParsed, $sql);
						$operation = SMF_SQL_LIKE;	// Force nothing but a 'LIKE' statement
					}

				} else {

					$data = BotBanishGetDatabaseInfo();
					$table = '`' . $data['database'] . '`.' . $value['name'];
					$sql .= ' ' . $table;
				}

				break;

			case 'ALTER':

				$operation = $operation | SMF_SQL_ALTER;
				BotBanishSMFBuildAlterClause($value, $sql, $table, $key, $arr);
				break;

			case 'OPTIONS':

				$operation = $operation | SMF_SQL_OPTIONS;
//				BotBanishSMFBuildOptionsClause($value, $sql, $table);
				break;

			case 'ON DUPLICATE KEY UPDATE':

				$operation = $operation | SMF_SQL_DUPLICATE;
				BotBanishSMFBuildDuplicateClause($value, $sql, $defn, $duplicates, $params, $key, $operation);
				break;

			default:

				break;
		}
	}

	if (!empty($defn))
		BotBanishConstructArray($arr, $defn);

	//=============================================================
	// Process the SMF call with the available parameters
	//=============================================================

	$rows = array();
	$result = '';
	$sql = trim($sql);

	global $sourcedir;
	require_once $sourcedir . '/Subs-Db-mysql.php';
	db_extend('packages');

	switch($operation) {

		case (SMF_SQL_CREATE & $operation) == true
			&& (SMF_SQL_TRIGGER & $operation) == false:

			// If we have a 'CURRENT_TIMESTAMP', 'DATETIME', 'TEXT' default value we need to create an ALTER statement to execute after table creation
			// to correct this because SMF does not support this default type
			//
			// TO-DO: We may need to change the order of the ALTER statements due to the fact that we may be changing a column
			// that is in the index that we may be creating first.

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

			if (isset($column['default']) && (stripos($column['default'], 'CURRENT_TIMESTAMP') !== false)) {

					$size =  !empty($column['size']) ? '(' . $column['size'] . ')' : '';
					$statements[] = 'ALTER TABLE ' . $table . ' CHANGE COLUMN `' . $column['name'] . '` `' . $column['name'] . '` ' . $column['type'] . $size . ' NOT NULL DEFAULT ' . $column['default'];
					unset($columns[$key]['default']);
				}
			}

			$arr[] = $columns;
			$arr[] = $indexes;
			$arr[] = $parameters;

			LogInst("\$smcFunc['db_create_table'](" . $table . ", \$columns, \$indexes, \$parameters, 'overwrite', 'fatal')", array($columns, $sql, $sql_statement));
			$result = $smcFunc['db_create_table']($table, $columns, $indexes, $parameters, 'overwrite', 'fatal');
			$rows = BotBanishGetResults($operation, $result);

			// Execute all created ALTER statements

			if (!empty($statements)) {

				foreach ($statements as $statement) {

					LogInst("\$smcFunc['db_query']('', \$statement, \$array())", array($statement, array()));
					$result = $smcFunc['db_query']('', $statement, array());
					$rows = BotBanishGetResults($operation, $result);
				}
			}
			break;

		case ((SMF_SQL_INSERT & $operation) == true
			&& (SMF_SQL_TRIGGER & $operation) == false
			&& (SMF_SQL_SELECT & $operation) == false):

			$method = '';
			$key = array();

			if ((SMF_SQL_DUPLICATE & $operation) == true) {

				// Only take primary key auto increment column. Should be first in the list.

//				$columns[$indexes[0]] = 0;
				$method = 'replace';
				$key = array($indexes[0]);
			}

			$params = BotBanishBuildSMFSQLStatementInsert($columns, $table);

			LogInst("\$smcFunc['db_insert']('" . $method . "', " . $table . ", \$params['columns'], \$params['values'], \$indexes)", array($params, $indexes, $sql, $sql_statement));
			$smcFunc['db_insert']($method, $table, $params['columns'], $params['values'], $key);
			$rows = BotBanishGetResults($operation, '');
			break;

		case (SMF_SQL_DROP & $operation) == true:
		case (SMF_SQL_SELECT & $operation) == true:
		case (SMF_SQL_TRIGGER & $operation) == true:
		case (SMF_SQL_SHOW & $operation) == true:
		case (SMF_SQL_RENAME & $operation) == true:
		case (SMF_SQL_UPDATE & $operation) == true:
		case (SMF_SQL_DELETE & $operation) == true:
		case (SMF_SQL_DESCRIBE & $operation) == true:
		case (SMF_SQL_LIKE & $operation) == true:
		default:

			global $modSettings;

			LogInst("\$smcFunc['db_query']('', \$sql, \$arr)", array($arr, $sql, $sql_statement));

			$m_array = preg_grep('/;/', $arr);

			if (!empty($m_array))
				$modSettings['disableQueryCheck'] = true;

			$result = $smcFunc['db_query']('', $sql, $arr);
			$rows = BotBanishGetResults($operation, $result);

			if (!empty($m_array))
				$modSettings['disableQueryCheck'] = '';
			break;
	}

	return $rows;
}

//=============================================================
// Processing Routines
//=============================================================

function BotBanishGetResults($operation, $result = '') {
	global $smcFunc;

	$rows = false;

	if (is_object($result)) {

		if ($smcFunc['db_num_rows']($result) > 0) {

			$rows = array();

			while ($row = $smcFunc['db_fetch_assoc']($result))
				$rows[] = $row;

			BotBanishLogInfo('Returned: ', count($rows));

			$smcFunc['db_free_result']($result);
		}


	} else {

		if ((SMF_SQL_INSERT & $operation) == true
				|| (SMF_SQL_DELETE & $operation) == true) {

			$rows = $smcFunc['db_affected_rows']();

			if ($rows <> 0)
				BotBanishLogInfo('Affected: ', $rows);
		}
	}

	return $rows;
}

function LogInst($inst, $arr = array()) {
	global $BotBanishSQL;

	$BotBanishSQL = $inst . PHP_EOL . var_export($arr, true);
	BotBanishLogInfo(PHP_EOL . PHP_EOL . 'Call: ' . $inst, $arr);
}

//=============================================================
// SQL Parameter Building Routines
//=============================================================

function BotBanishSMFBuildIntoClause($value, &$sql, $columns, &$table, &$indexes) {

	$data = BotBanishGetDatabaseInfo();
	$indexes = BotBanishGetTableUniqueIndexes(str_replace(array('`' ,"'" , "'", '.'), '', $value[0]));
	$table = '`' . $data['database'] . '`.' . $value[0];
	$sql .= ' INTO ' . $table . ' ';

	if (isset($value[1]))
		$sql .= $value[1] . ' ';
}

function BotBanishSMFBuildAlterClause($value, &$sql, &$table, $key, &$arr) {

	$data = BotBanishGetDatabaseInfo();
	$table = $value['sub_tree'][0]['table_name'];
	$sql .= $key . ' ' . $value['expr_type'] . ' `' . $data['database'] .  '`.' . $table . ' ' . $value['base_expr'];
	$arr['security_override'] = true;
}

function BotBanishSMFBuildSelectClause($value, &$sql) {

	$sql .= ' SELECT ';

	foreach ($value as $skey => $svalue) {

		switch($svalue['expr_type']) {

			case 'reserved':

				$sql .= $svalue['base_expr'] . ' ';
				break;

			case 'const':
			case 'colref':

				$sql .= $svalue['base_expr'] . ',';
				break;

			case 'aggregate_function':
			case 'function':

				$sql .= $svalue['base_expr'] . '(';

				foreach ($svalue['sub_tree'] as $sub_key => $sub_value) {

					switch ($sub_value['expr_type']) {

						case 'colref':
						case 'const':

							$sql .=  $sub_value['base_expr'] . ',';
							break;

						case 'expression':

							$sql .= '(' . $sub_value['base_expr'] . ')';

							if (isset($svalue['alias']))
								$sql .= ' ' . $svalue['alias']['base_expr'];

							$sql .= ',';
							break;
					}
				}

				$sql = rtrim($sql, ',') . ') ';

				if ($svalue['expr_type'] == 'aggregate_function')
					$sql .= ',';
				break;
/*
			case 'function':

				$sql .= $svalue['base_expr'];

				foreach ($svalue['sub_tree'] as $sub_value) {

					switch ($sub_value['expr_type']) {

						case 'expression':

							$sql .= '(' . $sub_value['base_expr'] . ')';

							if (isset($svalue['alias']))
								$sql .= ' ' . $svalue['alias']['base_expr'];

							$sql .= ',';
							break;
					}
				}
				break;
*/
		}
	}

	$sql = rtrim($sql,',');
}

function BotBanishSMFBuildCreateClause($value, &$sql) {

	$sql .= ' CREATE ';

	foreach ($value as $skey => $svalue) {

		switch ($skey) {

			case 'base_expr':

				$sql .= $svalue . ',';
				break;
		}
	}

	$sql = rtrim($sql,',');
}

function BotBanishSMFBuildInsertClause($value, &$sql, &$columns, &$table, $key) {

	// Possibly a trigger statement with insert that has no columns

	if (!is_array($value[0])) {

		BotBanishSMFBuildClause($value, $sql, '');
		return;
	}

	$sql .= ' ' . $key . ' ';

	BotBanishConstructColumns($value[0], $columns, $table);
}

function BotBanishSMFBuildIgnoreClause($value, &$sql, $key) {

	$sql .= ' ' . $key . ' ';

}

function BotBanishSMFBuildValuesClause($value, &$sql, &$columns, &$values, &$params, &$defn, $table, $operation, $sqlParsed) {

	// Trigger statement cannot have SMF replacable variable in the statement

	$value_data = '(';
	$i = count($defn);

	foreach ($columns as $skey => $svalue) {

		$key = str_replace(array('"', "'", '`'), '', $skey);

		if (BotBanishIsSerialized(trim($value[0]['data'][$i]['base_expr'], "'")))
			$columns[$key] = trim($value[0]['data'][$i]['base_expr'], "'");
		else
			if (BotBanishIsSerialized(trim($value[0]['data'][$i]['base_expr'], '"')))
				$columns[$key] = trim($value[0]['data'][$i]['base_expr'], '"');
			else
				if (stripslashes($value[0]['data'][$i]['base_expr']) != $value[0]['data'][$i]['base_expr']) {

					$columns[$key] = stripslashes($value[0]['data'][$i]['base_expr']);
					$columns[$key] = trim($columns[$key], '"');
					$columns[$key] = trim($columns[$key], "'");

				} else {

					$columns[$key] = str_replace(array('"', "'", '`'), '', $value[0]['data'][$i]['base_expr']);
				}

		if ((SMF_SQL_TRIGGER & $operation) == true) {

			$value_data .= ' ' . $value[0]['data'][$i]['base_expr'] . ',';

		} else {

			$defn[] = $value[0]['data'][$i]['base_expr'];
//			$value_data .= ' {raw:const_' . $i . '},';
			$values[$key] = ' {raw:const_' . $i . '}';
		}

		$i++;
	}

	if ((SMF_SQL_TRIGGER & $operation) == true) {

		$value_data = rtrim($value_data, ',') . ')';
		$sql = rtrim($sql, ',') . ' VALUES ' . $value_data;
	}

	$params = BotBanishBuildSMFSQLStatementInsert($columns, $table);
}

function BotBanishSMFBuildClause($value, &$sql, $key) {

	$data = BotBanishGetDatabaseInfo();

	if ($key !== '')
		$sql .= ' ' . $key . ' ';

	foreach($value as $sub_key => $sub_value) {

		if (!is_array($sub_value)) {

			// Check for a table name and add database name to it

			$str = str_ireplace(array('`', '"', '"'), '', $sub_value);

				$sub_value = ' `' . $data['database'] . '`.' . $sub_value;
		}

		if ($sub_key !== 'INSERT')
			$sql .= ' ' . $sub_value . ' ';
	}

	$sql .= ' ';
}

function BotBanishSMFBuildTriggerClause($value, &$sql, $key) {

	global $sourcedir;
	
	if (!isset($sourcedir)) {

		BotBanishSMFBuildClause($value, $sql, $key);
		return;
	}

	$data = BotBanishGetDatabaseInfo();

	// For SMF we must include the database name in the trigger statement for it to be executed by SMF

	$count = 0;

	if ($key !== '')
		$sql .= ' ' . $key . ' ';

	foreach($value as $sub_key => $sub_value) {

		$str = str_replace('`', '', $sub_value, $count);

		if ($count === 2)
			$sub_value = ' `' . $data['database'] . '`.' . $sub_value;

		$sql .= $sub_value;
	}

	$sql .= ' ';
}

function BotBanishSMFBuildOptionClause($value, &$sql, $key) {

	if (isset($value['OPTIONS'])) {

		foreach($value['OPTIONS'] as $sub_key => $sub_value)
				$sql .= $sub_value;

		$sql .= ' ';
	}
}

function BotBanishSMFBuildForClause($value, &$sql, &$columns, &$table, $key) {

	BotBanishSMFBuildClause($value, $sql, $key);

	if (!isset($value['INSERT']))
		return;

	BotBanishConstructColumns($value['INSERT'], $columns, $table);
}

function BotBanishSMFBuildLimitClause($value, &$sql) {

	$sql .= ' LIMIT ';
	$sql .= $value['rowcount'];
}

function BotBanishSMFBuildOrderClause($value, &$sql) {

	$sql .= ' ORDER BY ';

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

		if ($key > 0) $sql .= ',';
		$sql .= $item['base_expr'];
		$sql .= !empty($item['direction']) ? ' ' . $item['direction'] : '';
	}
}

function BotBanishSMFBuildDuplicateClause($value, &$sql, &$defn, &$duplicates, &$params, $key, $operation) {

	$i = count($defn);
	$k = 0;

	$sql .= ' ' . $key . ' ';

	foreach ($value as $skey => $svalue) {

		switch($skey) {

			case 'sub_tree':

				foreach ($svalue as $sub_key => $sub_value) {

						switch ($sub_key) {

							case 'sub_tree':

								$duplicates[] = '';

								foreach ($sub_value as $sub_tree_key => $sub_tree_value) {

									switch($sub_tree_value['expr_type']) {

										case 'colref':

											$params['updates'][] = $sub_tree_value['base_expr'];

											if ((SMF_SQL_TRIGGER & $operation) == true) {

												$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

											} else {

												$defn[] = $sub_tree_value['base_expr'];
												$duplicates[$k] .= '{raw:const_' . $i++ . '}';
//												$sql .= ' {raw:const_' . $i++ . '} ';
											}
											break;

										case 'operator':

											$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';
											$duplicates[$k] .= ' ' . $sub_tree_value['base_expr'] . ' ';
											break;

										case 'int':

											$params['updates'][] = intval($sub_tree_value['base_expr']);

											if ((SMF_SQL_TRIGGER & $operation) == true) {

												$sql .= ' ' . intval($sub_tree_value['base_expr']) . ' ';

											} else {

												$defn[] = intval($sub_tree_value['base_expr']);
//												$sql .= ' {int:const_' . $i++ . '} ';
												$duplicates[$k] .= '{int:const_' . $i++ . '}';
											}
											break;

										case 'const':

											$params['updates'][] = $sub_tree_value['base_expr'];
											if ((SMF_SQL_TRIGGER & $operation) == true) {

												$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

											} else {

												$defn[] = $sub_tree_value['base_expr'];
//												$sql .= ' {raw:const_' . $i++ . '} ';
												$duplicates[$k] .= '{raw:const_' . $i++ . '}';
											}
											break;
									}
								}
								$k++;
								break;
						}
				}
		}
	}
}

function BotBanishSMFBuildFromClause($value, &$sql, $table) {

	$data = BotBanishGetDatabaseInfo();
	$sql .= ' FROM ';

	foreach ($value as $skey => $svalue) {

		switch($svalue['expr_type']) {

			case 'table':

				// Make sure database name is not duplicated in table name
				if (stripos($svalue['base_expr'], $data['database']) !== false)
					$table = stripos($svalue['base_expr'], 'information_schema') !== false ? $svalue['base_expr'] : $svalue['base_expr'];
				else
					$table = stripos($svalue['base_expr'], 'information_schema') !== false ? $svalue['base_expr'] : '`' . $data['database'] . '`.' . $svalue['base_expr'];

				$sql .= $table . ' ';
				break;
		}
	}
}

function BotBanishSMFBuildWhereClause($value, &$sql, &$defn, $operation) {

	$sql .= ' WHERE ';
	$bracket = false;

	foreach ($value as $skey => $svalue) {

		switch($svalue['expr_type']) {

			case 'bracket_expression':

				$sql .= '(';
				$bracket = true;

			case 'colref':
			case 'const':

				if (!is_array($svalue['sub_tree'])) {

					BotBanishSMFConstructWhere($svalue, $sql, $defn, $operation);

				} else {

					foreach($svalue['sub_tree'] as $sub_tree_key => $sub_tree_value)
						BotBanishSMFConstructWhere($sub_tree_value, $sql, $defn, $operation);
				}
				break;

			case 'operator':

				if ($bracket) $sql .= ') ';

				$sql .= $svalue['base_expr'] . ' ';
				$bracket = false;
				break;

			case 'aggregate_function':
			case 'function':

				$sql .= $svalue['base_expr'] . '(';

				foreach($svalue['sub_tree'] as $sub_tree_key => $sub_tree_value) {

					BotBanishSMFConstructWhere($sub_tree_value, $sql, $defn, $operation);
					$sql .= ',';
				}

				$sql = rtrim($sql, ',') . ') ';

				if ($svalue['expr_type'] == 'aggregate_function')
					$sql .= ',';
				break;

			case 'in-list':

				$sql .= $svalue['base_expr'];
				break;
		}
	}

	if ($bracket)
		$sql .= ') ';
}

function BotBanishSMFBuildSetClause($value, &$sql, &$defn, $operation) {

	$sql .= ' SET ';
	$def = '';
	$i = count($defn);
	$bracket = false;

	foreach ($value as $skey => $svalue) {

		switch($svalue['expr_type']) {

			case 'expression':

				foreach($svalue['sub_tree'] as $sub_tree_key => $sub_tree_value) {

					switch($sub_tree_value['expr_type']) {

						case 'const':

							$sql .= $sub_tree_value['base_expr'];
							break;

						case 'colref':

							if (!is_array($sub_tree_value['sub_tree'])) {

								BotBanishSMFConstructSet($sub_tree_value, $sql, $defn, $operation);

							} else {

								foreach($sub_tree_value['sub_tree'] as $sub_tree_key1 => $sub_tree_value1)
									BotBanishSMFConstructSet($sub_tree_value1, $sql, $defn, $operation);
							}
							break;

						case 'operator':

							if ($bracket) {

								$sql .= ') ';
								$bracket = false;
							}

							$sql .= $sub_tree_value['base_expr'] . ' ';
							break;

						case 'function':

							$sql .= $sub_tree_value['base_expr'] . '()';
							break;
					}
				}
				$sql .= ',';
		}
	}

	$sql = rtrim($sql, ',');

	if ($bracket)
		$sql .= ') ';
}

function BotBanishSMFBuildLikeClause($sqlParsed, &$sql) {

	$data = BotBanishGetDatabaseInfo();
	$table_dest = str_replace('`','', $sqlParsed['LIKE']['table']);
	$table =  '`' . $data['database'] . '`.`' . str_replace('`','', $sqlParsed['LIKE']['table']) . '`';
	$sql .= ' LIKE ' .  $table;
	return $table_dest;
}

function BotBanishConstructColumns($value, &$columns, &$table) {

	$data = BotBanishGetDatabaseInfo();
	foreach ($value as $sub_key => $sub_value) {

		switch ($sub_key) {

			case 'table':

				$table = '`' . $data['database'] . '`.' . $sub_value;
				break;

			case 'columns':

				if (!empty($sub_value)) {

					foreach($sub_value as $col_key => $col_value) {

						switch($col_value['expr_type']) {

							case 'colref':

								// Eliminate enclosed quotes

//								$ckey = str_replace(array('\"', "\'", '\`'), array('%%', '^^'. '~~'), $col_value['base_expr']);
								$ckey = trim($col_value['base_expr'], '"');
								$ckey = trim($col_value['base_expr'], "'");
								$ckey = trim($col_value['base_expr'], '`');

//								$ckey = str_replace(array('"', "'", '`'), '', $ckey);
//								$ckey = str_replace(array('%%', '^^', '~~'), array('\"', "\'", '\`'), $ckey);
								$columns[$ckey] = '';
								break;
						}
					}
				}
				break;
		}
	}
}

function BotBanishSMFConstructWhere($sub_tree_value, &$sql, &$defn, $operation) {

	// If a trigger statement we DO NOT replace the references with SMF replacable items
	// they have to stay in their original form

	$i = count($defn);

	switch($sub_tree_value['expr_type']) {

		case 'operator':

			$sql .= $sub_tree_value['base_expr'] . ' ';
			break;

		case 'const':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

			} else {

				$defn[] = $sub_tree_value['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';
			}
			break;

		case 'int':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . intval($sub_tree_value['base_expr']) . ' ';

			} else {

				$defn[] = intval($sub_tree_value['base_expr']);
				$sql .= ' {int:const_' . $i++ . '} ';
			}
			break;

		case 'colref':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

			} else {

				$defn[] = $sub_tree_value['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';
			}
			break;

		case 'expression':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

			} else {

				$defn[] = $sub_tree_value['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';

				if (isset($sub_tree_value['alias']))
					$sql .= ' ' . $sub_tree_value['alias']['base_expr'];

				$sql .= ',';
			}
			break;
	}
}

function BotBanishSMFConstructSet($sub_tree_value, &$sql, &$defn, $operation) {

	$i = count($defn);

	switch($sub_tree_value['expr_type']) {

		case 'operator':

			$sql .= $sub_tree_value['base_expr'] . ' ';
			break;

		case 'int':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . intval($sub_tree_value['base_expr']) . ' ';

			} else {

				$defn[] = intval($sub_tree_value['base_expr']);
				$sql .= ' {int:const_' . $i++ . '} ';
			}
			break;

		case 'colref':

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

			} else {

				$defn[] = $sub_tree_value['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';
			}
			break;

		case 'const':
		default:

			if ((SMF_SQL_TRIGGER & $operation) == true) {

				$sql .= ' ' . $sub_tree_value['base_expr'] . ' ';

			} else {

				$defn[] = $sub_tree_value['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';
			}
			break;

	}
}

function BotBanishSMFBuildUpdateClause($value, &$sql, &$table) {
	global $modSettings;

	$data = BotBanishGetDatabaseInfo();
	$sql .= ' UPDATE ';

	foreach($value as $skey => $svalue) {

		foreach($svalue as $sub_key => $sub_value) {

			switch($sub_key) {

				case 'table':

					$table = '`' . $data['database'] . '`.' . $sub_value;
					$sql .= $table;
					break;
			}
		}
	}

	// We must do this for any serialized arrays may have semicolons in them and SMF willl not allow this

	$modSettings['disableQueryCheck'] = true;
}

function BotBanishSMFBuildDeleteClause($value, &$sql, $table) {

	$data = BotBanishGetDatabaseInfo();
	$sql .= ' DELETE ';

	// DELETE FROM clause handled differetly

	if (isset($value['TABLES'])) {

		foreach ($value['TABLES'] as $skey => $svalue) {

			if (stripos($svalue, $data['database']) !== false)
				$table = $svalue;
			else
				$table = '`' . $data['database'] . '`.' . $svalue;

			$sql .= ' ' . $table . ' ';
		}
	}
}

function BotBanishSMFBuildOptionsClause($value, &$sql) {

	foreach ($value as $skey => $svalue)
		$sql .= ' ' . $svalue . ' ';
}

function BotBanishSMFBuildRenameClause($value, &$sql, &$defn) {

	$data = BotBanishGetDatabaseInfo();
	$sql .= ' RENAME TABLE ';
	$i = count($defn);

	$defn[] = '`' . $data['database'] . '`.' . $value[0]['source']['table'];
	$sql .= ' {raw:const_' . $i++ . '} ';

	$sql .= ' TO ';

	$defn[] = '`' . $data['database'] . '`.' . $value[0]['destination']['table'];
	$sql .= ' {raw:const_' . $i++ . '} ';
}

function BotBanishSMFBuildShowClause($value, &$sql, &$defn, $table) {

	$sql .= ' SHOW ';
	$i = count($defn);

	foreach ($value as $skey => $svalue) {

		switch ($svalue['expr_type']) {

			case 'reserved':

				$sql .= $svalue['base_expr'] . ' ';
				break;

			case 'table':
			case 'database':

				$data = BotBanishGetDatabaseInfo();
				$table = '`' . $data['database'] . '`.' . $svalue['base_expr'];
				$sql .= $table . ' ';
				break;

			case 'keys':

				$keys = $svalue['base_expr'];
				$sql .= $keys . ' ';
				break;

			default:

				$defn[] = $svalue['base_expr'];
				$sql .= ' {raw:const_' . $i++ . '} ';
				break;
		}
	}
}


function BotBanishSMFBuildDropClause($value, &$sql, $table) {

	$sql .= ' DROP ';

	if (isset($value['name'])) {

		$data = BotBanishGetDatabaseInfo();
		$table = '`' . $data['database'] . '`.' . $value['name'];
		$sql .= $value['expr_type'] . ' `' . $data['database'] . '`.' . $value['name'];

	} else {

		$sql .=  $value['base_expr'];
	}
}

function BotBanishSMFBuildDescribeClause($value, &$sql, $sql_statement) {

	$sql = '';

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

		switch ($svalue['expr_type']) {

			case 'table':

				$data = BotBanishGetDatabaseInfo();
				$sql .= 'DESCRIBE `' . $data['database'] . '`.' . $svalue['base_expr'];
				break;

			default:
				break;
		}
	}
}

function BotBanishConstructArray(&$arr, $defn) {

	$i = 0;

	foreach($defn as $skey => $svalue)
		$arr['const_' . $i++] = $svalue;
}

function BotBanishSMFBuildTableClause($value, &$sql, &$columns, &$parameters, &$table, &$indexes, &$statements, $operation) {

	$data = BotBanishGetDatabaseInfo();

	if (stripos($value['name'], $data['database']) !== false)
		$table = $value['name'];
	else
		$table = '`' . $data['database'] . '`.' . $value['name'];

	$sql .= ' ' . $table . ' ';

	// If there are no columns found then we may be processing a 'CREATE [table] LIKE [new_table]' statement
	if (SMF_SQL_LIKE & $operation)
		return;

	// Options

	foreach ($value['options'] as $sub_tree_key => $sub_tree_value) {

		if (isset($sub_tree_value['base_expr'])) {

			$set = explode('=', $sub_tree_value['base_expr']);
			$parameters[$set[0]] = $set[1];
		}
	}

	// Columns & Indexes
	// If indexes exist create them, Otherwise indexes should be created later in an ALTER statement

	foreach ($value['create-def']['sub_tree'] as $create_def_key => $create_def_value) {

		foreach ($create_def_value as $skey => $svalue) {

			switch ($skey) {

				case 'expr_type':

					switch ($create_def_value[$skey]) {

						case 'column-def':

							GetColumnInfo($create_def_value['sub_tree'], $columns);
							break;

						case 'primary-key':

							GetPrimaryInfo($create_def_value['sub_tree'], $indexes , $create_def_value[$skey]);
							break;

						case 'index';

							GetIndexInfo($create_def_value['sub_tree'], $indexes, $create_def_value[$skey]);
							break;

						default:
							break;
					}
					break;

				default:
					break;
			}
		}

		// Only use the ALTER statement when any of the indexed columns uses a length
		// SMF cannot handle this type of index creation during the table creation call.
		// We also need to clear the whole index array since we will not be using it
		// in the table creation. We will be creating the indexes in the ALTER statement
		// after table creation

		if (!empty($indexes)) {

			$num = count($indexes) - 1;

			if ($indexes[$num]['alter']) {

				$stmt = 'ALTER TABLE ' . $table . ' ';

				foreach ($indexes as $key => $index) {

					foreach ($index as $skey => $svalue) {

						switch($skey) {

							case 'primary-key':
							case 'index':

								$stmt .= $index[$skey] . ' ';
								break;

							default:
								break;
						}
					}
				}

				$statements[] = $stmt;
				unset($indexes[$num]);
			}
		}
	}
}

function GetColumnInfo($sub_tree, &$columns) {

	$numerics = array('integer', 'int', 'bigint', 'mediumint', 'smallint', 'tinyint', 'bit', 'float', 'double', 'decimal', 'numeric');
	$datetime = array('datetime', 'timestamp', 'date', 'time');

	$column = array();

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

		switch ($value['expr_type']) {

			case 'colref':

				$column['name'] = BotBanishStripQuotes($value['base_expr']);
				break;

			case 'column-type':

				foreach ($value['sub_tree'] as $key1 => $value1) {

					switch($value1['expr_type']) {

						case 'data-type':

							$column['type'] = $value1['base_expr'];

							if (isset($value1['length']))
								$column['size'] = $value1['length'];

							if (isset($value1['unsigned'])) {

								$flag = $value1['unsigned'] == false ? 'false' : 'true' . ',';
								$column['unsigned'] = $flag;
							}

							$flag = (isset($value1['zerofill']) && ($value1['zerofill'] == true)) ? 'true' : 'false';
							$column['not_null'] = $flag;

							if (isset($value1['auto_inc'])) {

								$flag = $value['auto_inc'] == false ? 'false' : 'true';
								$column['auto'] = $flag;
							}
							break;

						case 'default-value':

							// SMF cannot handle a default timestamp or datetime value

							$column['default'] = in_array($column['type'], $numerics) ? intval($value1['base_expr']) : $value1['base_expr'];

							break;

						case 'reserved':

							if ($value1['base_expr'] == 'AUTO_INCREMENT')
								$column['auto'] = true;

							break;
					}
				}
			break;
		}
	}

	$columns[] = $column;
}

function GetIndexInfo($sub_tree, &$indexes, $type = 'index') {

	// Index should start at position 1 of the index unless there is no primary index

	$index = array();
	$index['columns'] = array();
	$index['alter'] = false;
	$index_type = '';
	$columns = array();

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

		switch ($value['expr_type']) {

			case 'index-type':

				$index_type = $value['base_expr'];
				break;

			case 'column-list':

				foreach ($value['sub_tree'] as $key1 => $value1) {

					foreach ($value1 as $key2 => $value2) {

						switch ($key2) {

							case 'base_expr':

								$str = '';

								$columns[] = $value1['no_quotes'];

								switch ($type) {

									case 'primary-key':

										$str =  'ADD PRIMARY KEY ' . $value['base_expr'];
										$index['type'] = 'primary';
										break;

									case 'index':

										$str = 'ADD KEY ' . $value['base_expr'];
										$index['type'] = 'index';
										break;

									default:
										break;
								}

								if (isset($value1['length']) && !empty($value1['length'])) {

									$index['alter'] = true;
									$index[$type] = $str . ' ' . $index_type;
								}
								break;

							default:
								break;
						}
					}

				}
		}
	}

	if (!empty($index)) {

		$index['columns'] = $columns;
		$indexes[] = $index;
	}
}

function GetPrimaryInfo($sub_tree, &$indexes, $type = 'primary-key') {

	// Primary index is ALWAYS at position 0 of the index array

	GetIndexInfo($sub_tree, $indexes, $type);
}

function BotBanishBuildSMFSQLStatementInsert($row, $table) {

	$columns = array();
	$values = array();

	$data = BotBanishGetDatabaseInfo();
	$table_name = str_ireplace(array($data['database'], '`', '.', "'", '"'), '', $table);

	$numerics = array('integer', 'int', 'bigint', 'mediumint', 'smallint', 'tinyint', 'bit', 'float', 'double', 'decimal', 'numeric');
	$table_column = BotBanishGetTableColumns($table_name);
	$table_indexes = BotBanishGetTableUniqueIndexes($table_name);
	$table_auto = BotBanishGetTableAutoIncrement($table_name);
	$table_primary = BotBanishGetTablePrimaryKey($table_name);

	foreach ($table_primary as $key => $value)
		if (!isset($row[$key])) $row[$key] = $value;

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

		if ($key == 'updated') continue;			// Ignore updated columns, they are auto filled

		$keytmp = strtolower($key);
		$value = trim($value, ' ');

		// Check the field against the table's column definition to determine the field type

		if (in_array($table_column[$keytmp], $numerics)) {

			$columns[$key] = 'int';
			$values[] = intval($value);

		} else {

			// Attempt to make sure we don't have too many slashes

			$columns[$key] = 'string';
			$values[] =  BotBanishIsSerialized($value) ? $value : addslashes($value);
		}
	}

	$params = array(
		'columns' => $columns,
		'values' => $values,
	);

	return $params;
}

function BotBanishFixMailQueue() {

	// If the "mail_next_send" setting is set to "0" the MailQueue will fail to work
	// If it is zero, we will set it to current time - 300 so that the MailQueue functions
	// can be fired.

	global $smcFunc, $modSettings, $sourcedir;

	$request = $smcFunc['db_query']('', '
		SELECT `id_mail`
		FROM {db_prefix}mail_queue',
		array(
		)
	);

	$mailtosend = false;

	if (($modSettings['mail_next_send'] < time()) && ($smcFunc['db_num_rows']($request) != 0)) {
		$mailtosend = true;

	} else {

	if (($modSettings['mail_next_send'] == 0) && ($smcFunc['db_num_rows']($request) != 0))
		$mailtosend = true;
	}

	if ($mailtosend) {

		// Make sure we run at least once!!!

		$modSettings['mail_next_send'] = time() - 300;
		updateSettings(array('mail_next_send' => $modSettings['mail_next_send']), true);
		require_once($sourcedir . '/ScheduledTasks.php');
		ReduceMailQueue();

		// Set the time delay between sending the next group of emails

		$delay = !empty($modSettings['mail_queue_delay']) ? $modSettings['mail_queue_delay'] : (!empty($modSettings['mail_limit']) && $modSettings['mail_limit'] < 5 ? 10 : 5);
		$modSettings['mail_next_send'] = time() + $delay; // Make sure the MailQueue can run again
		updateSettings(array('mail_next_send' => $modSettings['mail_next_send']), true);
	}
}

function BotBanishUpdatePrettyURLs() {
	global $sourcedir;

	if (BotBanishIsPrettyURLsInstalled()) {

		require_once($sourcedir . '/Subs-PrettyUrls.php');
		pretty_run_maintenance();
	}
}

function BotBanishRemoveSMFSettings() {

	// Remove BotBanish from Pretty URLs skipaction setting.

	if (defined('SMF')) {

		// See if Pretty URLs is installed
		
		if (BotBanishIsPrettyURLsInstalled()) {

			global $sourcedir, $db_prefix, $modSettings;

			if (isset($modSettings['pretty_skipactions']) && stripos($modSettings['pretty_skipactions'], 'botbanish') !== false) {

				include_once $sourcedir . '/Subs.php';
				$botSettings = array();
				$botSettings['pretty_skipactions'] = str_ireplace(array(',botbanish', 'botbanish,', 'botbanish'), '', $modSettings['pretty_skipactions']);
				updateSettings($botSettings, true);
			}
		}
	}
}

function BotBanishIsPrettyURLsInstalled() {
	global $db_prefix;

	BotBanishSaveOverrideState();
	
	// Check if PrettyUrls is installed

	$table = $db_prefix . 'log_packages';
	$sql = 'SELECT `version` FROM `' . $table . '` WHERE `name` = "Pretty URLs" AND `install_state` = 1';
	$rows = BotBanishExecuteSQL($sql, true, true);
	
	if (!empty($rows)) {
		
		global $sourcedir;
		
		if (file_exists($sourcedir . '/Subs-PrettyUrls.php'))
			return true;
		else
			return false;
	}

	BotBanishRestoreOverrideState();
}

function BotBanishInstallForSMF() {
	
	BotBanishBackupSMFFiles();

	// Import SMF Banned IP's and place into the botbanishclient_ip table

	BotBanishSMFBannedIPImport();

	// Insure that we haven't somehow messed up Pretty Urls by adding BotBanish to it.
	// Run PrettyUrls maintenance task to correct!

	BotBanishUpdatePrettyURLs();

	// This is here because of a flaw in SMF's Load.php that will abort if this file is not found when it looks for it
	
	global $boarddir;
	$themedir = $boarddir . '/Themes';
	$filename1 = $themedir . '/default/css/jquery.sceditor.default.css';
	$filename2 = $themedir . '/default/css/jquery.sceditor.theme.css';
	
	if (!file_exists($filename2))
		copy ($filename1, $filename2);
	
	if (BotBanishIsPrettyURLsInstalled()) {
		
		global $sourcedir;
		require_once $sourcedir . '/Subs-PrettyUrls.php';
		
		if (function_exists('pretty_run_maintenance'))
			pretty_run_maintenance(false, true);
	}
}

function BotBanishBackupSMFFiles() {
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// Let's backup some SMF folders, files and database tables before we start; just in case of
	// install failure. If BotBanish installation fails for ANY reason the SMF files may have
	// already been changed and may render SMF inoperable. In this case take the backed up files
	// and tables and restore them to their original location to restore SMF to it's prior state.
	//////////////////////////////////////////////////////////////////////////////////////////////

	global $boarddir;

	$selections = array(
						$boarddir . '/Sources',
						$boarddir . '/Themes',
						$boarddir . '/.htaccess',
						$boarddir . '/index.php',
						$boarddir . '/SSI.php',
						$boarddir . '/Settings.php',
					);

	BotBanishCompressFolders($selections, 'BotBanish_PreInstallBackup');
}


function BotBanishFindLanguage() {

	global $BotBanishSettings, $user_info;

	// See if BotBanish can match the current selected SMF language

	$index = 0;
	$language_types = $BotBanishSettings['BOTBANISH_LANGUAGES'];

	foreach ($language_types as $key => $botbanish_language)
	{
		if ($botbanish_language == $user_info['language'])
			break;

		$index++;
	}

	if ($index >= count($language_types))
		$index = 0;

	return $index;
}

?>