namespace Aeshnidae.Expedition;

/// <summary>
/// The quest's two tables, in the shard database beside the characters they belong to (the
/// Trials / AllegianceBounty pattern; credentials from ACE's own Config.js at runtime).
///
///   aeshnidae_expedition_cycle         one row per cycle: its act and its state as JSON
///                                      (meters, milestones told, zones reclaimed - later acts
///                                      add their own fields without a schema change). The open
///                                      cycle is the newest row with no finished_at.
///   aeshnidae_expedition_contribution  per character per cycle: contribution and kills.
///
/// Nothing on a hot path touches this class: the state lives in memory (Expedition) and is
/// written every Settings.FlushSeconds, off the world thread, and at shutdown.
/// </summary>
public static class ExpeditionDb
{
    public const string CycleTable = "aeshnidae_expedition_cycle";
    public const string ContributionTable = "aeshnidae_expedition_contribution";

    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 `{CycleTable}` (
                `cycle`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `act`          INT          NOT NULL,
                `state`        MEDIUMTEXT   NOT NULL,
                `started_at`   DATETIME     NOT NULL,
                `updated_at`   DATETIME     NOT NULL,
                `finished_at`  DATETIME     NULL,
                PRIMARY KEY (`cycle`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

            CREATE TABLE IF NOT EXISTS `{ContributionTable}` (
                `cycle`          INT UNSIGNED NOT NULL,
                `character_Id`   INT UNSIGNED NOT NULL,
                `character_Name` VARCHAR(64)  NOT NULL,
                `contribution`   DOUBLE       NOT NULL,
                `kills`          INT          NOT NULL,
                `updated_at`     DATETIME     NOT NULL,
                PRIMARY KEY (`cycle`, `character_Id`),
                KEY `board` (`cycle`, `contribution`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        cmd.ExecuteNonQuery();

        Ready = true;
    }

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

    /// <summary>The open cycle's (number, act, state JSON, started), or null when there is none.</summary>
    public static (int Cycle, int Act, string State, DateTime Started)? LoadOpenCycle()
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `cycle`, `act`, `state`, `started_at` FROM `{CycleTable}` WHERE `finished_at` IS NULL ORDER BY `cycle` DESC LIMIT 1";
        using var r = cmd.ExecuteReader();

        if (!r.Read())
            return null;

        return ((int)r.GetUInt32(0), r.GetInt32(1), r.GetString(2), r.GetDateTime(3));
    }

    /// <summary>Starts a new cycle row and returns its number.</summary>
    public static int InsertCycle(int act, string state, DateTime started)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"INSERT INTO `{CycleTable}` (`act`, `state`, `started_at`, `updated_at`) VALUES (@act, @state, @started, @started); SELECT LAST_INSERT_ID();";
        cmd.Parameters.AddWithValue("@act", act);
        cmd.Parameters.AddWithValue("@state", state);
        cmd.Parameters.AddWithValue("@started", started);
        return Convert.ToInt32(cmd.ExecuteScalar());
    }

    public static void SaveCycle(int cycle, int act, string state, DateTime? finished)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"UPDATE `{CycleTable}` SET `act` = @act, `state` = @state, `updated_at` = UTC_TIMESTAMP(), `finished_at` = @finished WHERE `cycle` = @cycle";
        cmd.Parameters.AddWithValue("@act", act);
        cmd.Parameters.AddWithValue("@state", state);
        cmd.Parameters.AddWithValue("@finished", (object?)finished ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@cycle", cycle);
        cmd.ExecuteNonQuery();
    }

    public static List<Contribution> LoadContributions(int cycle)
    {
        var list = new List<Contribution>();

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `character_Id`, `character_Name`, `contribution`, `kills` FROM `{ContributionTable}` WHERE `cycle` = @cycle";
        cmd.Parameters.AddWithValue("@cycle", cycle);
        using var r = cmd.ExecuteReader();

        while (r.Read())
            list.Add(new Contribution { CharacterId = r.GetUInt32(0), Name = r.GetString(1), Points = r.GetDouble(2), Kills = r.GetInt32(3) });

        return list;
    }

    /// <summary>Writes these characters' totals (absolute values, not increments).</summary>
    public static void SaveContributions(int cycle, IEnumerable<Contribution> rows)
    {
        using var conn = Open();

        foreach (var c in rows)
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $@"
                INSERT INTO `{ContributionTable}` (`cycle`, `character_Id`, `character_Name`, `contribution`, `kills`, `updated_at`)
                VALUES (@cycle, @id, @name, @points, @kills, UTC_TIMESTAMP())
                ON DUPLICATE KEY UPDATE `character_Name` = VALUES(`character_Name`), `contribution` = VALUES(`contribution`),
                                        `kills` = VALUES(`kills`), `updated_at` = VALUES(`updated_at`)";
            cmd.Parameters.AddWithValue("@cycle", cycle);
            cmd.Parameters.AddWithValue("@id", c.CharacterId);
            cmd.Parameters.AddWithValue("@name", c.Name);
            cmd.Parameters.AddWithValue("@points", c.Points);
            cmd.Parameters.AddWithValue("@kills", c.Kills);
            cmd.ExecuteNonQuery();
        }
    }
}

/// <summary>One character's part in a cycle.</summary>
public sealed class Contribution
{
    public uint CharacterId { get; set; }
    public string Name { get; set; } = "";
    public double Points { get; set; }
    public int Kills { get; set; }
}