108 lines
4.4 KiB
SQL
108 lines
4.4 KiB
SQL
-- ============================================
|
||
-- SPA管理系统 - 支付退款系统SQL
|
||
-- ============================================
|
||
-- 版本: v1.0
|
||
-- 创建时间: 2025-12-30
|
||
-- 数据库: MySQL 8.0+
|
||
-- 字符集: utf8mb4
|
||
-- ============================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================
|
||
-- 支付相关表
|
||
-- ============================================
|
||
|
||
-- 1. 支付记录表
|
||
DROP TABLE IF EXISTS `spa_payment`;
|
||
CREATE TABLE `spa_payment` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
`deleted_at` datetime(3) DEFAULT NULL,
|
||
|
||
`payment_no` varchar(50) NOT NULL COMMENT '支付流水号',
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`order_no` varchar(50) NOT NULL COMMENT '订单号',
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
||
|
||
`pay_amount` decimal(10,2) NOT NULL COMMENT '支付金额',
|
||
`pay_type` tinyint NOT NULL COMMENT '支付方式 1微信 2支付宝 3余额 4银行卡',
|
||
`pay_status` tinyint DEFAULT 1 COMMENT '支付状态 1待支付 2支付中 3支付成功 4支付失败 5已关闭',
|
||
|
||
`third_party_no` varchar(100) DEFAULT NULL COMMENT '第三方支付流水号',
|
||
`pay_time` datetime DEFAULT NULL COMMENT '支付完成时间',
|
||
`callback_time` datetime DEFAULT NULL COMMENT '回调时间',
|
||
`callback_data` text COMMENT '回调数据JSON',
|
||
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_payment_no` (`payment_no`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付记录表';
|
||
|
||
-- 2. 退款申请表
|
||
DROP TABLE IF EXISTS `spa_refund`;
|
||
CREATE TABLE `spa_refund` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
`deleted_at` datetime(3) DEFAULT NULL,
|
||
|
||
`refund_no` varchar(50) NOT NULL COMMENT '退款单号',
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`order_no` varchar(50) NOT NULL COMMENT '订单号',
|
||
`payment_id` bigint unsigned DEFAULT NULL COMMENT '支付记录ID',
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
||
|
||
`refund_amount` decimal(10,2) NOT NULL COMMENT '退款金额',
|
||
`refund_reason` varchar(500) DEFAULT NULL COMMENT '退款原因',
|
||
`refund_type` tinyint NOT NULL COMMENT '退款类型 1用户申请 2系统自动 3客服处理',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1待审核 2审核通过 3审核拒绝 4退款中 5退款成功 6退款失败',
|
||
`audit_user_id` bigint unsigned DEFAULT NULL COMMENT '审核人ID',
|
||
`audit_time` datetime DEFAULT NULL COMMENT '审核时间',
|
||
`audit_remark` varchar(500) DEFAULT NULL COMMENT '审核备注',
|
||
|
||
`third_party_refund_no` varchar(100) DEFAULT NULL COMMENT '第三方退款流水号',
|
||
`refund_success_time` datetime DEFAULT NULL COMMENT '退款成功时间',
|
||
|
||
`images` json DEFAULT NULL COMMENT '退款凭证图片JSON数组',
|
||
`remark` text COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_refund_no` (`refund_no`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款申请表';
|
||
|
||
-- 3. 账户余额变动记录表
|
||
DROP TABLE IF EXISTS `spa_balance_log`;
|
||
CREATE TABLE `spa_balance_log` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
|
||
`user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
|
||
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
|
||
|
||
`change_type` tinyint NOT NULL COMMENT '变动类型 1充值 2消费 3退款 4分佣收入 5提现 6系统调整',
|
||
`change_amount` decimal(10,2) NOT NULL COMMENT '变动金额（正数增加，负数减少）',
|
||
`before_balance` decimal(10,2) NOT NULL COMMENT '变动前余额',
|
||
`after_balance` decimal(10,2) NOT NULL COMMENT '变动后余额',
|
||
|
||
`related_id` bigint unsigned DEFAULT NULL COMMENT '关联业务ID（订单ID/提现ID等）',
|
||
`related_no` varchar(50) DEFAULT NULL COMMENT '关联业务单号',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user` (`user_type`, `user_id`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户余额变动记录表';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|