95 lines
4.8 KiB
SQL
95 lines
4.8 KiB
SQL
|
-- Migration: 0006_bot_guild_meta
|
||
|
|
-- Date: 2026-05-16
|
||
|
|
-- Purpose: Schema for the bot guild ecosystem (see
|
||
|
|
-- src/modules/PlayerbotV2/docs/GUILD_PLAN.md).
|
||
|
|
-- - bot_guild_meta: maps TC's `guild.guildid` to BotGuildMgr
|
||
|
|
-- metadata (faction, theme, founder, rival, member cap).
|
||
|
|
-- Distinguishes bot-founded guilds from operator/player
|
||
|
|
-- guilds so the manager never modifies guilds it didn't
|
||
|
|
-- create.
|
||
|
|
-- - bot_guild_name_reserved: in-flight name reservations
|
||
|
|
-- while a charter FSM is collecting signatures. Prevents
|
||
|
|
-- two parallel founders from picking the same name.
|
||
|
|
-- Reverts: yes (DROP TABLE both).
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS bot_guild_meta (
|
||
|
|
guild_id BIGINT UNSIGNED NOT NULL,
|
||
|
|
faction TINYINT UNSIGNED NOT NULL,
|
||
|
|
theme VARCHAR(32) NOT NULL DEFAULT 'adventurers',
|
||
|
|
founder_low BIGINT UNSIGNED NOT NULL,
|
||
|
|
rival_low BIGINT UNSIGNED DEFAULT NULL,
|
||
|
|
member_cap SMALLINT UNSIGNED NOT NULL DEFAULT 75,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
last_event_at TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
PRIMARY KEY (guild_id),
|
||
|
|
KEY idx_bot_guild_meta_faction (faction),
|
||
|
|
KEY idx_bot_guild_meta_founder (founder_low)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
|
|
||
|
|
-- Column docs:
|
||
|
|
-- guild_id FK to guild.guildid (CharacterDatabase). Not a real
|
||
|
|
-- FK constraint because TC's guild table doesn't carry
|
||
|
|
-- one for player rosters and we mirror the convention.
|
||
|
|
-- faction 0 = Alliance, 1 = Horde. Matches
|
||
|
|
-- BotGuildMgr::Faction enum.
|
||
|
|
-- theme 'adventurers' for the default Phase A.2 guild kind;
|
||
|
|
-- 'crafters' / 'raiders' for Phase E themed guilds.
|
||
|
|
-- founder_low Character guid_low of the bot that founded the
|
||
|
|
-- guild. NOT necessarily the current GM (GM rotates
|
||
|
|
-- via Phase B hygiene). Used by recently-disbanded
|
||
|
|
-- cooldowns and ownership lineage.
|
||
|
|
-- rival_low Optional rival guild_id (intra-faction). Set at
|
||
|
|
-- creation (Phase A.2 picks the longest-active
|
||
|
|
-- existing bot guild of same faction; NULL if first).
|
||
|
|
-- Phase E rivalries read this.
|
||
|
|
-- member_cap Per-guild membership ceiling. Defaults to 75 from
|
||
|
|
-- BotGuildMgr::kDefaultMaxMembersPerGuild but
|
||
|
|
-- configurable per guild for special cases.
|
||
|
|
-- created_at Insertion time = guild submission time.
|
||
|
|
-- last_event_at Last scheduled-event start (Phase D). NULL until
|
||
|
|
-- first event runs.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS bot_guild_name_reserved (
|
||
|
|
name VARCHAR(64) NOT NULL,
|
||
|
|
faction TINYINT UNSIGNED NOT NULL,
|
||
|
|
founder_low BIGINT UNSIGNED NOT NULL,
|
||
|
|
reserved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (name),
|
||
|
|
KEY idx_bot_guild_name_reserved_founder (founder_low)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
|
|
||
|
|
-- Column docs:
|
||
|
|
-- name The candidate guild name reserved by the charter
|
||
|
|
-- FSM at phase 2 (buy_charter). Released on success
|
||
|
|
-- (row deleted when bot_guild_meta row is INSERTed)
|
||
|
|
-- OR on FSM abort (founder lost the charter, exceeded
|
||
|
|
-- the 30-min total time-to-found budget, etc.).
|
||
|
|
-- faction Founder's faction (sanity check + queryability).
|
||
|
|
-- founder_low Owner of the reservation. Hygiene cron sweeps
|
||
|
|
-- reservations older than 60 min as orphans.
|
||
|
|
-- reserved_at For 60-min sweep cutoff.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS bot_guild_member_meta (
|
||
|
|
guild_id BIGINT UNSIGNED NOT NULL,
|
||
|
|
char_guid_low BIGINT UNSIGNED NOT NULL,
|
||
|
|
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
last_promoted_at TIMESTAMP NULL DEFAULT NULL,
|
||
|
|
PRIMARY KEY (guild_id, char_guid_low),
|
||
|
|
KEY idx_bot_gmm_char (char_guid_low),
|
||
|
|
KEY idx_bot_gmm_joined (joined_at)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
|
|
||
|
|
-- Column docs:
|
||
|
|
-- guild_id FK to guild.guildid (CharacterDatabase).
|
||
|
|
-- char_guid_low Bot character guid_low (FK to characters.guid).
|
||
|
|
-- joined_at When the bot joined this guild — populated by the
|
||
|
|
-- manager whenever Guild::AddMember succeeds for a
|
||
|
|
-- bot (charter-signer turn-in, organic recruitment,
|
||
|
|
-- etc). Drives the rank ladder hygiene cron
|
||
|
|
-- (Initiate <7d, Member <30d, Veteran >30d).
|
||
|
|
-- last_promoted_at Last rank change; null until first promotion.
|
||
|
|
|
||
|
|
-- Record this migration as applied.
|
||
|
|
INSERT INTO playerbot_v2_schema_version (version, sha256)
|
||
|
|
VALUES (6, 'pending-fill-at-release-time-with-actual-sha256-of-this-file');
|