<?php

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

require_once("demosettings.php");
require_once("parseUtils.php");
require_once 'sqlUtils.php';
require_once 'write_log.php';
require_once 'globalArr.php';
include_once("authorizations.php");
require_once("tableTree.php");
require_once 'allConceptsData.php';
require_once 'allConceptArr.php';
require_once ("cacheEng.php");


if (isset($argv[1]) && $argv[1] == "initializeDB")
{
    $_REQUEST["initializeDB"] = TRUE;
	if (!IS_CLI)
		die("not authorized to initialize DB");
	
	$logLevel = IVLOG_TRACE;
	$error = initializeDB("admin", "admin123");
	//useCake should be initialized only after DB is created
	require_once("userCake/models/config.php");
	//register default admin user
	$mysqli = sqlCreateConnection("registerNewAdminUser");
	$errors = registerNewUser($mysqli, "admin", "admin123", "admin123", "a@b.c", 0);
	$errors_cr = registerNewUser($mysqli, "crawler", "crawler123", "crawler123", "crawler@b.c", 0);

	sqlClose($mysqli, "registerNewAdminUser");
	if ($error)
		array_unshift($errors, $error);
	
	if (empty($errors))
		die("Done");
	
	print_r($errors);
	return;
}
include_once("loginCheckPoint.php");

$userdetails = getUserDetails();

function getTextFieldType()
{
	return "VARCHAR(100)";// "TEXT";
}

function getLongTextFieldType()
{
    return "VARCHAR(255)";// "TEXT";
}

function addUsersStatus($mysqli){
    $sql = "SELECT username FROM userCake_Users";
    $res = sqlQuery($mysqli, $sql);
    while ($row = $res->fetch_object()){
        addUserToUserStatusTable($mysqli, $row->username);
    }
}
function addUsersSettings($mysqli, $username = null){
    //fetch default Settings first per user (UI & Visual)
    $ui_settings = getUIDefaultSettings();
    $visual_settings = getVisualDefaultSettings();
    
    if(!empty($username)){
        addToSettings($mysqli, $username, $ui_settings, $visual_settings);
        return;
    }
    //else
    $sql = "SELECT username FROM userCake_Users";
    $res = sqlQuery($mysqli, $sql);
    while ($row = $res->fetch_object()){
        addToSettings($mysqli, $row->username, $ui_settings, $visual_settings);
    }
}

function initializeDB($username, $password, $email = "a@b.c", $roleSelect = 0)
{
	global $databaseName;
	global $trace_log_file;
	
	//patch - to change the password of userspice to un encrypted
//     check_admin_plaintext();
	
	$mysqli = sqlCreateDBConnection(null);
	$response = sqlQuery($mysqli, "SHOW DATABASES LIKE '$databaseName'");
	if (!$response) {
		return "error accessing SQL server";
	}
	if ($response->num_rows) {
		return "DB already initialized";
	}
	$trace_log_folder = dirname($trace_log_file);
	if (!is_dir($trace_log_folder) && !mkdir_full(trace_log_folder, 0777, true)) {
		return "unable to create log folder";
	}
	//create DB
	$isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
	$partition_memsql = $isMemSQL ? "PARTITIONS 24" : "";
	$res = sqlQuery($mysqli, "CREATE DATABASE IF NOT EXISTS $databaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci $partition_memsql");
		
	write_to_log("INFO", "Closed connection (MainEng) of $mysqli->host_info");
	sqlClose($mysqli, 'sqlTable - initializeDB');
	//create table userCake_Users
	$mysqli = sqlCreateConnection("initializeDB");
	sqlQuery($mysqli,
	"CREATE TABLE IF NOT EXISTS `userCake_Users` (
  `User_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(150) NOT NULL,
  `Username_Clean` varchar(150) NOT NULL,
  `Password` varchar(225) NOT NULL,
  `Email` varchar(150) NOT NULL,
  `ActivationToken` varchar(225) NOT NULL,
  `LastActivationRequest` int(11) NOT NULL,
  `LostPasswordRequest` int(1) NOT NULL DEFAULT '0',
  `Active` int(1) NOT NULL,
  `Group_ID` int(11) NOT NULL,
  `SignUpDate` int(11) NOT NULL,
  `LastSignIn` int(11) NOT NULL,
  PRIMARY KEY (`User_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;
			");
	
	//create DB tables
	return createDB($mysqli);
}


function check_admin_plaintext(){
    //Construct a db userspice instance
    $db_us = sqlCreateDBConnection('userSpice');
    sqlQuery($db_us,  "UPDATE users SET password = 'admin123' WHERE username='admin'");
}

function returnLoggedError($errMsg)
{
	write_to_log("ERROR", $errMsg);
	return $errMsg;
}

function createMysqlTextDB($clean) {
    global $textDataBase;
    $mysqli_t1 = sqlCreateDBConnection($textDataBase, true);
    sqlQuery($mysqli_t1, "CREATE DATABASE IF NOT EXISTS $textDataBase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
    $mysqli_t1->close();
    $mysqli_t2 = sqlCreateDBConnection($textDataBase);
    if ($clean) {
        sqlQuery($mysqli_t2, "DROP TABLE IF EXISTS documents");
    }
    sqlQuery($mysqli_t2, "CREATE TABLE IF NOT EXISTS documents(
							id INT NOT NULL,
							text LONGTEXT,
							username TEXT,
							PRIMARY KEY (id),
							FULLTEXT KEY fullTextIndex (text)
							) ENGINE=MyISAM");
    $mysqli_t2->close();
}

function dropAllConceptsTables($mysqli, $concept_class)
{
	global $entitiesArray;
	
	foreach($concept_class->getMergedData() as $element) {
		$tableName = ontologyName2TableName($element["name"]);
		$tableName_title = $tableName;
		
		$sql= "DROP TABLE IF EXISTS $tableName_title;";
		$res = sqlQuery($mysqli, $sql);
		if($res === FALSE) {
			die("Error droping concept tables");
		}
	}
}

/**
 * creates allConecpt table that contains all the data
 * @param unknown $mysqli
 * @param unknown $clean
 * @param unknown $concept_class
 * @param unknown $engineType
 * @param unknown $prtionDclr
 * @param unknown $textFieldType
 * @param unknown $docIdIndex
 */
function createAllConceptsOneTable($mysqli, $clean, $concept_class, $engineType, $prtionDclr, $textFieldType, $docIdIndex){
    global $entitiesArray, $isMemSQL, $conceptTablesAsColumnstore;
    global $allConceptsTable;
    
    if($clean){
        $drop_sql = "DROP TABLE IF EXISTS $allConceptsTable";
        $res = sqlQuery($mysqli, $drop_sql);
    }
    
    $sql= "";
    $name_str = "name_version";
    $orgText_str = "orgText_version";
    $names_agg = " names_agg $textFieldType,";
    
    $date = "";
    $modifier = "";
    $date = "";
    $place = "";
    $placeIndex = "";
    $org = "";
    $orgIndex = "";
    
    //$tableName === "person_object"
    $modifier = "modifier TEXT,";
    //$tableName === "date"
    $date = "date date,";
    //$tableName === "ethnicity"    
    $place = "country $textFieldType,";
    $placeIndex = "KEY country_index (country),";
        
    //$tableName == "place_object,city,natural_terrain_place_object, province, neighborhood, neighborhood_quarter"
    $place = "country $textFieldType,
    province $textFieldType,
    city $textFieldType,";
    $placeIndex = "KEY country_index (country),
				   KEY province_index (province),
				   KEY city_index (city),";
        
    //$tableName === "organizational_identity"  
    $org = "clean_name $textFieldType,";
    $orgIndex = "KEY clean_name_index (clean_name),";
        
    //in case we decide to use concept tables as columnstore - we should set the var below to true in config
    $autoIncrement = $conceptTablesAsColumnstore? "" : "AUTO_INCREMENT";
    
    
    if ($clean){
        $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS coordinates");
    }
    createCoordinatesTable($mysqli, $textFieldType, "");
    
    $sql.= "CREATE TABLE IF NOT EXISTS $allConceptsTable(
    id INT NOT NULL $autoIncrement,
    docId INT NOT NULL,
    $name_str $textFieldType,
    $names_agg
    $org
    ontName $textFieldType,
    ontUrl $textFieldType,
    docKey $textFieldType,
    $date
    $modifier
    $place
    anti $textFieldType,
    pro $textFieldType,
    username $textFieldType,
    occurrences INT,
    positive $textFieldType,
    negative $textFieldType,
    $orgText_str  VARBINARY(100),
    type $textFieldType,
    concept_type INT NOT NULL,
    concept_type_source INT NOT NULL,";
    if ($isMemSQL && $conceptTablesAsColumnstore) {
        $sql .= "KEY docId_Index (docId) USING CLUSTERED COLUMNSTORE";
    } 
    else 
    {
        $sql .= "
        PRIMARY KEY (id, docId),
        $docIdIndex
        KEY name_index ($name_str),
        KEY orgText_index ($orgText_str),
        KEY ontUrl_index (ontUrl),
        $placeIndex
        $orgIndex
        KEY type_index (type),
        KEY concept_type_index (concept_type)
        ";
    }
    
    $sql .= ")
    $engineType
    $prtionDclr;";
    $res = sqlQuery($mysqli, $sql);
    if($res === FALSE) {
        die("Error creating concept tables");
    }
}

function createAllConceptsViews($mysqli, $clean, $concept_class) {
	global $entitiesArray, $isMemSQL, $conceptTablesAsColumnstore;
	global $allConceptsTable;
	
	foreach($concept_class->getMergedData() as $element) {
	    $view_name_org = $element["name"];
	    $view_name = TablesInformation::getTableName($view_name_org);
	    
	    $index_sql = "INSERT INTO concepts_indexes (concept) VALUES('$view_name')";
	    $res = sqlQuery($mysqli, $index_sql);
	    $last_id = $mysqli->insert_id;
	    
	    if($clean){
	        $drop_sql = "DROP VIEW IF EXISTS `$view_name`";
	        $res = sqlQuery($mysqli, $drop_sql);
	    } 
    
	    $view_sql = "CREATE OR REPLACE VIEW `$view_name` AS (SELECT *,
            	    COALESCE($allConceptsTable.names_agg, $allConceptsTable.name_version) AS name,
            	    COALESCE($allConceptsTable.names_agg, $allConceptsTable.orgText_version) AS orgText
            	    FROM $allConceptsTable WHERE concept_type='$last_id')";
	    
	    $res = sqlQuery($mysqli, $view_sql);
	    
	}
	
}

function createGroupView($mysqli, $clean){
    global $allConceptsTable;
    
    if($clean){
        $drop_sql = "DROP VIEW IF EXISTS group_trend";
        $res = sqlQuery($mysqli, $drop_sql);
    }
    $index_sql = "INSERT INTO concepts_indexes (concept) VALUES('group_trend')";
    $res = sqlQuery($mysqli, $index_sql);
    $last_id = $mysqli->insert_id;
    
    $view_sql = "CREATE OR REPLACE VIEW group_trend AS (SELECT *,
    COALESCE($allConceptsTable.names_agg, $allConceptsTable.name_version) AS name,
    COALESCE($allConceptsTable.names_agg, $allConceptsTable.orgText_version) AS orgText
    FROM $allConceptsTable WHERE concept_type='$last_id')";
    $res = sqlQuery($mysqli, $view_sql);
    
}

function createAllConceptsTables($mysqli, $concept_class, $engineType, $prtionDclr, $textFieldType, $docIdIndex) {
	global $entitiesArray, $isMemSQL, $conceptTablesAsColumnstore;
	
	foreach($concept_class->getMergedData() as $element) {
    		$sql= "";
    		$tableName = ontologyName2TableName($element["name"]);
    		$name_str = "name";
    		$orgText_str = "orgText";
    		$tableName_title = $tableName;
    		$names_agg = "";
    	    		
    		$date = "";			
    		$modifier = "";
    		$date = "";
    		$place = "";
    		$placeIndex = "";		
    		$org = "";
    		$orgIndex = "";
    		if ($tableName === "person_object")
    			$modifier = "modifier TEXT,";	
    		elseif ($tableName === "date")
    			$date = "date date,";
    		elseif ($tableName === "ethnicity")
    		{
    			$place = "country $textFieldType,";
    			$placeIndex = "KEY country_index (country),";
    		}
    		elseif ($tableName == "place_object" || $tableName == "city"
    				|| $tableName == "natural_terrain_place_object"
    				|| $tableName == "province"  || $tableName == "neighborhood" || $tableName == "neighborhood_quarter"){
    			$place = "country $textFieldType,
    					  province $textFieldType,					  
    					  city $textFieldType,";
    			$placeIndex = "KEY country_index (country),
    						   KEY province_index (province),						  
    						   KEY city_index (city),";
    		}
    		elseif ($tableName === "organizational_identity")
    		{
    			$org = "clean_name $textFieldType,";
    			$orgIndex = "KEY clean_name_index (clean_name),";
    		}
    		//in case we decide to use concept tables as columnstore - we should set the var below to true in config
    		$autoIncrement = $conceptTablesAsColumnstore? "" : "AUTO_INCREMENT";
    		
    		$sql.= "CREATE TABLE IF NOT EXISTS $tableName_title(
    				id INT NOT NULL $autoIncrement,
    				docId INT NOT NULL, 
    				$name_str $textFieldType,
    				$names_agg
    				$org
    				ontName $textFieldType,				
    				ontUrl $textFieldType,
    				docKey $textFieldType,
    				$date
    				$modifier
    				$place					
    				anti $textFieldType,
    				pro $textFieldType,
    				username $textFieldType,
    				occurrences INT,
    				positive $textFieldType,
    				negative $textFieldType,
    				$orgText_str  VARBINARY(100),	
    				type $textFieldType,";
    		if ($isMemSQL && $conceptTablesAsColumnstore) {
                    $sql .= "KEY docId_Index (docId) USING CLUSTERED COLUMNSTORE";
    		} else {
                    $sql .= "
    				PRIMARY KEY (id, docId),
    				$docIdIndex
    				KEY name_index ($name_str),
    				KEY orgText_index ($orgText_str),
    				KEY ontUrl_index (ontUrl),
    				$placeIndex				
    				$orgIndex
    				KEY type_index (type)
    				";
            }
            $sql .= ")
    		$engineType
    		$prtionDclr;";
			$res = sqlQuery($mysqli, $sql);
			if($res === FALSE) {
				die("Error creating concept tables");
			}
	}
}

function createDB($mysqli, $clean=true){
	global $databaseHost;
	global $databaseUser;
	global $databasePass;
	global $databaseName;
	global $textDataBase;
	global $textColumnType;
	global $isSqlCluster;
	global $TEMP_FOLDER;
	global $dropdownArr;
// 	$useSolr = getSystemSettingsProp($mysqli, "useSolr");
	global $SolRoptions;
	global $SOLR_HOME;
	global $SOLR_COLLECTION;
	global $uploadedFilesPath;
    global $shouldInsertPhrases;
    global $allConceptArr;
    $returnError = "";
            
	if( FALSE === function_exists('socket_create') ) {
		$returnError = returnLoggedError("extension php_sockets is missing!");
	}
	if( FALSE === function_exists('mb_strcut') ) {
		$returnError = returnLoggedError("extension mbstring is missing!");
	}
	if (!extension_loaded("com_dotnet"))
	{
		$returnError = returnLoggedError("Ensure your php.ini contains: extension=php_com_dotnet.dll under section [COM_DOT_NET]");
	}
	/*
	 * //imap_rfc822_parse_adrlist is unreliable. switched to 3rd party mimeparser rfc822_addresses_class
	if (!function_exists('imap_rfc822_parse_adrlist'))
		$returnError = returnLoggedError("extension php_imap.dll is missing!");
	*/

	foreach ([$TEMP_FOLDER, $uploadedFilesPath] as $folder)
		if( !is_dir($folder) )
		{
			$r = mkdir_full($folder, 0777, true);
			if (!$r)
				$returnError = returnLoggedError("Failed to create folder $folder");
		}
	
	$isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
	$engineType = $isSqlCluster? " ENGINE=NDBCLUSTER " : "";
	$referDeclr = $isMemSQL?     " REFERENCE " : "";
	$docIdIndex = $isMemSQL?     " SHARD KEY (docId)," : " KEY docId_Index (docId),";
	$shardDclrF = $isMemSQL?     " SHARD KEY (id),"    : "";
	$prtionDclr = $isMemSQL?     "" : " PARTITION BY KEY(docId) ";
	$prtionDclF = $isMemSQL?     "" : " PARTITION BY KEY(id) ";
	$textFieldType = getTextFieldType();
	$longTextFieldType = getLongTextFieldType();
	$idPrimaryKeyOrColumnStoreIndex =
				  $isMemSQL? " USING CLUSTERED COLUMNSTORE" :
				  ",				
				id INT NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (id)";
	$idDocIdPrimaryKeyOrColumnStoreIndex = str_replace("(id)", "(id,docId)", $idPrimaryKeyOrColumnStoreIndex);
	
	//delete and create documents table
	
// 	if($useSolr)
// 	{
		//create the collection
	$response = file_get_contents("http://$SolRoptions[hostname]:$SolRoptions[port]/solr/admin/cores?action=status&core=$SOLR_COLLECTION");
	if (!$response)
	{
		$returnError = returnLoggedError("Failed to connect to SolR");
	}
	else
	{
        	if(isJson($response)){
        	    $response = json_decode($response,true);
        	    $respnseArr = !empty($response['status'][$SOLR_COLLECTION]) ? 1 : 0 ;
        	    
        	}
        	else{
        	    $response = simplexml_load_string($response);
        	    $respnseArr= $response->xpath('/response/lst/lst/long[@name="uptime"]');
        	    
        	}
        	    
        	if (!$respnseArr) 
        	{
        	    $solr_core_command = OSSpecific::getInstance()->getSolrCoreCommand($SOLR_COLLECTION);
        	    
        	    write_to_log("TRACE", "SolR create collection $SOLR_COLLECTION");
        	    $return = executeCommand($SOLR_HOME, $solr_core_command);
        		if ($return)
        			$returnError = returnLoggedError("Solr create collection failed with return code: $return");
        	}
        	
        	if ($clean)
        	{
            	$solrQuery = "http://$SolRoptions[hostname]:$SolRoptions[port]/solr/$SOLR_COLLECTION/update/json";
            	$solrQuery .= "?commit=true";
            
            	$updateResponse = file_get_contents($solrQuery, false,
            		stream_context_create(array("http" => array(
            			"header" => "Content-type:application/json",
            			"method" => "POST",
            			"content" => json_encode(array(
            				"delete" => array("query" => "*:*")
            			))
            		))));
            	write_to_log("TRACE", "SolR clean response: " . $updateResponse);
            	$solrResponse = json_decode($updateResponse);
            	if (!$solrResponse || !$solrResponse->responseHeader || $solrResponse->responseHeader->status !== 0)
            		$returnError = returnLoggedError("Unexpected SolR clean response: " . $updateResponse);
    	   }
	}
// 	} else {
//             createMysqlTextDB($clean);    
//         }


	//prevent error "Too many connections"
	sqlQuery($mysqli, "SET GLOBAL max_connections = 1000");
	sqlQuery($mysqli, "SET GLOBAL net_buffer_length = 1000000");
	sqlQuery($mysqli, "SET GLOBAL max_allowed_packet = 1000000000");
	sqlQuery($mysqli, "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");
	
	
	//recommendations from http://www.bigdbahead.com/?p=103
	if (!$isMemSQL) {
            sqlQuery($mysqli, "SET GLOBAL innodb_buffer_pool_size=8589934592");
        }

    sqlQuery($mysqli, "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION");
    
//     sqlQuery($mysqli, "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'intuview' WITH GRANT OPTION;");
    //drop all View - TODO:BEN fix this
    sqlQuery($mysqli, "DROP VIEW IF EXISTS tv_parent_author;");

    createUserStatusTable($mysqli);

    
    if(!$clean || !empty($_REQUEST['initializeDB']) || filter_var($_REQUEST['include_settings'] ?? null, FILTER_VALIDATE_BOOLEAN)){
        write_to_log("INFO", "create settings");
        createAlertSettingsTable($mysqli);
        createReportSettingsTable($mysqli);
        createFileReportSettingsTable($mysqli);
        createUserSettingsTable($mysqli);
        createSystemSettingsTable($mysqli);
        createCrawlerSettingsTable($mysqli);
    }

    createAllConceptsArr($mysqli, $textFieldType);

    createAllConceptsCaptions($mysqli);
    
    $concept_class =  new AllConceptsData($mysqli, $allConceptArr, null, null);


    if ($clean) {
        $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS concepts_indexes");
    }

    $sql = "CREATE TABLE IF NOT EXISTS concepts_indexes(
                          id int(11) unsigned NOT NULL AUTO_INCREMENT,
                        concept $textFieldType NOT NULL, 
                        PRIMARY KEY (`id`,`concept`)
                            ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8";
    $res = sqlQuery($mysqli, $sql);
    
    createAllConceptsOneTable($mysqli, $clean, $concept_class, $engineType, $prtionDclr, $textFieldType, $docIdIndex);
    createAllConceptsViews($mysqli, $clean, $concept_class);
    
    createGroupView($mysqli, $clean); //for trends accumulation
	foreach ($dropdownArr as $k=>$v){
            if ($clean) {
                $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS $k");
            }

        //special table creation script
		global $specialTableCreationScript;
		if ($specialTableCreationScript && array_key_exists($k, $specialTableCreationScript))
		{
			foreach ($specialTableCreationScript[$k] as $sql)
				sqlQuery($mysqli, $sql);
			
			continue;
		}
		$additionalFields = "";
		if ($k == "from_field" || $k == "to_field" || $k == "cc_field" || $k == "bcc_field")
		{
			//TODO: index additional fields
			$additionalFields = "display_name $textFieldType NULL, email $textFieldType NULL,";
		}
                $occurrences="";
                if ($k == "phrases"){
                    $occurrences = "doc_occurrences INT NOT NULL,";
                }
		$sql = "CREATE TABLE IF NOT EXISTS $k(
				docId INT NOT NULL,
				$docIdIndex
				value $longTextFieldType NOT NULL,
				$additionalFields
                                $occurrences
				KEY value_Index (value) $idDocIdPrimaryKeyOrColumnStoreIndex
				)
				$engineType 
				$prtionDclr";
		$res = sqlQuery($mysqli, $sql);
	}
	
	//sentiment
	$sentimentArr = array("anti_instance","anti_concept","pro_instance","pro_concept");
	foreach ($sentimentArr as $v){
		if ($clean)
		$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS $v");
		
		$sql = "CREATE TABLE IF NOT EXISTS $v(
				docId INT NOT NULL,
				$docIdIndex
				value $textFieldType NOT NULL,
				instanceId INT NOT NULL,
				instanceType $textFieldType NOT NULL,
				KEY value_Index (value) $idDocIdPrimaryKeyOrColumnStoreIndex
				)
				$engineType 
				$prtionDclr";
		$res = sqlQuery($mysqli, $sql);
	}
	
	
	//creates the groups table and insert data from groups.json
	createGroupsTable($mysqli, $referDeclr);
	//old files colunm
	/*$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS file_format");
	$sql = "CREATE TABLE IF NOT EXISTS file_format(
				docId INT NOT NULL,
				$docIdIndex
				value $textFieldType NOT NULL,
				KEY value_Index (value) $idDocIdPrimaryKeyOrColumnStoreIndex
				)
				$engineType 
				$prtionDclr";
	$res = sqlQuery($mysqli, $sql);
	
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS file_type");
	$sql = "CREATE TABLE IF NOT EXISTS file_type(
				docId INT NOT NULL,
				$docIdIndex
				value $textFieldType NOT NULL,
				KEY value_Index (value) $idDocIdPrimaryKeyOrColumnStoreIndex
				)
				$engineType 
				$prtionDclr";
	$res = sqlQuery($mysqli, $sql);*/

	//Dashboard table
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS dashboard");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS dashboard(
			id INT NOT NULL AUTO_INCREMENT,
			x_point INT,
			y_point INT,
			name TEXT NOT NULL,
			type TEXT NOT NULL,			
			search_field TEXT,
			refresh_seconds INT,
			slices INT, 
			pastperiod INT,
            details JSON,
			username $textFieldType NOT NULL,						
			PRIMARY KEY (id))";
	$res = sqlQuery($mysqli, $sql);

	
	//create v2 dashboard settings
	$sql_drop = "DROP TABLE IF EXISTS dashboard_panels";
	$res = sqlQuery($mysqli, $sql_drop);
	
	$sql_panels = "CREATE TABLE dashboard_panels (
                          id int(11) unsigned NOT NULL AUTO_INCREMENT,
                          name varchar(64) DEFAULT NULL,
                          tab varchar(64) DEFAULT NULL,
                          visibility tinyint(1) unsigned NOT NULL DEFAULT 1,
                          collapsed tinyint(1) unsigned NOT NULL DEFAULT 0,
                          panelSize tinyint(4) unsigned NOT NULL DEFAULT 0,
                          panelHeight tinyint(4) unsigned NOT NULL DEFAULT 0,
                          entity varchar(64) DEFAULT NULL,
                          chartType varchar(64) DEFAULT NULL,
                          selectedSortBy varchar(64) DEFAULT NULL,
                          maxItems int(11)  unsigned NOT NULL DEFAULT 0,
                          legend tinyint(1) unsigned NOT NULL DEFAULT 0,
                    	  search_field TEXT,
                          details JSON,
                          sort tinyint(4) unsigned NOT NULL DEFAULT 0,
                          username varchar(64) DEFAULT NULL,
                          config_panel JSON,
                          custom boolean,
                          selected_instances LONGTEXT,
                          filtered_view boolean DEFAULT false,

                          PRIMARY KEY (id)
                        ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8";
	$res = sqlQuery($mysqli, $sql_panels);
	
	if ($clean)
	cleanCreateDashboardFiles($mysqli);
	
	//Cache table
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS cacheeng");
	$sql = "CREATE  $referDeclr TABLE IF NOT EXISTS cacheeng(
		id INT NOT NULL AUTO_INCREMENT,
		cache_key VARCHAR(250),
        results JSON,
		date DATETIME,
        in_process INT DEFAULT 0,

		PRIMARY KEY (id),
        UNIQUE KEY `cache_key` (`cache_key`)
        )";
	sqlQuery($mysqli, $sql);
	
	//delete and create files table
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS files");
	
	$sql = "CREATE TABLE IF NOT EXISTS files(
				id INT NOT NULL AUTO_INCREMENT,
				batch TEXT,
				filename TEXT,
				date DATETIME,
				scan_time DATETIME,							
				type TEXT,											
				pro $textFieldType,
				anti $textFieldType,
				origFolder TEXT,
				file_size TEXT,
				file_creation_date DATETIME,
				file_format TEXT,
				file_type TEXT,
				file_last_accessed_date DATETIME,
				file_last_modified_date DATETIME,
				line_count TEXT,
				token_count TEXT,				
				user_notes TEXT,
				username $textFieldType,								
				timeStamp $textFieldType,
				results TEXT, 				
				shortFilename TEXT,				
				parentFolderId INT,
				session TEXT,									
				data TEXT,				
				userId INT,				
				batch_serial_id TEXT,	
				batch_id TEXT,																			
				author $textFieldType,							
				redactedId INT,
				redactedEndId INT,
                redactedJson JSON,
				$shardDclrF
				PRIMARY KEY (id),
				KEY timeStamp_Index (timeStamp),
				KEY username_Index(username),
				KEY date_Index(date),
				KEY parentFolderId_Index (parentFolderId)
			)
			$engineType
			$prtionDclF";
	$res = sqlQuery($mysqli, $sql);
    
	//delete and create files table
	if ($clean)
	    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS file_author");
	    
	    $sql = "CREATE TABLE IF NOT EXISTS file_author(
	    id INT NOT NULL AUTO_INCREMENT,
        docid INT NOT NULL,

        value varchar(150),
        nm_info JSON DEFAULT NULL,
 	    PRIMARY KEY (id)

	    )
	    $engineType
	    $prtionDclF";
	    $res = sqlQuery($mysqli, $sql);
	//delete and create metadata table
	if ($clean)
	    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS metadata");
	    
	    $sql = "CREATE TABLE IF NOT EXISTS metadata(
        docid INT NOT NULL,
	    metadata LONGBLOB,   
	    PRIMARY KEY (docid)

	    )
	    $engineType
	    $prtionDclr";
	    $res = sqlQuery($mysqli, $sql);
	    
    //delete and create alerts table
    if ($clean)
        $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS alerts");
        
        $sql = "CREATE TABLE IF NOT EXISTS alerts(
        docid INT NOT NULL,
        doc_username TEXT,
        entities_alerts LONGBLOB DEFAULT NULL,
        concepts_alerts LONGBLOB DEFAULT NULL,
        instances_alerts LONGBLOB DEFAULT NULL,
        keywords_alerts LONGBLOB DEFAULT NULL,
        date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	    reviewed BOOLEAN,
		username $textFieldType,		
        						
        PRIMARY KEY (docid)
        
        )
        $engineType
        $prtionDclr";
        $res = sqlQuery($mysqli, $sql);
	    
	//Emotion table
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS emotion");
	
	$sql = "CREATE TABLE IF NOT EXISTS emotion(	
				id INT NOT NULL AUTO_INCREMENT,							
				docid INT NOT NULL,
				$docIdIndex
				anti DECIMAL( 5, 2 ) NOT NULL,
				pro DECIMAL( 5, 2 ) NOT NULL,				
				e_token DECIMAL( 5, 2 ),
				e_concept DECIMAL( 5, 2 ),
				PRIMARY KEY (id, docId)																								
			)
			$engineType
			$prtionDclF";
	$res = sqlQuery($mysqli, $sql);
		
	//duplicate table
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS duplicate_files");
	
	$sql = "CREATE TABLE IF NOT EXISTS duplicate_files(
				id INT,				
				hashCode VARBINARY(100),
				duplicatedDocId INT,													
				PRIMARY KEY (id, hashCode),				
				$shardDclrF
				KEY hashCode_Index(hashCode),
				KEY duplicatedDocId_Index(duplicatedDocId)				
			)
			$engineType
			$prtionDclF";
	$res = sqlQuery($mysqli, $sql);
	
	//unique file id
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS unique_files");
	
	$sql = "CREATE TABLE IF NOT EXISTS unique_files(
				id INT,				
				date DATETIME,				
				username $textFieldType,	
				count_doc INT,
				$shardDclrF											
				PRIMARY KEY (id),				
				KEY username_Index(username),
				KEY date_Index(date)				
			)
			$engineType
			$prtionDclF";
	$res = sqlQuery($mysqli, $sql);
	
	
	//delete and create senddoc_file_id table
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS senddoc_file_id");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS senddoc_file_id (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))
			$engineType
			$prtionDclF";
	$res = sqlQuery($mysqli, $sql);
	if ($clean)
	$res = sqlQuery($mysqli, "INSERT INTO senddoc_file_id VALUES ()");

	//locked files
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS lockedfiles");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS lockedfiles(
				id INT NOT NULL AUTO_INCREMENT,
				user $textFieldType,
				docId INT,
				date DATETIME,
				PRIMARY KEY (id)
			)";
	$res = sqlQuery($mysqli, $sql);
	if ($clean)
	createLockedFiles($mysqli);
	
	//saved search
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS savedsearch");
	
	//since it's called outside the REQUEST - we can find if its v1 or v2 call - so leave both types of columns
	$search_values =   "search_values JSON DEFAULT NULL, 
        	            per TEXT,
        				 org TEXT,
        				 loc TEXT,";
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS savedsearch(
				id INT NOT NULL AUTO_INCREMENT,
				name TEXT,
				label TEXT,
				lastSearch LONGTEXT,
                $search_values
				parameters TEXT, 
				username $textFieldType,				
				date DATETIME,
				isrunning BOOLEAN,
				PRIMARY KEY (id))";
	$res = sqlQuery($mysqli, $sql);	
	
	//saved searches
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS savedsearchid");
	
	$sql = "CREATE TABLE IF NOT EXISTS savedsearchid(
				id INT NOT NULL,
				docid INT NOT NULL,
				PRIMARY KEY (id, docId),
				$docIdIndex
                score FLOAT DEFAULT 0,
				KEY id_Index (id))
				$engineType
				$prtionDclr";
	$res = sqlQuery($mysqli, $sql);
	
	//foldertree
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS foldertree");
	
	createFolderTreeTable($mysqli);
	
	if ($clean)
	createSingleFilesTree($mysqli);

	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS filetags");
	
	$sql = "CREATE TABLE IF NOT EXISTS filetags(
				id INT NOT NULL AUTO_INCREMENT,
				username $textFieldType,
				docId INT NOT NULL,
				exclusive boolean,
				groupName $textFieldType,
				name $textFieldType,				
				PRIMARY KEY (id, docId),
				$docIdIndex
				KEY name_index (name)
			)
			$engineType
			$prtionDclr";
	$res = sqlQuery($mysqli, $sql);
	
// 	if ($clean)
// 	createDefaultsFileTagsTable($mysqli);

    if(!empty($_REQUEST['initializeDB']) || filter_var($_REQUEST['include_settings'] ?? null, FILTER_VALIDATE_BOOLEAN)){
            
	   $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS entitiesmanage");
    }
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS entitiesmanage(
				id INT NOT NULL AUTO_INCREMENT,				
				entity $textFieldType,
				name $textFieldType,
				aggregatedName TEXT,
				aggregatedManual TEXT,	
				sumPossible INT,
                username $textFieldType,								
				PRIMARY KEY (id))";
	$res = sqlQuery($mysqli, $sql);
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS hide_entities(
	id INT NOT NULL AUTO_INCREMENT,
	entity $textFieldType,
	names TEXT,
	username $textFieldType,
	PRIMARY KEY (entity))";
	$res = sqlQuery($mysqli, $sql);
	
	
	if ($clean)
	    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS crossentities");
	    
	    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS crossentities(
	    entity $textFieldType,
	    orgConcept $textFieldType,
	    destConcept $textFieldType,

	    PRIMARY KEY (entity,orgConcept))";
	    $res = sqlQuery($mysqli, $sql);
	
	if ($clean)
	    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS entitiesids");
	    
	    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS entitiesids(
	    id INT NOT NULL,
	    entity_id INT,
	    PRIMARY KEY (id,entity_id))";
// 	    $res = sqlQuery($mysqli, $sql); //for now dont create this table

	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS configurationmanage");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS configurationmanage(
				id INT NOT NULL AUTO_INCREMENT,				
				page $textFieldType,
				operation $textFieldType,
				details TEXT,											
				PRIMARY KEY (id))";
	$res = sqlQuery($mysqli, $sql);

	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS inter_entity_relationship");
	
	$sql = "CREATE TABLE IF NOT EXISTS inter_entity_relationship(
				id INT NOT NULL AUTO_INCREMENT,
				docId INT NOT NULL, 
				relationship TEXT,
				relationship_url TEXT,
				relationship_text TEXT,
				subject $textFieldType,
				subject_agg $textFieldType,
				subject_url TEXT,
				subject_text TEXT,
				subject_id INT,
				subject_table TEXT,
				relation_type TEXT,
				relation_type_inst TEXT,
				object $textFieldType,
				object_agg $textFieldType,
				object_url TEXT,
				object_text TEXT,
				object_id INT,
				object_table TEXT,
				PRIMARY KEY (id, docId),
				$docIdIndex
				KEY object_index (object),
				KEY subject_index (subject)
			)
			$engineType
			$prtionDclr";
	$res = sqlQuery($mysqli, $sql);
	
	if ($clean)
	    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS sub_relations");
	    
	    $sql = "CREATE TABLE IF NOT EXISTS sub_relations(

	    relationship TEXT,
	    sub_relations TEXT
	    )
	    $engineType";
	    $res = sqlQuery($mysqli, $sql);
		
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS slotdata");
	
	$sql = "CREATE TABLE IF NOT EXISTS slotdata(
				docId INT NOT NULL,				
				conceptName $textFieldType,
				conceptUrl $textFieldType,";
	global $slotToCaption;
	foreach ($slotToCaption as $k => $v){
		$e = str_replace("-", "_", $k);
		if ($e == "analyst_notes" || $e == "links_to_people")
			$sql .= "$e VARCHAR(500),";
		else
		$sql .= "$e $textFieldType,";
	}
	$sql .= "
			KEY caption_index (caption) $idDocIdPrimaryKeyOrColumnStoreIndex
			)
			$engineType
			$prtionDclr";
	$res = sqlQuery($mysqli, $sql);
	
	//delete and create search
	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS search");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS search(	
				id INT NOT NULL AUTO_INCREMENT,			
				lastSearch LONGTEXT,
				parameters TEXT,
				label TEXT,
				per TEXT,
				org TEXT,
				loc TEXT,
				userName $textFieldType,				
				PRIMARY KEY (id))";
	$res = sqlQuery($mysqli, $sql);
	if ($clean)
	createSearch($mysqli);
	
	/*$sql = "INSERT INTO search(lastSearch) VALUES(\"\")";
	$res = sqlQuery($mysqli, $sql);*/

	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS name_alternatives");
	
	$sql = "CREATE TABLE IF NOT EXISTS name_alternatives (
				id INT NOT NULL AUTO_INCREMENT,
				watchListName $textFieldType,
				userName $textFieldType,
				personId INT NOT NULL,
				docId INT NOT NULL,
				personTable TEXT NOT NULL,
				PersonName $textFieldType," .
				join(" $textFieldType, ", getNAfields()) . " $textFieldType,
				PRIMARY KEY (id, personId, docId),
				$docIdIndex
				KEY personId_Table_index (personId, personTable(1)),
				KEY family_name_index (family_name),
				KEY given_name_index (given_name),
				KEY son_index (son)
						)
			$engineType
			$prtionDclr";
	$res = sqlQuery($mysqli, $sql);

	if ($clean)
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS nc_additional_info");
	
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS nc_additional_info (
				id INT NOT NULL AUTO_INCREMENT,
				name_component $textFieldType,
				info_type $textFieldType,
				value $textFieldType,
				PRIMARY KEY (id),
				KEY nc_value_index (name_component,value)
			)";
	$res = sqlQuery($mysqli, $sql);

    
	//Insert default column 
	if ($clean)
	{
	$sql = "DELETE FROM configurationmanage WHERE page=\"ListFiles\" AND operation=\"column\"";
	$res = sqlQuery($mysqli, $sql);
	$sql = "INSERT INTO configurationmanage (page,operation,details) VALUES (\"ListFiles\",\"column\",\"File Name;Date of Writing;Theatre;Language;Topic;\")";
	$res = sqlQuery($mysqli, $sql);
	}
    TableTree::createTreeTables($mysqli,$clean);   
    
    /**
     * name matcher tables
     */
//     createNMPatchTables($mysqli, $referDeclr);
    createPersonsWLDB($mysqli, $databaseName);
    
    sqlClose($mysqli, 'sqlTable - init');
    $clean_crawler = !empty($_REQUEST['include_crawler']) &&  $_REQUEST['include_crawler'] == 'true' ? true : false;
    
    createCrawlerTables($engineType, $prtionDclF, $clean_crawler);

	write_to_log("INFO", "Finished createDB with error=$returnError");
	return $returnError;
}

function createCoordinatesTable($mysqli, $textFieldType, $referDeclr){
    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS coordinates (
				id INT NOT NULL AUTO_INCREMENT,

    location $textFieldType,
    coordinates $textFieldType,
				PRIMARY KEY (id),

        UNIQUE KEY `location` (`location`)
    )";
    $res = sqlQuery($mysqli, $sql);
    
    $array = json_decode(file_get_contents('./files/coordinates.json',true));
    $values = [];

    foreach($array as $location=>$row) {
        $coord = "$row[0], $row[1]";
        $values[] = "(\"$location\", \"$coord\")";
        if(count($values) == 1000){
            $values_str = implode(',', $values);
            $query =    "INSERT INTO coordinates (location,coordinates) VALUES $values_str  ON DUPLICATE KEY UPDATE location=location";
            sqlQuery($mysqli, $query);
            $values = [];
        }
    }
    if(!empty($values)){
        $values_str = implode(',', $values);
        $query =    "INSERT INTO coordinates (location,coordinates) VALUES $values_str  ON DUPLICATE KEY UPDATE id=id";
        sqlQuery($mysqli, $query);
    }
}

function createPersonsWLDB($mysqli, $databaseName){
        require_once '../iventitymatcherhtm/utilities/sql_utilies.php';
            
        $columns_json = json_decode(file_get_contents('../iventitymatcherhtm/jsons/columns.json',true));
        $columns_arr = json_decode(json_encode($columns_json), TRUE);
        $resultsCols = $columns_arr["resultsCols"];
        
        $possible_same_person_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/possible_same_person_rules.json',true));
        
        $relations_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/relations_rules.json',true));
        
        $rules_json = init_rules_wl();
        $ret = createWLDatabase($mysqli, $databaseName, $resultsCols, $rules_json, true, true);
}


function createFbCrawlerTable($connection, $clean){

   $textFieldType = getTextFieldType();
   sqlQuery ($connection, "CREATE TABLE IF NOT EXISTS fb_folder_for_analysis(
			id INT NOT NULL AUTO_INCREMENT,
			folder_name $textFieldType,			
			PRIMARY KEY (id))" ); 
}

function createMutexTable($connection, $clean)
{
    if($clean){
        $res = sqlQuery($connection, "DROP TABLE IF EXISTS mutex");
    }
	//mutex table - for ANSI SQL variation for INSERT IGNORE
	sqlQuery ( $connection, 'CREATE TABLE IF NOT EXISTS mutex(
    	i INT NOT NULL PRIMARY KEY
	);' );
	$res = sqlQuery($connection, "SELECT * FROM mutex");
	if ($res && $res->num_rows == 0)
		sqlQuery ( $connection, 'INSERT INTO mutex(i) VALUES (0), (1)' );
}

function createDefaultsFileTagsTable($mysqli){
	global $userdetails;	
	//leagal
	
// 	$arr_exclusive['Relevance'] = array("Relevant", "Relevant - Hot", "Not Relevant");
// 	$arr_exclusive['Priority'] = array("High", "Medium", "Low");
// 	$arr_not_exclusive['Privileged'] = array("Privileged");
	//intelegance
	//TODO
	$arr_exclusive['Refer to management'] = array("Refer", "Do not refer");
	$arr_exclusive['Priority'] = array("High", "Medium", "Low");
	$arr_not_exclusive['Review'] = array("Reviewed");
	$arr_not_exclusive['Projects'] = array("Project A","Project B","Project C");
	$arr_not_exclusive['Follow up'] = array("Team A","Team B","Team C");
	$arr_not_exclusive['Topics'] = array("Economy","Politics","Terrorism");//Reviewed
	
	
	$sql = '';
	//exclusive
	foreach ($arr_exclusive as $tagGroup_key => $tagGroup_val){
		foreach ($tagGroup_val as $tag){
			$sql .= "INSERT INTO filetags(docId, exclusive, groupName, name) VALUES (0, true, \"$tagGroup_key\", \"$tag\");";
		}
	}
	//Not exclusive
	foreach ($arr_not_exclusive as $tagGroup_key => $tagGroup_val){
		foreach ($tagGroup_val as $tag){
			$sql .= "INSERT INTO filetags(docId, exclusive, groupName, name) VALUES (0, false, \"$tagGroup_key\", \"$tag\");";
		}
	}
	
	$result = sqlMultiQuery($mysqli, $sql);	
	
}

function createUserStatusTable($mysqli){
	global $databaseHost;
	global $databaseUser;
	global $databasePass;
	global $databaseName;
	$textFieldType = getTextFieldType();

	//delete and create user_status
	 
	$res = sqlQuery($mysqli, "DROP TABLE IF EXISTS user_status");
	$sql = "CREATE TABLE IF NOT EXISTS user_status(				
				username $textFieldType, 
				docNumber INT,
				fileSizeSum INT,
				isAddingFiles INT,
				isRunning INT,
				last_time_modified DATETIME)";
	$res = sqlQuery($mysqli, $sql);

}

function createUserSettingsTable($mysqli){
    $textFieldType = getTextFieldType();

    
    //delete and create user_status
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS user_settings");
    $sql = "CREATE TABLE IF NOT EXISTS user_settings(
    username $textFieldType,
    flavor $textFieldType,
    ui_settings JSON,
    visual_settings JSON,
    isRunning INT,
    last_time_modified DATETIME)";
    $res = sqlQuery($mysqli, $sql);
  
   
}

function createAlertSettingsTable($mysqli){
    
    $textFieldType = getTextFieldType();
    $alerts_settings = json_decode(getAlertSettings(), true);
    
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS alerts_settings");
    $sql = "CREATE TABLE IF NOT EXISTS alerts_settings(
    prop $textFieldType,
    label $textFieldType,
    type $textFieldType,
    value LONGTEXT DEFAULT NULL,
    description LONGTEXT
    )";
    $res = sqlQuery($mysqli, $sql);
    //fill user_status table with username
    foreach($alerts_settings as $system_element){
        $prop = $system_element["prop"];
        $label = $system_element["label"];
        $description = $system_element["description"];
        $type = $system_element["type"];
        
        $val = $system_element["value"];
        if(is_bool($val)){
            $val = $val ? 'true' : 'false';
        }
        if(empty($val)){
            $val = "null";
        }
        
        $sql = "INSERT INTO alerts_settings (prop, label, type, value, description) VALUES (\"$prop\",\"$label\", \"$type\", \"$val\", \"$description\" )";
        $r = sqlQuery($mysqli, $sql);
    }
    
}

function createReportSettingsTable($mysqli){
    
    $textFieldType = getTextFieldType();
  
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS report_settings");
    $sql = "CREATE TABLE IF NOT EXISTS report_settings(
    savedSearch $textFieldType,
    concepts $textFieldType,
    metadata $textFieldType,
    days INT,
    username $textFieldType
    )";
    $res = sqlQuery($mysqli, $sql);
    
}

function createFileReportSettingsTable($mysqli){
    
    $textFieldType = getTextFieldType();
    
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS file_report_settings");
    $sql = "CREATE TABLE IF NOT EXISTS file_report_settings(
    domain $textFieldType,
    category $textFieldType,
    present boolean)";
    $res = sqlQuery($mysqli, $sql);
    
    $textFieldType = getTextFieldType();
    $report_settings = json_decode(getFileReportSettings(), true);
    
    foreach($report_settings as $category=>$domains){
        foreach($domains as $domain){
            $sql = "INSERT INTO file_report_settings (domain,category,present) VALUES (\"$domain\",\"$category\",1)";
            $r = sqlQuery($mysqli, $sql);      
        }
    }
    
}

function createSystemSettingsTable($mysqli){
   
    $textFieldType = getTextFieldType();
    $system_settings = json_decode(getSystemSettings(), true);
    
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS system_settings");
    $sql = "CREATE TABLE IF NOT EXISTS system_settings(
    prop $textFieldType,
    label $textFieldType,
    type $textFieldType,
    value $textFieldType,
    description LONGTEXT
    )";
    $res = sqlQuery($mysqli, $sql);
    //fill user_status table with username
    foreach($system_settings as $system_element){
        $prop = $system_element["prop"];
        $label = $system_element["label"];
        $description = $system_element["description"];
        $type = $system_element["type"];
        
        $val = $system_element["value"];
        if(is_bool($val)){
            $val = $val ? 'true' : 'false';
        }
        if(empty($val)){
            $val = "null";
        }
        
        $sql = "INSERT INTO system_settings (prop, label, type, value, description) VALUES (\"$prop\",\"$label\", \"$type\", \"$val\", \"$description\" )";
        $r = sqlQuery($mysqli, $sql);
    }
    
}

function createCrawlerSettingsTable($mysqli){
    
    $textFieldType = getTextFieldType();
//     $system_settings = json_decode(getCrawlerSettings(), true);
    
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS crawler_settings");
    $sql = "CREATE TABLE IF NOT EXISTS crawler_settings(
prop TEXT,
    label TEXT,
    track LONGTEXT,
    locations TEXT,
    follow LONGTEXT,
    languages longtext,
    description TEXT,
    isRunning boolean,
    error TEXT

    )";
    $res = sqlQuery($mysqli, $sql);
    $sql = "INSERT INTO crawler_settings (prop, label, description, isRunning) VALUES (\"crawler\", \"Crawler\", \"your twitter crawler\",0)";
    $res = sqlQuery($mysqli, $sql);
    
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS filter_settings");
    $sql = "CREATE TABLE IF NOT EXISTS filter_settings(
    prop TEXT,
    label TEXT,
    min_rules INT,
    country_code TEXT,
    languages longtext,
    description TEXT,
    isRunning boolean
    )";
    $res = sqlQuery($mysqli, $sql);
    $sql = "INSERT INTO filter_settings (prop, label, description, isRunning) VALUES (\"filter\", \"Filter\", \"your twitter filter\",0)";
    $res = sqlQuery($mysqli, $sql);
}

function addUserToUserStatusTable($mysqli, $username){   
    $sql = "INSERT INTO user_status (username,docNumber,fileSizeSum) VALUES (\"$username\",0,0)";
	$r = sqlQuery($mysqli, $sql);	
}

function addToSettings($mysqli, $username, $ui_settings, $visual_settings){
    global $default_flavor;
    $ui_json_encoded = $mysqli->real_escape_string($ui_settings);
    $visual_json_encoded = $mysqli->real_escape_string($visual_settings);
    
    
    $sql = "INSERT INTO user_settings (username,flavor,ui_settings,visual_settings) VALUES (\"$username\",\"$default_flavor\",\"$ui_json_encoded\",\"$visual_json_encoded\")";
    $r = sqlQuery($mysqli, $sql);
}

function createAllConceptsArr($mysqli, $textFieldType){
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS allconceptsarr");
    
    $sql = "CREATE TABLE IF NOT EXISTS allconceptsarr(
    flavor $textFieldType,
    data JSON,
    PRIMARY KEY (flavor),
	UNIQUE KEY (flavor))";
    $res = sqlQuery($mysqli, $sql);
    
    require_once 'allConceptArr.php';
    global $allConceptArr;
    
    foreach($allConceptArr as $flavor=>$data){        
        $json_data = $mysqli->real_escape_string(json_encode($data,true));
        $sql = "INSERT INTO allconceptsarr (flavor, data) VALUES(\"$flavor\", \"$json_data\")";
        $res = sqlQuery($mysqli, $sql);
        
    }
}

function createAllConceptsCaptions($mysqli){
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS allconceptscaptions");
    
    $sql = "CREATE TABLE IF NOT EXISTS allconceptscaptions(
    concept varchar(150),
    caption varchar(150),
    PRIMARY KEY (concept))";
    $res = sqlQuery($mysqli, $sql);
    
    $file = fopen('./files/concepts_caption.csv', 'r');
    
    while ($row = fgetcsv($file, 1024, "\t")) {
        $row = array_filter($row);
        if(count($row) != 2){
            write_to_log("ERROR", json_encode($row,true));
        }
        $sql = "INSERT INTO allconceptscaptions (concept, caption) VALUES(\"$row[0]\", \"$row[1]\")  ON DUPLICATE KEY UPDATE concept=concept";
        $res = sqlQuery($mysqli, $sql);
    }
    fclose($file);
    
}

function getAllUsers($mysqli){
	global $databaseHost;
	global $databaseUser;
	global $databasePass;
	global $databaseName;
	
	$query = "SELECT Username FROM userCake_Users";
	$res = sqlQuery($mysqli, $query);
	$userName = array();
	while ($row = $res->fetch_object()){	
		$userName[$row->Username] = $row->Username;
	}
	return $userName;
}


function cleanCreateDashboardFiles($mysqli, $username=null){
    global $userdetails;
    //copy definition from dashboard template user, if defined
    global $dashboardTemplateUsername;
    global $defaultDashboradNames;
   
    $names_arr = fetchFromDefaultUser($mysqli, $dashboardTemplateUsername, $username);
    $names = $names_arr['names'];
    $name2row = $names_arr['name2row'];
    
    
    if (empty($username)) {
        $userNameArr = getAllUsers($mysqli);   
    } else {
        $userNameArr[$username] = $username;
    }

    foreach ($userNameArr as $username) {
        //  create files
        $res = sqlQuery($mysqli, "INSERT INTO dashboard_panels (NAME, tab, visibility, collapsed, username) VALUES(\"overview\", \"dashboard\", 0,0, \"$username\")");
        
        $names = isset($names) ? array_merge($names, $defaultDashboradNames) : $defaultDashboradNames; //merge w/ names from $dashboardTemplateUsername
        //the below is not needed, since $dashboardTemplateUsername is more convenient
        //allow changing default dashboard
        global $defaultDashboardDefinition;
        global $addSystemStatus;
        
        if(!empty($defaultDashboardDefinition) && $defaultDashboardDefinition !== $username){
            $names = array_merge($names, $defaultDashboardDefinition);
        }

        $query_statistics = "INSERT INTO dashboard (x_point,y_point,name,type,refresh_seconds,username) 
								VALUES (0,0,\"key_stats\",\"statistics\",360, \"$username\")";
        $res = sqlQuery($mysqli, $query_statistics);
        
        $x = 0;
        $y = 1;
        if($addSystemStatus){
            $systemStatus_query = "INSERT INTO dashboard (x_point,y_point,name,type,refresh_seconds,username)
    								VALUES (0,1,\"system_status\",\"status\",360, \"$username\")";
            $res = sqlQuery($mysqli, $systemStatus_query);
            $y++;
        }
        

        foreach ($names as $name => $properties) {
            if (empty($properties)){
                continue;
            }

            $properties['name'] = $name;
       
            $searchField = $properties["searchField"] ?? null;
            $slices = $properties["slices"] ?? null;

            // insert into database
            if ($y > 2) {
                $y = 0;
                $x++;
            }
            if (array_key_exists($name, $name2row)) {
                $row = $name2row[$name];
                if (property_exists($row, "x_point")){
                    $x = $row->x_point;
                }
                if (property_exists($row, "y_point")){
                    $y = $row->y_point;
                }
                if (property_exists($row, "search_field")){
                    $searchField = $row->search_field;
                }
                if (property_exists($row, "slices")){
                    $slices = $row->slices;
                }
                if (property_exists($row, "sort_by")){
                    $sort_by = $row->sort_by;
                }
                if (property_exists($row, "display_type")){
                    $display_type = $row->display_type;
                }
            }
            if (empty($slices)){
                $slices = "NULL";
            }
            if (empty($sort_by)){
                $sort_by = 0;
            }
            if (empty($display_type)){
                $display_type = 0;
            }

            $trendType = array_key_exists('trendType', $properties) ? $properties['trendType'] : "pie";
            if ($trendType == "graph"){
                $trendType = "trend";
            }
            
            //v2 - dashboard panels
            setDashboardPanel($mysqli, $name, $username, $properties, $searchField);
            
            $query = sprintf("INSERT INTO dashboard (x_point,y_point,name,type,refresh_seconds,search_field,slices,username) 
						  VALUES (%s, %s, \"$name\", \"$trendType\", 360, \"$searchField\", $slices, \"$username\")", $x === NULL ? "NULL" : $x, $y === NULL ? "NULL" : $y);
            $res = sqlQuery($mysqli, $query);
            setTableElementData($mysqli, 'dashboard', $name, 'details', $properties, 'username', $username);
            
            $y++;
        }
    }
    
    
}

/**
 * sets the specific panel for V2 
 * @param unknown $mysqli
 * @param unknown $name
 * @param unknown $username
 * @param unknown $properties
 * @param unknown $searchField
 */
function setDashboardPanel($mysqli, $name, $username, $properties, $searchField){
    global $details_panels;

    $panel_details = $details_panels[$name];
    $visibility = $panel_details['visibility'];
//     $collapsed = $panel_details['collapsed'];
    $panelSize = $panel_details['panelSize'];
    $panelHeight = $panel_details['panelHeight'];
    $entity = $panel_details['entity'];
    $chartType = $panel_details['chartType'];
    $selectedSortBy = $panel_details['selectedSortBy'];
    $maxItems = $panel_details['maxItems'];
    $legend = $panel_details['legend'];
    $sort = $panel_details['sort'];
    $panel_sql = "insert  into dashboard_panels(name, tab,visibility,collapsed, panelSize,panelHeight,entity,chartType,selectedSortBy,maxItems,legend, search_field, sort,username) values
    ('$name', 'dashboard',$visibility, 0, $panelSize,$panelHeight,'$entity','$chartType','$selectedSortBy',$maxItems,$legend,'$searchField',$sort,'$username'),
    ('$name', 'search',$visibility, 1, $panelSize,$panelHeight,'$entity','$chartType','$selectedSortBy',$maxItems,$legend,'$searchField',$sort,'$username')";
    $res = sqlQuery($mysqli, $panel_sql);
    setTableElementData($mysqli, 'dashboard_panels', $name, 'details', $properties, 'username', $username);
}

function cleanDBSpecificUser($mysqli, $user){
	global $databaseHost;
	global $databaseUser;
	global $databasePass;
	global $databaseName;
	require_once 'settings.php';
	$settings_class =  new Settings($mysqli, $user);
	$allConceptArrDB = $settings_class->getAllConceptsArr();
	
	$res = sqlQuery($mysqli, "DELETE FROM files WHERE username=\"$user\"");
	$concept_class =  new AllConceptsData($mysqli, $allConceptArrDB, null, null);
	foreach($concept_class->getMergedData() as $element){
    		$tableName = ontologyName2TableName($element["name"]);
    
    		$res = sqlQuery($mysqli, "DELETE FROM $tableName WHERE username=\"$user\"");	
   }
	
}
function getFirstCDATAChildText($element){
  	$node = $element->firstChild;
  	while($node != null && $node->nodeType != XML_CDATA_SECTION_NODE){
  		$node = $node->nextSibling;
  	}
  	if($node != null){
  		return $node->wholeText;
  	}
  	return "";
  }

function createLockedFiles($mysqli, $username=null){
	if (empty($username))
	    $userNameArr = getAllUsers($mysqli);
	else{ 
		$userNameArr[$username] = $username;	
	}	
	foreach ($userNameArr as $user){	
		$sql = "INSERT INTO lockedfiles(user) VALUES('$user')";
		$res = sqlQuery($mysqli, $sql);		
	}
}
  
function createSingleFilesTree($mysqli, $username=null){
	if (empty($username))
	    $userNameArr = getAllUsers($mysqli);
	else 
		$userNameArr[$username] = $username;
	
	foreach ($userNameArr as $user){	
		createUserSingleFilesTree($mysqli,$user); //also create save search		
	}

}

function createSearch($mysqli, $username=null){	
	global $dashboardTemplateUsername;
	global $lastSearchNameDefault;
	if (empty($username))
	    $userNameArr = getAllUsers($mysqli);
	else {
		$userNameArr[$username] = $username;
	}
		
	foreach ($userNameArr as $user){	
		$sql = "INSERT INTO search(lastSearch,userName) VALUES(\"\",'$user')";
		$res = sqlQuery($mysqli, $sql);	
		$sql = "INSERT INTO savedsearch(name,lastSearch,userName) VALUES('$lastSearchNameDefault',\"\",'$user')";
		$res = sqlQuery($mysqli, $sql);		
		//copy saved searches from dashboard template username
		if (!empty($dashboardTemplateUsername))
		{
			//idea taken from saveSearchToFiles().
			$sql = "INSERT INTO savedsearch(name, parameters, username, date, org, loc, per) " .
					"SELECT name, parameters, '$user', date, org, loc, per FROM savedsearch WHERE username = '$dashboardTemplateUsername' AND name IS NOT NULL";
			$res = sqlQuery($mysqli, $sql);

			//insert saved_search to foldertree
			$res = sqlQuery($mysqli, "SELECT id FROM foldertree WHERE Name = 'savedsearch' AND username='$user'");
			
			if (!$res || !$res->num_rows)
			{
				write_to_log("ERROR", "couldn't get/create 'saved searches' folder for user $user");
				continue;
			}
			
			$savedSearchesId = $res->fetch_object()->id;
		
			$res = sqlQuery($mysqli, "SELECT name FROM savedsearch WHERE username = '$dashboardTemplateUsername' AND name IS NOT NULL");
			foreach ($res->fetch_all() as $row)
			{
				$searchName = $row[0];
				$res = sqlQuery($mysqli, "INSERT INTO foldertree(Name, ParentId, username) VALUES('save_search::$searchName', $savedSearchesId, '$user')");
			
				$res = sqlQuery($mysqli, "SELECT id FROM foldertree WHERE Name = 'save_search::$searchName' AND username='$user'");
				$newSearchId = $res->fetch_object()->id;
			
				$res = sqlQuery($mysqli, "INSERT INTO foldertree(Name, ParentId, username) VALUES('save_search::Dynamic', $newSearchId, '$user')");
				$res = sqlQuery($mysqli, "INSERT INTO foldertree(Name, ParentId, username) VALUES('save_search::Static', $newSearchId, '$user')");
			}
		}
	}

}

function registerNewUser($mysqli, $username, $password, $confirm_pass, $email, $roleSelect)
{
	$errors = array();
	//Perform some validation
	//Feel free to edit / change as required
	
	if(minMaxRange(5,50,$username))
	{
		$errors[] = lang("ACCOUNT_USER_CHAR_LIMIT",array(5,50));
	}
	if(minMaxRange(8,50,$password) && minMaxRange(8,50,$confirm_pass))
	{
		$errors[] = lang("ACCOUNT_PASS_CHAR_LIMIT",array(8,50));
	}
	else if($password != $confirm_pass)
	{
		$errors[] = lang("ACCOUNT_PASS_MISMATCH");
	}
	if(!isValidEmail($email))
	{
		$errors[] = lang("ACCOUNT_INVALID_EMAIL");
	}
	//End data validation
	if(count($errors) == 0)
	{
		//Construct a user object
		$user = new User($username,$password,$email);
	
		//Checking this flag tells us whether there were any errors such as possible data duplication occured
		if(!$user->status)
		{
			if($user->username_taken) $errors[] = lang("ACCOUNT_USERNAME_IN_USE",array($username));
			if($user->email_taken) 	  $errors[] = lang("ACCOUNT_EMAIL_IN_USE",array($email));
		}
		else
		{
			//Attempt to add the user to the database, carry out finishing  tasks like emailing the user (if required)
			if(!$user->userCakeAddUser($roleSelect))
			{
				if($user->mail_failure) $errors[] = lang("MAIL_ERROR");
				if($user->sql_failure)  $errors[] = lang("SQL_ERROR");
			}
	
			//add user to tables
			addNewUser($mysqli, $username);
			
		}
	}
	return $errors;
}

function addNewUser($mysqli, $username){	
	createSingleFilesTree($mysqli, $username);
	createSearch($mysqli, $username);
	cleanCreateDashboardFiles($mysqli, $username);
	addUserToUserStatusTable($mysqli, $username);
	addUsersSettings($mysqli, $username);
	
	createLockedFiles($mysqli, $username);
	
}

function getAlertSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'alerts_settings.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}

function getFileReportSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'report.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}
function getSystemSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'system_settings.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}

function getCrawlerSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'crawler_settings.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}

//reads demosettings and fetch the default settings
function getUIDefaultSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'ui_settings.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}

function getVisualDefaultSettings(){
    $vars = file_get_contents('settings'.DIRECTORY_SEPARATOR.'visual_settings.json', true);
    $vars = json_decode($vars, true);
    return json_encode($vars, true);
}

function createCrawlerTables($engineType, $prtionDclF, $clean){
	global $connectionOverrides;
	$crawlerDbName = $connectionOverrides["crawlerDB"]["db"];
	$connection = sqlCreateDBConnection("crawlerDB", true);
        $isMemSQL = $connectionOverrides["crawlerDB"]["isMemSQL"];
        $referDeclr = $isMemSQL?" REFERENCE " : "";
	$res = sqlQuery($connection, "CREATE DATABASE IF NOT EXISTS $crawlerDbName CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
	$textFieldType = getTextFieldType();
	
	sqlQuery ($connection, "USE $crawlerDbName");
	
	//fetched_file_id - creates table that keeps track on the last tweet from cyberglob
	if($clean){
	    $res = sqlQuery($connection, "DROP TABLE IF EXISTS fetched_file_id");	    
	}
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS fetched_file_id (
            id BIGINT NOT NULL AUTO_INCREMENT,
            fetched_id BIGINT NOT NULL,
            source BIGINT NOT NULL,
             PRIMARY KEY (id))";
	
	sqlQuery ($connection,$sql);
	
	if($clean){
	    $res = sqlQuery($connection, "DROP TABLE IF EXISTS parsed_post_id");
	}
	sqlQuery ($connection, "CREATE TABLE IF NOT EXISTS parsed_post_id (
			id INT NOT NULL AUTO_INCREMENT,
			post_type $textFieldType,
			post_id $textFieldType,
			PRIMARY KEY (id),
			UNIQUE KEY post_type_id_index (post_type, post_id)
			)" );
	
	
	//raw_stream_data - creates the raw data table for the tweets
	if($clean){
	    $res = sqlQuery($connection, "DROP TABLE IF EXISTS raw_stream_data");
	}
	sqlQuery ($connection, "CREATE TABLE IF NOT EXISTS raw_stream_data (
			id BIGINT NOT NULL AUTO_INCREMENT,
            element_id $textFieldType NOT NULL,
			source $textFieldType,
			data JSON,
            PRIMARY KEY (id), 
            UNIQUE  KEY (element_id, source)
			)" );
	
	//num_to_source - connects the source to id 
	if($clean){
	    $res = sqlQuery($connection, "DROP TABLE IF EXISTS num_to_source");
	}
	sqlQuery ($connection, "CREATE TABLE IF NOT EXISTS num_to_source (
			id INT NOT NULL AUTO_INCREMENT,
			source $textFieldType,
			PRIMARY KEY (id)
			)" );
	//update hardcoded the sources - the id in the table is the unique id
	sqlQuery ($connection,"INSERT INTO num_to_source (`source`) VALUES ('twitter'),('facebook'),('webz'),('instegram'),('cyber_webz'),('telegram') ON DUPLICATE KEY UPDATE id=id");
	
	//filter_file_id - creates the filter counter table
	if($clean){
	    $res = sqlQuery($connection, "DROP TABLE IF EXISTS filter_file_id");
	}
	$sql = "CREATE $referDeclr TABLE IF NOT EXISTS filter_file_id (
            id BIGINT NOT NULL AUTO_INCREMENT ,
            filter_name $textFieldType,
            filter_id BIGINT NOT NULL  DEFAULT 0,
            UNIQUE  KEY (filter_name, filter_id),
             PRIMARY KEY (id))";
	
	sqlQuery ($connection,$sql);
	
	
	global $userAccountsTables;
    if (isset($userAccountsTables)) {
        if($clean){
            $res = sqlQuery($connection, "DROP TABLE IF EXISTS user_account_");
        }
        
        foreach ($userAccountsTables as $table) {
            sqlQuery($connection, "CREATE TABLE IF NOT EXISTS user_account_$table (
				id INT NOT NULL AUTO_INCREMENT,
				accountType TEXT,
				displayName TEXT,
				accountName TEXT,
				accountId BIGINT,
				URL TEXT,
				lastAccessed DATETIME,
				PRIMARY KEY (id),
				KEY URL_index (URL(50)),
				KEY accountName_index (accountName(20)),
				KEY accountId_INDEX(accountId)
				)");
        }
    }
    createMutexTable($connection, $clean);
    createFbCrawlerTable($connection, $clean);
    
    sqlClose($connection, 'sqlTable - createCrawlerTables');
}

function createGroupsTable($mysqli, $referDeclr = ""){
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS groups_table");
    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS groups_table(
    name varchar(100) NOT NULL,
    group_id INT,
    permissions JSON,
    PRIMARY KEY (name))";
    $res = sqlQuery($mysqli, $sql);
    
    $groups = file_get_contents('settings'.DIRECTORY_SEPARATOR.'groups.json', true);
    $groups_arr = json_decode($groups, true);
    foreach($groups_arr as $group){
        $name = $group["name"];
        $g_id = $group["id"];
        $permissions = json_encode($group["permissions"], true);
        $sql = "INSERT INTO groups_table (name, group_id, permissions) VALUES(\"$name\", $g_id, '$permissions')";
        $res = sqlQuery($mysqli, $sql);
        
    }
}


/**
 * NM patch tables
 */

function createNMPatchTables($mysqli, $referDeclr){
    //PATCH - Literals
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS literals_eth");
    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS literals_eth(
    unicode varchar(100) NOT NULL,
    lang LONGTEXT,
    PRIMARY KEY (unicode))";
    $res = sqlQuery($mysqli, $sql);
    
    $jsondata = file_get_contents('../iventitymatcherhtm/jsons/literals_eth.json');
    $data = json_decode($jsondata, true);
    foreach($data as $unicode=>$lang){
        $sql = "INSERT INTO literals_eth (unicode, lang) VALUES(\"$unicode\", \"$lang\")";
        $res = sqlQuery($mysqli, $sql);
    }
    //PATCH - ethnicities to naming convention
    $res = sqlQuery($mysqli, "DROP TABLE IF EXISTS ethnicities_mapping");
    $sql = "CREATE $referDeclr TABLE IF NOT EXISTS ethnicities_mapping(
    eth varchar(100) NOT NULL,
    naming_conv LONGTEXT)";
    $res = sqlQuery($mysqli, $sql);
    
    $ethnicities_mapping_file = '../iventitymatcherhtm/jsons/ethnicities_mapping.csv';
    $handle = @fopen($ethnicities_mapping_file, "r");
    
    while (!feof($handle)) {
        $row = fgetcsv($handle);
        $arr = explode("\t", $row[0]);
        $sql = "INSERT INTO ethnicities_mapping (eth, naming_conv) VALUES(\"$arr[0]\", \"$arr[1]\")";
        $res = sqlQuery($mysqli, $sql);
    }  
}
?>