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

let pool;

async function initDB() {
  if (!pool) {
    try {
      pool = mysql.createPool({
        host: config.DB_HOST,
        user: config.DB_USER,
        password: config.DB_PASS,
        database: config.DB_NAME,
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0
      });
      // Test connection
      await pool.query('SELECT 1');
    } catch (err) {
      console.error('Database connection failed:', err);
      // Fallback: try to create database if not exists
      try {
        const tempPool = mysql.createPool({
          host: config.DB_HOST,
          user: config.DB_USER,
          password: config.DB_PASS,
        });
        await tempPool.query(`CREATE DATABASE IF NOT EXISTS \`${config.DB_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
        await tempPool.end();
        
        pool = mysql.createPool({
          host: config.DB_HOST,
          user: config.DB_USER,
          password: config.DB_PASS,
          database: config.DB_NAME,
        });
      } catch (e) {
        console.error('Could not create database:', e);
      }
    }
  }
}

async function getDB() {
  if (!pool) await initDB();
  return pool;
}

module.exports = {
  getDB,
  query: async (sql, params) => {
    const db = await getDB();
    const [rows, fields] = await db.query(sql, params);
    return rows;
  },
  fetch: async (sql, params) => {
    const db = await getDB();
    const [rows, fields] = await db.query(sql, params);
    return rows.length > 0 ? rows[0] : null;
  },
  insert: async (sql, params) => {
    const db = await getDB();
    const [result] = await db.query(sql, params);
    return result.insertId;
  }
};
