using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using VeloBrawl.Titan.Graphic;

namespace VeloBrawl.Logic.Database.Account;

public class AccountDatabase : AccountModel
{
    private string _connectionString = null!;
    private readonly System.Collections.Concurrent.ConcurrentDictionary<long, AccountModel> _cache = new();

    public AccountDatabase(string? filePath, bool loadByBackup = false)
    {
    }

    public void InitMysql(string host, int port, string user, string password, string database)
    {
        var builder = new MySqlConnectionStringBuilder
        {
            Server = host,
            Port = (uint)port,
            UserID = user,
            Password = password,
            Database = database,
            SslMode = MySqlSslMode.Disabled,
            AllowPublicKeyRetrieval = true,
            CharacterSet = "utf8mb4"
        };
        _connectionString = builder.ToString();

        using var conn = Open();
        conn.Open();
        using var cmd = new MySqlCommand(@"
            CREATE TABLE IF NOT EXISTS accounts (
                Id BIGINT PRIMARY KEY,
                Data LONGTEXT NOT NULL,
                TelegramId BIGINT DEFAULT NULL,
                BindCode VARCHAR(16) DEFAULT NULL,
                BindCodeExpiry BIGINT DEFAULT NULL,
                SwitchCode VARCHAR(16) DEFAULT NULL,
                SwitchCodeExpiry BIGINT DEFAULT NULL
            ) CHARACTER SET utf8mb4;", conn);
        cmd.ExecuteNonQuery();

        LoadAllToCache();
        ConsoleLogger.WriteTextWithPrefix(ConsoleLogger.Prefixes.Start,
            $"Database-element started! Information: element name: {GetType().Name}; database buffer length: {_cache.Count}.");
    }

    private MySqlConnection Open() => new(_connectionString);

    private void LoadAllToCache()
    {
        _cache.Clear();
        using var conn = Open();
        conn.Open();
        using var cmd = new MySqlCommand("SELECT Id, Data FROM accounts", conn);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            try
            {
                var model = JsonConvert.DeserializeObject<AccountModel>((string)reader["Data"]);
                if (model != null)
                    _cache[Convert.ToInt64(reader["Id"])] = model;
            }
            catch { }
        }
    }

    public void CyclingLoadDataFromFile(int perSec)
    {
        Task.Run(async () =>
        {
            while (true)
            {
                await Task.Delay(perSec * 1000);
                LoadDataFromFile(true);
            }
        });
    }

    public void LoadDataFromFile(bool reloaded = false)
    {
        LoadAllToCache();
        ConsoleLogger.WriteTextWithPrefix(reloaded ? ConsoleLogger.Prefixes.Load : ConsoleLogger.Prefixes.Start,
            $"Database-element {(reloaded ? "reloaded" : "started")}! Information: element name: {GetType().Name}; database buffer length: {_cache.Count}.");
    }

    public AccountModel CreateAccount(AccountModel accountModel, long accountId = -1)
    {
        accountId = accountId < 0 ? GetNewAccountId() : accountId;
        try
        {
            accountModel.SetFieldValueByAccountStructureParameterFromAccountModel(AccountStructure.AccountId, accountId);
            var json = JsonConvert.SerializeObject(accountModel, Formatting.None);
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "INSERT INTO accounts (Id, Data) VALUES (@id, @data) ON DUPLICATE KEY UPDATE Data=@data", conn);
            cmd.Parameters.AddWithValue("@id", accountId);
            cmd.Parameters.AddWithValue("@data", json);
            cmd.ExecuteNonQuery();
            _cache[accountId] = accountModel;
        }
        catch { }
        return accountModel;
    }

    public AccountModel LoadAccount(long id)
    {
        if (_cache.TryGetValue(id, out var cached))
            return cached;
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand("SELECT Data FROM accounts WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@id", id);
            var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                var model = JsonConvert.DeserializeObject<AccountModel>((string)reader["Data"])!;
                _cache[id] = model;
                return model;
            }
        }
        catch { }
        return null!;
    }

    public AccountModel SetAccountParameter(long accountId, AccountStructure accountStructure, object value)
    {
        var model = LoadAccount(accountId);
        if (model == null!) return null!;
        model.SetFieldValueByAccountStructureParameterFromAccountModel(accountStructure, value);
        return OnAssistByAccountInFile(model, true, accountId);
    }

    public object GetAccountParameter(long accountId, AccountStructure accountStructure)
    {
        var model = LoadAccount(accountId);
        if (model != null!)
            return model.GetFieldValueByAccountStructureParameterFromAccountModel(accountStructure);
        return null!;
    }

    public AccountModel OnAssistByAccountInFile(AccountModel accountModel, bool wasReplace, long accountId = -1)
    {
        accountId = accountId < 0
            ? Convert.ToInt64(accountModel.GetFieldValueByAccountStructureParameterFromAccountModel(AccountStructure.AccountId))
            : accountId;

        if (wasReplace)
            _cache[accountId] = accountModel;
        else
            _cache.TryAdd(accountId, accountModel);

        try
        {
            var json = JsonConvert.SerializeObject(accountModel, Formatting.None);
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "INSERT INTO accounts (Id, Data) VALUES (@id, @data) ON DUPLICATE KEY UPDATE Data=@data", conn);
            cmd.Parameters.AddWithValue("@id", accountId);
            cmd.Parameters.AddWithValue("@data", json);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[DB ERROR] OnAssistByAccountInFile failed: {ex.Message}");
        }

        return accountModel;
    }

    public string ConvertDictionaryToJson(Dictionary<long, AccountModel>? onRamBase = null!)
    {
        return JsonConvert.SerializeObject(onRamBase ?? new Dictionary<long, AccountModel>(_cache));
    }

    public Dictionary<long, AccountModel> ConvertJsonToDictionary(string json, bool localUs = false)
    {
        return localUs ? new Dictionary<long, AccountModel>(_cache) : JsonConvert.DeserializeObject<Dictionary<long, AccountModel>>(json)!;
    }

    public long GetMaxAccountId()
    {
        var keys = _cache.Keys.ToList(); if (keys.Count > 0) return keys.Max();
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand("SELECT COALESCE(MAX(Id),0) FROM accounts", conn);
            return Convert.ToInt64(cmd.ExecuteScalar());
        }
        catch { return 0; }
    }

    public long GetNewAccountId() => GetMaxAccountId() + 1;

    public bool HasTelegramLinked(long accountId)
    {
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand("SELECT TelegramId FROM accounts WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@id", accountId);
            var result = cmd.ExecuteScalar();
            return result != null && result != DBNull.Value && Convert.ToInt64(result) != 0;
        }
        catch { return false; }
    }

    public void SetBindCode(long accountId, string code)
    {
        var expiry = DateTimeOffset.UtcNow.AddMinutes(15).ToUnixTimeSeconds();
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE accounts SET BindCode=@code, BindCodeExpiry=@exp WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@code", code);
            cmd.Parameters.AddWithValue("@exp", expiry);
            cmd.Parameters.AddWithValue("@id", accountId);
            cmd.ExecuteNonQuery();
        }
        catch { }
    }

    public void SetSwitchCode(long accountId, string code)
    {
        var expiry = DateTimeOffset.UtcNow.AddMinutes(10).ToUnixTimeSeconds();
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE accounts SET SwitchCode=@code, SwitchCodeExpiry=@exp WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@code", code);
            cmd.Parameters.AddWithValue("@exp", expiry);
            cmd.Parameters.AddWithValue("@id", accountId);
            cmd.ExecuteNonQuery();
        }
        catch { }
    }

    public (long accountId, bool isSwitchCode)? ConsumeCode(string code)
    {
        var now = DateTimeOffset.UtcNow.ToUnixTimeSeconds();
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT Id, BindCode, SwitchCode FROM accounts WHERE (BindCode=@code AND BindCodeExpiry>@now) OR (SwitchCode=@code AND SwitchCodeExpiry>@now) LIMIT 1",
                conn);
            cmd.Parameters.AddWithValue("@code", code);
            cmd.Parameters.AddWithValue("@now", now);
            var reader = cmd.ExecuteReader();
            if (!reader.Read()) return null;

            var accountId = Convert.ToInt64(reader["Id"]);
            bool isSwitch = reader["SwitchCode"] != DBNull.Value && reader["SwitchCode"]?.ToString() == code;
            reader.Close();

            using var clear = new MySqlCommand(
                isSwitch
                    ? "UPDATE accounts SET SwitchCode=NULL, SwitchCodeExpiry=NULL WHERE Id=@id"
                    : "UPDATE accounts SET BindCode=NULL, BindCodeExpiry=NULL WHERE Id=@id", conn);
            clear.Parameters.AddWithValue("@id", accountId);
            clear.ExecuteNonQuery();

            return (accountId, isSwitch);
        }
        catch { return null; }
    }

    public long? GetAccountIdByTelegramId(long telegramId)
    {
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand("SELECT Id FROM accounts WHERE TelegramId=@tid LIMIT 1", conn);
            cmd.Parameters.AddWithValue("@tid", telegramId);
            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value) return null;
            return Convert.ToInt64(result);
        }
        catch { return null; }
    }

    public long? GetTelegramIdByAccountId(long accountId)
    {
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand("SELECT TelegramId FROM accounts WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@id", accountId);
            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value) return null;
            return Convert.ToInt64(result);
        }
        catch { return null; }
    }

    public void SetTelegramId(long accountId, long telegramId)
    {
        try
        {
            using var conn = Open();
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE accounts SET TelegramId=@tid, BindCode=NULL, BindCodeExpiry=NULL WHERE Id=@id", conn);
            cmd.Parameters.AddWithValue("@tid", telegramId);
            cmd.Parameters.AddWithValue("@id", accountId);
            cmd.ExecuteNonQuery();
        }
        catch { }
    }
}