namespace Supercell.Laser.Server.Database
{
    using MySql.Data.MySqlClient;
    using Newtonsoft.Json;
    using Supercell.Laser.Logic.Home.Structures;
    using Supercell.Laser.Server.Database.Cache;
    using Supercell.Laser.Server.Database.Models;
    using Supercell.Laser.Server.Logic;
    using Supercell.Laser.Server.Settings;
    using Supercell.Laser.Server.Utils;

    public static class Accounts
    {
        private const uint DatabaseCommandTimeoutSeconds = 10;
        private const int DatabaseOpenRetryCount = 3;
        private const int DatabaseOpenRetryDelayMs = 150;
        private static long AvatarIdCounter;
        private static string ConnectionString;

        private static MySqlConnection OpenConnection()
        {
            MySqlException lastException = null;

            for (int attempt = 1; attempt <= DatabaseOpenRetryCount; attempt++)
            {
                var connection = new MySqlConnection(ConnectionString);
                try
                {
                    connection.Open();
                    return connection;
                }
                catch (MySqlException exception)
                {
                    connection.Dispose();
                    lastException = exception;

                    if (attempt >= DatabaseOpenRetryCount)
                    {
                        break;
                    }

                    Logger.Warning($"MySQL accounts open retry {attempt}/{DatabaseOpenRetryCount} failed: {exception.Message}");
                    Thread.Sleep(DatabaseOpenRetryDelayMs * attempt);
                }
            }

            if (lastException != null)
            {
                throw lastException;
            }

            throw new InvalidOperationException("Failed to open MySQL connection.");
        }

        public static void Init(string user, string password)
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.Server = "127.0.0.1";
            builder.UserID = user;
            builder.Password = password;
            //builder.SslMode = MySqlSslMode.Disabled;
            builder.Database = Configuration.Instance.DatabaseName;
            builder.CharacterSet = "utf8mb4";
            builder.ConnectionTimeout = DatabaseCommandTimeoutSeconds;
            builder.DefaultCommandTimeout = DatabaseCommandTimeoutSeconds;
            builder.Pooling = true;
            builder.MinimumPoolSize = 0;
            builder.MaximumPoolSize = 100;
            builder.ConnectionLifeTime = 300;
            builder.ConnectionReset = true;

            JsonConvert.DefaultSettings = () => new JsonSerializerSettings
            {
                DefaultValueHandling = DefaultValueHandling.Ignore,
                NullValueHandling = NullValueHandling.Ignore
            };

            ConnectionString = builder.ToString();

            AccountCache.Init();

            AvatarIdCounter = GetMaxAvatarId();
        }

        public static long GetMaxAvatarId()
        {
            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT coalesce(MAX(Id), 0) FROM accounts", connection);
            long result = Convert.ToInt64(command.ExecuteScalar());
            return result;
        }

        public static int GetTotalCount()
        {
            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT COUNT(*) FROM accounts", connection);
            return Convert.ToInt32(command.ExecuteScalar());
        }

        public static Account Create()
        {
            Account account = new Account();
            account.AccountId = ++AvatarIdCounter;
            account.PassToken = Helpers.RandomString(40);

            account.Avatar.AccountId = account.AccountId;
            account.Avatar.PassToken = account.PassToken;

            account.Home.HomeId = account.AccountId;

            Hero hero = new Hero(16000000, 23000000);
            account.Avatar.Heroes.Add(hero);

            string json = JsonConvert.SerializeObject(account);

            using var connection = OpenConnection();
            using var command = new MySqlCommand("INSERT INTO accounts (`Id`, `Trophies`, `Data`) VALUES (@id, @trophies, @data)", connection);
            command.Parameters.AddWithValue("@id", (long)account.AccountId);
            command.Parameters.AddWithValue("@trophies", account.Avatar.Trophies);
            command.Parameters.AddWithValue("@data", json);
            command.ExecuteNonQuery();

            AccountCache.Cache(account);

            return account;
        }

        public static void Save(Account account)
        {
            if (account == null) return;

            string json = JsonConvert.SerializeObject(account);

            using var connection = OpenConnection();
            using var command = new MySqlCommand("UPDATE accounts SET `Trophies`=@trophies, `Data`=@data WHERE Id = @id", connection);
            command.Parameters.AddWithValue("@trophies", account.Avatar.Trophies);
            command.Parameters.AddWithValue("@data", json);
            command.Parameters.AddWithValue("@id", account.AccountId);
            command.ExecuteNonQuery();
        }

        private static string NormalizeAndroidId(string androidId)
        {
            if (string.IsNullOrWhiteSpace(androidId))
            {
                return null;
            }

            return androidId.Trim();
        }

        public static Account GetBannedAccountByAndroidId(string androidId)
        {
            string normalizedAndroidId = NormalizeAndroidId(androidId);
            if (normalizedAndroidId == null)
            {
                return null;
            }

            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT `Data` FROM accounts WHERE `Data` LIKE @androidIdPattern", connection);
            command.Parameters.AddWithValue("@androidIdPattern", $"%\"AndroidId\":\"{MySqlHelper.EscapeString(normalizedAndroidId)}\"%");
            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                Account account = JsonConvert.DeserializeObject<Account>((string)reader["Data"]);
                if (account?.Avatar?.Banned == true && NormalizeAndroidId(account.Home?.AndroidId) == normalizedAndroidId)
                {
                    return account;
                }
            }
            return null;
        }

        public static void UpdateLoginMetadata(Account account, string device, string androidId, string ipAddress, string countryCode)
        {
            if (account == null)
            {
                return;
            }

            string normalizedAndroidId = NormalizeAndroidId(androidId);
            string normalizedIpAddress = BanRegistry.NormalizeIp(ipAddress);
            string normalizedCountryCode = BanRegistry.NormalizeCountryCode(countryCode);
            bool changed = false;

            if (account.Home.Device != device)
            {
                account.Home.Device = device;
                changed = true;
            }

            if (account.Home.AndroidId != normalizedAndroidId)
            {
                account.Home.AndroidId = normalizedAndroidId;
                changed = true;
            }

            if (account.Home.IpAddress != normalizedIpAddress)
            {
                account.Home.IpAddress = normalizedIpAddress;
                changed = true;
            }

            if (!string.IsNullOrWhiteSpace(normalizedIpAddress))
            {
                if (!account.Home.HasIP)
                {
                    account.Home.HasIP = true;
                    changed = true;
                }

                if (string.IsNullOrWhiteSpace(account.Home.FirstIpAddress))
                {
                    account.Home.FirstIpAddress = normalizedIpAddress;
                    changed = true;
                }
            }

            if (account.Home.CountryCode != normalizedCountryCode)
            {
                account.Home.CountryCode = normalizedCountryCode;
                changed = true;
            }

            if (changed)
            {
                Save(account);
            }
        }

        public static Account Load(long id)
        {
            if (AccountCache.IsAccountCached(id))
            {
                return AccountCache.GetAccount(id);
            }

            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT * FROM accounts WHERE Id = @id", connection);
            command.Parameters.AddWithValue("@id", id);
            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                Account account = JsonConvert.DeserializeObject<Account>((string)reader["Data"]);
                AccountCache.Cache(account);
                return account;
            }
            return null;
        }

        public static Account LoadNoCache(long id)
        {
            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT * FROM accounts WHERE Id = @id", connection);
            command.Parameters.AddWithValue("@id", id);
            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                Account account = JsonConvert.DeserializeObject<Account>((string)reader["Data"]);
                return account;
            }
            return null;
        }

        public static List<Account> GetRankingList()
        {
            #region GetGlobal

            var list = new List<Account>();

            try
            {
                using (var connection = OpenConnection())
                {
                    using var cmd = new MySqlCommand("SELECT * FROM accounts ORDER BY `Trophies` DESC LIMIT 200", connection);
                    using var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Account account = JsonConvert.DeserializeObject<Account>((string)reader["Data"]);
                        if (account?.Avatar != null && !account.Avatar.HasActiveBan())
                            list.Add(account);
                    }
                }

                return list;
            }
            catch
            {
                return list;
            }

            #endregion
        }

        public static void ExportAll(string outputPath)
        {
            var rows = new List<object>();

            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT `Id`, `Trophies`, `Data` FROM accounts ORDER BY `Id` ASC", connection);
            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                rows.Add(new
                {
                    Id = Convert.ToInt64(reader["Id"]),
                    Trophies = Convert.ToInt32(reader["Trophies"]),
                    Data = (string)reader["Data"]
                });
            }

            File.WriteAllText(outputPath, JsonConvert.SerializeObject(rows, Formatting.Indented));
        }
    }
}