namespace Aeshnidae.BuffBot;

/// <summary>One town's warden: what it has been paid for and who pays for it.</summary>
public sealed class WardenRow
{
    public uint Landblock { get; set; }
    public int Tier { get; set; }
    public long RadiancePaid { get; set; }
    public long PyrealsPaid { get; set; }
    public uint OwnerAllegianceId { get; set; }
    public string OwnerAllegianceName { get; set; } = "";
    public string LastPatronName { get; set; } = "";
}

/// <summary>What a contribution did.</summary>
public sealed record PayResult(bool Ok, string Message, bool Promoted = false, int Tier = 0);

/// <summary>
/// The wardens' table, in the shard database beside the characters and the bank it draws on
/// (credentials from ACE's Config.js at runtime, as every mod here does):
///
///   aeshnidae_town_warden   one row per town, keyed by the landblock the warden STANDS in
///                           (not the payer's): its tier, the pot toward the next tier in
///                           each currency, the allegiance that finished the last tier, and
///                           who paid last.
///
/// Radiance is taken straight out of Aeshnidae.Bank's `aeshnidae_bank` row with the same
/// guarded UPDATE that BankDb.TryAdjust and SkillMastery's MasteryDb use - cross-mod types
/// are unsafe, the row is not - so two buyers cannot drive a balance negative between them.
/// What this cannot see is Bank's in-memory buffer of earnings not yet flushed (ten seconds
/// by default); a player who just killed something may read as a few thousand short for a
/// moment, and the refusal says so.
///
/// A contribution and the tier it may complete happen in ONE transaction, with the row held
/// under SELECT ... FOR UPDATE, so two players paying the last of a tier at the same instant
/// cannot buy it twice or lose one of the payments.
/// </summary>
public static class WardenDb
{
    public const string TableName = "aeshnidae_town_warden";
    private const string BankTable = "aeshnidae_bank";
    private const string RadianceKey = "Radiance";

    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}` (
                    `landblock`              INT UNSIGNED NOT NULL,
                    `tier`                   INT          NOT NULL DEFAULT 0,
                    `radiance_paid`          BIGINT       NOT NULL DEFAULT 0,
                    `pyreals_paid`           BIGINT       NOT NULL DEFAULT 0,
                    `owner_allegiance_Id`    INT UNSIGNED NOT NULL DEFAULT 0,
                    `owner_allegiance_name`  VARCHAR(64)  NOT NULL DEFAULT '',
                    `last_patron_name`       VARCHAR(64)  NOT NULL DEFAULT '',
                    `updated`                DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                                          ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`landblock`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
            cmd.ExecuteNonQuery();

            Ready = true;
            LastError = "";
        }
        catch (Exception ex)
        {
            Ready = false;
            LastError = ex.Message;
            ModManager.Log($"[{Mod.Name}] warden storage unavailable, every town reads as tier 0: {ex.Message}",
                           ModManager.LogLevel.Error);
        }
    }

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

    /// <summary>Every town's row, for the in-memory cache.</summary>
    public static Dictionary<uint, WardenRow> LoadAll()
    {
        var result = new Dictionary<uint, WardenRow>();

        if (!Ready)
            return result;

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

            cmd.CommandText = $@"SELECT `landblock`, `tier`, `radiance_paid`, `pyreals_paid`,
                                        `owner_allegiance_Id`, `owner_allegiance_name`, `last_patron_name`
                                 FROM `{TableName}`;";

            using var r = cmd.ExecuteReader();

            while (r.Read())
                result[r.GetUInt32(0)] = new WardenRow
                {
                    Landblock = r.GetUInt32(0),
                    Tier = r.GetInt32(1),
                    RadiancePaid = r.GetInt64(2),
                    PyrealsPaid = r.GetInt64(3),
                    OwnerAllegianceId = r.GetUInt32(4),
                    OwnerAllegianceName = r.GetString(5),
                    LastPatronName = r.GetString(6),
                };
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] could not read {TableName}: {ex.Message}", ModManager.LogLevel.Error);
        }

        return result;
    }

    /// <summary>Banked Radiance for an account, as last written by Aeshnidae.Bank.</summary>
    public static long RadianceBalance(uint accountId)
    {
        if (!Ready)
            return 0;

        try
        {
            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", RadianceKey);

            var result = cmd.ExecuteScalar();
            return result is null or DBNull ? 0 : Convert.ToInt64(result);
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] could not read a Radiance balance: {ex.Message}", ModManager.LogLevel.Error);
            return 0;
        }
    }

    /// <summary>Admin: write a tier straight in, for testing. Pots and owner untouched.</summary>
    public static bool SetTier(uint landblock, int tier)
    {
        if (!Ready)
            return false;

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

            cmd.CommandText = $@"INSERT INTO `{TableName}` (`landblock`, `tier`) VALUES (@l, @t)
                                 ON DUPLICATE KEY UPDATE `tier` = @t;";
            cmd.Parameters.AddWithValue("@l", landblock);
            cmd.Parameters.AddWithValue("@t", tier);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] could not set the tier of 0x{landblock:X4}: {ex.Message}", ModManager.LogLevel.Error);
            return false;
        }
    }

    /// <summary>
    /// Put <paramref name="amount"/> of one currency into a town's pot and, if that finishes
    /// the next tier, buy it - all in one transaction.
    ///
    /// Radiance is debited from the payer's bank row here. PYREALS ARE NOT: the caller has
    /// already taken them out of the pack (only the world thread may touch inventory), so a
    /// refusal from this method means the caller must put them back. That is why the row is
    /// locked and every refusal happens before anything is written.
    /// </summary>
    public static PayResult Contribute(uint landblock, bool radiance, long amount, uint accountId,
                                       uint allegianceId, string allegianceName, string patronName,
                                       Func<int, (long Radiance, long Pyreals)> price, int maxTier,
                                       out WardenRow? row)
    {
        row = null;

        // A debit is a debit: a zero or negative amount would credit the payer through the
        // same guarded UPDATE, and would add nothing to the pot. Refused before anything opens.
        if (amount <= 0)
            return new PayResult(false, "Give an amount above zero.");

        if (!Ready)
            return new PayResult(false, "The wardens' ledger is not available right now.");

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

            using (var seed = conn.CreateCommand())
            {
                seed.Transaction = tx;
                seed.CommandText = $"INSERT IGNORE INTO `{TableName}` (`landblock`) VALUES (@l);";
                seed.Parameters.AddWithValue("@l", landblock);
                seed.ExecuteNonQuery();
            }

            int tier;
            long potRadiance, potPyreals;
            uint ownerId;
            string ownerName, lastPatron;

            using (var read = conn.CreateCommand())
            {
                read.Transaction = tx;
                read.CommandText = $@"SELECT `tier`, `radiance_paid`, `pyreals_paid`, `owner_allegiance_Id`,
                                             `owner_allegiance_name`, `last_patron_name`
                                      FROM `{TableName}` WHERE `landblock` = @l FOR UPDATE;";
                read.Parameters.AddWithValue("@l", landblock);

                using var r = read.ExecuteReader();

                if (!r.Read())
                {
                    r.Close();
                    tx.Rollback();
                    return new PayResult(false, "This town has no warden on the ledger.");
                }

                tier = r.GetInt32(0);
                potRadiance = r.GetInt64(1);
                potPyreals = r.GetInt64(2);
                ownerId = r.GetUInt32(3);
                ownerName = r.GetString(4);
                lastPatron = r.GetString(5);
            }

            if (tier >= maxTier)
            {
                tx.Rollback();
                return new PayResult(false, $"This town's warden is already at tier {tier}, the highest there is. Nothing more can be paid.");
            }

            if (radiance)
            {
                using var debit = conn.CreateCommand();
                debit.Transaction = tx;
                debit.CommandText = $@"UPDATE `{BankTable}` SET `amount` = `amount` - @x, `updated` = UTC_TIMESTAMP()
                                       WHERE `account_Id` = @a AND `currency` = @c AND `amount` - @x >= 0;";
                debit.Parameters.AddWithValue("@a", accountId);
                debit.Parameters.AddWithValue("@c", RadianceKey);
                debit.Parameters.AddWithValue("@x", amount);

                if (debit.ExecuteNonQuery() == 0)
                {
                    tx.Rollback();
                    return new PayResult(false, $"Your bank does not hold {amount:N0} Radiance. (Radiance earned in the last few seconds may not be banked yet.)");
                }

                potRadiance += amount;
            }
            else
                potPyreals += amount;

            var (needRadiance, needPyreals) = price(tier + 1);
            var promoted = potRadiance >= needRadiance && potPyreals >= needPyreals;

            if (promoted)
            {
                tier += 1;
                potRadiance -= needRadiance;
                potPyreals -= needPyreals;
                ownerId = allegianceId;
                ownerName = allegianceName;
            }

            lastPatron = patronName;

            using (var write = conn.CreateCommand())
            {
                write.Transaction = tx;
                write.CommandText = $@"UPDATE `{TableName}` SET `tier` = @t, `radiance_paid` = @rp, `pyreals_paid` = @pp,
                                              `owner_allegiance_Id` = @oi, `owner_allegiance_name` = @on,
                                              `last_patron_name` = @lp
                                       WHERE `landblock` = @l;";
                write.Parameters.AddWithValue("@t", tier);
                write.Parameters.AddWithValue("@rp", potRadiance);
                write.Parameters.AddWithValue("@pp", potPyreals);
                write.Parameters.AddWithValue("@oi", ownerId);
                write.Parameters.AddWithValue("@on", ownerName ?? "");
                write.Parameters.AddWithValue("@lp", lastPatron ?? "");
                write.Parameters.AddWithValue("@l", landblock);
                write.ExecuteNonQuery();
            }

            tx.Commit();

            row = new WardenRow
            {
                Landblock = landblock,
                Tier = tier,
                RadiancePaid = potRadiance,
                PyrealsPaid = potPyreals,
                OwnerAllegianceId = ownerId,
                OwnerAllegianceName = ownerName ?? "",
                LastPatronName = lastPatron ?? "",
            };

            return new PayResult(true, "", promoted, tier);
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] a contribution to 0x{landblock:X4} failed: {ex.Message}", ModManager.LogLevel.Error);
            return new PayResult(false, "The wardens' ledger could not record that. Nothing was taken.");
        }
    }
}