XFEServerManager

CREATE TABLE IF NOT EXISTS economy_currencies (
    currency_id TEXT PRIMARY KEY,
    code TEXT NOT NULL COLLATE NOCASE UNIQUE,
    name TEXT NOT NULL,
    symbol TEXT NOT NULL,
    icon TEXT NOT NULL,
    fraction_digits INTEGER NOT NULL CHECK (fraction_digits BETWEEN 0 AND 6),
    initial_minor INTEGER NOT NULL,
    minimum_minor INTEGER NOT NULL,
    maximum_minor INTEGER NOT NULL,
    player_transfer_allowed INTEGER NOT NULL CHECK (player_transfer_allowed IN (0, 1)),
    enabled INTEGER NOT NULL CHECK (enabled IN (0, 1)),
    primary_currency INTEGER NOT NULL CHECK (primary_currency IN (0, 1)),
    revision INTEGER NOT NULL,
    created_by TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    CHECK (minimum_minor <= initial_minor AND initial_minor <= maximum_minor)
);

CREATE UNIQUE INDEX IF NOT EXISTS economy_one_primary_currency_idx
    ON economy_currencies(primary_currency) WHERE primary_currency = 1;

CREATE TABLE IF NOT EXISTS economy_balances (
    player_id TEXT NOT NULL,
    currency_id TEXT NOT NULL REFERENCES economy_currencies(currency_id) ON DELETE RESTRICT,
    player_name TEXT NOT NULL,
    balance_minor INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    PRIMARY KEY (player_id, currency_id)
);

CREATE INDEX IF NOT EXISTS economy_balances_player_name_idx
    ON economy_balances(player_name COLLATE NOCASE, player_id);
CREATE INDEX IF NOT EXISTS economy_balances_currency_amount_idx
    ON economy_balances(currency_id, balance_minor DESC);

CREATE TABLE IF NOT EXISTS economy_transactions (
    transaction_id TEXT PRIMARY KEY,
    currency_id TEXT NOT NULL REFERENCES economy_currencies(currency_id) ON DELETE RESTRICT,
    kind TEXT NOT NULL CHECK (kind IN ('initial', 'deposit', 'withdraw', 'set', 'transfer')),
    source_player_id TEXT,
    source_player_name TEXT NOT NULL,
    target_player_id TEXT,
    target_player_name TEXT NOT NULL,
    amount_minor INTEGER NOT NULL CHECK (amount_minor >= 0),
    source_before_minor INTEGER,
    source_after_minor INTEGER,
    target_before_minor INTEGER,
    target_after_minor INTEGER,
    reason TEXT NOT NULL,
    actor TEXT NOT NULL,
    origin TEXT NOT NULL,
    correlation_id TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS economy_transactions_created_idx
    ON economy_transactions(created_at DESC, transaction_id);
CREATE INDEX IF NOT EXISTS economy_transactions_source_idx
    ON economy_transactions(source_player_id, created_at DESC);
CREATE INDEX IF NOT EXISTS economy_transactions_target_idx
    ON economy_transactions(target_player_id, created_at DESC);
CREATE INDEX IF NOT EXISTS economy_transactions_currency_idx
    ON economy_transactions(currency_id, created_at DESC);