using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using MySql.Data.MySqlClient;

namespace PatcherServer.Helper
{
	public class DatabaseHelper {
		public static void Initialize() {
			// Read connection strings.
			patcherConnectionString = 
				ConfigurationManager.ConnectionStrings["PatcherDatabase"].ConnectionString;
			accountConnectionString = 
				ConfigurationManager.ConnectionStrings["AccountDatabase"].ConnectionString;

			// Create instance
			Instance = new DatabaseHelper();
		}

		public static DatabaseHelper Instance { get; private set; }

		private static string accountConnectionString;
		private static string patcherConnectionString;

		#region Querys

		private const string CheckLoginAllowed =
			"SELECT [dbo].[LoginAllowed](@pUsername, @pPassword) AS Result;";

		private const string CheckUsernameMacAddress =
			"SELECT " +
			"CheckUsernameMac(@username, @mac) AS Result;";

		private const string CheckMacAllowed =
			"SELECT " +
			"MacAllowed(@mac) AS Result;";

		private const string CheckUserAllowed =
			"SELECT " +
			"dbo.CheckUserAllowed(@username) AS Result";

		private const string GetUsernameByCharname =
			"SELECT " +
			"dbo.UsernameByCharname(@charname) AS Result";

		private const string BlacklistUserQuery =
			"CALL BlacklistUser(@username);";

		private const string BannMacQuery =
			"CALL BannMac(@mac);";

		private const string BannUserQuery =
			"EXEC BannUser @pUsername = @username;";

		private const string AdminLoginQuery =
			"SELECT AdminLogin(@username, @password) AS Result;";

		private const string ResetUserMacsQuery =
			"CALL ResetUserMacs(@username);";

		private const string WhitelistUserQuery =
			"CALL WhitelistUser(@username);";

		private const string UnBlacklistUserQuery =
			"CALL UnBlacklistUser(@username);";

		private const string UnWhitelistUserQuery =
			"CALL UnWhitelistUserQuery(@username);";

		#endregion

		#region Account-Database Methods

		public SqlConnection GetAccountConnection() {
			SqlConnection connection = new SqlConnection(accountConnectionString);
			connection.Open();
			return connection;
		}

		public SqlCommand GetAccountCommand(string cmdText) {
			var connection = GetAccountConnection();
			SqlCommand command = new SqlCommand(cmdText, connection);
			return command;
		}

		public SqlDataReader ExecuteReader(SqlCommand command) {
			if(command.Connection == null)
				command.Connection = GetAccountConnection();
			return command.ExecuteReader();
		}

		public SqlDataReader ExecuteReader(string cmdText) {
			SqlCommand command = GetAccountCommand(cmdText);
			return command.ExecuteReader();
		}

		#endregion

		#region Patcher-Database Methods

		public MySqlConnection GetPatchConnection() {
			MySqlConnection connection = new MySqlConnection(patcherConnectionString);
			connection.Open();
			return connection;
		}

		public MySqlCommand GetPatchCommand(string cmdText) {
			MySqlCommand command = new MySqlCommand(cmdText, GetPatchConnection());
			return command;
		}

		public MySqlDataReader GetPatchReader(string cmdText) {
			var command = GetPatchCommand(cmdText);
			return command.ExecuteReader();
		}
		
		public MySqlDataReader GetPatchReader(MySqlCommand command) {
			if(command.Connection == null)
				command.Connection = GetPatchConnection();

			return command.ExecuteReader();
		}

		#endregion

		#region High-Level methods

		public bool AllowUsernameMacAddress(string username, string macAddress, out UsernameMacAddressStatus status) {
			using (var conn = GetPatchConnection())
			{
				using (var command = new MySqlCommand(CheckUsernameMacAddress, conn))
				{
					command.Parameters.AddWithValue("@username", username);
					command.Parameters.AddWithValue("@mac", macAddress);

					using (var reader = command.ExecuteReader())
					{
						reader.Read();
						int result = reader.GetInt32("Result");
						status = (UsernameMacAddressStatus)result;
						return (result == 0);
					}
				}
			}
		}
		public bool MacAllowed(string mac) {
			try
			{
				using (var con = GetPatchConnection())
				{
					using (var cmd = new MySqlCommand(CheckMacAllowed, con))
					{
						cmd.Parameters.AddWithValue("@mac", mac);
						using (var reader = cmd.ExecuteReader())
						{
							reader.Read();
							bool result = reader.GetBoolean("Result");
							return result;
						}
					}
				}
			}
			catch (System.Exception e) {
				Trace.WriteLine(e.ToString());
				throw;
			}
		}
		public bool UserAllowed(string username) {
			using (var con = GetAccountConnection()) {
				using (SqlCommand cmd = new SqlCommand(CheckUserAllowed, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					using (SqlDataReader reader = cmd.ExecuteReader()) {
						reader.Read();
						bool result = reader.GetBoolean(reader.GetOrdinal("Result"));
						return result;
					}
				}
			}
		}
		public bool CheckUserLogin(string username, string password) {
			using (var con = GetAccountConnection()) {
				using (var cmd = new SqlCommand(CheckLoginAllowed, con)) {
					cmd.Parameters.AddWithValue("@pUsername", username);
					cmd.Parameters.AddWithValue("@pPassword", password);
					using (var reader = cmd.ExecuteReader()) {
						reader.Read();
						bool result = reader.GetBoolean(reader.GetOrdinal("Result"));
						return result;
					}
				}
			}
			return false;
		}
		public string GetUsernameByCharactername(string charactername) {
			using (var con = GetAccountConnection()) {
				using (var cmd = new SqlCommand(GetUsernameByCharname, con)) {
					cmd.Parameters.AddWithValue("@charname", charactername);
					using (var reader = cmd.ExecuteReader()) {
						reader.Read();
						string name = reader.GetString(0);
						return name;
					}
				}
			}
		}
		public void BlacklistUser(string username) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(BlacklistUserQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public void BannMac(string mac) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(BannMacQuery, con)) {
					cmd.Parameters.AddWithValue("@mac", mac);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public void BannUser(string username) {
			using (var con = GetAccountConnection()) {
				using (var cmd = new SqlCommand(BannUserQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public bool AdminLogin(string username, string password) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(AdminLoginQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.Parameters.AddWithValue("@password", password);
					using (var reader = cmd.ExecuteReader()) {
						reader.Read();
						bool result = reader.GetBoolean(0);
						return result;
					}
				}
			}
		}
		public void ResetUserMacs(string username) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(ResetUserMacsQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public void WhitelistUser(string username) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(WhitelistUserQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public void UnBlacklistUser(string username) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(UnBlacklistUserQuery, con)) {
					cmd.Parameters.AddWithValue("@username", username);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public void UnWhitelistUser(string usernme) {
			using (var con = GetPatchConnection()) {
				using (var cmd = new MySqlCommand(UnWhitelistUserQuery, con)) {
					cmd.Parameters.AddWithValue("@username", usernme);
					cmd.ExecuteNonQuery();
				}
			}
		}

		#endregion
	}

	public enum UsernameMacAddressStatus {
		Ok = 0,
		DenyAccess = -1,
		BannUser = 1
	}
}