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 = {
  findAllCourses: async () => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

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

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

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

        connection.query(
          "SELECT * FROM course_evaluation where userId=? AND status = 'Pending'",
          id,
          (error, result) => {
            connection.release();

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

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

        connection.query(
          "SELECT * FROM course_evaluation where userId=? AND sessionId=? AND status = 'Pending'", // Change status to Evaluated
          [data.userId, data.sessionId],
          (error, result) => {
            connection.release();

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

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

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

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

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

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

  findUserCourseForCompilation(data) {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query(
          "SELECT * FROM course_evaluation where userId =? and sessionId=? and self_submite_yn='Y'",
          [data.userId, data.sessionId],
          (error, result) => {
            connection.release();

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

  findHodCourseForCompilation(data) {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }

        connection.query(
          "SELECT * FROM course_evaluation where userId =? and sessionId=? and status = 'Pending' and self_submite_yn='Y'",
          [data.userId, data.sessionId],
          (error, result) => {
            connection.release();

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

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

        const query = `
        UPDATE course_evaluation SET
          course_class_ontime_h =?,
          course_consultation_time_h=?,
          course_hosted_guest_h=?,
          course_instructional_tech_h=?,
          course_appropriate_design_h=?,
          course_multimedia_type_h=?,
          course_quality_instructional_h=?,
          course_updated_record_h=?,
          course_draft_exam_h=?,
          course_standard_exam_h=?,
          course_administer_exams_h=?,
          course_mark_script_h=?,
          course_process_grades_h=?,
          course_attend_school_meetings_h=?,
          course_support_school_activity_h=?,
          updatedAt=?,
          updated_by=?,
          hod_submited_yn =?
          WHERE id = ?`;
        const values = [
          updateCourse.course_class_ontime_h,
          updateCourse.course_consultation_time_h,
          updateCourse.course_hosted_guest_h,
          updateCourse.course_instructional_tech_h,
          updateCourse.course_appropriate_design_h,
          updateCourse.course_multimedia_type_h,
          updateCourse.course_quality_instructional_h,
          updateCourse.course_updated_record_h,
          updateCourse.course_draft_exam_h,
          updateCourse.course_standard_exam_h,
          updateCourse.course_administer_exams_h,
          updateCourse.course_mark_script_h,
          updateCourse.course_process_grades_h,
          updateCourse.course_attend_school_meetings_h,
          updateCourse.course_support_school_activity_h,
          updateCourse.updatedAt,
          updateCourse.updated_by,
          updateCourse.hod_submited_yn,
          updateCourse.id,
        ];
        connection.query(query, values, (error, result) => {
          connection.release();

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