namespace Aeshnidae.Towns;

/// <summary>A town an allegiance owns.</summary>
public sealed class TownRow
{
    public uint Id { get; set; }
    public uint MonarchId { get; set; }
    public string MonarchName { get; set; } = "";
    public string TownKey { get; set; } = "";
    public DateTime CreatedAt { get; set; }
}

/// <summary>
/// The towns' tables, in the shard database beside the characters and the bank they draw on
/// (credentials from ACE's Config.js at runtime, as every mod here does):
///
///   aeshnidae_town           one row per town bought: its monarch, which town, when; abandoned_at
///                            set instead of deleting, so the history stays
///   aeshnidae_town_treasury  one row per allegiance (by monarch): its pyreals
///   aeshnidae_town_ledger    every donation, charter and grant: who, how much, when
///
/// Money moves in one transaction each, and on the database's own terms: a donation debits the
/// donor's account row in Aeshnidae.Bank's `aeshnidae_bank` only WHERE the balance covers it
/// (the same row Bank itself adjusts - SkillMastery debits it the same way; cross-mod types are
/// unsafe, the row is not) and credits the treasury in the same transaction; a charter checks and
/// debits the treasury and writes the town in one. Nothing can leave one place without arriving in
/// the other, and a charter cannot be bought twice.
/// </summary>
public static class TownDb
{
    public const string TownTable = "aeshnidae_town";
    public const string TreasuryTable = "aeshnidae_town_treasury";
    public const string LedgerTable = "aeshnidae_town_ledger";
    private const string BankTable = "aeshnidae_bank";

    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 `{TownTable}` (
                `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `monarch_Id`    INT UNSIGNED NOT NULL,
                `monarch_Name`  VARCHAR(64)  NOT NULL,
                `town_Key`      VARCHAR(32)  NOT NULL,
                `created_at`    DATETIME     NOT NULL,
                `abandoned_at`  DATETIME     NULL,
                PRIMARY KEY (`id`),
                KEY `monarch` (`monarch_Id`, `abandoned_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

            CREATE TABLE IF NOT EXISTS `{TreasuryTable}` (
                `monarch_Id`  INT UNSIGNED NOT NULL,
                `pyreals`     BIGINT       NOT NULL DEFAULT 0,
                `updated_at`  DATETIME     NOT NULL,
                PRIMARY KEY (`monarch_Id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

            CREATE TABLE IF NOT EXISTS `{LedgerTable}` (
                `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `monarch_Id`      INT UNSIGNED NOT NULL,
                `character_Id`    INT UNSIGNED NOT NULL,
                `character_Name`  VARCHAR(64)  NOT NULL,
                `kind`            VARCHAR(16)  NOT NULL,
                `amount`          BIGINT       NOT NULL,
                `note`            VARCHAR(128) NOT NULL DEFAULT '',
                `at`              DATETIME     NOT NULL,
                PRIMARY KEY (`id`),
                KEY `monarch` (`monarch_Id`, `at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        cmd.ExecuteNonQuery();

        Ready = true;
    }

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

    private static void Ledger(MySqlConnection conn, MySqlTransaction tx, uint monarch, uint character, string name, string kind, long amount, string note)
    {
        using var cmd = conn.CreateCommand();
        cmd.Transaction = tx;
        cmd.CommandText = $"INSERT INTO `{LedgerTable}` (`monarch_Id`, `character_Id`, `character_Name`, `kind`, `amount`, `note`, `at`) VALUES (@m, @c, @n, @k, @a, @note, UTC_TIMESTAMP())";
        cmd.Parameters.AddWithValue("@m", monarch);
        cmd.Parameters.AddWithValue("@c", character);
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@k", kind);
        cmd.Parameters.AddWithValue("@a", amount);
        cmd.Parameters.AddWithValue("@note", note);
        cmd.ExecuteNonQuery();
    }

    // ------------------------------------------------------------ towns

    public static List<TownRow> LoadTowns()
    {
        var list = new List<TownRow>();

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `id`, `monarch_Id`, `monarch_Name`, `town_Key`, `created_at` FROM `{TownTable}` WHERE `abandoned_at` IS NULL";
        using var r = cmd.ExecuteReader();

        while (r.Read())
            list.Add(new TownRow { Id = r.GetUInt32(0), MonarchId = r.GetUInt32(1), MonarchName = r.GetString(2), TownKey = r.GetString(3), CreatedAt = r.GetDateTime(4) });

        return list;
    }

    public static long Treasury(uint monarch)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `pyreals` FROM `{TreasuryTable}` WHERE `monarch_Id` = @m";
        cmd.Parameters.AddWithValue("@m", monarch);
        return cmd.ExecuteScalar() is { } v and not DBNull ? Convert.ToInt64(v) : 0;
    }

    public static long BankPyreals(uint accountId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `amount` FROM `{BankTable}` WHERE `account_Id` = @a AND `currency` = 'Pyreal'";
        cmd.Parameters.AddWithValue("@a", accountId);
        return cmd.ExecuteScalar() is { } v and not DBNull ? Convert.ToInt64(v) : 0;
    }

    /// <summary>
    /// Bank to treasury, one transaction: the donor's account balance is debited only where it
    /// covers the amount, and the treasury credited with exactly that. Returns the new treasury,
    /// or null when the bank did not hold enough (nothing moved).
    /// </summary>
    public static long? Donate(uint accountId, uint monarch, uint character, string name, long amount)
    {
        using var conn = Open();
        using var tx = conn.BeginTransaction();

        using (var debit = conn.CreateCommand())
        {
            debit.Transaction = tx;
            debit.CommandText = $"UPDATE `{BankTable}` SET `amount` = `amount` - @x, `updated` = UTC_TIMESTAMP() WHERE `account_Id` = @a AND `currency` = 'Pyreal' AND `amount` >= @x";
            debit.Parameters.AddWithValue("@x", amount);
            debit.Parameters.AddWithValue("@a", accountId);

            if (debit.ExecuteNonQuery() != 1)
            {
                tx.Rollback();
                return null;
            }
        }

        using (var credit = conn.CreateCommand())
        {
            credit.Transaction = tx;
            credit.CommandText = $@"INSERT INTO `{TreasuryTable}` (`monarch_Id`, `pyreals`, `updated_at`) VALUES (@m, @x, UTC_TIMESTAMP())
                                    ON DUPLICATE KEY UPDATE `pyreals` = `pyreals` + @x, `updated_at` = UTC_TIMESTAMP()";
            credit.Parameters.AddWithValue("@m", monarch);
            credit.Parameters.AddWithValue("@x", amount);
            credit.ExecuteNonQuery();
        }

        Ledger(conn, tx, monarch, character, name, "donate", amount, "");
        tx.Commit();

        return Treasury(monarch);
    }

    /// <summary>
    /// Buys a town: the treasury debited by the price and the town written, one transaction; `price`
    /// 0 is a grant (staff). Returns the new town's id, or the reason it could not be bought.
    /// </summary>
    public static (uint Id, string? Error) Charter(uint monarch, string monarchName, string townKey, long price, uint byCharacter, string byName)
    {
        using var conn = Open();
        using var tx = conn.BeginTransaction();

        using (var owned = conn.CreateCommand())
        {
            owned.Transaction = tx;
            owned.CommandText = $"SELECT COUNT(*) FROM `{TownTable}` WHERE `monarch_Id` = @m AND `abandoned_at` IS NULL FOR UPDATE";
            owned.Parameters.AddWithValue("@m", monarch);

            if (Convert.ToInt64(owned.ExecuteScalar()) > 0)
            {
                tx.Rollback();
                return (0, "Your allegiance already has a town.");
            }
        }

        if (price > 0)
        {
            using var debit = conn.CreateCommand();
            debit.Transaction = tx;
            debit.CommandText = $"UPDATE `{TreasuryTable}` SET `pyreals` = `pyreals` - @x, `updated_at` = UTC_TIMESTAMP() WHERE `monarch_Id` = @m AND `pyreals` >= @x";
            debit.Parameters.AddWithValue("@x", price);
            debit.Parameters.AddWithValue("@m", monarch);

            if (debit.ExecuteNonQuery() != 1)
            {
                tx.Rollback();
                return (0, $"The treasury does not hold the {price:N0} pyreals a charter costs.");
            }
        }

        uint id;
        using (var insert = conn.CreateCommand())
        {
            insert.Transaction = tx;
            insert.CommandText = $"INSERT INTO `{TownTable}` (`monarch_Id`, `monarch_Name`, `town_Key`, `created_at`) VALUES (@m, @n, @k, UTC_TIMESTAMP()); SELECT LAST_INSERT_ID();";
            insert.Parameters.AddWithValue("@m", monarch);
            insert.Parameters.AddWithValue("@n", monarchName);
            insert.Parameters.AddWithValue("@k", townKey);
            id = Convert.ToUInt32(insert.ExecuteScalar());
        }

        Ledger(conn, tx, monarch, byCharacter, byName, price > 0 ? "charter" : "grant", -price, townKey);
        tx.Commit();

        return (id, null);
    }

    public static bool Abandon(uint townId)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"UPDATE `{TownTable}` SET `abandoned_at` = UTC_TIMESTAMP() WHERE `id` = @i AND `abandoned_at` IS NULL";
        cmd.Parameters.AddWithValue("@i", townId);
        return cmd.ExecuteNonQuery() == 1;
    }

    /// <summary>Distinct live accounts among the monarch and everyone sworn under them.</summary>
    public static int AccountsInAllegiance(uint monarch)
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = @"SELECT COUNT(DISTINCT c.`account_Id`) FROM `character` c
                            WHERE c.`is_Deleted` = 0
                              AND (c.`id` = @m OR c.`id` IN (SELECT `object_Id` FROM `biota_properties_i_i_d` WHERE `type` = 26 AND `value` = @m))";
        cmd.Parameters.AddWithValue("@m", monarch);
        return Convert.ToInt32(cmd.ExecuteScalar());
    }

    public static List<(string Name, string Kind, long Amount, DateTime At)> RecentLedger(uint monarch, int count)
    {
        var list = new List<(string, string, long, DateTime)>();

        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT `character_Name`, `kind`, `amount`, `at` FROM `{LedgerTable}` WHERE `monarch_Id` = @m ORDER BY `id` DESC LIMIT @n";
        cmd.Parameters.AddWithValue("@m", monarch);
        cmd.Parameters.AddWithValue("@n", count);
        using var r = cmd.ExecuteReader();

        while (r.Read())
            list.Add((r.GetString(0), r.GetString(1), r.GetInt64(2), r.GetDateTime(3)));

        return list;
    }
}