import MySql.Data;

class sqlClass{
	//Connection string to connect to our mysql database
	private var connectionString = "Server=localhost;UserID=root;Password=;Pooling=true";	
	private var connection;
	
	function sqlClass(){
	
	}
	
	function sqlClass(database){
		connectionString += ';Database='+database;
	}
	
	function createDataBase(){
		try{
			//Connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command:MySql.Data.MySqlClient.MySqlCommand;
			
			command = MySql.Data.MySqlClient.MySqlCommand("CREATE DATABASE IF NOT EXISTS `arkanoid`",connection);	
			command.ExecuteNonQuery();
			
			connection.Close();
			connection.Dispose();
			
			var namesList = createTable('arkanoid');
			return namesList;
			
		}
		catch(e){
			Debug.Log(e.ToString());
			return null;
		}
	}
	
	function createTable(database){
		try{
			connectionString += ';Database='+database;
			//Connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command:MySql.Data.MySqlClient.MySqlCommand;
			
			//Create username table
			command = MySql.Data.MySqlClient.MySqlCommand("CREATE TABLE IF NOT EXISTS `users` (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE, score INT)",connection);	
			command.ExecuteNonQuery();
			
			//Create userSave table
			command = MySql.Data.MySqlClient.MySqlCommand("CREATE TABLE IF NOT EXISTS `usersaves` (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100), score INT, level INT, saveTime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",connection);	
			command.ExecuteNonQuery();
			
			//Create objectHolder table
			command = MySql.Data.MySqlClient.MySqlCommand("CREATE TABLE IF NOT EXISTS `objectHolder` (saveId INT, objectId INT, PRIMARY KEY(saveId, objectId))",connection);	
			command.ExecuteNonQuery();
			
			//Create object table
			command = MySql.Data.MySqlClient.MySqlCommand("CREATE TABLE IF NOT EXISTS `object` (id INT PRIMARY KEY AUTO_INCREMENT, object TEXT, positionX FLOAT, positionY FLOAT)",connection);	
			command.ExecuteNonQuery();
			
			connection.Close();
			connection.Dispose();
			
			var namesList = getData();
			
			return namesList;
			
		}
		catch(e){
			Debug.Log(e.ToString());
			return null;
		}
	}
	
	function getData(){
		//Get the data from the database
		try{
			//Connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//Open the connection
			connection.Open();
			//Create the database command
			var command = MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM users ORDER BY score DESC LIMIT 40", connection);
			//Create the datareader
			var dataReader = command.ExecuteReader();
			//While there are more rows
			var namesList = Array();
			
			while(dataReader.Read()){
				namesList.Add(new Array(dataReader["username"].ToString(), dataReader["score"].ToString()));
			}
			
			connection.Close();
			connection.Dispose();
			
			return namesList;
			
		}catch(e){
			Debug.Log(e.ToString());
			return null;
		}
	}
	
	function addNewPlayer(playername)
	{
		try{
			connection = MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM users WHERE username = '"+playername+"'", connection);
			
			//Create the datareader
			var dataReader = command.ExecuteReader();
			//While there are more rows
			var namesList = Array();
			
			while(dataReader.Read()){
				namesList.Add(new Array(dataReader["username"].ToString(), dataReader["score"].ToString()));
			}	
			
			connection.Close();
			connection.Dispose();
			
			if(namesList.length<1){
				connection = MySqlClient.MySqlConnection(connectionString);
				connection.Open();
			
				command = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO users(username,score) VALUES (?playername,0)",connection);
		
				var param1:MySql.Data.MySqlClient.MySqlParameter;
				
				param1 = command.Parameters.AddWithValue("?playername",playername);
			
				command.ExecuteNonQuery();
				
				connection.Close();
				connection.Dispose();
			}	
			
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
}

function storeHighScore(username, score){
	try{
		connection = MySqlClient.MySqlConnection(connectionString);
		connection.Open();
		
		var command = MySql.Data.MySqlClient.MySqlCommand("SELECT score FROM users WHERE username = '"+username+"'", connection);
		
		//Create the datareader
		var dataReader = command.ExecuteReader();
		//While there are more rows
		var highScore:int;
		
		while(dataReader.Read()){
			highScore = parseInt(dataReader["score"].ToString());
		}	
		
		connection.Close();
		connection.Dispose();
		
		if(highScore<score){
			connection = MySqlClient.MySqlConnection(connectionString);
			connection.Open();
		
			command = MySql.Data.MySqlClient.MySqlCommand("UPDATE users SET score=?playerscore WHERE username=?playername",connection);
	
			var param1:MySql.Data.MySqlClient.MySqlParameter;
			var param2:MySql.Data.MySqlClient.MySqlParameter;
			
			param1 = command.Parameters.AddWithValue("?playerscore", score);
			param2 = command.Parameters.AddWithValue("?playername",username);		
		
			command.ExecuteNonQuery();
			
			connection.Close();
			connection.Dispose();
		}
		
	}
	catch(e){
		Debug.Log(e.ToString());
	}
}

function saveState(playerDetails, objects){
	try{
		connection = MySqlClient.MySqlConnection(connectionString);
		connection.Open();
		
		//First create save Id 
		command = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO usersaves(username,score, level, saveTime) VALUES (?playername,?score, ?level, now())",connection);
		
		var param1:MySql.Data.MySqlClient.MySqlParameter;
		var param2:MySql.Data.MySqlClient.MySqlParameter;
		var param3:MySql.Data.MySqlClient.MySqlParameter;
		
		param1 = command.Parameters.AddWithValue("?playername",playerDetails[0]);
		param2 = command.Parameters.AddWithValue("?score",playerDetails[1]);
		param3 = command.Parameters.AddWithValue("?level",playerDetails[2]);
	
		command.ExecuteNonQuery();
		//Get last inserted Id
		var saveId = command.LastInsertedId;
		
		connection.Close();
		connection.Dispose();
		
		for(var thisObject:GameObject in objects){
			if (thisObject.activeInHierarchy && thisObject.transform.tag!='Untagged'){				
				connection = MySqlClient.MySqlConnection(connectionString);
				connection.Open();
				
				//Create object Id 
				command = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO object(object,positionX, positionY) VALUES (?object,?x, ?y)",connection);
								
				param1 = command.Parameters.AddWithValue("?object",thisObject.transform.tag);
				param2 = command.Parameters.AddWithValue("?x",thisObject.transform.position.x);
				param3 = command.Parameters.AddWithValue("?y",thisObject.transform.position.y);
			
				command.ExecuteNonQuery();
				//Get last inserted Id
				var objectId = command.LastInsertedId;
				
				connection.Close();
				connection.Dispose();
				
				//Store in the object holder
				connection = MySqlClient.MySqlConnection(connectionString);
				connection.Open();
				
				//Create object Id 
				command = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO objectholder(saveId,objectId) VALUES (?save,?object)",connection);
							
				param1 = command.Parameters.AddWithValue("?save",saveId);
				param2 = command.Parameters.AddWithValue("?object",objectId);
			
				command.ExecuteNonQuery();
				
				connection.Close();
				connection.Dispose();
			}
		}
	}
	catch(e){
		Debug.Log(e.ToString());
	}
}

function getUserSaves(username){
	connection = MySqlClient.MySqlConnection(connectionString);
	connection.Open();
	
	var command = MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM usersaves WHERE username = '"+username+"' ORDER BY saveTime DESC LIMIT 5", connection);
	
	//Create the datareader
	var dataReader = command.ExecuteReader();
	//While there are more rows
	var saves = new Array();
	
	while(dataReader.Read()){
		saves.push(new Array(dataReader["id"].ToString(), dataReader["username"].ToString(), dataReader["score"].ToString(), dataReader["level"].ToString(), dataReader["saveTime"].ToString()));
	}	
	
	connection.Close();
	connection.Dispose();
	
	return saves;
}

function loadState(id){
	connection = MySqlClient.MySqlConnection(connectionString);
	connection.Open();
	
	var command = MySql.Data.MySqlClient.MySqlCommand("SELECT objectId FROM objectholder WHERE saveId = '"+id+"'", connection);
	
	//Create the datareader
	var dataReader = command.ExecuteReader();
	//While there are more rows
	var object = new Array();
	var objects = new Array();
	while(dataReader.Read()){
		object.push(dataReader["objectId"].ToString());
		
		
	}	
	
	connection.Close();
	connection.Dispose();
	
	for(var o in object){
		connection = MySqlClient.MySqlConnection(connectionString);
		connection.Open();
		var commandObjects = MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM object WHERE id = '"+o+"'", connection);
		var dataReadObject = commandObjects.ExecuteReader();
		while(dataReadObject.Read()){
			objects.push(new Array(dataReadObject["id"].ToString(), dataReadObject["object"].ToString(), dataReadObject["positionX"].ToString(), dataReadObject["positionY"].ToString()));
		}
		
		connection.Close();
		connection.Dispose();	
	}
	
	return objects;
}
