<?php

if(!isset($_SESSION)) session_start();

//require_once("loginCheckPoint.php");
//require_once("authorizations.php"); 
require_once("demosettings.php");
require_once 'write_log.php';
require_once("parseUtils.php");
require_once("sparql.php");
require_once("slotToCaption.php");
require_once 'globalArr.php';
require_once 'searchWord.php';
require_once 'sentimentUtils.php';

//check if search_function is empty
if (!isset($_REQUEST["search_function"]) && !isset($_REQUEST["skip_include"])){
	require_once("loginCheckPoint.php");
	require_once("authorizations.php");
	search($mysqli);
}

function search($mysqli, $savedSearchName=null, $searchParameters=null, $extrnalParmeters=null, $tableAndCaption=null, $searchJson=null, $ignore_limit=null, $numberToFetch=null, $offset=null, $skip_echo = null) {
	global $listPageColumn,$limitResult;
	global $textDataBase, $separateTextQuery, $useSolr;
	global $oneSearchQuery; 
	global $isLegal;
	global $aggregationArr;
	global $dropdownArrSolr;
	global $extraDataKey2Table;

	$userdetails = getUserDetails();
	$username = $userdetails['Username'];
	//////////////////////////////////////////////////
	//
	//	REQUIRED REFACTORING AS IN BACKGROUND_FUNCS - GAL
	//
	///////////////////////////////////////////////////
	
	

	$isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
	$params=[];
    $p=[];
	$isAdmin = getUserPermission(CAN_SHOW_ALL_USER);
    
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	validateSavedSearchStart - validate that the savedsearch is not running and valid
	//
	//////////////////////////////////////////////////////////////////////////////////////////////
	$searchId = validateSavedSearchStart($mysqli, $username, $savedSearchName);
	if(is_array($searchId) && array_key_exists("Error",$searchId)){
         die(json_encode($searchId));
    }
	
	//////////////////////////////////////////////////
	//
	//	If you add something to the $_REQUEST you need to add it to saved search parameters
	//
	///////////////////////////////////////////////////

	//Solr start checkup - if the search if for solr - arrange the params
// 	if($useSolr && isset($searchParameters[0]['regular']) && count($searchParameters) == 1){
// 	    $table = key($searchParameters[0]['regular']);
// 	    $val = $searchParameters[0]['regular'][$table];
// 	    $orgSolrField = array_search($table,$extraDataKey2Table);
// 	    if(in_array($orgSolrField, $dropdownArrSolr)){
// 	        $_REQUEST = array_merge($_REQUEST ?? [], array("Action"=>"search", "inResults"=>"results", "searchFreeText"=>$orgSolrField.':'.$val, "searchLabel"=>"", "skip_include" => true));
//     	    $searchParameters = null;
// 	    }
// 	}

	$inResults = false;
	if ($searchParameters != null){
		//change get duplictae to true to support old version		
		$getDuplicate = true;				
	}elseif ($savedSearchName === null){
                $params = getParameters($_REQUEST);
                $inResults = (!empty($_REQUEST['inResults']) && $_REQUEST['inResults'] === 'true');
	}else{

		//read json from table savedsearch
		$sql = "SELECT parameters FROM savedsearch WHERE name='$savedSearchName' AND username='$username'";
		$results = sqlQuery($mysqli, $sql);
		$parameters = $results->fetch_object()->parameters; 
		$p = json_decode($parameters, true);
		
		//check if it smart search
		if(!empty($p))
		if(array_key_exists("smart", $p[0]) || array_key_exists("regular", $p[0])){
		    $res = getDetailsForSmartSearch($mysqli, $parameters);
			$searchParameters = $res["searchParameters"];
			$extrnalParmeters = $res["entityParmetersArr"];
			$tableAndCaption = $res["tableAndCaption"];	
		}else{	
            $params = getParameters($p);
            $searchRelation          =  key2val($params, "searchRelation");
                        
			if (strlen($searchRelation) > 0){
				$arr = array("subjectType","objectType","subjectName","objectName","relationType");
				$relattinArr = array();
				foreach ($arr as $v){
					if (array_key_exists($v, $p) && strlen($p[$v]) > 0)
						$relattinArr[$v] = $p[$v];				
				}			
			}
			$inResults = ($p['inResults'] === 'true');
		}
	}
	
	
	
    $searchLabel            =  key2val($params, "searchLabel") ?? "";
	$searchPlaces            =  key2val($params, "searchPlaces");
	$searchPerson            =  key2val($params, "searchPerson");
	$searchFreeText          =  $_REQUEST['searchFreeText'] ?? key2val($params, "searchFreeText");
	$isRegex                 =  $_REQUEST['isRegex'] ?? false;
	$searchOrganization      =  key2val($params, "searchOrganization");
	$searchSentiment         =  key2val($params, "searchSentiment");
	$searchOther             =  key2val($params, "searchOther");
	$searchEvents            =  key2val($params, "searchEvents");
	$searchDateofWriting     =  key2val($params, "searchDateOfWriting");
	$searchDateMentioned     =  key2val($params, "searchDateMentioned");
	$searchDateofAnalysis    =  key2val($params, "searchDateofAnalysis");
	$searchUserNotesUsers    =  key2val($params, "searchUserNotesUser");
	$searchUserNotesText     =  key2val($params, "searchUserNotesText");
	$searchTags              =  key2val($params, "searchTags");
	$searchNotTags           =  key2val($params, "searchNotTagged");
	$predictiveCodingText    =  key2val($params, "predictiveCodingText");
	$predictiveCodingTag     =  key2val($params, "predictiveCodingTag");
	$searchTopicDomain       =  key2val($params, "document_topic");
	$searchIdeologicalStream =  key2val($params, "ideological_stream");
	$searchCountedTheatre    =  key2val($params, "counted_theatre");
	$searchIslamicSect       =  key2val($params, "islamic_sect");
	$searchLanguage          =  key2val($params, "languages");
	$searchSavesearch        =  key2val($params, "searchSavesearch");
	$searchThreatRating      =  key2val($params, "threat_rating");
	$getDuplicate            =  $getDuplicate ?? $params["getDuplicate"] ?? null;
	$searchEmailFrom         =  key2val($params, "searchEmailFrom");
	$searchEmailTo           =  key2val($params, "searchEmailTo");
	$searchEmailCc           =  key2val($params, "searchEmailCc");
	$searchEmailBcc          =  key2val($params, "searchEmailBcc");
	$searchEmailRecipient    =  key2val($params, "searchEmailRecipient");
	$searchFileFormat        =  key2val($params, "file_format");
	$searchGender            =  key2val($params, "searchGender");
	$searchEthnicity         =  key2val($params, "searchEthnicity");
	$searchNational          =  key2val($params, "searchNational");
	$searchConcept           =  key2val($params, "searchConcept");
	//relation
	$searchRelation          =  key2val($params, "searchRelation");
	$subjectType             =  key2val($params, "subjectType");
	$subjectName             =  key2val($params, "subjectName");
	$objectType              =  key2val($params, "objectType");
	$objectName              =  key2val($params, "objectName");
	$relationType            =  key2val($params, "relationType");
	$ignoreSubject           =  key2val($params, "ignoreSubject");
	$predictive_search = (isset($_REQUEST['predictivesearch'])) ? $_REQUEST['predictivesearch']  : null;
	$predictive_search_docid =  (isset($_REQUEST['predictivesearchDocId'])) ? $_REQUEST['predictivesearchDocId']  : null; //single document predictive coding
	$extradata_pred = isset($_REQUEST['extraData']) ?  json_decode($_REQUEST['extraData'], true) : null;

	global $extraColumns;
	$req_or_p = !empty($p) ? $p : $_REQUEST;
	$searchExtra = getSearchExtra($extraColumns, $req_or_p);

	
	$searchString = "";
	$searchSqls = [];
	$searchEntityArr = array();
	$searchEntityArr['per'] = !empty($searchPerson) ? $searchPerson : null;
	$searchEntityArr['org'] = !empty($searchOrganization) ? $searchOrganization : null;
	$searchEntityArr['loc'] = !empty($searchPlaces) ? $searchPlaces : null;
	
	$lastSearchs = "";
	write_to_log("PERMORMENCE", "search start time - " . date('H:i:s'.substr((string)microtime(), 1, 8)));
	if(!empty($inResults)){		
		$lastSearchResult = "SELECT id,label,per,org,loc FROM savedsearch WHERE username='$username'";
		$results = sqlQuery($mysqli, $lastSearchResult);
		if (!empty($results)){
			$row = $results->fetch_object();
			if (!empty($row)){
				$lastSearchsid =0;
				$searchString = $row->label;				
				if (strlen($searchEntityArr['per']) > 1)
					$searchEntityArr['per'] .= "&";
				if (strlen($searchEntityArr['org']) > 1)
					$searchEntityArr['org'] .= "&";
				if (strlen($searchEntityArr['loc']) > 1)
					$searchEntityArr['loc'] .= "&";
				$searchEntityArr['per'] .= $row->per;
				$searchEntityArr['org'] .= $row->org;
				$searchEntityArr['loc'] .= $row->loc;
			}
		}
		write_to_log("PERMORMENCE", "search finish inResults -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
	}

	//keep original search places/organization, as it is erased by $oneSearchQuery (to prevent multiple queries)
	$searchPlacesOrig = !empty($searchPlaces) ? $searchPlaces :null;
	$searchOrganizationOrig = !empty($searchOrganization) ? $searchOrganization : null;
	$searchEventsOrig = !empty($searchEvents) ? $searchEvents : null;
	//config definition
	if ($oneSearchQuery)
		$useSearchEntities = true;
	
	if ($useSearchEntities)
	{
		if ($searchPlaces != "")
		{
			$entitiesToSearch[] = array("text" => $searchPlaces, "type" => "place");
			if (strlen($searchString) > 0)
					$searchString .= ",";					
			$searchString .= " Place-" . $searchPlaces;				
		}
		$searchPlaces = "";
		//TODO: omit org prefixes/suffixes
		if ($searchOrganization != "")
		{
			$entitiesToSearch[] = array("text" => $searchOrganization, "type" => "organization");
			if (strlen($searchString) > 0)
					$searchString .= ",";					
			$searchString .= " Organisation-" . $searchOrganization;				
		}
		$searchOrganization = "";
		
		if ($searchEvents != "")
		{
			$entitiesToSearch[] = array("text" => $searchEvents, "type" => "event");
			if (strlen($searchString) > 0)
					$searchString .= ",";					
			$searchString .= " Events-" . $searchEvents;				
		}
		$searchEvents = "";
	}
	else
	{
		$searchPlaces = $mysqli->real_escape_string($searchPlaces);
		$searchOrganization = $mysqli->real_escape_string($searchOrganization);
	}
	if ($searchPerson != "")
	{
		$entitiesToSearch[] = array("text" => $searchPerson, "type" => "person,email");
		$searchString .= " Person-" . $searchPerson;
	}
	
	$sql = "";
    $file_col = filesTableColumen();
	$first_sql = true;
	$arr_id = array();	
	$doc2entities = array();
	$returnEmpty = false;	

	$id = "id"; 
        $suffix = "";
       if (!isSearchParamsEmpty($p, $searchExtra, $getDuplicate)) {
            $sql = "SELECT DISTINCT files.$id FROM files,";
            $suffix = getSuffix($id, $p, $inResults, $lastSearchsid ?? null, $searchExtra,$getDuplicate);
        } else{	
            $sql = "SELECT DISTINCT files.$id FROM files";
            if (isset($inResults) && $inResults) {
                $suffix = " AND files.id IN(SELECT docid FROM savedsearchid WHERE id=$lastSearchsid)";
            }
	}														
	
	if ($searchParameters != null){
		if (strlen($searchString) > 0)
				$searchString .= ",";							
		foreach ($searchParameters as $k => $tab){
		    $suffix .= " AND (";
		    $searchType = !empty($tab["smart"]) ? "smart" : "regular";
		    $in_cond = "IN";
		    
		    if(!empty($tab['not'])){
		        $in_cond = "NOT IN";
		    }
    
			$first = true;	
			if($searchType=="regular"){
    			foreach ((array)$tab["regular"] as $t => $v){
    			    
    			    $v_str = is_array($v) ? json_encode($v): $v;
    			 
    				$searchString .= " $t=>$v_str,";				
    				if ($first)
    					$first = false;
    				else 
    					$suffix .= " OR ";
    				$suffix .= "files.id $in_cond (" . getSearchString($mysqli, $t, $v, $table2CaptionsArray, $username, ") OR files.id IN (",$isAdmin) . ")";
    			
    				if (!(empty($table2CaptionsArray)))
    					$table2CaptionsArrayNew[] = $table2CaptionsArray;
    			}
            }   
            
            if($searchType=="smart"){
    			foreach ((array)$tab["smart"] as $v => $vv){
    				if ($first)
    					$first = false;
    				else 
    					$suffix .= " OR ";								
    				$suffix .= " files.id $in_cond ($vv) ";
    			}
			}
			$suffix .= ")";

		}			
	}
	if ($searchPlaces != ""){
		write_to_log("ERROR", "here: " . $sql);	
		$place = analyzeWord($searchPlaces,"Place-object");
		if (strlen($place) > 1){
			$searchPlaces = $place;
		}	
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Place-" . $searchPlaces;				
	
		$lowerSearchPlaces = strtolower($searchPlaces);
		foreach (array("place_object", "city", "province") as $place_type)
			foreach (array("name", "orgText", "country", "city", "province") as $place_field)
				$placeSqls[] = "SELECT docId,NAME,country,orgText FROM $place_type WHERE $place_field = '$lowerSearchPlaces'";
			
			
		$sql .= "(" . join(" UNION ", $placeSqls) . ") AS place";
		$first_sql = false;
	}
	
	//Events
	if ($searchEvents != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		//OR statment			
		$s = explode("^", $searchEvents);
		if (sizeof($s) > 1){
			$sql .= or_sql($searchEvents, "event", "events");
		}else{ 
			//AND statment
			$s = explode("&", $searchEvents);
			if (sizeof($s) > 1){
				$sql .= and_sql($searchEvents, "event", "events");
			}else{
				//single statment
				$sql .= single_sql($searchEvents, "event", "events");						
			}
		}
		//if ($searchOrganization != "" || $searchOther != "" || $searchTopicDomain != "")
		//	$sql .= ",";
		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Events-" . $searchEvents;
	}
	

	//Other Content
	if ($searchOther != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}				
		$e = explode("=", $searchOther);
		$sql .= "(" . buildSearchConceptSql($mysqli, $e[0], $e[1], "docId") . ") AS $e[0]";		
		$suffix = " AND files.$id=$e[0].docId" . $suffix;		
		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Other-" . $searchOther;
	}
	
	//TopicDomain
	if ($searchTopicDomain != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchTopicDomain, "document_topic", "domains");																													
		$searchString .= " Topic-" . $searchTopicDomain;
	}
	
	//ideological_stream
	if ($searchIdeologicalStream != ""){
			
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		if ($searchIdeologicalStream === "None")
			$sql .= non_sql_special_tables("ideological_stream", "ideologicalstreams");
		elseif (array_key_exists($searchIdeologicalStream,$aggregationArr["ideological_stream"]))
			$sql .= multi_sql_special_tables($searchIdeologicalStream, "ideological_stream", "ideologicalstreams");																
		//single statment	
		else
			$sql .= single_sql_special_tables($searchIdeologicalStream, "ideological_stream", "ideologicalstreams");																													
		$searchString .= " Political Orientation-" . $searchIdeologicalStream;
	}
	
	//counted_theatre
	if ($searchCountedTheatre != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchCountedTheatre, "counted_theatre", "countedtheatres");																													
		$searchString .= " Theatre-" . $searchCountedTheatre;
	}	
	
	//islamic_sect
	if ($searchIslamicSect != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchIslamicSect, "islamic_sect", "islamicsects");																													
		$searchString .= " Religious Sect-" . $searchIslamicSect;
	}
	
	//language
	if ($searchLanguage != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchLanguage, "languages", "languagess");																													
		$searchString .= " Language-" . $searchLanguage;
	}
	
	//threat_rating
	if ($searchThreatRating != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchThreatRating, "threat_rating", "threatratings");																													
		$searchString .= " Priority-" . $searchThreatRating;
	}
	
	//File Format
	if ($searchFileFormat != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		//single statment
		$sql .= single_sql_special_tables($searchFileFormat, "file_format", "fileformats");																													
		$searchString .= " File Format-" . $searchFileFormat;
	}
	
	//Gender
	if ($searchGender != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		
		if ($searchPerson != "")	
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE gender='$searchGender' AND caption='$searchPerson') AS searchgenders";
		else	
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE gender='$searchGender') AS searchgenders";		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Gender-" . $searchGender;			
	}

	//Ethnicity
	if ($searchEthnicity != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		
		if ($searchPerson != "")	
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE ethnicity='$searchEthnicity' AND caption='$searchPerson') AS searchethnicitys";
		else	
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE ethnicity='$searchEthnicity') AS searchethnicitys";							
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Ethnicity-" . $searchEthnicity;			
	}	
	
	//National Identity
	if ($searchNational != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}

		if ($searchPerson != "")	
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE national_identity='$searchNational' AND caption='$searchPerson') AS searchnationals";
		else
			$sql .= "(SELECT DISTINCT docId FROM slotdata WHERE national_identity='$searchNational') AS searchnationals";		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " National Identity-" . $searchNational;			
	}
	
	//Concept 
	if ($searchConcept != ""){					
		foreach ($searchConcept as $tab => $v){
			$suffix .= " AND (";
			$first = true;	
			foreach ($v as $vv){
				if ($first)
					$first = false;
				else 
					$suffix .= " OR ";								
				$suffix .= " files.id IN ($vv) ";
			}
			$suffix .= ")";
		}	
					
		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Concept-" . $searchConcept;	
				
	}
	
	//Relation
	if ($searchRelation != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}			
		
		if ($relationType == "coRefs")
		{
		    if(empty($subjectType) || empty($objectType)){
		        $sql = getMultiTopCoRefs($mysqli, $subjectType, $objectType, 1000, null, $username, $isAdmin, $name1, $name2, $groupByDoc, true, $smartSearch,$pastPeriod, $saved_sql);
		    }
// 		    global $entitiesArray;
// 		    if(empty($subjectType) && empty($objectType)){
// 		        $sql .= "(";
// 		        foreach(array_keys($entitiesArray) as $subject_table){
// 		            foreach(array_keys($entitiesArray) as $object_table){
// 		                $sql .= " UNION " . getTopCoRefs($mysqli, $subject_table, $object_table, 1000, null, $username,$isAdmin, $subjectName, $objectName, true, true);
// 		            }
// 		        }
// 		        $sql .= ") AS searchrelations";
// 		    }
		    
			if (empty($objectType) || empty($subjectType))
			{
				write_to_log("ERROR", "search coRefs: empty object type or subject type");
				die("Error");
			}	
			$sql .= "(" . getTopCoRefs($mysqli, $subjectType, $objectType, 1000, null, $username,$isAdmin, $subjectName, $objectName, true, true) .") AS searchrelations";
		}
		else
		$sql .= "(" . getTopRelation($mysqli, $relationType, $subjectType, $objectType, null, null, $subjectName, $objectName, false, $ignoreSubject,$isAdmin,$username,null,null) .") AS searchrelations";
		    
				
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Relation" ;			
	}		
	
	//Email fields handling: multiple names are seperated by ^
	foreach (["from_field" => $searchEmailFrom, "to_field" => $searchEmailTo, 
			"cc_field" => $searchEmailCc, "bcc_field" => $searchEmailBcc, "recipient_field" => $searchEmailRecipient] as $fieldName => $searchValue)
	{
		if(empty($searchValue))
			continue;
		
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		
		//search as person
		$table2CaptionsArrayPartial = getSearchCaptions($mysqli, $searchValue, "person,email", NULL, false);
		$searchValueArray = [$searchValue];
		foreach ($table2CaptionsArrayPartial as $agg2table2Captions)
		foreach ($agg2table2Captions as $agg => $table2Captions)
		foreach ($table2Captions as $table => $captions)
		foreach ($captions as $caption)
			$searchValueArray[] = $caption;
		
		$searchValueStr = join(" ^ ", $searchValueArray);
		
		$emailSqlPrefix = "";
		$emailSqlSuffix = "";
		$emailFields = [$fieldName];
		if ($fieldName == "recipient_field")
		{
			$emailSqlPrefix = " (SELECT * FROM ";
			$emailSqlSuffix = ") AS recipientfields\r\n ";
			$emailFields = ["to_field", "cc_field", "bcc_field"];
		}
		$emailUnions = [];
		foreach ($emailFields as $field)
		{
			$emailTableAlias = str_replace("_", "", $field) . "s"; //from_field -> fromfields, to_field -> tofields...
			$emailUnions[] = single_sql_special_tables($searchValueStr, $field, $emailTableAlias);
		}
		$sql .= $emailSqlPrefix . join ("\r\n UNION\r\n SELECT * FROM ", $emailUnions) . $emailSqlSuffix;
		
		$searchString .= " Email " . str_replace("_field", "", $fieldName) . "-" . $searchValue;
	}

				
	//Saved search
	if ($searchSavesearch != ""){	
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		$s = explode("^", $searchSavesearch);
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){
				if ($first){
					$first = false;
					$where = "savedsearch.name='$v'";	
				}else
				 	$where .= " OR savedsearch.name='$v'";	
			}						
		}else{ 			
			$s = explode("&", $searchSavesearch);
			if (sizeof($s) > 1){
				//AND statment
				$first = true;
				foreach ($s as $v){
					if ($first){
						$first = false;
						$where = "savedsearch.name='$v'";	
					}else
					 	$where .= " AND savedsearch.name='$v'";	
				}	
			}else{
				//single statment
				$where = "savedsearch.name='$searchSavesearch'";
			}
		}
		$sql .= "(SELECT DISTINCT docId FROM 
			savedsearchid,savedsearch WHERE ($where ";							
		$sql .=") AND savedsearch.username = '$username' AND savedsearchid.id=savedsearch.id) AS searchSavesearchs";
		
		//single statment
		//$sql .= single_sql_special_tables($searchThreatRating, "threat_rating", "threatratings");																													
		$searchString .= " Saved search-" . $searchSavesearch;
	}
		
	//search extra
	foreach ($searchExtra as $table => $searchValue){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}																		
		
		if (array_key_exists($table,$aggregationArr["add_to_table"]))
			$sql .= range_sql_special_tables($searchValue, $table, $table);
		elseif (array_key_exists($table,$aggregationArr))
			$sql .= multi_sql_special_tables($searchValue, $table, $table);															
		//single statment	
		else
			$sql .= single_sql_special_tables($searchValue, $table, $table);																																
		$searchString .= " " . $extraColumns[$table] . "-" . $searchValue;
	}
	
	if ($searchTags)
	{
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}

		$s = explode("^", $searchTags);
		$none = false;
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){				
				if(strpos($v,NOT_REVIEWED) !== FALSE){
					$w = "(files.id NOT IN (SELECT DISTINCT(filetags.docId) FROM filetags WHERE filetags.name='Reviewed'))";
					$searchString .= " Tag- " . NOT_REVIEWED;
				}else{
					$e = explode(" => " ,$v);
					$tagGroupName = $e[0];
					$tagName = $e[1];
					$w = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
					$searchString .= " Tag- " . $tagName . " in " . $tagGroupName;
				}	
				if ($first){
					$first = false;
					$where = $w;					
				}else
				 	$where .= " OR ($w)";			 	
			}										
		}else{
			if(strpos($searchTags,NOT_REVIEWED) !== FALSE){
				$where = "(files.id NOT IN (SELECT DISTINCT(filetags.docId) FROM filetags WHERE filetags.name='Reviewed'))";
		 		$searchString .= " Tag- " . NOT_REVIEWED;
			}else{
				$e = explode(" => " ,$searchTags);
				$tagGroupName = $e[0];
				$tagName = $e[1];
				$where = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
			}
		}
		$sql .= "(SELECT DISTINCT files.id FROM files, filetags WHERE $where) AS tags1";		
	}	
	
	if ($searchNotTags)
	{
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}

		$s = explode("^", $searchNotTags);
		$none = false;
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){								
				$e = explode(" => " ,$v);
				$tagGroupName = $e[0];
				$tagName = $e[1];
				$w = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
				$searchString .= " Not Tag- " . $tagName . " in " . $tagGroupName;					
				if ($first){
					$first = false;
					$where = $w;					
				}else
				 	$where .= " OR ($w)";			 	
			}										
		}else{			
			$e = explode(" => " ,$searchNotTags);
			$tagGroupName = $e[0];
			$tagName = $e[1];
			$where = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
			$searchString .= " Not Tag- " . $tagName . " in " . $tagGroupName;
		}
		$sql .= "(SELECT DISTINCT files.id FROM files, filetags WHERE files.id NOT IN(SELECT DISTINCT files.id FROM files, filetags WHERE $where)) AS tags2";		
		
	}
	
	//without duplicate doc ids
	if (!$getDuplicate){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		$sql .= " (SELECT id FROM duplicate_files WHERE duplicatedDocId IS NULL) AS duplicate";		
	}
		
	//Organization
	if ($searchOrganization != ""){
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}
		$arr_id = getEntitiesManage($searchOrganization,"org","organizational_identity",$mysqli);
		if (count($arr_id) == 0){					
			$organization = analyzeWord($searchOrganization,"Organizational-identity");
			if (strlen($organization) > 1){
				$searchOrganization = $organization;					
			}					
			///////////////////////
			// 1. Add company parameters (LTD, PLC, INC etc) to organization search
			// 2. Remove "The" "le" when searching organization
			// 3. If the organisation is composed of more than 3 tokens - then allow any with those three tokens
			// 4. If known organisation then return any organisation with that component
			///////////////////////
			
			$name_c = "";								
			$e = explode(' ',$searchOrganization);
			if (array_key_exists($e[0],$organizationARC_prefix)){	
				$c = explode($e[0],$name_c);
				$name_c = trim($c[1]);																																			
			} 														
			if (array_key_exists($e[sizeof($e) - 1],$organizationARC)){										
				if (strlen($name_c) > 0){
					$first = false;
					$c = explode($e[0],$name_c);
					$e = explode($e[sizeof($e) - 1],$name_c);
					$name_c = trim($e[0]);
				}else{
					$e = explode($e[sizeof($e) - 1],$searchOrganization);
					$name_c = trim($e[0]);
				}												
			}	
			
			if (strlen($name_c) > 0){
				$sql .= single_sql($mysqli, $name_c, "organizational_identity", "organization_clean");
			}
			
			
			//OR statment			
			$s = explode("^", $searchOrganization);				
			if (sizeof($s) > 1){
				$sql .= or_sql($searchOrganization, "organizational_identity", "organization");
			}else{ 
				//AND statment
				$s = explode("&", $searchOrganization);
				if (sizeof($s) > 1){
					$sql .= and_sql($searchOrganization, "organizational_identity", "organization");
				}else{
					//single statment
					$sql .= single_sql($searchOrganization, "organizational_identity", "organization");						
				}
			}	
			if (strlen($searchString) > 0)
				$searchString .= ",";					
			$searchString .= " Organisation-" . $searchOrganization;		
		}
		else{
			if (strlen($searchString) > 0)
				$searchString .= ",";					
			$s = "SELECT aggregatedName,aggregatedManual FROM entitiesmanage WHERE entity=\"org\" AND NAME=\"$searchOrganization\"";
			$res = sqlQuery($mysqli, $s);
			
			if (!empty($res)){
				$row = $res->fetch_object();
				if (!empty($row)){
					$searchString .= " Organisation ($searchOrganization)-" . $row->aggregatedName . $row->aggregatedManual;
				}
			}
		}
	}
	
	//Date Mentioned
	if ($searchDateMentioned != ""){									
		$date .= getDateSql($searchDateMentioned,"date");				
		if ($first_sql){
			$first_sql = false;
		}else{
			$sql .= ",";
		}					
	
		$sql .= "(SELECT docId FROM 
				date WHERE " . 					
				$date;												
		$sql .= ") AS DateMentioned";									
		
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " Date Mentioned-" . $searchDateMentioned;
	}
	
	if ($isAdmin)				
		$sql .= " WHERE TRUE";
	else
		$sql .= " WHERE files.username='$username'";
	
	$s = explode(";", $searchLabel ?? "");							
	if (sizeof($s) > 1){				
		foreach ($s as $v){
			if ($v != ""){
				$t = explode("=", $v);												
				$key = $t[0];
				$value = $t[1];
				if ($key == "predictiveCodingText")
				{
					$predictiveCodingText = $value;
					continue;
				}
				if ($key == "predictiveCodingTag")
				{
					$predictiveCodingTag = $value;
					continue;
				}
				$searchString .= $listPageColumn[$key] . "-$value ";
				$condition = $mysqli->real_escape_string($key) . " LIKE LOWER('%" . $mysqli->real_escape_string($value) . "%')";
				if ($key == "counted_theatre")
				{
					$value = analyzeWord($value,"Place-object");
					if (!empty($value))
						$condition = "($condition OR " . $mysqli->real_escape_string($key) . " LIKE LOWER('%" . $mysqli->real_escape_string($value) . "%'))";
				}
				
				$sql .= " AND " . $condition;						
			}
		}
	}
													
	//User Notes
	if ($searchUserNotesUsers != ""){
		if ($searchUserNotesUsers != "All"){
			$sql .= " AND (files.user_notes LIKE LOWER('%$searchUserNotesUsers%'))";					
		}else{
			$sql .= " AND (files.user_notes IS NOT NULL)";
		}
		if (strlen($searchString) > 0)
				$searchString .= ",";					
		$searchString .= " User Notes-" . $searchUserNotesUsers;			
	}		
		
	
	//Date of Writing			
	if ($searchDateofWriting != ""){
		$sql .= "AND (" . getDateSql($searchDateofWriting,"files") . ")";				
		$searchString .= " Date of Writing-" . $searchDateofWriting;			
	}	

	if ($searchEvents != "" || $searchOrganization != "" || $searchOther
	 	!= "" || $searchPlaces != "" || $searchDateMentioned != "" || $searchDateofAnalysis != "" 
	 	|| $searchLabel != "" || $searchUserNotesUsers != "" || $searchFileFormat != "" || $searchTopicDomain != "" || $searchIdeologicalStream != ""
	 	|| $searchCountedTheatre != "" || $searchIslamicSect != "" || $searchLanguage != "" || $searchThreatRating != ""
		|| !empty($searchExtra)
	 	|| $searchTags != "" || $searchNotTags != "" || $searchEmailFrom !="" || $searchEmailTo !="" || $searchDateofWriting != "" || (!$getDuplicate) != ""
		|| $searchEmailCc !="" || $searchEmailBcc !="" || $searchEmailRecipient !=""
	 	|| $searchGender != "" || $searchEthnicity != "" || $searchNational != "" || $searchRelation != "" || $searchConcept != "" || $searchParameters != null){
 	    
 	    //takes care of past period
 	    $dates = dateDetails($mysqli, $username);    
 	    $sql_date =  !empty($dates["date_sql"]) ? "  files.id IN $dates[date_sql]" : " TRUE ";
 	    $suffix .= " AND $sql_date";
 	    
		$sql .= $suffix;// . " limit $limitResult";		
			
		write_to_log("TRACE", $sql);		
 		if ($oneSearchQuery)
		{
			$searchSqls[] = array("docId" => "id", "Query" => $sql);
		}
		else //multiple queries
		{
		write_to_log("PERMORMENCE", "search start run sql - $sql time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
		$res = sqlQuery($mysqli, $sql);
		write_to_log("PERMORMENCE", "search end run sql time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));				
		//die("here");
		if (!empty($res)){					
			while ($row = $res->fetch_object()) {								
				if (!empty($row->id)){
					$arr_id[$row->id] = $row->id;	
				}elseif (!empty($row->userId)){
					$arr_id[$row->userId] = $row->userId;	
				}
			}					
		}
		if (empty($arr_id))
			$returnEmpty = true;	
		write_to_log("PERMORMENCE", "search end insert arr_id time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
		}					
	 }
	
	 //***predictive coding***
	if ($predictiveCodingTag) {
		$e = explode(" => " ,$predictiveCodingTag);
		$tagName = end($e);
		$predictSql = "SELECT person_object.orgText FROM filetags, person_object
			WHERE filetags.name = '$tagName' AND filetags.docId = person_object.docId
			UNION
			SELECT organizational_identity.orgText FROM filetags, organizational_identity
			WHERE filetags.name = '$tagName' AND filetags.docId = organizational_identity.docId";
		//if $predictiveCodingTables is set to a table array in config - add a SELECT on those tables in sql:
		global $predictiveCodingTables;
		if (!empty($predictiveCodingTables))
		foreach ($predictiveCodingTables as $pcTable)
		{
			$predictSql .= "UNION
			SELECT $pcTable.orgText FROM filetags, $pcTable
			WHERE filetags.name = '$tagName' AND filetags.docId = $pcTable.docId";
		}
		
		$collec = sqlQuery($mysqli, $predictSql);
		write_to_log("TRACE", "predictive coding tag sql: $predictSql");
		while ($collec && $row = $collec->fetch_object()) {
			$predictedCodingTaggedOrgTexts[$row->orgText]++;
		}
		if ($predictedCodingTaggedOrgTexts)
		{
			arsort($predictedCodingTaggedOrgTexts);
			$predictedCodingTaggedOrgTexts = array_slice(array_keys($predictedCodingTaggedOrgTexts), 0, 30);
			$predictiveCodingText .= " " . join(" ", $predictedCodingTaggedOrgTexts);
		}
	}
	if ($predictiveCodingText)
		$searchFreeText .= " " . $predictiveCodingText;
	
        $keepSolrOrder = false;
	
        //***free text***
	 if ($searchFreeText != "" && !$returnEmpty)
	{
	    //check only for V2 regex
	    if(!$isRegex && $v2){
	        $pos = strpos($searchFreeText, ':');
	        if ($pos !== false) {
	            $searchFreeText = substr_replace($searchFreeText, '=', $pos, 1);
	        }
	    }
	    $ret_arr = handle_free_text($mysqli, $isAdmin, $username, $searchFreeText, $limitResult, $textDataBase, $searchString, $searchSqls, $isRegex, $getDuplicate, $searchId);
	    $arr_text_id = $ret_arr['arr_text_id'];
	    $searchString = $ret_arr['searchString'];
	    $searchSqls = $ret_arr['searchSqls'];
	    $search_values = $ret_arr['solrQUery'];	    
	 }
	 
	 //sentiment
         $res = sentiment($searchSentiment ,$mysqli,$returnEmpty,$arr_id,$searchString,$isAdmin,$username);
         $searchSentiment = $res['searchSentiment'];
         $returnEmpty = $res['returnEmpty'];
         $arr_id = $res['arr_id'];
         $searchString = $res['searchString'];
  
        
	$table2CaptionsArray = entities2CaptionLists($mysqli, $entitiesToSearch);	
	
	if (empty($entitiesToSearch))
		$entitiesToSearch = $searchSqls;
	else
	if (!empty($searchSqls))
		$entitiesToSearch = array_merge($entitiesToSearch, $searchSqls);
	
	
	$sql = "DELETE FROM savedsearchid WHERE id=$searchId";
	$res_sql = sqlQuery($mysqli, $sql);
	
	$clearParams = false; //clear params (search Vals) from savedsearch - true if it's without results
	//person			
	if (!empty($entitiesToSearch) && !$returnEmpty){
		write_to_log("PERMORMENCE", "search start person time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));				
		if (!$isAdmin)
			$wl_name = "DOCEX Entities DB-". $userdetails['Username'];
		else
			$wl_name = "DOCEX Entities DB";				
		
		write_to_log("PERMORMENCE", "search entities:" . print_r($entitiesToSearch, true));
		//$wl_res = searchEntities($entitiesToSearch);
		
		//////////////////////////////////////////////////////////////////////////////////
		//
		// First we save the result to savesearchid and than we take the first 500 results
		//
		//////////////////////////////////////////////////////////////////////////////////
				
		$searchString = $mysqli->real_escape_string($searchString);
		global $ignore_tables, $lastSearchNameDefault;
		
		$search_per = $mysqli->real_escape_string($searchEntityArr["per"] ?? "");
		$search_org = $mysqli->real_escape_string($searchEntityArr["org"] ?? "");
		$search_loc = $mysqli->real_escape_string($searchEntityArr["loc"] ?? "");
		if ($extrnalParmeters != ""){	
		    if(!empty($_REQUEST["v2"])){
		        if(array_key_exists('searchSavesearch', $extrnalParmeters)){
		            $savedSearchTables = getSearchValuesFromSavedSearch($mysqli, $username, $extrnalParmeters['searchSavesearch'][0]);
		            $extrnalParmeters = array_merge($extrnalParmeters, $savedSearchTables);
		            unset($extrnalParmeters['searchSavesearch']);
		        }
		        
    		    foreach($extrnalParmeters as $table=>$value){
    		        if((!empty($savedSearchName) && $savedSearchName !== $lastSearchNameDefault) || in_array($table, $ignore_tables)){
    		            continue;
    		        }
		            if(strpos($value[0], '^') !== false){
    		            $value = explode('^', $value[0]);
    		        }
    		        elseif(strpos($value[0], '&') !== false){
    		            $value = explode('&', $value[0]);
    		        }
    		        $search_values[$table] = $value; 
    		    }
		    }
		    else{ //v1
		        if (strlen($search_per) > 0){
		            $search_per .= "&";
		        }
	            $search_per .= $mysqli->real_escape_string($extrnalParmeters["person_object"] ?? null);
	            if (strlen($search_org) > 0){
	                $search_org .= "&";
	            }
                $search_org .= $mysqli->real_escape_string($extrnalParmeters["organizational_identity"] ?? null);
                if (strlen($search_loc) > 0){
                    $search_loc .= "&";
                }
                $search_loc .= $mysqli->real_escape_string($extrnalParmeters["place_object"] ?? null);
		                    
		    }
			
			if (isset($searchJson)){
			    $parameters = !is_array($searchJson) && strlen($searchJson) > 0 ? $searchJson : null;
			}
			$parameters = $mysqli->real_escape_string($parameters ?? "");				
		}else{			
			//update search table saving last search	
			$parameters = array('searchLabel' => $searchLabel,
						   'searchPlaces' => $searchPlacesOrig,
						   'searchPerson' => $searchPerson,
						   'searchFreeText' => $searchFreeText,
						   'searchOrganization' => $searchOrganizationOrig,
						   'searchSentiment' => $searchSentiment,
						   'searchOther' => $searchOther,
						   'searchEvents' => $searchEventsOrig,
						   'searchDateOfWriting' => $searchDateofWriting,
						   'searchDateMentioned' => $searchDateMentioned,
						   'searchDateofAnalysis' => $searchDateofAnalysis,
						   'searchUserNotesUser' => $searchUserNotesUsers,
						   'searchUserNotesText' => $searchUserNotesText,
						   'searchTags' => $searchTags,
						   'searchNotTags' => $searchNotTags,
						   'document_topic' => $searchTopicDomain,
						   'ideological_stream' => $searchIdeologicalStream,
						   'counted_theatre' => $searchCountedTheatre,
						   'islamic_sect' => $searchIslamicSect,
						   'languages' => $searchLanguage,
						   'threat_rating' => $searchThreatRating,
						   'getDuplicate' => $getDuplicate,
						   'searchEmailFrom' => $searchEmailFrom,
						   'searchEmailTo' => $searchEmailTo,
						   'searchEmailCc' => $searchEmailCc,
						   'searchEmailBcc' => $searchEmailBcc,
						   'searchEmailRecipient' => $searchEmailRecipient,
						   'file_format' => $searchFileFormat,
						   'searchSavesearch' => $searchSavesearch,
						   'searchGender' => $searchGender,
						   'searchEthnicity' => $searchEthnicity,
						   'searchNational' => $searchNational,
						   'searchConcept' => $searchConcept,							
						   'searchRelation' => $searchRelation,	
						   'subjectType' => $subjectType,
						   'subjectName' => $subjectName,
						   'objectType' => $objectType,
						   'objectName' => $objectName,
			               'relationType' => $relationType,
						   'ignoreSubject' => $ignoreSubject,
						   'inResults' => $inResults);
			if (!empty($searchExtra))
				$parameters = array_merge($parameters, $searchExtra);
			
			$j = json_encode($parameters,JSON_UNESCAPED_UNICODE);
			$parameters = $mysqli->real_escape_string($j);			
		}				
		
		

		$perMegaSqls = buildSearchEntitiesSql($mysqli, $entitiesToSearch);
		$searchPerSql = buildFinalSql($perMegaSqls, $mysqli);
		
		//in case parameters are empty - we will fill it with searchParameters if provided (for params column in savedsearch)
		if(empty($parameters) && !empty($searchParameters)){
		    $parameters = $mysqli->real_escape_string(json_encode($searchParameters, true));
		}
		
		//1 - Update the params in the savedsearchId
		updateSavesearchParams_v2($mysqli,$savedSearchName,$parameters,$username,$searchString, $search_values ?? null);
	
		
		//2 - get the last savedsearch id 
		$searchId = getLastSavedSearchId($mysqli, $username, $savedSearchName);
				
		write_to_log("PERMORMENCE", "search start DELETE FROM savedsearchid");
		
		if (!empty($inResults)){
			write_to_log("PERMORMENCE", "search DELETE 0 FROM savedsearchid");
			$sql = "DELETE FROM savedsearchid WHERE id=0";
			$res_sql = sqlQuery($mysqli, $sql);
			write_to_log("PERMORMENCE", "search UPDATE savedsearchid");
			$sql = "UPDATE savedsearchid SET id=0 WHERE id=$searchId";
			$res_sql = sqlQuery($mysqli, $sql);
		}
		
		write_to_log("PERMORMENCE", "save Temp table for savedsearch, delete, insert into savedsearchid the temp");
		$sql = insert_to_saved_search_sql($mysqli, $searchPerSql);
		write_to_log("PERMORMENCE", "$sql");
		sqlQuery($mysqli, $sql);
				
		$res_sql = sqlQuery($mysqli, "INSERT INTO savedsearchid (id,docid) SELECT DISTINCT $searchId,docId from temp_savedsearchid_subquery as s");
      
		if(isset($predictive_search) || isset($predictive_search_docid)){
            
            global $predictive_search_type;
            require_once 'predictiveEng.php';
            write_to_log("TRACE", "Starting Predictive search");
            $total_files = getTotalFiles($mysqli, $isAdmin,$username);
            $PredictiveProcessClass = new PredictiveProcessClass($mysqli, $predictive_search_type,  $searchId, $predictive_search_docid, $extradata_pred, $username, $isAdmin);
            
            $PredictiveProcessClass->search_process($mysqli, $total_files); //perform the search action
            
            $score_id = $searchId = $PredictiveProcessClass->getPredictive_search_id();
                             
            $PredictiveProcessClass->filter_limit($predictive_search ?? null);
            
            $searchPerSql = "SELECT docid FROM savedsearchid WHERE id = $searchId";

            write_to_log("TRACE", "End Predictive Seacrch - continuing");
            $predictive_scores = getPredictiveScores($mysqli, $username, $searchId);
            
            highlightPredInstances($mysqli, $searchId, $extradata_pred);
        }
        
		//if we have skip_echo true - we will stop after insert to savedsearchid - no need for calculations and echo response
		if($skip_echo){
		    //in case we have savedSearchName - need to release it and update the date
		    if ($savedSearchName){
		        $date = date('YmdHis');
		        $sql = "UPDATE savedsearch SET DATE='$date',isrunning=FALSE WHERE NAME='$savedSearchName' AND username='$username'";
		        $res = sqlQuery($mysqli, $sql);
		    }
		    return !empty($arr_text_id); //return, in analytics case, if the arr_text_id is empty or not. if so - return true if not empty, false if so.
		}
		
		$res_count = sqlQuery($mysqli, "SELECT count(id) c FROM savedsearchid WHERE id=$searchId");
		$count_row = $res_count->fetch_object();
		$total_document = $count_row ? $count_row->c : 0; //when doing predictive search id == score_id 
		//get result to arr
		$limitResultSql = 500;
		$sql_pred = isset($predictive_search) ? "ORDER BY score ASC" : "";
		
        // TODO: keep order of ids in search. Suggestion: add auto-increment "Index" field to savedsearchid, and order by it in select
		if($ignore_limit){
		    $sql = "SELECT docid FROM savedsearchid WHERE id=$searchId";
		}
		
		elseif(isset($numberToFetch) && isset($offset)){
		    $sql = "SELECT docid FROM savedsearchid WHERE id=$searchId $sql_pred LIMIT $numberToFetch OFFSET $offset";
		}
		else{
		    $sql = "SELECT docid FROM savedsearchid WHERE id=$searchId $sql_pred LIMIT $limitResultSql";
		  
		}
		$res_sql = sqlQuery($mysqli, $sql);
		
		
		while ($row = $res_sql->fetch_object()){
			$personId[$row->docid] = $row->docid;
		}
		
		write_to_log("PERMORMENCE", "search entities:" . print_r($entitiesToSearch, true) . " END");
				
		if (empty($arr_id)){
			$arr_id = $personId ?? null;

		}else{
			$arr = array();
			if ($personId)
			foreach ($personId as $p){	
				if (key_exists($p, $arr_id)){
					$arr[$p] = $p;
				}								
			}	
			$arr_id = $arr;
		}
		write_to_log("PERMORMENCE", "search end person time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
		//if no results - treat as empty result
		if (empty($arr_id))
			$returnEmpty = true;
	}
	else{
	    $clearParams = true;
	}
	
	reset_is_running($mysqli, $searchId,$savedSearchName, $clearParams);
	

	//$lastSearchs
	if (strlen($lastSearchs) > 1){							
		$arr_last = explode(",",$lastSearchs);
		$arr = array();
		foreach ($arr_last as $v){
			if (array_key_exists($v,$arr_id))
				$arr[$v] = $v;
		}
		$arr_id = $arr;
	}	
	
	
	$limitResultSql = 500;
	if (!empty($arr_id)){
        $res = false;
        //get Reviewed document
        write_to_log("PERMORMENCE", "search start get reviewed col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $arr_review = getReviewed($mysqli, $arr_id, $limitResultSql, $searchId, $ignore_limit, $numberToFetch, $offset);
            
        write_to_log("PERMORMENCE", "search end get reviewed col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));

        
        //check if $tableAndCaption is empty - and fill it if we have the support
        $param_json = json_decode($searchJson, true);
        
        //check if it regular concept search - than we can add the table $tableAndCaption

        if((!empty($param_json[0]) && is_array($param_json)) && array_key_exists("regular", $param_json[0]) && array_key_exists('searchOther', $param_json[0]['regular'])){
            $table2Caption = explode('=',$param_json[0]['regular']['searchOther']);
            $curr_table = $table2Caption[0];
            $curr_caption = $table2Caption[1];
                $tableAndCaption[0][$curr_table] ="\"$curr_caption\"";
        }
                    
        //populate doc2entities
        if ($searchParameters){
            populateDoc2EntitiesNew($doc2entities, $mysqli, $tableAndCaption, $arr_id, $table2CaptionsArrayNew ?? null);
        }
        else{
            populateDoc2Entities($doc2entities, $mysqli, $table2CaptionsArray, $arr_id, $searchOther);
        }
        
        $res = false;
        if (empty($file_col)){
            $file_col = "id";
        }
        $lastSearchId = implode(",", array_slice($arr_id, 0, ($isMemSQL ? 100: $limitResultSql))); //needed to sort SolR results - should save the score for each id returned
        
        //if we have offset request
        if (isset($numberToFetch) && isset($offset)) {
            $sql = buildsqlLimitAddon("id",$file_col, $arr_id, 'files', $searchId, false, $username, $isAdmin, false, $keepSolrOrder, $lastSearchId, $numberToFetch, $offset);
                
        } 
        // if we have regular request or igonore limit request
        else {
            $sql = buildsqlLimitAddon("id",$file_col, $arr_id, 'files', $searchId, $limitResultSql, $username, $isAdmin, $ignore_limit, $keepSolrOrder, $lastSearchId);   
        }
        write_to_log("TRACE", "search get file columns sql: $sql");
        write_to_log("PERMORMENCE", "search start get files col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
   
        $res = sqlQuery($mysqli, $sql);
        write_to_log("PERMORMENCE", "search end get files col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        write_to_log("PERMORMENCE", "search start get other col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        if (!$isLegal){
            $arr_meta = getMetaDataSpecielArrFormDB($mysqli, $arr_id, $limitResultSql, $username, $isAdmin, $searchId, $ignore_limit, $numberToFetch, $offset);                
        }
        else{
            $arr_meta = array();
        }
        //get duplicate
            
        $arr_duplicate = getDuplicateId($mysqli, $arr_id, $limitResultSql, $username, $isAdmin, $searchId, $ignore_limit,$numberToFetch,$offset);
        write_to_log("PERMORMENCE", "search end get other col time -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    }
 	
	//add last search
    if (!empty($arr_id)){
		$lastSearchId = implode(",",$arr_id);
    }
    else{
		$lastSearchId = "";
    }
	
	$response = array();															
	$line = 0;	
	if (!empty($arr_id)){
		write_to_log("PERMORMENCE", "search start create result time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
		
		//If it's in ignore limit mode, we will fetch the first row keys&values and put it in the new downloaded file
		if($ignore_limit){
		    header('Content-Type:: application/csv');
		    header('Content-Disposition: attachment; filename="list_files.csv";');
                    //open the "output" stream
		    $stdout_file = fopen("php://output", 'w');
		    $first_row_file=true;
		    $num_docs=0;
		}
		
		
		
		while (($row = $res->fetch_object()) && ($ignore_limit || $line < $limitResultSql)){											
			if (!empty($row->id)){																							
				if (!empty($row->filename)){													
					$id = $row->id;
                     $aggregated_anti = $mysqli->escape_string(implode(",",array_unique(explode(';', $row->anti ?? ""))));
                     $aggregated_pro =   $mysqli->escape_string(implode(",",array_unique(explode(';', $row->pro ?? ""))));
					 $antipro = getAntiPro($aggregated_anti,$aggregated_pro);//not used
					 $insert_row = insertRow($mysqli, $username, $row,$arr_meta,$arr_duplicate,$searchFreeText,$arr_review,$doc2entities,$aggregated_anti,$aggregated_pro, $predictive_scores ?? null);
 					//if we in ignore limit mode - write it to file (that'll be downloaded later)
					if($ignore_limit){
					   writeToFile($insert_row,$first_row_file,$stdout_file);
                                           $first_row_file=false;
					   $num_docs++;
					}
					else{
					   $response["rows"][] = $insert_row;
					}
				}							
				$line++;												
			}
		}
		if(isset($insert_row['score'])){
		    usort($response["rows"], function ($item1, $item2) {
		        return $item1['score'] <=> $item2['score'];
		    });
		}
	}
	write_to_log("PERMORMENCE", "search end create result time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));	
				
	$response["max"] = getMaxFiles($mysqli, $isAdmin,$username);			
	$response["searchString"] = $searchString;	
	$response["numberDocument"] = $total_document ?? 0;
	$response["savedSearchId"] = $searchId ?? null;
	


	//////////////////////////////////////////////
	//		RETURN ONLY arr_id IF $name empty
	//////////////////////////////////////////////
	
// 	if ($savedSearchName){
// 		$date = date('YmdHis');
// 		$sql = "UPDATE savedsearch SET DATE='$date',isrunning=FALSE WHERE NAME='$savedSearchName' AND username='$username'";
// 		$res = sqlQuery($mysqli, $sql);
//         //TODO: we removed the mysqli->close() since we need it after the function - need to orginize it
// 		return $arr_id;		
// 	}
	if(array_key_exists("skip_echo", $_REQUEST) && $_REQUEST["skip_echo"]){
	    write_to_log("INFO", "skipping echo (from report etc its ok)");
	    return;
	}
	
	if(!$ignore_limit){
	    echo json_encode($response);
	}
	else{
	    fclose($stdout_file);
	    $sum_string = "Total ".$num_docs." documents were listed";
	    echo $sum_string;
	}
	
	sqlClose($mysqli, "searchEng");
	
}


function getSearchValuesFromSavedSearch($mysqli, $username, $ssname){
    $sql = "SELECT parameters FROM savedsearch WHERE name=\"$ssname\" AND username=\"$username\"";
    $results = sqlQuery($mysqli, $sql);
    $obj = $results->fetch_object();
    if(!empty($obj)){
        $param_json = $obj->parameters;
    }
    $res = getDetailsForSmartSearch_v2($mysqli, $username, $param_json);
    return $res["entityParmetersArr"];
    
}

function validateSavedSearchStart($mysqli, $username, $savedSearchName){
    $date = date('Y-m-d H:i:s');
    $time = date("Y-m-d H:i:s", time() - 1800); //30 Min
   
    $ssid = getLastSavedSearchId($mysqli, $username, $savedSearchName);
    
    $sql = "SELECT parameters FROM savedsearch WHERE id=$ssid";
    $results = sqlQuery($mysqli, $sql);
    $sql = !empty($row = $results->fetch_object()) ?
    "UPDATE savedsearch SET DATE='$date',isrunning=TRUE WHERE id=$ssid AND ((isrunning=FALSE OR isrunning IS NULL) OR (DATE < '$time')) AND username='$username'" :
    "INSERT INTO savedsearch  (name, DATE,isrunning, username) VALUES('$savedSearchName', '$date',TRUE, '$username')";
    $results = sqlQuery($mysqli, $sql);
    if ($mysqli->affected_rows == 0){
        write_to_log("error", "savesearch is already running");
        return ["Error"=>"savesearch is already running"];
    }
    return $ssid;
    
}


function filesTableColumen() {
    global $listPageColumn;
    $first = true;
    $file_col = "";
    foreach ($listPageColumn as $k => $v) {
        if ($first) {
            $first = false;
            $file_col .= "files." . $k;
        } else{
            $file_col .= "," . "files." . $k;
        }
    }
    return $file_col;
}

function getParameters($array) {
    $params=[];
    if(!empty($array)){
        $params['searchLabel'] = $array["searchLabel"] ?? null;
        $params['searchPlaces'] = $array["searchPlaces"] ?? null;
        $params['searchPerson'] = $array["searchPerson"] ?? null;
        $params['searchPerson'] = $array["searchPerson"] ?? null;
        $params['searchFreeText'] = $array["searchFreeText"] ?? null;
        $params['searchOrganization'] = $array["searchOrganization"] ?? null;
        $params['searchSentiment'] = $array["searchSentiment"] ?? null;
        $params['searchOther'] = $array["searchOther"] ?? null;
        $params['searchEvents'] = $array["searchEvents"] ?? null;
        $params['searchDateOfWriting'] = $array["searchDateOfWriting"] ?? null;
        $params['searchDateMentioned'] = $array["searchDateMentioned"] ?? null;
        $params['searchDateofAnalysis'] = $array["searchDateofAnalysis"] ?? null;
        $params['searchUserNotesUser'] = $array["searchUserNotesUser"] ?? null;
        $params['searchUserNotesText'] = $array["searchUserNotesText"] ?? null;
        $params['searchTags'] = $array["searchTags"]  ?? null;
        $params['searchNotTagged'] = $array["searchNotTagged"] ?? null;
        $params['predictiveCodingText'] = $array["predictiveCodingText"] ?? null;
        $params['predictiveCodingTag'] = $array["predictiveCodingTag"] ?? null;
        $params['document_topic'] = $array["document_topic"] ?? null;
        $params['ideological_stream'] = $array["ideological_stream"] ?? null;
        $params['counted_theatre'] = $array["counted_theatre"] ?? null;
        $params['islamic_sect'] = $array["islamic_sect"] ?? null;
        $params['languages'] = $array["languages"] ?? null;
        $params['searchSavesearch'] = $array["searchSavesearch"] ?? null;
        $params['threat_rating'] = $array["threat_rating"] ?? null;
        $params['getDuplicate'] = ($array["getDuplicate"]  ?? null ==='true');
        $params['searchEmailFrom'] = $array["searchEmailFrom"] ?? null;
        $params['searchEmailTo'] = $array["searchEmailTo"] ?? null;
        $params['searchEmailCc'] = $array["searchEmailCc"] ?? null;
        $params['searchEmailRecipient'] = $array["searchEmailRecipient"]  ?? null;
        $params['file_format'] = $array["file_format"] ?? null;
        $params['searchGender'] = $array["searchGender"] ?? null;
        $params['searchEthnicity'] = $array["searchEthnicity"] ?? null;
        $params['searchNational'] = $array["searchNational"] ?? null;
        $params['searchConcept'] = $array["searchConcept"] ?? null;
        $params['searchRelation'] = $array["searchRelation"] ?? null;
        $params['subjectType'] = $array["subjectType"] ?? null;
        $params['subjectName'] = $array["subjectName"] ?? null;
        $params['objectType'] = $array["objectType"] ?? null;
        $params['objectName'] = $array["objectName"] ?? null;
        $params['relationType'] = $array["relationType"] ?? null;
        $params['ignoreSubject'] = $array["ignoreSubject"] ?? null;
    }
    return $params;
}

function isSearchParamsEmpty($p, $searchExtra, $getDuplicate) {
    if(empty($p) && empty($searchExtra) && $getDuplicate){
        return true;
    }
    if(empty($p)){
        return false;
    }
    if ($p["searchPlaces"] != "" || $p["searchOrganization"] != "" || $p["searchOther"] != "" || $p["searchEvents"] != "" || $p["searchDateMentioned"] != "" || $p["document_topic"] != "" ||
            $p["ideological_stream"] != "" || $p["counted_theatre"] != "" || $p["islamic_sect"] != "" || $p["languages"] != "" || !empty($searchExtra) ||
            $p["searchSavesearch"] != "" || $p["threat_rating"] != "" || $p["searchTags"] != "" || $p["searchNotTagged"] != "" || !$getDuplicate || $p["file_format"] != "" ||
            $p["searchEmailTo"] != "" || $p["searchEmailFrom"] != "" || $p["searchGender"] != "" || $p["searchEmailCc"] != "" || $p["searchEmailBcc"] != "" || $p["searchEmailRecipient"] != "" ||
            $p["searchEthnicity"] != "" || $p["searchNational"] != "" || $p["searchRelation"] != "") {
        return false;
    }
    return true;
}


function getSuffix($id, $p, $inResults, $lastSearchsid, $searchExtra, $getDuplicate) {
    $suffix = "";
    $tables = array('place', 'organization', 'events', 'DateMentioned', 'domains', 'ideologicalstreams', 'countedtheatres', 'islamicsects', 'languagess', 'searchSavesearchs',
        'threatratings', 'fileformats', 'fromfields', 'tofields', 'ccfields', 'bccfields', 'recipientfields', 'searchgenders', 'searchethnicitys', 'searchnationals', 'searchnationals',
        'searchrelations',
    );
    if(!empty($p)){
        $values = array($p["searchPlaces"], $p["searchOrganization"], $p["searchEvents"], $p["searchDateMentioned"], $p["document_topic"], $p["ideological_stream"],
            $p["counted_theatre"], $p["islamic_sect"], $p["languages"], $p["searchSavesearch"], $p["threat_rating"], $p["file_format"], $p["searchEmailFrom"],
            $p["searchEmailTo"], $p["searchEmailCc"], $p["searchEmailBcc"], $p["searchEmailRecipient"], $p["searchGender"], $p["searchEthnicity"], $p["searchNational"],
            $p["searchNational"], $p["searchRelation"],);
    }
    else{
        $values = [];
    }

    if(!empty($values)){
        for ($i = 0; $i < count($tables); $i++) {
            if ($values[$i] != "") {
                $suffix .= " AND (files.$id=$tables[$i].docId)";
            }
        }
    }
    if (!$getDuplicate) {
        $suffix .= " AND (files.$id!=duplicate.id)";
    }

    foreach ($searchExtra as $table) {
        $suffix .= " AND (files.$id=$table.docId)";
    }
    if (!empty($p["searchTags"])){
        $suffix .= " AND (files.$id=tags1.id)";
    }
    if (!empty($p["searchNotTagged"])) {
        $suffix .= " AND (files.$id=tags2.id)";
    }
    if ($inResults) {
        $suffix .= " AND files.id IN(SELECT docid FROM savedsearchid WHERE id=$lastSearchsid)";
    }
    return $suffix;
}

function getMaxFiles($mysqli, $isAdmin,$username) {
    if ($isAdmin) {
        $sql = "SELECT max(id) as m FROM files";
    } else {
        $sql = "SELECT max(userId) as m FROM files WHERE username='$username'";
    }
    $res = sqlQuery($mysqli, $sql);
    $row = $res->fetch_object();
    $max = $row->m;
    return $max;
}

function getTotalFiles($mysqli, $isAdmin,$username) {
    if ($isAdmin) {
        $sql = "SELECT count(id) as m FROM files";
    } else {
        $sql = "SELECT count(id) as m FROM files WHERE username='$username'";
    }
    $res = sqlQuery($mysqli, $sql);
    $row = $res->fetch_object();
    $max = $row->m;
    return $max;
}

function writeToFile($insert_row,$first_row_file,$stdout_file) {
    $inst_field = $insert_row["instances"];
    $instances_arr = is_array($inst_field) ? array_keys($inst_field) : null;
    $insert_row["instances"] = is_array($instances_arr) ? implode(", ", $instances_arr) : null;
    if ($first_row_file) {
        $first_keys = array_keys($insert_row);
        fputcsv($stdout_file, $first_keys);
    }
    fputcsv($stdout_file, $insert_row);
}

function getAuthor($arr_meta,$id){
    $ret = "";
    $info = "";
    if(array_key_exists("file_author", $arr_meta) && array_key_exists($id, $arr_meta["file_author"])){
        $ret .= $arr_meta["file_author"][$id]["name"];
        $info .= $arr_meta["file_author"][$id]["info"];
        
        //in case its twitter use we will add this as well
        if(array_key_exists("user_screen_name", $arr_meta) && !empty($arr_meta['user_screen_name'])){
            if(!empty($ret)){
                $ret .=",";
            }
            $ret .=  array_key_exists("user_id_str", $arr_meta) ? (array_key_exists($id, $arr_meta["user_id_str"]) ? $arr_meta["user_id_str"][$id] : null) : null;
        }
    }
    
    if(empty($ret)){
        return null;
    }
    
    return ["name" => $ret, "info" => $info];
}

function insertRow($mysqli,$username, $row,$arr_meta,$arr_duplicate,$searchFreeText,$arr_review,$doc2entities,$aggregated_anti,$aggregated_pro, $predictive_scores = null) {
    $id = $row->id;
    $e = explode("-domain-parameter",isset($row->domain)? $row->domain : "");
    $domain = $e[0];
    $author = getAuthor($arr_meta,$id);
//     $relatedDocs = getRelatedFiles($mysqli, $id);
    
    return array("rowNum" => intval($id),
        "docId" => intval($id),
        "timeStamp" => isset($row->timeStamp) ? $row->timeStamp : null,
        //"tooltip" => isset($tooltip)? $tooltip : null,
        "filename" => $row->filename,
        "author" => !empty($author) ? $author["name"] : "_",
        "author_info" => !empty($author) ? $author["info"] : "",   
        "languages" => array_key_exists("languages", $arr_meta) ? (array_key_exists($id, $arr_meta["languages"]) ? $arr_meta["languages"][$id] : null) : null,
        "counted_theatre" => array_key_exists("counted_theatre", $arr_meta) ? (array_key_exists($id, $arr_meta["counted_theatre"]) ? $arr_meta["counted_theatre"][$id] : null) : null,
        "document_topic" => array_key_exists("document_topic", $arr_meta) ? (array_key_exists($id, $arr_meta["document_topic"]) ? $arr_meta["document_topic"][$id] : null) : null,
        "islamic_sect" => array_key_exists("islamic_sect", $arr_meta) ? getDoubleArrayValue($arr_meta, "islamic_sect", $id) : null,
        "threat_rating" => array_key_exists("threat_rating", $arr_meta) ? getDoubleArrayValue($arr_meta, "threat_rating", $id) : null,
        "ideological_stream" => array_key_exists("ideological_stream", $arr_meta) ? getDoubleArrayValue($arr_meta, "ideological_stream", $id) : null,
        "file_type" => array_key_exists("file_type", $arr_meta) ? getDoubleArrayValue($arr_meta, "file_type", $id) : null,
        "file_format" => array_key_exists("file_format", $arr_meta) ? getDoubleArrayValue($arr_meta, "file_format", $id) : null,
        "reviewed" => array_key_exists($id, $arr_review) ? "Yes" : "No",
        "filetags" => "" . getDoubleArrayValue($arr_meta, "filetags", $id),
//         "relatedFiles" => $relatedDocs,
        "redacted" => ($row->redactedId == NULL) ? "No" : "Yes",
        "duplicatedDocId" => $arr_duplicate[$id] ?? null,
        "from_field" => getDoubleArrayValue($arr_meta, "from_field", $id),
        "to_field" => getDoubleArrayValue($arr_meta, "to_field", $id),
        "cc_field" => getDoubleArrayValue($arr_meta, "cc_field", $id),
        "bcc_field" => getDoubleArrayValue($arr_meta, "bcc_field", $id),
        "subject_field" => "" . getDoubleArrayValue($arr_meta, "subject_field", $id),
        "aggregated_pro" => $aggregated_pro,
        "aggregated_anti" => $aggregated_anti,
        "pro" => $row->pro,
        "anti" => $row->anti,
        "emotion" => "" . getDoubleArrayValue($arr_meta, "emotion", $id),
        "pro_emotion" => "" . getDoubleArrayValue($arr_meta, "pro_emotion", $id),
        "anti_emotion" => "" . getDoubleArrayValue($arr_meta, "anti_emotion", $id),
        "mood" => "" . getDoubleArrayValue($arr_meta, "mood", $id),
        "results" => isset($row->results) ? $row->results : null,
        "score" => isset($predictive_scores) ?  $predictive_scores[$id] : 0, // getPredictiveScore($mysqli, $username, $id, $score_id) : 0,
        "shortFilename" => $row->shortFilename,
        "origFolder" => $row->origFolder,
        "session" => isset($row->session) ? $row->session : null,
        "username" => $row->username,
        "type" => $row->type,
        "data" => isset($row->data) ? $row->data : null,
        "userId" => isset($row->userId) ? $row->userId : null,
        "domain" => $domain,
        "file_size" => intval($row->file_size),
        "batch_serial_id" => $row->batch_serial_id,
        "batch_id" => $row->batch_id,
        "file_last_modified_date" => $row->file_last_modified_date,
        "scan_time" => $row->scan_time,
        "line_count" => intval($row->line_count),
        "token_count" => intval($row->token_count),
        "batch_serial_id" => $row->batch_serial_id,
        "user_notes" => $row->user_notes,
        "emailid" => getDoubleArrayValue($arr_meta, "emailid", $id),
        "date_field" => getDoubleArrayValue($arr_meta, "date_field", $id),
        "date" => $row->date,
        "file_creation_date" => $row->file_creation_date,
        "file_last_accessed_date" => $row->file_last_accessed_date,
        "freeText" => isset($searchFreeText) ? $searchFreeText : null,
        "instances" => array_key_exists($id, $doc2entities) ? $doc2entities[$id] : null);
}


function getPredictiveScores($mysqli, $username, $score_id){
    $sql = "SELECT docid, score FROM savedsearchid WHERE id=$score_id";
    $res_sql = sqlQuery($mysqli, $sql);
    while ($data = $res_sql->fetch_assoc())
    {
        $results[$data["docid"]] = $data["score"];
    }
    return $results;
}

function getAntiPro($aggregated_anti,$aggregated_pro) {
    $antipro = "";
    if (strlen($aggregated_anti) > 1 && strlen($aggregated_pro) > 1) {
        $antipro = "<b>Anti</b> - " . $aggregated_anti . " <br> " . "<b>Pro</b> - " . $aggregated_pro;
    } elseif (strlen($aggregated_anti) > 1) {
        $antipro = "<b>Anti</b> - " . $aggregated_anti;
    } elseif (strlen($aggregated_pro) > 1) {
        $antipro .= "<b>Pro</b> - " . $aggregated_pro;
    } else {
        $antipro = "";
    }
    return $antipro;
}

function getIsRunning($mysqli, $name, $username){
    $ret = "";
    $sql_running = "SELECT isrunning from savedsearch WHERE username='$username' AND name='$name'";
    $res_sql = sqlQuery($mysqli, $sql_running);	
    $res = $res_sql->fetch_object();
    if($res){
        $ret = $res->isrunning;
    }
    return $ret;
}

function reset_is_running($mysqli, $id, $name, $clear = false){
    write_to_log("TRACE", "Reseting isrunning for savedsearch $id");
    $resetParams = $clear ? "isrunning=0, search_values = null, loc=null,per=null, org=null, parameters=null" : "isrunning=0";
    $sql_running = "UPDATE savedsearch SET $resetParams WHERE id=$id";
    $res_sql = sqlQuery($mysqli, $sql_running);	
    if (!$res_sql)
        write_to_log("ERROR", "problem with reseting isrunning");
  
}

/**
 * Updates the updateSavesearchParams after performing the search
 * @param unknown $mysqli
 * @param unknown $savedSearchName - the savedsearchname
 * @param unknown $parameters
 * @param unknown $username
 * @param unknown $searchString
 * @param unknown $search_per - person parameters - for overview
 * @param unknown $search_org - org params - for overview
 * @param unknown $search_loc - loc params - for overview
 */
function updateSavesearchParams_v2($mysqli, $savedSearchName, $parameters, $username, $searchString, $search_vals){
    $search_vals = json_encode($search_vals, true);
    $search_vals = $mysqli->real_escape_string($search_vals);
    
	$date = date('YmdHis');
	$ssid = getLastSavedSearchId($mysqli, $username, $savedSearchName);
	if(!empty($ssid)) {
		//update if is_running (if it's not null there)
	    $sql_search = "UPDATE savedsearch SET isrunning=1,search_values = '$search_vals', parameters= '$parameters' WHERE id=$ssid";
	} else {
		//insert if not exists
		$sql_search = "INSERT INTO savedsearch(parameters, label, date, isrunning, search_values, username, name) VALUE('$parameters', '$searchString', '$date', 1, '$search_vals', '$username', '$savedSearchName')" ;
	}
	
	$res = sqlQuery($mysqli,$sql_search);
	
}

function updateSavesearchParams($mysqli, $savedSearchName, $parameters, $username, $searchString, $search_per, $search_org, $search_loc){
    $date = date('YmdHis');
    $ssid = getLastSavedSearchId($mysqli, $username, $savedSearchName);
    if(!empty($ssid)) {
        //update if is_running (if it's not null there)
        $sql_search = "UPDATE savedsearch SET isrunning=1,per = '$search_per', loc = '$search_loc', org = '$search_org' WHERE id=$ssid";
    } else {
        //insert if not exists
        $sql_search = "INSERT INTO savedsearch(parameters, label, date, isrunning, per, loc, org, username, name) VALUE('$parameters', '$searchString', '$date', 1, '$search_per', '$search_loc', '$search_org', '$username', '$savedSearchName')" ;
    }
    $res = sqlQuery($mysqli,$sql_search);
    
}

function getDoubleArrayValue(&$array, $key1, $key2)
{
	$val = array_key_exists($key1, $array) && array_key_exists($key2, $array[$key1])? $array[$key1][$key2] : null;
	//write_to_log("TRACE",  "getDoubleArrayValue key1:$key1,key2:$key2,val:$val");
	return $val;
}

function or_sql($search, $table_name, $as){
	$s = explode("^", $search);
	$sql .= "(SELECT DISTINCT docId FROM 
			$table_name WHERE";
	$sql .= " $table_name.name=LOWER('";
	$first = true;
	foreach ($s as $v){
		$v = trim($v);
		if ($v != ""){	
			if ($first){																	
				$sql .= $v . "') OR $table_name.orgText=LOWER('$v')";
				$first = false;
			}
			else 
				$sql .= " OR $table_name.name=LOWER('" . $v . "') OR $table_name.orgText=LOWER('$v')";						
		}
	}
	if (!getUserPermission(CAN_SHOW_ALL_USER))	
		$sql .= " AND $table_name.username='$username') AS $as";
	else 
		$sql .= ") AS $as";
	return $sql;
}	

function and_sql($search, $table_name, $as){
	$s = explode("&", $search);				
	$sql .= "(SELECT DISTINCT docId FROM "; 													
	$first = true;
	$i = 1;
	foreach ($s as $v){
		$v = trim($v);
		if ($v != ""){																	
			$sql .= "(SELECT DISTINCT docId AS P_id$i FROM  $table_name WHERE ($table_name.name=LOWER('$v') OR $table_name.orgText=LOWER('$v'))"; 
			if (!getUserPermission(CAN_SHOW_ALL_USER))
				$sql .= " AND $table_name.username='$username'";
			$sql .= ") AS P$i,";
			$i++;																																					
		}
	}
	$sql .= "$table_name 
			WHERE ";
	$first = true;
	$i--;
	while ($i > 0){
		if ($first){ 
			$sql .= "$table_name.docId = P$i.P_id$i";
			$first = false;
		}else{
			$sql .= " AND $table_name.docId = P$i.P_id$i";
		}
		$i--;
	}						
	$sql .= ") AS $as";
	return $sql;
}

function single_sql($mysqli, $search, $table_name, $as){
    $isOrgText = getSystemSettingsProp($mysqli, "isOrgText");
    //check org text
    if($isOrgText && $table_name != "organizational_identity" &&
	   $table_name != "person_object" &&
	   $table_name != "place_object"){
		$sql = "SELECT NAME FROM event WHERE $table_name.orgText=LOWER('$search') LIMIT 1";		
		$res = sqlQuery($mysqli, $sql);
		if ($res){
			$row = $res->fetch_object();
			$n = $row->NAME;
			if (strlen($n) > 1)
				$name = $n;
			else 
				$name = $search;
		}
	}else 	
		$name = $search;
	
	$s = "";
	if ($table_name == "organizational_identity"){
		$s = "OR $table_name.clean_name=LOWER('$search')";
	}
	$sql = "(SELECT DISTINCT docId FROM 
			$table_name WHERE";
		$sql .= " $table_name.name=LOWER('";					
		$sql .= $name . "') OR $table_name.orgText=LOWER('$search') $s) AS $as";
	return $sql;
}

function currencysql($mysqli, $search){
    $search_arr = explode('^', $search);
    $operator = $search["operator"];
    $amount = $search["sum"] ?? null;
    $from_amount = $search["from_sum"] ?? null;
    $to_amount = $search["to_sum"] ?? null;
    
    if($operator == "<>"){
        $sql = "SELECT docid FROM currency WHERE NAME+0  >$from_amount AND NAME+0 < $to_amount";    
    }
    else{
        $sql = "SELECT docid FROM currency WHERE NAME+0  $operator $amount";      
    }
    return $sql;
}

function slotDatasql($mysqli, $arr){
    global $entitiesTypeToTable, $slotsHirarchy;
   
    $text = $arr['text'];
    $concept = $arr['concept'];
    $regex = $arr['regex'];
    
    if(strtolower($concept) == "any"){
        $col = $arr['col'];
        $col = strtolower(str_replace('-', '_', $col));
        $cols_str = empty($text) ? " $col <> '' " : (empty($regex) ? "$col=\"$text\"" : "$col LIKE (\"%$text%\")"); 
    }
    else{
        $cols = $slotsHirarchy[$arr["concept"]][$arr["col"]];//$arr['col'];
        foreach($cols as $col){
            $col = strtolower(str_replace('-', '_', $col));
            if(TablesInformation::isColumnExists($mysqli, 'slotdata', $col)){
                $col_val_sql =  empty($text) ? " $col <> ''  " : (empty($regex) ? "$col=\"$text\"" : "$col LIKE (\"%$text%\")");
                $cols_arr[] = $col_val_sql;
            }
        }
        $cols_str = implode(' OR ', $cols_arr);
    }
    
    //check if its entity caption name - convert it to table name
    if(array_key_exists(strtolower($concept), $entitiesTypeToTable)){
        $concept = $entitiesTypeToTable[strtolower($concept)];
    }
    $concepts = [$concept];
    $sql = "SELECT DISTINCT sub_concept_name FROM `sub_tables` WHERE concept_name ='".ucfirst(str_replace('_', '-', $concept))."'";
    $res = sqlQuery($mysqli, $sql);
    if(!empty($res)){
        while( ($row = $res->fetch_object()) != null){
            $concepts[] = strtolower(str_replace('-', '_', $row->sub_concept_name));
        }
    }
    $concepts_str = implode("','", $concepts);
    $key_cond = " conceptName IN ('$concepts_str')";
    
    $concept_sql = empty($concept) || strtolower($concept) == 'any' ? " TRUE " : $key_cond; 
    
//     $col_val_sql = empty($regex) ? "$col=\"$text\"" : "$col LIKE (\"$text\")";
    $sql = "SELECT docid FROM slotdata WHERE $cols_str AND $concept_sql";
    return $sql;
}

function bankAccountsql($mysqli, $search){
    $type = $search["type"];
    $country = $search["country"];
    $number_arr = [$search["number"] ?? ""];
    $values = $search["values"] ?? [];
    foreach($values as $val){
        $number_arr[] = $val['label'];
    }
    $number_str = implode("','", array_filter($number_arr));
    $country_sql = !empty($country) ? " place_referred_to = \"$country\"" : " TRUE ";

    $number_sql = !empty($number_str) ? " number_value  IN ('$number_str')" : " TRUE ";
    
    $sql = "SELECT docid FROM slotdata WHERE $number_sql AND $country_sql AND conceptName=\"$type\"";
    return $sql;
}

function phonesql($mysqli, $search){
    $country = $search["country"];
    $number_arr = [$search["number"] ?? ""];
    $number = $search["number"];
    $values = $search["values"] ?? [];
    
    foreach($values as $val){
        $number_arr[] = $val['label'];
    }
    $number_str = implode("','", array_filter($number_arr));
    
    $cond_sql = !empty($number_str) ? " CONCAT_WS('-',phone_country_code,phone_area_code,phone_number)  IN ('$number_str')" : " TRUE ";

    $sql = "SELECT docid FROM slotdata WHERE $cond_sql AND conceptName='telephone_number'";
    return $sql;
}

function addresssql($mysqli, $search){
    $country = !empty($search["country"]) ? " country = \"$search[country]\"" : " TRUE ";
    $area = !empty($search["state_or_province"]) ? " country = \"$search[state_or_province]\"" : " TRUE ";
    $city = !empty($search["city"]) ? " city = \"$search[city]\"" : " TRUE ";
    $street = !empty($search["street"]) ? " street = \"$search[street]\"" : " TRUE ";
    $house_number = !empty($search["house_number"]) ? " house_number = \"$search[house_number]\"" : " TRUE ";
    $postal_code = !empty($search["postal_code"]) ? " postal_code = \"$search[postal_code]\"" : " TRUE ";
    $values = $search["values"] ?? [];
    
    foreach($values as $val){
        $address_arr[] = $val['label'];
    }
    $address_str = implode("','", array_filter($address_arr));
    
    $cond_sql = !empty($address_str) ? " CONCAT_WS(', ',house_number,street,city,state_or_province,postBox,country) IN ('$address_str')" : " TRUE " ;
    
    
    $sql = "SELECT docid FROM slotdata WHERE   $cond_sql AND conceptName='full_address'";
    return $sql;
}

function scaleSql($mysqli, $table, $search, $col){
    $min = $search["min"];
    $max = $search["max"];
    
    $sql = "SELECT docid FROM $table WHERE $col > $min AND $col < $max ";
    return $sql;
}

function single_sql_special_tables($mysqli, $search, $table_name, $as=null){
    $table_name = strtolower($table_name); //make sure the table is lower case (linux)
    $search = $search=='*' ? 'all' : $search;
    $sql = "";
	if ($as != null){
		$as = "AS $as";
	}
	$s = explode("^", $search);
	$none = false;
	if (sizeof($s) > 1){
		//or
		$first = true;
		foreach ($s as $v){
			$v = trim($v);
			$v = $mysqli->real_escape_string($v);
			if ($first){
				$first = false;
				if ($v == "None")
					$none = true;
				$where = "$table_name.value=LOWER('$v')";	
			}else
			 	$where .= " OR $table_name.value=LOWER('$v')";				 	
		}	
		if ($none)
			$sql .= "(SELECT id AS docId FROM files WHERE (id NOT IN (SELECT DISTINCT docId FROM $table_name)) OR id IN(SELECT DISTINCT docId FROM $table_name WHERE $where)) $as";			
		else					
			$sql .= "(SELECT DISTINCT docId FROM $table_name WHERE $where) $as";
	}else{ 			
		$s = explode("&", $search);
		if (sizeof($s) > 1){
			//AND statment
			$first = true;
			$i = 0;
			foreach ($s as $v){
				$v = trim($v);
				if ($first){
					$first = false;
					$from = "(SELECT docId AS id$i FROM $table_name WHERE ";
					$from .= "$table_name.value=LOWER('$v')) as t$i";	
				}else{
				 	$from .= ",(SELECT docId AS id$i FROM $table_name WHERE ";
					$from .= "$table_name.value=LOWER('$v')) as t$i";
				}
				$i++;
			}
			$j = 1;		
			while ($j < $i){
				$sql .= "(SELECT DISTINCT id0 as docId FROM $from WHERE ";
				if ($j > 1)
					$sql .= ",";
				$sql .= "t0.id0=t$j.id$j";			
				$j++;
			}
			$sql .= ") $as";	
			}else{
				//single statment
				//check if it's none - 
			    if (strtolower($search) == "none"){
					$sql .= "(SELECT id AS docId FROM files WHERE id NOT IN (SELECT DISTINCT docId FROM $table_name)) $as";
			    }
			    elseif(empty($search)  || strtolower($search) == "all"){
			        $sql .= "(SELECT id AS docId FROM files WHERE id  IN (SELECT DISTINCT docId FROM $table_name)) $as";
			        
			    }
			    else{
					$sql .= "(SELECT DISTINCT docId FROM $table_name WHERE ($table_name.value=LOWER('$search'))) $as";
			    }
			 }
			
	}	
	return $sql;
}			

function multi_sql_special_tables($search, $table_name, $as=null){	
	global $aggregationArr;
	if ($as != null){
		$as = "AS $as";
	}
	$sql .= "(SELECT DISTINCT docId FROM 
			$table_name WHERE (";
	$first = true;			
	foreach ($aggregationArr[$table_name][$search] as $v){
		if ($first){
			$first = false;
		}else{
			$sql .= " OR ";
		}
		$sql .= "($table_name.value=LOWER('";
		$sql .= $v . "'))";
	}						
	$sql .= ")) $as";		
	return $sql;
}

function range_sql_special_tables($search, $table_name, $as=null){
	global $aggregationArr;
	if ($as != null){
		$as = "AS $as";
	}
	$sql .= "(SELECT DISTINCT docId FROM 
			$table_name WHERE $table_name.value $search";
	$sql .= ") $as";
							
	return $sql;
}

function non_sql_special_tables($table_name, $as=null){
	if ($as != null){
		$as = "AS $as";
	}
	return "(SELECT DISTINCT files.id as docId FROM files WHERE files.id NOT IN (SELECT DISTINCT docId FROM $table_name)) $as";	
}
				
			
function getDateSql($searchDate,$t){
    //if send just one date (without _) we can choose all files within that date
    if(strpos($searchDate, '_') == false){
        $date = "$t.date BETWEEN '$searchDate 00:00:00' AND '$searchDate 23:59:59'";   
        return $date;
    }
	$e = explode("_", $searchDate);
	$dayFrom = $e[0];				
	$monFrom = $e[1];
	$yearFrom = $e[2];
	$dayTo = $e[3];
	$monTo = $e[4];
	$yearTo = $e[5];	
	$date = "$t.date > '00-00-00'";			
	
	if (is_numeric($dayFrom) || is_numeric($monFrom) || is_numeric($yearFrom)){
		if (!is_numeric($dayFrom))
			$dayFrom = "00";
		if (!is_numeric($monFrom))
			$monFrom = "00";
		if (!is_numeric($yearFrom))
			$yearFrom = "00";
		if (is_numeric($dayTo) || is_numeric($monTo) || is_numeric($yearTo)){
			if (!is_numeric($dayTo))
				$dayTo = "00";
			if (!is_numeric($monTo))
				$monTo = "00";
			if (!is_numeric($yearTo))
				$yearTo = "00";
			$date = "$t.date BETWEEN '$yearFrom-$monFrom-$dayFrom' AND '$yearTo-$monTo-$dayTo 23:59:59'";
		}else{
			$date = "$t.date >= '$yearFrom-$monFrom-$dayFrom'";
		}
	}elseif (is_numeric($dayTo) || is_numeric($monTo) || is_numeric($yearTo)){
		if (!is_numeric($dayTo))
				$dayTo = "00";
			if (!is_numeric($monTo))
				$monTo = "00";
			if (!is_numeric($yearTo))
				$yearTo = "00";
			$date = "$t.date <= '$yearTo-$monTo-$dayTo 23:59:59:99'";
	}
	return $date;
}

function entities2CaptionLists($mysqli, &$entitiesToSearch)
{
	$table2CaptionsArray = [];
	if (empty($entitiesToSearch))
		return $table2CaptionsArray;
	
	foreach ($entitiesToSearch as $ent2search)
	{
		//TODO check if entity exsist on entitiesmanage table		
		$table2CaptionsArrayPartial = getSearchCaptions($mysqli, $ent2search["text"], $ent2search["type"], $ent2search["condition"], $ent2search["skipAggregation"]);
		if (empty($table2CaptionsArrayPartial))
			return [];
		
		foreach ($table2CaptionsArrayPartial as $agg2table2Captions)
		{
			$sqlUnions = [];
			foreach ($agg2table2Captions as $agg => $table2Captions)
			foreach ($table2Captions as $table => $captions)
			foreach (parseAndReplaceExternalConditions($mysqli, $ent2search["condition"], $table, $table) AS $condObj)
			{
				$sqlUnions[] = "SELECT $table.docId from $table {$condObj->table} WHERE {$condObj->wherePhrase} $table.name IN ('" . join("', '", $captions) . "')";
			}
			$sql = join(" UNION ", $sqlUnions);
			$searchSqls[] = ["docId" => "docId", "Query" => $sql];
			array_push($table2CaptionsArray, ["search" => $ent2search, "tableCaptions" => $agg2table2Captions]);
		}
	}
	dumpDbg("table2CaptionsArray", $table2CaptionsArray);
	
	//run over entities to search with sql results
	$entitiesToSearch = $searchSqls;
	return $table2CaptionsArray;
}

function populateDoc2Entities(&$doc2entities, $mysqli, $table2CaptionsArray, $arr_id, $searchOther)
{
	$sqlsUnions = [];
	foreach ($table2CaptionsArray as $elements)
	{
		$condJson = array_key_exists("condition", $elements["search"])? json_encode($elements["search"]["condition"]) : "";
		foreach ($elements["tableCaptions"] as $agg => $table2Captions)
		foreach ($table2Captions as $table => $captions)
		//foreach (parseAndReplaceExternalConditions($mysqli, $elements["search"]["condition"], $table, $table) AS $condObj)
		{
			$sqlsUnions[$condJson][] = "SELECT docId, docKey FROM $table WHERE name IN('" . join("', '", $captions) . "') AND docId IN(" . join(",", $arr_id) . ")";
		}
	}
	//special treatment for concept search
	if (!empty($searchOther))
	{
		$e = explode("=", $searchOther);
		$sqlsUnions[""][] =buildSearchConceptSql($mysqli, $e[0], $e[1], "docId, docKey", "docId IN(" . join(",", $arr_id) . ")");
	}
	foreach ($sqlsUnions as $condJson => $sqlUnions)
	{
		$cond = $condJson? json_decode($condJson) : null;
	$sql = join(" UNION ", $sqlUnions);
	if (!empty($sql))
	{		
		$res = sqlQuery($mysqli, $sql);
		while ($res && ($row = $res->fetch_object()))
		{
			if ($cond)
				$doc2entities[$row->docId][$row->docKey] = $cond;
			else
			$doc2entities[$row->docId][$row->docKey] = true;
		}
	}
	}
}

function populateDoc2EntitiesNew(&$doc2entities, $mysqli, $table2CaptionsArray, $arr_id, $table2CaptionsArrayNew)
{
	$sqlsUnions = [];
	foreach ((array)$table2CaptionsArrayNew as $e){
		foreach ($e as $elements){
			$condJson = array_key_exists("condition", $elements["search"])? json_encode($elements["search"]["condition"]) : "";
			foreach ($elements["tableCaptions"] as $agg => $table2Captions)
			foreach ($table2Captions as $table => $captions)			
			{
			    if(TablesInformation::isColumnExists($mysqli, $table, "docKey")){
			        $cols = "docId, docKey";
			        $name_col = "name";     
			    }
			    else{
			        $cols = "docId";		
			        $name_col = "value";   
			    }
				$sqlsUnions[$condJson][] = "SELECT $cols FROM $table WHERE $name_col IN('" . join("', '", $captions) . "') AND docId IN(" . join(",", $arr_id) . ")";
			}
		}
	}
	
	
	if(!empty($table2CaptionsArray)){
    	foreach ($table2CaptionsArray as $elements)
    	{		
    		foreach ($elements as $table => $captions)		
    		{
    		    if(TablesInformation::isColumnExists($mysqli, $table, "docKey")){
    		        $cols = "docId, docKey";
    		        $name_col = "name";
    		    }
    		    else{
    		        $cols = "docId";
    		        $name_col = "value";
    		    }
    			$sqlsUnions[][] = "SELECT $cols FROM $table WHERE $name_col IN($captions) AND docId IN(" . join(",", $arr_id) . ")";
    		}
    	}
	}
	
	foreach ($sqlsUnions as $condJson => $sqlUnions)
	{
	    
		$cond = $condJson? json_decode($condJson) : null;
		$sql = join(" UNION ", $sqlUnions);
		if (!empty($sql))
		{		
		    
			$res = sqlQuery($mysqli, $sql);
			while ($res && ($row = $res->fetch_object()))
			{
			    if(property_exists($row, 'docKey')){
    				if ($cond){
    					$doc2entities[$row->docId][$row->docKey] = $cond;
    				}
    				else{
    				    $doc2entities[$row->docId][$row->docKey] = true;
    				}
			    }
			}
		}
	}
}


function handle_free_text($mysqli, $isAdmin, $username, $searchFreeText, $limitResult, $textDataBase, $searchString, $searchSqls, $isRegex, $getDuplicate, $searchId){
    global $isMemSQL;
    $useSolr = getSystemSettingsProp($mysqli, "useSolr");
    
    $freeTextNoQuotes = trim($searchFreeText, '"');
    if ($searchFreeText != $freeTextNoQuotes)
        $againstSect = "'\"" . $mysqli->real_escape_string($freeTextNoQuotes) . "\"' IN BOOLEAN MODE";
        else
            $againstSect = "'" . $mysqli->real_escape_string($searchFreeText) . "'";
            $sql = "SELECT id FROM $textDataBase.documents WHERE MATCH (documents.text) AGAINST ($againstSect)";
            if (!$isAdmin)
                $sql .= " AND username='$username'";
                write_to_log("TRACE", $sql);
                if($useSolr)
                {
                    global $SOLR_SERVER_HOSTNAME, $SOLR_SERVER_PORT, $SOLR_COLLECTION, $SOLR_TXT_FIELD;
                    $free_text_string = urlencode($searchFreeText);
                    $solrQuery = "http://$SOLR_SERVER_HOSTNAME:$SOLR_SERVER_PORT/solr/$SOLR_COLLECTION/select?wt=json&indent=true&fl=id&start=0&rows=$limitResult&q=$free_text_string";
                    if (!$isAdmin)
                        $solrQuery .= "&fq=username:$username";
                        $solrJson = file_get_contents($solrQuery);
                        write_to_log("TRACE", "solrQuery:$solrQuery");
                        $solrResponse = json_decode($solrJson);
                        write_to_log("TRACE", "solrResponse:$solrJson");
                        
                        if ($solrResponse && $solrResponse->response && $solrResponse->response->docs)
                            foreach ($solrResponse->response->docs as $doc)
                            {
                                $arr_text_id[ $doc->id ] = $doc->id;
                            }
                        //check duplicates in free text
                        $getDuplicate = $getDuplicate === 'true'? true: false;           
                        if(!$getDuplicate){
                            $sql = "SELECT  id FROM duplicate_files WHERE duplicatedDocId IS NOT NULL";
                            
                            $res = sqlQuery($mysqli,$sql);
                            while ($row = $res->fetch_object()){
                                $dup_ids[] = $row->id;
                            }
                            $arr_text_id = array_diff($arr_text_id, $dup_ids ?? []);
                            
                        }
                        
                        if (empty($arr_text_id)){
                            $returnEmpty = true;
                        }
                        else {
                            $keepSolrOrder = true; //this will make the mysql results order by the solr score
                            $joined_arr_text_id = join(",", $arr_text_id);
                            $order_by_id = "FIELD(id, " . $joined_arr_text_id . ")";
                            //avoid memsql error: cannot pass more than 100 arguments to a function
                            if ($isMemSQL) {
                                $order_by_id = "COALESCE(NULLIF(FIELD(id, " . join(",", array_slice($arr_text_id, 0, 99)) . "), 0), id+100)";
                            }
                            
                            //add SolR results at the beginning of the sqls order by SolR results (maybe we could make results intersection to be ordered by SolR)
                            array_unshift($searchSqls, [
                                "docId" => "id",
                                "Query" => "SELECT DISTINCT files.id FROM files WHERE id IN ($joined_arr_text_id) ORDER BY $order_by_id"
                            ]);
                        }
                }
                elseif ($oneSearchQuery && !$separateTextQuery)
                    {
                        $searchSqls[] = array("docId" => "id", "Query" => $sql);
                    }
                else
                {
                    $mysqli_t = sqlCreateDBConnection($textDataBase);
                    write_to_log("PERMORMENCE", "search freetext start sql - $sql time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
                    $res = $mysqli_t->query($sql);
                    write_to_log("PERMORMENCE", "search freetext end sql time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
                    if (!empty($res)){
                        while ($row = $res->fetch_object()) {
                            if (!empty($row->id)){
                                $arr_text_id[$row->id] = $row->id;
                            }elseif (!empty($row->userId)){
                                $arr_text_id[$row->userId] = $row->userId;
                            }
                        }
                        write_to_log("PERMORMENCE", "search freetext end insert arr_id time -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
                    }
                    if (empty($arr_id))
                        $arr_id=$arr_text_id;
                        else
                            $arr_id = array_intersect_key($arr_text_id, $arr_id);
                            
                            if (empty($arr_id))
                                $returnEmpty = true;
                                $mysqli_t->close();
                }
                if (strlen($searchString) > 0)
                    $searchString .= ",";
                    $searchString .= " Free Text-" . $searchFreeText;

            return array("arr_text_id"=>$arr_text_id ?? null, "searchSqls"=>$searchSqls ?? null, "searchString"=>$searchString ?? null, "solrQUery"=>$solrQuery ?? null);
}

function getSearchString($mysqli, $table, $searchValue, &$table2CaptionsArray, $username, $unionPhrase,$isAdmin){
    
    global $dropdownArrMaxMapping, $dropdownArrCaptionMapping;
    $sql = array();
	if ($table == "searchOther"){
	
	        
        
	    if(is_array($searchValue)){
	        foreach($searchValue as $val){
	            $e = explode("=", $val);
	            $sql[] .= buildSearchConceptSql($mysqli, $e[0], $e[1], "docId");
	        }
	        $sql = implode(" UNION ALL ", $sql);
	    }
	    else{
		  $e = explode("=", $searchValue);	  
		  $table = $e[0];
		  $searchValue = $mysqli->real_escape_string($e[1]);	  
		  
		  //sepcial case for maxMapping or mapping tables
		  if(array_key_exists($table, $dropdownArrMaxMapping) || array_key_exists($table, $dropdownArrCaptionMapping)){
		      $caption_table = $dropdownArrMaxMapping[$table] ?? $dropdownArrCaptionMapping[$table];
		      
		      $sql = "SELECT docid FROM $caption_table WHERE value=\"$searchValue\"";
		      
		  }
		  else{
		      $sql = "(" . buildSearchConceptSql($mysqli, $table, $searchValue, "docId") . ")";
		  }
	    }
		return $sql;									
	}
	
	if ($table == "document_topic" || $table == "ideological_stream" || $table == "counted_theatre"
		|| $table == "islamic_sect" || $table == "languages" || $table == "threat_rating" || $table == "file_format"){
		$sql = single_sql_special_tables($mysqli, $searchValue, $table);
		return $sql;
	}
	
	if ($table == "searchGender" || $table == "searchEthnicity" || $table == "searchNational"){
		if ($table == "searchGender"){
			$table = "gender";
		}
		if ($table == "searchNational"){
			$table = "national_identity";
		}
		if ($table == "searchEthnicity"){
			$table = "ethnicity";
		}		
		$sql = "(SELECT DISTINCT docId FROM slotdata WHERE $table='$searchValue')";
		return $sql;
	}
	
	if ($table == "searchRelation"){
	    
	    if(is_array($searchValue) && (empty($searchValue[0]) || !is_array($searchValue[0]))){
	        $searchValue = array($searchValue);
	    }
	    $sqls = array();
	    foreach($searchValue as $val){
	        
	        
	        if (!empty($val['relationType']) && ($val["relationType"] == "coRefs" || $val["relationType"][0] == "coRefs")){
    		    if(empty($val["subjectType"]) || empty($val["objectType"])){
    		        $arr = getMultiTopCoRefs($mysqli, null, null, 1000, null, $username, $isAdmin, $val["subjectName"], $val["objectName"], true, true, true,null, null);
    		          foreach($arr as $sqlArr){
    		              $sqls[] = $sqlArr["data"];
    		          }
    		    }
    		    else{
    		        $sqls[] = getTopCoRefs($mysqli, $val["subjectType"], $val["objectType"], 1000, null, $username, $isAdmin, $val["subjectName"], $val["objectName"], true, true, true);
    		    }
    		    
//     		    if (!array_key_exists("objectType", $val) || !array_key_exists("subjectType", $val))
//     			{
//     				write_to_log("ERROR", "search coRefs: empty object type or subject type");
//     				die("Error");
//     			}
//     			else{
//     			 $sql = getTopCoRefs($mysqli, $val["subjectType"], $val["objectType"], 1000, null, $username, $isAdmin, $val["subjectName"], $val["objectName"], true, true, true);
//     			}
    		}
    		else{
    		    
    		    $sql = getTopRelation($mysqli, $val["relationType"] ?? null, $val["subjectType"] ?? null, $val["objectType"] ?? null, null, null, $val["subjectName"] ?? null, $val["objectName"] ?? null, false, $val["ignoreSubject"] ?? null, $isAdmin,$username,true,null);
    		    $sqls[] = $sql;
 		    
    		}
	}
	return implode("  UNION ALL ", $sqls);
	}
	
	if ($table == "ignoreDuplicates"){		
		$sql = "(SELECT id FROM (
                SELECT id,GROUP_CONCAT(duplicatedDocId) AS dup FROM duplicate_files GROUP BY id
                ) AS t
                WHERE dup IS NULL)";			
		return $sql;
	}			
	
	if ($table == "searchEmailFrom" || $table == "searchEmailTo" || $table == "searchEmailCc"
	    || $table == "searchEmailBcc" || $table == "searchEmailRecipient"){ 	
		$emailArr = array("searchEmailFrom" => "from_field",
						  "searchEmailTo" => "to_field",
		                  "searchEmailCc" => "cc_field",
		                  "searchEmailBcc" => "bcc_field",
		                  "searchEmailRecipient" => "recipient_field");
	    $fieldName = $emailArr[$table];				
				
		//search as person
		//$table2CaptionsArrayPartial = getSearchCaptions($mysqli, $searchValue, "person,email", NULL, false);
		
		$pArr = whereEmailSql($table,$searchValue,$mysqli,true);
		
		/*$searchValueArray = [$searchValue];
		foreach ($table2CaptionsArrayPartial as $agg2table2Captions)
		foreach ($agg2table2Captions as $agg => $table2Captions)
		foreach ($table2Captions as $table => $captions)
		foreach ($captions as $caption)
			$searchValueArray[] = $caption;
		
		$searchValueStr = join(" ^ ", $searchValueArray);*/
		$searchValueStr = join(" ^ ", $pArr);
		
		$emailSqlPrefix = "";
		$emailSqlSuffix = "";
		$emailFields = [$fieldName];
		if ($fieldName == "recipient_field")
		{
			//$emailSqlPrefix = " (SELECT * FROM ";
			//$emailSqlSuffix = ") \r\n ";
			$emailFields = ["to_field", "cc_field", "bcc_field"];
		}
		$emailUnions = [];	
		foreach ($emailFields as $field)
		{
			if ($fieldName == "recipient_field")
				$emailTableAlias = str_replace("_", "", $field) . "s"; //from_field -> fromfields, to_field -> tofields...
			else 
				$emailTableAlias = null;
			$emailUnions[] = single_sql_special_tables($mysqli, $searchValueStr, $field, null);
		}
		//$sql = $emailSqlPrefix . join ($unionPhrase, $emailUnions) . $emailSqlSuffix;
		$sql = join ($unionPhrase, $emailUnions);
// 		$table2CaptionsArray = $table2CaptionsArrayPartial; 
		return $sql;		
	}
	
	if ($table == "searchSavesearch"){	
	   
		$v = $searchValue;

	    $info = getSavedSearch($mysqli, false, $v);
	    if(!empty($info)){
	        $res = getDetailsForSmartSearch_v2($mysqli, $username, $info[0]["parameters"]);
    	    $results = "";
    	    if(!empty($res["searchParameters"])){
        	    foreach($res["searchParameters"] as $index=>$value){
        	        $type = key($value);
        	        if($type == "regular"){
        	        
        	            $value_arr =!is_array($value[$type]) ? [$value[$type]] : $value[$type];
        	            $or_sqls_str = "";
        	            $or_sqls = [];
        	            $first = true;
        	            
        	            foreach($value_arr as $table=>$searchValue){
        	                $sqlret = getSearchString($mysqli, $table, $searchValue, $table2CaptionsArray, $username, $unionPhrase,$isAdmin);
        	               
        	                
        	                $sql = "$sqlret";
        	                if(empty(trim($sqlret))){
        	                    continue;
        	                }
        	                if ($first){
        	                    $first = false;
        	                    $or_sqls_str .= " $sql ";
        	                    continue;
        	                }
        	                $or_sqls_str .= "UNION $sql";
        	            }
        	            if(empty(trim($or_sqls_str))){
        	                continue;
        	            }
        	            $sqls[] = $or_sqls_str;
        	        }
        	        else{
        	            $or_sqls_str = "";
        	            $first = true;
        	            foreach($value[$type] as $val){
        	                if(empty(trim($val))){
        	                    continue;
        	                }
        	                $sql = " ($val) ";
        	                if ($first){
        	                    $first = false;
        	                    $or_sqls_str .= " $sql";  
        	                   continue;
        	                }
        	                $or_sqls_str .= "UNION $sql";
        	            }
        	            if(empty(trim($or_sqls_str))){
        	                continue;
        	            }
        	            $sqls[] = $or_sqls_str;
        	        }
        	    }
        	    
        	    $results = count($sqls)==1 ? $sqls[0] : implode(") AND (files.id IN ", $sqls);
    	    }
    	    else{
    	        /**
    	         * in case we have SOLR search as Last search - try to get the solrQuery
    	         * from search_values in savedsearhc table
    	         * TODO: maybe it would be too long query - check in bigger DB
    	         */
    	        $query = "SELECT search_values FROM savedsearch WHERE username='$username' AND name='Last search'";
    	        $res = sqlQuery($mysqli, $query);
    	        if($res){
    	            $obj = $res->fetch_object();
    	            if($obj){
        	            $solrQuery = $obj->search_values;
        	            if(!empty($solrQuery)){
        	                $solrQuery = trim($solrQuery,'"');
            	            $solrJson = file_get_contents($solrQuery);
            	            $solrResponse = json_decode($solrJson);	           
            	            if ($solrResponse && $solrResponse->response && 
            	                $solrResponse->response->docs){
            	                foreach ($solrResponse->response->docs as $doc)
            	                {
            	                    $arr_text_id[] = $doc->id;
            	                }
            	            }
    	            	}
    	            }
    	        }
    	        
    	        if(!empty($arr_text_id)){
    	           $results = implode(',', $arr_text_id);
    	        }
    	    }
	    }
		return $results;
// 	return implode(") AND (files.id IN ", $sqls);		
	}

	if ($table == "searchSavesearchWithoutRefresh"){	
		$searchSavesearch = $searchValue;
		$s = explode("^", $searchSavesearch);
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){
				if ($first){
					$first = false;
					$where = "savedsearch.name='$v'";	
				}else
				 	$where .= " OR savedsearch.name='$v'";	
			}						
		}else{ 			
			$s = explode("&", $searchSavesearch);
			if (sizeof($s) > 1){
				//AND statment
				$first = true;
				foreach ($s as $v){
					if ($first){
						$first = false;
						$where = "savedsearch.name='$v'";	
					}else
					 	$where .= " AND savedsearch.name='$v'";	
				}	
			}else{
				//single statment
				$where = "savedsearch.name='$searchSavesearch'";
			}
		}
		$sql = "(SELECT DISTINCT docId FROM 
			savedsearchid,savedsearch WHERE ($where ";		
		$sql .=") AND  savedsearchid.id=savedsearch.id)";
		
// 		$sql .=") AND savedsearch.username = '$username' AND savedsearchid.id=savedsearch.id)";
		return $sql;		
	}

	if ($table == "searchTags")
	{		
		$searchTags = $searchValue;
		$s = explode("^", $searchTags);
		$none = false;
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){				
				if(strpos($v,NOT_REVIEWED) !== FALSE){
					$w = "(files.id NOT IN (SELECT DISTINCT(filetags.docId) FROM filetags WHERE filetags.name='Reviewed'))";					
				}else{
					$e = explode(" => " ,$v);
					$tagGroupName = $e[0];
					$tagName = $e[1];
					$w = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";				
				}	
				if ($first){
					$first = false;
					$where = $w;					
				}else
				 	$where .= " OR ($w)";			 	
			}										
		}else{
			if(strpos($searchTags,NOT_REVIEWED) !== FALSE){
				$where = "(files.id NOT IN (SELECT DISTINCT(filetags.docId) FROM filetags WHERE filetags.name='Reviewed'))";
		 		$searchString .= " Tag- " . NOT_REVIEWED;
			}else{
				$e = explode(" => " ,$searchTags);
				$tagGroupName = $e[0];
				$tagName = $e[1];
				
				if($tagGroupName == "ALL" && empty($tagName)){
				    $where = "(files.id = filetags.docId  AND filetags.groupName NOT IN ('Review','System'))";
				    $searchString .= " Tagged - ALL";
				}
				else{
				    $where = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
				    $searchString .= " Taged- " . $tagName . " in " . $tagGroupName;
				}
				
			}
		}
		$sql = "(SELECT DISTINCT files.id FROM files, filetags WHERE $where)";
		return $sql;		
	}	
	
	if ($table == "searchNotTags" || $table == "searchNotTagged")
	{		
		$searchNotTags = $searchValue;
		$s = explode("^", $searchNotTags);
		$none = false;
		if (sizeof($s) > 1){
			//or
			$first = true;
			foreach ($s as $v){								
				$e = explode(" => " ,$v);
				$tagGroupName = $e[0];
				$tagName = $e[1];
				$w = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
				$searchString .= " Not Tag- " . $tagName . " in " . $tagGroupName;					
				if ($first){
					$first = false;
					$where = $w;					
				}else
				 	$where .= " OR ($w)";			 	
			}										
		}else{			
			$e = explode(" => " ,$searchNotTags);
			$tagGroupName = $e[0];
			$tagName = $e[1];
			if($tagGroupName == "ALL" && empty($tagName)){
			    $where = "(files.id = filetags.docId AND filetags.groupName  NOT IN ('Review','System'))";
			    $searchString .= " Not Tag - ALL";
			}
			else{
    			 $where = "(files.id = filetags.docId AND filetags.groupName = '$tagGroupName' AND filetags.name = '$tagName')";
    			 $searchString .= " Not Tag- " . $tagName . " in " . $tagGroupName;
			}
		}
		$sql = "(SELECT DISTINCT files.id FROM files, filetags WHERE files.id NOT IN(SELECT DISTINCT files.id FROM files, filetags WHERE $where))";
		return $sql;				
	}
	
	if ($table == "searchDateMentioned"){		
		$searchDateMentioned = $searchValue;							
		$date .= getDateSql($searchDateMentioned,"date");										
	
		$sql = "(SELECT docId FROM 
				date WHERE " . 					
				$date;												
		$sql .= ")";									
		return $sql;		
	}	
	if ($table == "DateSearch"){
	    $searchDateMentioned = $searchValue;
	    $dates_arr = explode('AND', $searchDateMentioned);
	    $date = "files.date BETWEEN '$dates_arr[0]' AND '$dates_arr[1]'";

	    $sql = "(SELECT id FROM
				files WHERE " .
				$date;
				$sql .= ")";
				return $sql;
	}
	
	if ($table == "positive"){
	    $sql = scaleSql($mysqli, "emotion", $searchValue, "pro");
	    return $sql;
	}
	
	if ($table == "negative"){
	    $sql = scaleSql($mysqli, "emotion", $searchValue, "anti");
	    return $sql;
	}
	
	if ($table == "mood"){
	    $sql = scaleSql($mysqli, "emotion", $searchValue, "pro-anti");
	    return $sql;
	}
	if ($table == "currency"){
	    $sql = currencysql($mysqli, $searchValue);
	    return $sql;
	}
	if ($table == "phone"){
	    $sql = phonesql($mysqli, $searchValue);
	    return $sql;
	}
	if ($table == "address"){
	    $sql = addresssql($mysqli, $searchValue);
	    return $sql;
	}
	
	if ($table == "bankAccount"){
	    $sql = bankAccountsql($mysqli, $searchValue);
	    return $sql;
	}
	
	if($table == "searchSlots"){
	    $sql = slotDatasql($mysqli, $searchValue);
	    return $sql;
	    
	}
	if ($table == "searchDateOfWriting"){
		$searchDateofWriting = $searchValue;
		$sql = "SELECT id FROM files WHERE (" . getDateSql($searchDateofWriting,"files") . ")";				
		return $sql;			
	}	
	
	if ($table == "searchFolder"){
	
        $folder_value = explode('^', $searchValue);
        
        foreach ($folder_value as $f){
            $fnames[] = $mysqli->real_escape_string($f); 
        }
      
        $fnames = join("','",$fnames);
        $sql = "SELECT id FROM files WHERE origfolder IN('$fnames')";
        return $sql;
	}	
	
	if ($table == "searchLabel"){
		$searchLabel = $searchValue;
		$s = explode(";", $searchLabel);							
		if (sizeof($s) > 1){				
			foreach ($s as $v){
				if ($v != ""){
					$t = explode("=", $v);												
					$key = $t[0];
					$value = $t[1];
									
					$condition = $mysqli->real_escape_string($key) . " LIKE LOWER('%" . $mysqli->real_escape_string($value) . "%')";
					if ($key == "counted_theatre")
					{
						$value = analyzeWord($value,"Place-object");
						if (!empty($value))
							$condition = "($condition OR " . $mysqli->real_escape_string($key) . " LIKE LOWER('%" . $mysqli->real_escape_string($value) . "%'))";
					}
					
					$sql = "SELECT id FROM files WHERE $condition";
					return $sql;						
				}
			}
		}
	}
		
	if ($table == "searchUserNotesUsers"){
		$searchUserNotesUsers = $searchValue;
		if ($searchUserNotesUsers != "All"){
			$sql = "SELECT id FROM files WHERE (files.user_notes LIKE LOWER('%$searchUserNotesUsers%'))";					
		}else{
			$sql = "SELECT id FROM files WHERE (files.user_notes IS NOT NULL)";
		}
		return $sql;				
	}		
	
	if ($table == "searchSentiment"){
	    $sql = getSearchStringSentiment($searchValue, $isAdmin, $username,$mysqli);
		return $sql;	
	}	
	global $dropdownArrMaxMapping, $dropdownArrCaptionMapping;
	if(array_key_exists($table, $dropdownArrMaxMapping) || array_key_exists($table, $dropdownArrCaptionMapping)){
	    $mappedCaption = $dropdownArrMaxMapping[$table] ?? $dropdownArrCaptionMapping[$table];
	    return "SELECT DISTINCT($table.docId) as v from $table, $mappedCaption WHERE $table.docId=$mappedCaption.docId AND $mappedCaption.value = '$searchValue'";
	}

	
	$sql = single_sql_special_tables($mysqli, $searchValue, $table);
	return $sql;		
}

function getSearchExtra($extraColumns, $req_or_p){
    $searchExtra = array();
    foreach ($extraColumns as $table => $displayName)
    {
        if (array_key_exists($table, $req_or_p)){
            $searchExtra[$table] = $req_or_p[$table];}
    }
    return $searchExtra;
}

//Asynchronously return results of a certain search.
//lowest (best) score first limited in number and starting from offset
function getSearchResults($mysqli, $id, $threshold, $numberToFetch, $offset){
    if(empty($id)) {
        write_to_log("ERROR", "Invalid getSearchResults request");
        return;
    }
    require_once 'sqlTable.php';
    
    $sql = "SELECT docid FROM savedsearchid WHERE id = $id AND score<$threshold ORDER BY score LIMIT $numberToFetch OFFSET $offset";
    $results = sqlQuery($mysqli, $sql);
    while(($row = $results->fetch_object()) != false){
        $ret_arr[] = $row->docid;
    }
    
    return $ret_arr;
    
}



function highlightPredInstances($mysqli, $searchId, $extradata_pred){
    if(!empty($extradata_pred)){
        if(array_key_exists('selectedLeafs', $extradata_pred)){
            $predictive_leafs = $extradata_pred['selectedLeafs'];
        }
        if(array_key_exists('mandatoryLeafs', $extradata_pred)){
            $predictive_mand_fields = $extradata_pred['mandatoryLeafs'];
            $predictive_mand_fields = checkMandFields($mysqli, $predictive_mand_fields);
        }
    }
    foreach($predictive_leafs as $element){
        $element['concept'] = str_replace('http://www.intuview.com/ontology#', '', $element['concept']);
        $element['concept'] = TablesInformation::getTableName($element['concept']);
        if(is_array($element['ont'])){
            foreach($element['ont'] as $e){
                $search_vals[$element['concept']][] = $e;
            }
        }
        else{
            $search_vals[$element['concept']][] = $element['ont'];     
        }
    }

    $search_vals = json_encode($search_vals, true);
    
    $sql_search = "UPDATE savedsearch SET search_values = '$search_vals' WHERE id=$searchId";
    sqlQuery($mysqli, $sql_search);
}