kknongchang_2506/app/Module/Blockchain/Database/blockchain.sql

-- 区块链钱包表
CREATE TABLE IF NOT EXISTS `blockchain_wallets` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `address` varchar(42) NOT NULL COMMENT '钱包地址',
    `type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '钱包类型：1=bnb, 2=usdt, 3=uraus',
    `balance` decimal(65,18) NOT NULL DEFAULT '0' COMMENT '余额',
    `balance_updated_at` timestamp NULL DEFAULT NULL COMMENT '余额最后更新时间',
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1=启用，0=禁用',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `address_type_unique` (`address`,`type`),
    KEY `address_index` (`address`),
    KEY `type_index` (`type`),
    KEY `status_index` (`status`),
    KEY `balance_updated_at_index` (`balance_updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区块链钱包表';

-- 区块链交易记录表
CREATE TABLE IF NOT EXISTS `blockchain_transactions` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `tx_hash` varchar(66) NOT NULL COMMENT '交易哈希',
    `from_address` varchar(42) NOT NULL COMMENT '发送地址',
    `to_address` varchar(42) NOT NULL COMMENT '接收地址',
    `amount` decimal(65,18) NOT NULL COMMENT '转账金额',
    `token_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '代币类型：1=bnb, 2=usdt, 3=uraus',
    `gas_price` decimal(65,18) NOT NULL COMMENT '矿工费单价(GWEI)',
    `gas_used` bigint(20) NOT NULL COMMENT '消耗的Gas数量',
    `gas_fee` decimal(65,18) NOT NULL COMMENT '矿工费总额(BNB)',
    `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态：0=待确认，1=成功，2=失败',
    `block_number` bigint(20) DEFAULT NULL COMMENT '区块号',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `tx_hash_unique` (`tx_hash`),
    KEY `from_address_index` (`from_address`),
    KEY `to_address_index` (`to_address`),
    KEY `token_type_index` (`token_type`),
    KEY `status_index` (`status`),
    KEY `block_number_index` (`block_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区块链交易记录表';

-- 钱包余额变更记录表
CREATE TABLE IF NOT EXISTS `blockchain_wallet_balance_logs` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `wallet_id` bigint(20) unsigned NOT NULL COMMENT '钱包ID',
    `old_balance` decimal(65,18) NOT NULL COMMENT '原余额',
    `new_balance` decimal(65,18) NOT NULL COMMENT '新余额',
    `change_type` tinyint(1) NOT NULL COMMENT '变更类型：1=交易,2=同步,3=手动修改',
    `sync_block_number` bigint(20) DEFAULT NULL COMMENT '同步时的区块号',
    `sync_tx_hash` varchar(66) DEFAULT NULL COMMENT '同步时的交易哈希',
    `sync_timestamp` bigint(20) DEFAULT NULL COMMENT '同步时的区块时间戳',
    `sync_data` json DEFAULT NULL COMMENT '同步的原始数据',
    `created_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `wallet_id_index` (`wallet_id`),
    KEY `created_at_index` (`created_at`),
    KEY `sync_block_number_index` (`sync_block_number`),
    KEY `sync_tx_hash_index` (`sync_tx_hash`),
    CONSTRAINT `blockchain_wallet_balance_logs_wallet_id_foreign` FOREIGN KEY (`wallet_id`) REFERENCES `blockchain_wallets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包余额变更记录表';