const mysql = require('mysql2/promise')

const config = {
  host: '110.42.255.239',
  port: 3306,
  user: 'admin',
  password: 'Admin@123',
  database: 'miniprogram',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
}

const pool = mysql.createPool(config)

async function query(sql, params = []) {
  const connection = await pool.getConnection()
  try {
    const [rows, fields] = await connection.execute(sql, params)
    return rows
  } finally {
    connection.release()
  }
}

async function initDatabase() {
  try {
    const connection = await mysql.createConnection({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      multipleStatements: true
    })
    
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${config.database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`)
    await connection.query(`USE ${config.database}`)
    
    const fs = require('fs')
    const path = require('path')
    const schema = fs.readFileSync(path.join(__dirname, 'schema.sql'), 'utf8')
    const statements = schema.split(';').filter(s => s.trim())
    
    for (const statement of statements) {
      await connection.query(statement)
    }
    
    await connection.end()
    console.log('Database initialized successfully')
  } catch (error) {
    console.error('Failed to initialize database:', error)
    throw error
  }
}

module.exports = {
  pool,
  query,
  initDatabase,
  config
}