-- 提现列表缺少订单状态字段、页面刷新待办接口缺少提现关联表时的本地库定向修复。
|
-- 在应用连接的数据库中执行（例如先 USE talent_card），不要执行在错误的 schema 中。
|
-- 根因：本地关闭 Flyway，订单表未同步 V132 的两个状态字段；查询失败时前端会显示空表。
|
-- 正常由 Flyway 管理的环境应执行正式迁移，而不是使用此脚本。
|
-- 本脚本用于已有迁移历史冲突的本地库：补订单状态、退款回填及缺失的提现关联表，支持重复执行。
|
-- 不改写 Flyway 历史，也不声称完成 V132 的核销唯一约束或后续自动提现的全部迁移。
|
-- 后续恢复 Flyway 前，仍需核对本地历史和已手工补齐的 DDL。
|
|
set @withdrawal_repair_ddl = (
|
select if(count(*) = 0,
|
'alter table benefit_order add column refund_status tinyint not null default 0 comment ''订单退款状态：0未退款 1退款中 2已退款''',
|
'select 1')
|
from information_schema.columns
|
where table_schema = database()
|
and table_name = 'benefit_order'
|
and column_name = 'refund_status'
|
);
|
prepare withdrawal_repair_stmt from @withdrawal_repair_ddl;
|
execute withdrawal_repair_stmt;
|
deallocate prepare withdrawal_repair_stmt;
|
|
set @withdrawal_repair_ddl = (
|
select if(count(*) = 0,
|
'alter table benefit_order add column withdrawal_status tinyint not null default 0 comment ''提现锁定状态：0未进入提现记录 1已进入提现记录''',
|
'select 1')
|
from information_schema.columns
|
where table_schema = database()
|
and table_name = 'benefit_order'
|
and column_name = 'withdrawal_status'
|
);
|
prepare withdrawal_repair_stmt from @withdrawal_repair_ddl;
|
execute withdrawal_repair_stmt;
|
deallocate prepare withdrawal_repair_stmt;
|
|
-- 与 V132 一致：退款成功优先于退款中，已退款订单不能因新增列默认 0 而重新参与提现。
|
update benefit_order o
|
join (select order_id, max(case when refund_status = 2 then 1 else 0 end) refunded
|
from order_refund
|
where deleted = 0 and refund_status in (1, 2)
|
group by order_id) r on r.order_id = o.id
|
set o.refund_status = case when r.refunded = 1 then 2 else 1 end
|
where o.refund_status = 0;
|
|
set @withdrawal_repair_ddl = null;
|
|
-- 页面刷新时 AdminLayout 还会请求 pending-notices；其中的失败提现提醒依赖这张表。
|
-- 缺表的旧库直接采用 V133 + V135 的最终结构，包含 released_at 和有效关联唯一约束。
|
-- 已有且完成迁移的表保持原样；历史手动提现没有来源订单记录，不凭金额猜测关联关系。
|
create table if not exists merchant_withdrawal_order (
|
id bigint not null auto_increment,
|
withdrawal_id bigint not null,
|
order_id bigint not null,
|
amount_cents bigint not null,
|
created_at datetime not null default current_timestamp,
|
released_at datetime default null comment '顺延解锁时间：解锁后该订单可以进入新的提现记录',
|
active tinyint generated always as (if(released_at is null, 1, null)) stored
|
comment '唯一键辅助列：未解锁为 1，已解锁为 NULL',
|
primary key (id),
|
unique key uk_withdrawal_order_active (order_id, active),
|
key idx_withdrawal_order_assoc_withdrawal (withdrawal_id)
|
) engine=InnoDB default charset=utf8mb4 comment='自动提现记录与来源订单的内部关联';
|
