using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace StarTrinity
{
	public class Database
	{
		MySqlConnection sqlConnection;
		readonly Log _log;
		readonly List<string> blockedSites = new List<string>();
        readonly string _connectionString;
        readonly UsersDatabase _usersDatabase;
		public Database(Log log, string connectionString)
		{
            _connectionString = connectionString;
			_log = log;
            _usersDatabase = new UsersDatabase(log);
#if DEBUG
			return;
#endif
			try
			{
				log.Write(null, null, "connecting to mysql database.. '{0}'", connectionString);
				sqlConnection = new MySqlConnection(connectionString);
				sqlConnection.Open();
				log.Write(null, null, "connected to mysql database");
				blockedSites = LoadBlockedSites();
			}
			catch (Exception exc)
			{
				log.WriteException(null, exc, "connecting to database " + connectionString);
				sqlConnection = null;
			}
		}
        void HandleExceptionInDatabase(Exception exc)
        {
            try
            {
                _log.WriteError(null, null, "handling exception in mysql database: {0}", exc);
                if (sqlConnection != null && sqlConnection.State != System.Data.ConnectionState.Closed) sqlConnection.Close();
                sqlConnection = null;

                _log.Write(null, null, "reconnecting to mysql database.. '{0}'", _connectionString);
                sqlConnection = new MySqlConnection(_connectionString);
                sqlConnection.Open();
                _log.Write(null, null, "connected to mysql database");
            }
            catch
            {

            }
        }

		List<string> LoadBlockedSites()
		{
			List<string> r = new List<string>();
			lock (sqlConnection)
			{
				var cmd = new MySqlCommand(String.Format("select pattern from blocked_sites"), sqlConnection);
				using (var reader = cmd.ExecuteReader())
					while (reader.Read())
						r.Add(reader.GetString("pattern"));
				
			}
			return r;
		}
		public void Dispose()
		{
			if (sqlConnection != null) { try { sqlConnection.Close(); } catch { } sqlConnection = null; }
		}

		readonly Dictionary<string, string> _passwordsForBrowserCache = new Dictionary<string, string>();
		public string GetPasswordForBrowser(string username)
		{
#if DEBUG
			return "123456"; // for testing
#endif

			lock (_passwordsForBrowserCache)
			{
				string r;
                if (_passwordsForBrowserCache.TryGetValue(username, out r))
                {
                    _log.Write(null, null, "<< Database.GetPasswordForBrowser(username = {0})  returns '{1}' from cache", username, r);
                    return r;
                }
			}

            if (sqlConnection == null) throw new InvalidOperationException("not connected to database");
            try
            {
                lock (sqlConnection)
                {
                    var cmd = new MySqlCommand(String.Format("select password from users where email = '{0}'", username), sqlConnection);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var password = reader.GetString("password");

                            lock (_passwordsForBrowserCache)
                                if (!_passwordsForBrowserCache.ContainsKey(username)) _passwordsForBrowserCache.Add(username, password);

                            _log.Write(null, null, "<< Database.GetPasswordForBrowser(username = '{0}')  returns '{1}'", username, password);
                            return password;
                        }
                    }
                    _log.WriteError(null, null, "<< Database.GetPasswordForBrowser(username = '{0}')  returns null: no such user", username);
                }
            }
            catch (Exception exc)
            {
                HandleExceptionInDatabase(exc);
            }

			return null;
		}

		public void DeleteOldConnectionRecords()
		{
			try
			{
				lock (sqlConnection)
				{
					int connectionTimeStamp = DateTimeToUnixTimeStamp(DateTime.UtcNow.AddHours(-3));
					var cmd = new MySqlCommand(String.Format("delete from node_connections where connectionTimestamp < {0}", connectionTimeStamp), sqlConnection);
					cmd.ExecuteNonQuery();
				}
			}
			catch (Exception exc)
			{
				HandleExceptionInDatabase(exc);
			}
		}
        
		/// <summary>
		/// checks credentials in users
		/// </summary>
		/// <returns>false to reject connection from DP</returns>
        public bool CheckInitializeFromDp(string email, string password, string mac, string dpIpAddress, string dpVersion)
		{
            _log.Write(null, null, ">> Database.CheckInitializeFromDp(email = {0}, password = {1}, mac = {2}, dpIpAddress = {3}, dpVersion = {4})", email, password, mac, dpIpAddress, dpVersion);

#if DEBUG
			_log.Write(null, null, "<< Database.CheckInitializeFromDp() returns true in debug mode");
			return true; // for testing
#endif
            if (email.StartsWith("free") && password == "free") return true;
			if (sqlConnection == null) throw new InvalidOperationException("not connected to database");

           // Users

            try
            {
                lock (sqlConnection)
                {
                    var cmd = new MySqlCommand(String.Format("select password from users where email = '{0}'", email), sqlConnection);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var passwordFromDb = reader.GetString("password");
                            if (password == passwordFromDb)
                            {
                                _log.Write(null, null, "<< Database.CheckInitializeFromDp(email = {0}, password = {1}, mac = {2}, dpIpAddress = {3})  returns true", email, password, mac, dpIpAddress);
                                return true;
                            }
                            else
                            {
                                _log.Write(null, null, "<< Database.CheckInitializeFromDp(email = {0}, password = {1}, mac = {2}, dpIpAddress = {3})  returns false. password is not correct", email, password, mac, dpIpAddress);
                                return false;
                            }
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                HandleExceptionInDatabase(exc);
                return false;
            }

            _log.Write(null, null, "<< Database.CheckInitializeFromDp(email = {0}, password = {1}, mac = {2}, dpIpAddress = {3})  returns false. no record is found in users table", email, password, mac, dpIpAddress);					
			return false;
		}


        public void OnProxyPingReceived(int port, string dpIpAddress, string macAddress, string email)
        {
			var dp = new DelayCounter();
			lock (sqlConnection)
			{
				int connectionTimeStamp = DateTimeToUnixTimeStamp(DateTime.UtcNow);
				var cmd4 = new MySqlCommand(String.Format("update node_connections set connectionTimestamp = {0} where email = '{1}' and ipAddress = '{2}'",
					connectionTimeStamp, email, dpIpAddress), sqlConnection);
				cmd4.ExecuteNonQuery();
			}
			dp.End(email, "Database.OnProxyPingReceived()", _log);
        }

		/// <summary>
		/// adds records to node_connections
		/// if row with the same mac address exists then update it. if row with the same IP address exists, then reject connection
		/// </summary>		
		public void OnProxyConnected(int port, string dpIpAddress, string macAddress, string email, string operatingSystem, string version, string hash, out bool reject)
		{
			_log.Write(null, null, ">> Database.OnProxyConnected(port = {0}, dpIpAddress = {1}, macAddress = {2}, email = {3}, operatingSystem = {4}, version = {5}, hash = {6})",
				port, dpIpAddress, macAddress, email, operatingSystem, version, hash);


#if DEBUG
			_log.Write(null, null, "<< Database.OnProxyConnected() does nothing in debug mode");
			reject = false;
			return; // for testing
#endif
			if (sqlConnection == null) throw new InvalidOperationException("not connected to database");

			var dp = new DelayCounter();
			reject = true;
			try
			{

				//reject = false;
				//return; //todo

				lock (sqlConnection)
				{
                   
					// update existing
					var cmd = new MySqlCommand(String.Format("select ipAddress from node_connections where macAddress = '{0}'", macAddress), sqlConnection);
					string previousIpAddress = null;
					using (var reader = cmd.ExecuteReader())
					{
						while (reader.Read())
						{
							previousIpAddress = reader.GetString("ipAddress");
							break;
						}
					}
					if (previousIpAddress != null)
					{
						var cmd2 = new MySqlCommand(String.Format("delete from node_connections where macAddress = '{0}'", macAddress), sqlConnection);
						cmd2.ExecuteNonQuery();
					}

					// check record with same ip
					var cmd3 = new MySqlCommand(String.Format("select macAddress from node_connections where ipAddress = '{0}'", dpIpAddress), sqlConnection);
					string existingMacAddress = null;
					using (var reader = cmd3.ExecuteReader())
					{
						while (reader.Read())
						{
							existingMacAddress = reader.GetString("macAddress");
							break;
						}
					}
					if (existingMacAddress != null)
                    {
                        {
                            _log.Write(null, null, "there was another connection from IP address {0} with MAC = '{1}'. removing record with previous MAC", dpIpAddress, existingMacAddress);
                            var cmd2 = new MySqlCommand(String.Format("delete from node_connections where macAddress = '{0}'", existingMacAddress), sqlConnection);
                            cmd2.ExecuteNonQuery();
                        }


                        //_log.Write(null, null, "rejecting connection from DP IP '{0}': there is another connection from that IP address with MAC = '{1}'", dpIpAddress, existingMacAddress);
                        //reject = true;
                        //return;
                    }

					// insert
					int connectionTimeStamp = DateTimeToUnixTimeStamp(DateTime.UtcNow);
					string status = "0";
					
					var cmd4 = new MySqlCommand(String.Format("insert into node_connections (ipAddress, macAddress, port, email, operatingSystem, connectionTimestamp, country, state, city, version, hash, status)" +
						" values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '', '', '', '{6}', '{7}', '{8}')",
						dpIpAddress, macAddress, port, email, operatingSystem, connectionTimeStamp, version, hash, status), sqlConnection);
					cmd4.ExecuteNonQuery();

					reject = false;
					_log.Write(null, null, "<< Database.OnProxyConnected() returns 'reject = false'.   node_connections table is updated",
						port, dpIpAddress, macAddress, email, operatingSystem, version, hash);
				}
			}
			catch (Exception exc)
			{
                HandleExceptionInDatabase(exc);
			}
			dp.End(email, "Database.OnProxyConnected()", _log);
		}

        public static int DateTimeToUnixTimeStamp(DateTime dtUtc)
        {
            DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            TimeSpan span = (dtUtc - epoch);
            return (int)span.TotalSeconds;
        }
        public static DateTime UnixTimeStampToDateTime(int ts)
        {
            DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return epoch.AddSeconds(ts);
        }
		public void OnProxyDisconnected(int port)
		{
			_log.Write(null, null, ">> Database.OnProxyDisconnected(port = {0})", port);
			Action a = () =>
				{
					try
					{
						var dp = new DelayCounter();
						lock (sqlConnection)
						{
							var cmd = new MySqlCommand(String.Format("delete from node_connections where port = '{0}'", port), sqlConnection);
							cmd.ExecuteNonQuery();
						}
						dp.End(port.ToString(), "Database.OnProxyDisconnected()", _log);
					}
					catch (Exception exc)
					{
                        HandleExceptionInDatabase(exc);
					}
				};
			a.BeginInvoke((ar) => { }, null);
		}

        public bool FilterBlockedWebSites(string urlString)
        {
            foreach (var blockedSite in blockedSites)
                if (urlString.IndexOf(blockedSite) != -1)
                {
                    _log.Write(null, null, "rejecting request to '{0}'. web site is blocked", urlString);
                    return false;
                }
            return true;
        }
        public bool AuthenticateBrowserByIp(string urlString, string browserIpAddress, string dpIpAddress)
        {
           _log.Write(null, null, ">> Database.AuthenticateBrowserByIp(urlString = {0}, browserIpAddress = {1}, dpIpAddress = {2})", urlString, browserIpAddress, dpIpAddress);

		   return true;
			if (urlString.StartsWith("proxy.startrinity.com")) return true;

            

                
#if DEBUG
			return true; // for testing
#endif
            // central server should only let a user access this ip if: ipAddress in usage matches ip in node_connections, and user_client == username, and timetotal == 0

            if (sqlConnection == null) throw new InvalidOperationException("not connected to database");
            try
            {
                lock (sqlConnection)
                {
                    var cmd = new MySqlCommand(String.Format("select timefinished from usages where ipAddress = '{0}' and authorized_browser_ip = '{1}'", dpIpAddress, browserIpAddress), sqlConnection);
                    using (var reader = cmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            var timetotal = reader.GetInt64("timefinished");

                            _log.Write(null, null, "timefinished = {0} for browser {1} DP {2}", timetotal, browserIpAddress, dpIpAddress);
                            return timetotal == 0;
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                HandleExceptionInDatabase(exc);
            }

            _log.WriteWarning(null, null, "<< Database.ValidateConnectionFromBrowser(browserIpAddress = {0}, dpIpAddress = {1}) returns false: no record in usages table", browserIpAddress, dpIpAddress);
            return false;
        }
	}
}
