60 lines
2.7 KiB
SQL
60 lines
2.7 KiB
SQL
-- 创建退款申请表
|
||
CREATE TABLE IF NOT EXISTS tb_refund_request (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP WITH TIME ZONE,
|
||
creator BIGINT NOT NULL DEFAULT 0,
|
||
updater BIGINT NOT NULL DEFAULT 0,
|
||
|
||
-- 退款单基础信息
|
||
refund_no VARCHAR(50) NOT NULL,
|
||
order_id BIGINT NOT NULL,
|
||
package_usage_id BIGINT,
|
||
shop_id BIGINT,
|
||
|
||
-- 金额信息
|
||
actual_received_amount BIGINT NOT NULL,
|
||
requested_refund_amount BIGINT NOT NULL,
|
||
approved_refund_amount BIGINT,
|
||
|
||
-- 退款原因和状态
|
||
refund_reason TEXT,
|
||
status INT NOT NULL DEFAULT 1,
|
||
|
||
-- 审批信息
|
||
processor_id BIGINT,
|
||
processed_at TIMESTAMP WITH TIME ZONE,
|
||
reject_reason TEXT,
|
||
remark TEXT,
|
||
|
||
-- 后处理标记
|
||
commission_deducted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
asset_reset BOOLEAN NOT NULL DEFAULT FALSE
|
||
);
|
||
|
||
-- 索引
|
||
CREATE UNIQUE INDEX idx_refund_request_refund_no ON tb_refund_request (refund_no) WHERE deleted_at IS NULL;
|
||
CREATE INDEX idx_refund_request_order_id ON tb_refund_request (order_id);
|
||
CREATE INDEX idx_refund_request_shop_id ON tb_refund_request (shop_id);
|
||
CREATE INDEX idx_refund_request_status ON tb_refund_request (status);
|
||
CREATE INDEX idx_refund_request_deleted_at ON tb_refund_request (deleted_at);
|
||
|
||
-- 字段注释
|
||
COMMENT ON TABLE tb_refund_request IS '退款申请表';
|
||
COMMENT ON COLUMN tb_refund_request.refund_no IS '退款单号（RF+日期时间+6位随机数）';
|
||
COMMENT ON COLUMN tb_refund_request.order_id IS '关联订单ID';
|
||
COMMENT ON COLUMN tb_refund_request.package_usage_id IS '关联套餐使用记录ID（可选）';
|
||
COMMENT ON COLUMN tb_refund_request.shop_id IS '店铺ID（从订单获取，用于数据权限过滤）';
|
||
COMMENT ON COLUMN tb_refund_request.actual_received_amount IS '实收金额（分）';
|
||
COMMENT ON COLUMN tb_refund_request.requested_refund_amount IS '申请退款金额（分）';
|
||
COMMENT ON COLUMN tb_refund_request.approved_refund_amount IS '审批实际退款金额（分）';
|
||
COMMENT ON COLUMN tb_refund_request.refund_reason IS '退款原因';
|
||
COMMENT ON COLUMN tb_refund_request.status IS '状态 1-待审批 2-已通过 3-已拒绝 4-已退回';
|
||
COMMENT ON COLUMN tb_refund_request.processor_id IS '审批人ID';
|
||
COMMENT ON COLUMN tb_refund_request.processed_at IS '审批时间';
|
||
COMMENT ON COLUMN tb_refund_request.reject_reason IS '拒绝原因';
|
||
COMMENT ON COLUMN tb_refund_request.remark IS '审批备注';
|
||
COMMENT ON COLUMN tb_refund_request.commission_deducted IS '佣金是否已回扣';
|
||
COMMENT ON COLUMN tb_refund_request.asset_reset IS '资产是否已重置（套餐停用+停机+世代重置）';
|