using System;
using System.Collections.Generic;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using Npgsql;
using NpgsqlTypes;
using Supercell.Laser.Logic.Avatar;
using Supercell.Laser.Logic.Club;
using Supercell.Laser.Server.Database.Cache;
using Supercell.Laser.Server.Settings;
using Supercell.Laser.Logic.Util;

namespace Supercell.Laser.Server.Database
{
    public static class Alliances
    {
        private static long _allianceIdCounter;
        private static string _connectionString;

        private static readonly JsonSerializerOptions SerializeOptions = new()
        {
            DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingDefault,
            PropertyNameCaseInsensitive = true, 
            IncludeFields = true 
        };

        private static readonly JsonSerializerOptions DeserializeOptions = new()
        {
            PropertyNameCaseInsensitive = true,
            UnmappedMemberHandling = JsonUnmappedMemberHandling.Skip, 
            IncludeFields = true 
        };

        public static void Init(string user, string password)
        {
            var builder = new NpgsqlConnectionStringBuilder
            {
                Host = "127.0.0.1",
                Username = user,
                Password = password,
                Database = Configuration.Instance.DatabaseName,
                Pooling = true, 
                MaxPoolSize = 50,
                MinPoolSize = 3
            };

            _connectionString = builder.ToString();

            AllianceCache.Init();

            _allianceIdCounter = GetMaxAllianceId();
        }

        private static long GetMaxAllianceId()
        {
            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand("SELECT COALESCE(MAX(id), 0) FROM alliances", connection);
            return Convert.ToInt64(command.ExecuteScalar());
        }

        public static void Create(Alliance alliance)
        {
            if (alliance == null) return;
            alliance.Id = Interlocked.Increment(ref _allianceIdCounter);
            string json = JsonSerializer.Serialize(alliance, SerializeOptions);

            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand(
                "INSERT INTO alliances (id, name, trophies, data) VALUES (@id, @name, @trophies, @data)", connection);
            command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = (long)alliance.Id });
            command.Parameters.Add(new NpgsqlParameter("@name", NpgsqlDbType.Text) { Value = alliance.Name });
            command.Parameters.Add(new NpgsqlParameter("@trophies", NpgsqlDbType.Integer) { Value = alliance.Trophies });
            command.Parameters.Add(new NpgsqlParameter("@data", NpgsqlDbType.Jsonb) { Value = json });
            command.ExecuteNonQuery();

            AllianceCache.Cache(alliance);
        }

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

            string json = JsonSerializer.Serialize(alliance, SerializeOptions);

            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand(
                "UPDATE alliances SET trophies = @trophies, data = @data WHERE id = @id", connection);
            command.Parameters.Add(new NpgsqlParameter("@trophies", NpgsqlDbType.Integer) { Value = alliance.Trophies });
            command.Parameters.Add(new NpgsqlParameter("@data", NpgsqlDbType.Jsonb) { Value = json });
            command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = (long)alliance.Id });
            command.ExecuteNonQuery();
        }

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

            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand("SELECT data FROM alliances WHERE id = @id", connection);
            command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = id });
            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                string json = reader.GetString(reader.GetOrdinal("data"));
                var alliance = JsonSerializer.Deserialize<Alliance>(json, DeserializeOptions);
                AllianceCache.Cache(alliance);
                return alliance;
            }

            return null;
        }

        public static void Delete(long id)
        {
            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand("DELETE FROM alliances WHERE id = @id", connection);
            command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = id });
            command.ExecuteNonQuery();

            AllianceCache.RemoveAlliance(id);
        }

        public static List<Alliance> GetRankingList()
        {
            var list = new List<Alliance>();
            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand("SELECT data FROM alliances ORDER BY trophies DESC LIMIT 200", connection);
                using var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string json = reader.GetString(reader.GetOrdinal("data"));
                    list.Add(JsonSerializer.Deserialize<Alliance>(json, DeserializeOptions));
                }

                return list;
            }
            catch (Exception)
            {
                return list;
            }
        }

        // Одним запросом достаёт id->name для всех клубов. Нужен для построения
        // лидербордов без N+1 (раньше на каждый аккаунт с альянсом дергался
        // Alliances.Load → отдельное соединение к Postgres).
        public static Dictionary<long, string> GetAllNames()
        {
            var map = new Dictionary<long, string>();
            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand("SELECT id, name FROM alliances", connection);
                using var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    long id = reader.GetInt64(0);
                    string name = reader.IsDBNull(1) ? null : reader.GetString(1);
                    map[id] = name;
                }
            }
            catch (Exception ex)
            {
                Logger.Error("[Alliances.GetAllNames] " + ex.Message);
            }
            return map;
        }

        // Fix 9: Альянсы, загруженные только для показа в поиске, не должны
        // оставаться в AllianceCache бессрочно — Load() добавляет их туда,
        // а «выселять» их было некому. Теперь после сборки ответа вызываем
        // EvictWithoutSave для каждого id, который не был в кеше до вызова
        // (т.е. загружен именно здесь). Объекты не изменялись — сохранять нечего.
        public static List<Alliance> GetRandomAlliances(ClientAvatar avatar, int maxCount)
        {
            long count = Math.Min(maxCount, _allianceIdCounter);
            var list = new List<Alliance>();
            var loadedIds = new List<long>(); // Fix 9: отслеживаем свежезагруженные

            try
            {
                var rand = new Random();
                for (int i = 0; i < count; i++)
                {
                    long id = rand.NextInt64(1, _allianceIdCounter + 1);
                    bool wasCached = AllianceCache.IsAllianceCached(id); // Fix 9

                    var alliance = Load(id);
                    if (alliance != null)
                    {
                        list.Add(alliance);
                        if (!wasCached) // Fix 9: запомнить только свежезагруженные
                            loadedIds.Add(id);
                    }
                }

                return list;
            }
            catch (Exception)
            {
                return list;
            }
            finally
            {
                // Fix 9: Вытесняем всё, что загрузили здесь сами — эти объекты
                // показаны в списке поиска и больше не нужны в горячем кеше.
                foreach (long id in loadedIds)
                    AllianceCache.EvictWithoutSave(id);
            }
        }

        public static List<Alliance> GetRankingList(string searchValue, bool isHashtagSearch = false)
        {
            var list = new List<Alliance>();

            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();

                string query = isHashtagSearch
                    ? "SELECT data FROM alliances WHERE id = @searchValue ORDER BY trophies DESC LIMIT 200"
                    : "SELECT data FROM alliances WHERE name LIKE @searchValue ORDER BY trophies DESC LIMIT 200";

                using var cmd = new NpgsqlCommand(query, connection);
                if (isHashtagSearch)
                {
                    long id = LogicLongCodeGenerator.ToId(searchValue);
                    cmd.Parameters.Add(new NpgsqlParameter("@searchValue", NpgsqlDbType.Bigint) { Value = id });
                }
                else
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@searchValue", NpgsqlDbType.Text) { Value = "%" + searchValue + "%" });
                }

                using var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string json = reader.GetString(reader.GetOrdinal("data"));
                    list.Add(JsonSerializer.Deserialize<Alliance>(json, DeserializeOptions));
                }

                return list;
            }
            catch (Exception)
            {
                return list;
            }
        }
    }
}