using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using Npgsql;
using NpgsqlTypes;
using Supercell.Laser.Logic.Club;
using Supercell.Laser.Logic.Home.Items;
using Supercell.Laser.Logic.Home.Structures;
using Supercell.Laser.Logic.Battle.Structures;
using Supercell.Laser.Server.Database.Cache;
using Supercell.Laser.Server.Database.Models;
using Supercell.Laser.Server.Settings;
using Supercell.Laser.Server.Utils;

namespace Supercell.Laser.Server.Database
{
    public static class Accounts
    {
        private static long _avatarIdCounter;
        private static string _connectionString;

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

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

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

            _connectionString = builder.ToString();

            AccountCache.Init();

            _avatarIdCounter = GetMaxAvatarId();
        }

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

        public static void TruncateAll()
        {
            using var connection = new NpgsqlConnection(_connectionString);
            connection.Open();
            using var command = new NpgsqlCommand("TRUNCATE TABLE accounts, alliances RESTART IDENTITY", connection);
            command.ExecuteNonQuery();
            AccountCache.Init();
            _avatarIdCounter = 0;
        }

        public static Account Create()
        {
            var account = new Account
            {
                AccountId = Interlocked.Increment(ref _avatarIdCounter),
                PassToken = Helpers.RandomString(40)
            };

            account.Avatar.AccountId = account.AccountId;
            account.Avatar.PassToken = account.PassToken;
            account.Home.HomeId = account.AccountId;

            var notificationFactory = new NotificationFactory();
            notificationFactory.Add(new Notification
            {
                Id = 83,
                PrimaryMessageEntry = "Добро пожаловать в Project Buzz",
                SecondaryMessageEntry = "Также рекомендуем зайти в наш тг канал и чат нажав кнопку ниже! (t.me/projectbuzz)",
                ButtonMessageEntry = "Перейти",
                FileLocation = "pop_up_1920x1235_welcome.png",
                FileSha = "6bb3b752a80107a14671c7bdebe0a1b662448d0c",
                ExtLint = "brawlstars://extlink?page=https%3A%2F%2Ft.me/projectbuzz"
            });
            account.Home.NotificationFactory = notificationFactory;
            account.Avatar.Heroes.Add(new Hero(16000000, 23000000));
            account.Home.AccountCreatedTime = DateTime.UtcNow;

            try
            {
                string json = JsonSerializer.Serialize(account, SerializeOptions);

                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var command = new NpgsqlCommand(
                    "INSERT INTO accounts (id, trophies, data) VALUES (@id, @trophies, @data)", connection);
                command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = (long)account.AccountId });
                command.Parameters.Add(new NpgsqlParameter("@trophies", NpgsqlDbType.Integer) { Value = account.Avatar.Trophies });
                command.Parameters.Add(new NpgsqlParameter("@data", NpgsqlDbType.Jsonb) { Value = json });
                command.ExecuteNonQuery();

                AccountCache.Cache(account);
                return account;
            }
            catch (Exception ex)
            {
                Logger.Error($"[Database] Ошибка при создании аккаунта: {ex.Message}");
                return null;
            }
        }

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

            try
            {
                string json = JsonSerializer.Serialize(account, SerializeOptions);

                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var command = new NpgsqlCommand(
                    "UPDATE accounts SET trophies = @trophies, data = @data WHERE id = @id", connection);
                command.Parameters.Add(new NpgsqlParameter("@trophies", NpgsqlDbType.Integer) { Value = account.Avatar.Trophies });
                command.Parameters.Add(new NpgsqlParameter("@data", NpgsqlDbType.Jsonb) { Value = json });
                command.Parameters.Add(new NpgsqlParameter("@id", NpgsqlDbType.Bigint) { Value = (long)account.AccountId });
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Error($"[Database] Ошибка при сохранении аккаунта {account.AccountId}: {ex.Message}");
            }
        }

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

            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var command = new NpgsqlCommand("SELECT data FROM accounts 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 account = JsonSerializer.Deserialize<Account>(json, DeserializeOptions);
                    AccountCache.Cache(account);
                    return account;
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"[Database] Ошибка при загрузке аккаунта {id}: {ex.Message}");
            }

            return null;
        }

        public static long GetTotalAccounts()
        {
            return _avatarIdCounter;
        }

        public static Account LoadNoChache(long id)
        {
            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var command = new NpgsqlCommand("SELECT data FROM accounts 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"));
                    return JsonSerializer.Deserialize<Account>(json, DeserializeOptions);
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"[Database] Ошибка при прямой загрузке аккаунта {id}: {ex.Message}");
            }

            return null;
        }

        public static List<Account> GetAllAccounts()
        {
            var list = new List<Account>();
            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand("SELECT data FROM accounts", connection);
                using var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        string json = reader.GetString(reader.GetOrdinal("data"));
                        var account = JsonSerializer.Deserialize<Account>(json, DeserializeOptions);
                        if (account?.Avatar != null) list.Add(account);
                    }
                    catch { }
                }
            }
            catch (Exception ex) { Logger.Error("[GetAllAccounts] " + ex.Message); }
            return list;
        }

        // Fix 9: Принимает allianceNames снаружи — вызывающий (Leaderboards.Update)
        // получает их один раз на весь цикл и передаёт во все три ranking-метода,
        // устраняя два лишних «SELECT id, name FROM alliances» за цикл.
        public static List<Account> GetRankingList(Dictionary<long, string> allianceNames)
        {
            var list = new List<Account>();

            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand(
                    "SELECT data FROM accounts ORDER BY trophies DESC LIMIT 200", connection);
                using var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        string json = reader.GetString(reader.GetOrdinal("data"));
                        var account = JsonSerializer.Deserialize<Account>(json, DeserializeOptions);

                        if (account?.Avatar == null)
                            continue;

                        if (account.Avatar.Banned)
                            continue;

                        long allianceId = account.Avatar.AllianceId;
                        if (allianceId > 0 && allianceNames.TryGetValue(allianceId, out var name))
                            account.Avatar.AllianceName = name;

                        list.Add(account);
                    }
                    catch (Exception ex) { }
                }

                return list;
            }
            catch (Exception ex)
            {
                Logger.Error("[RANKING GLOBAL ERROR] " + ex);
                return list;
            }
        }

        // Fix 9: Фильтрация и сортировка по RankedRank вынесены в SQL через JSONB-выражение,
        // чтобы не десериализовывать ВСЕ аккаунты базы в память каждые 2 минуты.
        // Postgres проверяет только поле Avatar.RankedRank внутри JSONB-колонки «data»;
        // аккаунты с RankedRank == 0 (поле отсутствует при WhenWritingDefault) естественно
        // отфильтровываются: NULL::int > 0 → NULL → строка в WHERE не проходит.
        //
        // Рекомендуемый индекс (создать один раз в psql):
        //   CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_ranked_rank
        //     ON accounts (((data->'Avatar'->>'RankedRank')::int))
        //     WHERE (data->'Avatar'->>'RankedRank')::int > 0;
        public static List<Account> GetRankedRankingList(Dictionary<long, string> allianceNames)
        {
            var list = new List<Account>();

            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand(
                    "SELECT data FROM accounts " +
                    "WHERE (data->'Avatar'->>'RankedRank')::int > 0 " +
                    "ORDER BY (data->'Avatar'->>'RankedRank')::int DESC " +
                    "LIMIT 200", connection);
                using var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        string json = reader.GetString(reader.GetOrdinal("data"));
                        var account = JsonSerializer.Deserialize<Account>(json, DeserializeOptions);

                        if (account?.Avatar == null)
                            continue;

                        long allianceId = account.Avatar.AllianceId;
                        if (allianceId > 0 && allianceNames.TryGetValue(allianceId, out var name))
                            account.Avatar.AllianceName = name;

                        list.Add(account);
                    }
                    catch (Exception ex)
                    {
                        Logger.Error("[RANKED RANKING ACCOUNT SKIPPED] " + ex);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                Logger.Error("[RANKED RANKING GLOBAL ERROR] " + ex);
                return list;
            }
        }

        // Fix 9: Принимает allianceNames снаружи (экономим лишний запрос к БД).
        // Также исправлена логика альянса: раньше при allianceId > 0 и отсутствии
        // имени в словаре аккаунт пропускался (continue) — теперь просто не заполняем
        // AllianceName, но в топ включаем.
        // Добавлен срез до 200 лучших в каждом бакете после сортировки — без этого
        // список рос неограниченно.
        public static Dictionary<int, List<Account>> GetBrawlersRankingList(Dictionary<long, string> allianceNames)
        {
            var list = new Dictionary<int, List<Account>>();

            try
            {
                using var connection = new NpgsqlConnection(_connectionString);
                connection.Open();
                using var cmd = new NpgsqlCommand("SELECT data FROM accounts", connection);
                using var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    try
                    {
                        string json = reader.GetString(reader.GetOrdinal("data"));
                        var account = JsonSerializer.Deserialize<Account>(json, DeserializeOptions);

                        if (account?.Avatar == null)
                            continue;

                        // Fix 9: Не пропускаем аккаунт, если имя альянса не найдено —
                        // просто оставляем AllianceName пустым.
                        long allianceId = account.Avatar.AllianceId;
                        if (allianceId > 0 && allianceNames.TryGetValue(allianceId, out var name))
                            account.Avatar.AllianceName = name;

                        foreach (Hero hero in account.Avatar.Heroes)
                        {
                            if (!list.ContainsKey(hero.CharacterId))
                                list[hero.CharacterId] = new List<Account>();

                            if (!list[hero.CharacterId].Any(a => a.AccountId == account.AccountId))
                                list[hero.CharacterId].Add(account);
                        }
                    }
                    catch (Exception e)
                    {
                        Logger.Error("LB Error " + e);
                    }
                }
            }
            catch
            {
                return list;
            }

            foreach (var brList in list.Values)
            {
                brList.Sort((a, b) => b.Avatar.Trophies.CompareTo(a.Avatar.Trophies));

                // Fix 9: Срезаем бакет до 200 лучших — раньше он рос неограниченно
                // и полностью держался в памяти (LeaderBoards.Brawlers).
                if (brList.Count > 200)
                    brList.RemoveRange(200, brList.Count - 200);
            }

            return list;
        }
    }
}