53 lines
3.3 KiB
SQL
53 lines
3.3 KiB
SQL
CREATE TABLE IF NOT EXISTS `binance_recharge_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '系统来源',
|
||
`api_key` varchar(255) NOT NULL DEFAULT '' COMMENT 'Binance API Key',
|
||
`api_secret` varchar(255) NOT NULL DEFAULT '' COMMENT 'Binance API Secret',
|
||
`enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用自动充值验证',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_binance_recharge_config_origin` (`sys_origin`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='币安自动充值配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `binance_recharge_rate` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`config_id` bigint NOT NULL COMMENT '配置ID',
|
||
`min_amount` decimal(20,8) NOT NULL DEFAULT 0 COMMENT '最小USDT金额，包含',
|
||
`max_amount` decimal(20,8) NOT NULL DEFAULT 0 COMMENT '最大USDT金额，不包含；0表示不限制',
|
||
`gold_per_usd` decimal(20,8) NOT NULL DEFAULT 0 COMMENT '1 USDT 对应金币',
|
||
`sort_order` int NOT NULL DEFAULT 0,
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_binance_recharge_rate_config` (`config_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='币安自动充值金币区间';
|
||
|
||
CREATE TABLE IF NOT EXISTS `binance_recharge_verify_record` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '系统来源',
|
||
`dealer_user_id` bigint NOT NULL COMMENT '提交验证的币商用户ID',
|
||
`target_user_id` bigint NOT NULL COMMENT '发放用户ID',
|
||
`order_no` varchar(128) NOT NULL COMMENT '业务订单号',
|
||
`transfer_type` varchar(32) NOT NULL COMMENT 'BINANCE_PAY/CHAIN',
|
||
`bill_no` varchar(160) NOT NULL COMMENT '币安账单号/链上txId',
|
||
`amount_usdt` decimal(20,8) NOT NULL DEFAULT 0 COMMENT '验证USDT金额',
|
||
`gold_amount` bigint NOT NULL DEFAULT 0 COMMENT '发放金币',
|
||
`rate_gold_per_usd` decimal(20,8) NOT NULL DEFAULT 0 COMMENT '命中金币比例',
|
||
`binance_transaction_id` varchar(180) NOT NULL DEFAULT '' COMMENT '币安交易ID',
|
||
`binance_order_type` varchar(64) NOT NULL DEFAULT '' COMMENT '币安交易类型',
|
||
`binance_raw_json` text COMMENT '币安原始记录',
|
||
`status` varchar(24) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SUCCESS/FAILED',
|
||
`fail_reason` varchar(500) NOT NULL DEFAULT '',
|
||
`remark` varchar(500) NOT NULL DEFAULT '',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_binance_recharge_verify_order` (`sys_origin`, `order_no`),
|
||
UNIQUE KEY `uk_binance_recharge_verify_bill` (`sys_origin`, `transfer_type`, `bill_no`),
|
||
KEY `idx_binance_recharge_verify_origin` (`sys_origin`),
|
||
KEY `idx_binance_recharge_verify_dealer` (`dealer_user_id`),
|
||
KEY `idx_binance_recharge_verify_target` (`target_user_id`),
|
||
KEY `idx_binance_recharge_verify_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='币安自动充值验证记录';
|