<?php

require_once 'write_log.php';
require_once 'sqlGeneratorClass.php';

function sqlCreateConnection($refrence)
{

    global $databaseName;
	//use default db name
    write_to_log("INFO", "Creating Connection ($refrence) to $databaseName"); //in error to track the login of users
    
	return sqlCreateDBConnection($databaseName);
}

function sqlGetConnectionDetails()
{
	global $databaseName;
	//use default db name
	return connectDetails($databaseName);
}

function connectDetails($databaseName)
{
	global $connectionOverrides, $databaseHost, $databaseUser, $databasePass, $db_port;
	if ($connectionOverrides && array_key_exists($databaseName, $connectionOverrides))
	{
		$connectionDetails = $connectionOverrides[$databaseName];
	} else {
		$connectionDetails = array(
			"host" => $databaseHost,
			"user" => $databaseUser,
			"pwd" => $databasePass,
			"db" => $databaseName,
			"port" => $db_port);
	}
	if (!$connectionDetails["port"]) {
		$connectionDetails["port"] = NULL;
	}
	return $connectionDetails;
}
function sqlCreateDBConnection($databaseName, $omitDbName = false) {
    $connectionDetails = connectDetails($databaseName);
    if ($omitDbName) {
        $connectionDetails["db"] = NULL;
    }

    $connection = new mysqli($connectionDetails["host"], $connectionDetails["user"], $connectionDetails["pwd"], $connectionDetails["db"], $connectionDetails["port"]);
    $numRetries = 0;
    while (!$connection || $connection->connect_error) {
        write_to_log("ERROR", "failed to create connection, retry # $numRetries. connection details: " . print_r($connectionDetails, true) . ",error: " . ($connection ? $connection->connect_error : ""));
        if ($numRetries++ > 40) {
            return null;
        }
        usleep(1000000);
        
        $connection = new mysqli($connectionDetails["host"], $connectionDetails["user"], $connectionDetails["pwd"], $connectionDetails["db"], $connectionDetails["port"]);
    }

    //All JSON needs to be in UTF8.
    mysqli_set_charset($connection, 'utf8mb4');
    write_to_log("TRACE", "Created connection to $connectionDetails[host], $connectionDetails[user], $connectionDetails[pwd], $connectionDetails[db], $connectionDetails[port], " . ((array_key_exists('isMemSQL', $connectionDetails) && $connectionDetails["isMemSQL"])?"":"Not ") . "MemSQL");
    return $connection;
}

function sqlClose($connection, $refrence, $kill = false) {
    write_to_log("INFO", "Closing connection ($refrence) of $connection->host_info");
    
    if (!$connection) {
        return;
    }
    if ($kill) {
        $connection->kill($connection->thread_id);
    }

    $connection->close();
}


function fetchFromDefaultUser($mysqli, $dashboardTemplateUsername, $username){
    $name2row = [];
    $names = [];
    if (!empty($dashboardTemplateUsername) && $dashboardTemplateUsername!==$username) {
        $res = sqlQuery($mysqli, "SELECT * from dashboard WHERE username ='" . $mysqli->real_escape_string($dashboardTemplateUsername) . "' AND type != 'statistics'");
        while ($res && $row = $res->fetch_object()) {
            $name = $row->name;
            $name2row[$name] = $row;
        }
        
        foreach ($name2row as $graph) {
            $json = json_decode($graph->details, true);
            $names[$graph->name] = $json;
        }
        
    }
    return array("names"=>$names,"name2row"=>$name2row);
}

//unique sqlQuery that prevent looping if except_err received
function sqlQueryUnique($connection, $query, $err){
    return sqlQuery($connection, $query, $err);
}

function sqlQuery($connection, $query, $err = null)
{
    
    global $truncate_sql;
    $query_log = $query;
    if(!empty($truncate_sql)){
        $query_log = mb_strcut($query, 0, $truncate_sql);
    }
	if (empty($query)){
		write_to_log("ERROR", "sql query empty: " . print_r(debug_backtrace(), true));
		return FALSE;
	}
		
	$start = microtime(true);
	$result = $connection->query($query);
	$err_msg =  mysqli_error($connection);

	if (!$result && isset($err) && !strpos($err_msg, $err) !== false) {
	    $username = $_SESSION["username"] ?? null;
	    write_to_log("TRACE", $query . " - failed with matching for Username: <$username> error string: '$err' - inside: '$err_msg'");
	    return $result;
	}
	
	$duration = microtime(true) - $start;
	write_to_log("INFO", "SQL duration: $duration, query: " . $query_log);

	for ($failureSleep = 10 ; $result === FALSE && $failureSleep < 5000; $failureSleep *=3)
	{
	    
		usleep($failureSleep*1000);
		$result = $connection->query($query);
	
	}
	if ($result === FALSE){
	    write_to_log("ERROR", $query . " - sql query failed after " . $failureSleep . "ms aprox:" . mysqli_error($connection));
	}
	
	return $result;
}

function sqlMultiQuery($connection, $query, $dontFreeResults=false)
{
	$result = $connection->multi_query($query);
	write_to_log("TRACE", mb_strcut($query, 0, 53) . print_r($result, true));
	if ($result === FALSE){
		write_to_log("ERROR", mb_strcut($query, 0, 53) . " - sql multi query failed:" . mysqli_error($connection));
		return false;
	}

	if (!$dontFreeResults)
	{
		do {
		    if ($sqlResult = $connection->store_result()){
				$sqlResult->free();
		    }
		} while ($connection->more_results() && $connection->next_result());
	}
	
}

/**Get a query that must return one result. 
 * This method returns The return type and not an object.
 * @param $connection - the connection to the database
 * @param $query - the query to run. 
 * @param $returnType - the return type only if the return is <b>one</b> result.
 * @return $result The type that was specified in the $returnType.*/
function getSingleResultFromQuery($connection, $query, $returnType){
	$res = sqlQuery($connection,$query);
	if(!empty($res)){
		$row = $res->fetch_object();
		if(!empty($row)){
			$result= $row->$returnType;
		}
	}
	return $result;
}

/**Updates the parent id in the foldertree table and the parentFolderId in files table. 
 * This method is being used in cases of url,singleFile and textBox.
 * @param $connection - connection to the database.
 * @param $username - must have a value.
 * @param $type - url,textBox or uploadedFile
 * @param $singleFilesName - the name of the folder under it the files will be inserted.
 * @param $docId - The document id of the file. 
 * @return <b>true</b> if all the updates where success and <b>false</b> if not. */
function updateParentId($connection,$username,$type,$singleFilesName,$docId){

	$userId = getSingleResultFromQuery($connection,"SELECT id FROM foldertree WHERE NAME='$username' AND username='$username'","id");
	if(empty($userId)){
		write_to_log("ERROR", "function ".__FUNCTION__ . " foldertree User id is empty with username->$username");
		return false;
	}
	$singleId = getSingleResultFromQuery($connection,"SELECT id FROM foldertree WHERE NAME='$singleFilesName' AND ParentId=$userId","id");
	if(empty($singleId)){
		write_to_log("ERROR", "function ".__FUNCTION__ ." foldertree $singleFilesName id is empty with userId->$userId");
		return false;
	}
	$typeId = getSingleResultFromQuery($connection,"SELECT id FROM foldertree WHERE NAME='$type' AND ParentId=$singleId","id");
	if(empty($typeId)){
		write_to_log("ERROR", "function ". __FUNCTION__ ." foldertree singlefile with type->$type and parent id->$singleId is empty");
		return false;
	}
	$setParentIdSql = "
	UPDATE files
	SET parentFolderId = $typeId
	WHERE id=" . $docId;
	$res = sqlQuery($connection,$setParentIdSql);
	if($res != TRUE){
		write_to_log("ERROR", "senddocEngAjx Update parentFolderId failed");
		return false;
	}
	return true;
}

function createFolderTreeTable($mysqli){
    global $isMemSQL;
    $referDeclr = $isMemSQL?" REFERENCE " : "";
    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS foldertree(
			id INT NOT NULL AUTO_INCREMENT,
			Name VARCHAR(150) NOT NULL,
			ParentId INT NOT NULL,
			username MEDIUMTEXT NOT NULL,
			PRIMARY KEY (id),
			UNIQUE (Name, ParentId),
			KEY ParentId_Index (ParentId)
		)";
        sqlQuery($mysqli, $sql);
}

//Creates a folder tree for a user for url, single files and text box and save search
function createUserSingleFilesTree($connection, $username){
    createFolderTreeTable($connection);
	$userFolder = "INSERT INTO foldertree (`Name`,`ParentId`,username) VALUES (\"$username\",0,\"$username\")";
	sqlQuery($connection,$userFolder);
	
	$userIdSql = "SELECT MAX(id) AS max FROM foldertree";
	$resMaxId = sqlQuery($connection,$userIdSql);
	if(!empty($resMaxId)){
		$row = $resMaxId->fetch_object();
		if(!empty($row)){
			$userId= $row->max;
		}
	}			
	
	$singleFilesRootDir = "INSERT INTO foldertree (`Name`,`ParentId`,username) VALUES (\"singleFiles\",$userId,\"$username\")";
	sqlQuery($connection,$singleFilesRootDir);
	
	$subFolders = array("url"=>$userId+1,"uploadedFiles"=>$userId+1,"textBox"=>$userId+1);
	
	if($username=='admin'){
	    $subFolders["removed_users"] = $userId;
	}
	
	foreach ($subFolders as $folder=>$parentId){
		$createFolderSql = "INSERT INTO foldertree (`Name`,`ParentId`,username) VALUES (\"$folder\",$parentId,\"$username\")";
		sqlQuery($connection,$createFolderSql);
	}
	
	$saveSearchRootDir = "INSERT INTO foldertree (`Name`,`ParentId`,username) VALUES (\"savedsearch\",$userId,\"$username\")";
	sqlQuery($connection,$saveSearchRootDir);
}

/**
 * getAllDatesByRange - generates a list of dates of files (ignore null) - ordered by date
 */
function getAllDatesByRange($connection,$dateColumn, $fromDate, $toDate, $isAdmin, $username, $savedSearchSqlId){
       global $bigData;
       if(!empty($bigData)){
           $dates = array();
           //gets the first date and last date 
           $min_max_date_sql = "SELECT MIN(scan_time) as min , MAX(scan_time) as max FROM files WHERE scan_time IS NOT NULL AND scan_time != '0000-00-00 00:00:00'";
           $res = sqlQuery($connection,$min_max_date_sql);
           if(!empty($res)){
               while ($row = $res->fetch_object()){
                   $period = new DatePeriod(
                       new DateTime($row->min),
                       new DateInterval('P1D'),
                       new DateTime($row->max)
                       );
               }
           }
           foreach ($period as $key => $value) {
               $dates[] = $value->format('Y-m-d');
           }
           return $dates;
       }
    $dates_range = array();
    $sql_all_analyzed_dates = sql_getAllDatesByRange($fromDate, $toDate, $dateColumn, $isAdmin, $username,$savedSearchSqlId);
	$res_all_analyzed_dates = sqlQuery($connection,$sql_all_analyzed_dates);
	
	//All the dates that were analyzed between fromDate to toDate
	if(!empty($res_all_analyzed_dates)){
	
		while ($row = $res_all_analyzed_dates->fetch_object()){
	
			if(!empty($row)){
				$dates_range[] = $row->date;
			}
		}
	}
	return $dates_range;
}

function getSumEntitySentimentPerDate($sentiment, $date, $entityDocId, $connection, $dateColumn){
    $sql_sent = sql_getSumEntitySentimentPerDate($entityDocId, $sentiment, $dateColumn, $date, $savedSearchSqlId);
	$res_sent = sqlQuery($connection, $sql_sent);

	if(!empty($res_sent)){
			
		$row = $res_sent->fetch_object();

		if(!empty($row)){
			$sent_docs = $row->sum;
		}
	}
	return $sent_docs;
}

/**
 * Returns the appearances number of an entity in a spacific date.
 */
function getEntitySumAppearancesInDatesRange($savedSearch_sql, $dates_range, $entity, $table, $connection, $dateColumn, $savedSearchSqlId, $isAdmin, $username){
    $entity_docs = array();
    $sql = sql_getEntitySumAppearancesInDatesRange($savedSearch_sql, $dates_range, $dateColumn, $table, $entity, $isAdmin, $username, $savedSearchSqlId);

	$res_docs_by_date_ent = sqlQuery($connection, $sql);

	while(!empty($res_docs_by_date_ent) && ($row = $res_docs_by_date_ent->fetch_object())){
		$entity_docs[$row->date_col]['total_doc'] = intval($row->sum);
		if($entity_docs[$row->date_col] > 0){
		    $entity_docs[$row->date_col]['total_occ'] = intval(max($row->occ,$row->sum));
		}
		else{
		    $entity_docs[$row->date_col]['total_occ'] = 0;
		}
	}
	return $entity_docs;
}



function convertDateArrayToString($dateArr,$dateColumn){
	//in all our cases it's enough to take the range between the first and the last date
    if(empty($dateArr)){
        $dateArr = array('0000-00-00', '2100-01-01');
    }
	$str = " ($dateColumn  BETWEEN '" . $dateArr[0] . " 00:00:00' AND '" . end($dateArr) . " 23:59:59') ";
	return $str;
}

/**
 * Get the number of analysed documents in a single date
 */
function getNumofAllAnalayzedDocsPerDatesRange($dates_range, $connection, $dateColumn, $username, $isAdmin, $savedSearchSqlId, $uniqueValueTableName = NULL){
    $all_entity_date_docs = [];

    $sql_all_docs_by_date = sql_NumofAllAnalayzedDocsPerDatesRange($dates_range,$dateColumn,$uniqueValueTableName,$isAdmin,$username,$savedSearchSqlId );

	$res_all_docs_by_date =  sqlQuery($connection, $sql_all_docs_by_date);

	while(!empty($res_all_docs_by_date) && ($row = $res_all_docs_by_date->fetch_object())){
		if(!empty($row)){
		    $createDate = new DateTime($row->date_col); 
		    $strip = $createDate->format('Y-m-d');
		    if(!isset($all_entity_date_docs[$strip])){
		        $all_entity_date_docs[$strip] = intval($row->sum);
		    }
		    else{
		      $all_entity_date_docs[$strip] += intval($row->sum);
		    } 

		}
	}
	return $all_entity_date_docs;
}

/**
 * Returns an count of distinct documents ids of an entity in a single date.
 */
function getDocIdsFromConcept($savedSearch_sql, $table, $name ,$dates_range, $connection, $dateColumn, $type, $isAdmin, $username, $isAll=false ,$savedSearchId = null){
    $results_arr = array();
    $sql_ent_date_docs = sql_getDocIdsFromConcept($savedSearch_sql, $type, $table, $isAdmin, $username, $isAll, $name, $savedSearchId, $dateColumn, $dates_range);
	$res_ent_date_docs = sqlQuery($connection,$sql_ent_date_docs);

	while(!is_null($row = $res_ent_date_docs->fetch_object())){
	    $entityDocId = $row->count;
		if($entityDocId > 0){
		    $entityOcc = max($row->occ,1); 
		}
		else{
		    $entityOcc = 0;
		}
		if(array_key_exists($row->DATE, $results_arr)){
		    $results_arr[$row->DATE]['doc'] += intval($entityDocId);
		    $results_arr[$row->DATE]['occ'] += intval($entityOcc);
		}
		else{
		  $results_arr[$row->DATE] = array("doc"=>intval($entityDocId), "occ"=>intval($entityOcc));
		}
	}

	return $results_arr;

}

/**
 * <u><b>WARRNINIG</b></u>: compatible only for the tables that have column field named 'value' that contains the value 
 *returns an array of doc ids.
 */
function getEntityDocIdsFromTable($table, $name ,$date, $connection, $dateColumn, $savedSearchSqlId = null){
    $entityDocIds = 0;
    $sql_ent_date_docs = sql_getEntityDocIdsFromTable($table, $name, $dateColumn, $date, $savedSearchSqlId);
    $res_ent_date_docs = sqlQuery($connection, $sql_ent_date_docs);

	while(!is_null($row = $res_ent_date_docs->fetch_object())){
		$entityDocIds = $row->sum;
	}
	return $entityDocIds;
}


//
//Crawler functions
//

function createCrawlerConnection(&$connection) {
    if ($connection == NULL) {
        $connection = sqlCreateDBConnection("crawlerDB");
    }
}

//
//Stream Crawler functions
//

function createStreamConnection(&$connection) {
    if ($connection == NULL) {
        $connection = sqlCreateDBConnection("streamDB");
    }
}

//
//Sample Usage:
//$connection = NULL;
//foreach ($posts as $post)
//{
//	if (!tryInsertUnique($connection, "Twitter", $post->id))
//		continue;
//
//	//parse post and save to $uploadedFilesFolder and extra data folder
//}
function tryInsertUnique(&$connection, $type, $id)
{
	createCrawlerConnection($connection);
	sqlQuery($connection,
			createInsertIgnoreSql($connection,
					["post_type" => $type, "post_id" => $id], TRUE, "parsed_post_id", true));
	
	return $connection->affected_rows;
}

//
//Sample usage: markUsersInTable($mysqli, "Twitter", "accountId", ["id1": [...], "id2": [...]], "egyptian_twitter")
//Sample return value: ["id1": ["egyptin_twitter": true, ...], "id2": [...]]
function markUsersInTable(&$connection, $accountType, $usersColumn, &$usersArray, $tableSuffix)
{
	createCrawlerConnection($connection);
	$res = sqlQuery($connection, "SELECT $usersColumn FROM user_account_$tableSuffix WHERE accountType='$accountType' AND $usersColumn IN ('" . join("' '", array_keys($usersArray) . "')"));
	while ($res && ($row = $res->fetch_object()))
	{
		$usersArray[$row->$usersColumn][$tableSuffix] = true;
	}
}

//Sample usage: isUserExistsInTable($mysqli, "Twitter", "accountId", "[id]", "egyptian_twitter")
//Sample return value: boolean
  function isUserExistsInTable(&$connection, $accountType, $usersColumn, $user, $tableSuffix)
{
    createCrawlerConnection($connection);
    $res = sqlQuery($connection, "SELECT $usersColumn FROM user_account_$tableSuffix WHERE accountType='$accountType' AND $usersColumn = $user");
    return $res && $res->num_rows;
    
}



/**
 *  CLASS TablesInformation
 *  gets us only information about sql structure (as columns names, tables names etc)
 */
class TablesInformation{
    public static $cache  = array();
    
    //get the element columns
    public static function getElementsColumns($mysqli, $table_name, $as_array = false, $extra_only = null){
        global $ignore_extra_cols;
        $table_name = self::getTableName($table_name);
        if(array_key_exists($table_name,self::$cache)){
            return self::$cache[$table_name];
        }
        
        $ret_val = "";
        $local_connection = false;
       
        $query = "SHOW COLUMNS FROM $table_name";
        $res = sqlQuery($mysqli, $query);
        while(($row = $res->fetch_object()) != null){
            if($extra_only && in_array($row->Field, $ignore_extra_cols)){
                continue;
            }
            $ret_val .= $row->Field.',';
        }
        $ret_val = rtrim($ret_val,',');
        self::$cache[$table_name] = $ret_val;
        
        if($as_array){
            $ret_val = explode(',', $ret_val);
            sort($ret_val);
        }
        return $ret_val;
    }
    
    /**
     * return slotdata columns (non empty) per specific concepts (if all or null - return all)
     * @param unknown $mysqli
     * @param unknown $concept
     * @return unknown
     */
    public static function getSlotDataColumns($mysqli, $concept = null){
        global $ignore_extra_cols, $entitiesTypeToTable;
        $response = [];
    
        $full_cols = self::getElementsColumns($mysqli, 'slotdata', true, true);
      
        if(empty($concept) || strtolower($concept) == "any"){
            $concept_cond = " TRUE ";
            
        }
        elseif(!empty($concept)){
            $concept = $entitiesTypeToTable[strtolower($concept)];
            $concept_cond = "conceptName=\"$concept\"";
        }
   
        foreach($full_cols as $col){
            $sql = "SELECT $col FROM slotdata WHERE $concept_cond AND $col <> '' limit 1";
            $res = sqlQuery($mysqli, $sql);
            if(!empty($res) && $row = $res->fetch_object()){
                $response[] = $col;
            }
        }

    return $response;
    }
 
    
    //gets the table name of a string
    public static function getTableName($text){ 

        
        return strtolower(str_replace(array(' ', '-'),'_',$text));
      
    } 
    
    //gets the table name of a string
    public static function getConceptPresentName($text){
        if(empty($text)){
            return "";
        }
        return ucfirst(strtolower(str_replace("_","-",$text)));
    }
    
    public static function isColumnExists($mysqli, $table,$column){
        $columnsStr = self:: getElementsColumns($mysqli, $table);
        if(strpos($columnsStr, $column)!== false){
             return true;  
        }
        return false;
    } 
    
    public static function isTableExists($mysqli, $table, $tables = null){
        if(empty($tables)){
            $query =" show tables";
            $res = sqlQuery($mysqli, $query);
         
             $rows = $res->fetch_all ();
        }
        else{
            $rows = $tables;
        }
         if(!empty($rows) && in_array(array($table), $rows)){
             return true;
         }
         return false;
   }
   
   public static function getRangeFromNumericTable($mysqli, $table){
       $query = "SELECT MIN(NAME+0) AS MIN, MAX(NAME+0) AS MAX FROM $table";
       $res = sqlQuery($mysqli, $query);
       
       if(($row = $res->fetch_object()) != null){
           return ["min"=> $row->MIN, "max" => $row->MAX];
       }
       return ["Error" => "error getting the min max val"];
   }
   
   public static function getDistinctValuesFromCol($mysqli, $table, $col, $conceptname = null){
       $ret_val = [];
       $key_cond = " TRUE ";
       if(!empty($conceptname) && strtolower($conceptname) !== "any"){
           global $entitiesTypeToTable;
           $conceptname = $entitiesTypeToTable[strtolower($conceptname)];
           $key_cond = " conceptName = \"$conceptname\"";
       }
       $query = "SELECT DISTINCT $col as col FROM $table WHERE $key_cond AND $col <> ''";
       $res = sqlQuery($mysqli, $query);
       while(($row = $res->fetch_object()) != null){
         
           $ret_val[] = $row->col;
       }
       sort($ret_val);
       return $ret_val;
   }
   
}
  

?>