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

const addPackage = async (
  Title,
  Intro,
  PackageCategory_idPackageCategory,
  NoDays
) => {
  // Translate title and intro
  const [Title_fr, Title_es, Title_gr, Intro_fr, Intro_es, Intro_gr] =
    await Promise.all([
      translateText(Title, "fr"),
      translateText(Title, "es"),
      translateText(Title, "de"),
      translateText(Intro, "fr"),
      translateText(Intro, "es"),
      translateText(Intro, "de"),
    ]);

  const sql = `
    INSERT INTO packages 
    (Title, Title_fr, Title_es, Title_gr, 
     Intro, Intro_fr, Intro_es, Intro_gr, 
     PackageCategory_idPackageCategory,NoDays) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,?)
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Title,
        Title_fr,
        Title_es,
        Title_gr,
        Intro,
        Intro_fr,
        Intro_es,
        Intro_gr,
        PackageCategory_idPackageCategory,
        NoDays,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

const addPackageHasItems = async (Item_Title, insertedId) => {
  // Translate Item_Title
  const [Item_Title_fr, Item_Title_es, Item_Title_gr] = await Promise.all([
    translateText(Item_Title, "fr"),
    translateText(Item_Title, "es"),
    translateText(Item_Title, "de"),
  ]);

  const sql = `
    INSERT INTO packages_has_items 
    (Item_Title, Item_Title_fr, Item_Title_es, Item_Title_gr, Package_idPackage) 
    VALUES (?, ?, ?, ?, ?)
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [Item_Title, Item_Title_fr, Item_Title_es, Item_Title_gr, insertedId],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

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

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

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

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

const updatePackageWithImage = async (
  idPackage,
  Title,
  Intro,
  NoDays,
  Image
) => {
  // Translate Title and Intro
  const [Title_fr, Title_es, Title_gr, Intro_fr, Intro_es, Intro_gr] =
    await Promise.all([
      translateText(Title, "fr"),
      translateText(Title, "es"),
      translateText(Title, "de"),
      translateText(Intro, "fr"),
      translateText(Intro, "es"),
      translateText(Intro, "de"),
    ]);

  const sql = `
    UPDATE packages 
    SET Title = ?, Title_fr = ?, Title_es = ?, Title_gr = ?, 
        Intro = ?, Intro_fr = ?, Intro_es = ?, Intro_gr = ?, 
        Image = ?, NoDays = ?
    WHERE idPackage = ?
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Title,
        Title_fr,
        Title_es,
        Title_gr,
        Intro,
        Intro_fr,
        Intro_es,
        Intro_gr,
        Image,
        NoDays,
        idPackage,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

const updatePackageWithOutImage = async (idPackage, Title, Intro, NoDays) => {
  // Translate Title and Intro
  const [Title_fr, Title_es, Title_gr, Intro_fr, Intro_es, Intro_gr] =
    await Promise.all([
      translateText(Title, "fr"),
      translateText(Title, "es"),
      translateText(Title, "de"),
      translateText(Intro, "fr"),
      translateText(Intro, "es"),
      translateText(Intro, "de"),
    ]);

  const sql = `
    UPDATE packages 
    SET Title = ?, Title_fr = ?, Title_es = ?, Title_gr = ?, 
        Intro = ?, Intro_fr = ?, Intro_es = ?, Intro_gr = ?,
        NoDays = ?
    WHERE idPackage = ?
  `;

  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Title,
        Title_fr,
        Title_es,
        Title_gr,
        Intro,
        Intro_fr,
        Intro_es,
        Intro_gr,
        NoDays,
        idPackage,
      ],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

const deletePackageImagesByPackageId = (idPackage) => {
  const sql = `DELETE 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 deletePackageItemsByPackageId = (idPackage) => {
  const sql = `DELETE FROM packages WHERE idPackage = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idPackage], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

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

const changePackageStatus = (idPackage) => {
  const sql = `UPDATE packages
SET Status = CASE 
    WHEN Status = 1 THEN 0
    ELSE 1
END

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

const addPackageHasImages = (images, Package_idPackage) => {
  const sql = `INSERT INTO package_has_images (Image, Package_idPackage) VALUES (?,?)`;

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

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

const getPackageItemDetailsByPackageIdByLang = (idPackage, lang) => {
  let itemTitleField = "Item_Title";
  if (["fr", "es", "gr"].includes(lang)) {
    itemTitleField = `Item_Title_${lang}`;
  }
  const sql = `SELECT COALESCE(${itemTitleField}) AS Item_Title , idPackagesHasItems ,Package_idPackage  FROM packages_has_items WHERE Package_idPackage = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idPackage], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

module.exports = {
  addPackage,
  addPackageHasItems,
  getAllPackages,
  getPackageItemDetailsByPackageId,
  getPackageById,
  deletePackage,
  updatePackageWithImage,
  updatePackageWithOutImage,
  deletePackageItemsByPackageId,
  deletePackagesItemsByPackageId,
  changePackageStatus,
  addPackageHasImages,
  getAllPackagesActive,
  getPackageItemDetailsByPackageIdByLang,
  deletePackageImagesByPackageId,
};
