<?php

require_once 'write_log.php';
require_once 'sqlUtils.php';
require_once 'parseUtils.php';
require_once 'sentimentUtils.php';

function insert_to_saved_search_sql($mysqli, $searchPerSql): string {
    sqlQuery($mysqli, "DROP TABLE IF EXISTS temp_savedsearchid_subquery");
    $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_savedsearchid_subquery (id int) AS $searchPerSql";
    return $sql;
}

/**
 * genertes the sql for function entitySumSentiment under proSentEng.php
 */
function entity_sent_sql($savedSearch_sql, $dateColumn, $freeTextInput, $sentiment,$table,$savedSearchSqlId, $sql_str_id){
    global $skipDuplicate;
    $mysqli = sqlCreateConnection( "settings isOrgText");
    $isOrgText = getSystemSettingsProp($mysqli, "isOrgText");
    sqlClose($mysqli, "settings isOrgText");
    $name_cond = "";
    if($isOrgText){
        $name_cond = "($table.name in ('$freeTextInput') OR $table.orgText in ('$freeTextInput'))";
    }
    else{
        $name_cond = "($table.name in ('$freeTextInput') )";
    }
    if ($skipDuplicate){
        $sql = "SELECT SUM(unique_files.count_doc) AS $sentiment, $table.$sentiment as ".strtoupper($sentiment)."_Captions, CAST(files.$dateColumn AS DATE) AS date_col FROM $table,unique_files, files 
                WHERE $table." . "$sentiment <> '' AND $name_cond AND unique_files.id=files.id  ";
    }
    else{
        $sql = "SELECT COUNT(DISTINCT($table.docId)) AS $sentiment, $table.$sentiment as ".strtoupper($sentiment)."_Captions, CAST(files.$dateColumn AS DATE) AS date_col FROM $table, files 
            WHERE  $table." . "$sentiment <> '' AND $name_cond";
    }

    $sql .= "AND files.id = $table.docId" . $savedSearchSqlId;
    if($savedSearch_sql){
        $sql .= " AND files.id in ($savedSearch_sql)";
    }
    if($sql_str_id){
        $sql .= " AND files.id in ($sql_str_id)";
        
    }
    $sql .= " GROUP BY date_col";
    
    return $sql;
}

/**
 * generates sql for abstract_idea search - takes the sql_str_id (array of docId that contain this idea)
 * and fetch the docId that contain the pro/anti ($sentiment)
 */
function abstract_idea_sent($sentiment,$table,$savedSearchSqlId, $sql_str_id, $sub_concept_sql){
    global $skipDuplicate;
    
    
    if ($skipDuplicate){
        $sql = "SELECT SUM(unique_files.count_doc) AS $sentiment FROM $table,unique_files, files, $sentiment"."_concept WHERE $table." . "$sentiment IS NOT NULL AND unique_files.id=files.id  ";
    }
    else{
        $sql = "SELECT DISTINCT($table.docId) AS $sentiment  FROM $table, files, $sentiment"."_concept WHERE $table." . "$sentiment IS NOT NULL  ";
    }
    
    $sql .= "AND files.id = $table.docId" . $savedSearchSqlId;
    $sql .= " AND files.id in ($sql_str_id)";
    $sql .= " AND $sub_concept_sql";
    
    return $sql;
}
/**
 * genertes the sql for method under function tryInsertUnique
 */

function createInsertIgnoreSql($connection, $columnsToValues, $uniqueColumns, $tableName, $sqlEscape)
{
    $quotedValues = [];
    $conditions = [];
    $firstColumn = null;
    foreach ($columnsToValues as $column => $val)
    {
        
        $v = $val;
        if ($sqlEscape){
            $v = $connection->real_escape_string($v);
        }
        $v = "'$v'";
        $quotedValues[] = $v;
        if ($uniqueColumns === TRUE || in_array($column, $uniqueColumns))
        {
            $conditions[] = "$tableName.$column = $v";
            if ($firstColumn == null){
                $firstColumn = $column;
            }
        }
    }
    $columnsStr = join(", ", array_keys($columnsToValues));
    $valuesStr = join(", ", $quotedValues);
    $conditionsStr = join(" and ", $conditions);
    return "insert $tableName($columnsStr)
            	select $valuesStr
            	from mutex
            	left outer join $tableName
            	on $conditionsStr
            	where mutex.i = 1 and $tableName.$firstColumn is null;";
}

function get_findInSet($name, $t_name) : string
{
	$isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
	return $isMemSQL? ("$t_name RLIKE '[[:<:]]{$name}[[:>:]]'") : ("FIND_IN_SET('$name',$t_name)");	
}

/**
 * genertes the sql for the function concept count of docId and occurrences
 */
function sql_getDocIdsFromConcept($savedSearch_sql, $type, $table, $isAdmin, $username, $isAll, $name, $savedSearchId, $dateColumn, $dates_range){
    global $skipDuplicate;
    if ($type){
        $t_name = $table . ".type";
    }
    else{
        $t_name = $table . ".name";
    }
    $dates_string = implode("','", $dates_range);
    $userSql = $isAdmin ? 'TRUE' : " files.username = '$username' ";
    
    if($isAll){
        if ($skipDuplicate)	{
            $sql_ent_date_docs = "SELECT CAST(files.date AS DATE) AS DATE, SUM(unique_files.count_doc) AS count, SUM($table.occurrences) AS occ FROM $table,files,unique_files WHERE files.id IN($savedSearch_sql) AND $userSql AND CAST(files.$dateColumn AS DATE) IN('" . $dates_string . "') AND unique_files.id=files.id AND files.id=$table.docId" .$savedSearchId . " GROUP BY DATE";
        }
        else{
            $sql_ent_date_docs = "SELECT CAST(files.date AS DATE) AS DATE, COUNT(DISTINCT($table.docId)) AS count, SUM($table.occurrences) AS occ FROM $table,files WHERE files.id IN($savedSearch_sql) AND $userSql AND CAST(files.$dateColumn AS DATE)  IN('" . $dates_string . "') AND files.id=$table.docId" .$savedSearchId . " GROUP BY DATE";
        }
    }else{
        if ($skipDuplicate){
            $sql_ent_date_docs = "SELECT CAST(files.date AS DATE) AS DATE, SUM(unique_files.count_doc) AS count,SUM($table.occurrences) AS occ FROM $table,files,unique_files WHERE files.id IN($savedSearch_sql) AND $userSql AND " . get_findInSet($name, $t_name) . " AND CAST(files.$dateColumn AS DATE)  IN('" . $dates_string . "') AND unique_files.id=files.id AND files.id=$table.docId" . $savedSearchId . " GROUP BY DATE";
        }
        else{
            $sql_ent_date_docs = "SELECT CAST(files.date AS DATE) AS DATE, COUNT(DISTINCT($table.docId)) AS count, SUM($table.occurrences) AS occ FROM $table,files WHERE files.id IN($savedSearch_sql) AND $userSql AND " . get_findInSet($name, $t_name) . " AND CAST(files.$dateColumn AS DATE)  IN('" . $dates_string . "') AND files.id=$table.docId" . $savedSearchId . " GROUP BY DATE";
        }
    }
    return $sql_ent_date_docs;
}

/*
 * genertes the sql for the function concept doc ids
 */
function sql_DocIdsFromConcept($type, $table, $isAdmin, $username, $isAll, $name, $savedSearchId, $dateColumn, $dates_range){
    global $skipDuplicate;
    
    
    if ($skipDuplicate){
        $date = convertDateArrayToString($dates_range,"files.date");
    }
    else{
        $date = convertDateArrayToString($dates_range,$dateColumn);
    }
    
    if ($type){
        $t_name = $table . ".type";
    }
    else{
        $t_name = $table . ".name";
    }
    
    $userSql = $isAdmin ? 'TRUE' : " files.username = '$username' ";
    
    if($isAll){
        if ($skipDuplicate)	{
            $sql_ent_date_docs = "SELECT unique_files.count_doc AS count FROM $table,files,unique_files WHERE $userSql AND $date AND unique_files.id=files.id AND files.id=$table.docId" .$savedSearchId;
        }
        else{
            $sql_ent_date_docs = "SELECT $table.docId AS docId FROM $table,files WHERE $userSql AND $date AND files.id=$table.docId" .$savedSearchId;
        }
    }else{
        if ($skipDuplicate){
            $sql_ent_date_docs = "SELECT unique_files.count_doc AS count FROM $table,files,unique_files WHERE $userSql AND " . get_findInSet($name, $t_name) . " AND $date  AND unique_files.id=files.id AND files.id=$table.docId" . $savedSearchId;
        }
        else{
            $sql_ent_date_docs = "SELECT $table.docId AS count FROM $table,files WHERE $userSql AND " . get_findInSet($name, $t_name) . " AND $date AND files.id=$table.docId" . $savedSearchId;
        }
    }
    return $sql_ent_date_docs;
}

/**
 * genertes the sql for the function getEntitySumAppearancesInDatesRange
 */
function sql_getEntitySumAppearancesInDatesRange($savedSearch_sql, $dates_range, $dateColumn, $table, $entity, $isAdmin, $username, $savedSearchSqlId){
    global $skipDuplicate, $isOrgText, $group_trend_table;
    if ($skipDuplicate){
        $date = convertDateArrayToString($dates_range,"files.date");
    }
    else{
        $date = convertDateArrayToString($dates_range,"files.date");
    }
    if ($skipDuplicate){
        $sql = "SELECT SUM(unique_files.count_doc) AS sum, SUM($table.occurrences) as occ,  CAST(files.$dateColumn AS DATE) AS date_col FROM files,unique_files, $table ";
    }
    else{
        $sql = "SELECT COUNT(DISTINCT($table.docId)) AS sum, SUM($table.occurrences) as occ, CAST(files.$dateColumn AS DATE) AS date_col FROM files, $table ";
    }
    $sql .= " WHERE ($date) AND files.id IN($savedSearch_sql) AND ";
    if ($skipDuplicate){
        $sql .= " unique_files.id=files.id AND ";
    }
    if ($table == "place_object") {
        $sql .=getLocationSqlString($entity,$table);
    } else{
        $sql .= "$table.name='$entity' ";// OR $table.orgText='$entity'";
        if($isOrgText && $table !== $group_trend_table){
            $sql .= " OR $table.orgText='$entity'";
        }
    }
    
    $usersql = ($isAdmin) ? TRUE : " files.username = '$username'";
    
    $sql .= "AND $usersql AND files.id = $table.docId" . $savedSearchSqlId;
    $sql .= " GROUP BY date_col";
    return $sql;
}

function getLocationSqlString($entity,$table){
    $mysqli = sqlCreateConnection( "settings isOrgText");
    $isOrgText = getSystemSettingsProp($mysqli, "isOrgText");
    sqlClose($mysqli, "settings isOrgText");
    
    $sql = "";
      if(!is_array ($entity)){
            $entity =array($entity);
        }
        $first = true;
        $sql .= "(";
        foreach ($entity as $key => $value) {
            if (!$first) {
                $sql .= " OR ";
            }
            $sql .= " $table.name='$value'  OR $table.country='$value'";
            if($isOrgText){
                $sql .= " OR $table.orgText='$value'";
            }
            $first = false;
        }
        $sql .= ")";
        return $sql;
} 

function sql_getSentimentsDocsIds($savedSearch_sql, $isAdmin, $username, $table, $dates_range, $freeTextInput, $dateSQLFormat,$savedSearchSqlId){
    $searchPerSql = sql_getEntityIdsAppearancesInDatesRange($savedSearch_sql, $dates_range, $dateSQLFormat, $table, $freeTextInput, $isAdmin, $username, $savedSearchSqlId);
    return "SELECT docId FROM ($searchPerSql )as docIds";  
}

function sql_getEntityIdsAppearancesInDatesRange($savedSearch_sql, $dates_range, $dateColumn, $table, $entity, $isAdmin, $username, $savedSearchSqlId){
    global $skipDuplicate;
    $mysqli = sqlCreateConnection( "settings isOrgText");
    $isOrgText = getSystemSettingsProp($mysqli, "isOrgText");
    sqlClose($mysqli, "settings isOrgText");
    
    if ($skipDuplicate){
        $date = convertDateArrayToString($dates_range,"files.date");
    }
    else{
        $date = convertDateArrayToString($dates_range,"files.date");
    }
    if ($skipDuplicate){
        $sql = "SELECT unique_files.id AS docId,  CAST(files.$dateColumn AS DATE) AS date_col FROM files,unique_files, $table ";
    }
    else{
        $sql = "SELECT DISTINCT($table.docId) as docId, CAST(files.$dateColumn AS DATE) AS date_col FROM files, $table ";
    }
    $sql .= " WHERE files.id IN($savedSearch_sql) AND ($date) AND ";
    if ($skipDuplicate){
        $sql .= " unique_files.id=files.id AND ";
    }
    $orgTextCond = "";
    if($isOrgText){
        $orgTextCond = " OR $table.orgText='$entity'";
    }
    
    
    if($table == "place_object"){
        $sql .= "($table.name='$entity' OR $table.country='$entity' $orgTextCond) ";
    }else{
        $sql .= "$table.name='$entity' $orgTextCond";
    }
  
    $usersql = ($isAdmin) ? TRUE : " files.username = '$username'";
    
    $sql .= "AND $usersql AND files.id = $table.docId" . $savedSearchSqlId;
    return $sql;
}

function quoteArrayValues($array){
      foreach ($array as $key => $value) {
      $array[$key]= "'$value'";  
     }
   return $array; 
}


function arrayToSqlString($array){
    if(is_array($array)){
     $quotedArray = quoteArrayValues($array);
     return implode(",", $quotedArray);
    }
    return "'$array'";
}

function sql_getEntityIdsAppearancesInDatesRangeSentiment($dates_range, $dateColumn, $table, $entity, $isAdmin=null, $username=null,$savedSearchSqlId=null) {
    global $skipDuplicate;
    if ($skipDuplicate) {
        $date = convertDateArrayToString($dates_range, "files.date");
    } else {
        $date = convertDateArrayToString($dates_range, $dateColumn);
    }
    $pro_sql = get_sentiment_sql("pro",$table,$entity,$date);
    $anti_sql = get_sentiment_sql("anti",$table,$entity,$date);
    return array("pro"=>$pro_sql,"anti"=>$anti_sql);
}

function get_sentiment_sql($sentiment,$table,$entity,$date){
    global $isOrgText;
    $name_cond = "";
    if($isOrgText){
        $name_cond = "($table.name IN('$entity') OR $table.orgText IN('$entity'))";
    }
    else{
        $name_cond = "($table.name IN('$entity'))";
    }
    
    $sql = "SELECT id AS docId FROM 
            (SELECT DISTINCT files.id FROM files WHERE TRUE 
            AND 
            files.id IN
            (SELECT id FROM files WHERE ($date))
            AND
            files.id IN
            (SELECT $table.docId FROM $table WHERE $name_cond AND $table.$sentiment <> ''))
             as t";
    return $sql;
}

function sentiment_sql_query($sentiment, $table, $conceptsList, $entity, $date) {
    $sentiment_table = "$sentiment" . "_concept";
    $sql = "(SELECT id AS docId FROM (SELECT DISTINCT files.id FROM files WHERE TRUE AND (files.id IN (SELECT id FROM files WHERE ($date))) AND 
           (files.id IN (SELECT $table.docId FROM $table  JOIN $sentiment_table ON $table.docId = $sentiment_table.docId AND 
            $table.id = $sentiment_table.instanceId AND $sentiment_table.instanceType =  '$table'  
             WHERE $sentiment_table.value IN($conceptsList) AND  $table.name IN ($entity)))) AS t1)";
    return $sql;
}
/**
 * genertes the sql for the entity sentiment (under  getEntitySumAppearancesInDatesRange)
 */
function sql_NumofAllAnalayzedDocsPerDatesRange($dates_range,$dateColumn,$uniqueValueTableName,$isAdmin,$username,$savedSearchSqlId ){
    $date = convertDateArrayToString($dates_range,$dateColumn);
    $fieldName = "files.id";
    $uvTable = "";
    $uvCondition = "";
    //for counting unique users (for example), instead of counting files
    if ($uniqueValueTableName)
    {
        $uvTable = ", $uniqueValueTableName AS ut ";
        $fieldName = "DISTINCT(ut.value)";
        $uvCondition = " AND ut.docId = files.id ";
        
    }
    $user_sql = (!$isAdmin) ? " AND username='$username'": "";
    
    
    return "SELECT COUNT($fieldName) AS sum, files.date AS date_col FROM files $uvTable" .
    " WHERE ($date) " . $savedSearchSqlId .
    $uvCondition .
    " $user_sql GROUP BY date_col";
}

function createSqlInsertDocPath($connection, $docId, $folderPath, $batchName, $userName, $insertFolders=false)
{
    
    $folders = preg_split("#[\\/\\\\]#", $folderPath, -1, PREG_SPLIT_NO_EMPTY);
    array_unshift($folders, $userName, $batchName);
    
    $sql = "SET @parentId = 0;";
    for ($index = 0; $index < count($folders); $index++)
    {
        $sqlFolder = $connection->real_escape_string($folders[$index]);
        if ($insertFolders)
            $sql .= "
				INSERT INTO foldertree(`Name`, ParentId, username)
				(SELECT '$sqlFolder', @parentId, '$userName'
				FROM foldertree
				WHERE `Name` = '$sqlFolder' AND username = '$userName' AND ParentId = @parentId
				HAVING COUNT(`Name`) = 0);
				";
            $sql .= "
				SELECT @parentId := id FROM foldertree WHERE `Name` = '$sqlFolder' AND ParentId = @parentId;
		";
    }
    $sql .= " UPDATE files SET parentFolderId = @parentId WHERE id = $docId;";
    
    return $sql;
}

function sql_getAllDatesByRange($fromDate, $toDate, $dateColumn, $isAdmin, $username,$savedSearchSqlId){
    
    if(!empty($fromDate) && !empty($toDate)){
        $date_sql="  CAST($dateColumn AS DATE) BETWEEN '$fromDate'  AND '$toDate'";
    }
    else{
        $date_sql = "  TRUE";
    }
    
    if(!$isAdmin){
        $user_sql = "username='$username'";
    }
    else{
        $user_sql = "TRUE";
    }
    if(empty($savedSearchSqlId)){
        $savedSearchSqlId = "AND TRUE";
    }
    //All the analyzed texts X pivot
    return "SELECT DISTINCT(CAST($dateColumn AS DATE)) AS date FROM files WHERE $date_sql AND $user_sql $savedSearchSqlId AND date is not NULL  ORDER BY DATE ASC ";
}

function sql_getSumEntitySentimentPerDate($entityDocId, $sentiment, $dateColumn, $date, $savedSearchSqlId){
    global $skipDuplicate;
    if ($skipDuplicate){
        $sql_sent = "SELECT SUM(count_doc) AS sum FROM files,unique_files WHERE files.id IN ($entityDocId) AND files.id=unique_files.id AND $sentiment IS NOT NULL AND $sentiment != '' AND CAST($dateColumn AS DATE)='" . $date . "'" . $savedSearchSqlId;
    }
    else{
        $sql_sent = "SELECT COUNT(id) AS sum FROM files WHERE id IN ($entityDocId) AND $sentiment IS NOT NULL AND $sentiment != '' AND CAST($dateColumn AS DATE)='" . $date . "'" . $savedSearchSqlId;
    }
    return $sql_sent;
}

function sql_getEntityDocIdsFromTable($table, $name, $dateColumn, $date, $savedSearchSqlId){
    global $skipDuplicate,$extraColumns;
    if ($skipDuplicate && !array_key_exists($table,$extraColumns)){
        $sql_ent_date_docs = "SELECT SUM(unique_files.count_doc) AS sum FROM $table,files,unique_files WHERE $table.value='$name' AND CAST(files.$dateColumn AS DATE)='" . $date . "' AND unique_files.id=files.id AND files.id=$table.docId" . $savedSearchSqlId;
    }
    else{
        $sql_ent_date_docs = "SELECT COUNT(DISTINCT($table.docId)) AS sum FROM $table,files WHERE $table.value='$name' AND CAST(files.$dateColumn AS DATE)='" . $date . "' AND files.id=$table.docId" . $savedSearchSqlId;
    }
    return $sql_ent_date_docs;
}

function sql_personSentimentPerDate($docIds_str, $date_range, $dateFormat, $uniqueValuesTableName, $sentiment, $table, $searchPerSql, $savedSearchId, $username, $isAdmin){
    global $skipDuplicate;
    $date = convertDateArrayToString($date_range,"f." .$dateFormat);
    
    $uvTable = "";
    $fieldName = "f.id";
    $uvCondition = "";
    //for counting unique users (for example), instead of counting files
    if ($uniqueValuesTableName)
    {
        $uvTable = ", $uniqueValuesTableName AS ut";
        $fieldName = "ut.value";
        $uvCondition =" AND ut.docId = f.id ";
    }
    $sql_sent_captions = ($sentiment != "total")  ? ", GROUP_CONCAT(DISTINCT t.$sentiment  SEPARATOR ', ') AS ".strtoupper($sentiment)."_caption" : "";
    
    
    
    if ($skipDuplicate && !$uniqueValuesTableName){
        $sql_sent = "SELECT SUM(count_doc) AS " . $sentiment  . ", CAST(f.$dateFormat AS DATE) AS date_col, SUM(t.occurrences) as occ $sql_sent_captions FROM files AS f,unique_files, " . $table . " AS t, ($searchPerSql) AS pms WHERE f.id IN ($docIds_str) AND t.docId = pms.docId AND unique_files.id = f.id AND t.id = pms.instanceId ";
    }
    else{
        $sql_sent = "SELECT COUNT(DISTINCT($fieldName)) AS " . $sentiment  . ", CAST(f.$dateFormat AS DATE) AS date_col, SUM(t.occurrences) as occ $sql_sent_captions FROM files AS f, " . $table . " AS t, ($searchPerSql) AS pms $uvTable WHERE f.id IN ($docIds_str) AND t.docId = pms.docId AND t.id = pms.instanceId ";
    }
    
    if ($sentiment != "total"){
        $sql_sent.= "AND t." . $sentiment  . " > '' ";
    }
    
    $sql_sent.= "AND t.docId = f.id AND ($date)";
    if (strlen($savedSearchId) > 1){
        $sql_sent .= " AND f.id in ($savedSearchId)";
    }
    
    $sql_sent .= $uvCondition;
    
    if(!$isAdmin){
        $sql_sent .= " AND f.username='$username'";
    }
   
    $sql_sent .= " GROUP BY date_col";
    
    return $sql_sent;
}

function getMappedCaptionSql($field, $mappedCaption, $free_text_str, $limit, $docs_arr_sql){
    $freeText = empty($free_text_str) ? "TRUE" : " $mappedCaption.value = '$free_text_str'";
    return "SELECT COUNT($field.value) AS c, $mappedCaption.value AS m FROM $field, $mappedCaption
    WHERE  $field.docId IN($docs_arr_sql)  AND $field.docId=$mappedCaption.docId AND $freeText GROUP BY $field.docId ORDER BY c DESC LIMIT $limit";
}


function getMaxCaptionSql($field, $mappedCaption, $free_text_str, $limit, $docs_arr_sql){
    $freeText = empty($free_text_str) ? "TRUE" : " $mappedCaption.value = '$free_text_str'";
    $sql = "SELECT $field.value as v, $mappedCaption.value as m from $field, $mappedCaption WHERE $field.docId IN($docs_arr_sql) AND $field.docId=$mappedCaption.docId AND $freeText";
    $sql .= " GROUP BY v ORDER BY m DESC";
    
    return "SELECT m, MAX(v) AS c FROM
        ($sql)
        AS t GROUP BY m ORDER BY CONVERT(MAX(v), DECIMAL) DESC limit $limit";
}

?>