namespace Aeshnidae.Trials;

/// <summary>One recorded run - completed or voided.</summary>
public sealed class RunRow
{
    public uint Id { get; set; }
    public uint CharacterId { get; set; }
    public string CharacterName { get; set; } = "";
    public ushort Landblock { get; set; }
    public int Tier { get; set; }
    public float Seconds { get; set; }
    public int Kills { get; set; }
    public string Status { get; set; } = "";
    public string Reason { get; set; } = "";
    public DateTime StartedAt { get; set; }
    public DateTime FinishedAt { get; set; }
    public string Week { get; set; } = "";

    public string Time => Format(Seconds);

    public static string Format(double seconds)
    {
        var t = TimeSpan.FromSeconds(Math.Max(0, seconds));
        return t.TotalHours >= 1 ? $"{(int)t.TotalHours}:{t.Minutes:00}:{t.Seconds:00}" : $"{t.Minutes}:{t.Seconds:00}";
    }
}

/// <summary>One weekly trophy: awarded, and claimed when the winner was in the world to take it.</summary>
public sealed class AwardRow
{
    public uint Id { get; set; }
    public string Week { get; set; } = "";
    public ushort Landblock { get; set; }
    public uint CharacterId { get; set; }
    public string CharacterName { get; set; } = "";
    public int Tier { get; set; }
    public float Seconds { get; set; }
    public uint RunId { get; set; }
    public long Xp { get; set; }
    public DateTime AwardedAt { get; set; }
    public DateTime? ClaimedAt { get; set; }

    public string Time => RunRow.Format(Seconds);
}

/// <summary>
/// The two tables, in the shard database so the rows are covered by the same backups
/// as the characters they belong to (the Aeshnidae.Bank / AllegianceBounty pattern).
/// Connection details come from ACE's own Config.js at runtime - no credentials here.
///
/// `aeshnidae_trial_run` is every attempt that ended - status 'complete' with its time,
/// or 'void' with the reason - so the cooldown survives a restart and the log is honest.
/// Records are the complete rows: best per (character, landblock, tier) and server best
/// per (landblock, tier). `aeshnidae_trial_award` is one row per (week, landblock): the
/// trophy, claimed at once when the winner was online, else at their next login. Its
/// unique key is what makes `trial-award` safe to run twice.
///
/// Nothing on a tick touches this class: a run is memory until it ends.
/// </summary>
public static class TrialDb
{
    public const string RunTable = "aeshnidae_trial_run";
    public const string AwardTable = "aeshnidae_trial_award";

    private static string _connectionString = "";

    public static bool Ready { get; private set; }

    public static void Initialize()
    {
        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 `{RunTable}` (
                `id`             INT UNSIGNED      NOT NULL AUTO_INCREMENT,
                `character_Id`   INT UNSIGNED      NOT NULL,
                `character_Name` VARCHAR(64)       NOT NULL,
                `landblock`      SMALLINT UNSIGNED NOT NULL,
                `tier`           INT               NOT NULL,
                `seconds`        FLOAT             NOT NULL,
                `kills`          INT               NOT NULL,
                `status`         VARCHAR(16)       NOT NULL,
                `reason`         VARCHAR(96)       NOT NULL DEFAULT '',
                `started_at`     DATETIME          NOT NULL,
                `finished_at`    DATETIME          NOT NULL,
                `week`           VARCHAR(8)        NOT NULL,
                PRIMARY KEY (`id`),
                KEY `board` (`landblock`, `status`, `tier`, `seconds`),
                KEY `character` (`character_Id`, `landblock`, `started_at`),
                KEY `week` (`week`, `landblock`, `status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

            CREATE TABLE IF NOT EXISTS `{AwardTable}` (
                `id`             INT UNSIGNED      NOT NULL AUTO_INCREMENT,
                `week`           VARCHAR(8)        NOT NULL,
                `landblock`      SMALLINT UNSIGNED NOT NULL,
                `character_Id`   INT UNSIGNED      NOT NULL,
                `character_Name` VARCHAR(64)       NOT NULL,
                `tier`           INT               NOT NULL,
                `seconds`        FLOAT             NOT NULL,
                `run_Id`         INT UNSIGNED      NOT NULL,
                `xp`             BIGINT            NOT NULL,
                `awarded_at`     DATETIME          NOT NULL,
                `claimed_at`     DATETIME          NULL,
                PRIMARY KEY (`id`),
                UNIQUE KEY `week_landblock` (`week`, `landblock`),
                KEY `pending` (`character_Id`, `claimed_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        cmd.ExecuteNonQuery();

        Ready = true;
    }

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

    // ------------------------------------------------------------ runs

    /// <summary>Records an ended run and returns its id.</summary>
    public static uint InsertRun(RunRow r)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"
            INSERT INTO `{RunTable}`
                (`character_Id`, `character_Name`, `landblock`, `tier`, `seconds`, `kills`, `status`, `reason`, `started_at`, `finished_at`, `week`)
            VALUES (@cid, @cname, @lb, @tier, @sec, @kills, @status, @reason, @start, @finish, @week);
            SELECT LAST_INSERT_ID();";
        cmd.Parameters.AddWithValue("@cid", r.CharacterId);
        cmd.Parameters.AddWithValue("@cname", r.CharacterName);
        cmd.Parameters.AddWithValue("@lb", r.Landblock);
        cmd.Parameters.AddWithValue("@tier", r.Tier);
        cmd.Parameters.AddWithValue("@sec", r.Seconds);
        cmd.Parameters.AddWithValue("@kills", r.Kills);
        cmd.Parameters.AddWithValue("@status", r.Status);
        cmd.Parameters.AddWithValue("@reason", r.Reason.Length > 96 ? r.Reason[..96] : r.Reason);
        cmd.Parameters.AddWithValue("@start", r.StartedAt);
        cmd.Parameters.AddWithValue("@finish", r.FinishedAt);
        cmd.Parameters.AddWithValue("@week", r.Week);
        return Convert.ToUInt32(cmd.ExecuteScalar());
    }

    /// <summary>When this character last started an attempt at this landblock (UTC), or null.</summary>
    public static DateTime? LastAttempt(uint characterId, ushort landblock)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT MAX(`started_at`) FROM `{RunTable}` WHERE `character_Id` = @cid AND `landblock` = @lb;";
        cmd.Parameters.AddWithValue("@cid", characterId);
        cmd.Parameters.AddWithValue("@lb", landblock);
        var v = cmd.ExecuteScalar();
        return v is DateTime dt ? DateTime.SpecifyKind(dt, DateTimeKind.Utc) : null;
    }

    /// <summary>A character's best complete run at a landblock and tier, or null.</summary>
    public static RunRow? PersonalBest(uint characterId, ushort landblock, int tier)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} WHERE `character_Id` = @cid AND `landblock` = @lb AND `tier` = @tier AND `status` = 'complete'
                             ORDER BY `seconds` ASC, `finished_at` ASC LIMIT 1;";
        cmd.Parameters.AddWithValue("@cid", characterId);
        cmd.Parameters.AddWithValue("@lb", landblock);
        cmd.Parameters.AddWithValue("@tier", tier);
        return ReadRuns(cmd).FirstOrDefault();
    }

    /// <summary>A character's best complete run at each tier of each landblock.</summary>
    public static List<RunRow> PersonalBests(uint characterId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} r WHERE `character_Id` = @cid AND `status` = 'complete'
                             AND `seconds` = (SELECT MIN(`seconds`) FROM `{RunTable}` b WHERE b.`character_Id` = r.`character_Id` AND b.`landblock` = r.`landblock` AND b.`tier` = r.`tier` AND b.`status` = 'complete')
                             ORDER BY `landblock`, `tier`;";
        cmd.Parameters.AddWithValue("@cid", characterId);
        return Distinct(ReadRuns(cmd), r => (r.Landblock, r.Tier));
    }

    /// <summary>The server's best complete run at a landblock and tier, or null.</summary>
    public static RunRow? ServerBest(ushort landblock, int tier)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} WHERE `landblock` = @lb AND `tier` = @tier AND `status` = 'complete'
                             ORDER BY `seconds` ASC, `finished_at` ASC LIMIT 1;";
        cmd.Parameters.AddWithValue("@lb", landblock);
        cmd.Parameters.AddWithValue("@tier", tier);
        return ReadRuns(cmd).FirstOrDefault();
    }

    /// <summary>The server's best complete run at every (landblock, tier) that has one.</summary>
    public static List<RunRow> ServerBests()
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} r WHERE `status` = 'complete'
                             AND `seconds` = (SELECT MIN(`seconds`) FROM `{RunTable}` b WHERE b.`landblock` = r.`landblock` AND b.`tier` = r.`tier` AND b.`status` = 'complete')
                             ORDER BY `landblock`, `tier`, `finished_at`;";
        return Distinct(ReadRuns(cmd), r => (r.Landblock, r.Tier));
    }

    /// <summary>
    /// The top complete runs at a landblock, all tiers, one row per character (their
    /// best), fastest first.
    /// </summary>
    public static List<RunRow> Top(ushort landblock, int count)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} r WHERE `landblock` = @lb AND `status` = 'complete'
                             AND `seconds` = (SELECT MIN(`seconds`) FROM `{RunTable}` b WHERE b.`character_Id` = r.`character_Id` AND b.`landblock` = r.`landblock` AND b.`status` = 'complete')
                             ORDER BY `seconds` ASC, `finished_at` ASC LIMIT @n;";
        cmd.Parameters.AddWithValue("@lb", landblock);
        cmd.Parameters.AddWithValue("@n", Math.Max(1, count) * 2);
        return Distinct(ReadRuns(cmd), r => r.CharacterId).Take(count).ToList();
    }

    /// <summary>The fastest complete run of a week at a landblock, any tier, or null.</summary>
    public static RunRow? WeekBest(string week, ushort landblock)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"{SelectRun} WHERE `week` = @week AND `landblock` = @lb AND `status` = 'complete'
                             ORDER BY `seconds` ASC, `finished_at` ASC LIMIT 1;";
        cmd.Parameters.AddWithValue("@week", week);
        cmd.Parameters.AddWithValue("@lb", landblock);
        return ReadRuns(cmd).FirstOrDefault();
    }

    /// <summary>Records held by a character: how many (landblock, tier) server bests are theirs.</summary>
    public static List<RunRow> RecordsHeldBy(uint characterId) =>
        ServerBests().Where(r => r.CharacterId == characterId).ToList();

    private const string SelectRun =
        $"SELECT `id`, `character_Id`, `character_Name`, `landblock`, `tier`, `seconds`, `kills`, `status`, `reason`, `started_at`, `finished_at`, `week` FROM `{RunTable}`";

    private static List<RunRow> ReadRuns(MySqlCommand cmd)
    {
        var list = new List<RunRow>();
        using var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(new RunRow
            {
                Id = reader.GetUInt32(0),
                CharacterId = reader.GetUInt32(1),
                CharacterName = reader.GetString(2),
                Landblock = reader.GetUInt16(3),
                Tier = reader.GetInt32(4),
                Seconds = reader.GetFloat(5),
                Kills = reader.GetInt32(6),
                Status = reader.GetString(7),
                Reason = reader.GetString(8),
                StartedAt = DateTime.SpecifyKind(reader.GetDateTime(9), DateTimeKind.Utc),
                FinishedAt = DateTime.SpecifyKind(reader.GetDateTime(10), DateTimeKind.Utc),
                Week = reader.GetString(11),
            });
        }

        return list;
    }

    /// <summary>Ties on seconds come back as several rows; the earliest finish keeps the record.</summary>
    private static List<RunRow> Distinct<TKey>(List<RunRow> rows, Func<RunRow, TKey> key) where TKey : notnull
    {
        var seen = new HashSet<TKey>();
        var result = new List<RunRow>();

        foreach (var r in rows.OrderBy(r => r.Seconds).ThenBy(r => r.FinishedAt))
        {
            if (seen.Add(key(r)))
                result.Add(r);
        }

        return result;
    }

    // ------------------------------------------------------------ awards

    /// <summary>Records a trophy. Returns false when this (week, landblock) already has one.</summary>
    public static bool InsertAward(AwardRow a)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"
            INSERT IGNORE INTO `{AwardTable}`
                (`week`, `landblock`, `character_Id`, `character_Name`, `tier`, `seconds`, `run_Id`, `xp`, `awarded_at`, `claimed_at`)
            VALUES (@week, @lb, @cid, @cname, @tier, @sec, @run, @xp, @at, @claimed);";
        cmd.Parameters.AddWithValue("@week", a.Week);
        cmd.Parameters.AddWithValue("@lb", a.Landblock);
        cmd.Parameters.AddWithValue("@cid", a.CharacterId);
        cmd.Parameters.AddWithValue("@cname", a.CharacterName);
        cmd.Parameters.AddWithValue("@tier", a.Tier);
        cmd.Parameters.AddWithValue("@sec", a.Seconds);
        cmd.Parameters.AddWithValue("@run", a.RunId);
        cmd.Parameters.AddWithValue("@xp", a.Xp);
        cmd.Parameters.AddWithValue("@at", a.AwardedAt);
        cmd.Parameters.AddWithValue("@claimed", (object?)a.ClaimedAt ?? DBNull.Value);
        var n = cmd.ExecuteNonQuery();

        if (n > 0)
        {
            cmd.CommandText = "SELECT LAST_INSERT_ID();";
            cmd.Parameters.Clear();
            a.Id = Convert.ToUInt32(cmd.ExecuteScalar());
        }

        return n > 0;
    }

    public static bool WeekAwarded(string week, ushort landblock)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT COUNT(*) FROM `{AwardTable}` WHERE `week` = @week AND `landblock` = @lb;";
        cmd.Parameters.AddWithValue("@week", week);
        cmd.Parameters.AddWithValue("@lb", landblock);
        return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
    }

    public static void MarkClaimed(uint awardId, DateTime whenUtc)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"UPDATE `{AwardTable}` SET `claimed_at` = @at WHERE `id` = @id;";
        cmd.Parameters.AddWithValue("@at", whenUtc);
        cmd.Parameters.AddWithValue("@id", awardId);
        cmd.ExecuteNonQuery();
    }

    /// <summary>Trophies a character has not yet received, oldest first.</summary>
    public static List<AwardRow> PendingFor(uint characterId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"{SelectAward} WHERE `character_Id` = @cid AND `claimed_at` IS NULL ORDER BY `awarded_at`;";
        cmd.Parameters.AddWithValue("@cid", characterId);
        return ReadAwards(cmd);
    }

    /// <summary>Every trophy a character has won.</summary>
    public static List<AwardRow> AwardsOf(uint characterId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"{SelectAward} WHERE `character_Id` = @cid ORDER BY `awarded_at` DESC;";
        cmd.Parameters.AddWithValue("@cid", characterId);
        return ReadAwards(cmd);
    }

    /// <summary>The most recent trophies, newest first.</summary>
    public static List<AwardRow> RecentAwards(int count)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"{SelectAward} ORDER BY `awarded_at` DESC, `landblock` LIMIT @n;";
        cmd.Parameters.AddWithValue("@n", Math.Max(1, count));
        return ReadAwards(cmd);
    }

    private const string SelectAward =
        $"SELECT `id`, `week`, `landblock`, `character_Id`, `character_Name`, `tier`, `seconds`, `run_Id`, `xp`, `awarded_at`, `claimed_at` FROM `{AwardTable}`";

    private static List<AwardRow> ReadAwards(MySqlCommand cmd)
    {
        var list = new List<AwardRow>();
        using var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(new AwardRow
            {
                Id = reader.GetUInt32(0),
                Week = reader.GetString(1),
                Landblock = reader.GetUInt16(2),
                CharacterId = reader.GetUInt32(3),
                CharacterName = reader.GetString(4),
                Tier = reader.GetInt32(5),
                Seconds = reader.GetFloat(6),
                RunId = reader.GetUInt32(7),
                Xp = reader.GetInt64(8),
                AwardedAt = DateTime.SpecifyKind(reader.GetDateTime(9), DateTimeKind.Utc),
                ClaimedAt = reader.IsDBNull(10) ? null : DateTime.SpecifyKind(reader.GetDateTime(10), DateTimeKind.Utc),
            });
        }

        return list;
    }
}