const db = require("../db/db");

const addAccommodation = (Name, Email, Tel, Link, Image) => {
  const sql = `INSERT INTO accommodation (Name, Email, Tel, Link, Image) VALUES (?, ?, ?, ?, ?)`;
  return new Promise((resolve, reject) => {
    db.query(sql, [Name, Email, Tel, Link, Image], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getAllAccommodations = () => {
  const sql = `
    SELECT a.*, l.Name AS Location_Name
    FROM accommodation a
    JOIN locations l ON a.Location_idLocation = l.idLocations
  `;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const getAccommodationById = (idAccommodation) => {
  const sql = `
    SELECT a.*, l.LocationName, l.Address
    FROM accommodation a
    JOIN location l ON a.Location_idLocation = l.idLocation
    WHERE a.idAccommodation = ?
  `;
  return new Promise((resolve, reject) => {
    db.query(sql, [idAccommodation], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

const updateAccommodationWithImage = (
  idAccommodation,
  Name,
  Email,
  Tel,
  Link,
  Image
) => {
  const sql = `UPDATE accommodation SET Name = ?, Email = ?, Tel = ?, Link = ?, Image = ? WHERE idAccommodation = ?`;
  return new Promise((resolve, reject) => {
    db.query(
      sql,
      [Name, Email, Tel, Link, Image, idAccommodation],
      (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      }
    );
  });
};

const updateAccommodationWithoutImage = (
  idAccommodation,
  Name,
  Email,
  Tel,
  Link
) => {
  const sql = `UPDATE accommodation SET Name = ?, Email = ?, Tel = ?, Link = ? WHERE idAccommodation = ?`;
  return new Promise((resolve, reject) => {
    db.query(sql, [Name, Email, Tel, Link, idAccommodation], (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};

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

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

module.exports = {
  addAccommodation,
  getAllAccommodations,
  getAccommodationById,
  updateAccommodationWithImage,
  updateAccommodationWithoutImage,
  deleteAccommodation,
  getAccomadationByLocId,
};
