const mysql = require("mysql");
const axios = require("axios");
const fs = require("fs");
const APIKey = `9cd261ea7c3ca6300211c780454c4ba8`;
const Json2csvParser = require("json2csv").Parser;

const conn = mysql.createPool({
  connectionLimit: 11,
  host: "localhost",
  user: "root",
  database: "movielab",
  password: "dE5vbtjQ42Nf",
  charset: "utf8mb4",
});
const APIURL = `https://api.themoviedb.org/3`;
const pageSize = 5;

const botStart = `1674636900`;

//
//
//
// Configuration

const dbRequest = async (query) => {
  return new Promise((resolve, reject) => {
    conn.query(query, (err, result) => {
      if (err) {
        console.log(err?.code);
        console.log(query);
        resolve({ status: 500, message: err?.code });
      } else if (result) {
        resolve({ status: 200, message: result });
      }
    });
  });
};

const addMessage = (id, message_id, first_name, last_name, username, timestamp, sent_to) => {
  const table = `messages`;

  dbRequest(`INSERT INTO ${table} (chat_id,
    message_id,
    first_name,
    last_name,
    username,
    timestamp_of_message,
    sent_to,
    status) VALUES ('${id}', '${message_id}', '${first_name}', '${last_name}', '${username}', '${timestamp}', '${sent_to}', 'actual')`).then(
    (data) => {
      if (data.status !== 200) {
        console.log(data);
        console.log(`INSERT INTO messages (chat_id,
        message_id,
        first_name,
        last_name,
        username,
        timestamp_of_message,
        sent_to,
        status) VALUES ('${id}', '${message_id}', '${first_name}', '${last_name}', '${username}', '${timestamp}', '${sent_to}', 'actual')`);
      }
    }
  );
};

const addMessageAdmin = (id, message_id, first_name, last_name, username, timestamp, sent_to) => {
  const table = `messagesAdmin`;

  dbRequest(`INSERT INTO ${table} (chat_id,
    message_id,
    first_name,
    last_name,
    username,
    timestamp_of_message,
    sent_to,
    status) VALUES ('${id}', '${message_id}', '${first_name}', '${last_name}', '${username}', '${timestamp}', '${sent_to}', 'actual')`).then(
    (data) => {
      if (data.status !== 200) {
        console.log(data);
        console.log(`INSERT INTO messages (chat_id,
        message_id,
        first_name,
        last_name,
        username,
        timestamp_of_message,
        sent_to,
        status) VALUES ('${id}', '${message_id}', '${first_name}', '${last_name}', '${username}', '${timestamp}', '${sent_to}', 'actual')`);
      }
    }
  );
};

const clearMessages = (c_id, bot) => {
  try {
    const table = `messages`;

    dbRequest(`SELECT * FROM ${table} WHERE sent_to = '${c_id}' and status = 'actual'`).then((data) => {
      if (data.status === 200) {
        for (let q = 0; q < data.message.length; q++) {
          const item = data.message[q];

          try {
            bot.deleteMessage(c_id, item.message_id).then((data) => {
              dbRequest(`UPDATE ${table} SET status = 'removed' where id = '${item.id}'`);
            });
          } catch {
            console.log(`Can't delete message`);
          }
        }
      } else {
        console.log(data);
        console.log(`Can't get a needed message list for ${c_id}`);
      }
    });
  } catch {
    console.log(`Can't clear messages`);
  }
};

const clearMessagesAdmin = (c_id, bot) => {
  const table = `messagesAdmin`;

  dbRequest(`SELECT * FROM ${table} WHERE sent_to = '${c_id}' and status = 'actual'`).then((data) => {
    if (data.status === 200) {
      data.message.map((item) => {
        bot.deleteMessage(c_id, item.message_id);
      });

      dbRequest(`UPDATE ${table} SET status = 'removed' where sent_to = '${c_id}' and status = 'actual'`);
    } else {
      console.log(data);
      console.log(`Can't get a needed message list for ${c_id}`);
    }
  });
};

const configuration = async () => {
  return new Promise(async (resolve) => {
    axios.get(`https://api.themoviedb.org/3/configuration?api_key=${APIKey}`).then((data) => {
      resolve({
        baseURL: data.data.images.base_url.replace(`http://`, `https://`),
        posterSizes: data.data.images.poster_sizes,
        profileSizes: data.data.images.profile_sizes,
      });
    });
  });
};

const checkAdmin = async (id, firstName, username) => {
  const adminList = [737153680, 177011730, 1906217124];
  return new Promise(async (resolve) => {
    if (adminList.includes(id)) {
      resolve({ status: 200, message: `admin` });
    } else {
      const data = await dbRequest(`SELECT * FROM admins WHERE chat_id = '${id}' AND status = 1`);

      if (data.status === 200 && data.message.length > 0) {
        if (!data.message[0].first_name) {
          const update = await dbRequest(
            `UPDATE admins SET first_name = '${firstName}', username = '${username}' WHERE chat_id = '${id}'`
          );

          if (update.status !== 200) {
            errorReport(id, username, `Can't update admin data`);
          }
        }

        resolve({ status: 200, message: `admin` });
      } else {
        resolve({ status: 200, message: `not an admin` });
      }
    }
  });
};

const keyGenerator = () => {
  const abc = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ";
  let rs = "";
  while (rs.length < 6) {
    rs += abc[Math.floor(Math.random() * abc.length)];
  }
  return rs;
};

const checkSubscription = async (id, bot) => {
  return new Promise(async (resolve) => {
    bot
      .getChatMember(`@Movie_Lab`, id)
      .then((data) => {
        if (data?.status && data?.status !== `left` && data?.status !== `kicked`) {
          resolve({ status: 200, message: true });
        } else {
          resolve({ status: 200, message: false });
        }
      })
      .catch((err) => {
        console.log(err);
        console.log(`Not found`);
        resolve({ status: 200, message: false });
      });
  });
};

const errorReport = (id, username, message) => {};

const getMenuKeyboard = async (id) => {
  return new Promise(async (resolve) => {
    const isAdmin = await dbRequest(`SELECT * FROM admins WHERE chat_id = '${id}'`);
    const keyboard = [
      [{ text: "Поиск фильма", callback_data: "search categories" }],
      [{ text: "Рекомендованные фильмы", callback_data: "lucky shot" }],
      [{ text: "Обратная связь", callback_data: "contact us" }],
      [
        {
          text: "Поделиться ботом",
          url: `tg://msg_url?url=https://t.me/movielabtv_bot`,
        },
      ],
    ];

    if (isAdmin.status === 200) {
      if (isAdmin.message.length > 0) {
        keyboard.push([{ text: `Необработанные запросы`, callback_data: `unsolved requests` }]);
        resolve({ status: 200, message: keyboard });
      } else {
        resolve({ status: 200, message: keyboard });
      }
    } else {
      resolve({ status: 500, message: `Can't get admins list` });
    }
  });
};

const getAdminKeyboard = async (id) => {
  return new Promise((resolve) => {
    const superAdmins = [737153680, 177011730];

    let keyboard = [
      [
        { text: "Редактировать FAQ", callback_data: "faq" },
        // { text: "Создать рассылку", callback_data: "create broadcast" },
      ],
      [
        { text: "Список пользователей", callback_data: "users list" },
        { text: "История запросов", callback_data: "requests history" },
      ],
      [{ text: "Запросы на добавление фильма", callback_data: "getNewFilmRequestsHistory" }],
      [{ text: "Предложения сотрудничества", callback_data: "cooperation" }],
      [{ text: "Список администраторов", callback_data: "admins list" }],
      [{ text: "Список рекомендованных фильмов", callback_data: "random list" }],
    ];

    if (superAdmins.includes(id)) {
      keyboard.push([{ text: `Аналитика`, callback_data: `deep analytics` }]);
    }

    resolve({ status: 200, message: keyboard });
  });
};

const newFilmRequest = async (id, firstName, username, userMessage, timestamp) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(
      `INSERT INTO filmRequests (user_id,user_name,user_username,film_name, timestamp) VALUES ('${id}','${firstName}','${username}','${userMessage}','${timestamp}')`
    );

    if (result?.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't add new film request` });
    }
  });
};

const getTimestamp = () => {
  const date = new Date().getTime();

  return parseInt(date / 1000);
};

//
//
//
// FAQ

const getCategories = async (type = "") => {
  return new Promise(async (resolve) => {
    const faq = await dbRequest(`SELECT * FROM faq`);

    if (faq.status === 200) {
      const keys = faq.message;
      let categories = [];
      let keyboard = [];

      for (let q = 0; q < keys.length; q++) {
        const { category, categoryID } = keys[q];
        const requestType = type === "" ? "" : ` ${type}`;

        if (!categories.includes(categoryID)) {
          categories.push(categoryID);

          keyboard.push([{ text: category, callback_data: `category,${categoryID},${requestType}` }]);
        }
      }
      keyboard.push([{ text: `Главное меню`, callback_data: `main menu` }]);

      resolve({ status: 200, message: keyboard });
    } else {
      resolve({ status: 500, message: `Can't get answers` });
    }
  });
};

const getQuestions = async (categoryID, prefix = `getan`) => {
  return new Promise(async (resolve) => {
    const faq = await dbRequest(`SELECT * FROM faq WHERE categoryID = '${categoryID}'`);

    if (faq.status === 200) {
      let keyboard = [];

      if (faq.message.length > 0) {
        for (let q = 0; q < faq.message.length; q++) {
          const { uniqueID, question } = faq.message[q];

          keyboard.push([{ text: question, callback_data: `${prefix} ${uniqueID}` }]);
        }

        if (prefix === `getan`) {
          keyboard.push([
            { text: `Задать вопрос`, callback_data: `ask for request` },
            { text: `Назад`, callback_data: `support service` },
          ]);
        } else {
          keyboard.push([{ text: `Главное меню`, callback_data: `main menu` }]);
        }

        resolve({ status: 200, message: keyboard });
      } else {
        resolve({ status: 200, message: [] });
      }
    } else {
      resolve({ status: 500, message: `Can't get categories from DB` });
    }
  });
};

const getAnswer = async (questionID, id) => {
  return new Promise(async (resolve) => {
    const faq = await dbRequest(`SELECT * FROM faq WHERE uniqueID = '${questionID}'`);

    if (faq.status === 200 && faq.message.length > 0) {
      const { question, answer, categoryID, media, mediaType } = faq.message[0];
      const faqUpdate = await dbRequest(`UPDATE faq SET requested = requested + 1 WHERE uniqueID = '${questionID}'`);
      const usersUpdate = await dbRequest(`UPDATE users SET requests = requests + 1 WHERE chat_id = '${id}'`);

      if (faqUpdate.status === 200 && usersUpdate.status === 200) {
        resolve({
          status: 200,
          message: {
            question,
            answer,
            categoryID,
            media,
            mediaType,
          },
        });
      } else {
        console.log(faqUpdate, usersUpdate);
        resolve({ status: 500, message: `Can't get answer` });
      }
    } else {
      resolve({ status: 500, message: `Can't get a question` });
    }
  });
};

const getFAQ = async (categoryID) => {
  return new Promise(async (resolve) => {
    const faq = await dbRequest(`SELECT * FROM faq WHERE categoryID = '${categoryID}'`);

    if (faq.status === 200) {
      let messages = [``, ``, ``];

      for (let q = 0; q < faq.message.length; q++) {
        const question = faq.message[q].question;
        const answer = faq.message[q].answer;

        if (messages[0].length > 3000 && messages[1].length < 3000) {
          messages[1] += `<b>${question}</b>\n\n${answer}\n\n\n`;
        } else if (messages[0].length > 3000 && messages[1].length > 3000) {
          messages[2] += `<b>${question}</b>\n\n${answer}\n\n\n`;
        } else {
          messages[0] += `<b>${question}</b>\n\n${answer}\n\n\n`;
        }
      }

      resolve({ status: 200, message: messages });
    } else {
      resolve({ status: 500, message: `Can't get faq` });
    }
  });
};

const updateFAQ = (type, questionID, message) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`UPDATE faq SET ${type} = '${message}' WHERE uniqueID = '${questionID}'`);

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't update FAQ` });
    }
  });
};

const removeFAQ = (uniqueID) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`DELETE FROM faq WHERE uniqueID = '${uniqueID}'`);

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't remove question` });
    }
  });
};

//
//
//
// Reports

const getUsersList = () => {
  return new Promise(async (resolve) => {
    const users = await dbRequest(`SELECT * FROM users`);

    if (users.status === 200 && users.message.length > 0) {
      const result = users.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("users.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `./users.csv` });
        }
      });
    } else {
      resolve({ status: 200, message: [] });
    }
  });
};

const getRequestsHistory = () => {
  return new Promise(async (resolve) => {
    const requests = await dbRequest(`SELECT * FROM requests`);

    if (requests.status === 200 && requests.message.length > 0) {
      const result = requests.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("requests.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `requests.csv` });
        }
      });
    } else {
      resolve({ status: 200, message: `No info yet` });
    }
  });
};

const getFilmRequests = () => {
  return new Promise(async (resolve) => {
    const requests = await dbRequest(`SELECT * FROM filmRequests`);

    if (requests.status === 200 && requests.message.length > 0) {
      const result = requests.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("newFilmRequests.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `newFilmRequests.csv` });
        }
      });
    } else {
      resolve({ status: 200, message: `No info yet` });
    }
  });
};

const getCooperationRequests = () => {
  return new Promise(async (resolve) => {
    const cooperation = await dbRequest(`SELECT * FROM cooperation`);

    if (cooperation.status === 200 && cooperation.message.length > 0) {
      const result = cooperation.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("cooperation.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `cooperation.csv` });
        }
      });
    } else {
      resolve({ status: 200, message: `No info yet` });
    }
  });
};

const getUserRequestsHistory = (id) => {
  return new Promise(async (resolve) => {
    const retrieveUser = await dbRequest(`SELECT user_id FROM requests WHERE uniqueID = '${id}'`);

    if (retrieveUser?.message[0]?.user_id) {
      const userID = retrieveUser?.message[0]?.user_id;
      const result = await dbRequest(`SELECT * FROM requests WHERE user_id = '${userID}'`);
      let message = ``;

      if (result?.status === 200 && result?.message) {
        if (result?.message.length > 0) {
          for (let q = 0; q < result.message.length; q++) {
            const { request, user_username, user_first_name, response } = result.message[q];

            message += `Вопрос: <b>${request}</b>\nОтвет: <b>${response}</b>\n\n`;
          }
          resolve({ status: 200, message });
        } else {
          resolve({ status: 200, message: [] });
        }
      } else {
        resolve({ status: 500, message: `Can't get user history` });
      }
    } else {
      resolve({ status: 500, message: `Can't retrieve user` });
    }
  });
};

//  Популярность запросов FAQ (+ в скобках сколько всего обращений по этому запросу)

const getAdminAnalytics = async () => {
  return new Promise(async (resolve) => {
    const requests = await dbRequest(`SELECT * FROM requests WHERE created_timestamp > '${botStart}'`);

    const totalRequests = requests?.message.length;
    let analyticsMessage = `<b>Аналитика службы поддержки</b>\n\nВсего запросов в поддержку - ${totalRequests}\n`;

    let unsolvedRequests = 0;
    let processingTime = [];
    let solutionTime = [];

    for (let q = 0; q < requests?.message.length; q++) {
      const row = requests?.message[q];

      if (!row?.tookBy) {
        unsolvedRequests++;
      } else {
        const createdTimestamp = parseInt(row?.created_timestamp);
        const tookTimestamp = parseInt(row?.tookTimestamp);
        const solvedTimestamp = parseInt(row?.solved_timestamp);

        if (createdTimestamp && tookTimestamp && solvedTimestamp) {
          const processing = tookTimestamp - createdTimestamp;
          const solving = solvedTimestamp - createdTimestamp;

          processingTime.push(processing);
          solutionTime.push(solving);
        } else {
          continue;
        }
      }
    }

    const getAverage = (numbers) => {
      let sum = 0;
      for (let i = 0; i < numbers.length; i += 1) {
        sum += numbers[i];
      }
      return parseInt(sum / numbers.length / 60);
    };

    analyticsMessage += `Нерешённых запросов - ${unsolvedRequests}\n\nСреднее время до начала обработки: ${getAverage(
      processingTime
    )} минут\nСреднее время решения вопроса (от обращения, до решения): ${getAverage(solutionTime)} минут`;

    resolve({
      status: 200,
      message: analyticsMessage,
    });
  });
};

const getUserAnalytics = async () => {
  return new Promise(async (resolve) => {
    let analyticsMessage = `<b>Пользовательская аналитика</b>\n\n`;
    const users = await dbRequest(`SELECT * FROM users WHERE timestamp_of_join > '${botStart}'`);
    const totalUsers = users?.message?.length;
    analyticsMessage += `Всего пользователей - ${totalUsers}\n`;
    const usersThisMonth = await dbRequest(
      `SELECT * FROM users WHERE timestamp_of_join > '${getTimestamp() - 2678400}'`
    );
    const totalUsersThisMonth = usersThisMonth?.message?.length;
    const usersThisWeek = await dbRequest(`SELECT * FROM users WHERE timestamp_of_join > '${getTimestamp() - 604800}'`);
    const totalUsersThisWeek = usersThisWeek?.message?.length;
    analyticsMessage += `Новых пользователей за 7 дней - ${totalUsersThisWeek}\n`;
    analyticsMessage += `Новых пользователей за 31 день - ${totalUsersThisMonth}\n`;
    // Unsubscribed requests

    const unsubscribedRequestsCount = await dbRequest(
      `SELECT MAX(action) AS Name, COUNT(action) AS Count FROM analytics WHERE action = 'Unsubscribed request' AND timestamp > '${botStart}' GROUP BY (action)`
    );
    analyticsMessage += `Всего пользователей не из группы MovieLab - ${unsubscribedRequestsCount.message[0].Count}\n\n`;

    const analytics = await dbRequest(`SELECT * FROM analytics WHERE timestamp > '${botStart}'`);
    const totalActions = analytics?.message?.length;
    analyticsMessage += `Всего действий - ${totalActions}\n\n`;

    // Frequency of actions
    const getCountOfRequests = await dbRequest(
      `SELECT MAX(action) AS Name, COUNT(action) AS Count FROM analytics WHERE timestamp > '${botStart}' GROUP BY (action)`
    );
    getCountOfRequests.message.sort((a, b) => b.Count - a.Count); // b - a for reverse sort
    let requestsCountMessage = `Самые частые действия:\n\n`;
    for (let q = 0; q < 3; q++) {
      const request = getCountOfRequests.message[q];
      requestsCountMessage += `${request.Name} - ${request.Count}\n`;
    }
    analyticsMessage += `${requestsCountMessage}`;

    // FAQ frequency

    const getFAQFrequency = await dbRequest(`SELECT question, requested FROM faq ORDER BY requested DESC`);

    let FAQFrequencyMessage = `\nЧастые запросы в FAQ:\n\n`;
    for (let q = 0; q < 10; q++) {
      const row = getFAQFrequency.message[q];
      FAQFrequencyMessage += `${row.question} - ${row.requested}\n`;
    }

    analyticsMessage += `${FAQFrequencyMessage}`;
    resolve({ status: 200, message: analyticsMessage });
  });
};

const getAnalyticsFile = async () => {
  return new Promise(async (resolve) => {
    const analytics = await dbRequest(`SELECT * FROM analytics WHERE timestamp > '1674636900'`);

    if (analytics.status === 200 && analytics.message.length > 0) {
      const result = analytics.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("analytics.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `analytics.csv` });
        }
      });
    } else {
      resolve({ status: 200, message: `No info yet` });
    }
  });
};

//
//
//
// Поиск

const idSearch = async (id) => {
  return new Promise(async (resolve) => {
    axios
      .get(`https://movielabapi.vb17123filippaaniketos.pw/api/v3/movie/${id}`)
      .then((data) => {
        if (data?.data?.kinopoisk_id && data?.data?.isBanned !== true) {
          const { year, rating, duration, description, poster, link, kinopoisk_id } = data?.data;
          const name = data?.data?.title_ru ? data?.data?.title_ru : data?.data?.title_en ? data?.data?.title_en : "";

          const genres = [];

          if (data?.data?.genres) {
            data?.data?.genres.map((genre) => {
              if (genre.name) {
                genres.push(genre.name);
              }
            });
          }

          resolve({
            status: 200,
            message: { name, year, genres, rating, duration, description, poster, link, kinopoisk_id },
          });
        } else {
          resolve({ status: 500, message: `No such film` });
        }
      })
      .catch((err) => {
        resolve({ status: 500, message: `Can't find film` });
      });
  });
};

// const nameSearch = async (title) => {
//   return new Promise(async (resolve) => {
//     axios
//       .post(`https://movielabapi.vb24131crasosnemesis.com/api/v3/search`, {
//         title,
//       })
//       .then((data) => {
//         if (data?.data?.results) {
//           let results = [];
//           for (let q = 0; q < data.data.results.length; q++) {
//             if (q > 10) break;

//             if (data.data.results[q]?.isBanned === false || data.data.results[q]?.isBanned === undefined) {
//               const { title_ru, poster, description, rating, kinopoisk_id, duration, year, type, genres } =
//                 data.data.results[q];

//               let genresArray = [];

//               if (genres.length > 0) {
//                 for (let w = 0; w < genres.length; w++) {
//                   const genre = genres[w];
//                   genresArray.push(genre.name);
//                 }
//               }

//               results.push({
//                 name: title_ru,
//                 poster,
//                 description,
//                 rating,
//                 kinopoisk_id,
//                 duration,
//                 year,
//                 type,
//                 genres: genresArray.toString().replace(/,/g, ", "),
//                 link: `https://mlab.vip/movies/${kinopoisk_id}`,
//               });

//               resolve({ status: 200, message: results });
//             } else {
//               // console.log(data.data.results[q]?.isBanned);
//               resolve({ status: 200, message: [] });
//               console.log(`Film banned`);
//             }
//           }
//         } else {
//           resolve({ status: 500, message: `Can't get films` });
//         }
//       });
//   });
// };

const nameSearch = async (title) => {
  return new Promise(async (resolve) => {
    if (title?.length > 0) {
      axios
        .get(`https://dev.movielab.guru/api/v1/new-search/movies`, {
          params: { title },
        })
        .then((response) => {
          const data = response.data;
          if (data?.results) {
            let results = [];
            for (let q = 0; q < data.results.length; q++) {
              if (q > 10) break;

              if (data.results[q]?.isBanned === false || data.results[q]?.isBanned === undefined) {
                const { title_ru, poster, description, rating, kinopoisk_id, duration, year, type, genres } =
                  data.results[q];

                let genresArray = [];

                if (genres.length > 0) {
                  for (let w = 0; w < genres.length; w++) {
                    const genre = genres[w];
                    genresArray.push(genre.name);
                  }
                }

                results.push({
                  name: title_ru,
                  poster,
                  description,
                  rating,
                  kinopoisk_id,
                  duration,
                  year,
                  type,
                  genres: genresArray.toString().replace(/,/g, ", "),
                  link: `https://mlab.vip/movies/${kinopoisk_id}`,
                });

                resolve({ status: 200, message: results });
              } else {
                resolve({ status: 200, message: [] });
                console.log(`Film banned`);
              }
            }
          } else {
            resolve({ status: 500, message: `Can't get films` });
          }
        })
        .catch((error) => {
          resolve({ status: 500, message: `Error: ${error.message}` });
        });
    }
  });
};

const getFilmByLetters = async (query, queryID, type = false) => {
  return new Promise(async (resolve) => {
    const response = await nameSearch(query);

    if (response.status === 200) {
      if (response.message.length > 0) {
        let filmsArray = [];

        for (let q = 0; q < response.message.length; q++) {
          const film = response.message[q];

          if (type === false) {
            filmsArray.push({
              type: "article",
              id: keyGenerator(),
              thumb_url: film.poster,
              thumb_width: 180,
              thumb_height: 180,
              title: `${film.name} (${film.year}, ${film.rating}/10)`,
              description: film.description,
              reply_markup: {
                inline_keyboard: [[{ text: "Перейти на сайт", url: film.link }]],
              },
              input_message_content: {
                message_text: `Хочу посмотреть <b>${film.name}</b> (${film.year})`,
                disable_web_page_preview: true,
                parse_mode: "HTML",
              },
            });
          } else if (type === `addrandom`) {
            filmsArray.push({
              type: "article",
              id: keyGenerator(),
              thumb_url: film.poster,
              thumb_width: 180,
              thumb_height: 180,
              title: `${film.name} (${film.year}, ${film.rating}/10)`,
              description: film.description,
              input_message_content: {
                message_text: `Добавить в рекомендованные фильм ${film.kinopoisk_id}`,
                disable_web_page_preview: true,
                parse_mode: "HTML",
              },
            });
          }
        }

        resolve({ status: 200, message: filmsArray });
      } else {
        resolve({
          status: 200,
          message: [
            {
              type: "article",
              id: keyGenerator(),
              title: `Результатов не найдено`,
              reply_markup: {
                inline_keyboard: [
                  [{ text: "Найти ещё", switch_inline_query_current_chat: "" }],
                  [{ text: "Главное меню", callback_data: "main menu" }],
                ],
              },
              input_message_content: {
                message_text: `Фильм не выбран`,
                disable_web_page_preview: true,
              },
            },
          ],
        });
      }
    } else {
      console.log(response.message);
      resolve({ status: 500, message: `Can't get films by letters` });
    }
  });
};

const getPersonByLetters = async (name) => {
  return new Promise(async (resolve) => {
    if (name.length >= 2) {
      axios
        .get(`${APIURL}/search/person?api_key=${APIKey}&query=${name}&page=1&language=ru`)
        .then(async (data) => {
          if (data?.data?.results) {
            if (data.data.results.length > 0) {
              let personsArray = [];
              console.log(mainPath);
              for (let q = 0; q < data.data.results.length; q++) {
                const person = data.data.results[q];

                const department =
                  person?.known_for_department == `Sound`
                    ? `Композитор`
                    : person?.known_for_department == `Acting`
                    ? `Актёр/Актриса`
                    : person?.known_for_department == `Camera`
                    ? `Оператор`
                    : person?.known_for_department == `Directing`
                    ? `Режиссёр`
                    : person?.known_for_department == `Production`
                    ? `Продюссёр`
                    : person?.known_for_department == `Writing`
                    ? `Сценарист`
                    : person?.known_for_department == `Editing`
                    ? `Монтажёр`
                    : person?.known_for_department;

                let knownFor = ``;

                if (data.data.results[q]?.known_for.length > 0) {
                  for (let w = 0; w < data.data.results[q].known_for.length; w++) {
                    const film = data.data.results[q].known_for[w];
                    if (film.title) {
                      knownFor += `, ${film.title}`;
                    } else if (film.name) {
                      knownFor += `, ${film.name}`;
                    }
                  }
                }

                personsArray.push({
                  type: "article",
                  id: keyGenerator(),
                  title: `${person.name} (${department})`,
                  description: knownFor.replace(", ", ""),
                  input_message_content: {
                    message_text: `Найдите пожалуйста фильмы персоны ${person.name}(${person.id})`,
                    disable_web_page_preview: true,
                    parse_mode: "HTML",
                  },
                });
              }

              resolve({ status: 200, message: personsArray });
            } else {
              resolve({
                status: 200,
                message: [
                  {
                    type: "article",
                    id: keyGenerator(),
                    title: `Результатов не найдено`,
                    reply_markup: {
                      inline_keyboard: [
                        [{ text: "Найти ещё", switch_inline_query_current_chat: "" }],
                        [{ text: "Главное меню", callback_data: "main menu" }],
                      ],
                    },
                    input_message_content: {
                      message_text: `Фильм не выбран`,
                      disable_web_page_preview: true,
                    },
                  },
                ],
              });
            }
          } else {
            console.log(data.data);
            resolve({ status: 500, message: `API Error` });
          }
        })
        .catch((err) => {
          console.log(err);
          resolve({ status: 500, message: `API Error` });
        });
    } else {
      resolve({
        status: 200,
        message: [
          {
            type: "article",
            id: keyGenerator(),
            title: `Результатов не найдено`,
            reply_markup: {
              inline_keyboard: [
                [{ text: "Найти ещё", switch_inline_query_current_chat: "" }],
                [{ text: "Главное меню", callback_data: "main menu" }],
              ],
            },
            input_message_content: {
              message_text: `Фильм не выбран`,
              disable_web_page_preview: true,
            },
          },
        ],
      });
    }
  });
};

const movieLabCheck = async (title, year) => {
  return new Promise(async (resolve) => {
    axios
      .post(`https://movielabapi.vb17123filippaaniketos.pw/api/v3/search`, {
        title,
      })
      .then((data) => {
        console.log(data.data.results);

        if (data.data.results.length > 0) {
          let found = false;
          for (let q = 0; q < data.data.results.length; q++) {
            const film = data.data.results[q];
            if (film.year == year && !film.isBanned) {
              resolve({ status: 200, message: film });
              found = true;
              break;
            }
          }

          if (found === false) {
            resolve({ status: 200, message: false });
          }
        } else {
          resolve({ status: 200, message: false });
        }
      })
      .catch((err) => {
        console.log(err);
        resolve({ status: 500, message: false });
      });
  });
};

const checkArray = async (films, stoppedAt = 0) => {
  return new Promise(async (resolve) => {
    let j = stoppedAt;
    let resultsObject = [];
    let errors = 0;

    const gettingResults = () => {
      if (resultsObject.length < pageSize && j < films.length) {
        movieLabCheck(films[j].title, films[j].year)
          .then((data) => {
            if (data.message !== false) {
              const film = data.message;
              let genres = [];

              if (film?.genres.length > 0) {
                for (let w = 0; w < film.genres.length; w++) {
                  const genre = film.genres[w];
                  genres.push(genre.name);
                }
              }

              const filmObject = {
                name: film.title_ru,
                poster: film.poster,
                description: film.description,
                duration: film.duration,
                rating: film.rating,
                year: film.year,
                link: `https://mlab.vip/movies/${film.kinopoisk_id}`,
                genres,
              };
              resultsObject.push(filmObject);
            }
            setTimeout(function () {
              j++;
              gettingResults();
            }, 400);
          })
          .catch((err) => {
            errors++;
            if (errors < 3) {
              setTimeout(function () {
                j++;
                gettingResults();
              }, 1000);
            } else {
              resolve({
                status: 200,
                message: {
                  results: resultsObject,
                  stoppedAt: j,
                  firstPage: stoppedAt === 0 ? true : false,
                  lastPage: films.length >= j ? false : true,
                  films,
                },
              });
            }
          });
      } else {
        resolve({
          status: 200,
          message: {
            results: resultsObject,
            stoppedAt: j,
            firstPage: stoppedAt === 0 ? true : false,
            lastPage: films.length >= j ? false : true,
            films,
          },
        });
      }
    };

    gettingResults();
  });
};

const getPersonFilms = async (id, stoppedAt = 0) => {
  return new Promise(async (resolve) => {
    let filmsArray = [];
    axios
      .get(`${APIURL}/person/${id}/movie_credits?api_key=9cd261ea7c3ca6300211c780454c4ba8&language=ru`)
      .then(async (data) => {
        if (data.data?.cast.length > 0 || data.data?.crew.length > 0) {
          for (let q = 0; q < data.data.cast.length; q++) {
            const film = data.data.cast[q];
            filmsArray.push({
              id: film.id,
              poster_path: film.poster_path,
              title: film.title,
              original_title: film.original_title,
              overview: film.overview,
              genre_ids: film.genre_ids,
              release_date: film.release_date,
              year: film.release_date.split("-")[0],
            });
          }

          for (let q = 0; q < data.data.crew.length; q++) {
            const film = data.data.crew[q];
            filmsArray.push({
              id: film.id,
              poster_path: film.poster_path,
              title: film.title,
              original_title: film.original_title,
              overview: film.overview,
              genre_ids: film.genre_ids,
              release_date: film.release_date,
              year: film.release_date.split("-")[0],
            });
          }

          const results = await checkArray(filmsArray, stoppedAt);

          if (results.status === 200) {
            resolve({ status: 200, message: results.message });
          } else {
            resolve({ status: 500, message: [] });
          }
        } else {
          resolve({ status: 200, message: [] });
        }
      })
      .catch((err) => {
        console.log(err);
        resolve({ status: 500, message: `Error getting movies` });
      });
  });
};

const newSupportRequest = async (requestObject) => {
  return new Promise(async (resolve) => {
    const { uniqueID, id, firstName, username, request, timestamp, media } = requestObject;
    let mediaArray = "";

    if (media.length > 0) {
      for (let q = 0; q < media.length; q++) {
        const filePath = media[q].media;
        mediaArray += `${filePath};;`;
      }
    }
    const result = await dbRequest(
      `INSERT INTO requests (uniqueID, user_id, user_first_name, user_username, request, created_timestamp, media) VALUES ('${uniqueID}', '${id}', '${firstName}', '${username}', '${request.replace(
        /'/g,
        '"'
      )}', '${timestamp}', '${mediaArray}')`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `DB Error` });
    }
  });
};

const newCooperation = async (id, firstName, lastName, username, message, bot) => {
  return new Promise(async (resolve) => {
    const timestamp = new Date().getTime();
    const settings = JSON.parse(fs.readFileSync("./settings.json", "utf-8"));

    const result = await dbRequest(`INSERT INTO cooperation (chat_id,
      first_name,
      last_name,
      username,
      timestamp,
      text) VALUES ('${id}','${firstName}','${lastName}','${username}','${timestamp}','${message.replace(
      /'/g,
      '"'
    )}')`);

    if (result.status === 200) {
      const credits =
        username.length > 3 ? `${firstName} ${lastName}(${id}) @${username}` : `${firstName} ${lastName} (${id})`;

      bot
        .sendMessage(settings.cooperationGroup, `<b>Запрос на сотрудничество</b>\n\n${credits}\n\n${message}`, {
          parse_mode: "HTML",
        })
        .then(function (sendedMessage) {
          if (sendedMessage["message_id"]) {
            resolve({ status: 200, message: `Success` });
          } else {
            resolve({ status: 500, message: `Can't send new cooperation` });
          }
        });
    } else {
      console.log(result);
      resolve({ status: 500, message: `Can't add new cooperation` });
    }
  });
};

const addFAQ = (faq, id, timestamp) => {
  return new Promise(async (resolve) => {
    const categoryID = faq.categoryID ? faq.categoryID : keyGenerator();

    const result = await dbRequest(
      `INSERT INTO faq (category, categoryID, uniqueID, question, answer, media, mediaType, addedBy, timestamp) VALUES ('${
        faq.category
      }', '${categoryID}', '${keyGenerator()}', '${faq.question}', '${faq.answer}', '${faq.media}', '${
        faq.mediaType
      }', '${id}', '${timestamp}')`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't add new FAQ` });
    }
  });
};

function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min)) + min; //Максимум не включается, минимум включается
}

const getLuckyShot = (currentLuckyShot) => {
  return new Promise(async (resolve) => {
    console.log(currentLuckyShot);
    const films = await dbRequest(
      `SELECT * FROM randomFilms WHERE status = 1 AND kinopoisk_id != '${currentLuckyShot}'`
    );

    if (films.status === 200) {
      if (films.message.length > 0) {
        const index = getRandomInt(0, films.message.length);

        resolve({ status: 200, message: films.message[index] });
      } else {
        errorReport(`admin`, `admin`, `No random films in the database`);
        resolve({ status: 200, message: [] });
      }
    } else {
      resolve({ status: 500, message: `Can't get random films` });
    }
  });
};

const addAdmin = (newID, newFirstName, newUsername, currentID, currentName, currentUsername, timestamp) => {
  return new Promise(async (resolve) => {
    const admins = await dbRequest(`SELECT * FROM admins WHERE chat_id = '${newID}'`);

    if (admins.status === 200) {
      if (admins.message.length === 0) {
        const result = await dbRequest(`INSERT INTO admins (chat_id,
          first_name,
          username,
          added_by_id,
          added_by_name,
          added_by_username,
          timestamp) VALUES ('${newID}','${newFirstName}','${newUsername}','${currentID}','${currentName}','${currentUsername}','${timestamp}')`);

        if (result.status === 200) {
          resolve({ status: 200, message: `Администратор успешно добавлен` });
        } else {
          resolve({ status: 500, message: `Can't add admin` });
        }
      } else {
        resolve({ status: 200, message: `Администратор уже был добавлен ранее` });
      }
    } else {
      console.log(data);
      resolve({ status: 500, message: `Can't get admin` });
    }
  });
};

const getAdmins = () => {
  return new Promise(async (resolve) => {
    const admins = await dbRequest(`SELECT * FROM admins`);

    if (admins.status === 200 && admins.message.length > 0) {
      const result = admins.message;

      const jsonData = JSON.parse(JSON.stringify(result));
      const json2csvParser = new Json2csvParser({ header: true });
      const csv = json2csvParser.parse(jsonData);

      fs.writeFile("admins.csv", csv, function (error) {
        if (error) {
          errorReport(`admin`, `admin`, `Can't save file`);
          resolve({ status: 500, message: `Can't save file` });
        } else {
          resolve({ status: 200, message: `admins.csv` });
        }
      });
    }
  });
};

const getAdminsList = () => {
  return new Promise(async (resolve) => {
    const admins = await dbRequest(`SELECT * FROM admins WHERE status = 1`);

    if (admins.status === 200) {
      if (admins.message.length > 0) {
        let keyboard = [];

        for (let q = 0; q < admins.message.length; q++) {
          const { chat_id, first_name, username } = admins.message[q];
          keyboard.push([{ text: `${first_name} ${username} (${chat_id})`, callback_data: `removeadmin ${chat_id}` }]);
        }

        keyboard.push([{ text: `Главное меню`, callback_data: `main menu` }]);

        resolve({ status: 200, message: keyboard });
      } else {
        resolve({ status: 200, message: `No admins yet` });
      }
    } else {
      resolve({ status: 500, message: `Can't get admins` });
    }
  });
};

const removeAdmin = (adminID, id, username, timestamp) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(
      `UPDATE admins SET status = 0, removed_by = '${id}', removed_by_username = '${username}', removed_timestamp = '${timestamp}' WHERE chat_id = ${adminID}`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Removed` });
    } else {
      resolve({ status: 500, message: `Can't remove admin` });
    }
  });
};

const sendAnswer = (requestID, message, id, username, timestamp) => {
  return new Promise(async (resolve) => {
    const requestRow = await dbRequest(`SELECT * FROM requests WHERE uniqueID = '${requestID}'`);

    if (requestRow.status === 200 && requestRow.message.length > 0) {
      const { user_id, request } = requestRow.message[0];

      const insertData = await dbRequest(
        `UPDATE requests SET response = '${message}', solved_by = '${id}', solved_by_username = '${username}', solved_timestamp = '${timestamp}' WHERE uniqueID = '${requestID}'`
      );
      const updateSolved = await dbRequest(`UPDATE admins SET requestsSolved = requestsSolved + 1`);

      if (insertData.status === 200 && updateSolved.status === 200) {
        resolve({ status: 200, message: { user_id, request } });
      } else {
        console.log(insertData, updateSolved);
        resolve({ status: 500, message: `Can't update database` });
      }
    } else {
      resolve({ status: 500, message: `Can't get request row` });
    }
  });
};

const getRandomFilmsList = () => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * FROM randomFilms WHERE status = 1`);

    if (result.status === 200) {
      if (result.message.length > 0) {
        resolve({ status: 200, message: result.message });
      } else {
        resolve({ status: 200, message: [] });
      }
    } else {
      resolve({ status: 500, message: `Can't get films list` });
    }
  });
};

const getRandomFilmsArray = (offsetString = 0) => {
  return new Promise(async (resolve) => {
    const offset = parseInt(offsetString);
    const pageSize = 10;
    const result = await dbRequest(`SELECT * FROM randomFilms WHERE status = 1`);

    let keyboard = [];

    if (result.status === 200) {
      if (result.message.length > 0) {
        if (result.message.length > offset) {
          let processed = offset;
          for (let q = offset; q < result.message.length; q++) {
            if (q < offset + pageSize) {
              const film = result.message[q];
              processed++;
              const { id, name, year, kinopoisk_id } = film;
              keyboard.push([{ text: `${name} (${year}, ID: ${kinopoisk_id})`, callback_data: `remove random ${id}` }]);
            } else {
              break;
            }
          }

          if (processed < result.message.length) {
            console.log(processed, result.message.length);

            if (offset > 0) {
              keyboard.push(
                [
                  { text: `\u{2B05}`, callback_data: `remove random film ${offset - pageSize}` },
                  { text: `\u{27A1}`, callback_data: `remove random film ${processed}` },
                ],
                [{ text: "Главное меню", callback_data: "main menu" }]
              );
            } else {
              keyboard.push(
                [{ text: `\u{27A1}`, callback_data: `remove random film ${processed}` }],
                [{ text: "Главное меню", callback_data: "main menu" }]
              );
            }
          } else {
            if (offset > 0) {
              keyboard.push(
                [{ text: `\u{2B05}`, callback_data: `remove random film ${offset - pageSize}` }],
                [{ text: "Главное меню", callback_data: "main menu" }]
              );
            }
          }

          keyboard.push();

          resolve({ status: 200, message: keyboard });
        } else {
          resolve({ status: 500, message: `Paginaton error getting the random films` });
        }
      } else {
        resolve({ status: 200, message: [] });
      }
    } else {
      resolve({ status: 500, message: `Can't get list of random films` });
    }
  });
};

const addFilmToRandom = (
  name,
  poster,
  description,
  rating,
  duration,
  year,
  type,
  genres,
  link,
  added_by,
  added_timestamp,
  kinopoisk_id
) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`INSERT INTO randomFilms (name,
      poster,
      description,
      rating,
      duration,
      year,
      type,
      genres,
      link,
      added_by,
      added_timestamp, kinopoisk_id) VALUES ('${name}',
        '${poster}',
        '${description}',
        '${rating}',
        '${duration}',
        '${year}',
        '${type}',
        '${genres}',
        '${link}',
        '${added_by}',
        '${added_timestamp}', '${kinopoisk_id}')`);

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't add new random film` });
    }
  });
};

const getUnsolvedRequests = (id) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * FROM requests WHERE tookBy = '${id}' AND solved_timestamp IS NULL`);

    if (result.status === 200) {
      resolve({ status: 200, message: result.message });
    } else {
      resolve({ status: 500, message: `Can't get unsolved requests` });
    }
  });
};

const removeRandomFilm = (filmID, id, timestamp) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(
      `UPDATE randomFilms SET status = 0, removed_by = '${id}', removed_timestamp = '${timestamp}' WHERE id = ${filmID}`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't remove random` });
    }
  });
};

const checkActiveBroadcast = () => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * from broadcasts WHERE status = 0`);

    if (result.status === 200) {
      if (result.message.length === 0) {
        resolve({ status: 200, message: true });
      } else {
        resolve({ status: 200, message: false });
      }
    } else {
      resolve({ status: 500, message: `Can't get broadcasts table` });
    }
  });
};

const createBroadcast = (broadcastObject, id, timestamp) => {
  return new Promise(async (resolve) => {
    const users = await dbRequest(`SELECT * FROM users`);

    if (users.status === 200) {
      const recipients = users.message.length;
      const result = await dbRequest(
        `INSERT INTO broadcasts (created_by, created_timestamp, media, text, recipients, status) VALUES ('${id}', '${timestamp}', '${broadcastObject.media}', '${broadcastObject.text}', ${recipients}, 0)`
      );

      if (result.status === 200) {
        resolve({ status: 200, message: `Success` });
      } else {
        resolve({ status: 500, message: `Can't add new broadcast` });
      }
    } else {
      resolve({ status: 500, message: `Can't add new broadcast` });
    }
  });
};

const getRequestRow = (uniqueID) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * FROM requests WHERE uniqueID = '${uniqueID}'`);

    if (result.status === 200 && result.message.length > 0) {
      resolve({ status: 200, message: result.message[0] });
    } else {
      resolve({ status: 500, message: `Can't get a request row` });
    }
  });
};

const tookRequest = (id) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`UPDATE admins SET requestsTook = requestsTook + 1 WHERE chat_id = '${id}' `);

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't update requestsTook for user ${id}` });
    }
  });
};

const getCategoryName = (categoryID) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * FROM faq WHERE categoryID = '${categoryID}'`);

    if (result.status === 200 && result.message.length > 0) {
      resolve({ status: 200, message: result.message[0].category });
    } else {
      resolve({ status: 500, message: `Can't get category name` });
    }
  });
};

const newUser = (id, firstName, lastName, username, timestamp) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(`SELECT * FROM users WHERE chat_id = '${id}'`);

    if (result.status === 200) {
      if (result.message.length === 0) {
        const addNewUser = await dbRequest(`INSERT INTO users (chat_id,
          first_name,
          last_name,
          username,
          timestamp_of_join) VALUES ('${id}', '${firstName}','${lastName}','${username}','${timestamp}')`);

        if (addNewUser.status === 200) {
          resolve({ status: 200, message: `New user added` });
        } else {
          errorReport(`admin`, `admin`, `Can't add new user`);
          resolve({ status: 500, message: `Can't add new user` });
        }
      } else {
        resolve({ status: 200, message: `User already exist` });
      }
    } else {
      resolve({ status: 500, message: `Can't add new user` });
      errorReport(`admin`, `admin`, `Can't add new user`);
    }
  });
};

const checkBlackList = (id) => {
  return new Promise(async (resolve) => {
    const inBlackList = await dbRequest(`SELECT * FROM blacklist WHERE chat_id = '${id}'`);

    if (inBlackList.status === 200) {
      if (inBlackList.message.length > 0) {
        resolve({ status: 200, message: true });
      } else {
        resolve({ status: 200, message: false });
      }
    } else {
      resolve({ status: 500, message: `Can't get blacklist` });
    }
  });
};

const banUser = (uniqueID, id, username, timestamp) => {
  return new Promise(async (resolve) => {
    const userRow = await dbRequest(`SELECT * FROM requests WHERE uniqueID = '${uniqueID}'`);
    const updateRow = await dbRequest(
      `UPDATE requests SET response = 'banned', solved_by = '${id}', solved_timestamp = '${timestamp}' WHERE uniqueID = '${uniqueID}'`
    );

    const { user_id, user_first_name, user_username, request } = userRow.message[0];

    if (userRow.status === 200) {
      const ban = await dbRequest(
        `INSERT INTO blacklist (chat_id, first_name, last_name, username, timestamp, added_by, added_by_username, added_for) VALUES ('${user_id}','${user_first_name}','','${user_username}','${timestamp}','${id}','${username}','${request}')`
      );

      if (ban.status === 200) {
        console.log(`Success`);
        resolve({ status: 200, message: `Success` });
      } else {
        resolve({ status: 500, message: `Can't add user to blacklist` });
      }
    } else {
      console.log(`Can't get user row`);
      resolve({ status: 500, message: `Can't get user row` });
    }
  });
};

const analytics = (botType, chat_id, firstName, username, timestamp, action) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(
      `INSERT INTO analytics (bot_type, chat_id, first_name, username, timestamp, action) VALUES ('${botType}', '${chat_id}', '${firstName}', '${username}', '${timestamp}', '${action}')`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      resolve({ status: 500, message: `Can't save analytics` });
    }
  });
};

const takeQuestion = async (id, timestamp, requestID) => {
  return new Promise(async (resolve) => {
    const result = await dbRequest(
      `UPDATE requests SET tookBy = '${id}', tookTimestamp = '${timestamp}' WHERE uniqueID = '${requestID}'`
    );

    if (result.status === 200) {
      resolve({ status: 200, message: `Success` });
    } else {
      console.log(result);
      resolve({ status: 500, message: `Can't update requests` });
    }
  });
};

module.exports = {
  nameSearch,
  newUser,
  checkAdmin,
  sendAnswer,
  errorReport,
  getCategories,
  newCooperation,
  keyGenerator,
  checkSubscription,
  addMessage,
  clearMessages,
  getFAQ,
  newSupportRequest,
  getPersonByLetters,
  getPersonFilms,
  getQuestions,
  getAnswer,
  getUsersList,
  getRequestsHistory,
  getCooperationRequests,
  addMessageAdmin,
  clearMessagesAdmin,
  addFAQ,
  addAdmin,
  getAdmins,
  getAdminsList,
  removeAdmin,
  updateFAQ,
  removeFAQ,
  addFilmToRandom,
  getRandomFilmsArray,
  removeRandomFilm,
  createBroadcast,
  checkActiveBroadcast,
  getRequestRow,
  getMenuKeyboard,
  getLuckyShot,
  getUnsolvedRequests,
  tookRequest,
  getCategoryName,
  dbRequest,
  banUser,
  checkBlackList,
  analytics,
  getAdminKeyboard,
  takeQuestion,
  getRandomFilmsList,
  getFilmByLetters,
  getUserRequestsHistory,
  newFilmRequest,
  getFilmRequests,
  idSearch,
  getAnalyticsFile,
  getAdminAnalytics,
  getUserAnalytics,
};
