const config = require("../config/config");
const mysql = require("mysql2"); // Use mysql2 for connection pooling

// Create a connection pool
const pool = mysql.createPool({
  host: config.connection.host,
  user: config.connection.user,
  password: config.connection.password,
  database: config.connection.database,
  waitForConnections: true,
  connectionLimit: 10, // Adjust as needed
  queueLimit: 0, // Unlimited queue size
});

module.exports = {
  findAllScores: async () => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query("SELECT * FROM score", (error, result) => {
          connection.release();

          if (error) {
            return reject(error);
          }
          resolve(result);
        });
      });
    });
  },

  createScore: async (score) => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query("INSERT INTO score SET ?", score, (error, result) => {
          connection.release();

          if (error) {
            return reject(error);
          }
          resolve(result);
        });
      });
    });
  },
  findScoreById: async (id) => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query(
          "SELECT * FROM score where id=?",
          id,
          (error, result) => {
            connection.release();

            if (error) {
              return reject(error);
            }
            resolve(result[0]);
          }
        );
      });
    });
  },

  findScoreByName: async (name) => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query(
          "SELECT * FROM score where name=?",
          name,
          (error, result) => {
            connection.release();

            if (error) {
              return reject(error);
            }
            resolve(result[0]);
          }
        );
      });
    });
  },

  updateScoreList: async (score) => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }
        const query =
          "UPDATE score SET name =?, value=?, updatedAt = ?, updated_by =? WHERE id = ?";
        const values = [
          score.name,
          score.value,
          score.updated_at,
          score.updated_by,
          score.id,
        ];
        connection.query(query, values, (error, result) => {
          connection.release();

          if (error) {
            return reject(error);
          }
          resolve(result);
        });
      });
    });
  },
};