224 lines
12 KiB
SQL
224 lines
12 KiB
SQL
-- pay-bridge 扩展功能表
|
||
-- 分润、服务费、收款匹配、商户进件、微信通知、对账
|
||
|
||
USE pay_bridge;
|
||
|
||
-- 分润配置
|
||
CREATE TABLE IF NOT EXISTS `profit_sharing_config` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '应用ID',
|
||
`channel_code` VARCHAR(32) NOT NULL COMMENT '渠道编码',
|
||
`receiver_merchant_id` VARCHAR(64) NOT NULL COMMENT '收款商户ID（渠道侧）',
|
||
`max_sharing_ratio` DECIMAL(5,4) NOT NULL DEFAULT 0.3000 COMMENT '最大分润比例，如0.3=30%',
|
||
`auto_sharing` TINYINT NOT NULL DEFAULT 0 COMMENT '1=支付后自动分润',
|
||
`status` TINYINT NOT NULL DEFAULT 1,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_channel` (`app_id`, `channel_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分润配置';
|
||
|
||
-- 分润订单
|
||
CREATE TABLE IF NOT EXISTS `profit_sharing_order` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`sharing_no` VARCHAR(32) NOT NULL COMMENT 'pay-bridge分润单号',
|
||
`trade_no` VARCHAR(32) NOT NULL COMMENT '关联交易号',
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`channel_sharing_no` VARCHAR(64) DEFAULT NULL COMMENT '渠道分润单号',
|
||
`amount` BIGINT NOT NULL COMMENT '分润金额（分）',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`fail_reason` VARCHAR(256) DEFAULT NULL,
|
||
`finished_at` DATETIME(3) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_sharing_no` (`sharing_no`),
|
||
UNIQUE KEY `uk_trade_no` (`trade_no`),
|
||
KEY `idx_app_status` (`app_id`, `status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分润订单';
|
||
|
||
-- 分润流水
|
||
CREATE TABLE IF NOT EXISTS `profit_sharing_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`sharing_no` VARCHAR(32) NOT NULL,
|
||
`trade_no` VARCHAR(32) NOT NULL,
|
||
`action` VARCHAR(20) NOT NULL COMMENT 'SPLIT/ROLLBACK',
|
||
`amount` BIGINT NOT NULL,
|
||
`remark` VARCHAR(256) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sharing_no` (`sharing_no`),
|
||
KEY `idx_trade_no` (`trade_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分润流水';
|
||
|
||
-- 服务费配置
|
||
CREATE TABLE IF NOT EXISTS `service_fee_config` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '应用ID',
|
||
`pay_method_group` VARCHAR(32) NOT NULL COMMENT '支付方式分组：WECHAT/ALIPAY/BANK',
|
||
`fee_type` VARCHAR(20) NOT NULL DEFAULT 'PERCENTAGE' COMMENT 'PERCENTAGE/FIXED',
|
||
`fee_rate` DECIMAL(6,5) NOT NULL DEFAULT 0 COMMENT '费率，如0.006=0.6%',
|
||
`fee_fixed` BIGINT NOT NULL DEFAULT 0 COMMENT '固定费用（分）',
|
||
`min_fee` BIGINT NOT NULL DEFAULT 0 COMMENT '最低费用（分）',
|
||
`max_fee` BIGINT NOT NULL DEFAULT 0 COMMENT '最高费用，0=不限',
|
||
`receiver_merchant_id` VARCHAR(64) NOT NULL COMMENT '收款商户ID',
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`status` TINYINT NOT NULL DEFAULT 1,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_method_group` (`app_id`, `pay_method_group`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务费配置';
|
||
|
||
-- 服务费流水
|
||
CREATE TABLE IF NOT EXISTS `service_fee_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`trade_no` VARCHAR(32) NOT NULL,
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`action` VARCHAR(20) NOT NULL COMMENT 'CHARGE/ROLLBACK',
|
||
`fee_amount` BIGINT NOT NULL,
|
||
`remark` VARCHAR(256) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_trade_action` (`trade_no`, `action`),
|
||
KEY `idx_app` (`app_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务费流水';
|
||
|
||
-- 子商户收款账户（固定账户收款）
|
||
CREATE TABLE IF NOT EXISTS `sub_merchant_account` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`sub_merchant_id` VARCHAR(64) NOT NULL,
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`account_type` VARCHAR(20) NOT NULL COMMENT 'BANK_CARD',
|
||
`account_no` VARCHAR(64) NOT NULL COMMENT '脱敏账号，后4位',
|
||
`account_no_enc` TEXT DEFAULT NULL COMMENT 'AES加密完整账号',
|
||
`account_name` VARCHAR(128) NOT NULL COMMENT '账户名称',
|
||
`bank_name` VARCHAR(64) DEFAULT NULL,
|
||
`status` TINYINT NOT NULL DEFAULT 1,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_app_merchant` (`app_id`, `sub_merchant_id`),
|
||
KEY `idx_account_no` (`account_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='子商户收款账户';
|
||
|
||
-- 收款匹配日志
|
||
CREATE TABLE IF NOT EXISTS `payment_match_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`account_id` BIGINT UNSIGNED NOT NULL COMMENT '关联账户ID',
|
||
`trade_no` VARCHAR(32) DEFAULT NULL COMMENT '匹配到的交易号',
|
||
`incoming_amount` BIGINT NOT NULL COMMENT '入账金额（分）',
|
||
`incoming_remark` VARCHAR(256) DEFAULT NULL COMMENT '转账备注',
|
||
`payer_name` VARCHAR(128) DEFAULT NULL COMMENT '付款方名称',
|
||
`channel_bill_no` VARCHAR(64) NOT NULL COMMENT '渠道流水号',
|
||
`match_status` VARCHAR(20) NOT NULL DEFAULT 'PENDING_MANUAL',
|
||
`name_diff` TINYINT NOT NULL DEFAULT 0 COMMENT '1=名称不一致',
|
||
`match_time` DATETIME(3) DEFAULT NULL,
|
||
`operator` VARCHAR(64) DEFAULT NULL COMMENT '人工操作者',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_channel_bill_no` (`channel_bill_no`),
|
||
KEY `idx_account_status` (`account_id`, `match_status`),
|
||
KEY `idx_trade_no` (`trade_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收款匹配日志';
|
||
|
||
-- 商户信息
|
||
CREATE TABLE IF NOT EXISTS `merchant` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`merchant_id` VARCHAR(32) NOT NULL COMMENT '商户ID',
|
||
`merchant_name` VARCHAR(128) NOT NULL COMMENT '商户名称',
|
||
`license_no` VARCHAR(64) DEFAULT NULL COMMENT '营业执照号',
|
||
`legal_person` VARCHAR(64) DEFAULT NULL COMMENT '法人姓名',
|
||
`bank_account` VARCHAR(64) DEFAULT NULL COMMENT '银行账号（脱敏）',
|
||
`channel_merchant_id` VARCHAR(64) DEFAULT NULL COMMENT '渠道商户ID',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_merchant_id` (`merchant_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户信息';
|
||
|
||
-- 商户进件申请
|
||
CREATE TABLE IF NOT EXISTS `merchant_application` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`application_id` VARCHAR(32) NOT NULL COMMENT '申请ID',
|
||
`merchant_id` VARCHAR(32) NOT NULL COMMENT '商户ID',
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`submit_data` JSON DEFAULT NULL COMMENT '提交的进件数据',
|
||
`audit_status` VARCHAR(20) NOT NULL DEFAULT 'SUBMITTING',
|
||
`reject_reason` VARCHAR(512) DEFAULT NULL,
|
||
`submitted_at` DATETIME(3) DEFAULT NULL,
|
||
`audited_at` DATETIME(3) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_application_id` (`application_id`),
|
||
KEY `idx_merchant_id` (`merchant_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户进件申请';
|
||
|
||
-- 微信公众号绑定
|
||
CREATE TABLE IF NOT EXISTS `wechat_binding` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '平台应用ID',
|
||
`wx_app_id` VARCHAR(32) NOT NULL COMMENT '微信公众号/小程序AppID',
|
||
`wx_secret` TEXT NOT NULL COMMENT '微信AppSecret（AES加密）',
|
||
`template_id` VARCHAR(64) NOT NULL COMMENT '消息模板ID',
|
||
`status` TINYINT NOT NULL DEFAULT 1,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_id` (`app_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信公众号绑定';
|
||
|
||
-- 微信消息发送日志
|
||
CREATE TABLE IF NOT EXISTS `wechat_message_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`trade_no` VARCHAR(32) DEFAULT NULL,
|
||
`open_id` VARCHAR(64) NOT NULL,
|
||
`template_id` VARCHAR(64) NOT NULL,
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`err_msg` VARCHAR(256) DEFAULT NULL,
|
||
`sent_at` DATETIME(3) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_app_id` (`app_id`),
|
||
KEY `idx_trade_no` (`trade_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信消息发送日志';
|
||
|
||
-- 对账报告
|
||
CREATE TABLE IF NOT EXISTS `reconciliation_report` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`bill_date` VARCHAR(10) NOT NULL COMMENT '账单日期 yyyy-MM-dd',
|
||
`total_count` INT NOT NULL DEFAULT 0 COMMENT '渠道账单总笔数',
|
||
`total_amount` BIGINT NOT NULL DEFAULT 0 COMMENT '渠道账单总金额',
|
||
`matched_count` INT NOT NULL DEFAULT 0 COMMENT '匹配成功笔数',
|
||
`exception_count` INT NOT NULL DEFAULT 0 COMMENT '异常笔数',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_app_date` (`app_id`, `bill_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账报告';
|
||
|
||
-- 对账异常明细
|
||
CREATE TABLE IF NOT EXISTS `reconciliation_exception` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`report_id` BIGINT UNSIGNED NOT NULL COMMENT '关联对账报告ID',
|
||
`trade_no` VARCHAR(32) DEFAULT NULL,
|
||
`channel_bill_no` VARCHAR(64) DEFAULT NULL,
|
||
`exception_type` VARCHAR(32) NOT NULL COMMENT 'MISSING_LOCAL/MISSING_CHANNEL/AMOUNT_MISMATCH',
|
||
`local_amount` BIGINT NOT NULL DEFAULT 0,
|
||
`channel_amount` BIGINT NOT NULL DEFAULT 0,
|
||
`remark` VARCHAR(256) DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_report_id` (`report_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账异常明细';
|