<?php

/*
 * Helper functions for building a DataTables server-side processing SQL query
 *
 * The static functions in this class are just helper functions to help build
 * the SQL used in the DataTables demo server-side processing scripts. These
 * functions obviously do not represent all that can be done with server-side
 * processing, they are intentionally simple to show how it works. More complex
 * server-side processing operations will likely require a custom script.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */


// Please Remove below 4 lines as this is use in Datatatables test environment for your local or live environment please remove it or else it will not work
// $file = $_SERVER['DOCUMENT_ROOT'].'/datatables/pdo.php';
// if ( is_file( $file ) ) {
// 	include( $file );
// }


class SSP
{
	/**
	 * Create the data output array for the DataTables rows
	 *
	 *  @param  array $columns Column information array
	 *  @param  array $data    Data from the SQL get
	 *  @return array          Formatted data in a row based format
	 */
	static function data_output($columns, $data)
	{
		$out = array();

		for ($i = 0, $ien = count($data); $i < $ien; $i++) {
			$row = array();

			for ($j = 0, $jen = count($columns); $j < $jen; $j++) {
				$column = $columns[$j];

				// Is there a formatter?
				if (isset($column['formatter'])) {
					if (empty($column['db'])) {
						$row[$column['dt']] = $column['formatter']($data[$i]);
					} else {
						$row[$column['dt']] = $column['formatter']($data[$i][$column['db']], $data[$i]);
					}
				} else {
					if (!empty($column['db'])) {
						$row[$column['dt']] = $data[$i][$columns[$j]['db']];
					} else {
						$row[$column['dt']] = "";
					}
				}
			}

			$out[] = $row;
		}

		return $out;
	}


	/**
	 * Database connection
	 *
	 * Obtain an PHP PDO connection from a connection details array
	 *
	 *  @param  array $conn SQL connection details. The array should have
	 *    the following properties
	 *     * host - host name
	 *     * db   - database name
	 *     * user - user name
	 *     * pass - user password
	 *  @return resource PDO connection
	 */
	static function db($conn)
	{
		if (is_array($conn)) {
			return self::sql_connect($conn);
		}

		return $conn;
	}


	/**
	 * Paging
	 *
	 * Construct the LIMIT clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL limit clause
	 */
	static function limit($request, $columns)
	{
		$limit = '';

		if (isset($request['start']) && $request['length'] != -1) {
			$limit = "LIMIT " . intval($request['start']) . ", " . intval($request['length']);
		}

		return $limit;
	}


	/**
	 * Ordering
	 *
	 * Construct the ORDER BY clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL order by clause
	 */
	static function order($request, $columns)
	{
		$order = '';

		if (isset($request['order']) && count($request['order'])) {
			$orderBy = array();
			$dtColumns = self::pluck($columns, 'dt');

			for ($i = 0, $ien = count($request['order']); $i < $ien; $i++) {
				// Convert the column index into the column data property
				$columnIdx = intval($request['order'][$i]['column']);
				$requestColumn = $request['columns'][$columnIdx];

				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];

				if ($requestColumn['orderable'] == 'true') {
					$dir = $request['order'][$i]['dir'] === 'asc' ?
						'ASC' :
						'DESC';

					$orderBy[] = '`' . $column['db'] . '` ' . $dir;
				}
			}

			if (count($orderBy)) {
				$order = 'ORDER BY ' . implode(', ', $orderBy);
			}
		}

		return $order;
	}

	static function containsSpecialCharacters($str)
	{
		// Define an array of ASCII values for the specific special characters
		$specialCharactersAscii = [37, 95, 91, 93, 94, 123, 125, 124, 42];

		// Iterate through each character in the string
		for ($i = 0; $i < strlen($str); $i++) {
			$asciiValue = ord($str[$i]); // Get ASCII value of the character

			// Check if the ASCII value is in the array of specific special characters
			if (in_array($asciiValue, $specialCharactersAscii)) {
				return true; // Special character found
			}
		}

		return false; // No specific special character found
	}


	/**
	 * Searching / Filtering
	 *
	 * Construct the WHERE clause for server-side processing SQL query.
	 *
	 * NOTE this does not match the built-in DataTables filtering which does it
	 * word by word on any field. It's possible to do here performance on large
	 * databases would be very poor
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @param  array $bindings Array of values for PDO bindings, used in the
	 *    sql_exec() function
	 *  @return string SQL where clause
	 */
	static function filter($request, $columns, &$bindings)
	{
		$globalSearch = array();
		$columnSearch = array();
		$dtColumns = self::pluck($columns, 'dt');

		if (isset($request['search']) && $request['search']['value'] != '') {
			$str = $request['search']['value'];
			if (!self::containsSpecialCharacters($str)) {
				$str = '%' . $str . '%';
			}

			for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];

				if ($requestColumn['searchable'] == 'true') {
					if (!empty($column['db'])) {
						$binding = self::bind($bindings, $str, PDO::PARAM_STR);
						$globalSearch[] = "`" . $column['db'] . "` LIKE " . $binding;
					}
				}
			}
		}

		// Individual column filtering
		if (isset($request['columns'])) {
			for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];

				$str = $requestColumn['search']['value'];

				if ($requestColumn['searchable'] == 'true' && $str != '') {
					if (!empty($column['db'])) {
						if (!self::containsSpecialCharacters($str)) {
							$str = '%' . $str . '%';
						}
						$binding = self::bind($bindings, $str, PDO::PARAM_STR);
						$columnSearch[] = "`" . $column['db'] . "` LIKE " . $binding;
					}
				}
			}
		}

		// Combine the filters into a single string
		$where = '';

		if (count($globalSearch)) {
			$where = '(' . implode(' OR ', $globalSearch) . ')';
		}

		if (count($columnSearch)) {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) :
				$where . ' AND ' . implode(' AND ', $columnSearch);
		}

		if ($where !== '') {
			$where = 'WHERE ' . $where;
		}

		return $where;
	}


	/**
	 * Perform the SQL queries needed for an server-side processing requested,
	 * utilising the helper functions of this class, limit(), order() and
	 * filter() among others. The returned array is ready to be encoded as JSON
	 * in response to an SSP request, or can be modified if needed before
	 * sending back to the client.
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array|PDO $conn PDO connection resource or connection parameters array
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @return array          Server-side processing response array
	 */
	static function simple($request, $conn, $table, $primaryKey, $columns)
	{
		$bindings = array();
		$db = self::db($conn);

		// Build the SQL query string from the request
		$limit = self::limit($request, $columns);
		$order = self::order($request, $columns);
		$where = self::filter($request, $columns, $bindings);

		// Generate filter hash for caching
		$filterHash = self::getFilterHash($request);

		// actual query to return
		$query = self::actual_query(
			$bindings,
			"SELECT `" . implode("`, `", self::pluck($columns, 'db')) . "`
			 FROM $table
			 $where
			 $order"
		);

		// Main query to actually get the data
		$data = self::sql_exec(
			$db,
			$bindings,
			"SELECT `" . implode("`, `", self::pluck($columns, 'db')) . "`
         FROM $table
         $where
         $order
         $limit"
		);

		// Get both counts with caching
		$counts = self::getCountsWithCache(
			$db,
			$table,
			$primaryKey,
			$where,
			'',
			$bindings,
			$filterHash
		);

		/*
     * Output
     */
		return array(
			"draw"            => isset($request['draw']) ?
				intval($request['draw']) :
				0,
			"recordsTotal"    => intval($counts['total']),
			"recordsFiltered" => intval($counts['filtered']),
			"data"            => self::data_output($columns, $data),
			"query"           => $query,
			"filterHash"     => $filterHash
		);
	}


	/**
	 * The difference between this method and the `simple` one, is that you can
	 * apply additional `where` conditions to the SQL queries. These can be in
	 * one of two forms:
	 *
	 * * 'Result condition' - This is applied to the result set, but not the
	 *   overall paging information query - i.e. it will not effect the number
	 *   of records that a user sees they can have access to. This should be
	 *   used when you want apply a filtering condition that the user has sent.
	 * * 'All condition' - This is applied to all queries that are made and
	 *   reduces the number of records that the user can access. This should be
	 *   used in conditions where you don't want the user to ever have access to
	 *   particular records (for example, restricting by a login id).
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array|PDO $conn PDO connection resource or connection parameters array
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @param  string $whereResult WHERE condition to apply to the result set
	 *  @param  string $whereAll WHERE condition to apply to all queries
	 *  @return array          Server-side processing response array
	 */
	static function complex($request, $conn, $table, $primaryKey, $columns, $whereResult = null, $whereAll = null, $variable_sql = null)
	{
		$bindings = array();
		$db = self::db($conn);
		$whereAllSql = '';

		// Build the SQL query string from the request
		$limit = self::limit($request, $columns);
		$order = self::order($request, $columns);
		$where = self::filter($request, $columns, $bindings);

		$whereResult = self::_flatten($whereResult);
		$whereAll = self::_flatten($whereAll);

		if ($whereResult) {
			$where = $where ?
				$where . ' AND ' . $whereResult :
				'WHERE ' . $whereResult;
		}

		if ($whereAll) {
			$where = $where ?
				$where . ' AND ' . $whereAll :
				'WHERE ' . $whereAll;

			$whereAllSql = 'WHERE ' . $whereAll;
		}

		// Generate filter hash for caching
		$filterHash = self::getFilterHash($request, $whereAll);


		// actual query to return
		$query = self::actual_query(
			$bindings,
			"SELECT `" . implode("`, `", self::pluck($columns, 'db')) . "`
			 FROM $table
			 $where
			 $order"
		);

		// Main query to actually get the data
		$data = self::sql_exec(
			$db,
			$bindings,
			"SELECT `" . implode("`, `", self::pluck($columns, 'db')) . "`
         FROM $table
         $where
         $order
         $limit",
			$variable_sql
		);

		// Get both counts with caching
		$counts = self::getCountsWithCache(
			$db,
			$table,
			$primaryKey,
			$where,
			$whereAllSql,
			$bindings,
			$filterHash,
			$variable_sql
		);

		/*
     * Output
     */
		return array(
			"draw"            => isset($request['draw']) ?
				intval($request['draw']) :
				0,
			"recordsTotal"    => intval($counts['total']),
			"recordsFiltered" => intval($counts['filtered']),
			"data"            => self::data_output($columns, $data),
			"query"           => $query,
			"filterHash"     => $filterHash
		);
	}


	/**
	 * Connect to the database
	 *
	 * @param  array $sql_details SQL server connection details array, with the
	 *   properties:
	 *     * host - host name
	 *     * db   - database name
	 *     * user - user name
	 *     * pass - user password
	 * @return resource Database connection handle
	 */
	static function sql_connect($sql_details)
	{
		try {
			$db = @new PDO(
				"mysql:host={$sql_details['Database_Server']};dbname={$sql_details['Database_Name']}",
				$sql_details['User_Id'],
				$sql_details['Password'],
				array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
			);
		} catch (PDOException $e) {
			self::fatal(
				"An error occurred while connecting to the database. " .
					"The error reported by the server was: " . $e->getMessage()
			);
		}

		return $db;
	}


	/**
	 * Execute an SQL query on the database
	 *
	 * @param  resource $db  Database handler
	 * @param  array    $bindings Array of PDO binding values from bind() to be
	 *   used for safely escaping strings. Note that this can be given as the
	 *   SQL query string if no bindings are required.
	 * @param  string   $sql SQL query to execute.
	 * @return array         Result from the query (all rows)
	 */
	static function sql_exec($db, $bindings, $sql = null, $variable_sql = null)
	{
		// Argument shifting
		if ($sql === null) {
			$sql = $bindings;
		}
		if ($variable_sql !== null) {
			$db->prepare($variable_sql)->execute();
		}
		$stmt = $db->prepare($sql);
		//echo $sql;

		// Bind parameters
		if (is_array($bindings)) {
			for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
				$binding = $bindings[$i];
				$stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
			}
		}

		// Execute
		try {
			$stmt->execute();
		} catch (PDOException $e) {
			self::fatal("An SQL error occurred: " . $e->getMessage());
		}

		// Return all
		return $stmt->fetchAll(PDO::FETCH_BOTH);
	}


	static function actual_query($bindings, $sql = null)
	{
		if (is_array($bindings)) {
			$ien = count($bindings);
			for ($i = 0; $i < $ien; $i++) {
				$binding = $bindings[$i];
				$sql = str_replace($binding['key'], "'$binding[val]'", $sql);
			}
		}
		return $sql;
	}


	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * Internal methods
	 */

	/**
	 * Throw a fatal error.
	 *
	 * This writes out an error message in a JSON string which DataTables will
	 * see and show to the user in the browser.
	 *
	 * @param  string $msg Message to send to the client
	 */
	static function fatal($msg)
	{
		echo json_encode(array(
			"error" => $msg
		));

		exit(0);
	}

	/**
	 * Create a PDO binding key which can be used for escaping variables safely
	 * when executing a query with sql_exec()
	 *
	 * @param  array &$a    Array of bindings
	 * @param  *      $val  Value to bind
	 * @param  int    $type PDO field type
	 * @return string       Bound key to be used in the SQL where this parameter
	 *   would be used.
	 */
	static function bind(&$a, $val, $type)
	{
		$key = ':binding_' . count($a);

		$a[] = array(
			'key' => $key,
			'val' => $val,
			'type' => $type
		);

		return $key;
	}


	/**
	 * Pull a particular property from each assoc. array in a numeric array, 
	 * returning and array of the property values from each item.
	 *
	 *  @param  array  $a    Array to get data from
	 *  @param  string $prop Property to read
	 *  @return array        Array of property values
	 */
	static function pluck($a, $prop)
	{
		$out = array();

		for ($i = 0, $len = count($a); $i < $len; $i++) {
			if (empty($a[$i][$prop])) {
				continue;
			}
			//removing the $out array index confuses the filter method in doing proper binding,
			//adding it ensures that the array data are mapped correctly
			$out[$i] = $a[$i][$prop];
		}

		return $out;
	}


	/**
	 * Return a string from an array or a string
	 *
	 * @param  array|string $a Array to join
	 * @param  string $join Glue for the concatenation
	 * @return string Joined string
	 */
	static function _flatten($a, $join = ' AND ')
	{
		if (!$a) {
			return '';
		} else if ($a && is_array($a)) {
			return implode($join, $a);
		}
		return $a;
	}








	/**
	 * Generate filter hash for cache key
	 */
	static function getFilterHash($request, $whereAll = null)
	{
		return md5(json_encode([
			'search' => isset($request['search']) ? $request['search']['value'] : '',
			'columns' => isset($request['columns']) ? array_column($request['columns'], 'search') : [],
			'whereAll' => $whereAll
		]));
	}

	/**
	 * Get cached counts (both total and filtered) from cookie
	 * 
	 * @param string $table Table name
	 * @param string $filterHash Current filter hash
	 * @return array|null Array with 'total' and 'filtered' or null if not found/invalid
	 */
	static function getCachedCounts($table, $filterHash)
	{
		$cookieName = 'dt_' . md5($table);
		
		if (isset($_COOKIE[$cookieName])) {
			$cached = json_decode($_COOKIE[$cookieName], true);
			if ($cached && isset($cached['h']) && $cached['h'] === $filterHash) {
				return [
					'total' => isset($cached['t']) ? (int)$cached['t'] : null,
					'filtered' => isset($cached['f']) ? (int)$cached['f'] : null
				];
			}
		}
		
		return null;
	}

	/**
	 * Set cached counts (both total and filtered) in cookie
	 * 
	 * @param string $table Table name
	 * @param string $filterHash Current filter hash
	 * @param int $total Total count
	 * @param int $filtered Filtered count
	 * @param int $expires Expiration time in seconds (default 1 hour)
	 */
	static function setCachedCounts($table, $filterHash, $total, $filtered, $expires = 3600)
	{
		$cookieName = 'dt_' . md5($table);
		setcookie(
			$cookieName, 
			json_encode([
				'h' => $filterHash,  // hash
				't' => $total,       // total
				'f' => $filtered     // filtered
			]), 
			time() + $expires, 
			'/',
			'',
			false,
			true // httponly for security
		);
	}

	/**
	 * Get counts with caching (both total and filtered)
	 * 
	 * @param PDO $db Database connection
	 * @param string $table Table name
	 * @param string $primaryKey Primary key column
	 * @param string $where WHERE clause for filtered query
	 * @param string $whereAllSql WHERE clause for total query
	 * @param array $bindings PDO bindings
	 * @param string $filterHash Current filter hash
	 * @param mixed $variable_sql Variable SQL to execute before query
	 * @return array Array with 'total' and 'filtered' counts
	 */
	static function getCountsWithCache($db, $table, $primaryKey, $where, $whereAllSql, $bindings, $filterHash, $variable_sql = null)
	{
		// Try to get from cache
		$cached = self::getCachedCounts($table, $filterHash);
		
		if ($cached !== null && $cached['total'] !== null && $cached['filtered'] !== null) {
			return $cached;
		}
		
		// Cache miss - calculate both counts
		
		// Filtered count
		$resFilterLength = self::sql_exec(
			$db,
			$bindings,
			"SELECT COUNT(`{$primaryKey}`) FROM $table $where",
			$variable_sql
		);
		$filtered = $resFilterLength[0][0];
		
		// Total count
		$resTotalLength = self::sql_exec(
			$db,
			[],
			"SELECT COUNT(`{$primaryKey}`) FROM $table ",
			$variable_sql
		);
		$total = $resTotalLength[0][0];
		
		// Save to cache
		self::setCachedCounts($table, $filterHash, $total, $filtered);
		
		return [
			'total' => $total,
			'filtered' => $filtered
		];
	}
}
