moonwell-core/localize-playerbots-ru.sh

#!/usr/bin/env bash

set -euo pipefail

SCRIPT_DIR="$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" && pwd)"
cd "$SCRIPT_DIR"

MODE="${1:---dry-run}"
CHARACTER_NAMES_SQL="modules/mod-playerbots/data/sql/characters/updates/2026_07_30_00_playerbots_russian_names.sql"
BOT_TEXTS_SQL="modules/mod-playerbots/data/sql/playerbots/updates/2026_07_30_00_ai_playerbot_russian_texts.sql"

case "$MODE" in
    --dry-run | --apply) ;;
    *)
        echo "Usage: $0 [--dry-run|--apply]" >&2
        exit 2
        ;;
esac

if ! docker compose ps --status running --services | grep -qx "ac-database"; then
    echo "ac-database must be running." >&2
    exit 1
fi

mysql_query() {
    local database="$1"
    local sql="$2"
    docker compose exec -T ac-database bash -lc \
        "mysql --default-character-set=utf8mb4 -N -s -uroot -p\"\$MYSQL_ROOT_PASSWORD\" \"$database\"" <<<"$sql"
}

import_sql() {
    local database="$1"
    local file_path="$2"
    docker compose exec -T ac-database bash -lc \
        "mysql --default-character-set=utf8mb4 -uroot -p\"\$MYSQL_ROOT_PASSWORD\" \"$database\"" <"$file_path"
}

read -r bot_count guild_count < <(
    mysql_query acore_characters "
        SELECT
            COUNT(DISTINCT CASE
                WHEN REGEXP_LIKE(c.name, '^[[:ascii:]]+$') THEN c.guid
            END),
            COUNT(DISTINCT CASE
                WHEN REGEXP_LIKE(g.name, '^[[:ascii:]]+$') THEN g.guildid
            END)
        FROM characters c
        JOIN acore_playerbots.playerbots_account_type pat
          ON pat.account_id = c.account AND pat.account_type = 1
        LEFT JOIN guild g ON g.leaderguid = c.guid;
    "
)

echo "RNDbot characters to rename: $bot_count"
echo "RNDbot-led guilds to rename: $guild_count"

if [[ "$MODE" == "--dry-run" ]]; then
    echo "Dry run only. Stop ac-worldserver and run '$0 --apply' to apply."
    exit 0
fi

if docker compose ps --status running --services | grep -qx "ac-worldserver"; then
    echo "Refusing to rename live characters while ac-worldserver is running." >&2
    echo "Stop it with: docker compose stop ac-worldserver" >&2
    exit 1
fi

import_sql acore_characters "$CHARACTER_NAMES_SQL"
import_sql acore_playerbots "$BOT_TEXTS_SQL"

mysql_query acore_characters "
    SET NAMES utf8mb4;

    CREATE TABLE IF NOT EXISTS moonwell_playerbot_name_backup (
        guid INT UNSIGNED NOT NULL,
        original_name VARCHAR(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
        localized_name VARCHAR(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
        changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (guid),
        UNIQUE KEY uq_moonwell_playerbot_localized_name (localized_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS moonwell_playerbot_guild_name_backup (
        guildid INT UNSIGNED NOT NULL,
        original_name VARCHAR(24) NOT NULL,
        localized_name VARCHAR(24) NOT NULL,
        changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (guildid),
        UNIQUE KEY uq_moonwell_playerbot_localized_guild_name (localized_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    DROP TEMPORARY TABLE IF EXISTS tmp_moonwell_available_names;
    CREATE TEMPORARY TABLE tmp_moonwell_available_names AS
    SELECT
        n.gender,
        n.name,
        ROW_NUMBER() OVER (PARTITION BY n.gender ORDER BY n.name_id) AS row_number_in_category
    FROM playerbots_names_ru n
    LEFT JOIN characters used
      ON used.name = CONVERT(n.name USING utf8mb4) COLLATE utf8mb4_bin
    LEFT JOIN moonwell_playerbot_name_backup reserved
      ON reserved.localized_name = CONVERT(n.name USING utf8mb4) COLLATE utf8mb4_bin
    WHERE used.guid IS NULL
      AND reserved.guid IS NULL;

    DROP TEMPORARY TABLE IF EXISTS tmp_moonwell_bot_targets;
    CREATE TEMPORARY TABLE tmp_moonwell_bot_targets AS
    SELECT
        c.guid,
        c.name AS original_name,
        CASE
            WHEN c.race IN (1, 5) THEN c.gender
            WHEN c.race = 7 THEN 2 + c.gender
            WHEN c.race = 3 THEN 4 + c.gender
            WHEN c.race = 4 THEN 6 + c.gender
            WHEN c.race = 11 THEN 8 + c.gender
            WHEN c.race = 2 THEN 10 + c.gender
            WHEN c.race = 8 THEN 12 + c.gender
            WHEN c.race = 6 THEN 14 + c.gender
            WHEN c.race = 10 THEN 16 + c.gender
        END AS name_category,
        ROW_NUMBER() OVER (
            PARTITION BY CASE
                WHEN c.race IN (1, 5) THEN c.gender
                WHEN c.race = 7 THEN 2 + c.gender
                WHEN c.race = 3 THEN 4 + c.gender
                WHEN c.race = 4 THEN 6 + c.gender
                WHEN c.race = 11 THEN 8 + c.gender
                WHEN c.race = 2 THEN 10 + c.gender
                WHEN c.race = 8 THEN 12 + c.gender
                WHEN c.race = 6 THEN 14 + c.gender
                WHEN c.race = 10 THEN 16 + c.gender
            END
            ORDER BY c.guid
        ) AS row_number_in_category
    FROM characters c
    JOIN acore_playerbots.playerbots_account_type pat
      ON pat.account_id = c.account AND pat.account_type = 1
    WHERE REGEXP_LIKE(c.name, '^[[:ascii:]]+$');

    DROP TEMPORARY TABLE IF EXISTS tmp_moonwell_character_renames;
    CREATE TEMPORARY TABLE tmp_moonwell_character_renames AS
    SELECT t.guid, t.original_name, n.name AS localized_name
    FROM tmp_moonwell_bot_targets t
    JOIN tmp_moonwell_available_names n
      ON n.gender = t.name_category
     AND n.row_number_in_category = t.row_number_in_category;

    START TRANSACTION;
    INSERT IGNORE INTO moonwell_playerbot_name_backup (guid, original_name, localized_name)
    SELECT guid, original_name, localized_name
    FROM tmp_moonwell_character_renames;

    UPDATE characters c
    JOIN moonwell_playerbot_name_backup b ON b.guid = c.guid
    SET c.name = b.localized_name
    WHERE c.name = b.original_name;

    DELETE declined
    FROM character_declinedname declined
    JOIN moonwell_playerbot_name_backup b ON b.guid = declined.guid;
    COMMIT;

    DROP TEMPORARY TABLE IF EXISTS tmp_moonwell_available_guild_names;
    CREATE TEMPORARY TABLE tmp_moonwell_available_guild_names AS
    SELECT
        n.name,
        ROW_NUMBER() OVER (ORDER BY n.name_id) AS row_number_in_pool
    FROM playerbots_guild_names_ru n
    LEFT JOIN guild used ON used.name = n.name
    LEFT JOIN moonwell_playerbot_guild_name_backup reserved
      ON reserved.localized_name = n.name
    WHERE used.guildid IS NULL
      AND reserved.guildid IS NULL;

    DROP TEMPORARY TABLE IF EXISTS tmp_moonwell_guild_targets;
    CREATE TEMPORARY TABLE tmp_moonwell_guild_targets AS
    SELECT
        g.guildid,
        g.name AS original_name,
        ROW_NUMBER() OVER (ORDER BY g.guildid) AS row_number_in_pool
    FROM guild g
    JOIN characters leader ON leader.guid = g.leaderguid
    JOIN acore_playerbots.playerbots_account_type pat
      ON pat.account_id = leader.account AND pat.account_type = 1
    WHERE REGEXP_LIKE(g.name, '^[[:ascii:]]+$');

    START TRANSACTION;
    INSERT IGNORE INTO moonwell_playerbot_guild_name_backup (guildid, original_name, localized_name)
    SELECT t.guildid, t.original_name, n.name
    FROM tmp_moonwell_guild_targets t
    JOIN tmp_moonwell_available_guild_names n
      ON n.row_number_in_pool = t.row_number_in_pool;

    UPDATE guild g
    JOIN moonwell_playerbot_guild_name_backup b ON b.guildid = g.guildid
    SET g.name = b.localized_name
    WHERE g.name = b.original_name;
    COMMIT;
"

read -r renamed_bot_count renamed_guild_count < <(
    mysql_query acore_characters "
        SELECT
            (SELECT COUNT(*) FROM moonwell_playerbot_name_backup),
            (SELECT COUNT(*) FROM moonwell_playerbot_guild_name_backup);
    "
)

echo "Localized RNDbot characters: $renamed_bot_count"
echo "Localized RNDbot-led guilds: $renamed_guild_count"
echo "Static ruRU texts and Russian name pools are installed."