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 '群组类型：个人用户/私聊群组/超级群组',
                creator_id VARCHAR(255) NOT NULL,
                is_active BOOLEAN DEFAULT true,
                last_join_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_leave_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
                MODIFY COLUMN group_type ENUM('personal', 'group', 'supergroup') NOT NULL DEFAULT 'group' COMMENT '群组类型：个人用户/私聊群组/超级群组',
                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,
                ADD COLUMN last_leave_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '机器人最后离开时间' AFTER last_join_time,
                ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间' AFTER last_leave_time
            `);
        } 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 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;