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='币安自动充值验证记录';