namespace Supercell.Laser.Server.Database
{
    using MySql.Data.MySqlClient;
    using Newtonsoft.Json;
    using Supercell.Laser.Logic.Club;
    using Supercell.Laser.Server.Database.Cache;
    using Supercell.Laser.Server.Settings;

    public static class Alliances
    {
        private const uint DatabaseCommandTimeoutSeconds = 10;
        private const int DatabaseOpenRetryCount = 3;
        private const int DatabaseOpenRetryDelayMs = 150;
        private static long AllianceIdCounter;
        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 alliances 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();

            AllianceCache.Init();

            AllianceIdCounter = GetMaxAllianceId();
        }

        public static long GetMaxAllianceId()
        {
            using var connection = OpenConnection();
            using var command = new MySqlCommand("SELECT coalesce(MAX(Id), 0) FROM alliances", 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 alliances", connection);
            return Convert.ToInt32(command.ExecuteScalar());
        }

        public static void Create(Alliance alliance)
        {
            if (alliance == null) return;
            alliance.Id = ++AllianceIdCounter;
            string json = JsonConvert.SerializeObject(alliance);

            using var connection = OpenConnection();
            using var command = new MySqlCommand("INSERT INTO alliances (`Id`, `Name`, `Trophies`, `Data`) VALUES (@id, @name, @trophies, @data)", connection);
            command.Parameters.AddWithValue("@id", (long)alliance.Id);
            command.Parameters.AddWithValue("@name", alliance.Name);
            command.Parameters.AddWithValue("@trophies", alliance.Trophies);
            command.Parameters.AddWithValue("@data", json);
            command.ExecuteNonQuery();

            AllianceCache.Cache(alliance);
        }

        public static void Save(Alliance alliance)
        {
            if (alliance == null) return;

            string json = JsonConvert.SerializeObject(alliance);

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

        public static void Delete(long id)
        {
            AllianceCache.Uncache(id);
            using var connection = OpenConnection();
            using var command = new MySqlCommand("DELETE FROM alliances WHERE Id = @id", connection);
            command.Parameters.AddWithValue("@id", id);
            command.ExecuteNonQuery();
        }

        public static Alliance Load(long id)
        {
            if (AllianceCache.IsAllianceCached(id))
            {
                return AllianceCache.GetAlliance(id);
            }

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

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

            var list = new List<Alliance>();

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

                    while (reader.Read())
                        list.Add(JsonConvert.DeserializeObject<Alliance>((string)reader["Data"]));
                }

                return list;
            }
            catch
            {
                return list;
            }

            #endregion
        }

        public static List<Alliance> GetRandomAlliances(int maxCount)
        {
            long count = Math.Min(maxCount, AllianceIdCounter);

            #region GetGlobal

            var list = new List<Alliance>();

            try
            {
                Random rand = new Random();
                for (int i = 0; i < count; i++)
                {
                    var alliance = Load(rand.NextInt64(1, AllianceIdCounter+1));
                    if (alliance != null) list.Add(alliance);
                }

                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`, `Name`, `Trophies`, `Data` FROM alliances ORDER BY `Id` ASC", connection);
            using var reader = command.ExecuteReader();

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

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