namespace Aeshnidae.ResonanceAuras;

/// <summary>
/// Aura ranks live in their own table in the shard database, keyed by character and
/// aura. The same reasoning as Aeshnidae.SkillMastery's MasteryDb: kept out of the
/// character's own properties so that nothing ACE does to those (enlightenment
/// resets the luminance auras to zero) can touch them. Living out here is what makes
/// an aura survive enlightenment by construction.
///
/// Connection details come from ACE's own Config.js at runtime - no credentials in
/// the mod - and the shard db means the ranks are covered by the same backups as the
/// characters they belong to.
/// </summary>
public static class AuraDb
{
    public const string TableName = "aeshnidae_resonance_auras";

    private static string _connectionString = "";

    public static bool Ready { get; private set; }

    public static string LastError { get; private set; } = "";

    public static void Initialize()
    {
        try
        {
            var cfg = ConfigManager.Config?.MySql?.Shard
                      ?? throw new InvalidOperationException("shard database is not configured");

            _connectionString = new MySqlConnectionStringBuilder
            {
                Server = cfg.Host,
                Port = cfg.Port,
                Database = cfg.Database,
                UserID = cfg.Username,
                Password = cfg.Password,
            }.ConnectionString;

            using var conn = Open();
            using var cmd = conn.CreateCommand();

            cmd.CommandText = $@"
                CREATE TABLE IF NOT EXISTS `{TableName}` (
                    `character_Id`  INT UNSIGNED NOT NULL,
                    `aura`          VARCHAR(32)  NOT NULL,
                    `ranks`         INT          NOT NULL DEFAULT 0,
                    `updated`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                                 ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`character_Id`, `aura`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
            cmd.ExecuteNonQuery();

            Ready = true;
            LastError = "";
        }
        catch (Exception ex)
        {
            Ready = false;
            LastError = ex.Message;
            ModManager.Log($"[{Mod.Name}] storage unavailable, auras are disabled: {ex.Message}",
                           ModManager.LogLevel.Error);
        }
    }

    private static MySqlConnection Open()
    {
        var conn = new MySqlConnection(_connectionString);
        conn.Open();
        return conn;
    }

    // --------------------------------------------------------------- resonance
    //
    // Auras are paid for in Resonance, which lives in Aeshnidae.Bank's table on the same
    // shard database. This talks to that table directly rather than to the Bank mod,
    // exactly as SkillMastery does for Radiance: each mod has its own collectible
    // assembly context and sharing a type across that boundary is unsafe. The guarded
    // UPDATE is the statement BankDb.TryAdjust uses, so two writers cannot drive a
    // balance negative between them.
    //
    // What this cannot see is Bank's in-memory buffer of earnings not yet flushed (up to
    // its FlushSeconds, ten by default). A player who just turned in a quest may be told
    // they are a few points short for a few seconds. The refusal message says so.

    private const string BankTable = "aeshnidae_bank";
    private const string ResonanceKey = "Resonance";

    /// <summary>Banked Resonance for an account, as last written by Aeshnidae.Bank.</summary>
    public static long ResonanceBalance(uint accountId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `amount` FROM `{BankTable}` WHERE `account_Id` = @a AND `currency` = @c;";
        cmd.Parameters.AddWithValue("@a", accountId);
        cmd.Parameters.AddWithValue("@c", ResonanceKey);

        var result = cmd.ExecuteScalar();
        return result is null or DBNull ? 0 : Convert.ToInt64(result);
    }

    /// <summary>
    /// Takes Resonance from an account, refusing rather than going negative. The guard
    /// is in the UPDATE itself, so a purchase racing a transfer cannot overdraw.
    /// </summary>
    public static bool TryDebitResonance(uint accountId, long amount, out long remaining)
    {
        remaining = 0;

        using var conn = Open();
        using var tx = conn.BeginTransaction();

        int rows;
        using (var update = conn.CreateCommand())
        {
            update.Transaction = tx;
            update.CommandText = $@"
                UPDATE `{BankTable}` SET `amount` = `amount` - @d
                WHERE `account_Id` = @a AND `currency` = @c AND `amount` - @d >= 0;";
            update.Parameters.AddWithValue("@a", accountId);
            update.Parameters.AddWithValue("@c", ResonanceKey);
            update.Parameters.AddWithValue("@d", amount);
            rows = update.ExecuteNonQuery();
        }

        if (rows > 0)
        {
            using var select = conn.CreateCommand();
            select.Transaction = tx;
            select.CommandText = $"SELECT `amount` FROM `{BankTable}` WHERE `account_Id` = @a AND `currency` = @c;";
            select.Parameters.AddWithValue("@a", accountId);
            select.Parameters.AddWithValue("@c", ResonanceKey);
            remaining = Convert.ToInt64(select.ExecuteScalar() ?? 0L);
        }

        tx.Commit();
        return rows > 0;
    }

    /// <summary>Every aura row for a character: key -> ranks.</summary>
    public static Dictionary<string, int> Load(uint characterId)
    {
        var result = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

        if (!Ready)
            return result;

        try
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();

            cmd.CommandText = $"SELECT `aura`, `ranks` FROM `{TableName}` WHERE `character_Id` = @c;";
            cmd.Parameters.AddWithValue("@c", characterId);

            using var reader = cmd.ExecuteReader();

            while (reader.Read())
                result[reader.GetString(0)] = reader.GetInt32(1);
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] failed to read auras for 0x{characterId:X8}: {ex.Message}",
                           ModManager.LogLevel.Error);
        }

        return result;
    }

    public static void Save(uint characterId, string aura, int ranks)
    {
        if (!Ready)
            return;

        try
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();

            cmd.CommandText = $@"
                INSERT INTO `{TableName}` (`character_Id`, `aura`, `ranks`)
                VALUES (@c, @a, @r)
                ON DUPLICATE KEY UPDATE `ranks` = @r;";

            cmd.Parameters.AddWithValue("@c", characterId);
            cmd.Parameters.AddWithValue("@a", aura);
            cmd.Parameters.AddWithValue("@r", ranks);

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] failed to save {aura} for 0x{characterId:X8}: {ex.Message}",
                           ModManager.LogLevel.Error);
        }
    }
}