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

// ADD (already done)
const add = async (title, description, Image, Image2) => {
  const [
    title_fr,
    title_es,
    title_gr,
    description_fr,
    description_es,
    description_gr,
  ] = await Promise.all([
    translateText(title, "fr"),
    translateText(title, "es"),
    translateText(title, "de"),
    translateText(description, "fr"),
    translateText(description, "es"),
    translateText(description, "de"),
  ]);
  const sql = `INSERT INTO package_category (title, description,Image,Hero,title_fr,title_es,title_gr,description_fr,description_es,description_gr)
   VALUES (?,?,?,?,?,?,?,?,?,?)`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        title,
        description,
        Image,
        Image2,
        title_fr,
        title_es,
        title_gr,
        description_fr,
        description_es,
        description_gr,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

// UPDATE
const update = async (idPackageCategory, title, description, Image, Image2) => {
  const [
    title_fr,
    title_es,
    title_gr,
    description_fr,
    description_es,
    description_gr,
  ] = await Promise.all([
    translateText(title, "fr"),
    translateText(title, "es"),
    translateText(title, "de"),
    translateText(description, "fr"),
    translateText(description, "es"),
    translateText(description, "de"),
  ]);

  const sql = `
    UPDATE package_category 
    SET title = ?, title_fr = ?, title_es = ?, title_gr = ?, 
        description = ?, description_fr = ?, description_es = ?, description_gr = ?, 
        Image = ? , Image2 = ?
    WHERE idPackageCategory = ?
  `;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        title,
        title_fr,
        title_es,
        title_gr,
        description,
        description_fr,
        description_es,
        description_gr,
        Image,
        Image2,
        idPackageCategory,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

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

// GET BY ID
const getById = (idPackageCategory) => {
  const sql = `SELECT * FROM package_category WHERE idPackageCategory = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idPackageCategory], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results); // return single result
      }
    });
  });
};

// GET ALL
const getAll = () => {
  const sql = `SELECT * FROM package_category`;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getPackageCategoryByLang = (lang) => {
  let titleField = "title";
  let desField = "description";

  if (["fr", "es", "gr"].includes(lang)) {
    titleField = `title_${lang}`;
    desField = `description_${lang}`;
  }

  const sql = `SELECT  title,COALESCE(${desField}) AS description,idPackageCategory,Image,Hero FROM package_category`;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getPackageCategoryByLangID = (lang, idPackageCategory) => {
  let titleField = "title";
  let desField = "description";

  if (["fr", "es", "gr"].includes(lang)) {
    titleField = `title_${lang}`;
    desField = `description_${lang}`;
  }

  const sql = `SELECT  title,COALESCE(${desField}) AS description,idPackageCategory,Image,Hero FROM package_category WHERE idPackageCategory = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idPackageCategory], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

// const getPackageCategoryByLang = (lang) => {
//   let titleField = "title";
//   let desField = "description";

//   if (["fr", "es", "gr"].includes(lang)) {
//     titleField = `title_${lang}`;
//     desField = `description_${lang}`;
//   }

//   const sql = `SELECT COALESCE(${titleField}) AS title,COALESCE(${desField}) AS description,idPackageCategory,Image FROM package_category`;
//   return new Promise((resolve, reject) => {
//     db.query(sql, (err, results) => {
//       if (err) {
//         reject(err);
//       } else {
//         resolve(results);
//       }
//     });
//   });
// };

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

const getAllPackagesByPackageCategoryId = (
  PackageCategory_idPackageCategory
) => {
  const sql = `SELECT * FROM packages WHERE PackageCategory_idPackageCategory = ? AND Status = 1`;
  return new Promise((resolve, reject) => {
    db.query(sql, [PackageCategory_idPackageCategory], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const updateWithoutImage = async (id, title, description) => {
  // Translate fields
  const [
    title_fr,
    title_es,
    title_gr,
    description_fr,
    description_es,
    description_gr,
  ] = await Promise.all([
    translateText(title, "fr"),
    translateText(title, "es"),
    translateText(title, "de"),
    translateText(description, "fr"),
    translateText(description, "es"),
    translateText(description, "de"),
  ]);

  const sql = `
    UPDATE package_category 
    SET title = ?, title_fr = ?, title_es = ?, title_gr = ?, 
        description = ?, description_fr = ?, description_es = ?, description_gr = ?
    WHERE idPackageCategory = ?
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        title,
        title_fr,
        title_es,
        title_gr,
        description,
        description_fr,
        description_es,
        description_gr,
        id,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

module.exports = { updateWithoutImage };

const getAllPackagesByPackageCategoryIdByLang = (idPackageCategory, lang) => {
  let titleField = "Title";
  let IntroField = "Intro";

  if (["fr", "es", "gr"].includes(lang)) {
    titleField = `Title_${lang}`;
    IntroField = `Intro_${lang}`;
  }
  const sql = `SELECT  Title, COALESCE(${IntroField}) AS Intro ,idPackage,PackageCategory_idPackageCategory,Status  FROM packages WHERE PackageCategory_idPackageCategory = ? AND Status = 1`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idPackageCategory], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getActivePackagesByNoDaysByLang = (NoDays, lang) => {
  let titleField = "Title";
  let introField = "Intro";

  if (["fr", "es", "gr"].includes(lang)) {
    titleField = `Title_${lang}`;
    introField = `Intro_${lang}`;
  }

  const sql = `
    SELECT 
      p.${titleField} AS Title,
      COALESCE(p.${introField}, '') AS Intro,
      p.idPackage,
      p.PackageCategory_idPackageCategory,
      p.Status
    FROM packages p
    WHERE p.NoDays = ? AND p.Status = 1
  `;

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

const updateOnlyImage1 = async (id, title, description, Image) => {
  const [
    title_fr,
    title_es,
    title_gr,
    description_fr,
    description_es,
    description_gr,
  ] = await Promise.all([
    translateText(title, "fr"),
    translateText(title, "es"),
    translateText(title, "de"),
    translateText(description, "fr"),
    translateText(description, "es"),
    translateText(description, "de"),
  ]);

  const sql = `
    UPDATE package_category 
    SET title = ?, title_fr = ?, title_es = ?, title_gr = ?, 
        description = ?, description_fr = ?, description_es = ?, description_gr = ?, 
        Image = ?
    WHERE idPackageCategory = ?
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        title,
        title_fr,
        title_es,
        title_gr,
        description,
        description_fr,
        description_es,
        description_gr,
        Image,
        id,
      ],
      (err, results) => {
        if (err) reject(err);
        else resolve(results);
      }
    );
  });
};

const updateOnlyImage2 = async (id, title, description, Image2) => {
  const [
    title_fr,
    title_es,
    title_gr,
    description_fr,
    description_es,
    description_gr,
  ] = await Promise.all([
    translateText(title, "fr"),
    translateText(title, "es"),
    translateText(title, "de"),
    translateText(description, "fr"),
    translateText(description, "es"),
    translateText(description, "de"),
  ]);

  const sql = `
    UPDATE package_category 
    SET title = ?, title_fr = ?, title_es = ?, title_gr = ?, 
        description = ?, description_fr = ?, description_es = ?, description_gr = ?, 
        Hero = ?
    WHERE idPackageCategory = ?
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        title,
        title_fr,
        title_es,
        title_gr,
        description,
        description_fr,
        description_es,
        description_gr,
        Image2,
        id,
      ],
      (err, results) => {
        if (err) reject(err);
        else resolve(results);
      }
    );
  });
};

module.exports = {
  add,
  update,
  remove,
  getById,
  getAll,
  getPackageImagesByPackageId,
  getAllPackagesByPackageCategoryId,
  updateWithoutImage,
  getPackageCategoryByLang,
  getAllPackagesByPackageCategoryIdByLang,
  getPackageCategoryByLangID,
  getActivePackagesByNoDaysByLang,
  updateOnlyImage1,
  updateOnlyImage2,
};
