//import mysql library
import MySql.Data;

//connection string to mysql database
var connectionString = "Server=localhost;Database=gameproject;UserID=root;Password=;Pooling=true;";
var connection;
var command;
var dataReader;
var namesList=new Array();
var IdList=new Array();
var List=new Array();
var resultOk: boolean;


class dbAccess{

	function insertNewPlayer(playername: String){
		try{
			if(GetPlayerName(playername) == true){ 
				connection = MySqlClient.MySqlConnection(connectionString);
				connection.Open();				
				var command:MySql.Data.MySqlClient.MySqlCommand;
					
				command = MySql.Data.MySqlClient.MySqlCommand("insert into player (Name) VALUES (?Name)",connection);
				var param1:MySql.Data.MySqlClient.MySqlParameter;				
				param1 = command.Parameters.AddWithValue("?Name",playername);
				command.ExecuteNonQuery();
						
				connection.Close();
				connection.Dispose();
			}
		}catch(e){
			Debug.Log(e.ToString());
		}				
	}

	function insertPlayerScore(playername: String, score : int){
		try{
			var Id: int;
			connection = MySqlClient.MySqlConnection(connectionString);
			connection.Open();				
			var command:MySql.Data.MySqlClient.MySqlCommand;
				
			command = MySql.Data.MySqlClient.MySqlCommand("SELECT ID from player WHERE Name='" + playername + "'",connection);
			dataReader = command.ExecuteReader();			
			
			while(dataReader.Read())
			{
				IdList.Add(dataReader["ID"].ToString());
			}
			
			for(id in IdList){
				Id = parseInt(id);
			}
			
			connection.Close();
			connection.Dispose();
			connection.Open();	
			
			command = MySql.Data.MySqlClient.MySqlCommand("insert into score (PlayerId,Score) VALUES (?PlayerId, ?Score)",connection);
			var paramPlayerId:MySql.Data.MySqlClient.MySqlParameter;	
			var paramScore:MySql.Data.MySqlClient.MySqlParameter;
							
			paramPlayerId = command.Parameters.AddWithValue("?PlayerId",Id);
			paramScore = command.Parameters.AddWithValue("?Score",score);
			command.ExecuteNonQuery();

					
			connection.Close();
			connection.Dispose();
		}catch(e){
			Debug.Log(e.ToString());
		}
	}

	function GetPlayerName(playername: String): boolean{
		try
		{
			namesList = 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 player",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
				namesList.Add(dataReader["Name"].ToString());
			}
			
			for(name in namesList){
				if(name == playername){
					resultOk = false;
					break;
				}
				else{
					resultOk = true;
				}
			}
					
			connection.Close();
			connection.Dispose();
			return resultOk;
			
		}catch(e){
			Debug.Log(e.ToString());
		}	
	}

	function SaveGameState(playername: String, score: int ,level: int, numOfTargets: int, gameTime: double,bullets : int){
		try{
			var Id: int;
			connection = MySqlClient.MySqlConnection(connectionString);
			connection.Open();				
			var command:MySql.Data.MySqlClient.MySqlCommand;
				
			command = MySql.Data.MySqlClient.MySqlCommand("SELECT ID from player WHERE Name='" + playername + "';",connection);
			dataReader = command.ExecuteReader();			
			
			while(dataReader.Read())
			{
				IdList.Add(dataReader["ID"].ToString());
			}
			
			for(id in IdList){
				Id = parseInt(id);
			}
			
			connection.Close();
			connection.Dispose();
			connection.Open();	
			
			
			command = MySql.Data.MySqlClient.MySqlCommand("DELETE FROM gamestate WHERE PlayerId=" + Id + ";",connection);
			command.ExecuteNonQuery();
			
			connection.Close();
			connection.Dispose();
			connection.Open();	
			
			command = MySql.Data.MySqlClient.MySqlCommand("insert into gamestate (PlayerId,Score,Level,NumOfTargets,Time,Bullets) VALUES (?PlayerId, ?Score,?Level,?NumOfTargets,?Time,?Bullets);",connection);
			var paramPlayerId:MySql.Data.MySqlClient.MySqlParameter;	
			var paramScore:MySql.Data.MySqlClient.MySqlParameter;
			var paramLevel:MySql.Data.MySqlClient.MySqlParameter;
			var paramNumOfTargets:MySql.Data.MySqlClient.MySqlParameter;
			var paramTime:MySql.Data.MySqlClient.MySqlParameter;
			var paramBullets:MySql.Data.MySqlClient.MySqlParameter;
							
			paramPlayerId = command.Parameters.AddWithValue("?PlayerId",Id);
			paramScore = command.Parameters.AddWithValue("?Score",score);
			paramLevel = command.Parameters.AddWithValue("?Level",level);
			paramNumOfTargets = command.Parameters.AddWithValue("?NumOfTargets",numOfTargets);
			paramTime = command.Parameters.AddWithValue("?Time",gameTime);
			paramBullets = command.Parameters.AddWithValue("?Bullets",bullets);
			command.ExecuteNonQuery();

					
			connection.Close();
			connection.Dispose();
		}catch(e){
			Debug.Log(e.ToString());
		}
	}	

	function LoadGameState(playername:String): Array
	{
		try{
			var Id: int;
			connection = MySqlClient.MySqlConnection(connectionString);
			connection.Open();				
			var command:MySql.Data.MySqlClient.MySqlCommand;
				
			command = MySql.Data.MySqlClient.MySqlCommand("SELECT ID from player WHERE Name='" + playername + "'",connection);
			dataReader = command.ExecuteReader();			
			
			while(dataReader.Read())
			{
				IdList.Add(dataReader["ID"].ToString());
			}
			
			for(id in IdList){
				Id = parseInt(id);
			}				
			connection.Close();
			connection.Dispose();
			connection.Open();	
				
			command = MySql.Data.MySqlClient.MySqlCommand("SELECT * from gamestate WHERE PlayerId='" + Id + "'",connection);
			dataReader = command.ExecuteReader();			
			
			while(dataReader.Read())
			{
				List[0] = dataReader["Score"].ToString();
				List[1] = dataReader["Level"].ToString();
				List[2] = dataReader["NumOfTargets"].ToString();
				List[3] = dataReader["Time"].ToString();
				List[4] = dataReader["Bullets"].ToString();
			}

			return List;
		}catch(e){
			return null;
			Debug.Log(e.ToString());
		}
	}
}