75 lines
3.3 KiB
SQL
75 lines
3.3 KiB
SQL
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `gm_vip_gift_record` (
|
|
`id` bigint NOT NULL COMMENT '记录ID',
|
|
`user_id` bigint NOT NULL COMMENT 'APP用户ID',
|
|
`sys_origin` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '系统来源',
|
|
`from_level` int NOT NULL DEFAULT '0' COMMENT '赠送前VIP等级',
|
|
`vip_level` int NOT NULL COMMENT '赠送VIP等级',
|
|
`level_code` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '等级编码',
|
|
`display_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '显示名称',
|
|
`duration_days` int NOT NULL DEFAULT '30' COMMENT '赠送天数',
|
|
`price_gold` bigint NOT NULL DEFAULT '0' COMMENT '对应购买价格，金币',
|
|
`start_at` datetime(3) DEFAULT NULL COMMENT '权益开始时间',
|
|
`expire_at` datetime(3) DEFAULT NULL COMMENT '权益到期时间',
|
|
`order_id` bigint NOT NULL COMMENT '关联VIP订单ID',
|
|
`event_id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '赠送幂等事件ID',
|
|
`status` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'SUCCESS' COMMENT '状态',
|
|
`applicant_id` bigint NOT NULL COMMENT '后台操作人ID',
|
|
`applicant_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '后台操作人名称',
|
|
`remarks` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
|
`executed_time` datetime(3) DEFAULT NULL COMMENT '执行时间',
|
|
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
`create_user` bigint DEFAULT NULL COMMENT '创建用户',
|
|
`update_user` bigint DEFAULT NULL COMMENT '更新用户',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_gm_vip_gift_event` (`event_id`),
|
|
KEY `idx_gm_vip_gift_user` (`user_id`),
|
|
KEY `idx_gm_vip_gift_applicant` (`applicant_id`),
|
|
KEY `idx_gm_vip_gift_time` (`create_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='GM VIP赠送记录';
|
|
|
|
SET @gm_operation_menu_id := (
|
|
SELECT `id` FROM `sys_menu` WHERE `alias` = 'GmOperation' LIMIT 1
|
|
);
|
|
|
|
INSERT INTO `sys_menu` (
|
|
`id`, `parent_id`, `menu_name`, `path`, `menu_type`, `icon`, `create_user`, `update_user`,
|
|
`sort`, `status`, `router`, `alias`
|
|
)
|
|
SELECT
|
|
1700000403, @gm_operation_menu_id, 'VIP赠送', 'gm/vip-gift', 2, 'lucide:badge',
|
|
'0', '0', 3, 0, 'vip-gift', 'GmVipGift'
|
|
WHERE @gm_operation_menu_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM `sys_menu` WHERE `alias` = 'GmVipGift'
|
|
);
|
|
|
|
UPDATE `sys_menu`
|
|
SET
|
|
`parent_id` = @gm_operation_menu_id,
|
|
`menu_name` = 'VIP赠送',
|
|
`path` = 'gm/vip-gift',
|
|
`menu_type` = 2,
|
|
`icon` = 'lucide:badge',
|
|
`create_user` = '0',
|
|
`update_user` = '0',
|
|
`sort` = 3,
|
|
`status` = 0,
|
|
`router` = 'vip-gift'
|
|
WHERE `alias` = 'GmVipGift';
|
|
|
|
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
|
SELECT DISTINCT valid_source_role.`id`, target_menu.`id`
|
|
FROM `sys_role_menu` source
|
|
JOIN `sys_role` valid_source_role ON valid_source_role.`id` = source.`role_id`
|
|
JOIN `sys_menu` target_menu ON target_menu.`alias` = 'GmVipGift'
|
|
JOIN `sys_menu` source_menu ON source_menu.`alias` = 'GmGoldOperation'
|
|
WHERE source.`menu_id` = source_menu.`id`
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM `sys_role_menu` target
|
|
WHERE target.`role_id` = source.`role_id`
|
|
AND target.`menu_id` = target_menu.`id`
|
|
);
|