<?php
define ('SITE_ROOT', ""); //added on 18 01 2021

header("X-Frame-Options: SAMEORIGIN");
header('X-XSS-Protection: 1; mode=block');
header('X-Content-Type-Options: nosniff');
header_remove("X-Powered-By");

        // echo "=> " . print_r($_SERVER); exit();
        header("Access-Control-Allow-Origin: https://capapi.kreonsolutions.in/");
        header('Access-Control-Allow-Credentials: true');
        header('Access-Control-Max-Age: 1200');    // cache for 20mins
        // header('Access-Control-Allow-Origin: *');
        header('Access-Control-Allow-Methods: GET, POST');
        // header("Access-Control-Allow-Headers: X-Requested-With");        
        // header('Access-Control-Allow-Headers: Origin, Content-Type, X-Auth-Token , Authorization');

$k_debug = 0;
if (session_status() === PHP_SESSION_NONE) {
	session_start();
}

class db extends mysqli {
	// single instance of self shared among all instances
	private static $instance = null;
	private static $instanceClient = null;
	private $conn = null;
	private $env = 1; //0 = Localhost; 1 = Live Server
	private $user = "root";
	private $pass = "";
	private $dbName = "Main";
	private $dbHost = "localhost";
	
	// db connection config vars
	private $user1 = "root";
	private $pass1 = "";
	private $dbName1 = "Main";
	private $dbHost1 = "localhost";
	private $dbDebug = 0;
	public $enableLog = 0;
	
	//This method must be static, and must return an instance of the object if the object does not already exist.
	public static function getInstanceMaster() {
		// echo "hi";exit();
		if (!self::$instance instanceof self) {	
			self::$instance = new self(0);	//0 added for MASTER 
		}
		return self::$instance;
	}
	//This method must be static, and must return an instance of the object if the object does not already exist.
	public static function getInstance() {
		// echo "hi1";exit();
		// if (!self::$instance instanceof self) {	
		// 	self::$instance = new self(1);	//1 added for Client 
		// }
		// return self::$instance;
		if (!self::$instanceClient instanceof self) {	
			self::$instanceClient = new self(1);	//1 added for Client 
		}
		return self::$instanceClient;
	}
	// The clone and wakeup methods prevents external instantiation of copies of the Singleton class, thus eliminating the possibility of duplicate objects.
	public function __clone() {
		trigger_error('Clone is not allowed.', E_USER_ERROR);
	}
	
	public function __wakeup() {
		trigger_error('De-serializing is not allowed.', E_USER_ERROR);
	}
	
	function __destruct() {
        // ob_end_flush();
		if (ob_get_level() > 0) {
			@ob_end_flush(); // suppress warning with @
		}
		//print "Destroying " . __CLASS__ . "\n";
		//parent::__destruct();
    }
	
	public function __construct($dbType) {
		// echo $dbType;exit();
		//$dbType => 0 for Master, 1 for Client
		if($dbType == 0){
			if($this->env == 1){
				// echo "hi1";exit();
				parent::__construct($this->dbHost1, $this->user1, $this->pass1, $this->dbName1);
				// parent::set_charset('utf8');
			}else{			
				// echo "hi2";exit();
				parent::__construct($this->dbHost, $this->user, $this->pass, $this->dbName);
				// parent::set_charset('utf-8');
			}
		}
		if($dbType == 1){
			// echo $_SESSION['dbHost'] ." ". $_SESSION['dbUser'] ." ". $_SESSION['dbPass']." ". $_SESSION['dbName'];
			parent::__construct($_SESSION['dbHost'], $_SESSION['dbUser'], $_SESSION['dbPass'], $_SESSION['dbName']);
			// parent::set_charset('utf8');
		}
		if (mysqli_connect_error()) {
			exit('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
		}
	}
	
	public function query_valid($query){
		if($this->query($query)){
			return true;
		}
	} 
	
	public function get_result($query) {
		$result = $this->query($query);
		if ($result->num_rows > 0){
			//$row = $result->fetch_assoc();
			return $result;
		}else{
			return null;
		}
	}

	public function db_sp_select($sp, $set, $val) {

		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		$output['result_set'] = array();

		// print_r($val);
		
		$finalParams = array_map(function($val) {
			if ($val === '' || is_null($val)) {
				return "NULL";  // You can use NULL for missing
			} elseif (is_numeric($val)) {
				return $val;
			} else {
				return "'" . addslashes($val) . "'";
			}
		}, $val);
	
		// Convert array to comma-separated values (without quotes)
		$params = implode(',', $finalParams);

		// Form the query
		$query = "CALL $sp($params);";

		// echo "<br>".$query;
		// exit();

		// Log the query
		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL . "-------------------------" . PHP_EOL .
			"User: " . $_SESSION['email'] . ' - ' . $_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a") .
			" => Data: " . json_encode($_REQUEST) . PHP_EOL .
			("https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]") . PHP_EOL .
			$query;
	
		// Execute the stored procedure
		if ($result = $this->query($query)) {
			do {
				$result_set = array();
				while ($row = mysqli_fetch_assoc($result)) {
					$result_set[] = $row;
				}
				if (sizeof($result_set) > 0) {
					$output['result_set'][] = $result_set;
				}
				mysqli_free_result($result);
				
			} while ($this->more_results() && $this->next_result() && ($result = $this->store_result()));
	
			$output['error'] = "0";
			// print_r($output);
			if ($result instanceof mysqli_result) {
				mysqli_free_result($result);
			}
			// mysqli_free_result($result);
		} else {
			$output['error_statement'] = "Error: " . mysqli_error($this->conn);
			$devLog .= PHP_EOL . $output['error_statement'];
		}
	
		// Save log
		file_put_contents('./logs/' . $_SESSION['email'] . '.log', $devLog, FILE_APPEND);
		// $this->addToSqlProfiler($query);
	
		return $output;
	}

	
	//SELECT Fn
	public function db_select($query, $limit=0, $offset=0) { ///returns "result_set, error, error_statement, num_rows
		// echo "<br><br>".$query;
		if($limit > 0){
			$query .= " LIMIT ".$limit;
		}

		// echo "<br><br>".$query;
		$output = array();
		$output['result_set'] = array();
		$output['error'] = "1";
		// echo $query;				
		//$this->set_charset("utf-8");
		$output['error_statement'] = "";
		if ($result = $this->query($query)) {
			// print_r($result);
			$output['error'] = "0";
			$output['num_rows'] = $result->num_rows;
			while($row = $result->fetch_assoc()){
				$output['result_set'][] = $row;
			}
			$result->free();
		} else {
			$output['error_statement'] = "Error: " . $this->error;
		}
		// print_r($output);
		return $output;
	}
	//UPDATE Fn
	public function db_update($query) { ///SAME for DELETE & UPDATE

		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}

		//log eventry record
		if($this->enableLog == 1){
			//If DELETE query
			if (strpos(strtoupper($query), 'DELETE') !== false) {
				$extractQuery = extractTableAndCondition($query,'DELETE');
			}else{ // If UPDATE query
				// Extract table name and WHERE condition from $query
				$extractQuery = extractTableAndCondition($query,'UPDATE');
			}

			// Output the extractQuery
			if ($extractQuery) {
				$tableName = $extractQuery['table'];
				$whereCondition = $extractQuery['where'];
				

				if (strpos(strtoupper($query), 'UPDATE') !== false) {

					//extract whereCondition & get only id from this
					$entryId = explode("=",$whereCondition);


					// SELECT TO GET OLD DATA
					$query1  = "SELECT * from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					//Define variable for oldData
					$oldData = $query1Result['result_set'][0];

					//TO GET NEW DATA
					$newData = $extractQuery['new_data'];

					// Unset 'UpdatedAt' and 'UpdatedBy' from $oldData
					unset($oldData['UpdatedAt']);
					unset($oldData['UpdatedBy']);

					// 2. Compare Old Data with New Data
					$changes = []; // Array to store changes

					foreach ($newData as $key => $new_value) {
						// Check if the key exists in old_data
						if(is_array($oldData)){
							if (array_key_exists($key, $oldData)) {
								
								// Check if the value in oldData is a DateTime object
								if ($oldData[$key] instanceof DateTime) {
									$old_value = $oldData[$key]->format('Y-m-d'); // Format DateTime to string
								} else {
									$old_value = $oldData[$key]; // Keep the value as it is if it's not a DateTime object
								}

								// Check if the new value is a DateTime object
								if ($new_value instanceof DateTime) {
									$new_value = $new_value->format('Y-m-d'); // Format DateTime to string
								}

								// Now compare the values
								if ($old_value != $new_value) {
									$changes[] = [
										'field' => $key,
										'old_value' => $old_value,
										'new_value' => $new_value
									];
								}
							}
						}
					}

					// print_r($changes);
					// echo "<pre></pre>";
					// exit();

					// 3. Log Changes in Logs Table if there are changes
					if (!empty($changes)) {
						// Insert logs for changes
						foreach ($changes as $change) {
							$field = $change['field'];
							$old_value = $change['old_value'];
							$new_value = $change['new_value'];

							// LOG NEW DATA IN LOGS TABLE
							$log_sql = "INSERT INTO logs (field, Mode, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
									VALUES ('$field', 'EDIT', '$old_value', '$new_value', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $entryId[1], $entryId[1], '{$_POST['FormID']}', '{$_SESSION['user_id']}')";
								
							$Result = db::getInstance()->db_insertQuery($log_sql);
						}

					} 
				}else{
					// Check if the string contains an assignment (field = value) with an "AND" condition
					if (strpos($whereCondition, 'AND') !== false) {
						// Regular expression to match any field and its associated value (e.g., entryid = 1, bid = 5, etc.)
						preg_match('/\b\w+\s*=\s*\d+/', $whereCondition, $matches);

						// Print the matched part (e.g., entryid = 1)
						if (isset($matches[0])) {
							$entryId = explode("=",$matches[0]);  // Output: entryid = 1 (or any other field=value)
						}
					}else{
						//extract whereCondition & get only id from this
						$entryId = explode("=",$whereCondition);
					}

					// SELECT TO GET OLD DATA
					$query1  = "SELECT id from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					for($j=0; $j<count($query1Result['result_set']); $j++){
						$query2  = "SELECT * from $tableName where id= '".$query1Result['result_set'][$j]['id']."'";
						$query2Result = db::getInstance()->db_select($query2);

						//Define variable for oldData
						$oldData = $query2Result['result_set'];
				
						$json_oldData = json_encode($oldData);

						if($json_oldData != 'null'){
							// LOG NEW DATA IN LOGS TABLE
							$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
									VALUES ('DELETE', '', '$json_oldData', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $entryId[1], '{$query1Result['result_set'][$j]['id']}', '{$deleteFormID}', '{$_SESSION['user_id']}')";			
							$Result = db::getInstance()->db_insertQuery($log_sql);
						}
					}	
				}	
			}
		}

		// echo $query;
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		if ($this->query($query)) {
			$output['error'] = "0";
			$output['num_rows'] = $this->affected_rows;
		} else {
			$output['error_statement'] = "Error: " . $this->error;
		}
		return $output;
	}
	//INSERT Fn
	public function db_insert($table, $set, $val) { ///returns should have at least 1 set & set==val
		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}

		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		if(!(strlen($table) > 0 && sizeof($set) > 0 && sizeof($set) == sizeof($val))){
			$output['error_statement'] = "Invalid Arguments";
			return $output;
		}
		$s1 = "INSERT INTO ".$table." ("; 
		$s2 = " ";
		$separator = "";
		for($i = 0; $i < sizeof($set); $i++){
			$s1 .= $separator . $set[$i];
			$s2 .= $separator . "'" . mysqli_real_escape_string($this, $val[$i]) . "'";
			$separator = ",";
		}
		$sql = $s1 . " ) Values( " . $s2 . ")";

		if ($result = $this->query($sql)) {
			$output['error'] = "0";
			$output['last_id'] = $this->insert_id; //returns 0 if PK not set/AI
			
			//Check log is enabled
			if($this->enableLog == 1){
			
				if($_POST['editID']){
					$editid = $_POST['editID'];
				}else{
					$editid = $output['last_id'];
				}
				// LOG NEW DATA IN LOGS TABLE
				$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
				VALUES ('ADD', '', '', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, {$output['last_id']}, '{$_POST['FormID']}', '{$_SESSION['user_id']}')";
				
				db::getInstance()->db_insertQuery($log_sql);
			}
		} else {
			$output['error'] = "1";
			$output['error_statement'] = "Error: " . $this->error;
		}

		return $output;
	}
	
	//INSERT Fn with Query
	public function db_insertQuery($query) {  //returns should have at least 1 set

		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}

		$sql = $query;
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		// echo $query;exit();
		if(!(strlen($query) > 0)){
			$output['error_statement'] = "Invalid Arguments";
			return $output;
		}

		if($this->enableLog == 1){
			// Check if the query contains INSERT or UPDATE (case-insensitive)
			if(stripos($sql, 'UPDATE') !== false){
				// Find the position of the "SELECT SCOPE_IDENTITY()" part in the query
				$selectPos = strpos($sql, "SELECT SCOPE_IDENTITY()");

				// If "SELECT SCOPE_IDENTITY()" is found, remove it and everything after
				if ($selectPos !== false) {
					$sql = substr($query, 0, $selectPos);  // Keep everything before "SELECT SCOPE_IDENTITY()"
				}

				// Extract table name and WHERE condition from $query
				$extractQuery = extractTableAndCondition($sql,'UPDATE');

				// Output the extractQuery
				if ($extractQuery) {
					$tableName = $extractQuery['table'];
					$whereCondition = rtrim(trim($extractQuery['where']),";");

				

					// SELECT TO GET OLD DATA
					$query1  = "SELECT * from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					// Query to get the primary key column name from the database
					$query3 = "SELECT * FROM kmaingrid WHERE TableName = '".$tableName."'";
					$query3Result = db::getInstanceMAster()->db_select($query3);

					//extract whereCondition & get only id from this
					$entryId = explode("=",$whereCondition);
					$editid = $query1Result['result_set'][0][$query3Result['result_set'][0]['TablePrimaryKey']];
					$gridIdValue = $query3Result['result_set'][0]['GridId'];
					if (strpos(strtoupper($sql), 'UPDATE') !== false) {
						//Define variable for oldData
						$oldData = $query1Result['result_set'][0];

						//TO GET NEW DATA
						$newData = $extractQuery['new_data'];

						// Unset 'UpdatedAt' and 'UpdatedBy' from $oldData
						unset($oldData['UpdatedAt']);
						unset($oldData['UpdatedBy']);

						// 2. Compare Old Data with New Data
						$changes = []; // Array to store changes

						foreach ($newData as $key => $new_value) {
							// Check if the key exists in old_data
							if(is_array($oldData)){
								if (array_key_exists($key, $oldData)) {
									
									// Check if the value in oldData is a DateTime object
									if ($oldData[$key] instanceof DateTime) {
										$old_value = $oldData[$key]->format('Y-m-d'); // Format DateTime to string
									} else {
										$old_value = $oldData[$key]; // Keep the value as it is if it's not a DateTime object
									}

									// Check if the new value is a DateTime object
									if ($new_value instanceof DateTime) {
										$new_value = $new_value->format('Y-m-d'); // Format DateTime to string
									}

									// Now compare the values
									if ($old_value != $new_value) {
										$changes[] = [
											'field' => $key,
											'old_value' => $old_value,
											'new_value' => $new_value
										];
									}
								}
							}
						}

						// 3. Log Changes in Logs Table if there are changes
						if (!empty($changes)) {
							// Insert logs for changes
							foreach ($changes as $change) {
								$field = $change['field'];
								$old_value = $change['old_value'];
								$new_value = $change['new_value'];

								// LOG NEW DATA IN LOGS TABLE
								echo $log_sql = "INSERT INTO logs (field, Mode, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy, GridID) 
										VALUES ('$field', 'GRID EDIT', '$old_value', '$new_value', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, $entryId[1], '{$_POST['FormID']}', '{$_SESSION['user_id']}',$gridIdValue)";
								
								$Result = db::getInstance()->db_insertQuery($log_sql);
							}

						} 
					
					}
				}
			}
		}
		// echo $sql;
	
		if ($result = $this->query($sql)) {
			$output['error'] = "0";
			$output['last_id'] = $this->insert_id; //returns 0 if PK not set/AI

			//Check log is enabled
			if($this->enableLog == 1){
				// Check if the query contains INSERT or UPDATE (case-insensitive)
				if(stripos($sql, 'INSERT') !== false){
					
					// Find the position of the "SELECT SCOPE_IDENTITY()" part in the query
					$selectPos = strpos($sql, "SELECT SCOPE_IDENTITY()");

					// If "SELECT SCOPE_IDENTITY()" is found, remove it and everything after
					if ($selectPos !== false) {
						$sql = substr($query, 0, $selectPos);  // Keep everything before "SELECT SCOPE_IDENTITY()"
					}

					//get count of insert query
					// Regular expression to count occurrences of a row (based on the `VALUES` keyword)
					preg_match_all('/\(\d+,.*?\)/', $sql, $matches);

					// Output the count of rows
					$rowsAffected = count($matches[0]);

					// Use a regular expression to extract the table name after 'INSERT INTO'
					preg_match('/INSERT INTO\s+([a-zA-Z0-9_]+)/i', $sql, $matches);

					// Extracted table name will be in $matches[1]
					$tableName = $matches[1];

					//If logs table found then don't add
					if($tableName != 'logs'){
						// Query to get the primary key column name from the database
						$query3 = "SELECT * FROM kmaingrid WHERE TableName = '".$tableName."'";
						$query3Result = db::getInstanceMAster()->db_select($query3);

						//Not getting inserted id so using select query getting this
						$query4 = "SELECT id FROM $tableName WHERE CreatedBy= '{$_SESSION['user_id']}' OR UpdatedBy= '{$_SESSION['user_id']}' ORDER BY id DESC LIMIT $rowsAffected";
						$query4Result = db::getInstance()->db_select($query4);

						// Check if query result is correct
						if (isset($query3Result['result_set'][0]['TablePrimaryKey'])) {
							$matches = [];

							// Use regular expression to match the first value in the VALUES clause
							preg_match('/VALUES\s*\((.*)\)/s', $sql, $matches);
							if (isset($matches[1])) {
								// $matches[1] contains the values part of the query
								$values = $matches[1];

								// Split values by commas, but don't split inside quotes
								$pattern = '/\s*,\s*(?=(?:[^"]*"[^"]*")*[^"]*$)/';
								$valuesArray = preg_split($pattern, $values);

								// Clean up quotes and whitespace around the values
								$cleanValuesArray = array_map(function($value) {
									return trim($value, " \t\n\r\0\x0B'\"");
								}, $valuesArray);

								// Extract the first value
								$editid = $cleanValuesArray[0];
								echo $editid;
							}
					
							for($i=count($query4Result['result_set'])-1; $i >= 0; $i--){
								
								$entryIdValue = $query4Result['result_set'][$i]['id'];
								$gridIdValue = $query3Result['result_set'][0]['GridId'];
								// LOG NEW DATA IN LOGS TABLE
								$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy, GridID) 
								VALUES ('GRID ADD', '', '', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, $entryIdValue, '{$_POST['FormID']}', '{$_SESSION['user_id']}',$gridIdValue)";
								$Result = db::getInstance()->db_insertQuery($log_sql);
							}
			
						}
					}

				}
			}
		} else {
			$output['error'] = "1";
			$output['error_statement'] = "Error: " . $this->error;
		}
		return $output;
	}	
}
?>