157 lines
7.2 KiB
SQL
157 lines
7.2 KiB
SQL
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));
|