-- 微信支付相关数据表
-- 创建时间：2025-01-27
-- 说明：包含微信支付订单、退款、配置、日志等表结构

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- 使用xiaozhi数据库
USE `xiaozhi`;

-- =============================================
-- 微信支付订单表
-- =============================================
DROP TABLE IF EXISTS `wechat_pay_order`;
CREATE TABLE `wechat_pay_order` (
  `order_id` varchar(64) NOT NULL COMMENT '订单ID，主键',
  `out_trade_no` varchar(32) NOT NULL COMMENT '商户订单号',
  `transaction_id` varchar(32) DEFAULT NULL COMMENT '微信支付订单号',
  `openid` varchar(128) NOT NULL COMMENT '用户openid',
  `device_id` varchar(255) DEFAULT NULL COMMENT '设备ID',
  `body` varchar(128) NOT NULL COMMENT '商品描述',
  `detail` text COMMENT '商品详情',
  `attach` varchar(127) DEFAULT NULL COMMENT '附加数据',
  `total_fee` int NOT NULL COMMENT '订单金额（分）',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额（元）',
  `fee_type` varchar(16) DEFAULT 'CNY' COMMENT '货币类型',
  `spbill_create_ip` varchar(64) DEFAULT NULL COMMENT '终端IP',
  `trade_type` varchar(16) DEFAULT 'JSAPI' COMMENT '交易类型',
  `goods_tag` varchar(32) DEFAULT NULL COMMENT '商品标记',
  `notify_url` varchar(256) DEFAULT NULL COMMENT '通知地址',
  `trade_state` varchar(32) DEFAULT 'NOTPAY' COMMENT '订单状态：NOTPAY-未支付，SUCCESS-支付成功，REFUND-转入退款，CLOSED-已关闭，REVOKED-已撤销，USERPAYING-用户支付中，PAYERROR-支付失败',
  `trade_state_desc` varchar(256) DEFAULT NULL COMMENT '订单状态描述',
  `time_end` datetime DEFAULT NULL COMMENT '支付完成时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `time_expire` datetime DEFAULT NULL COMMENT '订单失效时间',
  `prepay_id` varchar(64) DEFAULT NULL COMMENT '预支付交易会话标识',
  `bank_type` varchar(32) DEFAULT NULL COMMENT '支付银行',
  `cash_fee` int DEFAULT NULL COMMENT '现金支付金额',
  `cash_fee_type` varchar(16) DEFAULT NULL COMMENT '现金支付货币类型',
  `rate` varchar(16) DEFAULT NULL COMMENT '汇率',
  `coupon_fee` int DEFAULT NULL COMMENT '代金券金额',
  `coupon_count` int DEFAULT NULL COMMENT '代金券使用数量',
  `mch_id` varchar(32) DEFAULT NULL COMMENT '微信支付分配的商户号',
  `app_id` varchar(32) DEFAULT NULL COMMENT '微信分配的小程序ID',
  `nonce_str` varchar(32) DEFAULT NULL COMMENT '随机字符串',
  `sign` varchar(64) DEFAULT NULL COMMENT '签名',
  `sign_type` varchar(32) DEFAULT 'MD5' COMMENT '签名类型',
  `deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_openid` (`openid`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_trade_state` (`trade_state`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付订单表';

-- =============================================
-- 微信支付退款表
-- =============================================
DROP TABLE IF EXISTS `wechat_pay_refund`;
CREATE TABLE `wechat_pay_refund` (
  `refund_id` varchar(64) NOT NULL COMMENT '退款ID，主键',
  `out_trade_no` varchar(32) NOT NULL COMMENT '商户订单号',
  `transaction_id` varchar(32) DEFAULT NULL COMMENT '微信支付订单号',
  `out_refund_no` varchar(64) NOT NULL COMMENT '商户退款单号',
  `refund_id_wx` varchar(32) DEFAULT NULL COMMENT '微信退款单号',
  `refund_channel` varchar(16) DEFAULT NULL COMMENT '退款渠道',
  `refund_fee` int NOT NULL COMMENT '退款金额（分）',
  `refund_amount` decimal(10,2) NOT NULL COMMENT '退款金额（元）',
  `total_fee` int NOT NULL COMMENT '订单总金额（分）',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额（元）',
  `fee_type` varchar(16) DEFAULT 'CNY' COMMENT '货币类型',
  `cash_fee` int DEFAULT NULL COMMENT '现金支付金额',
  `cash_refund_fee` int DEFAULT NULL COMMENT '现金退款金额',
  `coupon_refund_fee` int DEFAULT NULL COMMENT '代金券退款金额',
  `coupon_refund_count` int DEFAULT NULL COMMENT '退款代金券使用数量',
  `refund_status` varchar(32) DEFAULT 'PROCESSING' COMMENT '退款状态：SUCCESS-退款成功，REFUNDCLOSE-退款关闭，PROCESSING-退款处理中，CHANGE-退款异常',
  `refund_account` varchar(80) DEFAULT NULL COMMENT '退款资金来源',
  `refund_recv_accout` varchar(64) DEFAULT NULL COMMENT '退款入账账户',
  `refund_success_time` datetime DEFAULT NULL COMMENT '退款成功时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`refund_id`),
  UNIQUE KEY `uk_out_refund_no` (`out_refund_no`),
  KEY `idx_out_trade_no` (`out_trade_no`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_refund_id_wx` (`refund_id_wx`),
  KEY `idx_refund_status` (`refund_status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付退款表';

-- =============================================
-- 微信支付配置表
-- =============================================
DROP TABLE IF EXISTS `wechat_pay_config`;
CREATE TABLE `wechat_pay_config` (
  `config_id` int NOT NULL AUTO_INCREMENT COMMENT '配置ID，主键',
  `config_name` varchar(100) NOT NULL COMMENT '配置名称',
  `app_id` varchar(32) NOT NULL COMMENT '应用ID',
  `mch_id` varchar(32) NOT NULL COMMENT '商户号',
  `partner_key` varchar(32) NOT NULL COMMENT '商户密钥',
  `sign_type` varchar(32) DEFAULT 'MD5' COMMENT '签名类型',
  `cert_path` varchar(500) DEFAULT NULL COMMENT '证书路径',
  `cert_password` varchar(100) DEFAULT NULL COMMENT '证书密码',
  `notify_url` varchar(500) DEFAULT NULL COMMENT '支付回调通知URL',
  `refund_notify_url` varchar(500) DEFAULT NULL COMMENT '退款回调通知URL',
  `sandbox` tinyint DEFAULT 0 COMMENT '是否沙箱环境：0-正式环境，1-沙箱环境',
  `status` tinyint DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `uk_config_name` (`config_name`),
  KEY `idx_app_id` (`app_id`),
  KEY `idx_mch_id` (`mch_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付配置表';

-- =============================================
-- 微信支付日志表
-- =============================================
DROP TABLE IF EXISTS `wechat_pay_log`;
CREATE TABLE `wechat_pay_log` (
  `log_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID，主键',
  `trade_no` varchar(64) DEFAULT NULL COMMENT '交易单号（订单号或退款单号）',
  `log_type` varchar(32) NOT NULL COMMENT '日志类型：ORDER-订单，REFUND-退款，NOTIFY-回调通知，QUERY-查询',
  `operation` varchar(64) NOT NULL COMMENT '操作类型：CREATE-创建，UPDATE-更新，QUERY-查询，NOTIFY-通知',
  `request_data` text COMMENT '请求数据',
  `response_data` text COMMENT '响应数据',
  `result_code` varchar(16) DEFAULT NULL COMMENT '结果代码',
  `result_msg` varchar(500) DEFAULT NULL COMMENT '结果消息',
  `error_code` varchar(32) DEFAULT NULL COMMENT '错误代码',
  `error_msg` varchar(500) DEFAULT NULL COMMENT '错误消息',
  `cost_time` int DEFAULT NULL COMMENT '耗时（毫秒）',
  `ip_address` varchar(64) DEFAULT NULL COMMENT 'IP地址',
  `user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`),
  KEY `idx_trade_no` (`trade_no`),
  KEY `idx_log_type` (`log_type`),
  KEY `idx_operation` (`operation`),
  KEY `idx_result_code` (`result_code`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付日志表';

-- =============================================
-- 插入默认配置数据
-- =============================================
INSERT INTO `wechat_pay_config` (
  `config_name`, 
  `app_id`, 
  `mch_id`, 
  `partner_key`, 
  `sign_type`, 
  `notify_url`, 
  `refund_notify_url`, 
  `sandbox`, 
  `status`, 
  `remark`
) VALUES (
  '默认微信支付配置',
  'wxff56c34ef9aceb62',
  '1234567890',
  'your_partner_key_here',
  'MD5',
  'http://localhost:8091/api/wechat/pay/notify',
  'http://localhost:8091/api/wechat/pay/refund/notify',
  0,
  1,
  '系统默认微信支付配置，请根据实际情况修改'
);

-- =============================================
-- 创建视图：订单统计视图
-- =============================================
DROP VIEW IF EXISTS `v_wechat_pay_order_stats`;
CREATE VIEW `v_wechat_pay_order_stats` AS
SELECT 
  DATE(create_time) as order_date,
  trade_state,
  COUNT(*) as order_count,
  SUM(total_fee) as total_fee_sum,
  SUM(amount) as total_amount_sum,
  AVG(total_fee) as avg_fee,
  AVG(amount) as avg_amount
FROM `wechat_pay_order`
WHERE deleted = 0
GROUP BY DATE(create_time), trade_state
ORDER BY order_date DESC, trade_state;

-- =============================================
-- 创建视图：退款统计视图
-- =============================================
DROP VIEW IF EXISTS `v_wechat_pay_refund_stats`;
CREATE VIEW `v_wechat_pay_refund_stats` AS
SELECT 
  DATE(create_time) as refund_date,
  refund_status,
  COUNT(*) as refund_count,
  SUM(refund_fee) as total_refund_fee,
  SUM(refund_amount) as total_refund_amount,
  AVG(refund_fee) as avg_refund_fee,
  AVG(refund_amount) as avg_refund_amount
FROM `wechat_pay_refund`
WHERE deleted = 0
GROUP BY DATE(create_time), refund_status
ORDER BY refund_date DESC, refund_status;