namespace Aeshnidae.AllegianceBounty;

/// <summary>An active bounty entry: one quest, one allegiance, one day.</summary>
public class BountyEntry
{
    public uint MonarchId { get; set; }
    public string QuestName { get; set; } = "";
    public string Flag { get; set; } = "";
    public double Percent { get; set; }
    public string CompletedBy { get; set; } = "";
    public uint CompletedById { get; set; }
    public DateTime CompletedAt { get; set; }
    public DateTime ExpiresAt { get; set; }

    public bool Expired(DateTime nowUtc) => ExpiresAt <= nowUtc;
}

/// <summary>
/// Entries live in their own table in the shard database, keyed by the monarch's guid,
/// so a bounty survives a restart and dies on its own clock, not the server's.
///
/// The shard db is used rather than a side file so the rows are covered by the same
/// backups as the characters they belong to; the pattern is Aeshnidae.Bank's. Connection
/// details come from ACE's own Config.js at runtime - no credentials in the mod.
///
/// The hot path (every kill's GrantXP) never touches this class: Bounty keeps the active
/// entries in memory and only writes through here when one is gained.
/// </summary>
public static class BountyDb
{
    public const string TableName = "aeshnidae_allegiance_bounty";

    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 `{TableName}` (
                `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `monarch_Id`      INT UNSIGNED NOT NULL,
                `quest_name`      VARCHAR(128) NOT NULL,
                `flag`            VARCHAR(255) NOT NULL,
                `percent`         DOUBLE       NOT NULL,
                `completed_by`    VARCHAR(64)  NOT NULL,
                `completed_by_Id` INT UNSIGNED NOT NULL,
                `completed_at`    DATETIME     NOT NULL,
                `expires_at`      DATETIME     NOT NULL,
                PRIMARY KEY (`id`),
                UNIQUE KEY `monarch_quest` (`monarch_Id`, `quest_name`),
                KEY `expires` (`expires_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        cmd.ExecuteNonQuery();

        // The personal layer's table lives beside it (Personal.cs).
        Personal.CreateTable(conn);

        Ready = true;
    }

    /// <summary>A connection for the personal layer's own reads and writes.</summary>
    public static MySqlConnection OpenConnection() => Open();

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

    /// <summary>Every entry that has not expired yet, for the cache at startup. Times are UTC.</summary>
    public static List<BountyEntry> LoadActive()
    {
        var list = new List<BountyEntry>();

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"
            SELECT `monarch_Id`, `quest_name`, `flag`, `percent`, `completed_by`, `completed_by_Id`, `completed_at`, `expires_at`
            FROM `{TableName}` WHERE `expires_at` > @now;";
        cmd.Parameters.AddWithValue("@now", DateTime.UtcNow);

        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new BountyEntry
            {
                MonarchId = reader.GetUInt32(0),
                QuestName = reader.GetString(1),
                Flag = reader.GetString(2),
                Percent = reader.GetDouble(3),
                CompletedBy = reader.GetString(4),
                CompletedById = reader.GetUInt32(5),
                CompletedAt = DateTime.SpecifyKind(reader.GetDateTime(6), DateTimeKind.Utc),
                ExpiresAt = DateTime.SpecifyKind(reader.GetDateTime(7), DateTimeKind.Utc),
            });
        }

        return list;
    }

    /// <summary>
    /// Records an entry. One row per (allegiance, quest): a completion after the old
    /// row expired overwrites it, which is the same as pruning it first.
    /// </summary>
    public static void Upsert(BountyEntry e)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $@"
            INSERT INTO `{TableName}`
                (`monarch_Id`, `quest_name`, `flag`, `percent`, `completed_by`, `completed_by_Id`, `completed_at`, `expires_at`)
            VALUES (@m, @q, @f, @p, @by, @byid, @at, @exp)
            ON DUPLICATE KEY UPDATE
                `flag` = @f, `percent` = @p, `completed_by` = @by, `completed_by_Id` = @byid, `completed_at` = @at, `expires_at` = @exp;";
        cmd.Parameters.AddWithValue("@m", e.MonarchId);
        cmd.Parameters.AddWithValue("@q", e.QuestName);
        cmd.Parameters.AddWithValue("@f", e.Flag);
        cmd.Parameters.AddWithValue("@p", e.Percent);
        cmd.Parameters.AddWithValue("@by", e.CompletedBy);
        cmd.Parameters.AddWithValue("@byid", e.CompletedById);
        cmd.Parameters.AddWithValue("@at", e.CompletedAt);
        cmd.Parameters.AddWithValue("@exp", e.ExpiresAt);
        cmd.ExecuteNonQuery();
    }

    /// <summary>Deletes every row past its expiry. Returns how many went.</summary>
    public static int PruneExpired()
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"DELETE FROM `{TableName}` WHERE `expires_at` <= @now;";
        cmd.Parameters.AddWithValue("@now", DateTime.UtcNow);
        return cmd.ExecuteNonQuery();
    }
}