SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

USE hyapp_user;

CREATE TABLE IF NOT EXISTS manager_profiles (
  app_code VARCHAR(32) NOT NULL DEFAULT 'lalu' COMMENT '应用编码，用于多租户隔离',
  user_id BIGINT NOT NULL PRIMARY KEY COMMENT '经理用户 ID',
  status VARCHAR(32) NOT NULL COMMENT '业务状态',
  contact_info VARCHAR(128) NOT NULL DEFAULT '' COMMENT '联系信息',
  can_grant_avatar_frame TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心赠送头像框',
  can_grant_vehicle TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心赠送座驾',
  can_grant_badge TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心赠送徽章',
  can_grant_vip TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心赠送 VIP',
  can_update_user_level TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心升级用户等级',
  can_add_bd_leader TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心添加 BD Leader',
  can_add_admin TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心添加 Admin',
  can_add_superadmin TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心添加 Superadmin',
  can_block_user TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心封禁用户',
  can_transfer_user_country TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否允许在经理中心转移用户国家',
  created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建后台管理员 ID',
  created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
  updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
  KEY idx_manager_profiles_status (app_code, status, updated_at_ms),
  KEY idx_manager_profiles_created_by (app_code, created_by_admin_id, created_at_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='经理资料表';

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'manager_profiles' AND COLUMN_NAME = 'contact_info') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN contact_info VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''联系信息'' AFTER 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 = 'manager_profiles' AND COLUMN_NAME = 'can_grant_avatar_frame') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_grant_avatar_frame TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心赠送头像框'' AFTER contact_info',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_grant_vehicle') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_grant_vehicle TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心赠送座驾'' AFTER can_grant_avatar_frame',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_grant_badge') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_grant_badge TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心赠送徽章'' AFTER can_grant_vehicle',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_update_user_level') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_update_user_level TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心升级用户等级'' AFTER can_grant_badge',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_grant_vip') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_grant_vip TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心赠送 VIP'' AFTER can_grant_badge',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_add_bd_leader') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_add_bd_leader TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心添加 BD Leader'' AFTER can_update_user_level',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_add_admin') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_add_admin TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心添加 Admin'' AFTER can_add_bd_leader',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_add_superadmin') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_add_superadmin TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心添加 Superadmin'' AFTER can_add_admin',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_block_user') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_block_user TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心封禁用户'' AFTER can_add_superadmin',
  '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 = 'manager_profiles' AND COLUMN_NAME = 'can_transfer_user_country') = 0,
  'ALTER TABLE manager_profiles ADD COLUMN can_transfer_user_country TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''是否允许在经理中心转移用户国家'' AFTER can_block_user',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT INTO manager_profiles (
  app_code, user_id, status, contact_info, can_grant_avatar_frame, can_grant_vehicle, can_grant_badge,
  can_grant_vip, can_update_user_level, can_add_bd_leader, can_add_admin, can_add_superadmin, can_block_user, can_transfer_user_country,
  created_by_admin_id, created_at_ms, updated_at_ms
)
SELECT
  bl.app_code,
  bl.created_by_user_id,
  'active',
  '',
  1,
  1,
  1,
  1,
  1,
  1,
  1,
  1,
  1,
  1,
  0,
  MIN(bl.created_at_ms),
  MAX(bl.updated_at_ms)
FROM bd_leader_profiles bl
JOIN users u
  ON u.app_code = bl.app_code
 AND u.user_id = bl.created_by_user_id
WHERE bl.created_by_user_id > 0
GROUP BY bl.app_code, bl.created_by_user_id
ON DUPLICATE KEY UPDATE
  updated_at_ms = GREATEST(manager_profiles.updated_at_ms, VALUES(updated_at_ms));