const db = require("../db/db");
const { translateText } = require("../utils/translator");
const getAllEvents = () => {
  const sql = `SELECT e.*,ec.Name,ec.idBlogCategory AS Blog_CatName FROM events e INNER JOIN blog_category ec ON  e.BlogCategory_idBlogCategory = ec.idBlogCategory`;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
};

const addEvent = async (
  Date,
  Title,
  Description,
  Image,
  BlogCategory_idBlogCategory
) => {
  const Title_fr = await translateText(Title, "fr");
  const Title_es = await translateText(Title, "es");
  const Title_gr = await translateText(Title, "de");
  const Description_fr = await translateText(Description, "fr");
  const Description_es = await translateText(Description, "es");
  const Description_gr = await translateText(Description, "de");

  const sql = `INSERT INTO events (Date, Title, Description, Image,BlogCategory_idBlogCategory,Title_fr,Title_es,Title_gr,Description_fr,Description_es,Description_gr,Date_fr,Date_es,Date_gr) VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?)`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Date,
        Title,
        Description,
        Image,
        BlogCategory_idBlogCategory,
        Title_fr,
        Title_es,
        Title_gr,
        Description_fr,
        Description_es,
        Description_gr,
        Date,
        Date,
        Date,
      ],
      (err, results) => {
        if (err) reject(err);
        else resolve(results);
      }
    );
  });
};

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

const updateEventById = async (
  Date,
  Title,
  Description,
  Image,
  BlogCategory_idBlogCategory,
  idEvent
) => {
  const Title_fr = await translateText(Title, "fr");
  const Title_es = await translateText(Title, "es");
  const Title_gr = await translateText(Title, "de");
  const Description_fr = await translateText(Description, "fr");
  const Description_es = await translateText(Description, "es");
  const Description_gr = await translateText(Description, "de");

  const sql = `UPDATE events SET Date = ?, Title = ?, Title_fr = ?, Title_es = ?, Title_gr = ?, Description = ?, Description_fr = ?, Description_es = ?, Description_gr = ?, Image = ?, BlogCategory_idBlogCategory = ?,Date_fr = ?,Date_es =? ,Date_gr = ? WHERE idEvents = ?`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Date,
        Title,
        Title_fr,
        Title_es,
        Title_gr,
        Description,
        Description_fr,
        Description_es,
        Description_gr,
        Image,
        BlogCategory_idBlogCategory,
        Date,
        Date,
        Date,
        idEvent,
      ],
      (err, results) => {
        if (err) reject(err);
        else resolve(results);
      }
    );
  });
};

const deleteEvent = (idEvent) => {
  const sql = `DELETE FROM events WHERE idEvents = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [idEvent], (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
};
const updateEventWithoutImage = async (
  Date,
  Title,
  Description,
  BlogCategory_idBlogCategory,
  idEvent
) => {
  const Title_fr = await translateText(Title, "fr");
  const Title_es = await translateText(Title, "es");
  const Title_gr = await translateText(Title, "de");
  const Description_fr = await translateText(Description, "fr");
  const Description_es = await translateText(Description, "es");
  const Description_gr = await translateText(Description, "de");

  const sql = `UPDATE events SET Date = ?, Title = ?, Title_fr = ?, Title_es = ?, Title_gr = ?, Description = ?, Description_fr = ?, Description_es = ?, Description_gr = ?, BlogCategory_idBlogCategory = ?, Date_fr = ?, Date_es = ?, Date_gr = ? WHERE idEvents = ?`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [
        Date,
        Title,
        Title_fr,
        Title_es,
        Title_gr,
        Description,
        Description_fr,
        Description_es,
        Description_gr,
        BlogCategory_idBlogCategory,
        Date,
        Date,
        Date,
        idEvent,
      ],
      (err, results) => {
        if (err) reject(err);
        else resolve(results);
      }
    );
  });
};

const getEventByLang = (lang) => {
  let dateField = "Date";
  let titleField = "Title";
  let desField = "Description";

  if (["fr", "es", "gr"].includes(lang)) {
    dateField = `Date_${lang}`;
    titleField = `Title_${lang}`;
    desField = `Description_${lang}`;
  }

  const sql = `
    SELECT 
      e.*, 
      e.${dateField} AS Date, 
      e.${titleField} AS Title, 
      e.${desField} AS Description, 
      ec.Name, 
      ec.idBlogCategory AS Blog_CatName 
    FROM events e 
    INNER JOIN blog_category ec 
      ON e.BlogCategory_idBlogCategory = ec.idBlogCategory
  `;

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

module.exports = {
  getAllEvents,
  addEvent,
  getEventById,
  updateEventById,
  deleteEvent,
  updateEventWithoutImage,
  getEventByLang,
};
