-- 支付订单表
CREATE TABLE IF NOT EXISTS `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '支付订单ID',
  `payment_no` varchar(64) NOT NULL COMMENT '支付订单号',
  `order_id` int(11) NOT NULL COMMENT '关联订单ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `amount` decimal(10,2) NOT NULL COMMENT '支付金额',
  `paid_amount` decimal(10,2) DEFAULT NULL COMMENT '实际支付金额',
  `payment_method` enum('wechat','alipay','balance') NOT NULL COMMENT '支付方式：wechat-微信支付，alipay-支付宝，balance-余额支付',
  `status` enum('pending','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'pending' COMMENT '支付状态：pending-待支付，paid-已支付，failed-支付失败，refunded-已退款，cancelled-已取消',
  `transaction_id` varchar(128) DEFAULT NULL COMMENT '第三方交易号',
  `return_url` varchar(255) DEFAULT NULL COMMENT '支付成功回调地址',
  `notify_url` varchar(255) DEFAULT NULL COMMENT '异步通知地址',
  `paid_at` datetime DEFAULT NULL COMMENT '支付时间',
  `failure_reason` varchar(255) DEFAULT NULL COMMENT '失败原因',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_payment_no` (`payment_no`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_payment_method` (`payment_method`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_deleted_at` (`deleted_at`),
  CONSTRAINT `fk_payments_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payments_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付订单表';

-- 退款记录表
CREATE TABLE IF NOT EXISTS `refunds` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '退款ID',
  `refund_no` varchar(64) NOT NULL COMMENT '退款订单号',
  `payment_id` int(11) NOT NULL COMMENT '支付订单ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `refund_amount` decimal(10,2) NOT NULL COMMENT '退款金额',
  `refund_reason` varchar(500) NOT NULL COMMENT '退款原因',
  `status` enum('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending' COMMENT '退款状态：pending-待处理，approved-已同意，rejected-已拒绝，completed-已完成',
  `processed_by` int(11) DEFAULT NULL COMMENT '处理人ID',
  `process_remark` varchar(500) DEFAULT NULL COMMENT '处理备注',
  `refund_transaction_id` varchar(128) DEFAULT NULL COMMENT '退款交易号',
  `processed_at` datetime DEFAULT NULL COMMENT '处理时间',
  `refunded_at` datetime DEFAULT NULL COMMENT '退款完成时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_refund_no` (`refund_no`),
  KEY `idx_payment_id` (`payment_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_processed_by` (`processed_by`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_deleted_at` (`deleted_at`),
  CONSTRAINT `fk_refunds_payment_id` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_refunds_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_refunds_processed_by` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='退款记录表';

-- 插入示例数据（可选）
-- INSERT INTO `payments` (`payment_no`, `order_id`, `user_id`, `amount`, `payment_method`, `status`) VALUES
-- ('PAY202401010001', 1, 1, 299.00, 'wechat', 'pending'),
-- ('PAY202401010002', 2, 2, 199.00, 'alipay', 'paid');

-- 创建索引优化查询性能
CREATE INDEX `idx_payments_user_status` ON `payments` (`user_id`, `status`);
CREATE INDEX `idx_payments_method_status` ON `payments` (`payment_method`, `status`);
CREATE INDEX `idx_refunds_user_status` ON `refunds` (`user_id`, `status`);