namespace Aeshnidae.Leaderboard;

/// <summary>
/// One character as the boards know them.
/// </summary>
public sealed record CharacterRow(uint Id, string Name, uint AccountId, double LastLogin);

/// <summary>
/// Everything one refresh reads from the databases, before any board is built.
/// Dictionaries are keyed by character id unless they say account.
/// </summary>
public sealed class RawData
{
    public List<CharacterRow> Characters { get; } = new();
    /// <summary>Every character on the shard by id, staff and the excluded included: an allegiance counts all its members.</summary>
    public Dictionary<uint, string> AllNames { get; } = new();
    /// <summary>Character id to monarch id, for everyone sworn to anyone (a monarch is their own).</summary>
    public Dictionary<uint, uint> MonarchOf { get; } = new();
    /// <summary>Monarch id to the allegiance's name, where one has been set.</summary>
    public Dictionary<uint, string> AllegianceNames { get; } = new();
    /// <summary>Allegiance experience each character has generated, by character id.</summary>
    public Dictionary<uint, long> AllegianceXpGenerated { get; } = new();
    public Dictionary<int, Dictionary<uint, long>> IntProps { get; } = new();
    public Dictionary<int, Dictionary<uint, long>> Int64Props { get; } = new();
    public Dictionary<string, Dictionary<uint, long>> BankByAccount { get; } = new();
    public Dictionary<uint, long> MasteryRanks { get; } = new();
    public Dictionary<uint, long> AuraRanks { get; } = new();
    public Dictionary<string, Dictionary<uint, long>> Stats { get; } = new();

    public long Int(int type, uint id) => IntProps.TryGetValue(type, out var d) && d.TryGetValue(id, out var v) ? v : 0;
    public long Int64(int type, uint id) => Int64Props.TryGetValue(type, out var d) && d.TryGetValue(id, out var v) ? v : 0;
    public long Bank(string currency, uint accountId) => BankByAccount.TryGetValue(currency, out var d) && d.TryGetValue(accountId, out var v) ? v : 0;
    public long Stat(string stat, uint id) => Stats.TryGetValue(stat, out var d) && d.TryGetValue(id, out var v) ? v : 0;
}

/// <summary>
/// The database side: the lifetime counters this mod keeps, and the one big read a
/// refresh makes across the shard, the auth database (for staff) and the sibling
/// mods' tables (bank balances, mastery ranks, aura ranks).
///
/// Counters are buffered in memory and flushed on a timer, because a kill happens
/// several times a second on a good pull and a row write per kill would be the
/// most expensive thing the server does. The buffer is what can be lost on a crash:
/// at most FlushSeconds of counting.
/// </summary>
public static class Db
{
    public const string StatsTable = "aeshnidae_stats";
    public const string HistoryTable = "aeshnidae_leaderboard_history";

    private static string _shard = "";
    private static string _authDatabase = "";

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

    private static readonly ConcurrentDictionary<(uint Character, string Stat), long> _pending = new();

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

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

            _authDatabase = ConfigManager.Config?.MySql?.Authentication?.Database ?? "";

            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $@"
                CREATE TABLE IF NOT EXISTS `{StatsTable}` (
                    `character_Id` INT UNSIGNED NOT NULL,
                    `stat`         VARCHAR(32)  NOT NULL,
                    `value`        BIGINT       NOT NULL DEFAULT 0,
                    `updated`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                                ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`character_Id`, `stat`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
            cmd.ExecuteNonQuery();

            // The boards as they stood, hour by hour: every row of every board at
            // the top of each hour, so a day's or a week's movement can be read
            // off later. Hourly rows are thinned to one a day after a week.
            using var history = conn.CreateCommand();
            history.CommandText = $@"
                CREATE TABLE IF NOT EXISTS `{HistoryTable}` (
                    `taken` DATETIME    NOT NULL,
                    `board` VARCHAR(32) NOT NULL,
                    `name`  VARCHAR(64) NOT NULL,
                    `value` BIGINT      NOT NULL,
                    PRIMARY KEY (`board`, `name`, `taken`),
                    KEY `by_time` (`taken`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
            history.ExecuteNonQuery();

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

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

    // ------------------------------------------------------------ counters

    public static void Add(uint characterId, string stat, long delta)
    {
        if (!Ready || delta == 0 || characterId == 0)
            return;

        _pending.AddOrUpdate((characterId, stat), delta, (_, v) => v + delta);
    }

    /// <summary>What has been counted since the last flush, for the live overlay.</summary>
    public static long Pending(uint characterId, string stat) =>
        _pending.TryGetValue((characterId, stat), out var v) ? v : 0;

    public static void Flush()
    {
        if (!Ready || _pending.IsEmpty)
            return;

        var keys = _pending.Keys.ToList();

        try
        {
            using var conn = Open();

            foreach (var key in keys)
            {
                if (!_pending.TryRemove(key, out var delta) || delta == 0)
                    continue;

                try
                {
                    using var cmd = conn.CreateCommand();
                    cmd.CommandText = $@"
                        INSERT INTO `{StatsTable}` (`character_Id`, `stat`, `value`) VALUES (@c, @s, @v)
                        ON DUPLICATE KEY UPDATE `value` = `value` + @v;";
                    cmd.Parameters.AddWithValue("@c", key.Character);
                    cmd.Parameters.AddWithValue("@s", key.Stat);
                    cmd.Parameters.AddWithValue("@v", delta);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    // Put it back and try again next flush; nothing is lost.
                    _pending.AddOrUpdate(key, delta, (_, v) => v + delta);
                    throw;
                }
            }
        }
        catch (Exception ex)
        {
            ModManager.Log($"[{Mod.Name}] flush failed, will retry: {ex.Message}", ModManager.LogLevel.Warn);
        }
    }

    // ------------------------------------------------------------ history

    /// <summary>When the most recent snapshot was taken, or null when there is none.</summary>
    public static DateTime? LastSnapshotUtc()
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT MAX(`taken`) FROM `{HistoryTable}`;";
        var value = cmd.ExecuteScalar();
        return value is DateTime taken ? DateTime.SpecifyKind(taken, DateTimeKind.Utc) : null;
    }

    /// <summary>Writes every row of every board as of one instant.</summary>
    public static void WriteSnapshot(DateTime takenUtc, IReadOnlyDictionary<string, List<Row>> snapshot)
    {
        using var conn = Open();
        using var transaction = conn.BeginTransaction();
        using var cmd = conn.CreateCommand();
        cmd.Transaction = transaction;
        cmd.CommandText = $"INSERT IGNORE INTO `{HistoryTable}` (`taken`, `board`, `name`, `value`) VALUES (@t, @b, @n, @v);";
        var t = cmd.Parameters.Add("@t", MySqlDbType.DateTime);
        var b = cmd.Parameters.Add("@b", MySqlDbType.VarChar);
        var n = cmd.Parameters.Add("@n", MySqlDbType.VarChar);
        var v = cmd.Parameters.Add("@v", MySqlDbType.Int64);
        t.Value = takenUtc;

        foreach (var (board, rows) in snapshot)
        {
            b.Value = board;
            foreach (var row in rows)
            {
                n.Value = row.Name.Length > 64 ? row.Name[..64] : row.Name;
                v.Value = row.Value;
                cmd.ExecuteNonQuery();
            }
        }

        transaction.Commit();
    }

    /// <summary>
    /// The boards as they stood at the latest snapshot taken at or before
    /// <paramref name="asOfUtc"/>: board, then name, to value. Null when there is
    /// no snapshot that old, with the time the snapshot was taken.
    /// </summary>
    public static (DateTime Taken, Dictionary<string, Dictionary<string, long>> Boards)? ReadBaseline(DateTime asOfUtc)
    {
        using var conn = Open();

        // The latest snapshot old enough; failing that - the history younger
        // than the window - the earliest there is, so the movers say what has
        // moved since the history began rather than nothing.
        DateTime taken;
        using (var when = conn.CreateCommand())
        {
            when.CommandText = $"SELECT COALESCE((SELECT MAX(`taken`) FROM `{HistoryTable}` WHERE `taken` <= @asOf), (SELECT MIN(`taken`) FROM `{HistoryTable}`));";
            when.Parameters.AddWithValue("@asOf", asOfUtc);
            if (when.ExecuteScalar() is not DateTime found)
                return null;
            taken = DateTime.SpecifyKind(found, DateTimeKind.Utc);
        }

        var boards = new Dictionary<string, Dictionary<string, long>>();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `board`, `name`, `value` FROM `{HistoryTable}` WHERE `taken` = @t;";
        cmd.Parameters.AddWithValue("@t", taken);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var board = reader.GetString(0);
            if (!boards.TryGetValue(board, out var rows))
                boards[board] = rows = new Dictionary<string, long>(StringComparer.OrdinalIgnoreCase);
            rows[reader.GetString(1)] = reader.GetInt64(2);
        }

        return (taken, boards);
    }

    /// <summary>
    /// A name's value on each board at the first snapshot of each day, for the
    /// last <paramref name="days"/>: board, then day (yyyy-MM-dd), to value.
    /// </summary>
    public static Dictionary<string, SortedDictionary<string, long>> ReadDaily(string name, int days)
    {
        var result = new Dictionary<string, SortedDictionary<string, long>>();
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"
            SELECT h.board, DATE(h.taken), h.value FROM `{HistoryTable}` h
            JOIN (SELECT DATE(`taken`) d, MIN(`taken`) first FROM `{HistoryTable}` WHERE `taken` >= @since GROUP BY DATE(`taken`)) f
              ON f.first = h.taken
            WHERE h.name = @n;";
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@since", DateTime.UtcNow.Date.AddDays(-Math.Max(1, days)));
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var board = reader.GetString(0);
            if (!result.TryGetValue(board, out var byDay))
                result[board] = byDay = new SortedDictionary<string, long>(StringComparer.Ordinal);
            byDay[reader.GetDateTime(1).ToString("yyyy-MM-dd")] = reader.GetInt64(2);
        }
        return result;
    }

    /// <summary>
    /// Every name's value on every board at the first snapshot of each day, for
    /// the last <paramref name="days"/>: the days in order, then board, then
    /// name, to a value per day (null where the name was not on the board).
    /// </summary>
    public static (List<string> Days, Dictionary<string, Dictionary<string, long?[]>> Boards) ReadDailyAll(int days)
    {
        var dayIndex = new Dictionary<string, int>(StringComparer.Ordinal);
        var dayList = new List<string>();
        var boards = new Dictionary<string, Dictionary<string, long?[]>>();
        using var conn = Open();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $"SELECT DISTINCT DATE(`taken`) FROM `{HistoryTable}` WHERE `taken` >= @since ORDER BY 1;";
            cmd.Parameters.AddWithValue("@since", DateTime.UtcNow.Date.AddDays(-Math.Max(1, days)));
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var day = reader.GetDateTime(0).ToString("yyyy-MM-dd");
                dayIndex[day] = dayList.Count;
                dayList.Add(day);
            }
        }
        if (dayList.Count == 0)
            return (dayList, boards);

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
                SELECT h.board, h.name, DATE(h.taken), h.value FROM `{HistoryTable}` h
                JOIN (SELECT DATE(`taken`) d, MIN(`taken`) first FROM `{HistoryTable}` WHERE `taken` >= @since GROUP BY DATE(`taken`)) f
                  ON f.first = h.taken;";
            cmd.Parameters.AddWithValue("@since", DateTime.UtcNow.Date.AddDays(-Math.Max(1, days)));
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var board = reader.GetString(0);
                var name = reader.GetString(1);
                if (!dayIndex.TryGetValue(reader.GetDateTime(2).ToString("yyyy-MM-dd"), out var index))
                    continue;
                if (!boards.TryGetValue(board, out var names))
                    boards[board] = names = new Dictionary<string, long?[]>(StringComparer.Ordinal);
                if (!names.TryGetValue(name, out var values))
                    names[name] = values = new long?[dayList.Count];
                values[index] = reader.GetInt64(3);
            }
        }
        return (dayList, boards);
    }

    /// <summary>
    /// Hourly snapshots older than a week are thinned to the one taken in the
    /// first hour of each day; those older than <paramref name="keepDays"/> go.
    /// </summary>
    public static void PruneHistory(int keepDays)
    {
        using var conn = Open();
        using (var thin = conn.CreateCommand())
        {
            thin.CommandText = $"DELETE FROM `{HistoryTable}` WHERE `taken` < @weekAgo AND HOUR(`taken`) <> 0;";
            thin.Parameters.AddWithValue("@weekAgo", DateTime.UtcNow.AddDays(-8));
            thin.ExecuteNonQuery();
        }
        if (keepDays > 0)
        {
            using var old = conn.CreateCommand();
            old.CommandText = $"DELETE FROM `{HistoryTable}` WHERE `taken` < @oldest;";
            old.Parameters.AddWithValue("@oldest", DateTime.UtcNow.AddDays(-keepDays));
            old.ExecuteNonQuery();
        }
    }

    // ------------------------------------------------------------ the read

    public static RawData Read()
    {
        var data = new RawData();

        using var conn = Open();

        // The characters that count: not deleted, not staff (by account access
        // level in the auth database), not on the exclusion list.
        var staffJoin = Mod.Settings.ExcludeStaff && _authDatabase.Length > 0
            ? $"LEFT JOIN `{_authDatabase}`.`account` a ON a.accountId = c.account_Id"
            : "";
        var staffWhere = Mod.Settings.ExcludeStaff && _authDatabase.Length > 0
            ? "AND COALESCE(a.accessLevel, 0) = 0"
            : "";

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
                SELECT c.id, c.name, c.account_Id, c.last_Login_Timestamp
                FROM `character` c {staffJoin}
                WHERE c.is_Deleted = 0 {staffWhere};";

            using var reader = cmd.ExecuteReader();
            var excluded = new HashSet<string>(Mod.Settings.ExcludeNames, StringComparer.OrdinalIgnoreCase);

            while (reader.Read())
            {
                var name = reader.GetString(1);
                if (excluded.Contains(name))
                    continue;

                data.Characters.Add(new CharacterRow(reader.GetUInt32(0), name, reader.GetUInt32(2), reader.GetDouble(3)));
            }
        }

        // Allegiances count everyone sworn, staff and the excluded included, so
        // the names and the monarch links are read for every character there is.
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT c.id, c.name FROM `character` c WHERE c.is_Deleted = 0;";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
                data.AllNames[reader.GetUInt32(0)] = reader.GetString(1);
        }

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
                SELECT i.object_Id, i.value FROM `biota_properties_i_i_d` i
                JOIN `character` c ON c.id = i.object_Id AND c.is_Deleted = 0
                WHERE i.type = {Boards.MonarchProperty};";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
                data.MonarchOf[reader.GetUInt32(0)] = reader.GetUInt32(1);
        }

        // The allegiance's name, when the monarch has given it one, sits on the
        // allegiance object - not a character - which points at its monarch.
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
                SELECT i.value, s.value FROM `biota_properties_string` s
                JOIN `biota_properties_i_i_d` i ON i.object_Id = s.object_Id AND i.type = {Boards.MonarchProperty}
                WHERE s.type = {Boards.AllegianceNameProperty};";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var name = reader.GetString(1).Trim();
                if (name.Length > 0)
                    data.AllegianceNames[reader.GetUInt32(0)] = name;
            }
        }

        foreach (var (id, value) in ReadProps(conn, "biota_properties_int64", Boards.AllegianceXpGeneratedProperty))
            data.AllegianceXpGenerated[id] = value;

        foreach (var type in Boards.IntPropertiesRead)
            data.IntProps[type] = ReadProps(conn, "biota_properties_int", type);

        foreach (var type in Boards.Int64PropertiesRead)
            data.Int64Props[type] = ReadProps(conn, "biota_properties_int64", type);

        foreach (var currency in new[] { "Radiance", "Resonance", "Luminance" })
            data.BankByAccount[currency] = ReadBank(conn, currency);

        ReadSum(conn, "aeshnidae_skill_mastery", data.MasteryRanks);
        ReadSum(conn, "aeshnidae_resonance_auras", data.AuraRanks);

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = $"SELECT `character_Id`, `stat`, `value` FROM `{StatsTable}`;";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var stat = reader.GetString(1);
                if (!data.Stats.TryGetValue(stat, out var d))
                    data.Stats[stat] = d = new Dictionary<uint, long>();
                d[reader.GetUInt32(0)] = reader.GetInt64(2);
            }
        }

        return data;
    }

    private static Dictionary<uint, long> ReadProps(MySqlConnection conn, string table, int type)
    {
        var result = new Dictionary<uint, long>();
        using var cmd = conn.CreateCommand();
        // Player biotas only: the character table is the list of those.
        cmd.CommandText = $@"
            SELECT p.object_Id, p.value FROM `{table}` p
            JOIN `character` c ON c.id = p.object_Id
            WHERE p.type = @t;";
        cmd.Parameters.AddWithValue("@t", type);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
            result[reader.GetUInt32(0)] = reader.GetInt64(1);
        return result;
    }

    private static Dictionary<uint, long> ReadBank(MySqlConnection conn, string currency)
    {
        var result = new Dictionary<uint, long>();
        try
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT `account_Id`, `amount` FROM `aeshnidae_bank` WHERE `currency` = @c;";
            cmd.Parameters.AddWithValue("@c", currency);
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
                result[reader.GetUInt32(0)] = reader.GetInt64(1);
        }
        catch (MySqlException)
        {
            // No bank table yet: the Bank mod has never run here. Empty board, not a failure.
        }
        return result;
    }

    private static void ReadSum(MySqlConnection conn, string table, Dictionary<uint, long> into)
    {
        try
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $"SELECT `character_Id`, SUM(`ranks`) FROM `{table}` GROUP BY `character_Id`;";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
                into[reader.GetUInt32(0)] = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
        }
        catch (MySqlException)
        {
            // That mod has never run here; an empty board.
        }
    }
}