import mysql from 'mysql2/promise';
import fs from 'fs';
import path from 'path';
import dotenv from 'dotenv';

dotenv.config();

async function initDatabase() {
  try {
    console.log('开始初始化数据库...\n');
    
    // 连接到 MySQL
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: Number(process.env.DB_PORT) || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      multipleStatements: true, // 允许执行多条语句
    });
    
    console.log('✓ 已连接到 MySQL 服务器');
    
    // 创建数据库
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME || 'employee_performance'} DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci`);
    console.log('✓ 数据库已创建');
    
    // 切换到目标数据库
    await connection.query(`USE ${process.env.DB_NAME || 'employee_performance'}`);
    
    // 读取并执行初始化脚本
    const initSql = fs.readFileSync(path.join(__dirname, 'src/db/init.sql'), 'utf-8');
    
    // 移除 CREATE DATABASE 和 USE 语句
    const cleanedSql = initSql
      .split('\n')
      .filter(line => !line.trim().startsWith('CREATE DATABASE') && !line.trim().startsWith('USE '))
      .join('\n');
    
    await connection.query(cleanedSql);
    console.log('✓ 表结构已创建');
    
    // 读取并执行种子数据脚本
    const seedSql = fs.readFileSync(path.join(__dirname, 'src/db/seed.sql'), 'utf-8');
    const cleanedSeedSql = seedSql
      .split('\n')
      .filter(line => !line.trim().startsWith('USE '))
      .join('\n');
    
    await connection.query(cleanedSeedSql);
    console.log('✓ 测试数据已插入');
    
    // 验证表是否创建成功
    const [tables] = await connection.query('SHOW TABLES');
    console.log('\n创建的表:', tables);
    
    await connection.end();
    
    console.log('\n✅ 数据库初始化完成！');
    console.log('\n测试账号信息：');
    console.log('总经理: gm001 / 123456');
    console.log('技术部经理: mgr001 / 123456');
    console.log('销售部经理: mgr002 / 123456');
    console.log('技术部员工: emp001, emp002, emp003 / 123456');
    console.log('销售部员工: emp004, emp005 / 123456');
    
    process.exit(0);
  } catch (error: any) {
    console.error('\n❌ 数据库初始化失败:', error.message);
    console.error(error);
    process.exit(1);
  }
}

initDatabase();