-- ============================================
-- 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;