bot/admin/config/initDb.js

const fs = require('fs');
const path = require('path');
const {
    pool
} = require('./database');
const bcrypt = require('bcryptjs');
const User = require('../models/User');

async function initDatabase() {
    try {
        // 读取并执行迁移文件
        const migrationsDir = path.join(__dirname, 'migrations');
        const migrationFiles = fs.readdirSync(migrationsDir).sort();

        for (const file of migrationFiles) {
            if (file.endsWith('.sql')) {
                const migrationPath = path.join(migrationsDir, file);
                const migrationSQL = fs.readFileSync(migrationPath, 'utf8');

                console.log(`执行数据库迁移: ${file}`);
                await pool.query(migrationSQL);
            }
        }

        // 创建数据库
        await pool.query('CREATE DATABASE IF NOT EXISTS notebot');
        await pool.query('USE notebot');

        // 创建用户表
        await pool.query(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                role ENUM('admin', 'user') DEFAULT 'user',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 创建群组表
        await pool.query(`
            CREATE TABLE IF NOT EXISTS groups (
                id INT AUTO_INCREMENT PRIMARY KEY,
                group_id VARCHAR(255) NOT NULL UNIQUE,
                group_name VARCHAR(255) NOT NULL,
                group_type ENUM('personal', 'group', 'supergroup') NOT NULL DEFAULT 'group' COMMENT '群组类型：个人用户/私聊群组/超级群组',
                in_fee_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '入款费率',
                in_exchange_rate DECIMAL(10,4) DEFAULT 1.0000 COMMENT '入款汇率',
                out_fee_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '出款费率',
                out_exchange_rate DECIMAL(10,4) DEFAULT 1.0000 COMMENT '出款汇率',
                admin_id INT NOT NULL COMMENT '管理员ID',
                creator_id VARCHAR(255) NOT NULL,
                is_active BOOLEAN DEFAULT true,
                last_join_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '机器人最后加入时间',
                last_leave_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '机器人最后离开时间',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间'
            )
        `);

        // 创建入款出款记录表
        await pool.query(`
            CREATE TABLE IF NOT EXISTS transactions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                amount DECIMAL(10,2) NOT NULL COMMENT '金额',
                operator_id INT NOT NULL COMMENT '操作人ID',
                responder_id INT NOT NULL COMMENT '回复人ID',
                group_id VARCHAR(50) NOT NULL COMMENT '关联群组ID',
                type ENUM('deposit', 'withdrawal') NOT NULL COMMENT '类型：入款/出款',
                status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' COMMENT '状态',
                fee_rate DECIMAL(5,2) DEFAULT NULL COMMENT '费率',
                exchange_rate DECIMAL(10,4) DEFAULT NULL COMMENT '汇率',
                FOREIGN KEY (operator_id) REFERENCES users(id),
                FOREIGN KEY (responder_id) REFERENCES users(id),
                FOREIGN KEY (group_id) REFERENCES groups(group_id),
                INDEX idx_group_time (group_id, time)
            )
        `);

        // 检查是否存在管理员用户
        const adminUser = await User.findByUsername('admin');

        if (!adminUser) {
            console.log('创建默认管理员用户...');
            // 创建默认管理员用户
            const adminData = {
                username: 'admin',
                password: 'admin123', // 默认密码
                role: 'admin'
            };

            await User.create(adminData);
            console.log('默认管理员用户创建成功');
        } else {
            console.log('管理员用户已存在');
        }

        console.log('数据库初始化完成');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error;
    }
}

module.exports = initDatabase;