java-bbc/db/2024-01-22 弹窗广告&用户表添加vip开始时间.sql

USE yami_bbc;
DROP TABLE IF EXISTS `tz_cost_per_popup`;
CREATE TABLE `tz_cost_per_popup` (
    `popup_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '弹窗广告id',
    `shop_id` bigint NOT NULL COMMENT '店铺id',
    `popup_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '弹窗名称',
    `status` tinyint NOT NULL COMMENT '状态  1.未开始 2.投放中 3.已结束',
    `page_type` tinyint NOT NULL COMMENT '触发页面类型 1.平台首页 2.会员中心 3.支付成功 4.店铺首页 5.商品详情',
    `popup_pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '弹窗图片',
    `jump_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '跳转页面',
    `user_type` tinyint NOT NULL DEFAULT '0' COMMENT '推送用户类型 0.所有用户 1.免费会员 2.付费会员 3.店铺客户 4.店铺会员',
    `start_time` datetime NOT NULL COMMENT '推送开始时间',
    `end_time` datetime NOT NULL COMMENT '推送结束时间',
    `push_frequency` tinyint NOT NULL DEFAULT '0' COMMENT '推送频次 0.永久一次 1.每次进入 2.自定义频次',
    `day_frequency` tinyint DEFAULT NULL COMMENT '按x天推送',
    `week_frequency` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '按周推送 eg. 1,2',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`popup_id`),
    KEY `shop_id` (`popup_id`,`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='弹窗广告';

DROP TABLE IF EXISTS `tz_popup_relate`;
CREATE TABLE `tz_popup_relate` (
    `relate_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '关联id',
    `popup_id` bigint NOT NULL COMMENT '弹窗广告id',
    `prod_id` bigint DEFAULT NULL COMMENT '商品id',
    `user_level_id` bigint DEFAULT NULL COMMENT '会员等级id',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`relate_id`),
    KEY `popup_id` (`popup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='弹窗广告关联';

DROP TABLE IF EXISTS `tz_popup_user_log`;
CREATE TABLE `tz_popup_user_log` (
    `popup_user_log_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户弹窗日志id',
    `popup_id` bigint NOT NULL COMMENT '弹窗id',
    `create_time` int NOT NULL COMMENT '弹窗时间 yyMMdd',
    `user_id` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户id',
    `uuid` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '游客uuid',
    PRIMARY KEY (`popup_user_log_id`),
    KEY `popup_id` (`popup_id`),
    KEY `popup_id_user_id` (`popup_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户弹窗记录表';

insert into `xxl_job_info`(`job_group`, `job_desc`, `add_time`, `update_time`, `author`, `alarm_email`,
                           `schedule_type`, `schedule_conf`, `misfire_strategy`, `executor_route_strategy`,
                           `executor_handler`, `executor_param`, `executor_block_strategy`, `executor_timeout`,
                           `executor_fail_retry_count`, `glue_type`, `glue_source`, `glue_remark`, `glue_updatetime`,
                           `child_jobid`, `trigger_status`, `trigger_last_time`, `trigger_next_time`)
values (2, '改变弹窗广告状态', '2024-01-17 10:07:30', '2024-01-17 10:07:30', 'admin', '', 'CRON',
        '0 0/30 * * * ?', 'DO_NOTHING', 'FIRST', 'changePopup', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN',
        '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 0, 0, 0);

insert into `tz_shop_menu`(`menu_id`,`parent_id`,`url`,`perms`,`type`,`icon`,`order_num`,`hidden`) values
(733,353,'/fitment/feature/popup-ad/index','',1,'',2,0),
(734,353,'/fitment/feature/popup-ad/add-or-update','',1,'',3,1),
(735,733,'','shop:popupAd:save',2,'',0,0),
(736,733,'','shop:popupAd:update',2,'',0,0),
(737,733,'','shop:popupAd:view',2,'',0,0),
(738,733,'','shop:popupAd:delete',2,'',0,0),
(739,733,'','shop:popupAd:invalid',2,'',0,0),
(740,733,'','shop:popupAd:list',2,'',0,0);

insert into `tz_shop_menu_lang`(`menu_id`,`lang`,`name`) values
(733,0,'弹窗广告'),
(733,1,'popup-ad'),
(734,0,'新增弹窗广告'),
(734,1,'Add popup-ad'),
(735,0,'新增'),
(736,0,'编辑'),
(737,0,'查看'),
(738,0,'删除'),
(739,0,'失效活动'),
(740,0,'列表');

insert into `tz_sys_menu`(`menu_id`,`parent_id`,`url`,`perms`,`type`,`icon`,`order_num`,`hidden`) values
(1169,867,'/fitment/feature/popup-ad/index','',1,'',2,0),
(1170,867,'/fitment/feature/popup-ad/add-or-update','',1,'',3,1),
(1171,1169,'','platform:popupAd:save',2,'',0,0),
(1172,1169,'','platform:popupAd:update',2,'',0,0),
(1173,1169,'','platform:popupAd:view',2,'',0,0),
(1174,1169,'','platform:popupAd:delete',2,'',0,0),
(1175,1169,'','platform:popupAd:invalid',2,'',0,0),
(1176,1169,'','platform:popupAd:list',2,'',0,0);

insert into `tz_sys_menu_lang`(`menu_id`,`lang`,`name`) values
(1169,0,'弹窗广告'),
(1169,1,'popup-ad'),
(1170,0,'新增弹窗广告'),
(1170,1,'Add popup-ad'),
(1171,0,'新增'),
(1172,0,'编辑'),
(1173,0,'查看'),
(1174,0,'删除'),
(1175,0,'失效活动'),
(1176,0,'列表');

# 添加vip开始时间
ALTER TABLE `tz_user`
    ADD COLUMN `vip_start_time` datetime NULL COMMENT 'vip开始时间' AFTER `level`;

# 将之前的vip开始时间设置到tz_user表，确保数据正确
UPDATE tz_user u set vip_start_time = (
    SELECT create_time from tz_pay_info pi
    WHERE pi.pay_entry = 2 and u.user_id = pi.user_id ORDER BY pi.create_time DESC LIMIT 1
)

# 按钮权限移除空格
UPDATE tz_shop_menu
SET perms = 'prod:prodComm:delete'
WHERE
    perms = '    prod:prodComm:delete';