namespace Aeshnidae.BuffBot;

/// <summary>One town's tinker: how much skill it has been paid for, and who pays for it.</summary>
public sealed class TinkerRow
{
    public uint Landblock { get; set; }
    public int Skill { 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>
/// The tinkers' table, in the shard database beside the wardens' and the bank they both draw
/// on (credentials from ACE's Config.js at runtime, as every mod here does):
///
///   aeshnidae_town_tinker   one row per town, keyed by the landblock the tinker STANDS in
///                           (not the payer's): its skill, the pot toward the next +10 in
///                           each currency, the allegiance that finished the last step, and
///                           who paid last.
///
/// A separate table rather than more columns on <see cref="WardenDb.TableName"/>: the warden
/// and the tinker are bought independently, they are paid for in separate transactions, and
/// a row that two features lock would make one of them wait on the other for no reason. The
/// schema, the FOR UPDATE discipline and the pyreal handling are deliberately the wardens'
/// to the letter, so there is one pattern here and not two.
///
/// Radiance is taken straight out of Aeshnidae.Bank's `aeshnidae_bank` row with the same
/// guarded UPDATE WardenDb, 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 step it may complete happen in ONE transaction, with the row held
/// under SELECT ... FOR UPDATE, so two players paying the last of a step at the same instant
/// cannot buy it twice or lose one of the payments.
/// </summary>
public static class TinkerDb
{
    public const string TableName = "aeshnidae_town_tinker";
    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,
                    `skill`                  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}] tinker storage unavailable, every town's tinker reads as skill 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, TinkerRow> LoadAll()
    {
        var result = new Dictionary<uint, TinkerRow>();

        if (!Ready)
            return result;

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

            cmd.CommandText = $@"SELECT `landblock`, `skill`, `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 TinkerRow
                {
                    Landblock = r.GetUInt32(0),
                    Skill = 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>Admin: write a skill straight in, for testing. Pots and owner untouched.</summary>
    public static bool SetSkill(uint landblock, int skill)
    {
        if (!Ready)
            return false;

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

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

    /// <summary>
    /// Put <paramref name="amount"/> of one currency into a town's tinker pot and, if that
    /// finishes the next +<see cref="Settings.TinkerSkillPerStep"/>, buy it - all in one
    /// transaction. More than one step can turn over in a single payment, and does: a lump
    /// sum that covers three steps buys three (the loop below), priced one step at a time so
    /// the compounding is paid in full for each.
    ///
    /// 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 skillPerStep,
                                       out TinkerRow? 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 tinkers' 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 skill;
            long potRadiance, potPyreals;
            uint ownerId;
            string ownerName, lastPatron;

            using (var read = conn.CreateCommand())
            {
                read.Transaction = tx;
                read.CommandText = $@"SELECT `skill`, `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 tinker on the ledger.");
                }

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

            var step = Tinkers.StepOf(skill, skillPerStep) + 1;
            var (needNow, _) = price(step);

            // There is no cap on the skill, so the only thing that can stop a payment is
            // arithmetic: past ~step 331 the price leaves the range of a long. Saturated
            // prices are refused by name rather than letting a wrapped number decide.
            if (needNow >= Tinkers.Unaffordable)
            {
                tx.Rollback();
                return new PayResult(false, $"This town's tinker is at skill {skill}. The next step costs more than " +
                                            "any balance can hold - there is nothing left to pay toward.");
            }

            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.)");
                }

                // Saturating, never wrapping. The pot cannot realistically approach this,
                // because the biggest single payment is bounded by a player's balance - but a
                // pot that wrapped negative would read as "nothing paid" and quietly eat it.
                potRadiance = potRadiance > Tinkers.Unaffordable - amount ? Tinkers.Unaffordable : potRadiance + amount;
            }
            else
                potPyreals = potPyreals > Tinkers.Unaffordable - amount ? Tinkers.Unaffordable : potPyreals + amount;

            // A lump sum may cover several steps. Each is priced on its own, so the
            // compounding is never skipped, and the loop stops the moment a step is either
            // unpaid or beyond arithmetic.
            var bought = 0;

            while (true)
            {
                var next = Tinkers.StepOf(skill, skillPerStep) + 1;
                var (needRadiance, needPyreals) = price(next);

                if (needRadiance >= Tinkers.Unaffordable || needPyreals >= Tinkers.Unaffordable)
                    break;

                if (potRadiance < needRadiance || potPyreals < needPyreals)
                    break;

                skill += Math.Max(1, skillPerStep);
                potRadiance -= needRadiance;
                potPyreals -= needPyreals;
                bought++;

                ownerId = allegianceId;
                ownerName = allegianceName;
            }

            lastPatron = patronName;

            using (var write = conn.CreateCommand())
            {
                write.Transaction = tx;
                write.CommandText = $@"UPDATE `{TableName}` SET `skill` = @s, `radiance_paid` = @rp, `pyreals_paid` = @pp,
                                              `owner_allegiance_Id` = @oi, `owner_allegiance_name` = @on,
                                              `last_patron_name` = @lp
                                       WHERE `landblock` = @l;";
                write.Parameters.AddWithValue("@s", skill);
                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 TinkerRow
            {
                Landblock = landblock,
                Skill = skill,
                RadiancePaid = potRadiance,
                PyrealsPaid = potPyreals,
                OwnerAllegianceId = ownerId,
                OwnerAllegianceName = ownerName ?? "",
                LastPatronName = lastPatron ?? "",
            };

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