const mysql = require('mysql2/promise');
const config = require('../../config/env');

// 数据库配置 - 使用env.js中的mysql配置
const dbConfig = {
  ...config.mysql,
  // 连接池配置
  waitForConnections: true,
  queueLimit: 0,
  // 超时配置
  connectTimeout: 10000 // 10秒连接超时
};

// 创建连接池
const pool = mysql.createPool(dbConfig);

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ MySQL数据库连接成功');
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ MySQL数据库连接失败:', error.message);
    console.error('🔍 错误代码:', error.code);
    console.error('📌 数据库配置:', {
      host: dbConfig.host,
      port: dbConfig.port,
      database: dbConfig.database,
      user: dbConfig.user
    });
    
    if (error.code === 'ECONNREFUSED') {
      console.error('💡 可能原因: MySQL服务器未启动或网络不可达');
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      console.error('💡 可能原因: 用户名或密码错误');
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      console.error('💡 可能原因: 数据库不存在');
    }
    
    return false;
  }
}

// 执行查询
const query = async (sql, params = []) => {
  let connection;
  try {
    connection = await pool.getConnection();
    const [results] = await connection.execute(sql, params);
    connection.release();
    return results;
  } catch (error) {
    if (connection) {
      connection.release();
    }
    throw error;
  }
};

// 事务处理
const transaction = async (callback) => {
  let connection;
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();
    
    const result = await callback(connection);
    
    await connection.commit();
    connection.release();
    return result;
  } catch (error) {
    if (connection) {
      await connection.rollback();
      connection.release();
    }
    throw error;
  }
};

// 关闭连接池
async function closePool() {
  try {
    await pool.end();
    console.log('✅ MySQL连接池已关闭');
  } catch (error) {
    console.error('❌ 关闭MySQL连接池时出错:', error.message);
  }
}

// 管理员数据库操作
const adminDB = {
  // 根据用户名查找管理员
  findByUsername: async (username) => {
    const sql = 'SELECT * FROM admins WHERE username = ?';
    const results = await query(sql, [username]);
    return results[0];
  },
  
  // 根据ID查找管理员
  findById: async (id) => {
    const sql = 'SELECT * FROM admins WHERE id = ?';
    const results = await query(sql, [id]);
    return results[0];
  },
  
  // 创建管理员
  create: async (adminData) => {
    const keys = Object.keys(adminData);
    const values = Object.values(adminData);
    const placeholders = keys.map(() => '?').join(', ');
    const sql = `INSERT INTO admins (${keys.join(', ')}) VALUES (${placeholders})`;
    const result = await query(sql, values);
    return result.insertId;
  },
  
  // 更新管理员最后登录时间
  updateLastLogin: async (id) => {
    const sql = 'UPDATE admins SET last_login = CURRENT_TIMESTAMP WHERE id = ?';
    await query(sql, [id]);
  },
  
  // 更新管理员信息
  update: async (id, adminData) => {
    const keys = Object.keys(adminData);
    const values = Object.values(adminData);
    const setClause = keys.map(key => `${key} = ?`).join(', ');
    const sql = `UPDATE admins SET ${setClause} WHERE id = ?`;
    await query(sql, [...values, id]);
  },
  
  // 删除管理员
  delete: async (id) => {
    const sql = 'DELETE FROM admins WHERE id = ?';
    await query(sql, [id]);
  }
};

module.exports = {
  pool,
  query,
  transaction,
  testConnection,
  closePool,
  adminDB
};