bot/admin/config/initDb.js

const { pool } = require('./database');
const bcrypt = require('bcryptjs');

const initDatabase = async () => {
    try {
        // 创建数据库
        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', 'public') NOT NULL,
                creator_id VARCHAR(255) NOT NULL,
                is_active BOOLEAN DEFAULT true,
                last_join_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        `);

        // 修改群组表，添加新字段
        try {
            await pool.query(`
                ALTER TABLE groups
                ADD COLUMN group_type ENUM('personal', 'public', 'private') NOT NULL DEFAULT 'public' COMMENT '群组类型：个人用户/公开群聊/隐私群聊' AFTER group_name,
                ADD COLUMN fee_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '费率' AFTER group_type,
                ADD COLUMN exchange_rate DECIMAL(10,4) DEFAULT 1.0000 COMMENT '汇率' AFTER fee_rate,
                ADD COLUMN admin_id INT NOT NULL COMMENT '管理员ID' AFTER exchange_rate,
                ADD COLUMN last_join_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '机器人最后加入时间' AFTER is_active
            `);
        } catch (error) {
            // 如果列已存在，忽略错误
            if (!error.message.includes('Duplicate column name')) {
                throw error;
            }
        }

        // 添加外键约束
        // try {
        //     await pool.query(`
        //         ALTER TABLE groups
        //         ADD CONSTRAINT fk_groups_creator 
        //         FOREIGN KEY (creator_id) REFERENCES users(id),
        //         ADD CONSTRAINT fk_groups_admin 
        //         FOREIGN KEY (admin_id) REFERENCES users(id)
        //     `);
        // } catch (error) {
        //     // 如果约束已存在，忽略错误
        //     if (!error.message.includes('Duplicate key name')) {
        //         throw error;
        //     }
        // }

        // 创建入款出款记录表
        await pool.query(`
            CREATE TABLE IF NOT EXISTS money_records (
                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 '状态',
                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)
            )
        `);

        // // 创建交易表
        // await pool.query(`
        //     CREATE TABLE IF NOT EXISTS transactions (
        //         id INT AUTO_INCREMENT PRIMARY KEY,
        //         group_id VARCHAR(50) NOT NULL,
        //         group_name VARCHAR(100) NOT NULL,
        //         type ENUM('deposit', 'withdrawal') NOT NULL,
        //         amount DECIMAL(10,2) NOT NULL,
        //         time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        //         INDEX idx_group_time (group_id, time)
        //     )
        // `);

        // 创建默认管理员账户
        const adminPassword = await bcrypt.hash('admin123', 10);
        await pool.query(`
            INSERT INTO users (username, password, role) 
            VALUES ('admin', ?, 'admin')
            ON DUPLICATE KEY UPDATE id=id
        `, [adminPassword]);

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

module.exports = initDatabase;