java-bbc/db/2025-08-05 添加订单时间表、清除异常数据和添加主题功能.sql

USE `yami_bbc`;


CREATE TABLE `tz_order_time_config`
(
    `id`                           bigint(20)                                                   NOT NULL AUTO_INCREMENT,
    `order_number`                 varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
    `order_max_time`               int DEFAULT NULL COMMENT '订单最大付款时间',
    `max_order_payed_time`         int DEFAULT NULL COMMENT '最大待发货时间',
    `auto_success_time`            int DEFAULT NULL COMMENT '自动确认收货时间',
    `max_finally_refund_time`      int DEFAULT NULL COMMENT '最大确认收货退款申请时间',
    `max_refund_apply_time`        int DEFAULT NULL COMMENT '退款申请最大处理时间',
    `distribution_settlement_time` int DEFAULT NULL COMMENT '订单结算时间',
    `max_submit_voucher_time`      int DEFAULT NULL COMMENT '最大提交凭证时间',
    `max_intervention_time`        int DEFAULT NULL COMMENT '最大介入时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = 'utf8mb4_general_ci' COMMENT ='订单的时间配置表';

INSERT INTO `tz_order_time_config` (`order_number`,
                                    `order_max_time`,
                                    `max_order_payed_time`,
                                    `auto_success_time`,
                                    `max_finally_refund_time`,
                                    `max_refund_apply_time`,
                                    `distribution_settlement_time`,
                                    `max_submit_voucher_time`,
                                    `max_intervention_time`)
SELECT order_number,
       30,
       7,
       15,
       7,
       7,
       15,
       3,
       7
FROM tz_order
WHERE order_number IS NOT NULL;


-- 清除管理员角色关联表中不存在的管理员id相关数据
DELETE
FROM tz_sys_user_role
WHERE user_id NOT IN (SELECT user_id FROM tz_sys_user);



-- 新增平台主题功能
CREATE TABLE `tz_theme` (
    `theme_id` bigint NOT NULL AUTO_INCREMENT COMMENT '平台主题id',
    `shop_id` bigint DEFAULT NULL COMMENT '店铺id，平台为0',
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主题名称',
    `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主题内容',
    `theme_type` tinyint DEFAULT '0' COMMENT '主题类型 0.系统内置 1.自定义',
    `is_default` tinyint DEFAULT '0' COMMENT '是否默认主题 1.是 0.不是',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`theme_id`),
    KEY `shop_idx` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='平台主题表';


insert into `tz_theme` (`theme_id`, `shop_id`, `name`, `content`, `theme_type`,`is_default`, `create_time`, `update_time`) values
('1','0','red','#F81A1A','0','1','2025-07-15 15:13:07','2025-07-15 15:13:07'),
('2','0','cyan','#16CFB0','0','0','2025-07-15 15:29:13','2025-07-15 15:29:13'),
('3','0','blue','#378CFA','0','0','2025-07-15 15:29:37','2025-07-15 15:29:37'),
('4','0','purple','#8C51FF','0','0','2025-07-15 15:30:00','2025-07-15 15:30:00');


-- 新增主题相关菜单，其中867是移动端装修菜单
insert into `tz_sys_menu` (`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`, `router_name`) values
('1210','867','fitment/feature/theme/index','','1','','4','0','theme'),
('1211','1210','','platform:theme:save','2','','0','0',''),
('1212','1210','','platform:theme:update','2','','0','0',''),
('1213','1210','','platform:theme:page','2','','0','0',''),
('1214','1210','','platform:theme:delete','2','','0','0',''),
('1215','1210','','platform:theme:updateDefaultTheme','2','','0','0','');


insert into `tz_sys_menu_lang` (`menu_id`, `lang`, `name`) values
('1210','0','主题配置'),
('1210','1','Theme Config'),
('1211','0','新增'),
('1211','1','Save'),
('1212','0','修改'),
('1212','1','Update'),
('1213','0','列表'),
('1213','1','Page'),
('1214','0','删除'),
('1214','1','Delete'),
('1215','0','修改默认主题'),
('1215','1','Modify default theme');