const mysql = require('mysql2');
require('dotenv').config();

const TRANSIENT_DB_ERRORS = new Set([
  'ECONNRESET',
  'PROTOCOL_CONNECTION_LOST',
  'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR',
  'ETIMEDOUT',
  'EPIPE'
]);

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: Number(process.env.DB_CONNECTION_LIMIT || 10),
  queueLimit: 0,
  connectTimeout: Number(process.env.DB_CONNECT_TIMEOUT || 10000),
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

const promisePool = pool.promise();

const wait = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

const runWithRetry = async (operation, sql, params, maxRetries = 1) => {
  let attempt = 0;

  while (attempt <= maxRetries) {
    try {
      return await operation(sql, params);
    } catch (error) {
      const isTransient = TRANSIENT_DB_ERRORS.has(error.code);
      if (!isTransient || attempt === maxRetries) {
        throw error;
      }

      attempt += 1;
      await wait(150 * attempt);
    }
  }
};

const db = {
  query(sql, params) {
    return runWithRetry((q, p) => promisePool.query(q, p), sql, params);
  },

  execute(sql, params) {
    return runWithRetry((q, p) => promisePool.execute(q, p), sql, params);
  },

  getConnection() {
    return promisePool.getConnection();
  },

  end() {
    return promisePool.end();
  }
};

module.exports = db;
