<?php
/*
 +-------------------------------------------------------------------------+
 | Copyright (C) 2004-2026 The Cacti Group                                 |
 |                                                                         |
 | This program is free software; you can redistribute it and/or           |
 | modify it under the terms of the GNU General Public License             |
 | as published by the Free Software Foundation; either version 2          |
 | of the License, or (at your option) any later version.                  |
 |                                                                         |
 | 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 the           |
 | GNU General Public License for more details.                            |
 +-------------------------------------------------------------------------+
 | Cacti: The Complete RRDtool-based Graphing Solution                     |
 +-------------------------------------------------------------------------+
 | This code is designed, written, and maintained by the Cacti Group. See  |
 | about.php and/or the AUTHORS file for specific developer information.   |
 +-------------------------------------------------------------------------+
 | http://www.cacti.net/                                                   |
 +-------------------------------------------------------------------------+
*/

/** update_replication_crc - update hash stored in settings table to inform
 * remote pollers to replicate tables
 *
 * @param int    $poller_id
 * @param string $variable
 *
 * @return void
 */
function update_replication_crc(int $poller_id, string $variable) : void {
	$hash = hash('ripemd160', date('Y-m-d H:i:s') . random_int(0, mt_getrandmax()) . $poller_id);

	db_execute_prepared("REPLACE INTO settings
		SET value = ?, name='$variable" . ($poller_id > 0 ? '_' . "$poller_id'" : "'"),
		[$hash]);
}

function repopulate_poller_cache() : void {
	include_once(CACTI_PATH_LIBRARY . '/api_data_source.php');

	$poller_data = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id
		FROM data_local AS dl
		INNER JOIN host AS h
		ON dl.host_id=h.id
		WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "")
		ORDER BY h.poller_id ASC, h.id ASC');

	$poller_items   = [];
	$local_data_ids = [];
	$poller_prev    = 1;
	$poller_id      = 1;

	$i = 0;
	$j = 0;

	if (cacti_sizeof($poller_data)) {
		foreach ($poller_data as $data) {
			if ($j == 0) {
				$poller_prev = $data['poller_id'];
			}

			$poller_id = $data['poller_id'];

			if ($i > 500 || $poller_prev != $poller_id) {
				poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_prev);

				$i = 0;

				$local_data_ids = [];
				$poller_items   = [];
			}

			$poller_prev      = $poller_id;
			$poller_items     = array_merge($poller_items, update_poller_cache($data));
			$local_data_ids[] = $data['id'];

			$i++;
			$j++;
		}

		if ($i > 0) {
			poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
		}
	}

	$poller_ids = array_rekey(
		db_fetch_assoc('SELECT DISTINCT poller_id
			FROM poller_item'),
		'poller_id', 'poller_id'
	);

	if (cacti_sizeof($poller_ids)) {
		foreach ($poller_ids as $poller_id) {
			api_data_source_cache_crc_update($poller_id);
		}
	}

	// update the field mappings for the poller
	db_execute('TRUNCATE TABLE poller_data_template_field_mappings');
	db_execute('INSERT IGNORE INTO poller_data_template_field_mappings
		SELECT dtr.data_template_id, dif.data_name,
		GROUP_CONCAT(DISTINCT dtr.data_source_name ORDER BY dtr.data_source_name) AS data_source_names, NOW()
		FROM graph_templates_item AS gti
		INNER JOIN data_template_rrd AS dtr
		ON gti.task_item_id = dtr.id
		INNER JOIN data_input_fields AS dif
		ON dtr.data_input_field_id = dif.id
		WHERE dtr.local_data_id = 0
		AND gti.local_graph_id = 0
		GROUP BY dtr.data_template_id, dif.data_name');

	if (isset($_SESSION[SESS_USER_ID])) {
		cacti_log('NOTE: Poller Cache repopulated by user ' . get_username($_SESSION[SESS_USER_ID]), false, 'PCACHE');
	} else {
		cacti_log('NOTE: Poller Cache repopulated by cli script', false, 'PCACHE');
	}
}

function update_poller_cache_from_query(int $host_id, int $data_query_id, array $local_data_ids) : void {
	include_once(CACTI_PATH_LIBRARY . '/api_data_source.php');

	$poller_data = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' *
		FROM data_local
		WHERE host_id = ?
		AND snmp_query_id = ?
		AND id IN(' . implode(', ', $local_data_ids) . ')
		AND snmp_index != ""',
		[$host_id, $data_query_id]);

	$poller_id = db_fetch_cell_prepared('SELECT poller_id
		FROM host
		WHERE id = ?',
		[$host_id]);

	$i            = 0;
	$poller_items = $local_data_ids = [];

	if (cacti_sizeof($poller_data)) {
		foreach ($poller_data as $data) {
			// every row here shares $host_id, so the poller_id computed once above
			// applies to all of them; no need for update_poller_cache() to re-derive it
			$poller_items     = array_merge($poller_items, update_poller_cache($data, false, $poller_id));
			$local_data_ids[] = $data['id'];
			$i++;

			if ($i > 500) {
				$i = 0;
				poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
				$local_data_ids = [];
				$poller_items   = [];
			}
		}

		if ($i > 0) {
			poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
		}
	}

	if ($poller_id > 1) {
		api_data_source_cache_crc_update($poller_id);
	}
}

/**
 * Rebuilds the poller cache entries for a single data source.
 *
 * @param mixed    $data_source A data_local row, or the local_data_id to read one with.
 * @param bool     $commit      Whether to write the rebuilt entries straight to poller_item.
 * @param int|null $poller_id   The poller the data source belongs to, or null to look it up.
 *
 * @return array The rebuilt poller items, or an empty array when they were committed.
 */
function update_poller_cache(mixed $data_source, bool $commit = false, ?int $poller_id = null) : array {
	include_once(CACTI_PATH_LIBRARY . '/data_query.php');
	include_once(CACTI_PATH_LIBRARY . '/api_poller.php');

	static $template_data_id_data    = [];
	static $host_field_data          = [];
	static $data_template_field_data = [];
	static $num_outfields_data       = [];

	if (!is_array($data_source)) {
		$data_source = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' *
			FROM data_local AS dl
			WHERE id = ?
			AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != ""))',
			[$data_source]);
	}

	$poller_items = [];
	$outputs      = [];

	if (!cacti_sizeof($data_source)) {
		return $poller_items;
	}

	// callers that already know the poller_id (via an explicit argument, or a
	// 'poller_id' key on an array $data_source they joined against host themselves)
	// can skip this per-row lookup entirely
	if ($poller_id === null) {
		$poller_id = $data_source['poller_id'] ?? db_fetch_cell_prepared('SELECT poller_id
			FROM host AS h
			INNER JOIN data_local AS dl
			ON h.id = dl.host_id
			WHERE dl.id = ?',
			[$data_source['id']]);
	}

	$data_input = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . '
		di.id, di.type_id, dtd.id AS data_template_data_id,
		dtd.data_template_id, dtd.active, dtd.rrd_step
		FROM data_template_data AS dtd
		INNER JOIN data_input AS di
		ON dtd.data_input_id = di.id
		WHERE dtd.local_data_id = ?',
		[$data_source['id']]);

	if (cacti_sizeof($data_input)) {
		// Check whitelist for input validation
		if (!data_input_whitelist_check($data_input['id'])) {
			return $poller_items;
		}

		// we have to perform some additional sql queries if this is a 'query'
		if (($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) ||
			($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) ||
			($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)) {
			$field = data_query_field_list($data_input['data_template_data_id']);

			$params = [];

			if (cacti_sizeof($field) && $field['output_type'] != '') {
				// GHSA-xhpr-w454-cc9w: output_type is a stored snmp_query_graph_id
				// interpolated into the query below; cast it so a tampered
				// data-query field cannot inject SQL second-hand.
				$output_type_sql = ' AND sqgr.snmp_query_graph_id = ' . (int) $field['output_type'];
			} else {
				$output_type_sql = '';
			}

			$params[] = $data_input['data_template_id'];
			$params[] = $data_source['id'];

			$outputs = db_fetch_assoc_prepared('SELECT DISTINCT ' . SQL_NO_CACHE . "
				sqgr.snmp_field_name, dtr.id AS data_template_rrd_id
				FROM snmp_query_graph_rrd AS sqgr
				INNER JOIN data_template_rrd AS dtr
				ON sqgr.data_template_rrd_id = dtr.local_data_template_rrd_id
				WHERE sqgr.data_template_id = ?
				AND dtr.local_data_id = ?
				$output_type_sql
				ORDER BY dtr.id", $params);
		}

		if ($data_input['active'] == 'on') {
			if (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT) ||
				($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER)) {
				if ($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER) {
					$action      = POLLER_ACTION_SCRIPT_PHP;
					$script_path = get_full_script_path($data_source['id']);
				} else {
					$action      = POLLER_ACTION_SCRIPT;
					$script_path = get_full_script_path($data_source['id']);
				}

				if (!isset($num_outfields_data[$data_input['id']])) {
					$num_output_fields = cacti_sizeof(db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' id
						FROM data_input_fields
						WHERE data_input_id = ?
						AND input_output = "out"
						AND update_rra = "on"',
						[$data_input['id']]));

					$num_outfields_data[$data_input['id']] = $num_output_fields;
				} else {
					$num_output_fields = $num_outfields_data[$data_input['id']];
				}

				if ($num_output_fields == 1) {
					$data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
						FROM data_template_rrd
						WHERE local_data_id = ?',
						[$data_source['id']]);

					$data_source_item_name = get_data_source_item_name($data_template_rrd_id);
				} else {
					$data_source_item_name = '';
				}

				$poller_items[] = api_poller_cache_item_add($data_source['host_id'], [], $data_source['id'], $data_input['rrd_step'], $action, $data_source_item_name, 1, $script_path);
			} elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP) {
				// get the host override fields
				if (!isset($data_source['data_template_id'])) {
					$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
						FROM data_template_data
						WHERE local_data_id = ?',
						[$data_source['id']]);
				} else {
					$data_template_id = $data_source['data_template_id'];
				}

				if (!isset($template_data_id_data[$data_template_id])) {
					$template_data_template_data_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
						FROM data_template_data
						WHERE data_template_id = ?
						AND local_data_id = 0',
						[$data_template_id]);

					$template_data_id_data[$data_template_id] = $template_data_template_data_id;
				} else {
					$template_data_template_data_id = $template_data_id_data[$data_template_id];
				}

				// get host fields first
				if (!isset($host_field_data[$data_input['data_template_data_id']])) {
					$host_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code IN("hostname", "host_id"))
							AND did.data_template_data_id = ?
							AND did.value != ""', [$data_input['data_template_data_id']]),
						'type_code', 'value'
					);

					$host_field_data[$data_input['data_template_data_id']] = $host_fields;
				} else {
					$host_fields = $host_field_data[$data_input['data_template_data_id']];
				}

				if (!isset($data_template_field_data[$template_data_template_data_id])) {
					$data_template_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code = "hostname")
							AND did.data_template_data_id = ?
							AND did.value != ""', [$template_data_template_data_id]),
						'type_code', 'value'
					);

					$data_template_field_data[$template_data_template_data_id] = $data_template_fields;
				} else {
					$data_template_fields = $data_template_field_data[$template_data_template_data_id];
				}

				if (cacti_sizeof($host_fields)) {
					if (cacti_sizeof($data_template_fields)) {
						foreach ($data_template_fields as $key => $value) {
							if (!isset($host_fields[$key])) {
								$host_fields[$key] = $value;
							}
						}
					}
				} elseif (cacti_sizeof($data_template_fields)) {
					$host_fields = $data_template_fields;
				}

				$data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
					FROM data_template_rrd
					WHERE local_data_id = ?',
					[$data_source['id']]);

				$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($data_template_rrd_id), 1, ($host_fields['snmp_oid'] ?? ''));
			} elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) {
				$snmp_queries = get_data_query_array($data_source['snmp_query_id']);

				// get the host override fields
				if (!isset($data_source['data_template_id'])) {
					$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
						FROM data_template_data
						WHERE local_data_id = ?',
						[$data_source['id']]);
				} else {
					$data_template_id = $data_source['data_template_id'];
				}

				if (!isset($template_data_id_data[$data_template_id])) {
					$template_data_template_data_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
						FROM data_template_data
						WHERE data_template_id = ?
						AND local_data_id = 0',
						[$data_template_id]);

					$template_data_id_data[$data_template_id] = $template_data_template_data_id;
				} else {
					$template_data_template_data_id = $template_data_id_data[$data_template_id];
				}

				// get host fields first
				if (!isset($host_field_data[$data_input['data_template_data_id']])) {
					$host_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code IN("hostname", "host_id"))
							AND did.data_template_data_id = ?
							AND did.value != ""', [$data_input['data_template_data_id']]),
						'type_code', 'value'
					);

					$host_field_data[$data_input['data_template_data_id']] = $host_fields;
				} else {
					$host_fields = $host_field_data[$data_input['data_template_data_id']];
				}

				if (!isset($data_template_field_data[$template_data_template_data_id])) {
					$data_template_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code = "hostname")
							AND did.data_template_data_id = ?
							AND did.value != ""', [$template_data_template_data_id]),
						'type_code', 'value'
					);

					$data_template_field_data[$template_data_template_data_id] = $data_template_fields;
				} else {
					$data_template_fields = $data_template_field_data[$template_data_template_data_id];
				}

				if (cacti_sizeof($host_fields)) {
					if (cacti_sizeof($data_template_fields)) {
						foreach ($data_template_fields as $key => $value) {
							if (!isset($host_fields[$key])) {
								$host_fields[$key] = $value;
							}
						}
					}
				} elseif (cacti_sizeof($data_template_fields)) {
					$host_fields = $data_template_fields;
				}

				if (cacti_sizeof($outputs) && cacti_sizeof($snmp_queries)) {
					foreach ($outputs as $output) {
						/* $oid is assigned inside the guard below but read after
						   it, so an output with no 'oid' mapping would otherwise
						   be cached against the previous output's OID. */
						unset($oid);

						if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid'])) {
							$oid = $snmp_queries['fields'][$output['snmp_field_name']]['oid'] . '.' . $data_source['snmp_index'];

							if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix'])) {
								$oid .= '.' . $snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix'];
							}
						}

						if (!empty($oid)) {
							$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $oid);
						}
					}
				}
			} elseif (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) ||
				($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)) {
				$script_queries = get_data_query_array($data_source['snmp_query_id']);

				// get the host override fields
				if (!isset($data_source['data_template_id'])) {
					$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
						FROM data_template_data
						WHERE local_data_id = ?',
						[$data_source['id']]);
				} else {
					$data_template_id = $data_source['data_template_id'];
				}

				$template_data_template_data_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
					FROM data_template_data
					WHERE data_template_id = ?
					AND local_data_id = 0',
					[$data_template_id]);

				// get host fields first
				if (!isset($host_field_data[$data_input['data_template_data_id']])) {
					$host_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code IN("hostname", "host_id"))
							AND did.data_template_data_id = ?
							AND did.value != ""', [$data_input['data_template_data_id']]),
						'type_code', 'value'
					);

					$host_field_data[$data_input['data_template_data_id']] = $host_fields;
				} else {
					$host_fields = $host_field_data[$data_input['data_template_data_id']];
				}

				if (!isset($data_template_field_data[$template_data_template_data_id])) {
					$data_template_fields = array_rekey(
						db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
							FROM data_input_fields AS dif
							LEFT JOIN data_input_data AS did
							ON dif.id = did.data_input_field_id
							WHERE (type_code LIKE "snmp_%" OR type_code = "hostname")
							AND did.data_template_data_id = ?
							AND did.value != ""', [$template_data_template_data_id]),
						'type_code', 'value'
					);

					$data_template_field_data[$template_data_template_data_id] = $data_template_fields;
				} else {
					$data_template_fields = $data_template_field_data[$template_data_template_data_id];
				}

				if (cacti_sizeof($host_fields)) {
					if (cacti_sizeof($data_template_fields)) {
						foreach ($data_template_fields as $key => $value) {
							if (!isset($host_fields[$key])) {
								$host_fields[$key] = $value;
							}
						}
					}
				} elseif (cacti_sizeof($data_template_fields)) {
					$host_fields = $data_template_fields;
				}

				if (cacti_sizeof($outputs) && cacti_sizeof($script_queries)) {
					foreach ($outputs as $output) {
						$action = POLLER_ACTION_NONE;

						/* Same leak as the SNMP branch: the post-guard read is
						   isset($script_path), so unset() rather than a null. */
						unset($script_path);

						if (isset($script_queries['fields'][$output['snmp_field_name']]['query_name'])) {
							$identifier = $script_queries['fields'][$output['snmp_field_name']]['query_name'];

							if ($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER) {
								$action = POLLER_ACTION_SCRIPT_PHP;

								$prepend = '';

								if (isset($script_queries['arg_prepend']) && $script_queries['arg_prepend'] != '') {
									$prepend = $script_queries['arg_prepend'];
								}

								$script_path = get_script_query_path(trim($prepend . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' "' . $data_source['snmp_index'] . '"'), $script_queries['script_path'] . ' ' . $script_queries['script_function'], $data_source['host_id']);
							} else {
								$action      = POLLER_ACTION_SCRIPT;
								$script_path = get_script_query_path(trim(($script_queries['arg_prepend'] ?? '') . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' "' . $data_source['snmp_index'] . '"'), $script_queries['script_path'], $data_source['host_id']);
							}
						}

						if (isset($script_path)) {
							$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], $action, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $script_path);
						}
					}
				}
			} else {
				$arguments = [
					'poller_items' => $poller_items,
					'data_source'  => $data_source,
					'data_input'   => $data_input
				];

				$arguments = api_plugin_hook_function('data_source_to_poller_items', $arguments);

				// Process the returned poller items
				if ((isset($arguments['poller_items'])) &&
					(is_array($arguments['poller_items'])) &&
					(cacti_sizeof($poller_items) < cacti_sizeof($arguments['poller_items']))) {
					$poller_items = $arguments['poller_items'];
				}
			}
		}
	} else {
		$data_template_data = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' *
			FROM data_template_data
			WHERE local_data_id = ?',
			[$data_source['id']]);

		if (cacti_sizeof($data_template_data) && $data_template_data['data_input_id'] > 0) {
			cacti_log('WARNING: Repopulate Poller Cache found Data Input Missing for Data Source ' . $data_source['id'] . '.  Database may be corrupted', false, 'PCACHE');
		}
	}

	if ($commit && cacti_sizeof($poller_items)) {
		poller_update_poller_cache_from_buffer([$data_source['id']], $poller_items, $poller_id);
	} elseif (!$commit) {
		return $poller_items;
	}

	return [];
}

function push_out_data_input_method(int $data_input_id) : void {
	$data_sources = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id
		FROM data_local AS dl
		INNER JOIN (
			SELECT DISTINCT local_data_id
			FROM data_template_data
			WHERE data_input_id = ?
			AND local_data_id > 0
		) AS dtd
		ON dtd.local_data_id = dl.id
		INNER JOIN host AS h
		ON h.id = dl.host_id
		WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "")
		ORDER BY h.poller_id ASC',
		[$data_input_id]);

	$poller_items       = [];
	$_my_local_data_ids = [];

	if (cacti_sizeof($data_sources)) {
		$prev_poller = -1;

		foreach ($data_sources as $data_source) {
			if ($prev_poller > 0 && $data_source['poller_id'] != $prev_poller) {
				poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller);
				$_my_local_data_ids = [];
				$poller_items       = [];
			} else {
				$_my_local_data_ids[] = $data_source['id'];
				$poller_items         = array_merge($poller_items, update_poller_cache($data_source));
			}

			$prev_poller = $data_source['poller_id'];
		}

		if (cacti_sizeof($_my_local_data_ids)) {
			poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller);
		}
	}
}

/**
 * mass update of poller cache - can run in parallel to poller
 *
 * @param array $local_data_ids Either a scalar (all ids) or an array of data source to act on
 * @param array $poller_items   The new items for poller cache
 * @param int   $poller_id      The poller_id of the buffer
 *
 * @return void
 */
function poller_update_poller_cache_from_buffer(array $local_data_ids, array &$poller_items, int $poller_id = 1) : void {
	$ids    = '';
	$raised = false;

	// set all fields present value to 0, to mark the outliers when we are all done
	if (cacti_sizeof($local_data_ids)) {
		$ids = implode(', ', $local_data_ids);

		if ($ids != '') {
			db_execute_prepared("UPDATE poller_item
				SET present = 0
				WHERE poller_id = ?
				AND local_data_id IN ($ids)",
				[$poller_id]);

			if ($poller_id > 1) {
				if (remote_poller_up($poller_id)) {
					if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
						db_execute_prepared("UPDATE poller_item
							SET present = 0
							WHERE poller_id = ?
							AND local_data_id IN ($ids)",
							[$poller_id], true, $rcnn_id);
					} else {
						raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
						$raised = true;
					}
				} else {
					raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
					$raised = true;
				}
			}
		}
	} else {
		/* don't mark anything in case we have no $local_data_ids =>
		 *this would flush the whole table at bottom of this function */
	}

	// setup the database call
	$sql_prefix = 'INSERT INTO poller_item (local_data_id, poller_id, host_id, action, hostname, ' .
		'snmp_community, snmp_version, snmp_timeout, snmp_retries, snmp_username, snmp_password, ' .
		'snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, snmp_engine_id, ' .
		'snmp_port, rrd_name, rrd_path, rrd_num, rrd_step, rrd_next_step, arg1, arg2, arg3, present) ' .
		'VALUES';

	$sql_suffix = ' ON DUPLICATE KEY UPDATE
		poller_id = VALUES(poller_id),
		host_id = VALUES(host_id),
		action = VALUES(action),
		hostname = VALUES(hostname),
		snmp_community = VALUES(snmp_community),
		snmp_version = VALUES(snmp_version),
		snmp_timeout = VALUES(snmp_timeout),
		snmp_retries = VALUES(snmp_retries),
		snmp_username = VALUES(snmp_username),
		snmp_password = VALUES(snmp_password),
		snmp_auth_protocol = VALUES(snmp_auth_protocol),
		snmp_priv_passphrase = VALUES(snmp_priv_passphrase),
		snmp_priv_protocol = VALUES(snmp_priv_protocol),
		snmp_context = VALUES(snmp_context),
		snmp_engine_id = VALUES(snmp_engine_id),
		snmp_port = VALUES(snmp_port),
		rrd_path = VALUES(rrd_path),
		rrd_num = VALUES(rrd_num),
		rrd_step = VALUES(rrd_step),
		rrd_next_step = VALUES(rrd_next_step),
		arg1 = VALUES(arg1),
		arg2 = VALUES(arg2),
		arg3 = VALUES(arg3),
		present = 1';

	// use a reasonable insert buffer, the default is 1MByte
	$max_packet   = 256000;

	// setup some defaults
	$overhead     = strlen($sql_prefix) + strlen($sql_suffix);
	$buf_len      = 0;
	$buf_count    = 0;
	$buffer       = '';

	if (cacti_sizeof($poller_items)) {
		foreach ($poller_items as $record) {
			// take care of invalid entries
			if ($record == '') {
				continue;
			}

			if ($buf_count == 0) {
				$delim = ' ';
			} else {
				$delim = ', ';
			}

			$buffer .= $delim . $record;

			$buf_len += strlen($record);

			if ($overhead + $buf_len > $max_packet - 1024) {
				db_execute($sql_prefix . $buffer . $sql_suffix);

				if ($poller_id > 1) {
					if (remote_poller_up($poller_id)) {
						if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
							db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id);
						} elseif (!$raised) {
							raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
							$raised = true;
						}
					} elseif (!$raised) {
						raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
						$raised = true;
					}
				}

				$buffer    = '';
				$buf_len   = 0;
				$buf_count = 0;
			} else {
				$buf_count++;
			}
		}
	}

	if ($buf_count > 0) {
		db_execute($sql_prefix . $buffer . $sql_suffix);

		if ($poller_id > 1) {
			if (remote_poller_up($poller_id)) {
				if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
					db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id);
				} else {
					raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
					$raised = true;
				}
			} elseif (!$raised) {
				raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
				$raised = true;
			}
		}
	}

	// remove stale records FROM the poller cache
	if ($ids != '') {
		db_execute_prepared("DELETE FROM poller_item
			WHERE present = 0
			AND poller_id = ?
			AND local_data_id IN ($ids)",
			[$poller_id]);

		if ($poller_id > 1) {
			if (remote_poller_up($poller_id)) {
				if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
					db_execute_prepared("DELETE FROM poller_item
						WHERE present = 0
						AND poller_id = ?
						AND local_data_id IN ($ids)",
						[$poller_id], true, $rcnn_id);
				} elseif (!$raised) {
					raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
				}
			} elseif (!$raised) {
				raise_message('poller_down_' . $poller_id, __esc('Remote Poller %s is Down, you will need to perform a FullSync once it is up again', $poller_id), MESSAGE_LEVEL_WARN);
			}
		}
	}

	/**
	 * Save the last time a device/site was created/updated
	 * for Caching.
	 */
	set_config_option('time_last_change_poller_item', time());

	/* Only SNMPv3 credentials live in the shared credential cache. Bump the version
	 * only when this flush touched v3 poller items, so a non-v3 (or non-SNMP) data
	 * source change does not force a needless credential cache rebuild. */
	if ($ids != '') {
		$v3_items = db_fetch_cell_prepared("SELECT COUNT(*) FROM poller_item
			WHERE poller_id = ?
			AND local_data_id IN ($ids)
			AND snmp_version = 3",
			[$poller_id]);
	} else {
		$v3_items = db_fetch_cell('SELECT COUNT(*) FROM poller_item WHERE snmp_version = 3');
	}

	if ($v3_items > 0) {
		$cred_version = uniqid('', true);
		set_config_option('snmp_cred_version', $cred_version);

		/* A remote collector builds its host-local credential cache from its own
		 * database copy and reads this token from its own settings, so the main
		 * database bump alone never invalidates it. Push the same token to the
		 * affected remote poller so its next poll rebuilds the cache instead of
		 * serving stale/rotated credentials until the daily out-of-band rebuild. */
		if ($poller_id > 1 && remote_poller_up($poller_id)) {
			if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
				db_execute_prepared('REPLACE INTO settings (name, value) VALUES (\'snmp_cred_version\', ?)',
					[$cred_version], true, $rcnn_id);
			}
		}
	}
}

/**
 * for a given data template, update all input data and the poller cache
 *
 * @param int $host_id          The id of host, if any
 * @param int $local_data_id    The id of a single data source, if any
 * @param int $data_template_id The id of data template
 *                              works on table data_input_data and poller cache
 *
 * @return void
 */
function push_out_host(int $host_id, int $local_data_id = 0, int $data_template_id = 0) : void {
	include_once(CACTI_PATH_LIBRARY . '/api_data_source.php');

	/* ok here's the deal: first we need to find every data source that uses this host.
	then we go through each of those data sources, finding each one using a data input method
	with "special fields". if we find one, fill it will the data here FROM this host */
	// setup the poller items array
	$poller_items    = [];
	$local_data_ids  = [];
	$hosts           = [];
	$template_fields = [];
	$sql_where       = '';
	$sql_where_p     = '';

	// setup the sql where, and if using a host, get it's host information
	if ($host_id > 0) {
		// get all information about this host so we can write it to the data source
		$hosts[$host_id] = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' id AS host_id, host.*
			FROM host WHERE id = ?',
			[$host_id]);

		$sql_where .= ' AND dl.host_id=' . $host_id;
		$sql_where_p .= ' dl.host_id=' . $host_id;
	}

	// sql WHERE for local_data_id
	if ($local_data_id > 0) {
		$sql_where .= ' AND dl.id = ' . $local_data_id;
		$sql_where_p .= ($sql_where_p != '' ? ' AND' : '') . ' dl.id = ' . $local_data_id;
	}

	// sql WHERE for data_template_id
	if ($data_template_id > 0) {
		$sql_where .= ' AND dtd.data_template_id = ' . $data_template_id;
		$sql_where_p .= ($sql_where_p != '' ? ' AND' : '') . ' dtd.data_template_id = ' . $data_template_id;
	}

	$data_sources = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . " dtd.id,
		dtd.data_input_id, dtd.local_data_id,
		dtd.local_data_template_data_id, dl.host_id,
		dl.data_template_id,
		dl.snmp_query_id, dl.snmp_index
		FROM data_local AS dl
		INNER JOIN data_template_data AS dtd
		ON dl.id = dtd.local_data_id
		WHERE dtd.data_input_id > 0
		AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != ''))
		$sql_where");

	/**
	 * Mark poller_item as not present to remove stray entries in the end
	 */
	db_execute_prepared("UPDATE poller_item AS pi
		INNER JOIN data_local AS dl
		ON pi.local_data_id = dl.id
		INNER JOIN data_template_data AS dtd
		ON dl.id = dtd.local_data_id
		SET pi.present = 0
		WHERE $sql_where_p");

	// loop through each matching data source
	if (cacti_sizeof($data_sources)) {
		foreach ($data_sources as $data_source) {
			// set the host information
			if (!isset($hosts[$data_source['host_id']])) {
				$hosts[$data_source['host_id']] = db_fetch_row_prepared('SELECT *
					FROM host
					WHERE id = ?',
					[$data_source['host_id']]);
			}
			$host = $hosts[$data_source['host_id']];

			/**
			 * get field information FROM the data template
			 */
			if (!isset($template_fields[$data_source['local_data_template_data_id']])) {
				$template_fields[$data_source['local_data_template_data_id']] = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . '
					did.value, did.t_value, dif.id, dif.type_code
					FROM data_input_fields AS dif
					INNER JOIN data_input_data AS did
					ON dif.id = did.data_input_field_id
					WHERE dif.data_input_id = ?
					AND did.data_template_data_id = ?
					AND (did.t_value = "" OR did.t_value IS NULL OR (did.t_value = "on" AND did.value = ""))
					AND dif.input_output = "in"',
					[$data_source['data_input_id'], $data_source['local_data_template_data_id']]);
			}

			/**
			 * loop through each field contained in the data template and push out a host value if:
			 * - the field is a valid "host field" and it's not set to be over-written
			 * - in other words the t_value is not checked
			 */
			if (cacti_sizeof($template_fields[$data_source['local_data_template_data_id']])) {
				foreach ($template_fields[$data_source['local_data_template_data_id']] as $template_field) {
					$update = false;
					$field  = '';

					// handle special case type_code
					if ($template_field['type_code'] == 'host_id') {
						$field = 'id';
					}

					// Only override if the template value is null as this point
					if (preg_match('/^' . VALID_HOST_FIELDS . '$/i', $template_field['type_code'])) {
						if ($template_field['t_value'] != 'on') {
							// It's a valid host type-code
							$update = true;

							if ($template_field['type_code'] != 'host_id') {
								$field = $template_field['type_code'];
							}
						}
					}

					if ($update) {
						// Don't mess with a field that does not exist
						// In the host table
						if (isset($host[$field])) {
							$old_data = db_fetch_row_prepared('SELECT *
								FROM data_input_data
								WHERE data_input_field_id = ?
								AND data_template_data_id = ?',
								[$template_field['id'], $data_source['id']]);

							if (isset($old_value['value']) && $old_data['value'] != $host[$field]) {
								cacti_log("WARNING: Poller Cache updated Device[{$host['id']}], Field[$field], Old[{$old_data['value']}], New[{$host[$field]}]", false, 'PCACHE', POLLER_VERBOSITY_MEDIUM);
							}

							db_execute_prepared('REPLACE INTO data_input_data
								(data_input_field_id, data_template_data_id, data_template_id, local_data_id, host_id, value)
								VALUES (?, ?, ?, ?, ?, ?)',
								[
									$template_field['id'],
									$data_source['id'],
									$data_source['data_template_id'],
									$data_source['local_data_id'],
									$data_source['host_id'],
									$host[$field]
								]
							);
						}
					}
				}
			}

			/**
			 * flag an update to the poller cache as well
			 */
			$local_data_ids[] = $data_source['local_data_id'];

			// create a new compatible structure
			$data       = $data_source;
			$data['id'] = $data['local_data_id'];

			// $host was already looked up above for this data source's host_id
			$data['poller_id'] = $host['poller_id'];

			$poller_items = array_merge($poller_items, update_poller_cache($data));
		}
	}

	if (cacti_sizeof($hosts)) {
		$poller_ids = [];

		foreach ($hosts as $host) {
			if (isset($host['poller_id'])) {
				$poller_ids[$host['poller_id']] = $host['poller_id'];
			}
		}

		if (cacti_sizeof($poller_ids) > 1) {
			cacti_log('WARNING: function push_out_host() discovered more than a single host', false, 'PCACHE');
		}
	}

	$poller_id = db_fetch_cell_prepared('SELECT poller_id
		FROM host
		WHERE id = ?',
		[$host_id]);

	if (cacti_sizeof($local_data_ids)) {
		poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
	}

	/**
	 * Remove orphaned entries now
	 */
	$sql_where_p .= ($sql_where_p != '' ? ' AND ' : '') . ' present = 0';

	db_execute_prepared("DELETE pi
		FROM poller_item AS pi
		INNER JOIN data_local AS dl
		ON pi.local_data_id = dl.id
		INNER JOIN data_template_data AS dtd
		ON dl.id = dtd.local_data_id
		WHERE $sql_where_p");

	api_data_source_cache_crc_update($poller_id);
}

function data_input_whitelist_check(int $data_input_id) : bool {
	static $data_input_whitelist = null;
	static $validated_input_ids  = null;
	static $notified             = [];

	// no whitelist file defined, everything whitelisted
	if (!defined('CACTI_WHITELIST')) {
		return true;
	}

	// whitelist is configured but does not exist, means nothing whitelisted
	if (!file_exists(CACTI_WHITELIST)) {
		return false;
	}

	// load whitelist, but only once within process execution
	if ($data_input_whitelist == null) {
		$data_input_ids = array_rekey(
			db_fetch_assoc('SELECT * FROM data_input'),
			'hash', ['id', 'name', 'input_string']
		);

		$data_input_whitelist = json_decode((string) file_get_contents(CACTI_WHITELIST), true);

		if ($data_input_whitelist === null) {
			cacti_log('ERROR: Failed to parse input whitelist file: ' . CACTI_WHITELIST);

			return true;
		}

		if (cacti_sizeof($data_input_ids)) {
			foreach ($data_input_ids as $hash => $id) {
				if ($id['input_string'] != '') {
					if (isset($data_input_whitelist[$hash])) {
						if ($data_input_whitelist[$hash] == $id['input_string']) {
							$validated_input_ids[$id['id']] = true;
						} else {
							cacti_log('ERROR: Whitelist entry failed validation for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ].  Data Collection will not run.  Run CLI command input_whitelist.php --audit and --update to remediate.');
							$validated_input_ids[$id['id']] = false;
						}
					} else {
						cacti_log('WARNING: Whitelist entry missing for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ].  Run CLI command input_whitelist.php --update to remediate.');
						$validated_input_ids[$id['id']] = true;
					}
				} else {
					$validated_input_ids[$id['id']] = true;
				}
			}
		}
	}

	if (isset($validated_input_ids[$data_input_id])) {
		if ($validated_input_ids[$data_input_id] == true) {
			return true;
		} else {
			cacti_log('WARNING: Data Input ' . $data_input_id . ' failing validation check.');
			$notified[$data_input_id] = true;

			return false;
		}
	} else {
		return true;
	}
}

/**
 * utilities_mysql_variable_capabilities - return the support matrix for every
 * MySQL/MariaDB system variable that Cacti recommends tuning.  Each entry
 * carries a human readable description and its available settings (used for the
 * hover hint), plus the per-engine version boundaries at which the variable was
 * introduced, deprecated or removed.  A missing engine key means the variable
 * does not exist for that engine (rendered as N/A); an empty engine array means
 * the variable is supported for every version of that engine.
 *
 * @return array the capability matrix keyed by variable name
 */
function utilities_mysql_variable_capabilities() : array {
	static $matrix = null;

	if ($matrix !== null) {
		return $matrix;
	}

	$matrix = [
		'collation_server' => [
			'description' => __('Default collation used by the server when creating databases and tables.'),
			'settings'    => __('e.g. utf8mb4_unicode_ci, utf8mb4_general_ci.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'character_set_server' => [
			'description' => __('Default character set used by the server.'),
			'settings'    => __('e.g. utf8mb4, utf8, latin1.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'character_set_client' => [
			'description' => __('Character set the client uses when sending statements to the server.'),
			'settings'    => __('e.g. utf8mb4, utf8, latin1.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'max_connections' => [
			'description' => __('Maximum number of simultaneous client connections permitted.'),
			'settings'    => __('Integer; default 151. Allow enough for the pollers plus user logins.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'table_cache' => [
			'description' => __('Legacy name for the open-table cache; renamed to table_open_cache.'),
			'settings'    => __('Integer. Use table_open_cache on modern servers.'),
			'MariaDB'     => ['removed' => '10.0.0'],
			'MySQL'       => ['removed' => '5.1.3'],
		],
		'max_allowed_packet' => [
			'description' => __('Maximum size of one packet or any generated/intermediate string.'),
			'settings'    => __('Bytes; keep at or above 16M for remote pollers.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'max_heap_table_size' => [
			'description' => __('Maximum size to which user-created MEMORY tables may grow.'),
			'settings'    => __('Bytes; sized relative to total system memory.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'tmp_table_size' => [
			'description' => __('Maximum size of internal in-memory temporary tables before they spill to disk.'),
			'settings'    => __('Bytes; a larger value keeps temporary tables in memory.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'join_buffer_size' => [
			'description' => __('Minimum per-join buffer size for joins that do not use indexes.'),
			'settings'    => __('Bytes; per-connection allocation, default 262144.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'sort_buffer_size' => [
			'description' => __('Per-session buffer allocated for sorts (ORDER BY / GROUP BY).'),
			'settings'    => __('Bytes; per-connection allocation, default 2097152.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_file_per_table' => [
			'description' => __('Store each InnoDB table and its indexes in its own .ibd tablespace file.'),
			'settings'    => __('ON or OFF; ON is recommended.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_file_format' => [
			'description' => __('InnoDB on-disk file format (Antelope or Barracuda); Barracuda is now the only format.'),
			'settings'    => __('Antelope, Barracuda.'),
			'MariaDB'     => ['deprecated' => '10.2.2', 'removed' => '10.3.1'],
			'MySQL'       => ['deprecated' => '5.7.7', 'removed' => '8.0.0'],
		],
		'innodb_large_prefix' => [
			'description' => __('Allow index key prefixes longer than 767 bytes (requires the Barracuda format).'),
			'settings'    => __('ON/1 or OFF/0; this behaviour is now always enabled.'),
			'MariaDB'     => ['deprecated' => '10.2.2', 'removed' => '10.3.1'],
			'MySQL'       => ['deprecated' => '5.7.7', 'removed' => '8.0.0'],
		],
		'innodb_buffer_pool_size' => [
			'description' => __('Total memory InnoDB uses to cache table and index data.'),
			'settings'    => __('Bytes; commonly 25%-80% of total system memory.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_doublewrite' => [
			'description' => __('Write pages twice (the doublewrite buffer) to protect against partial page writes.'),
			'settings'    => __('ON or OFF.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_additional_mem_pool_size' => [
			'description' => __('Legacy pool for the InnoDB internal data dictionary and structures.'),
			'settings'    => __('Bytes; obsolete on modern servers.'),
			'MariaDB'     => ['removed' => '10.0.0'],
			'MySQL'       => ['removed' => '5.7.4'],
		],
		'innodb_lock_wait_timeout' => [
			'description' => __('Seconds an InnoDB transaction waits for a row lock before giving up.'),
			'settings'    => __('Seconds; default 50.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_flush_method' => [
			'description' => __('Method used to flush data and log files to disk.'),
			'settings'    => __('O_DIRECT, fsync, O_DSYNC, and similar.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_flush_log_at_trx_commit' => [
			'description' => __('Controls how the redo log is written and flushed at transaction commit.'),
			'settings'    => __('0, 1, or 2.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_file_io_threads' => [
			'description' => __('Legacy single setting for InnoDB file I/O threads, replaced by the read/write I/O thread settings.'),
			'settings'    => __('Integer; superseded by innodb_read_io_threads and innodb_write_io_threads.'),
			'MariaDB'     => ['removed' => '5.5.0'],
			'MySQL'       => ['removed' => '5.5.0'],
		],
		'innodb_flush_log_at_timeout' => [
			'description' => __('Write and flush the InnoDB redo log every N seconds.'),
			'settings'    => __('Seconds (1-2700); default 1.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_read_io_threads' => [
			'description' => __('Number of I/O threads used for read operations in InnoDB.'),
			'settings'    => __('1-64; default 4.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_write_io_threads' => [
			'description' => __('Number of I/O threads used for write operations in InnoDB.'),
			'settings'    => __('1-64; default 4.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_buffer_pool_instances' => [
			'description' => __('Number of regions the InnoDB buffer pool is divided into.'),
			'settings'    => __('1-64; MariaDB removed this in 10.6.'),
			'MariaDB'     => ['deprecated' => '10.5.0', 'removed' => '10.6.0'],
			'MySQL'       => [],
		],
		'innodb_io_capacity' => [
			'description' => __('Estimated I/O operations per second available to InnoDB background tasks.'),
			'settings'    => __('Integer; use higher values for SSD/NVMe storage.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_io_capacity_max' => [
			'description' => __('Maximum IOPS InnoDB may use when flushing falls behind.'),
			'settings'    => __('Integer; use higher values for SSD/NVMe storage.'),
			'MariaDB'     => [],
			'MySQL'       => [],
		],
		'innodb_flush_neighbors' => [
			'description' => __('Flush neighbouring dirty pages in the same extent when flushing a page.'),
			'settings'    => __('0, 1, or 2; use 0 on SSD storage.'),
			'MariaDB'     => [],
			'MySQL'       => ['deprecated' => '8.0.20', 'removed' => '8.4.0'],
		],
		'innodb_use_atomic_writes' => [
			'description' => __('Use hardware atomic writes (MariaDB) so the doublewrite buffer can be safely disabled.'),
			'settings'    => __('ON or OFF; MariaDB only.'),
			'MariaDB'     => [],
		],
		'innodb_snapshot_isolation' => [
			'description' => __('Enforce strict snapshot isolation for InnoDB transactions.'),
			'settings'    => __('ON or OFF; back-ported to MariaDB 10.6.18+, 10.11.8+ and 11.4.2+. Set OFF for the Cacti poller.'),
			'MariaDB'     => ['introduced' => ['10.6.18', '10.11.8', '11.4.2']],
		],
	];

	return $matrix;
}

/**
 * utilities_mysql_normalize_version - reduce a server version string to its
 * leading numeric dotted component so version_compare() behaves predictably.
 * Server version() output often carries suffixes such as '-1:10.11.2+maria...'
 * or '-0ubuntu0.22.04.1' that version_compare() treats as pre-release markers.
 *
 * @param string $version the raw version string
 *
 * @return string the numeric major.minor.patch prefix, or the input unchanged
 */
function utilities_mysql_normalize_version(string $version) : string {
	if (preg_match('/^[0-9]+(\.[0-9]+)*/', $version, $matches)) {
		return $matches[0];
	}

	return $version;
}

/**
 * utilities_mysql_branch - return the major.minor branch of a version string.
 *
 * @param string $version the version string, e.g. '10.6.18'
 *
 * @return string the branch, e.g. '10.6'
 */
function utilities_mysql_branch(string $version) : string {
	$parts = explode('.', $version);

	return $parts[0] . '.' . ($parts[1] ?? '0');
}

/**
 * utilities_mysql_version_introduced - decide whether a variable that was
 * introduced (and often back-ported to several stable branches) exists in the
 * given version.  The variable is present when the version is at or after the
 * introduction point for its own major.minor branch, or when the version lives
 * on a branch newer than every branch that received a back-port (the feature
 * then ships natively).
 *
 * @param string       $version the detected engine version string
 * @param array|string $intro   one version, or a list of per-branch versions
 *
 * @return bool true when the variable exists in the version
 */
function utilities_mysql_version_introduced(string $version, array|string $intro) : bool {
	if (!is_array($intro)) {
		$intro = [$intro];
	}

	$vbranch = utilities_mysql_branch($version);
	$newest  = '0';

	foreach ($intro as $floor) {
		$fbranch = utilities_mysql_branch((string) $floor);

		if (version_compare($fbranch, $newest, '>')) {
			$newest = $fbranch;
		}

		if ($vbranch === $fbranch) {
			return version_compare($version, (string) $floor, '>=');
		}
	}

	return version_compare($vbranch, $newest, '>');
}

/**
 * utilities_mysql_variable_status - determine the support status of a single
 * capability-matrix variable for a specific database engine and version.
 *
 * @param array       $cap      a single entry from utilities_mysql_variable_capabilities()
 * @param string      $database the detected engine, either 'MariaDB' or 'MySQL'
 * @param string|null $version  the detected engine version string, or null/empty when unknown
 *
 * @return string one of 'ok', 'deprecated', 'removed' or 'na'
 */
function utilities_mysql_variable_status(array $cap, string $database, ?string $version) : string {
	if (!isset($cap[$database]) || !is_array($cap[$database])) {
		return 'na';
	}

	if ($version === null || $version === '') {
		return 'ok';
	}

	$version = utilities_mysql_normalize_version($version);
	$bounds  = $cap[$database];

	if (isset($bounds['introduced']) && !utilities_mysql_version_introduced($version, $bounds['introduced'])) {
		return 'na';
	}

	if (isset($bounds['removed']) && version_compare($version, $bounds['removed'], '>=')) {
		return 'removed';
	}

	if (isset($bounds['deprecated']) && version_compare($version, $bounds['deprecated'], '>=')) {
		return 'deprecated';
	}

	return 'ok';
}

/**
 * utilities_mysql_capability_cell - render the glyph for a capability-matrix
 * cell based on the variable support status.
 *
 * @param string $status one of 'ok', 'deprecated', 'removed' or 'na'
 *
 * @return string the HTML glyph for the status
 */
function utilities_mysql_capability_cell(string $status) : string {
	switch ($status) {
		case 'removed':
			return "<span class='deviceDown' title='" . __esc('Removed') . "'>R</span>";
		case 'deprecated':
			return "<span class='deviceRecovering' title='" . __esc('Deprecated') . "'>D</span>";
		case 'na':
			return "<span title='" . __esc('Not applicable to this engine') . "'>" . __('N/A') . '</span>';
		default:
			return "<i class='ti ti-check deviceUp' title='" . __esc('Supported') . "'></i>";
	}
}

/**
 * utilities_get_mysql_capabilities - render the MySQL/MariaDB capability matrix
 * as a separate table in the Database section.  Each recommended variable is
 * listed with its support status (supported, deprecated, removed or N/A) across
 * the common MariaDB and MySQL releases.  Hovering a variable name shows a hint
 * describing the feature and its available settings.
 *
 * @return void
 */
function utilities_get_mysql_capabilities() : void {
	$mysql_info = get_mysql_info(POLLER_ID);
	$database   = $mysql_info['database'];

	$capabilities = utilities_mysql_variable_capabilities();

	// Each column maps a short release label to a representative patch release of
	// that series so version_compare reflects a current, fully patched server
	// (including back-ported features).
	$columns = [
		'MariaDB' => [
			'10.5' => '10.5.27',
			'10.6' => '10.6.21',
			'11.4' => '11.4.5',
			'11.8' => '11.8.2',
		],
		'MySQL' => [
			'8.0' => '8.0.40',
			'8.4' => '8.4.4',
			'9.x' => '9.1.0',
		],
	];

	$total_columns = 1;

	foreach ($columns as $versions) {
		$total_columns += cacti_sizeof($versions);
	}

	print '<tr class="tableHeader tableFixed">';
	print '<th colspan="2">' . __('%s Variable Capability Matrix', $database) . ' - ' . __('Support by engine and version for each Cacti-recommended variable') . '</th>';
	print '</tr>';

	form_alternate_row();
	print "<td colspan='2' style='text-align:left;padding:0px'>";
	print "<table id='mysql_capabilities' class='cactiTable' style='width:100%'>";
	print '<thead>';
	print "<tr class='tableHeader'>";
	print "  <th class='tableSubHeaderColumn' rowspan='2'>" . __('Variable') . '</th>';

	foreach ($columns as $engine => $versions) {
		print "  <th class='tableSubHeaderColumn center' colspan='" . cacti_sizeof($versions) . "'>" . html_escape($engine) . '</th>';
	}

	print '</tr>';
	print "<tr class='tableHeader'>";

	foreach ($columns as $engine => $versions) {
		foreach ($versions as $label => $v) {
			print "  <th class='tableSubHeaderColumn center'>" . html_escape($label) . '</th>';
		}
	}

	print '</tr>';
	print '</thead>';

	foreach ($capabilities as $name => $cap) {
		form_alternate_row();

		$title = html_escape_attr($cap['description']) . '<br><br><strong>' . html_escape_attr(__('Available settings:')) . '</strong> ' . html_escape_attr($cap['settings']);

		print "<td><span class='cactiTooltipHint' tabindex='0' title='" . $title . "'>" . html_escape($name) . '</span></td>';

		foreach ($columns as $engine => $versions) {
			foreach ($versions as $v) {
				$status = utilities_mysql_variable_status($cap, $engine, $v);
				print "<td class='center'>" . utilities_mysql_capability_cell($status) . '</td>';
			}
		}

		form_end_row();
	}

	form_alternate_row();
	$legend  = "<i class='ti ti-check deviceUp'></i> " . __('Supported') . ' &nbsp; ';
	$legend .= "<span class='deviceRecovering'>D</span> " . __('Deprecated') . ' &nbsp; ';
	$legend .= "<span class='deviceDown'>R</span> " . __('Removed') . ' &nbsp; ';
	$legend .= __('N/A - Not applicable to this engine');
	print "<td colspan='" . $total_columns . "' class='left'>" . $legend . '</td>';
	form_end_row();

	print '</table>';
	print '</td>';
	form_end_row();
}

function utilities_get_mysql_recommendations() : int {
	global $local_db_cnn_id;

	// MySQL/MariaDB Important Variables
	// Assume we are successfully, until we aren't!
	$result = DB_STATUS_SUCCESS;

	$memInfo = utilities_get_system_memory();

	$mysql_info = get_mysql_info(POLLER_ID);

	$database  = $mysql_info['database'];
	$version   = $mysql_info['version'];
	$link_ver  = $mysql_info['link_ver'];
	$variables = $mysql_info['variables'];

	$recommendations = [
		'version' => [
			'value'   => ($database == 'MariaDB' ? '10.11' : '8.4'),
			'class'   => 'warning',
			'measure' => 'ge',
			'comment' => __('MariaDB 10.11+ and MySQL 8.4+ are the recommended, supported releases for Cacti 1.3.x (available on Rocky Linux 9 through the vendor module streams). Make sure you run the very latest release though which fixes a long standing low level networking issue that was causing spine many issues with reliability.')
		]
	];

	if (isset($variables['innodb_version']) && version_compare($variables['innodb_version'], '5.6', '<')) {
		if (version_compare($link_ver, '5.5', '>=')) {
			if (!isset($variables['innodb_version'])) {
				$recommendations += [
					'innodb' => [
						'value'   => 'ON',
						'class'   => 'warning',
						'measure' => 'equalint',
						'comment' => __('It is STRONGLY recommended that you enable InnoDB in any %s version greater than 5.5.3.', $database)
					]
				];

				$variables['innodb'] = 'UNSET';
			}
		}

		$recommendations += [
			'collation_server' => [
				'value'   => 'utf8mb4_unicode_ci',
				'class'   => 'warning',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.')
			],
			'character_set_server' => [
				'value'   => 'utf8mb4',
				'class'   => 'warning',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
			],
			'character_set_client' => [
				'value'   => 'utf8mb4',
				'class'   => 'warning',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
			]
		];
	} else {
		if (version_compare($link_ver, '5.2', '>=')) {
			if (!isset($variables['innodb_version']) &&
				($database == 'MySQL' || ($database == 'MariaDB' && version_compare($link_ver, '10.10', '<')))) {
				$recommendations += [
					'innodb' => [
						'value'   => 'ON',
						'class'   => 'warning',
						'measure' => 'equalint',
						'comment' => __('It is recommended that you enable InnoDB in any %s version greater than 5.1.', $database)
					]
				];

				$variables['innodb'] = 'UNSET';
			}
		}

		$recommendations += [
			'collation_server' => [
				'value'   => 'utf8mb4_unicode_ci',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.')
			],
			'character_set_server' => [
				'value'   => 'utf8mb4',
				'class'   => 'warning',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
			],
			'character_set_client' => [
				'value'   => 'utf8mb4',
				'class'   => 'warning',
				'measure' => 'equal',
				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
			]
		];
	}

	$recommendations += [
		'max_connections' => [
			'value'   => '100',
			'measure' => 'ge',
			'comment' => __('Depending on the number of logins and use of spine data collector, %s will need many connections.  The calculation for spine is: total_connections = total_processes * (total_threads + script_servers + 1), then you must leave headroom for user connections, which will change depending on the number of concurrent login accounts.', $database)
		],
		'table_cache' => [
			'value'   => '200',
			'measure' => 'ge',
			'comment' => __('Keeping the table cache larger means less file open/close operations when using innodb_file_per_table.')
		],
		'max_allowed_packet' => [
			'value'   => 16777216,
			'measure' => 'ge',
			'comment' => __('With Remote polling capabilities, large amounts of data will be synced from the main server to the remote pollers.  Therefore, keep this value at or above 16M.')
		],
		'max_heap_table_size' => [
			'value'   => '1.6',
			'measure' => 'pmem',
			'class'   => 'warning',
			'comment' => __('If using the Cacti Performance Booster and choosing a memory storage engine, you have to be careful to flush your Performance Booster buffer before the system runs out of memory table space.  This is done two ways, first reducing the size of your output column to just the right size.  This column is in the tables poller_output, and poller_output_boost.  The second thing you can do is allocate more memory to memory tables.  We have arbitrarily chosen a recommended value of 10%% of system memory, but if you are using SSD disk drives, or have a smaller system, you may ignore this recommendation or choose a different storage engine.  You may see the expected consumption of the Performance Booster tables under Console -> System Utilities -> View Boost Status.')
		],
		'tmp_table_size' => [
			'value'   => '1.6',
			'measure' => 'pmem',
			'class'   => 'warning',
			'comment' => __('When executing subqueries, having a larger temporary table size, keep those temporary tables in memory.')
		],
		'join_buffer_size' => [
			'value'   => '262144',
			'measure' => 'ge',
			'class'   => 'warning',
			'comment' => __('If this number is negative, reduce the innodb_buffer_pool_size until the join_buffer_size turns positive, but allocate approximately from between 25%-50% of memory to the innodb_buffer_pool_size if the database is hosted on the Cacti server, or upto 80% of the systems memory if the database is separate from the Cacti web server.  However, try to not go below the default of 262,144.  When performing joins, if they are below this size, they will be kept in memory and never written to a temporary file.  As this is a per connection memory allocation, care must be taken not to increase it too high.  The sum of the join_buffer_size + sort_buffer_size + read_buffer_size + read_rnd_buffer_size + thread_stack + binlog_cache_size + Core MySQL/MariaDB memory should be below 80% if the database is hosted on the Cacti web server and less if you intend to have very large RRDfiles or hundreds of thousands to millions long term.')
		],
		'sort_buffer_size' => [
			'value'   => '2097152',
			'measure' => 'ge',
			'class'   => 'warning',
			'comment' => __('If this number is negative, reduce the innodb_buffer_pool_size until the sort_buffer_size turns positive, but allocate approximately from between 25%-50% of memory to the innodb_buffer_pool_size if the database is hosted on the Cacti server, or upto 80% of the system memory if the database is separate from the Cacti web server.  However, try to not go below the default setting of 2,097,152.  A sort buffer performs sorts for some queries using ORDER BY or GROUP BY. Configuring sort_buffer_size decides how much memory will be allocated for sort queries.  The sort_buffer_size may need to be adjusted from the default if the workload requires a significant number of sort queries. The sort_buffer_size is defined on a per-session variable.  Use the same equation as that of the join_buffer_size to determine the per connection possible memory.')
		],
		'innodb_file_per_table' => [
			'value'   => 'ON',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('When using InnoDB storage it is important to keep your table spaces separate.  This makes managing the tables simpler for long time users of %s.  If you are running with this currently off, you can migrate to the per file storage by enabling the feature, and then running an alter statement on all InnoDB tables.', $database)
		],
		'innodb_file_format' => [
			'value'   => 'Barracuda',
			'measure' => 'equal',
			'class'   => 'error',
			'comment' => __('When using innodb_file_per_table, it is important to set the innodb_file_format to Barracuda.  This setting will allow longer indexes important for certain Cacti tables.')
		],
		'innodb_large_prefix' => [
			'value'   => '1',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('If your tables have very large indexes, you must operate with the Barracuda innodb_file_format and the innodb_large_prefix equal to 1.  Failure to do this may result in plugins that can not properly create tables.')
		],
		'innodb_buffer_pool_size' => [
			'value'   => '25',
			'measure' => 'pmem',
			'class'   => 'warning',
			'comment' => __('InnoDB will hold as much tables and indexes in system memory as is possible.  Therefore, you should make the innodb_buffer_pool large enough to hold as much of the tables and index in memory.  Checking the size of the /var/lib/mysql/cacti directory will help in determining this value.  We are recommending 25%% of your systems total memory, but your requirements will vary depending on your systems size.  If you database is very large or remote, you can consider increasing this size.  If remote, it can by as high as 80% of the systems memory.  However, cautions must be taken to reduce the swappiness of the system, or to remove swap to keep the system from swapping.')
		],
		'innodb_doublewrite' => [
			'value'   => 'ON',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('This setting should remain ON unless your Cacti instances are running on either ZFS or FusionI/O which both have internal journaling to accommodate abrupt system crashes.  However, if you have very good power, and your systems rarely go down and you have backups, turning this setting to OFF can net you almost a 50% increase in database performance.')
		],
		'innodb_additional_mem_pool_size' => [
			'value'   => '80M',
			'measure' => 'gem',
			'comment' => __('This is where metadata is stored. If you had a lot of tables, it would be useful to increase this.')
		],
		'innodb_lock_wait_timeout' => [
			'value'   => '50',
			'measure' => 'ge',
			'comment' => __('Rogue queries should not for the database to go offline to others.  Kill these queries before they kill your system.')
		],
		'innodb_flush_method' => [
			'value'   => 'O_DIRECT',
			'measure' => 'eq',
			'comment' => __('Maximum I/O performance happens when you use the O_DIRECT method to flush pages.')
		]
	];

	if (isset($variables['innodb_version'])) {
		if (version_compare($variables['innodb_version'], '5.6', '<')) {
			$recommendations += [
				'innodb_flush_log_at_trx_commit' => [
					'value'   => '2',
					'measure' => 'equal',
					'comment' => __('Setting this value to 2 means that you will flush all transactions every second rather than at commit.  This allows %s to perform writing less often.', $database)
				],
				'innodb_file_io_threads' => [
					'value'   => '16',
					'measure' => 'ge',
					'comment' => __('With modern SSD type storage, having multiple io threads is advantageous for applications with high io characteristics.')
				]
			];
		} elseif ($database == 'MariaDB' && version_compare($variables['innodb_version'], '10.5', '<') || $database == 'MySQL') {
			$recommendations += [
				'innodb_flush_log_at_timeout' => [
					'value'    => '3',
					'measure'  => 'ge',
					'comment'  => __('As of %s %s, the you can control how often %s flushes transactions to disk.  The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database),
				],
				'innodb_read_io_threads' => [
					'value'   => '32',
					'measure' => 'ge',
					'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics.  Depending on your MariaDB/MySQL versions, this value can go as high as 64.  But try to keep the number less than your total SMT threads on the database server.')
				],
				'innodb_write_io_threads' => [
					'value'   => '16',
					'measure' => 'ge',
					'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics.  Depending on your MariaDB/MySQL versions, this value can go as high as 64.  But try to keep the number less than your total SMT threads on the database server.')
				],
				'innodb_buffer_pool_instances' => [
					'value'   => '16',
					'measure' => 'pinst',
					'class'   => 'warning',
					'comment' => ($database == 'MySQL' ? __('%s will divide the innodb_buffer_pool into memory regions to improve performance for versions of MySQL upto and including MySQL 8.0.  The max value is 64, but should not exceed more than the number of CPU cores/threads.  When your innodb_buffer_pool is less than 1GB, you should use the pool size divided by 128MB.  Continue to use this equation up to the max of the number of CPU cores or 64.', $database) : __('%s will divide the innodb_buffer_pool into memory regions to improve performance for versions of MariaDB less than 10.5.  The max value is 64, but should not exceed more than the number of CPU cores/threads.  When your innodb_buffer_pool is less than 1GB, you should use the pool size divided by 128MB.  Continue to use this equation up to the max the number of CPU cores or 64.', $database))
				],
				'innodb_io_capacity' => [
					'value'   => '5000',
					'measure' => 'ge',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 200 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.')
				],
				'innodb_io_capacity_max' => [
					'value'   => '10000',
					'measure' => 'ge',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 2000 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.')
				],
				'innodb_flush_neighbors' => [
					'value'   => '0',
					'measure' => 'equalint',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, set this to 0. Otherwise, do not set this setting.')
				]
			];
		} else {
			$recommendations += [
				'innodb_flush_log_at_timeout' => [
					'value'    => '3',
					'measure'  => 'ge',
					'comment'  => __('As of %s %s, the you can control how often %s flushes transactions to disk.  The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database),
				],
				'innodb_read_io_threads' => [
					'value'   => '32',
					'measure' => 'ge',
					'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics.')
				],
				'innodb_write_io_threads' => [
					'value'   => '16',
					'measure' => 'ge',
					'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics.')
				],
				'innodb_io_capacity' => [
					'value'   => '5000',
					'measure' => 'ge',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 200 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.')
				],
				'innodb_io_capacity_max' => [
					'value'   => '10000',
					'measure' => 'ge',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 2000 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.')
				],
				'innodb_flush_neighbors' => [
					'value'   => '0',
					'measure' => 'equalint',
					'class'   => 'warning',
					'comment' => __('If you have SSD disks, set this to 0. Otherwise, do not set this setting.')
				]
			];

			unset($recommendations['innodb_additional_mem_pool_size']);
		}
	}

	if ($database == 'MariaDB' && version_compare($version, '10.2.4', '>')) {
		$recommendations['innodb_doublewrite'] = [
			'value'   => 'OFF',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('When using MariaDB 10.2.4 and above, this setting should be off if atomic writes are enabled.  Therefore, please enable atomic writes instead of the double write buffer as it will increase performance.')
		];

		$recommendations['innodb_use_atomic_writes'] = [
			'value'   => 'ON',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('When using MariaDB 10.2.4 and above, you can use atomic writes over the doublewrite buffer to increase performance.')
		];
	}

	if ($database == 'MariaDB' && version_compare($version, '11.8', '>=')) {
		// MariaDB 11.8 enabled innodb_snapshot_isolation=ON by default (MDEV-39628).
		// Under this mode, concurrent UPDATE statements on any InnoDB table can block
		// for tens of thousands of seconds even when the table has only a handful of
		// rows. Cacti's poller cycle issues many rapid UPDATEs; this setting makes
		// those updates contend in ways that can freeze the entire poller.
		$recommendations['innodb_snapshot_isolation'] = [
			'value'   => 'OFF',
			'measure' => 'equalint',
			'class'   => 'error',
			'comment' => __('MariaDB 11.8 enabled innodb_snapshot_isolation=ON by default. This causes severe lock contention: UPDATE statements can block for tens of thousands of seconds on small tables under concurrent load, which stalls the Cacti poller. Set innodb_snapshot_isolation=OFF in your [mysqld] configuration. See MDEV-39628.')
		];
	}

	// Drop any recommendation for a variable that is deprecated, removed or does
	// not exist in the detected engine and version so we only suggest tunables
	// that are actually present on this server.
	$capabilities = utilities_mysql_variable_capabilities();

	foreach (array_keys($recommendations) as $rec_name) {
		if (!isset($capabilities[$rec_name])) {
			continue;
		}

		$status = utilities_mysql_variable_status($capabilities[$rec_name], $database, $version);

		if ($status == 'deprecated' || $status == 'removed' || $status == 'na') {
			unset($recommendations[$rec_name]);
		}
	}

	if (file_exists('/etc/my.cnf.d/server.cnf')) {
		$location = '/etc/my.cnf.d/server.cnf';
	} else {
		$location = '/etc/my.cnf';
	}

	if ($database == 'MariaDB') {
		$variables_url = 'https://mariadb.com/kb/en/server-system-variables/';
	} else {
		$variables_url = htmle('https://dev.mysql.com/doc/refman/' . $link_ver . '/en/server-system-variables.html');
	}

	print '<tr class="tableHeader tableFixed">';
	print '<th colspan="2">' . __('%s Tuning', $database) . ' (' . $location . ') - [ <a class="linkOverDark" target="_blank" href="' . $variables_url . '">' . __('Documentation') . '</a> ] ' . __('Note: Many changes below require a database restart') . '</th>';
	print '</tr>';

	form_alternate_row();
	print "<td colspan='2' style='text-align:left;padding:0px'>";
	print "<table id='mysql' class='cactiTable' style='width:100%'>";
	print '<thead>';
	print "<tr class='tableHeader'>";
	print "  <th class='tableSubHeaderColumn'>" . __('Variable') . '</th>';
	print "  <th class='tableSubHeaderColumn right'>" . __('Current Value') . '</th>';
	print "  <th class='tableSubHeaderColumn center'>&nbsp;</th>";
	print "  <th class='tableSubHeaderColumn'>" . __('Recommended Value') . '</th>';
	print "  <th class='tableSubHeaderColumn'>" . __('Comments') . '</th>';
	print '</tr>';
	print '</thead>';

	$innodb_pool_size = 0;

	foreach ($recommendations as $name => $r) {
		if (isset($variables[$name])) {
			$class = '';

			// Unset $passed so that we only display fields that we checked
			unset($passed);

			$compare         = '';
			$value_recommend = $r['value'];
			$value_current   = $variables[$name] ?? '<unset>';
			$value_display   = $value_current;

			switch($r['measure']) {
				case 'gem':
					$compare       = '>=';
					$value_display = ($variables[$name] / 1024 / 1024) . ' M';
					$value         = intval(trim($r['value'], 'M')) * 1024 * 1024;

					if ($variables[$name] < $value) {
						$passed = false;
					}

					break;
				case 'ge':
					$compare = '>=';
					$passed  = (version_compare($value_current, $value_recommend, '>='));

					break;
				case 'eq':
				case 'equalint':
				case 'equal':
					$compare = '=';

					if (!isset($variables[$name])) {
						$passed = false;
					} else {
						$e_var = $variables[$name];
						$e_rec = $value_recommend;

						if ($r['measure'] == 'equalint') {
							$e_var = (!strcasecmp('on', $e_var) ? 1 : (!strcasecmp('off', $e_var) ? 0 : $e_var));
							$e_rec = (!strcasecmp('on', $e_rec) ? 1 : (!strcasecmp('off', $e_rec) ? 0 : $e_rec));
						}
						$passed = $e_var == $e_rec;
					}

					break;
				case 'pmem':
					if (isset($memInfo['MemTotal'])) {
						$totalMem = $memInfo['MemTotal'];
					} elseif (isset($memInfo['TotalVisibleMemorySize'])) {
						$totalMem = $memInfo['TotalVisibleMemorySize'];
					} else {
						break;
					}

					if ($name == 'innodb_buffer_pool_size') {
						$innodb_pool_size = $variables[$name];
					}

					$compare         = '>=';
					$passed          = ($variables[$name] >= ($r['value'] * $totalMem / 100));
					$value_display   = round($variables[$name] / 1024 / 1024, 2) . ' M';
					$value_recommend = round($r['value'] * $totalMem / 100 / 1024 / 1024, 2) . ' M';

					break;
				case 'cmem':
					if (isset($memInfo['MemTotal'])) {
						$totalMem = $memInfo['MemTotal'];
					} elseif (isset($memInfo['TotalVisibleMemorySize'])) {
						$totalMem = $memInfo['TotalVisibleMemorySize'];
					} else {
						break;
					}

					if (POLLER_ID == 1) {
						$maxConnections = db_fetch_cell('SELECT @@GLOBAL.max_connections');
					} else {
						$maxConnections = db_fetch_cell('SELECT @@GLOBAL.max_connections', '', false, $local_db_cnn_id);
					}

					if ($name == 'sort_buffer_size') {
						if (POLLER_ID == 1) {
							if ($database == 'MariaDB') {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.query_cache_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.join_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)');
							} else {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.join_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)');
							}
						} else {
							if ($database == 'MariaDB') {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.query_cache_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.join_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)', '', false, $local_db_cnn_id);
							} else {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.join_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)', '', false, $local_db_cnn_id);
							}
						}
					} else {
						if (POLLER_ID == 1) {
							if ($database == 'MariaDB') {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.query_cache_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.sort_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)');
							} else {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.sort_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)');
							}
						} else {
							if ($database == 'MariaDB') {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.query_cache_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.sort_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)', '', false, $local_db_cnn_id);
							} else {
								$totalMemorySans = db_fetch_cell('SELECT @@GLOBAL.key_buffer_size +
								@@GLOBAL.tmp_table_size +
								@@GLOBAL.innodb_buffer_pool_size +
								@@GLOBAL.innodb_log_buffer_size
								+ @@GLOBAL.max_connections * (
									@@GLOBAL.sort_buffer_size +
									@@GLOBAL.read_buffer_size +
									@@GLOBAL.read_rnd_buffer_size +
									@@GLOBAL.thread_stack +
									@@GLOBAL.binlog_cache_size)', '', false, $local_db_cnn_id);
							}
						}
					}

					$remainingMem = ($totalMem * 0.8) - $totalMemorySans;

					/* max_connections is read from the server, and a user without
					   rights to the global variable gets nothing back. Dividing by
					   that is fatal on PHP 8 rather than the warning it once was. */
					$recommendation = $maxConnections > 0 ? $remainingMem / $maxConnections : 0;

					$compare         = '<=';
					$passed          = ($variables[$name] >= ($recommendation / 1024 / 1024)) && $recommendation > 0;
					$value_display   = round($variables[$name] / 1024 / 1024, 2) . ' M';
					$value_recommend = round($recommendation / 1024 / 1024, 2) . ' M';

					break;
				case 'pinst':
					$compare = '>=';

					// Divide the buffer pool size by 128MB, and ensure 1 or more
					$pool_instances = round(($innodb_pool_size / 1024 / 1024 / 128) + 0.5);

					if (CACTI_SERVER_OS == 'win32') {
						$nproc = getenv('NUMBER_OF_PROCESSORS');
					} else {
						$nproc = system('nproc');
					}

					if ($pool_instances > $nproc) {
						$pool_instances = $nproc;
					}

					if ($pool_instances < 1) {
						$pool_instances = 1;
					} elseif ($pool_instances > 64) {
						$pool_instances = 64;
					}

					$passed          = ($variables[$name] >= $pool_instances);
					$value_recommend = $pool_instances;

					break;
				default:
					$compare = $r['measure'];
					$passed  = true;
			}

			if (isset($passed)) {
				if (!$passed) {
					if (isset($r['class']) && $r['class'] == 'warning') {
						$class = 'textWarning';

						if ($result == DB_STATUS_SUCCESS) {
							$result = DB_STATUS_WARNING;
						}
					} else {
						$class = 'textError';

						if ($result != DB_STATUS_ERROR) {
							$result = DB_STATUS_ERROR;
						}
					}
				}

				form_alternate_row();

				print '<td>' . $name . '</td>';
				print "<td class='right $class'>$value_display</td>";
				print "<td class='center'>$compare</td>";
				print "<td>$value_recommend</td>";
				print "<td class='$class'>" . $r['comment'] . '</td>';

				form_end_row();
			}
		}
	}
	print '</table>';
	print '</td>';
	form_end_row();

	return $result;
}

function utilities_php_modules() : string {
	/*
	   Gather phpinfo into a string variable - This has to be done before
	   any headers are sent to the browser, as we are going to do some
	   output buffering fun
	*/

	ob_start();
	phpinfo(INFO_MODULES);
	$php_info = ob_get_contents();
	ob_end_clean();

	// Remove nasty style sheets, links and other junk
	$php_info = str_replace("\n", '', $php_info);
	$php_info = preg_replace('/^.*\<body\>/', '', $php_info);
	$php_info = preg_replace('/\<\/body\>.*$/', '', (string) $php_info);
	$php_info = preg_replace('/(\<a name.*\>)([^<>]*)(\<\/a\>)/U', '$2', (string) $php_info);
	$php_info = preg_replace('/\<img.*\>/U', '', (string) $php_info);
	$php_info = preg_replace('/\<div[^<>]*\>\<\/div\>/U', '', (string) $php_info);
	$php_info = preg_replace('/\<\/?address\>/', '', (string) $php_info);

	return $php_info;
}

function memory_bytes(mixed $val) : mixed {
	$val  = trim($val);
	$last = cacti_strtolower($val[strlen($val) - 1]);
	$val  = (float) trim($val, 'GMKgmk');

	switch($last) {
		case 'g':
			$val *= 1024;
			// Fall through: gigabytes also need the megabyte and kilobyte multipliers.
		case 'm':
			$val *= 1024;
			// Fall through: megabytes also need the kilobyte multiplier.
		case 'k':
			$val *= 1024;
	}

	return $val;
}

function memory_readable(mixed $val) : string {
	if ($val < 1024) {
		$val_label = 'bytes';
	} elseif ($val < 1048576) {
		$val_label = 'K';
		$val /= 1024;
	} elseif ($val < 1073741824) {
		$val_label = 'M';
		$val /= 1048576;
	} else {
		$val_label = 'G';
		$val /= 1073741824;
	}

	return $val . $val_label;
}

function utilities_get_system_memory() : array {
	$memInfo = [];

	if (CACTI_SERVER_OS == 'win32') {
		exec('wmic os get FreePhysicalMemory', $memInfo['FreePhysicalMemory']);
		exec('wmic os get FreeSpaceInPagingFiles', $memInfo['FreeSpaceInPagingFiles']);
		exec('wmic os get FreeVirtualMemory', $memInfo['FreeVirtualMemory']);
		exec('wmic os get SizeStoredInPagingFiles', $memInfo['SizeStoredInPagingFiles']);
		exec('wmic os get TotalVirtualMemorySize', $memInfo['TotalVirtualMemorySize']);
		exec('wmic os get TotalVisibleMemorySize', $memInfo['TotalVisibleMemorySize']);

		if (cacti_sizeof($memInfo)) {
			foreach ($memInfo as $key => $values) {
				$memInfo[$key] = intval($values[1]) * 1000;
			}
		}
	} else {
		$file = '';

		if (file_exists('/proc/meminfo')) {
			$file = '/proc/meminfo';
		} elseif (file_exists('/linux/proc/meminfo')) {
			$file = '/linux/proc/meminfo';
		} elseif (file_exists('/compat/linux/proc/meminfo')) {
			$file = '/compat/linux/proc/meminfo';
		} elseif (file_exists('/usr/compat/linux/proc/meminfo')) {
			$file = '/usr/compat/linux/proc/meminfo';
		}

		if ($file != '') {
			$data = explode("\n", (string) file_get_contents($file));

			foreach ($data as $l) {
				if (trim($l) != '') {
					[$key, $val]     = explode(':', $l);
					$val             = intval(trim($val, " kBb\r\n"));
					$memInfo[$key]   = round($val * 1000,0);
				}
			}
		} elseif (file_exists('/usr/bin/free')) {
			$menInfo   = [];
			$output    = [];
			$exit_code = 0;

			exec('/usr/bin/free', $output, $exit_code);

			if ($exit_code == 0) {
				foreach ($output as $line) {
					$parts = preg_split('/\s+/', $line);

					switch ($parts[0]) {
						case 'Mem:':
							$memInfo['MemTotal']  = (isset($parts[1]) ? intval($parts[1]) * 1000 : 0);
							$memInfo['MemUsed']   = (isset($parts[2]) ? intval($parts[2]) * 1000 : 0);
							$memInfo['MemFree']   = (isset($parts[3]) ? intval($parts[3]) * 1000 : 0);
							$memInfo['MemShared'] = (isset($parts[4]) ? intval($parts[4]) * 1000 : 0);
							$memInfo['Buffers']   = (isset($parts[5]) ? intval($parts[5]) * 1000 : 0);
							$memInfo['Cached']    = (isset($parts[6]) ? intval($parts[6]) * 1000 : 0);

							break;
						case '-/+':
							$memInfo['Active']    = (isset($parts[2]) ? intval($parts[3]) * 1000 : 0);
							$memInfo['Inactive']  = (isset($parts[3]) ? intval($parts[3]) * 1000 : 0);

							break;
						case 'Swap:':
							$memInfo['SwapTotal'] = (isset($parts[1]) ? intval($parts[1]) * 1000 : 0);
							$memInfo['SwapUsed']  = (isset($parts[2]) ? intval($parts[2]) * 1000 : 0);

							break;
					}
				}
			}
		}
	}

	return $memInfo;
}

function utility_php_sort_extensions(mixed $a, mixed $b) : int {
	$name_a = $a['name'] ?? '';
	$name_b = $b['name'] ?? '';

	return strcasecmp($name_a, $name_b);
}

function utility_php_extensions() : array {
	$php      = cacti_escapeshellcmd((string) read_config_option('path_php_binary', true));
	$php_file = cacti_escapeshellarg(CACTI_PATH_INSTALL . '/cli_check.php') . ' extensions';
	$json     = shell_exec($php . ' -q ' . $php_file);

	$ext      = [];

	if ($json != '') {
		$ext = @json_decode($json, true);
	}

	utility_php_verify_extensions($ext, 'web');
	utility_php_set_installed($ext);

	return $ext;
}

function utility_php_verify_extensions(mixed &$extensions, string $source) : void {
	if (empty($extensions)) {
		$extensions = [
			'ctype'     => ['cli' => false, 'web' => false],
			'curl'      => ['cli' => false, 'web' => false],
			'date'      => ['cli' => false, 'web' => false],
			'filter'    => ['cli' => false, 'web' => false],
			'gd'        => ['cli' => false, 'web' => false],
			'gmp'       => ['cli' => false, 'web' => false],
			'hash'      => ['cli' => false, 'web' => false],
			'intl'      => ['cli' => false, 'web' => false],
			'json'      => ['cli' => false, 'web' => false],
			'ldap'      => ['cli' => false, 'web' => false],
			'mbstring'  => ['cli' => false, 'web' => false],
			'openssl'   => ['cli' => false, 'web' => false],
			'pcre'      => ['cli' => false, 'web' => false],
			'PDO'       => ['cli' => false, 'web' => false],
			'pdo_mysql' => ['cli' => false, 'web' => false],
			'session'   => ['cli' => false, 'web' => false],
			'simplexml' => ['cli' => false, 'web' => false],
			'sockets'   => ['cli' => false, 'web' => false],
			'spl'       => ['cli' => false, 'web' => false],
			'standard'  => ['cli' => false, 'web' => false],
			'xml'       => ['cli' => false, 'web' => false],
			'zlib'      => ['cli' => false, 'web' => false]
		];

		if (CACTI_SERVER_OS == 'unix') {
			$extensions['posix'] = ['cli' => false, 'web' => false];
			$extensions['pcntl'] = ['cli' => false, 'web' => true];
		} else {
			$extensions['com_dotnet'] = ['cli' => false, 'web' => false];
		}
	}

	uksort($extensions, 'utility_php_sort_extensions');

	foreach ($extensions as $name=>$extension) {
		if (extension_loaded($name)) {
			$extensions[$name][$source] = true;
		}
	}
}

function utility_php_recommends() : array {
	$php        = cacti_escapeshellcmd((string) read_config_option('path_php_binary', true));
	$php_file   = cacti_escapeshellarg(CACTI_PATH_INSTALL . '/cli_check.php') . ' recommends';
	$json       = shell_exec($php . ' -q ' . $php_file);
	$ext        = ['web' => '', 'cli' => ''];
	$ext['cli'] = @json_decode((string) $json, true);

	utility_php_verify_recommends($ext['web'], 'web');
	utility_php_set_recommends_text($ext);

	return $ext;
}

function utility_get_formatted_bytes(mixed $input_value, string $wanted_type, mixed &$output_value, string $default_type = 'B') : mixed {
	$default_type = cacti_strtoupper($default_type);
	$multiplier   = [
		'B' => 1,
		'K' => 1024,
		'M' => 1048576,
		'G' => 1073741824,
	];

	if ($input_value > 0 && preg_match('/([0-9.]+)([BKMG]){0,1}/i',$input_value,$matches)) {
		$input_value = floatval($matches[1]);

		if (isset($matches[2])) {
			$default_type = $matches[2];
		}

		if (isset($multiplier[$default_type])) {
			$input_value *= $multiplier[$default_type];
		}
	}

	if (intval($input_value) < 0) {
		$output_value = $input_value . $wanted_type;
	} elseif (isset($multiplier[$wanted_type])) {
		$output_value = ($input_value / $multiplier[$wanted_type]) . $wanted_type;
	} elseif (isset($multiplier[$default_type])) {
		$output_value = ($input_value * $multiplier[$default_type]) . $default_type;
	} else {
		$output_value = $input_value . 'B';
	}

	cacti_log("Input values $input_value, Wanted Type $wanted_type, Output Value $output_value", false, 'SYSTEM', POLLER_VERBOSITY_DEBUG);

	return $input_value;
}

function utility_php_verify_recommends(mixed &$recommends, string $source) : void {
	global $original_memory_limit;

	$rec_version    = '7.4.0';
	$rec_memory_mb  = 800;
	$rec_execute_m  = 1;
	$memory_ini     = ($original_memory_limit ?? ini_get('memory_limit'));

	// adjust above appropriately (used in configs)
	$rec_execute    = $rec_execute_m * 60;
	$rec_memory     = utility_get_formatted_bytes($rec_memory_mb, 'M', $rec_memory_mb, 'M');
	$memory_limit   = utility_get_formatted_bytes($memory_ini, 'M', $memory_ini, 'B');

	$execute_time   = ini_get('max_execution_time');
	$timezone       = ini_get('date.timezone');

	$config_file    = get_cfg_var('cfg_file_path');
	$cfg_values     = is_string($config_file) && $config_file !== '' && is_file($config_file) && is_readable($config_file)
		? parse_ini_file($config_file)
		: [];
	$cfg_values     = is_array($cfg_values) ? $cfg_values : [];
	$cfg_mem_limit  = empty($cfg_values['memory_limit']) ? '' : $cfg_values['memory_limit'];
	$cfg_timezone   = empty($cfg_values['date.timezone']) ? '' : $cfg_values['date.timezone'];
	$cfg_max_exec   = empty($cfg_values['max_execution_time']) ? '' : $cfg_values['max_execution_time'];

	// moving into local variable for phpstan
	$php_version    = PHP_VERSION;

	$recommends = [
		[
			'name'        => 'location',
			'value'       => get_cfg_var('cfg_file_path'),
			'current'     => get_cfg_var('cfg_file_path'),
			'status'      => 2,
		],
		[
			'name'        => 'version',
			'value'       => $rec_version,
			'current'     => PHP_VERSION,
			'status'      => version_compare($php_version, $rec_version, '>=') ? DB_STATUS_SUCCESS : DB_STATUS_ERROR,
		],
		[
			'name'        => 'memory_limit',
			'value'       => $rec_memory_mb,
			'current'     => $memory_ini,
			'status'      => (($memory_limit <= 0 || $memory_limit >= $rec_memory) ? DB_STATUS_SUCCESS : ($memory_limit != $cfg_mem_limit ? DB_STATUS_RESTART : DB_STATUS_WARNING)),
		],
		[
			'name'        => 'max_execution_time',
			'value'       => $rec_execute,
			'current'     => $execute_time,
			'status'      => (($execute_time <= 0 || $execute_time >= $rec_execute) ? DB_STATUS_SUCCESS : ($execute_time != $cfg_max_exec ? DB_STATUS_RESTART : DB_STATUS_WARNING)),
		],
		[
			'name'        => 'date.timezone',
			'value'       => '<timezone>',
			'current'     => $timezone,
			'status'      => ($timezone ? DB_STATUS_SUCCESS : ($timezone != $cfg_timezone ? DB_STATUS_RESTART : DB_STATUS_ERROR)),
		],
	];
}

function utility_php_set_recommends_text(mixed &$recs) : void {
	if (is_array($recs) && cacti_sizeof($recs)) {
		foreach ($recs as $name => $recommends) {
			if (cacti_sizeof($recommends)) {
				foreach ($recommends as $index => $recommend) {
					if ($recommend['name'] == 'version') {
						$recs[$name][$index]['description'] = __('PHP %s is the minimum version', $recommend['value']);
					} elseif ($recommend['name'] == 'memory_limit') {
						$recs[$name][$index]['description'] = __('A minimum of %s memory limit', $recommend['value']);
					} elseif ($recommend['name'] == 'max_execution_time') {
						$recs[$name][$index]['description'] = __('A minimum of %s m execution time', $recommend['value']);
					} elseif ($recommend['name'] == 'date.timezone') {
						$recs[$name][$index]['description'] = __('A valid timezone that matches MySQL and the system');
					}
				}
			}
		}
	}
}

function utility_php_optionals() : array {
	$php      = cacti_escapeshellcmd((string) read_config_option('path_php_binary', true));
	$php_file = cacti_escapeshellarg(CACTI_PATH_INSTALL . '/cli_check.php') . ' optionals';
	$json     = shell_exec($php . ' -q ' . $php_file);
	$opt      = [];

	if ($json != '') {
		$opt = json_decode($json, true);
	}

	utility_php_verify_optionals($opt, 'web');
	utility_php_set_installed($opt);

	return $opt;
}

function utility_php_verify_optionals(mixed &$optionals, string $source) : void {
	if (empty($optionals)) {
		$optionals = [
			'snmp'          => ['web' => false, 'cli' => false],
			'gettext'       => ['web' => false, 'cli' => false],
			'TrueType Box'  => ['web' => false, 'cli' => false],
			'TrueType Text' => ['web' => false, 'cli' => false],
		];
	}

	foreach ($optionals as $name => $optional) {
		if (extension_loaded($name)) {
			$optionals[$name][$source] = true;
		}
	}

	$optionals['TrueType Box'][$source]  = function_exists('imagettfbbox');
	$optionals['TrueType Text'][$source] = function_exists('imagettftext');
}

function utility_php_set_installed(array &$extensions) : void {
	foreach ($extensions as $name=>$extension) {
		$extensions[$name]['installed'] = $extension['web'] && $extension['cli'];
	}
}

/**
 * object_cache_get_totals - This function gets the count of objects
 *   for a set of object types.  The object types include:
 *
 *   - device_state  - Load the object database with the current state
 *                     This is to be used for diff updates.
 *   - device_delete - Delete devices Delete Graphs and Data Sources
 *   - device_leave  - Delete devices Leave Graphs and Data Sources
 *   - graph_delete  - Delete Graphs Delete Data Sources
 *   - graph_leave   - Delete Graphs Leave Data Sources
 *   - data_source   - Delete Data Sources
 *
 * This function is used to prime the previous values for processing
 * by object removal functions.
 *
 * @param string $class      The class of object as above
 * @param mixed  $object_ids The ids for the object class
 * @param bool   $diff       Load the objects into the diff array
 *
 * @return void
 */
function object_cache_get_totals(string $class, mixed $object_ids, bool $diff = false) : void {
	global $object_totals, $object_totals_diff;

	if (!is_array($object_ids)) {
		$object_ids = explode(',', $object_ids);
	}

	// temp variable for object array
	$variable = [];

	// object totals loaded already
	if (cacti_sizeof($object_totals) && $diff === false) {
		return;
	}

	// avoid an error if there are no objects
	if (!cacti_sizeof($object_ids)) {
		return;
	}

	switch($class) {
		case 'device_state':
		case 'device_delete':
			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_data_sources'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_graphs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT site_id AS id, COUNT(*) AS totals
				FROM host AS h
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY site_id');

			if (cacti_sizeof($data)) {
				$variable['sites'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT host_template_id AS id, COUNT(*) AS totals
				FROM host AS h
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY host_template_id');

			if (cacti_sizeof($data)) {
				$variable['host_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT graph_template_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY graph_template_id');

			if (cacti_sizeof($data)) {
				$variable['graph_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_template_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY data_template_id');

			if (cacti_sizeof($data)) {
				$variable['data_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT snmp_query_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY snmp_query_id');

			if (cacti_sizeof($data)) {
				$variable['data_queries'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT cdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE cdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY cdef_id');

			if (cacti_sizeof($data)) {
				$variable['cdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT vdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE vdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY vdef_id');

			if (cacti_sizeof($data)) {
				$variable['vdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT color_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE color_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY color_id');

			if (cacti_sizeof($data)) {
				$variable['colors'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT gprint_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE gprint_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY gprint_id');

			if (cacti_sizeof($data)) {
				$variable['gprints'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_input_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_template_data AS dtd
				INNER JOIN data_local AS dl
				ON dl.id = dtd.local_data_id
				WHERE data_input_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY data_input_id');

			if (cacti_sizeof($data)) {
				$variable['data_inputs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_source_profile_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_template_data AS dtd
				INNER JOIN data_local AS dl
				ON dl.id = dtd.local_data_id
				WHERE data_source_profile_id > 0
				AND ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY data_source_profile_id');

			if (cacti_sizeof($data)) {
				$variable['data_profiles'] = array_rekey($data, 'id', 'totals');
			}

			break;
		case 'device_leave':
			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_data_sources'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'host_id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_graphs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT site_id AS id, COUNT(*) AS totals
				FROM host AS h
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY site_id');

			if (cacti_sizeof($data)) {
				$variable['sites'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT host_template_id AS id, COUNT(*) AS totals
				FROM host AS h
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY host_template_id');

			if (cacti_sizeof($data)) {
				$variable['host_templates'] = array_rekey($data, 'id', 'totals');
			}

			break;
		case 'graph_delete':
			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_graphs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_local AS dl
				INNER JOIN data_template_rrd AS dtr
				ON dl.id = dtr.local_data_id
				INNER JOIN graph_templates_item AS gti
				ON dtr.id = gti.task_item_id
				WHERE ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_data_sources'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT graph_template_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY graph_template_id');

			if (cacti_sizeof($data)) {
				$variable['graph_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_template_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_template_rrd AS dtr
				INNER JOIN graph_templates_item AS gti
				ON dtr.id = gti.task_item_id
				WHERE ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY data_template_id');

			if (cacti_sizeof($data)) {
				$variable['data_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT snmp_query_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_local AS dl
				INNER JOIN data_template_rrd AS dtr
				ON dl.id = dtr.local_data_id
				INNER JOIN graph_templates_item AS gti
				ON dtr.id = gti.task_item_id
				WHERE ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY snmp_query_id');

			if (cacti_sizeof($data)) {
				$variable['data_queries'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT cdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE cdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY cdef_id');

			if (cacti_sizeof($data)) {
				$variable['cdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT vdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE vdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY vdef_id');

			if (cacti_sizeof($data)) {
				$variable['vdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT color_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE color_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY color_id');

			if (cacti_sizeof($data)) {
				$variable['colors'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT gprint_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE gprint_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY gprint_id');

			if (cacti_sizeof($data)) {
				$variable['gprints'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_input_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_template_data AS dtd
				INNER JOIN data_local AS dl
				ON dl.id = dtd.local_data_id
				INNER JOIN graph_templates_item AS gti
				ON dtd.id = gti.task_item_id
				WHERE data_input_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY data_input_id');

			if (cacti_sizeof($data)) {
				$variable['data_inputs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_source_profile_id AS id, COUNT(DISTINCT local_data_id) AS totals
				FROM data_template_data AS dtd
				INNER JOIN data_local AS dl
				ON dl.id = dtd.local_data_id
				INNER JOIN graph_templates_item AS gti
				ON dtd.id = gti.task_item_id
				WHERE data_input_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY data_source_profile_id');

			if (cacti_sizeof($data)) {
				$variable['data_profiles'] = array_rekey($data, 'id', 'totals');
			}

			break;
		case 'graph_leave':
			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_graphs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT graph_template_id AS id, COUNT(*) AS totals
				FROM graph_local AS gl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY graph_template_id');

			if (cacti_sizeof($data)) {
				$variable['graph_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT cdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE cdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY cdef_id');

			if (cacti_sizeof($data)) {
				$variable['cdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT vdef_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE vdef_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY vdef_id');

			if (cacti_sizeof($data)) {
				$variable['vdefs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT color_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE color_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY color_id');

			if (cacti_sizeof($data)) {
				$variable['colors'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT gprint_id AS id, COUNT(DISTINCT local_graph_id) AS totals
				FROM graph_templates_item AS gti
				INNER JOIN graph_local AS gl
				ON gl.id = gti.local_graph_id
				WHERE gprint_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_graph_id') . '
				GROUP BY gprint_id');

			if (cacti_sizeof($data)) {
				$variable['gprints'] = array_rekey($data, 'id', 'totals');
			}

			break;
		case 'data_source':
			$data = db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY host_id');

			if (cacti_sizeof($data)) {
				$variable['host_data_sources'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_template_id AS id, COUNT(*) AS totals
				FROM data_template_data AS dtd
				WHERE ' . array_to_sql_or($object_ids, 'local_data_id') . '
				GROUP BY data_template_id');

			if (cacti_sizeof($data)) {
				$variable['data_templates'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT snmp_query_id AS id, COUNT(*) AS totals
				FROM data_local AS dl
				WHERE ' . array_to_sql_or($object_ids, 'id') . '
				GROUP BY snmp_query_id');

			if (cacti_sizeof($data)) {
				$variable['data_queries'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_input_id AS id, COUNT(*) AS totals
				FROM data_template_data AS dtd
				WHERE data_input_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_data_id') . '
				GROUP BY data_input_id');

			if (cacti_sizeof($data)) {
				$variable['data_inputs'] = array_rekey($data, 'id', 'totals');
			}

			$data = db_fetch_assoc('SELECT data_source_profile_id AS id, COUNT(*) AS totals
				FROM data_template_data AS dtd
				WHERE data_source_profile_id > 0
				AND ' . array_to_sql_or($object_ids, 'local_data_id') . '
				GROUP BY data_source_profile_id');

			if (cacti_sizeof($data)) {
				$variable['data_profiles'] = array_rekey($data, 'id', 'totals');
			}

			break;
	}

	if ($diff) {
		$object_totals_diff = $variable;
	} else {
		$object_totals = $variable;
	}
}

function object_cache_update_totals(string $direction) : bool {
	global $object_totals, $object_totals_diff;

	if ($direction == 'add') {
		$operator = '+';
	} elseif ($direction == 'delete') {
		$operator = '-';
	} elseif ($direction != 'diff') {
		cacti_log("WARNING: Object cache direction $direction not known", false, 'WEBUI');

		return false;
	} elseif (!cacti_sizeof($object_totals_diff)) {
		cacti_log('WARNING: Unable to perform diff if the diff array is not loaded', false, 'WEBUI');

		return false;
	} else {
		// scan the changes and then construct a new array with the deltas
		$prev_object_totals = $object_totals;
		$object_totals      = [];
		$operator           = '+';

		foreach ($object_totals_diff as $object_id => $data) {
			foreach ($data as $id => $value) {
				if (isset($prev_object_totals[$object_id][$id])) {
					$object_totals[$object_id][$id] = $value - $prev_object_totals[$object_id][$id];
				} else {
					$object_totals[$object_id][$id] = $value;
				}
			}
		}
	}

	if (cacti_sizeof($object_totals)) {
		foreach ($object_totals as $object_id => $data) {
			foreach ($data as $id => $value) {
				switch($object_id) {
					case 'host_graphs':
						db_execute_prepared("UPDATE host
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'host_data_sources':
						db_execute_prepared("UPDATE host
							SET data_sources = data_sources $operator ?
							WHERE id = ?
							AND data_sources $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'host_templates':
						db_execute_prepared("UPDATE host_template
							SET devices = devices $operator ?
							WHERE id = ?
							AND devices $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'graph_templates':
						db_execute_prepared("UPDATE graph_templates
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'data_templates':
						db_execute_prepared("UPDATE data_template
							SET data_sources = data_sources $operator ?
							WHERE id = ?
							AND data_sources $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'cdefs':
						db_execute_prepared("UPDATE cdef
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'vdefs':
						db_execute_prepared("UPDATE vdef
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'colors':
						db_execute_prepared("UPDATE colors
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'gprints':
						db_execute_prepared("UPDATE graph_templates_gprint
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'data_inputs':
						db_execute_prepared("UPDATE data_input
							SET data_sources = data_sources $operator ?
							WHERE id = ?
							AND data_sources $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'data_queries':
						db_execute_prepared("UPDATE snmp_query
							SET graphs = graphs $operator ?
							WHERE id = ?
							AND graphs $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'data_profiles':
						db_execute_prepared("UPDATE data_source_profiles
							SET data_sources = data_sources $operator ?
							WHERE id = ?
							AND data_sources $operator ? >= 0",
							[$value, $id, $value]);

						break;
					case 'sites':
						db_execute_prepared("UPDATE sites
							SET devices = devices $operator ?
							WHERE id = ?
							AND devices $operator ? >= 0",
							[$value, $id, $value]);

						break;
				}
			}
		}
	}

	unset($object_totals);
	unset($object_totals_diff);

	return true;
}

function object_cache_update_device_totals() : void {
	$tables = [
		'host',
		'host_template',
		'poller',
		'sites',
	];

	foreach ($tables as $table) {
		switch($table) {
			case 'host':
				$data_sources = array_rekey(
					db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS drows
						FROM data_local GROUP
						BY host_id'),
					'id', 'drows'
				);

				$graphs = array_rekey(
					db_fetch_assoc('SELECT host_id AS id, COUNT(*) AS grows
						FROM graph_local GROUP
						BY host_id'),
					'id', 'grows'
				);

				$devices = db_fetch_assoc('SELECT id FROM host');

				if (cacti_sizeof($devices)) {
					foreach ($devices as $d) {
						$gcount = 0;
						$dcount = 0;

						if (isset($graphs[$d['id']])) {
							$gcount = $graphs[$d['id']];
						}

						if (isset($data_sources[$d['id']])) {
							$dcount = $data_sources[$d['id']];
						}

						db_execute_prepared('UPDATE host
							SET graphs = ?, data_sources = ?
							WHERE id = ?',
							[
								$gcount,
								$dcount,
								$d['id']
							]
						);
					}
				}

				break;
			case 'host_template':
				// 'devices'
				$items = array_rekey(
					db_fetch_assoc('SELECT host_template_id AS id, COUNT(*) AS devices
						FROM host
						GROUP BY host_template_id'),
					'id', 'devices'
				);

				$templates = db_fetch_assoc('SELECT id FROM host_template');

				if (cacti_sizeof($templates)) {
					foreach ($templates as $t) {
						if (!isset($items[$t['id']])) {
							db_execute_prepared('UPDATE host_template
								SET devices = 0
								WHERE id = ?',
								[$t['id']]);
						} else {
							db_execute_prepared('UPDATE host_template
								SET devices = ?
								WHERE id = ?',
								[
									$items[$t['id']],
									$t['id']
								]
							);
						}
					}
				}

				break;
			case 'poller':
				// 'devices'
				$items = array_rekey(
					db_fetch_assoc('SELECT poller_id AS id, COUNT(*) AS devices
						FROM host
						GROUP BY poller_id'),
					'id', 'devices'
				);

				$pollers = db_fetch_assoc('SELECT id FROM poller');

				if (cacti_sizeof($pollers)) {
					foreach ($pollers as $p) {
						if (!isset($items[$p['id']])) {
							db_execute_prepared('UPDATE poller
								SET devices = 0
								WHERE id = ?',
								[$p['id']]);
						} else {
							db_execute_prepared('UPDATE poller
								SET devices = ?
								WHERE id = ?',
								[
									$items[$p['id']],
									$p['id']
								]
							);
						}
					}
				}

				break;
			case 'sites':
				// 'devices'
				$items = array_rekey(
					db_fetch_assoc('SELECT site_id AS id, COUNT(*) AS devices
						FROM host
						GROUP BY site_id'),
					'id', 'devices'
				);

				$sites = db_fetch_assoc('SELECT id FROM sites');

				if (cacti_sizeof($sites)) {
					foreach ($sites as $s) {
						if (!isset($items[$s['id']])) {
							db_execute_prepared('UPDATE sites
								SET devices = 0
								WHERE id = ?',
								[$s['id']]);
						} else {
							db_execute_prepared('UPDATE sites
								SET devices = ?
								WHERE id = ?',
								[
									$items[$s['id']],
									$s['id']
								]
							);
						}
					}
				}

				break;
		}
	}
}

function object_cache_update_data_source_totals() : void {
	$tables = [
		'data_input',
		'data_source_profiles',
		'data_template',
	];

	foreach ($tables as $table) {
		switch($table) {
			case 'data_input':
				// 'data_sources', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT data_input_id AS id,
						SUM(CASE WHEN local_data_id > 0 THEN 1 ELSE 0 END) AS data_sources,
						SUM(CASE WHEN local_data_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (SELECT DISTINCT data_input_id, local_data_id FROM data_template_data WHERE data_input_id > 0) AS rs
						GROUP BY data_input_id'),
					'id', ['data_sources', 'templates']
				);

				$inputs = db_fetch_assoc('SELECT id FROM data_input');

				if (cacti_sizeof($inputs)) {
					foreach ($inputs as $i) {
						if (!isset($items[$i['id']])) {
							db_execute_prepared('UPDATE data_input
								SET data_sources = 0, templates = 0
								WHERE id = ?',
								[$i['id']]);
						} else {
							db_execute_prepared('UPDATE data_input
								SET data_sources = ?, templates = ?
								WHERE id = ?',
								[
									$items[$i['id']]['data_sources'],
									$items[$i['id']]['templates'],
									$i['id']
								]
							);
						}
					}
				}

				break;
			case 'data_source_profiles':
				// 'data_sources', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT data_source_profile_id AS id,
						SUM(CASE WHEN local_data_id > 0 THEN 1 ELSE 0 END) AS data_sources,
						SUM(CASE WHEN local_data_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (SELECT DISTINCT data_source_profile_id, local_data_id FROM data_template_data WHERE data_source_profile_id > 0) AS rs
						GROUP BY data_source_profile_id'),
					'id', ['data_sources', 'templates']
				);

				$profiles = db_fetch_assoc('SELECT id FROM data_source_profiles');

				if (cacti_sizeof($profiles)) {
					foreach ($profiles as $p) {
						if (!isset($items[$p['id']])) {
							db_execute_prepared('UPDATE data_source_profiles
								SET data_sources = 0, templates = 0
								WHERE id = ?',
								[$p['id']]);
						} else {
							db_execute_prepared('UPDATE data_source_profiles
								SET data_sources = ?, templates = ?
								WHERE id = ?',
								[
									$items[$p['id']]['data_sources'],
									$items[$p['id']]['templates'],
									$p['id']
								]
							);
						}
					}
				}

				break;
			case 'data_template':
				// 'data_sources'
				$items = array_rekey(
					db_fetch_assoc('SELECT data_template_id AS id, COUNT(*) AS data_sources
						FROM data_local
						GROUP BY data_template_id'),
					'id', 'data_sources'
				);

				$templates = db_fetch_assoc('SELECT id FROM data_template');

				if (cacti_sizeof($templates)) {
					foreach ($templates as $t) {
						if (!isset($items[$t['id']])) {
							db_execute_prepared('UPDATE data_template
								SET data_sources = 0
								WHERE id = ?',
								[$t['id']]);
						} else {
							db_execute_prepared('UPDATE data_template
								SET data_sources = ?
								WHERE id = ?',
								[
									$items[$t['id']],
									$t['id']
								]
							);
						}
					}
				}

				break;
		}
	}
}

function object_cache_update_graph_totals() : void {
	$tables = [
		'cdef',
		'colors',
		'graph_templates',
		'graph_templates_gprint',
		'snmp_query',
		'vdef'
	];

	foreach ($tables as $table) {
		switch($table) {
			case 'cdef':
				// 'graphs', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT cdef_id AS id,
						SUM(CASE WHEN local_graph_id > 0 THEN 1 ELSE 0 END) AS graphs,
						SUM(CASE WHEN local_graph_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (
							SELECT DISTINCT cdef_id, graph_template_id, local_graph_id
							FROM graph_templates_item WHERE cdef_id > 0
						) AS rs
						GROUP BY cdef_id'),
					'id', ['graphs', 'templates']
				);

				$cdefs = db_fetch_assoc('SELECT id FROM cdef');

				if (cacti_sizeof($cdefs)) {
					foreach ($cdefs as $c) {
						if (!isset($items[$c['id']])) {
							db_execute_prepared('UPDATE cdef
								SET graphs = 0, templates = 0
								WHERE id = ?',
								[$c['id']]);
						} else {
							db_execute_prepared('UPDATE cdef
								SET graphs = ?, templates = ?
								WHERE id = ?',
								[
									$items[$c['id']]['graphs'],
									$items[$c['id']]['templates'],
									$c['id']
								]
							);
						}
					}
				}

				break;
			case 'colors':
				// 'graphs', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT color_id AS id,
						SUM(CASE WHEN local_graph_id > 0 THEN 1 ELSE 0 END) AS graphs,
						SUM(CASE WHEN local_graph_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (
							SELECT DISTINCT color_id, graph_template_id, local_graph_id
							FROM graph_templates_item
							WHERE color_id > 0
						) AS rs
						GROUP BY color_id'),
					'id', ['graphs', 'templates']
				);

				$colors = db_fetch_assoc('SELECT id FROM colors');

				if (cacti_sizeof($colors)) {
					foreach ($colors as $c) {
						if (!isset($items[$c['id']])) {
							db_execute_prepared('UPDATE colors
								SET graphs = 0, templates = 0
								WHERE id = ?',
								[$c['id']]);
						} else {
							db_execute_prepared('UPDATE colors
								SET graphs = ?, templates = ?
								WHERE id = ?',
								[
									$items[$c['id']]['graphs'],
									$items[$c['id']]['templates'],
									$c['id']
								]
							);
						}
					}
				}

				break;
			case 'graph_templates':
				// 'graph'
				$items = array_rekey(
					db_fetch_assoc('SELECT graph_template_id AS id, COUNT(*) AS graphs
						FROM graph_local
						GROUP BY graph_template_id'),
					'id', 'graphs'
				);

				$templates = db_fetch_assoc('SELECT id FROM graph_templates');

				if (cacti_sizeof($templates)) {
					foreach ($templates as $t) {
						if (!isset($items[$t['id']])) {
							db_execute_prepared('UPDATE graph_templates
								SET graphs = 0
								WHERE id = ?',
								[$t['id']]);
						} else {
							db_execute_prepared('UPDATE graph_templates
								SET graphs = ?
								WHERE id = ?',
								[
									$items[$t['id']],
									$t['id']
								]
							);
						}
					}
				}

				break;
			case 'graph_templates_gprint':
				// 'graphs', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT gprint_id AS id,
						SUM(CASE WHEN local_graph_id > 0 THEN 1 ELSE 0 END) AS graphs,
						SUM(CASE WHEN local_graph_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (
							SELECT DISTINCT gprint_id, graph_template_id, local_graph_id
							FROM graph_templates_item
							WHERE gprint_id > 0) AS rs
						GROUP BY gprint_id'),
					'id', ['graphs', 'templates']
				);

				$gprints = db_fetch_assoc('SELECT id FROM graph_templates_gprint');

				if (cacti_sizeof($gprints)) {
					foreach ($gprints as $g) {
						if (!isset($items[$g['id']])) {
							db_execute_prepared('UPDATE graph_templates_gprint
								SET graphs = 0, templates = 0
								WHERE id = ?',
								[$g['id']]);
						} else {
							db_execute_prepared('UPDATE graph_templates_gprint
								SET graphs = ?, templates = ?
								WHERE id = ?',
								[
									$items[$g['id']]['graphs'],
									$items[$g['id']]['templates'],
									$g['id']
								]
							);
						}
					}
				}

				break;
			case 'snmp_query':
				// 'graphs', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT sq.id,
						COUNT(DISTINCT gl.id) AS graphs,
						COUNT(DISTINCT sqg.graph_template_id) AS templates
						FROM snmp_query AS sq
						LEFT JOIN snmp_query_graph AS sqg
						ON sq.id = sqg.snmp_query_id
						LEFT JOIN data_input AS di
						ON sq.data_input_id = di.id
						LEFT JOIN graph_local AS gl
						ON gl.snmp_query_id = sq.id
						GROUP BY sq.id'),
					'id', ['graphs', 'templates']
				);

				$data_queries = db_fetch_assoc('SELECT id FROM snmp_query');

				if (cacti_sizeof($data_queries)) {
					foreach ($data_queries as $q) {
						if (!isset($items[$q['id']])) {
							db_execute_prepared('UPDATE snmp_query
								SET graphs = 0, templates = 0
								WHERE id = ?',
								[$q['id']]);
						} else {
							db_execute_prepared('UPDATE snmp_query
								SET graphs = ?, templates = ?
								WHERE id = ?',
								[
									$items[$q['id']]['graphs'],
									$items[$q['id']]['templates'],
									$q['id']
								]
							);
						}
					}
				}

				break;
			case 'vdef':
				// 'graphs', 'templates'
				$items = array_rekey(
					db_fetch_assoc('SELECT vdef_id AS id,
						SUM(CASE WHEN local_graph_id > 0 THEN 1 ELSE 0 END) AS graphs,
						SUM(CASE WHEN local_graph_id = 0 THEN 1 ELSE 0 END) AS templates
						FROM (
							SELECT DISTINCT vdef_id, graph_template_id, local_graph_id
							FROM graph_templates_item
							WHERE vdef_id > 0
						) AS rs
						GROUP BY vdef_id'),
					'id', ['graphs', 'templates']
				);

				$vdefs = db_fetch_assoc('SELECT id FROM vdef');

				if (cacti_sizeof($vdefs)) {
					foreach ($vdefs as $v) {
						if (!isset($items[$v['id']])) {
							db_execute_prepared('UPDATE vdef
								SET graphs = 0, templates = 0
								WHERE id = ?',
								[$v['id']]);
						} else {
							db_execute_prepared('UPDATE vdef
								SET graphs = ?, templates = ?
								WHERE id = ?',
								[
									$items[$v['id']]['graphs'],
									$items[$v['id']]['templates'],
									$v['id']
								]
							);
						}
					}
				}

				break;
		}
	}
}

function object_cache_update_aggregate_totals() : void {
	$tables = [
		'aggregate_graph_templates',
		'color_templates',
	];

	foreach ($tables as $table) {
		switch($table) {
			case 'aggregate_graph_templates':
				// 'graphs'
				$items = array_rekey(
					db_fetch_assoc('SELECT aggregate_template_id AS id, COUNT(*) AS graphs
						FROM aggregate_graphs
						WHERE aggregate_template_id > 0
						GROUP BY aggregate_template_id'),
					'id', 'graphs'
				);

				$templates = db_fetch_assoc('SELECT id FROM aggregate_graph_templates');

				if (cacti_sizeof($templates)) {
					foreach ($templates as $t) {
						$graphs = 0;

						if (isset($items[$t['id']])) {
							$graphs = $items[$t['id']];
						}

						db_execute_prepared('UPDATE aggregate_graph_templates
							SET graphs = ?
							WHERE id = ?',
							[
								$graphs,
								$t['id']
							]
						);
					}
				}

				break;
			case 'color_templates':
				// 'graphs', 'templates'
				$gitems = array_rekey(
					db_fetch_assoc('SELECT color_template AS id, COUNT(*) AS graphs
						FROM (
							SELECT DISTINCT color_template, aggregate_graph_id
							FROM aggregate_graphs_graph_item
							WHERE color_template > 0
						) AS rs
						GROUP BY color_template'),
					'id', 'graphs'
				);

				$titems = array_rekey(
					db_fetch_assoc('SELECT aggregate_template_id AS id, COUNT(*) AS templates
						FROM (
							SELECT DISTINCT color_template, aggregate_template_id
							FROM aggregate_graph_templates_item
							WHERE color_template > 0
						) AS rs
						GROUP BY color_template'),
					'id', 'templates'
				);

				$templates = db_fetch_assoc('SELECT color_template_id AS id FROM color_templates');

				if (cacti_sizeof($templates)) {
					foreach ($templates as $t) {
						$graphs    = 0;
						$templates = 0;

						if (isset($gitems[$t['id']])) {
							$graphs = $gitems[$t['id']];
						}

						if (isset($titems[$t['id']])) {
							$templates = $titems[$t['id']];
						}

						db_execute_prepared('UPDATE color_templates
							SET graphs = ?, templates = ?
							WHERE color_template_id = ?',
							[
								$graphs,
								$templates,
								$t['id']
							]
						);
					}
				}

				break;
		}
	}
}