-- ----------------------------
-- bnrybot_guilds
-- id + name + prefix + welcomes + autorole
-- ----------------------------
CREATE TABLE IF NOT EXISTS bnrybot_guilds (
  id VARCHAR(32) NOT NULL,
  name VARCHAR(128) NOT NULL,
  prefix VARCHAR(16) NOT NULL DEFAULT '!',
  welcome_channel_id VARCHAR(32) NULL,
  welcome_message TEXT NULL,
  autorole_id VARCHAR(32) NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- bnrybot_users
-- clé composée (user_id, guild_id)
-- ----------------------------
CREATE TABLE IF NOT EXISTS bnrybot_users (
  user_id VARCHAR(32) NOT NULL,
  guild_id VARCHAR(32) NOT NULL,
  xp INT NOT NULL DEFAULT 0,
  level INT NOT NULL DEFAULT 0,
  last_xp_at BIGINT NULL,
  PRIMARY KEY (user_id, guild_id),
  KEY idx_bnrybot_users_guild (guild_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- bnrybot_reaction_roles
-- clé composée (guild_id, message_id, emoji)
-- ----------------------------
CREATE TABLE IF NOT EXISTS bnrybot_reaction_roles (
  guild_id VARCHAR(32) NOT NULL,
  message_id VARCHAR(32) NOT NULL,
  emoji VARCHAR(191) NOT NULL,
  role_id VARCHAR(32) NOT NULL,
  PRIMARY KEY (guild_id, message_id, emoji),
  KEY idx_bnrybot_rr_msg (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- bnrybot_custom_commands
-- clé composée (guild_id, name)
-- ----------------------------
CREATE TABLE IF NOT EXISTS bnrybot_custom_commands (
  guild_id VARCHAR(32) NOT NULL,
  name VARCHAR(64) NOT NULL,
  response TEXT NOT NULL,
  PRIMARY KEY (guild_id, name),
  KEY idx_bnrybot_cc_guild (guild_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;