<?php
/*
Opens connection to MySQL DB and creates/copes/deletes DB as required

For create and open options it writes DB name to ../CurrentlyOpenedDBName.tmp
and closes connection to MySQL

File validity checks are made along the way(file exists or not exists etc.)
*/


require '../Common/openDB.php';
//<TODO> close mysql connections on exiting this file using mysql_close();

// Create new data base from scratch
if(isset($_POST['projNewScratch'])){
	echo "POST[DBDesc] = " . $_POST[DBDesc] . "<br/>";
	$desc = $_POST[DBDesc];
	if($desc == NULL || $desc == ""){
		echo "Blank name provided";
		header("Location: ../2_Projects/MakeNewDatabase.html?GivenName=blank");
	}else{

		// Auto generate database name called $name from user supplied description
		$name = getCapitalLetters($desc);

		CreateEmptyDB($name); 				// create database if not exists already
		WriteProjectsListFile($name,$desc); // write DB name to ProjectsList.csv
		SelectDB($name);					// write DB name to CurrentlyOpenedDBName.tmp
		mysql_close();
		header("Location: ../Common/TopWithDropDown.html?DB=".$name."&refresh=true");


	}
}elseif(isset($_POST['cancel'])){
	DeleteCurrentlyOpenedDBName();
	header("Location: ../Common/TopWithDropDown.html"); // go back to original page

}elseif(isset($_POST['projCopy'])){
	echo"copy existing";

}elseif(isset($_POST['projDel'])){
	// Check if delFile has correct extension
	if (endsWith($_POST['delFile'], '.entrak')){ 	// Has correct extension
		$delFileName = str_replace(".entrak", "", $_POST['delFile']);
	}else{
		WarnUser("This does not appear to be an Entrak file","FileOperations.html");
		exit;
	}

	// Check if db by that name exists
	if(DBNotExistsAlready($delFileName)){
		WarnUser("Database by that name does not exist","FileOperations.html");
	}else{
		mysql_query("DROP DATABASE " . $delFileName);
		mysql_close();
		unlink("../CurrentlyOpenedDBName.tmp");
		WarnUser("Database deleted on server. <br /> Remove the file " . $_POST['delFile'] . " <br/>from your computer and click the OK button <br />","FileOperations.html");
	}
}

function DeleteCurrentlyOpenedDBName(){
	unlink("../CurrentlyOpenedDBName.tmp"); // delete file
}

function CreateEmptyDB($DBName){

	// Define string sizes
	// IMPORTANT: Upon changing sizes of fields also carry this change
	// to individual files that AddEdit each table
	$smaStr = 60;
	$longStr = 120;
	$xlongStr = 240;
	$xxlongStr = 1000;

	mysql_query("CREATE DATABASE IF NOT EXISTS $DBName;");
	mysql_query("USE $DBName;");
	// Table 0
	mysql_query("	CREATE TABLE HighLevInfo(
					db_version INT,
					name VARCHAR(60),
					value VARCHAR(3)
					);");
	mysql_query("INSERT INTO HighLevInfo (db_version) VALUES (6);");
	mysql_query("INSERT INTO HighLevInfo (name) VALUES ('MDF');");

	// Table 4
	mysql_query("	CREATE TABLE Frequency(
					frequency_id INT NOT NULL AUTO_INCREMENT,
					name VARCHAR($smaStr) NOT NULL,
					period VARCHAR($smaStr),
					times_per_period VARCHAR($smaStr),
					start DATETIME,
					stop DATETIME,
					sta_tm_day VARCHAR({$smaStr}),
					end_tm_day VARCHAR({$smaStr}),
					day_o_week VARCHAR({$smaStr}),
					day_o_mnth VARCHAR({$smaStr}),

					PRIMARY KEY (frequency_id)
					);");
	// Table 3
	mysql_query("	CREATE TABLE Source(
					source_id INT NOT NULL AUTO_INCREMENT,
					name VARCHAR($smaStr),

					PRIMARY KEY (source_id)
					);");
	// Table 6
	mysql_query("	CREATE TABLE Fetcher(
					fetcher_id INT NOT NULL AUTO_INCREMENT,
					name VARCHAR($smaStr),
					description VARCHAR($longStr),
					input_method VARCHAR($smaStr),
					frequency_id INT,
					EG_source VARCHAR($smaStr),
					EG_user VARCHAR($smaStr),
					EG_password VARCHAR($smaStr),
					EG_destination VARCHAR($smaStr),
					EG_appdestinationLocal VARCHAR($smaStr),
					EG_appdestinationRemote VARCHAR($smaStr),
					EG_IP VARCHAR($smaStr),
					EG_file VARCHAR($smaStr),
					EG_fetchfile VARCHAR($smaStr),
					EG_fetchpath VARCHAR($smaStr),
					EG_ftp VARCHAR($smaStr),
					EG_duration VARCHAR($smaStr),
					EG_frequency VARCHAR($smaStr),
					expected_data_items INT,

					FOREIGN KEY (frequency_id) REFERENCES Frequency(frequency_id),
					PRIMARY KEY (fetcher_id)
					);");
	// Table 1
	mysql_query("	CREATE TABLE Static(
					static_id INT NOT NULL AUTO_INCREMENT,
					attribute VARCHAR($smaStr) NOT NULL,
					value VARCHAR($longStr),
					source_id INT,

					FOREIGN KEY (source_id) REFERENCES Source(source_id) ON DELETE CASCADE,
					PRIMARY KEY (static_id)
					);");
	// Table 2
	mysql_query("	CREATE TABLE Dynamic(
					dynamic_id INT NOT NULL AUTO_INCREMENT,
					source_id INT,
					attribute VARCHAR($smaStr),
					value VARCHAR($smaStr),
					fetcher_id INT,
					status VARCHAR($smaStr),
					fetch_status VARCHAR($smaStr),
					MDF_status VARCHAR(20),


					FOREIGN KEY (source_id) REFERENCES Source(source_id) ON DELETE CASCADE,
					FOREIGN KEY (fetcher_id) REFERENCES Fetcher(fetcher_id),
					PRIMARY KEY (dynamic_id)
					);");
	// Table 5
	mysql_query("	CREATE TABLE DynamicValue(
					dynamic_id INT,
					timestamp DATETIME,
					value FLOAT,

					FOREIGN KEY (dynamic_id) REFERENCES Dynamic(dynamic_id)
					);");
	// Table 9
	mysql_query("	CREATE TABLE Output(
					output_id INT NOT NULL AUTO_INCREMENT,
					name VARCHAR($smaStr) NOT NULL,
					frequency_id INT,
					ranupto DATETIME,
					status VARCHAR($smaStr),
					es_data_freq_id INT,

					FOREIGN KEY (frequency_id) REFERENCES Frequency(frequency_id),
					FOREIGN KEY (es_data_freq_id) REFERENCES Frequency(frequency_id),
					PRIMARY KEY (output_id)
					);");
	// Table 8
	mysql_query("	CREATE TABLE Operation(
					operation_id INT NOT NULL AUTO_INCREMENT,
					output_id INT,
					name VARCHAR($smaStr) NOT NULL,
					output VARCHAR($smaStr),
					SelectionA VARCHAR($smaStr),
					operation VARCHAR($smaStr),
					SelectionB VARCHAR($smaStr),

					FOREIGN KEY (output_id) REFERENCES Output(output_id),
					PRIMARY KEY (operation_id)
					);");
	// Table 7
	mysql_query("	CREATE TABLE Scope(
					scope_id INT NOT NULL AUTO_INCREMENT,
					output_id INT,
					attribute VARCHAR($smaStr),
					rule VARCHAR($smaStr),
					text VARCHAR($smaStr),
					altSQLStatement VARCHAR($xlongStr),
					master_id INT,

					FOREIGN KEY (output_id) REFERENCES Output(output_id),
					PRIMARY KEY (scope_id)
					);");
	// Table 10
	mysql_query("	CREATE TABLE Archive(
					output_id INT,
					scope_id INT,
					operation_id INT,

					FOREIGN KEY (output_id) REFERENCES Output(output_id) ON DELETE CASCADE,
					FOREIGN KEY (scope_id) REFERENCES Scope(scope_id) ON DELETE CASCADE,
					FOREIGN KEY (operation_id) REFERENCES Operation(operation_id) ON DELETE CASCADE
					);");

}

function WriteProjectsListFile($DBName,$DBDesc){
	$myfile = fopen("../ProjectsList.csv", "a") or die("Unable to open file from FileOperations.php method = WriteClientSideInputFile");
	fwrite($myfile, "{$DBName}!{$DBDesc}!unlocked\n");
	fclose($myfile);
}

function ReadClientSideInputFile(){
	// Copy file to server as ../CurrentlyOpenedDBName.tmp

	// read file for db name
	$myfile = fopen("../CurrentlyOpenedDBName.tmp", "r") or die("Unable to open file from FileOperations.php method = ReadClientSideInputFile");
	$DBName = fgets($myfile);
	fclose($myfile);
	echo "DB to open is called " . $DBName . " <br/> Closing connection to MySQL";


}

function getCapitalLetters($str){
	// Get capital letters from description
	$name_prefix = "";
	if(preg_match_all('#([A-Z]+)#',$str,$matches))$name_prefix = implode('',$matches[1]);

	if(strlen($name_prefix) < 2) $name_prefix .= "EDB";
	$name_found = false;
	for($i = 10; $i < 1000; $i++){
		if(DBNotExistsAlready($name_prefix)){
			$name_found = true;
		}else{
			$name_prefix = $name_prefix . $i;
		}
		if($name_found)break;
	}
	return $name_prefix;
	// $name_suffix = generateRandomString(); // This is currently not used
	// return $name_prefix.$name_suffix;
}

function generateRandomString($length = 10) {
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
    return $randomString;
}

function DBNotExistsAlready($DBName){ // returns boolean
	$NewDB = true;
	$result = mysql_query('SHOW DATABASES');
	while ($row = mysql_fetch_assoc($result)){
		if($row['Database'] == $DBName)$NewDB = false;
	}
	return $NewDB;
}

function SelectDB($DBName){ // write file CurrentlyOpenedDBName.tmp
	$myfile = fopen("../CurrentlyOpenedDBName.tmp", "w") or die("Unable to open file from FileOperations.php method = SelectDB");
	fwrite($myfile,$DBName);
	fclose($myfile);
}

function DelFromList($DBName){ // delete entry from ProjectsList with name $DBName
	$myfile = fopen("../ProjectsList.csv", "r") or die("Unable to open file from FileOperations.php method = SelectDB");
	$tmpfile = fopen("../tmpFile", "w") or die("Unable to open file from FileOperations.php method = SelectDB");
	while(!feof($myfile)) {
		$ReadLine = fgets($myfile);
		if($ReadLine[0] == "#"){
			fwrite($tmpfile,$ReadLine);
		}else{
			$line = explode('!',$ReadLine);
			if ($line[0] == $DBName || $line[0] == "") {

			}else{
				fwrite($tmpfile,$ReadLine);
			}
		}
  }
	fclose($myfile);


	unlink("../ProjectsList.csv");
	fclose($tmpfile);
	rename("../tmpFile","../ProjectsList.csv");
}
?>
