53 lines
3.0 KiB
SQL
53 lines
3.0 KiB
SQL
USE youlai_admin;
|
|
|
|
-- 订单异常补偿闭环升级脚本
|
|
-- 说明：
|
|
-- 1. 为订单主表补充补偿状态、补偿说明、重试次数和最近补偿时间。
|
|
-- 2. 新增订单补偿记录表，保留每次退款补偿尝试的结果。
|
|
-- 3. 新增“订单补偿重试”按钮权限，方便后台人工重试失败补偿。
|
|
|
|
ALTER TABLE `xxk_proxy_order`
|
|
ADD COLUMN `compensation_status` VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '补偿状态(NONE无需补偿 PENDING待补偿 SUCCESS补偿成功 FAIL补偿失败)' AFTER `open_status`,
|
|
ADD COLUMN `compensation_reason` VARCHAR(255) NULL COMMENT '补偿说明' AFTER `remark`,
|
|
ADD COLUMN `compensation_retry_count` INT NOT NULL DEFAULT 0 COMMENT '补偿重试次数' AFTER `compensation_reason`,
|
|
ADD COLUMN `last_compensation_time` DATETIME NULL COMMENT '最近补偿时间' AFTER `compensation_retry_count`,
|
|
ADD KEY `idx_xxk_proxy_order_compensation_status` (`compensation_status`, `update_time`);
|
|
|
|
CREATE TABLE `xxk_order_compensation_record` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
|
`order_no` VARCHAR(64) NOT NULL COMMENT '订单号',
|
|
`compensation_type` VARCHAR(32) NOT NULL COMMENT '补偿类型',
|
|
`attempt_no` INT NOT NULL DEFAULT 1 COMMENT '第几次补偿尝试',
|
|
`compensation_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '补偿状态(PENDING/SUCCESS/FAIL)',
|
|
`reason` VARCHAR(255) DEFAULT NULL COMMENT '补偿原因',
|
|
`error_message` VARCHAR(500) DEFAULT NULL COMMENT '失败原因',
|
|
`related_flow_no` VARCHAR(64) DEFAULT NULL COMMENT '关联退款流水号',
|
|
`operator_id` BIGINT DEFAULT NULL COMMENT '操作人ID',
|
|
`operator_name` VARCHAR(100) DEFAULT NULL COMMENT '操作人名称',
|
|
`execute_time` DATETIME DEFAULT NULL COMMENT '执行时间',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_xxk_order_compensation_attempt` (`order_id`, `compensation_type`, `attempt_no`),
|
|
KEY `idx_xxk_order_compensation_order_no` (`order_no`),
|
|
KEY `idx_xxk_order_compensation_status` (`compensation_status`, `execute_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单补偿记录表';
|
|
|
|
INSERT INTO `sys_menu`
|
|
(`id`, `parent_id`, `tree_path`, `name`, `type`, `route_name`, `route_path`, `component`, `perm`,
|
|
`always_show`, `keep_alive`, `visible`, `sort`, `icon`, `redirect`, `create_time`, `update_time`, `params`)
|
|
VALUES
|
|
(11504, 1150, '0,1100,1150', '订单补偿重试', 'B', NULL, '', NULL, 'proxy:order:compensate',
|
|
NULL, NULL, 1, 4, '', NULL, NOW(), NOW(), NULL)
|
|
ON DUPLICATE KEY UPDATE
|
|
`name` = VALUES(`name`),
|
|
`perm` = VALUES(`perm`),
|
|
`visible` = VALUES(`visible`),
|
|
`sort` = VALUES(`sort`),
|
|
`update_time` = NOW();
|
|
|
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES
|
|
(2, 11504);
|