//import mysql library
import MySql.Data;
import Level;
import Highscore;

//Database Class
public class DBase{
	private var conn;	//Will keep the connection
	private var comm:MySql.Data.MySqlClient.MySqlCommand;	//Used to query the database
	public var lol;
	
	//Constructor, When the class is initiated this will run.
	public function DBase(){
		try{
			//connection string to mysql database
			var connectionString = "Server=localhost;Database=gaming;UserID=root;Password=;Pooling=true;";
			conn = MySqlClient.MySqlConnection(connectionString);
			
		}catch(e){
			Debug.Log(e);
		}
	}
	
	//Add New Highscore
	public function addSave(life:int, time:int, score:int, tscore:int, level:int):int{
		try{
			//Opening connection
			conn.Open();
			
			var mySave: Save;
			var password: int;
			do{
				password = Random.Range(11111,99999);
				mySave = this.getSaveByPassword(password);
			}while(mySave != null);
			
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO save (password, life, time, score, tscore, level) VALUES(?password, ?life, ?time, ?score, ?tscore, ?level)",conn);
		
			//Assign parameters to the sql
			var param1:MySql.Data.MySqlClient.MySqlParameter;
			var param2:MySql.Data.MySqlClient.MySqlParameter;
			var param3:MySql.Data.MySqlClient.MySqlParameter;
			var param4:MySql.Data.MySqlClient.MySqlParameter;
			var param5:MySql.Data.MySqlClient.MySqlParameter;
			
			param1 = comm.Parameters.AddWithValue("?password",password);
			param1 = comm.Parameters.AddWithValue("?life",life);
			param2 = comm.Parameters.AddWithValue("?time",time);
			param2 = comm.Parameters.AddWithValue("?score",score);
			param2 = comm.Parameters.AddWithValue("?tscore",tscore);
			param2 = comm.Parameters.AddWithValue("?level",level);
			
			//Execute query
			comm.ExecuteNonQuery();
			return password;
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	
	public function getSaveByPassword(password:int):Save{
		try{
			var mySave: Save;
			mySave = null;
			//Opening connection
			conn.Open();
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("SELECT life, time, score, tscore, level FROM save WHERE password = ?password",conn);
		
			//Assign parameters to the sql
			var param:MySql.Data.MySqlClient.MySqlParameter;
			param1 = comm.Parameters.AddWithValue("?password",password);
			
			//Execute query
			dataReader = comm.ExecuteReader();
			//while there are more rows
			while(dataReader.Read())
			{
				//Instantiate the level with that particular id
				mySave = new Save(int.Parse(dataReader["life"].ToString()), int.Parse(dataReader["time"].ToString()), int.Parse(dataReader["score"].ToString()), int.Parse(dataReader["tscore"].ToString()), int.Parse(dataReader["level"].ToString()));
			}
			
			//Close Connection
			conn.Close();
			conn.Dispose();
			return mySave;	//Return level
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	
	//Add New Highscore
	public function addScore(name:String, score:int){
		try{
			//Opening connection
			conn.Open();
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("INSERT INTO highscores (username, score) VALUES(?name, ?score)",conn);
		
			//Assign parameters to the sql
			var param1:MySql.Data.MySqlClient.MySqlParameter;
			var param2:MySql.Data.MySqlClient.MySqlParameter;
			
			param1 = comm.Parameters.AddWithValue("?name",name);
			param2 = comm.Parameters.AddWithValue("?score",score);
			
			//Execute query
			comm.ExecuteNonQuery();
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	//Get Level By Id
	public function getLevelById(lev_id:int):Level{
		try{
			var myLevel: Level;
			myLevel = null;
			//Opening connection
			conn.Open();
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("SELECT level_id AS lev_id, name AS name, floor AS floor, duration AS duration, speed AS speed, score AS score FROM levels WHERE level_id = ?lev_id",conn);
		
			//Assign parameters to the sql
			var param:MySql.Data.MySqlClient.MySqlParameter;
			param1 = comm.Parameters.AddWithValue("?lev_id",lev_id);
			
			//Execute query
			dataReader = comm.ExecuteReader();
			//while there are more rows
			while(dataReader.Read())
			{
				//Instantiate the level with that particular id
				myLevel = new Level(int.Parse(dataReader["lev_id"].ToString()),dataReader["name"].ToString(),dataReader["floor"].ToString(),int.Parse(dataReader["duration"].ToString()),int.Parse(dataReader["speed"].ToString()),int.Parse(dataReader["score"].ToString()));
			}
			
			//Close Connection
			conn.Close();
			conn.Dispose();
			return myLevel;	//Return level
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	
	//Get Highscore List
	public function getScores():Array{
		try{
			var scoreList = new Array();
			//Opening connection
			conn.Open();
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("SELECT user_id AS user_id, username AS name, score AS score FROM highscores ORDER BY score DESC LIMIT 10",conn);
		
			//Execute query
			dataReader = comm.ExecuteReader();
			//while there are more rows
			while(dataReader.Read())
			{
				//Instantiate every score inside the db as an object of type HIGHSCORE and add it into the scoreList Array
				scoreList.Add(new Highscore(int.Parse(dataReader["user_id"].ToString()),dataReader["name"].ToString(),int.Parse(dataReader["score"].ToString())));
			}
			
			//Close Connection
			conn.Close();
			conn.Dispose();
			return scoreList;	//Return array
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	
	//Get Highscore List
	public function getScoresByUsername(username:String):Array{
		try{
			var scoreList = new Array();
			//Opening connection
			conn.Open();
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("SELECT user_id AS user_id, username AS name, score AS score FROM highscores WHERE username = ?username ORDER BY score DESC LIMIT 10",conn);
			
			var param1:MySql.Data.MySqlClient.MySqlParameter;
			param1 = comm.Parameters.AddWithValue("?username",username);
			//Execute query
			dataReader = comm.ExecuteReader();
			//while there are more rows
			while(dataReader.Read())
			{
				//Instantiate every score inside the db as an object of type HIGHSCORE and add it into the scoreList Array
				scoreList.Add(new Highscore(int.Parse(dataReader["user_id"].ToString()),dataReader["name"].ToString(),int.Parse(dataReader["score"].ToString())));
			}
			
			//Close Connection
			conn.Close();
			conn.Dispose();
			return scoreList;	//Return array
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
	
	//Get Level List
	public function getLevels():Array{
		try{
			var levelList = new Array();
			//Opening connection
			conn.Open();
			
			//Setup the SQL Query
			comm = MySql.Data.MySqlClient.MySqlCommand("SELECT level_id AS lev_id, name AS name, floor AS floor, duration AS duration, speed AS speed, score AS score FROM levels ORDER BY level_id",conn);
		
			//Execute query
			dataReader = comm.ExecuteReader();
			//while there are more rows
			while(dataReader.Read())
			{
				//Instantiate every level inside the db as an object of type LEVEL and add it into the levelList Array
				levelList.Add(new Level(int.Parse(dataReader["lev_id"].ToString()),dataReader["name"].ToString(),dataReader["floor"].ToString(),int.Parse(dataReader["duration"].ToString()),int.Parse(dataReader["speed"].ToString()),int.Parse(dataReader["score"].ToString())));
			}
			
			//Close Connection
			conn.Close();
			conn.Dispose();
			return levelList;	//Return array
		}
		catch(e)
		{
			Debug.Log(e.ToString());
		}
	}
}