const db = require("../db/db");
const { translateText } = require("../utils/translator");

const getAllRequests = () => {
  const sql = `SELECT * FROM requests`;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const addReq = async (Name, Rating, Feedback) => {
  const [Feedback_fr, Feedback_es, Feedback_gr] = await Promise.all([
    translateText(Feedback, "fr"),
    translateText(Feedback, "es"),
    translateText(Feedback, "de"),
  ]);
  const sql = `INSERT INTO requests ( Name, Rating, Feedback,Feedback_fr,Feedback_es,Feedback_gr) VALUES (?,?,?,?,?,?)`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [Name, Rating, Feedback, Feedback_fr, Feedback_es, Feedback_gr],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

const deleteReq = (idRequests) => {
  const sql = `DELETE FROM requests WHERE idRequests = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idRequests], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getByid = (idRequests) => {
  const sql = `SELECT * FROM requests WHERE idRequests = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idRequests], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const changeStatus = (idRequests) => {
  const sql = `UPDATE requests
SET Status = CASE WHEN Status = 0 THEN 1 ELSE 0 END WHERE idRequests = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idRequests], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getActiveRequests = (lang) => {
  let nameField = "Name";
  let feedbackField = "Feedback";

  // Set translated fields if language is supported
  if (["fr", "es", "gr"].includes(lang)) {
    nameField = `Name_${lang}`;
    feedbackField = `Feedback_${lang}`;
  }

  const sql = `
    SELECT 
      idRequests,
      Rating,
      Status,
      Name,
      COALESCE(${feedbackField}, Feedback) AS Feedback
    FROM requests
    WHERE Status = 1
  `;

  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

module.exports = {
  getAllRequests,
  addReq,
  deleteReq,
  getByid,
  changeStatus,
  getActiveRequests,
};
