//connection string to mysql database
import MySql.Data;

	private var connectionString = "Server=localhost;Database=unitygame;Uid=root;";
	//private var connectionString = "Server=MYSQL5005.Smarterasp.net;Database=db_9ac483_unity;Uid=9ac483_unity;Pwd=unityassignment;";

	private var connection;
	private var command;
	private var dataReader;

	private var count = 0;
	private var caninsert:boolean;
	private var insertedID:int;

	function InsertUser(playername: String, password: String)
	{
		try
		{
			connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = new MySql.Data.MySqlClient.MySqlCommand("insert into players(playername,password) VALUES (?playername,?password)",connection);

			var param1 = command.Parameters.AddWithValue("?playername",playername);
			var param2 = command.Parameters.AddWithValue("?password",password);

			command.ExecuteNonQuery();
			intsertedID = command.LastInsertedId;
			MainSettings.SetPlayerId(intsertedID);
			return true;
		}
		catch(e)
		{
			return false;
		}
	}


	function GetHighScores()
	{
		try
		{
			var HighScores = new Array();

			//connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//open the database connection
			connection.Open();
			//create the database command
			command = MySql.Data.MySqlClient.MySqlCommand("select * from players ORDER BY score DESC LIMIT 5",connection);
			//create the datareader
			dataReader = command.ExecuteReader();
			//while there are more rows
			
			
			while(dataReader.Read())
			{
				//the datareader is an array of all the fields in the column
				HighScores.Add("Name: "+ dataReader["playername"].ToString()+ " Score: "+dataReader["score"].ToString());
			}
					
			connection.Close();
			connection.Dispose();
			return HighScores;
			
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}	
	}
	
	function GetHighScore(playername: String)
	{
		try
		{
			var HighScore:int = 5;

			//connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//open the database connection
			connection.Open();
			//create the database command
			command = MySql.Data.MySqlClient.MySqlCommand("select score from players where playername = '"+playername+"' LIMIT 1",connection);
			//create the datareader
			dataReader = command.ExecuteReader();
			//while there are more rows
			
			
			while(dataReader.Read())
			{
				HighScore = parseInt(dataReader["score"].ToString());
			}
					
			connection.Close();
			connection.Dispose();
			return HighScore;
			
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}	
	}
	
	function GetSaveGames(playername: String, password: String)
	{
	
		try
		{
			var Entry = new Array();

			//connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//open the database connection
			connection.Open();
			//create the database command
			command = MySql.Data.MySqlClient.MySqlCommand("select * from players WHERE playername = '"+playername+"' AND password='"+password+"' LIMIT 1" , connection);
			//command = MySql.Data.MySqlClient.MySqlCommand("select * from players LIMIT 5",connection);
			//create the datareader
			dataReader = command.ExecuteReader();
			//while there are more rows
			
			
			while(dataReader.Read())
			{			
				Entry.Add(dataReader["playerid"].ToString());
				Entry.Add(dataReader["playername"].ToString()+ ": "+dataReader["CurrentScore"].ToString());
				
			}
					
			connection.Close();
			connection.Dispose();
			return Entry;
		}
			catch(e)
				{
					Debug.Log("Error: " + e.ToString());
					//Debug.Log(e.ToString());
				}	
	}
	
	function LoadUserDetails(playerid: int)
	{
		try
		{
			var Entry = new Array();
			//connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//open the database connection
			connection.Open();
			//create the database command
			command = MySql.Data.MySqlClient.MySqlCommand("select * from players WHERE playerid = '"+playerid+"' LIMIT 1" , connection);
			//create the datareader
			dataReader = command.ExecuteReader();
			//while there are more rows
			
			
			while(dataReader.Read())
			{	
				Entry.Add(dataReader["playername"].ToString());
				Entry.Add(dataReader["CurrentScore"].ToString());
				Entry.Add(dataReader["Lives"].ToString());
				Entry.Add(dataReader["IsInvincible"].ToString());
				Entry.Add(dataReader["Fuel"].ToString());
				Entry.Add(dataReader["CarPosition"].ToString());
				Entry.Add(dataReader["Invincibilities"].ToString());
				Entry.Add(dataReader["Level"].ToString());
				Entry.Add(dataReader["time"].ToString());
			}
					
			connection.Close();
			connection.Dispose();
			return Entry;
		}
			catch(e)
				{
					Debug.Log("Error: " + e.ToString());
				}
	}
	
	function LoadGameObjects(object: int)
	{
		try
		{
			var Entry = new Array();
			//connect to the database
			connection = MySql.Data.MySqlClient.MySqlConnection(connectionString);
			//open the database connection
			connection.Open();
			//create the database command
			var Query:String = "select * from gameobjects WHERE object = '"+object+"' AND playerid='"+MainSettings.GetPlayerId()+"'";
			command = MySql.Data.MySqlClient.MySqlCommand(Query , connection);
			//create the datareader
			dataReader = command.ExecuteReader();
			//while there are more rows
			
			
			while(dataReader.Read())
			{				
				var SingleEntry = new Array();
				SingleEntry.Add(dataReader["x"].ToString());
				SingleEntry.Add(dataReader["y"].ToString());
				SingleEntry.Add(dataReader["z"].ToString());
				Entry.Add(SingleEntry);
			}
					
			connection.Close();
			connection.Dispose();
			return Entry;
		}
			catch(e)
				{
					Debug.Log("Error: " + e.ToString());
				}
	}
	
	function SaveUserState(CurrentScore: int, Lives: int, IsInvincible: boolean, Invincibilities: int, Fuel: int, CarPosition: int, playerid: int, time:int)
	{

		try{
			connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = new MySql.Data.MySqlClient.MySqlCommand("UPDATE players SET CurrentScore = ?CurrentScore, Lives=?Lives, IsInvincible=?IsInvincible, Invincibilities=?Invincibilities, Fuel=?Fuel, CarPosition=?CarPosition, level=?level, time=?time WHERE playerid=?playerid",connection);

			command.Parameters.AddWithValue("?CurrentScore",CurrentScore);
			command.Parameters.AddWithValue("?Lives",Lives);
			command.Parameters.AddWithValue("?IsInvincible",IsInvincible);
			command.Parameters.AddWithValue("?Invincibilities",Invincibilities);
			command.Parameters.AddWithValue("?Fuel",Fuel);
			command.Parameters.AddWithValue("?CarPosition",CarPosition);
			command.Parameters.AddWithValue("?playerid",playerid);
			command.Parameters.AddWithValue("?level",Application.loadedLevel);
			command.Parameters.AddWithValue("?time",time);
			
			command.ExecuteNonQuery();
			}
			catch(e)
			{
				Debug.Log(e.ToString());
			}
	}
	
	function SaveGameObjects(playerid:int, object:int, x:float, y:float, z:float)
	{

		try
		{
			connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = new MySql.Data.MySqlClient.MySqlCommand("insert into gameobjects(playerid,object,x,y,z) VALUES (?playerid,?object,?x,?y,?z)",connection);

			command.Parameters.AddWithValue("?playerid",playerid);
			command.Parameters.AddWithValue("?object",object);
			command.Parameters.AddWithValue("?x",x);
			command.Parameters.AddWithValue("?y",y);
			command.Parameters.AddWithValue("?z",z);

			command.ExecuteNonQuery();
			
			return true;
		}
		catch(e)
		{
			return false;
		}
	}
	
	function DeleteGameObjects(playerid: int)
	{
			try{
			connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = new MySql.Data.MySqlClient.MySqlCommand("DELETE from gameobjects WHERE playerid=?playerid",connection);

			command.Parameters.AddWithValue("?playerid",playerid);
			
			command.ExecuteNonQuery();
			}
			catch(e)
			{
				Debug.Log(e.ToString());
			}
	}
	
	function SaveScore(score: int, playername: String)
	{
		if(score > GetHighScore(playername))
		{
			try{
			connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
			connection.Open();
			
			var command = new MySql.Data.MySqlClient.MySqlCommand("UPDATE players SET score=?score  WHERE playername=?playername",connection);

			command.Parameters.AddWithValue("?score",score);
			command.Parameters.AddWithValue("?playername",playername);
			
			command.ExecuteNonQuery();
			}
			catch(e)
			{
				Debug.Log(e.ToString());
				return GetHighScore(playername);
			}
			
			return -1;
		}
		else
		{	Debug.Log(GetHighScore(playername));
			return GetHighScore(playername);
		}
	}