470 lines
27 KiB
SQL
470 lines
27 KiB
SQL
-- ========================================
|
|
-- yanzhu-module-pay 建表 SQL
|
|
-- 数据库: MySQL 8.0+
|
|
-- ========================================
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- ========================================
|
|
-- 核心支付表
|
|
-- ========================================
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_app
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_app`;
|
|
CREATE TABLE `pay_app` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '应用编号',
|
|
`app_key` varchar(64) NOT NULL COMMENT '应用密钥',
|
|
`name` varchar(64) NOT NULL COMMENT '应用名称',
|
|
`status` tinyint NOT NULL COMMENT '状态',
|
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
|
`order_notify_url` varchar(1024) NOT NULL COMMENT '订单支付通知 URL',
|
|
`refund_notify_url` varchar(1024) NOT NULL COMMENT '退款通知 URL',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_app_key` (`app_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付应用';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_channel
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_channel`;
|
|
CREATE TABLE `pay_channel` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '渠道编号',
|
|
`code` varchar(32) NOT NULL COMMENT '渠道编码',
|
|
`status` tinyint NOT NULL COMMENT '状态',
|
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
|
`fee_rate` double NOT NULL DEFAULT 0 COMMENT '手续费费率',
|
|
`app_id` bigint NOT NULL COMMENT '应用编号',
|
|
`config` varchar(10240) NOT NULL COMMENT '渠道配置',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付渠道';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_order
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_order`;
|
|
CREATE TABLE `pay_order` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单编号',
|
|
`app_id` bigint NOT NULL COMMENT '应用编号',
|
|
`channel_id` bigint DEFAULT NULL COMMENT '渠道编号',
|
|
`channel_code` varchar(32) DEFAULT NULL COMMENT '渠道编码',
|
|
`merchant_order_id` varchar(64) NOT NULL COMMENT '商户订单编号',
|
|
`subject` varchar(32) NOT NULL COMMENT '商品标题',
|
|
`body` varchar(128) NOT NULL COMMENT '商品描述',
|
|
`notify_url` varchar(1024) NOT NULL COMMENT '通知地址',
|
|
`price` bigint NOT NULL COMMENT '支付金额（分）',
|
|
`channel_fee_rate` double DEFAULT 0 COMMENT '渠道手续费费率',
|
|
`channel_fee_price` bigint DEFAULT 0 COMMENT '渠道手续费（分）',
|
|
`status` tinyint NOT NULL COMMENT '支付状态',
|
|
`user_ip` varchar(50) NOT NULL COMMENT '用户 IP',
|
|
`user_id` bigint DEFAULT NULL COMMENT '用户编号',
|
|
`user_type` tinyint DEFAULT NULL COMMENT '用户类型',
|
|
`expire_time` datetime NOT NULL COMMENT '过期时间',
|
|
`success_time` datetime DEFAULT NULL COMMENT '成功时间',
|
|
`notify_time` datetime DEFAULT NULL COMMENT '通知时间',
|
|
`extension_id` bigint DEFAULT NULL COMMENT '拓展编号',
|
|
`no` varchar(64) DEFAULT NULL COMMENT '支付单号',
|
|
`refund_price` bigint NOT NULL DEFAULT 0 COMMENT '退款金额（分）',
|
|
`channel_user_id` varchar(255) DEFAULT NULL COMMENT '渠道用户编号',
|
|
`channel_order_no` varchar(64) DEFAULT NULL COMMENT '渠道订单号',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_merchant_order_id` (`merchant_order_id`),
|
|
KEY `idx_no` (`no`),
|
|
KEY `idx_app_id` (`app_id`),
|
|
KEY `idx_channel_id` (`channel_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_order_extension
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_order_extension`;
|
|
CREATE TABLE `pay_order_extension` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '拓展编号',
|
|
`no` varchar(64) NOT NULL COMMENT '支付单号',
|
|
`order_id` bigint NOT NULL COMMENT '订单编号',
|
|
`channel_id` bigint NOT NULL COMMENT '渠道编号',
|
|
`channel_code` varchar(32) NOT NULL COMMENT '渠道编码',
|
|
`user_ip` varchar(50) DEFAULT NULL COMMENT '用户 IP',
|
|
`status` tinyint NOT NULL COMMENT '状态',
|
|
`channel_extras` varchar(1024) DEFAULT NULL COMMENT '渠道扩展数据',
|
|
`channel_error_code` varchar(64) DEFAULT NULL COMMENT '渠道错误码',
|
|
`channel_error_msg` varchar(256) DEFAULT NULL COMMENT '渠道错误信息',
|
|
`channel_notify_data` varchar(4096) DEFAULT NULL COMMENT '渠道通知数据',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_no` (`no`),
|
|
KEY `idx_order_id` (`order_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单拓展';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_refund
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_refund`;
|
|
CREATE TABLE `pay_refund` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '退款编号',
|
|
`no` varchar(64) NOT NULL COMMENT '退款单号',
|
|
`app_id` bigint NOT NULL COMMENT '应用编号',
|
|
`channel_id` bigint NOT NULL COMMENT '渠道编号',
|
|
`channel_code` varchar(32) NOT NULL COMMENT '渠道编码',
|
|
`order_id` bigint NOT NULL COMMENT '订单编号',
|
|
`order_no` varchar(64) NOT NULL COMMENT '订单号',
|
|
`merchant_order_id` varchar(64) NOT NULL COMMENT '商户订单编号',
|
|
`merchant_refund_id` varchar(64) NOT NULL COMMENT '商户退款编号',
|
|
`notify_url` varchar(1024) NOT NULL COMMENT '通知地址',
|
|
`status` tinyint NOT NULL COMMENT '退款状态',
|
|
`pay_price` bigint NOT NULL COMMENT '支付金额（分）',
|
|
`refund_price` bigint NOT NULL COMMENT '退款金额（分）',
|
|
`reason` varchar(256) NOT NULL COMMENT '退款原因',
|
|
`user_ip` varchar(50) DEFAULT NULL COMMENT '用户 IP',
|
|
`user_id` bigint DEFAULT NULL COMMENT '用户编号',
|
|
`user_type` tinyint DEFAULT NULL COMMENT '用户类型',
|
|
`channel_order_no` varchar(64) NOT NULL COMMENT '渠道订单号',
|
|
`channel_refund_no` varchar(64) DEFAULT NULL COMMENT '渠道退款单号',
|
|
`success_time` datetime DEFAULT NULL COMMENT '成功时间',
|
|
`channel_error_code` varchar(128) DEFAULT NULL COMMENT '渠道错误码',
|
|
`channel_error_msg` varchar(256) DEFAULT NULL COMMENT '渠道错误信息',
|
|
`channel_notify_data` varchar(4096) DEFAULT NULL COMMENT '渠道通知数据',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_no` (`no`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_app_id` (`app_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款订单';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_notify_task
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_notify_task`;
|
|
CREATE TABLE `pay_notify_task` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务编号',
|
|
`app_id` bigint NOT NULL COMMENT '应用编号',
|
|
`type` tinyint NOT NULL COMMENT '通知类型',
|
|
`data_id` bigint NOT NULL COMMENT '数据编号',
|
|
`merchant_order_id` varchar(64) NOT NULL COMMENT '商户订单编号',
|
|
`status` tinyint NOT NULL COMMENT '状态',
|
|
`next_notify_time` datetime DEFAULT NULL COMMENT '下次通知时间',
|
|
`last_execute_time` datetime DEFAULT NULL COMMENT '最后执行时间',
|
|
`notify_times` int NOT NULL DEFAULT 0 COMMENT '通知次数',
|
|
`max_notify_times` int NOT NULL COMMENT '最大通知次数',
|
|
`notify_url` varchar(1024) NOT NULL COMMENT '通知地址',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_app_id` (`app_id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_next_notify_time` (`next_notify_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付通知任务';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_notify_log
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_notify_log`;
|
|
CREATE TABLE `pay_notify_log` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志编号',
|
|
`task_id` bigint NOT NULL COMMENT '任务编号',
|
|
`notify_times` int NOT NULL COMMENT '通知次数',
|
|
`response` varchar(4096) NOT NULL COMMENT '响应结果',
|
|
`status` tinyint NOT NULL COMMENT '状态',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_task_id` (`task_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付通知日志';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_transfer
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_transfer`;
|
|
CREATE TABLE `pay_transfer` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '转账编号',
|
|
`no` varchar(64) NOT NULL COMMENT '转账单号',
|
|
`app_id` bigint NOT NULL COMMENT '应用编号',
|
|
`channel_id` bigint NOT NULL COMMENT '渠道编号',
|
|
`channel_code` varchar(32) NOT NULL COMMENT '渠道编码',
|
|
`user_id` bigint DEFAULT NULL COMMENT '用户编号',
|
|
`user_type` tinyint DEFAULT NULL COMMENT '用户类型',
|
|
`merchant_transfer_id` varchar(64) NOT NULL COMMENT '商户转账编号',
|
|
`price` bigint NOT NULL COMMENT '转账金额（分）',
|
|
`subject` varchar(256) NOT NULL COMMENT '转账主题',
|
|
`user_account` varchar(256) NOT NULL COMMENT '用户账号',
|
|
`user_name` varchar(64) DEFAULT NULL COMMENT '用户姓名',
|
|
`status` tinyint NOT NULL COMMENT '转账状态',
|
|
`notify_url` varchar(1024) DEFAULT NULL COMMENT '通知地址',
|
|
`channel_transfer_no` varchar(64) DEFAULT NULL COMMENT '渠道转账单号',
|
|
`success_time` datetime DEFAULT NULL COMMENT '成功时间',
|
|
`channel_error_code` varchar(128) DEFAULT NULL COMMENT '渠道错误码',
|
|
`channel_error_msg` varchar(256) DEFAULT NULL COMMENT '渠道错误信息',
|
|
`channel_notify_data` varchar(4096) DEFAULT NULL COMMENT '渠道通知数据',
|
|
`channel_extras` varchar(1024) DEFAULT NULL COMMENT '渠道扩展数据',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_no` (`no`),
|
|
KEY `idx_app_id` (`app_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='转账单';
|
|
|
|
-- ========================================
|
|
-- 钱包相关表
|
|
-- ========================================
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_wallet
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_wallet`;
|
|
CREATE TABLE `pay_wallet` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
|
`user_type` int DEFAULT NULL COMMENT '用户类型',
|
|
`balance` int NOT NULL DEFAULT 0 COMMENT '余额（分）',
|
|
`freeze_price` int NOT NULL DEFAULT 0 COMMENT '冻结金额（分）',
|
|
`total_expense` int NOT NULL DEFAULT 0 COMMENT '累计支出（分）',
|
|
`total_recharge` int NOT NULL DEFAULT 0 COMMENT '累计充值（分）',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员钱包';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_wallet_transaction
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_wallet_transaction`;
|
|
CREATE TABLE `pay_wallet_transaction` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
|
`no` varchar(64) NOT NULL COMMENT '流水号',
|
|
`wallet_id` bigint NOT NULL COMMENT '钱包编号',
|
|
`biz_type` int NOT NULL COMMENT '业务类型',
|
|
`biz_id` varchar(64) DEFAULT NULL COMMENT '业务编号',
|
|
`title` varchar(64) DEFAULT NULL COMMENT '流水说明',
|
|
`price` int NOT NULL COMMENT '交易金额（分）',
|
|
`balance` int NOT NULL COMMENT '交易后余额（分）',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_wallet_id` (`wallet_id`),
|
|
KEY `idx_no` (`no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员钱包流水';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_wallet_recharge
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_wallet_recharge`;
|
|
CREATE TABLE `pay_wallet_recharge` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
|
`wallet_id` bigint NOT NULL COMMENT '钱包编号',
|
|
`total_price` int NOT NULL COMMENT '用户实际到账余额（分）',
|
|
`pay_price` int NOT NULL COMMENT '实际支付金额（分）',
|
|
`bonus_price` int NOT NULL DEFAULT 0 COMMENT '钱包赠送金额（分）',
|
|
`package_id` bigint DEFAULT NULL COMMENT '充值套餐编号',
|
|
`pay_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已支付',
|
|
`pay_order_id` bigint DEFAULT NULL COMMENT '支付订单编号',
|
|
`pay_channel_code` varchar(32) DEFAULT NULL COMMENT '支付渠道',
|
|
`pay_time` datetime DEFAULT NULL COMMENT '订单支付时间',
|
|
`pay_refund_id` bigint DEFAULT NULL COMMENT '支付退款单编号',
|
|
`refund_total_price` int DEFAULT NULL COMMENT '退款金额（分）',
|
|
`refund_pay_price` int DEFAULT NULL COMMENT '退款支付金额（分）',
|
|
`refund_bonus_price` int DEFAULT NULL COMMENT '退款钱包赠送金额（分）',
|
|
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
|
`refund_status` int DEFAULT NULL COMMENT '退款状态',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_wallet_id` (`wallet_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员钱包充值';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_wallet_recharge_package
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_wallet_recharge_package`;
|
|
CREATE TABLE `pay_wallet_recharge_package` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
|
`name` varchar(64) NOT NULL COMMENT '套餐名',
|
|
`pay_price` int NOT NULL COMMENT '支付金额（分）',
|
|
`bonus_price` int NOT NULL DEFAULT 0 COMMENT '赠送金额（分）',
|
|
`status` int NOT NULL COMMENT '状态',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员钱包充值套餐';
|
|
|
|
-- ========================================
|
|
-- 示例演示表
|
|
-- ========================================
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_demo_order
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_demo_order`;
|
|
CREATE TABLE `pay_demo_order` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单编号',
|
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
|
`spu_id` bigint DEFAULT NULL COMMENT '商品编号',
|
|
`spu_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
|
|
`price` int NOT NULL COMMENT '价格（分）',
|
|
`pay_status` bit(1) DEFAULT NULL COMMENT '是否支付',
|
|
`pay_order_id` bigint DEFAULT NULL COMMENT '支付订单编号',
|
|
`pay_time` datetime DEFAULT NULL COMMENT '付款时间',
|
|
`pay_channel_code` varchar(32) DEFAULT NULL COMMENT '支付渠道',
|
|
`pay_refund_id` bigint DEFAULT NULL COMMENT '支付退款单号',
|
|
`refund_price` int DEFAULT NULL COMMENT '退款金额（分）',
|
|
`refund_time` datetime DEFAULT NULL COMMENT '退款完成时间',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例订单';
|
|
|
|
-- ----------------------------
|
|
-- Table structure for pay_demo_withdraw
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `pay_demo_withdraw`;
|
|
CREATE TABLE `pay_demo_withdraw` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '提现单编号',
|
|
`subject` varchar(255) DEFAULT NULL COMMENT '提现标题',
|
|
`price` int NOT NULL COMMENT '提现金额（分）',
|
|
`user_account` varchar(256) NOT NULL COMMENT '收款人账号',
|
|
`user_name` varchar(64) DEFAULT NULL COMMENT '收款人姓名',
|
|
`type` int DEFAULT NULL COMMENT '提现方式',
|
|
`status` int DEFAULT NULL COMMENT '提现状态',
|
|
`pay_transfer_id` bigint DEFAULT NULL COMMENT '转账单编号',
|
|
`transfer_channel_code` varchar(32) DEFAULT NULL COMMENT '转账渠道',
|
|
`transfer_time` datetime DEFAULT NULL COMMENT '转账成功时间',
|
|
`transfer_error_msg` varchar(500) DEFAULT NULL COMMENT '转账错误提示',
|
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
|
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例提现订单';
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- ========================================
|
|
-- 字典数据
|
|
-- ========================================
|
|
|
|
-- 支付渠道编码
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(115, 20, '支付宝 PC 支付', 'alipay_pc', 'pay_channel_code', 0, 'primary', '', '支付宝 PC 支付', '1', '2021-12-03 10:42:38', '1', '2023-07-19 20:09:15', b'0'),
|
|
(116, 22, '支付宝 WAP 支付', 'alipay_wap', 'pay_channel_code', 0, 'primary', '', '支付宝 WAP 支付', '1', '2021-12-03 10:42:48', '1', '2023-07-19 20:09:17', b'0'),
|
|
(117, 11, '支付宝 App 支付', 'alipay_app', 'pay_channel_code', 0, 'primary', '', '支付宝 App 支付', '1', '2021-12-03 10:42:55', '1', '2023-07-19 20:09:20', b'0'),
|
|
(118, 12, '支付宝扫码支付', 'alipay_qr', 'pay_channel_code', 0, 'primary', '', '支付宝扫码支付', '1', '2021-12-03 10:43:10', '1', '2023-07-19 20:09:28', b'0'),
|
|
(1230, 13, '支付宝条码支付', 'alipay_bar', 'pay_channel_code', 0, 'primary', '', '支付宝条码支付', '1', '2023-02-18 23:32:24', '1', '2023-07-19 20:09:23', b'0'),
|
|
(1344, 4, '微信扫码支付', 'wx_native', 'pay_channel_code', 0, 'success', '', '微信扫码支付', '1', '2023-07-19 20:07:47', '1', '2023-07-19 20:09:03', b'0'),
|
|
(1345, 5, '微信条码支付', 'wx_bar', 'pay_channel_code', 0, 'success', '', '微信条码支付', '1', '2023-07-19 20:08:06', '1', '2023-07-19 20:09:08', b'0'),
|
|
(1348, 20, '模拟支付', 'mock', 'pay_channel_code', 0, 'default', '', '模拟支付', '1', '2023-07-29 11:10:51', '1', '2023-07-29 03:14:10', b'0'),
|
|
(1385, 21, '钱包', 'wallet', 'pay_channel_code', 0, 'primary', '', '', '1', '2023-10-01 21:46:19', '1', '2023-10-01 21:48:01', b'0');
|
|
|
|
-- 支付订单状态
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(123, 10, '支付成功', '10', 'pay_order_status', 0, 'success', '', '支付成功', '1', '2021-12-03 11:18:29', '1', '2023-07-19 18:04:28', b'0'),
|
|
(124, 30, '支付关闭', '30', 'pay_order_status', 0, 'info', '', '支付关闭', '1', '2021-12-03 11:18:42', '1', '2023-07-19 18:05:07', b'0'),
|
|
(125, 0, '等待支付', '0', 'pay_order_status', 0, 'info', '', '未支付', '1', '2021-12-03 11:18:18', '1', '2023-07-19 18:04:15', b'0'),
|
|
(1341, 20, '已退款', '20', 'pay_order_status', 0, 'danger', '', '已退款', '1', '2023-07-19 18:05:37', '1', '2023-07-19 18:05:37', b'0');
|
|
|
|
-- 退款状态
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(1118, 0, '等待退款', '0', 'pay_refund_status', 0, 'info', '', '等待退款', '1', '2021-12-10 16:44:59', '1', '2023-07-19 10:14:39', b'0'),
|
|
(1119, 20, '退款失败', '20', 'pay_refund_status', 0, 'danger', '', '退款失败', '1', '2021-12-10 16:45:10', '1', '2023-07-19 10:15:10', b'0'),
|
|
(1124, 10, '退款成功', '10', 'pay_refund_status', 0, 'success', '', '退款成功', '1', '2021-12-10 16:46:26', '1', '2023-07-19 10:15:00', b'0');
|
|
|
|
-- 通知状态
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(536, 10, '通知成功', '10', 'pay_notify_status', 0, 'success', '', '通知成功', '1', '2021-12-03 11:02:41', '1', '2023-07-19 10:08:19', b'0'),
|
|
(537, 20, '通知失败', '20', 'pay_notify_status', 0, 'danger', '', '通知失败', '1', '2021-12-03 11:02:59', '1', '2023-07-19 10:08:21', b'0'),
|
|
(538, 0, '等待通知', '0', 'pay_notify_status', 0, 'info', '', '未通知', '1', '2021-12-03 11:03:10', '1', '2023-07-19 10:08:24', b'0'),
|
|
(1342, 21, '请求成功，但是结果失败', '21', 'pay_notify_status', 0, 'warning', '', '请求成功，但是结果失败', '1', '2023-07-19 18:10:47', '1', '2023-07-19 18:11:38', b'0');
|
|
|
|
-- 通知类型
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(1346, 1, '支付单', '1', 'pay_notify_type', 0, 'primary', '', '支付单', '1', '2023-07-20 12:23:17', '1', '2023-07-20 12:23:17', b'0'),
|
|
(1347, 2, '退款单', '2', 'pay_notify_type', 0, 'danger', '', NULL, '1', '2023-07-20 12:23:26', '1', '2023-07-20 12:23:26', b'0');
|
|
|
|
-- 转账状态
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
|
(1482, 4, '转账失败', '20', 'pay_transfer_status', 0, 'warning', '', '', '1', '2023-10-28 16:24:16', '1', '2025-05-08 12:59:01', b'0'),
|
|
(1483, 3, '转账成功', '10', 'pay_transfer_status', 0, 'success', '', '', '1', '2023-10-28 16:23:50', '1', '2025-05-08 12:58:58', b'0'),
|
|
(1484, 2, '转账进行中', '5', 'pay_transfer_status', 0, 'info', '', '', '1', '2023-10-28 16:23:12', '1', '2025-05-08 12:58:54', b'0');
|
|
|
|
-- ========================================
|
|
-- 示例数据
|
|
-- ========================================
|
|
|
|
-- 插入示例支付应用
|
|
INSERT INTO `pay_app` (`id`, `app_key`, `name`, `status`, `remark`, `order_notify_url`, `refund_notify_url`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) VALUES
|
|
(1, 'yanzhu-pay-app-key', '研筑支付应用', 0, '研筑科技官方支付应用', 'http://localhost:8080/admin-api/pay/order/notify', 'http://localhost:8080/admin-api/pay/refund/notify', '1', NOW(), '1', NOW(), b'0', 1);
|
|
|
|
-- 插入示例支付渠道
|
|
INSERT INTO `pay_channel` (`id`, `code`, `status`, `remark`, `fee_rate`, `app_id`, `config`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) VALUES
|
|
(1, 'mock', 0, '模拟支付渠道', 0, 1, '{}', '1', NOW(), '1', NOW(), b'0', 1);
|
|
|
|
-- 插入示例钱包充值套餐
|
|
INSERT INTO `pay_wallet_recharge_package` (`id`, `name`, `pay_price`, `bonus_price`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) VALUES
|
|
(1, '小额充值', 100, 0, 0, '1', NOW(), '1', NOW(), b'0', 1),
|
|
(2, '中额充值', 500, 50, 0, '1', NOW(), '1', NOW(), b'0', 1),
|
|
(3, '大额充值', 1000, 200, 0, '1', NOW(), '1', NOW(), b'0', 1);
|