SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

USE hyapp_user;

-- BD/Agency/Host 的区域统一由 users 当前区域或用户国家映射区域派生。
-- 这些身份表里的 region_id 是旧快照，先删除依赖索引，再删除列，避免后续继续写入或误读。
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'host_profiles' AND INDEX_NAME = 'idx_host_profiles_region_status') > 0,
  'ALTER TABLE host_profiles DROP INDEX idx_host_profiles_region_status',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bd_profiles' AND INDEX_NAME = 'idx_bd_profiles_region_role') > 0,
  'ALTER TABLE bd_profiles DROP INDEX idx_bd_profiles_region_role',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bd_leader_profiles' AND INDEX_NAME = 'idx_bd_leader_profiles_region_status') > 0,
  'ALTER TABLE bd_leader_profiles DROP INDEX idx_bd_leader_profiles_region_status',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'agencies' AND INDEX_NAME = 'idx_agencies_region_status') > 0,
  'ALTER TABLE agencies DROP INDEX idx_agencies_region_status',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'host_profiles' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE host_profiles DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bd_profiles' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE bd_profiles DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bd_leader_profiles' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE bd_leader_profiles DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'agencies' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE agencies DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'agency_memberships' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE agency_memberships DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'agency_applications' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE agency_applications DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'role_invitations' AND COLUMN_NAME = 'region_id') > 0,
  'ALTER TABLE role_invitations DROP COLUMN region_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;