namespace Aeshnidae.Mule;

/// <summary>One link of one account's vault chain.</summary>
public sealed record MuleRow(uint AccountId, int Seq, uint ContainerId);

/// <summary>One line of the ledger, for /mule log and the website.</summary>
public sealed record LedgerLine(long Id, uint AccountId, string Character, uint ObjectId, uint Wcid,
                                string ItemName, int StackSize, string Direction, string Result,
                                string Detail, DateTime At);

/// <summary>
/// The vault's two tables, in the shard database beside the characters whose items they
/// hold (credentials out of ACE's own Config.js at runtime, as every mod here does - no
/// secret in the source):
///
///   aeshnidae_mule          account -> the object ids of its vault containers, one row
///                           per link of the chain. THIS TABLE IS THE AUTHORITY FOR WHICH
///                           CONTAINER BELONGS TO WHOM. Nothing else records it: the
///                           containers themselves carry no account number, so losing a
///                           row loses the way back to the items (they are still in the
///                           biota table, still pointing at their container - `mule-admin
///                           find <guid>` reads them back).
///
///   aeshnidae_mule_ledger   append-only, one row per movement and per refusal. INSERT
///                           only: no row is ever updated or deleted. It is what tells a
///                           loss from a dupe after the fact, and it feeds /mule log and
///                           the website.
///
/// The seam left for later: a vault is identified by ACCOUNT and the ledger records which
/// CHARACTER moved each item, so grants (another account allowed at this vault) and a
/// market (an item offered out of this vault) can be added as new tables beside these two
/// without reshaping either. `seq` exists for the same reason - the chain can grow.
/// </summary>
public static class MuleDb
{
    public const string TableName = "aeshnidae_mule";
    public const string LedgerTableName = "aeshnidae_mule_ledger";

    private static string _connectionString = "";

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

    /// <summary>
    /// Every container id in aeshnidae_mule, read once at startup and kept up to date as
    /// links are created. Read by the orphan-purge guard (Patches), which runs before the
    /// world is up and cannot ask a loaded vault anything.
    /// </summary>
    private static readonly HashSet<uint> _protectedContainers = new();
    private static readonly object _protectedLock = new();

    public static bool IsVaultContainer(uint guid)
    {
        lock (_protectedLock)
            return _protectedContainers.Contains(guid);
    }

    public static int ProtectedCount
    {
        get { lock (_protectedLock) return _protectedContainers.Count; }
    }

    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}` (
                        `account_Id`   INT UNSIGNED      NOT NULL,
                        `seq`          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                        `container_Id` INT UNSIGNED      NOT NULL,
                        `created`      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (`account_Id`, `seq`),
                        UNIQUE KEY `uq_container` (`container_Id`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
                cmd.ExecuteNonQuery();
            }

            using (var cmd = conn.CreateCommand())
            {
                // DATETIME(3): two movements in the same second are normal in a bulk
                // deposit and the order they happened in is the whole point of a ledger.
                cmd.CommandText = $@"
                    CREATE TABLE IF NOT EXISTS `{LedgerTableName}` (
                        `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        `account_Id`     INT UNSIGNED    NOT NULL,
                        `character_Id`   INT UNSIGNED    NOT NULL DEFAULT 0,
                        `character_name` VARCHAR(64)     NOT NULL DEFAULT '',
                        `object_Id`      INT UNSIGNED    NOT NULL DEFAULT 0,
                        `wcid`           INT UNSIGNED    NOT NULL DEFAULT 0,
                        `item_name`      VARCHAR(128)    NOT NULL DEFAULT '',
                        `stack_size`     INT             NOT NULL DEFAULT 1,
                        `direction`      VARCHAR(16)     NOT NULL,
                        `result`         VARCHAR(48)     NOT NULL,
                        `detail`         VARCHAR(255)    NOT NULL DEFAULT '',
                        `container_Id`   INT UNSIGNED    NOT NULL DEFAULT 0,
                        `at`             DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
                        PRIMARY KEY (`id`),
                        KEY `k_account` (`account_Id`, `id`),
                        KEY `k_object`  (`object_Id`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
                cmd.ExecuteNonQuery();
            }

            lock (_protectedLock)
            {
                _protectedContainers.Clear();

                using var all = conn.CreateCommand();
                all.CommandText = $"SELECT `container_Id` FROM `{TableName}`;";
                using var r = all.ExecuteReader();

                while (r.Read())
                    _protectedContainers.Add(r.GetUInt32(0));
            }

            Ready = true;
            LastError = "";
        }
        catch (Exception ex)
        {
            Ready = false;
            LastError = ex.Message;
            ModManager.Log($"[{Mod.Name}] vault storage unavailable, every /mule verb will refuse: {ex.Message}",
                           ModManager.LogLevel.Error);
        }
    }

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

    // ---- THE CHAIN -----------------------------------------------------------

    /// <summary>Every link an account already has, in seq order. Empty if it has no vault yet.</summary>
    public static List<MuleRow> Chain(uint accountId)
    {
        var rows = new List<MuleRow>();

        if (!Ready)
            return rows;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `account_Id`, `seq`, `container_Id` FROM `{TableName}` WHERE `account_Id` = @a ORDER BY `seq`;";
        cmd.Parameters.AddWithValue("@a", accountId);

        using var r = cmd.ExecuteReader();

        while (r.Read())
            rows.Add(new MuleRow(r.GetUInt32(0), r.GetInt32(1), r.GetUInt32(2)));

        return rows;
    }

    /// <summary>
    /// Records a container id as link <paramref name="seq"/> of an account's chain.
    ///
    /// CALLED ONLY AFTER THE CONTAINER'S BIOTA IS CONFIRMED ON DISK. The order matters and
    /// it is this way round for a reason: if the server dies between the biota landing and
    /// this row, what is left behind is an empty container nobody points at - garbage, and
    /// garbage that cannot swallow an item, because no item will ever be told to point at
    /// a container whose row does not exist. The other order would leave a row naming a
    /// guid that is not in the biota table, and that guid is back in the pool
    /// GuidManager hands out - a later item could be born with it.
    ///
    /// The whole thing runs under SELECT ... FOR UPDATE on the account's existing rows, so
    /// two characters on one account filling the last slot at the same moment cannot both
    /// create link N.
    /// </summary>
    public static bool AddLink(uint accountId, int seq, uint containerId, out string error)
    {
        error = "";

        if (!Ready)
        {
            error = "the vault ledger is not available";
            return false;
        }

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

            using (var hold = conn.CreateCommand())
            {
                hold.Transaction = tx;
                hold.CommandText = $"SELECT `seq` FROM `{TableName}` WHERE `account_Id` = @a FOR UPDATE;";
                hold.Parameters.AddWithValue("@a", accountId);
                using var r = hold.ExecuteReader();
                while (r.Read()) { }
            }

            using (var ins = conn.CreateCommand())
            {
                ins.Transaction = tx;
                ins.CommandText = $"INSERT INTO `{TableName}` (`account_Id`, `seq`, `container_Id`) VALUES (@a, @s, @c);";
                ins.Parameters.AddWithValue("@a", accountId);
                ins.Parameters.AddWithValue("@s", seq);
                ins.Parameters.AddWithValue("@c", containerId);
                ins.ExecuteNonQuery();
            }

            tx.Commit();

            lock (_protectedLock)
                _protectedContainers.Add(containerId);

            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            ModManager.Log($"[{Mod.Name}] could not record vault link {seq} (container 0x{containerId:X8}) for account {accountId}: {ex.Message}",
                           ModManager.LogLevel.Error);
            return false;
        }
    }

    /// <summary>Which account a container belongs to, 0 if none. For mule-admin.</summary>
    public static uint OwnerOf(uint containerId)
    {
        if (!Ready)
            return 0;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `account_Id` FROM `{TableName}` WHERE `container_Id` = @c;";
        cmd.Parameters.AddWithValue("@c", containerId);

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

    /// <summary>Every link on the shard, for mule-admin verify.</summary>
    public static List<MuleRow> AllLinks()
    {
        var rows = new List<MuleRow>();

        if (!Ready)
            return rows;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `account_Id`, `seq`, `container_Id` FROM `{TableName}` ORDER BY `account_Id`, `seq`;";

        using var r = cmd.ExecuteReader();

        while (r.Read())
            rows.Add(new MuleRow(r.GetUInt32(0), r.GetInt32(1), r.GetUInt32(2)));

        return rows;
    }

    /// <summary>
    /// How many items the shard says are in a set of containers. The authority for a
    /// LOADED vault's count is the loaded container's own Inventory - this is the
    /// cross-check that proves the two agree, and the number the website sees.
    /// </summary>
    public static int CountInContainers(IEnumerable<uint> containerIds)
    {
        var ids = containerIds?.Distinct().ToList() ?? new List<uint>();

        if (!Ready || ids.Count == 0)
            return 0;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"SELECT COUNT(*) FROM `biota_properties_i_i_d`
                             WHERE `type` = {(ushort)PropertyInstanceId.Container}
                               AND `value` IN ({string.Join(",", ids)});";

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

    // ---- THE LEDGER ----------------------------------------------------------

    /// <summary>One movement or one refusal. Append only.</summary>
    public sealed record Entry(uint AccountId, uint CharacterId, string CharacterName, uint ObjectId,
                               uint Wcid, string ItemName, int StackSize, string Direction,
                               string Result, string Detail, uint ContainerId);

    /// <summary>
    /// Writes one row. Never throws: a ledger that cannot be written must not be able to
    /// break a movement, because the item's own biota - not this table - is what decides
    /// where the item is. A failure here is logged at Error and leaves a hole in the
    /// audit, and that is the correct trade.
    /// </summary>
    public static void Write(Entry entry) => Write(new[] { entry });

    /// <summary>
    /// Writes a batch as one multi-row INSERT. A bulk deposit uses this so a 250-item
    /// move costs one round trip rather than 250.
    /// </summary>
    public static void Write(IReadOnlyList<Entry> entries)
    {
        if (!Ready || entries is null || entries.Count == 0)
            return;

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

            var sql = new StringBuilder($@"INSERT INTO `{LedgerTableName}`
                (`account_Id`, `character_Id`, `character_name`, `object_Id`, `wcid`,
                 `item_name`, `stack_size`, `direction`, `result`, `detail`, `container_Id`) VALUES ");

            for (var i = 0; i < entries.Count; i++)
            {
                var e = entries[i];

                if (i > 0)
                    sql.Append(", ");

                sql.Append($"(@a{i}, @ci{i}, @cn{i}, @o{i}, @w{i}, @n{i}, @s{i}, @d{i}, @r{i}, @t{i}, @k{i})");

                cmd.Parameters.AddWithValue($"@a{i}", e.AccountId);
                cmd.Parameters.AddWithValue($"@ci{i}", e.CharacterId);
                cmd.Parameters.AddWithValue($"@cn{i}", Trim(e.CharacterName, 64));
                cmd.Parameters.AddWithValue($"@o{i}", e.ObjectId);
                cmd.Parameters.AddWithValue($"@w{i}", e.Wcid);
                cmd.Parameters.AddWithValue($"@n{i}", Trim(e.ItemName, 128));
                cmd.Parameters.AddWithValue($"@s{i}", e.StackSize);
                cmd.Parameters.AddWithValue($"@d{i}", Trim(e.Direction, 16));
                cmd.Parameters.AddWithValue($"@r{i}", Trim(e.Result, 48));
                cmd.Parameters.AddWithValue($"@t{i}", Trim(e.Detail, 255));
                cmd.Parameters.AddWithValue($"@k{i}", e.ContainerId);
            }

            sql.Append(';');
            cmd.CommandText = sql.ToString();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] LEDGER WRITE FAILED for {entries.Count} movement(s) - the items moved, the audit line did not: {ex.Message}",
                           ModManager.LogLevel.Error);
        }
    }

    private static string Trim(string? s, int max)
    {
        s ??= "";
        return s.Length <= max ? s : s[..max];
    }

    /// <summary>The last <paramref name="limit"/> lines for an account, newest first.</summary>
    public static List<LedgerLine> Recent(uint accountId, int limit)
    {
        var lines = new List<LedgerLine>();

        if (!Ready)
            return lines;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"SELECT `id`, `account_Id`, `character_name`, `object_Id`, `wcid`, `item_name`,
                                    `stack_size`, `direction`, `result`, `detail`, `at`
                             FROM `{LedgerTableName}` WHERE `account_Id` = @a ORDER BY `id` DESC LIMIT @n;";
        cmd.Parameters.AddWithValue("@a", accountId);
        cmd.Parameters.AddWithValue("@n", Math.Clamp(limit, 1, 200));

        using var r = cmd.ExecuteReader();

        while (r.Read())
            lines.Add(new LedgerLine(r.GetInt64(0), r.GetUInt32(1), r.GetString(2), r.GetUInt32(3),
                                     r.GetUInt32(4), r.GetString(5), r.GetInt32(6), r.GetString(7),
                                     r.GetString(8), r.GetString(9), r.GetDateTime(10)));

        return lines;
    }

    /// <summary>Totals per result, for mule-admin. Cheap enough to run on demand.</summary>
    public static List<(string Direction, string Result, long Count)> Tally()
    {
        var rows = new List<(string, string, long)>();

        if (!Ready)
            return rows;

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `direction`, `result`, COUNT(*) FROM `{LedgerTableName}` GROUP BY `direction`, `result` ORDER BY 3 DESC;";

        using var r = cmd.ExecuteReader();

        while (r.Read())
            rows.Add((r.GetString(0), r.GetString(1), r.GetInt64(2)));

        return rows;
    }

    /// <summary>
    /// Where the shard says an object is: its container, its wielder, and whether it has a
    /// location. The accounting query for a crash test - run it for an object id and the
    /// answer is one row, never two.
    /// </summary>
    public static (uint Container, uint Wielder, bool HasLocation, bool Exists) WhereIs(uint objectId)
    {
        if (!Ready)
            return (0, 0, false, false);

        using var conn = Open();

        bool exists;
        using (var e = conn.CreateCommand())
        {
            e.CommandText = "SELECT COUNT(*) FROM `biota` WHERE `id` = @o;";
            e.Parameters.AddWithValue("@o", objectId);
            exists = Convert.ToInt32(e.ExecuteScalar() ?? 0) > 0;
        }

        uint container = 0, wielder = 0;
        using (var iid = conn.CreateCommand())
        {
            iid.CommandText = "SELECT `type`, `value` FROM `biota_properties_i_i_d` WHERE `object_Id` = @o AND `type` IN (2, 3);";
            iid.Parameters.AddWithValue("@o", objectId);
            using var r = iid.ExecuteReader();

            while (r.Read())
            {
                if (r.GetUInt16(0) == 2) container = r.GetUInt32(1);
                else wielder = r.GetUInt32(1);
            }
        }

        bool hasLocation;
        using (var pos = conn.CreateCommand())
        {
            pos.CommandText = "SELECT COUNT(*) FROM `biota_properties_position` WHERE `object_Id` = @o AND `position_Type` = 1;";
            pos.Parameters.AddWithValue("@o", objectId);
            hasLocation = Convert.ToInt32(pos.ExecuteScalar() ?? 0) > 0;
        }

        return (container, wielder, hasLocation, exists);
    }
}