import {
  BadGatewayException,
  BadRequestException,
  ConflictException,
  ForbiddenException,
  Injectable,
  InternalServerErrorException,
  NotFoundException,
  UnauthorizedException,
} from '@nestjs/common';
import { EntityManager } from 'typeorm';
import {
  RetrieveRecipes_Recipe,
  RetrieveRecipesQueryParamsDto,
  RetrieveRecipesResponseDto,
} from './dtos/retrieve-recipes.dto';
import { CloudinaryService } from '../../services/image-management/cloudinary/cloudinary.service';
import { IUser } from '../../shared/decorators/auth/user.decorator';
import {
  RetrieveRecipe_Recipe,
  RetrieveRecipeParamsDto,
  RetrieveRecipeResponseDto,
} from './dtos/retrieve-recipe.dto';
import {
  RetrieveIngredientsParamsDto,
  RetrieveIngredientsResponseDto,
} from './dtos/retrieve-ingredients.dto';
import merge from 'lodash/merge';
import {
  RetrieveNutrientsParamsDto,
  RetrieveNutrientsResponseDto,
} from './dtos/retrieve-nutrients.dto';
import { CreateRecipeDto } from './dtos/create-recipe.dto';
import { LexoRank } from 'lexorank';
import { DeleteRecipeParamsDto } from './dtos/delete-recipe.dto';
import { AddLikeToRecipeDto } from './dtos/add-like-to-recipe.dto';
import { RemoveLikeFromRecipeParamsDto } from './dtos/remove-like-from-recipe.dto';
import { UploadRecipePictureParamsDto } from './dtos/upload-recipe-picture.dto';
import { UploadImageOptions } from '../../services/image-management/cloudinary/interfaces/upload-image-options.interface';
import { DeleteRecipePictureParamsDto } from './dtos/delete-recipe-picture.dto';
import {
  RetrieveRecipesTagsQueryParamsDto,
  RetrieveRecipesTagsResponseDto,
} from './dtos/retrieve-recipes-tags.dto';
import {
  RetrieveRecipeTagParamsDto,
  RetrieveRecipeTagResponseDto,
} from './dtos/retrieve-recipe-tag.dto';
import {
  UpdateRecipeDto,
  UpdateRecipeParamsDto,
  UpdateRecipe_Ingredient,
  UpdateRecipe_StepByStep,
} from './dtos/update-recipe.dto';
import isEmpty from 'lodash/isEmpty';
import { NutrientPer100GramsDto } from '../../shared/dtos/nutrients/nutrient-per-100-grams.dto';
import { sortNutrients } from '../../shared/functions/nutrients/sort-nutrients.function';
import { mergeAndSumIntakesAndNutrientsQuantity } from '../../shared/functions/nutrients/merge-and-sum-intakes-and-nutrients-quantity.function';
import { retrieveUserTDEE } from '../../shared/functions/user/retrieve-user-tdee.function';
import { NutrientIntakesDto } from '../../shared/dtos/nutrients/nutrient-intakes.dto';
import { SimpleNumberDto } from '../../shared/dtos/simple-number.dto';

@Injectable()
export class RecipesService {
  constructor(
    private readonly _entityManager: EntityManager,
    private readonly _cloudinaryService: CloudinaryService,
  ) {}

  private _validateQueryParams(
    queryParams: RetrieveRecipesQueryParamsDto,
    user?: IUser,
  ) {
    const {
      self,
      liked_only: likedOnly,
      from_user_id: fromUserId,
      from_username: fromUsername,
    } = queryParams;

    if (self && !user?.id) {
      throw new UnauthorizedException(
        "'self' como verdadeiro requer que esteja autenticado",
      );
    }

    if (likedOnly && !user?.id) {
      throw new UnauthorizedException(
        "'likedOnly' como verdadeiro requer que esteja autenticado",
      );
    }

    if (self && (fromUserId || fromUsername)) {
      throw new BadRequestException(
        "'self' como verdadeiro não pode ser usado junto com 'from_user_id' ou 'from_username'",
      );
    }

    if (fromUserId && fromUsername)
      throw new BadRequestException(
        "'from_user_id' não pode ser usado junto com 'from_username'",
      );
  }

  private _generateExtraFields(user?: IUser) {
    const fields = [];
    const values = [];

    if (user?.id) {
      fields.push(`
        EXISTS(
          SELECT * FROM user_liked_recipe
          WHERE user_person_id = ? AND recipe_id = recipe.id
        ) AS isLiked
      `);

      values.push(user?.id);
    }

    return {
      EXTRA_FIELDS_SQL: fields.length > 0 ? `, ${fields.join(', ')}` : '',
      EXTRA_FIELDS_VALUES: values,
    };
  }

  private _SELECT_CALORIES_FROM_RECIPE = `
    (
      SELECT
        IFNULL(
          ROUND(SUM(IFNULL(food.kcal, 0) * recipe_has_food.quantity / 100) / _recipe.servings),
          '*'
        )
      FROM recipe AS _recipe
      INNER JOIN recipe_has_food
        ON _recipe.id = recipe_has_food.recipe_id
      INNER JOIN food
        ON recipe_has_food.food_id = food.id
      WHERE _recipe.id = recipe.id
    )
  `;

  private _generateConditions(
    queryParams: RetrieveRecipesQueryParamsDto,
    user?: IUser,
  ) {
    const {
      self,
      liked_only: likedOnly,
      from_user_id: userId,
      from_username: username,
      ingredients_ids: ingredientsIds,
      min_calorie_content_per_portion: minCalorieContentPerPortion,
      max_calorie_content_per_portion: maxCalorieContentPerPortion,
      servings,
      tags_ids: tagsIds,
      search_by_name: searchByName,
    } = queryParams;

    const joins = [];
    const whereConditions = ['recipe.was_deleted = false'];
    const havingConditions = [];
    const whereValues = [];
    const havingValues = [];

    if (self && user?.id) {
      whereConditions.push('user.person_id = ?');
      whereValues.push(user?.id);
    }

    if (likedOnly) {
      havingConditions.push('isLiked = true');
    }

    if (userId) {
      whereConditions.push('user.person_id = ?');
      whereValues.push(userId);
    }

    if (username) {
      whereConditions.push('user.username = ?');
      whereValues.push(username);
    }

    if (ingredientsIds) {
      joins.push(`
        LEFT JOIN recipe_has_food
          ON recipe.id = recipe_has_food.recipe_id
      `);

      whereConditions.push(
        `recipe.id IN (
          SELECT recipe_id AS id FROM recipe_has_food
          WHERE food_id IN (${ingredientsIds.map(() => '?').join(', ')})
          GROUP BY recipe_id
          HAVING COUNT(*) = ?
        )`,
      );

      whereValues.push(...ingredientsIds, ingredientsIds.length);
    }

    if (minCalorieContentPerPortion || maxCalorieContentPerPortion) {
      havingConditions.push(
        `CAST(kcal AS UNSIGNED) BETWEEN ${
          minCalorieContentPerPortion ? '?' : '0'
        } AND ${maxCalorieContentPerPortion ? '?' : '~0'}`,
      );

      if (minCalorieContentPerPortion)
        havingValues.push(minCalorieContentPerPortion);

      if (maxCalorieContentPerPortion)
        havingValues.push(maxCalorieContentPerPortion);
    }

    if (servings) {
      whereConditions.push('recipe.servings = ?');
      whereValues.push(servings);
    }

    if (tagsIds) {
      whereConditions.push(
        `recipe.id IN (
          SELECT recipe_id AS id FROM recipe_has_recipe_tag
          WHERE recipe_tag_id IN (${tagsIds.map(() => '?').join(', ')})
          GROUP BY recipe_id
          HAVING COUNT(*) = ?
        )`,
      );

      whereValues.push(...tagsIds, tagsIds.length);
    }

    if (searchByName) {
      whereConditions.push('MATCH(recipe.name) AGAINST(? IN BOOLEAN MODE)');

      const searchTerms = searchByName.replace(/ +/g, '*+*');

      whereValues.push(`*${searchTerms}*`);
    }

    return {
      EXTRA_JOINS_SQL: joins.length > 0 ? `${joins.join(' ')}` : '',

      WHERE_CONDITIONS_SQL:
        whereConditions.length > 0
          ? `WHERE ${whereConditions.join(' AND ')}`
          : '',

      HAVING_CONDITIONS_SQL:
        havingConditions.length > 0
          ? `HAVING ${havingConditions.join(' AND ')}`
          : '',

      CONDITIONS_VALUES: [...whereValues, ...havingValues],
    };
  }

  private _orderByTypes = {
    'likes.asc': 'likes ASC',
    'likes.desc': 'likes DESC',
    'calories.asc': `CAST(kcal AS UNSIGNED) ASC`,
    'calories.desc': `CAST(kcal AS UNSIGNED) DESC`,
    'servings.asc': 'servings ASC',
    'servings.desc': 'servings DESC',
    'name.asc': 'recipe.name ASC',
    'name.desc': 'recipe.name DESC',
    'id.asc': 'recipe.id ASC',
    'id.desc': 'recipe.id DESC',
  };

  async retrieveRecipes(
    queryParams: RetrieveRecipesQueryParamsDto,
    user?: IUser,
  ): Promise<RetrieveRecipesResponseDto> {
    this._validateQueryParams(queryParams, user);

    const { EXTRA_FIELDS_SQL, EXTRA_FIELDS_VALUES } =
      this._generateExtraFields(user);

    const {
      EXTRA_JOINS_SQL,
      WHERE_CONDITIONS_SQL,
      HAVING_CONDITIONS_SQL,
      CONDITIONS_VALUES,
    } = this._generateConditions(queryParams, user);

    const { order_by: orderBy } = queryParams;

    const ORDER_BY_SQL = this._orderByTypes[orderBy]
      ? `ORDER BY ${this._orderByTypes[orderBy]}`
      : '';

    const { page } = queryParams;

    const { items_per_page: recipesPerPage } = queryParams;

    const RECIPES_SQL = `
      SELECT
        recipe.id AS id,
        user.username AS creator,
        recipe.name AS name,
        recipe.servings AS servings,
        recipe_picture.path AS picturePath,
        (
          SELECT
            COUNT(*)
          FROM user_liked_recipe
          WHERE recipe_id = recipe.id
        ) AS likes,
        (
          SELECT
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'id', recipe_tag.id,
                'name', recipe_tag.name
              )
            )
          FROM recipe_has_recipe_tag
          INNER JOIN recipe_tag
            ON recipe_has_recipe_tag.recipe_tag_id = recipe_tag.id
          WHERE recipe_has_recipe_tag.recipe_id = recipe.id
        ) AS tags,
        ${this._SELECT_CALORIES_FROM_RECIPE} AS kcal
        ${EXTRA_FIELDS_SQL}
      FROM recipe
      LEFT JOIN user
        ON recipe.user_person_id = user.person_id
      LEFT JOIN recipe_picture
        ON recipe.id = recipe_picture.recipe_id
      LEFT JOIN user_liked_recipe
        ON recipe.id = user_liked_recipe.recipe_id
      ${EXTRA_JOINS_SQL}
      ${WHERE_CONDITIONS_SQL}
      GROUP BY
        recipe.id,
        user.username,
        recipe.name,
        recipe.servings,
        recipe_picture.path
      ${HAVING_CONDITIONS_SQL}
      ${ORDER_BY_SQL}
      LIMIT ? OFFSET ?;
    `;

    const parameters = [
      ...EXTRA_FIELDS_VALUES,
      ...CONDITIONS_VALUES,
      recipesPerPage,
      (page - 1) * recipesPerPage,
    ];

    const recipes = await this._entityManager
      .query(RECIPES_SQL, parameters)
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!recipes || recipes.length === 0) return { data: [] };

    const data = recipes.map((recipe: any) => {
      if (recipe.picturePath) {
        recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/${recipe.picturePath}`;
      } else {
        recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/recipes/default/picture`;
      }

      delete recipe.picturePath;

      return new RetrieveRecipes_Recipe(recipe);
    });

    const COUNT_SQL = `
      SELECT
        COUNT(*) AS count
      FROM (
        SELECT
          recipe.id,
          recipe.servings,
          ${this._SELECT_CALORIES_FROM_RECIPE} AS kcal
          ${EXTRA_FIELDS_SQL}
        FROM recipe
        LEFT JOIN user
          ON recipe.user_person_id = user.person_id
        LEFT JOIN recipe_picture
          ON recipe.id = recipe_picture.recipe_id
        LEFT JOIN user_liked_recipe
          ON recipe.id = user_liked_recipe.recipe_id
        ${EXTRA_JOINS_SQL}
        ${WHERE_CONDITIONS_SQL}
        GROUP BY
          recipe.id
        ${HAVING_CONDITIONS_SQL}
      ) AS recipe
    `;

    const [{ count }] = await this._entityManager
      .query(COUNT_SQL, parameters)
      .catch(() => {
        throw new BadGatewayException();
      });

    return {
      data,
      actualPage: page,
      totalPages: Math.ceil(count / recipesPerPage),
    };
  }

  async retrieveRecipesCount(): Promise<SimpleNumberDto> {
    const SQL =
      'SELECT COUNT(*) AS count FROM recipe WHERE was_deleted = false';

    const [{ count }] = await this._entityManager.query(SQL).catch(() => {
      throw new BadGatewayException();
    });

    return { data: Number(count) };
  }

  private _sortRecommendedRecipes(recipes: any[]) {
    const maxQuantity = 12;

    const iterations =
      recipes.length < maxQuantity ? recipes.length : maxQuantity;

    const recommendedRecipes = [];

    for (let i = 0; i < iterations; i++) {
      let index: number;

      const weights = recipes.map(
        (recipe: any, i: number) => recipes.length - i,
      );

      for (index = 1; index < recipes.length; index++) {
        weights[index] = weights[index] + weights[index - 1];
      }

      const random = Math.random() * weights[weights.length - 1];

      for (index = 0; index < weights.length; index++) {
        if (weights[index] > random) break;
      }

      recommendedRecipes.push(...recipes.splice(index, 1));
    }

    return recommendedRecipes;
  }

  async retrieveRecommendations(user?: IUser) {
    const USER_LIKED_SQL = `
      EXISTS(
        SELECT * FROM user_liked_recipe
        WHERE user_person_id = ? AND recipe_id = recipe.id
      ) AS isLiked
    `;

    const RECIPES_SQL = `
      SELECT * FROM
      (
        SELECT
          recipe.id AS id,
          user.username AS creator,
          recipe.name AS name,
          recipe.servings AS servings,
          recipe_picture.path AS picturePath,
          (
            SELECT
              COUNT(*)
            FROM user_liked_recipe
            WHERE recipe_id = recipe.id
          ) AS likes,
          (
            SELECT
              JSON_ARRAYAGG(
                JSON_OBJECT(
                  'id', recipe_tag.id,
                  'name', recipe_tag.name
                )
              )
            FROM recipe_has_recipe_tag
            INNER JOIN recipe_tag
              ON recipe_has_recipe_tag.recipe_tag_id = recipe_tag.id
            WHERE recipe_has_recipe_tag.recipe_id = recipe.id
          ) AS tags,
          ${this._SELECT_CALORIES_FROM_RECIPE} AS kcal
          ${user?.id ? `,${USER_LIKED_SQL}` : ''}
        FROM recipe
        LEFT JOIN user
          ON recipe.user_person_id = user.person_id
        LEFT JOIN recipe_picture
          ON recipe.id = recipe_picture.recipe_id
        LEFT JOIN user_liked_recipe
          ON recipe.id = user_liked_recipe.recipe_id
        WHERE recipe.was_deleted = false
        GROUP BY
          recipe.id,
          user.username,
          recipe.name,
          recipe.servings,
          recipe_picture.path
        ORDER BY likes
        LIMIT 20
      ) AS results
      ORDER BY results.id DESC
    `;

    const recipes = await this._entityManager
      .query(RECIPES_SQL, user?.id ? [user?.id] : undefined)
      .catch(() => {
        throw new BadGatewayException();
      });

    const data = this._sortRecommendedRecipes(recipes).map((recipe: any) => {
      if (recipe.picturePath) {
        recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/${recipe.picturePath}`;
      } else {
        recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/recipes/default/picture`;
      }

      delete recipe.picturePath;

      return new RetrieveRecipes_Recipe(recipe);
    });

    return { data };
  }

  private async _retrieveRecipesTagsByIds(ids: number[]) {
    const TAGS_BY_ID_SQL = `
      SELECT
        id,
        name
      FROM recipe_tag
      WHERE id IN (${ids.map(() => '?').join(',')})
    `;

    const tags = await this._entityManager
      .query(TAGS_BY_ID_SQL, ids)
      .catch(() => {
        throw new BadGatewayException();
      });

    return { data: tags ?? [] };
  }

  async retrieveRecipesTags(
    queryParams: RetrieveRecipesTagsQueryParamsDto,
  ): Promise<RetrieveRecipesTagsResponseDto> {
    const { page, search_by_name: searchByName, ids } = queryParams;

    if (ids) return this._retrieveRecipesTagsByIds(ids);

    const { items_per_page: tagsPerPage } = queryParams;

    const TAGS_SQL = `
      SELECT 
        id,
        name
      FROM recipe_tag
      ${searchByName ? 'WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE)' : ''}
      LIMIT ? OFFSET ?
    `;

    const parameters: any[] = [];

    if (searchByName) {
      const searchTerms = searchByName.replace(/ +/g, '*+*');

      parameters.push(`*${searchTerms}*`);
    }

    parameters.push(tagsPerPage, (page - 1) * tagsPerPage);

    const tags = await this._entityManager
      .query(TAGS_SQL, parameters)
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!tags || tags.length === 0) return { data: [] };

    const COUNT_SQL = `
      SELECT
        COUNT(*) AS count
      FROM recipe_tag
      ${searchByName ? 'WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE)' : ''}
    `;

    const [{ count }] = await this._entityManager
      .query(COUNT_SQL, parameters)
      .catch(() => {
        throw new BadGatewayException();
      });

    return {
      data: tags,
      actualPage: page,
      totalPages: Math.ceil(count / tagsPerPage),
    };
  }

  async retrieveRecipeTag(
    params: RetrieveRecipeTagParamsDto,
  ): Promise<RetrieveRecipeTagResponseDto> {
    const { id } = params;

    const TAG_SQL = 'SELECT id, name FROM recipe_tag WHERE id = ?';

    const [tag] = await this._entityManager.query(TAG_SQL, [id]).catch(() => {
      throw new BadGatewayException();
    });

    if (!tag) throw new NotFoundException();

    return { data: tag };
  }

  async retrieveRecipe(
    params: RetrieveRecipeParamsDto,
    user?: IUser,
  ): Promise<RetrieveRecipeResponseDto> {
    const { EXTRA_FIELDS_SQL, EXTRA_FIELDS_VALUES } =
      this._generateExtraFields(user);

    const RECIPE_SQL = `
      SELECT
        recipe.id AS id,
        user.username AS creator,
        recipe.name AS name,
        ${this._SELECT_CALORIES_FROM_RECIPE} AS kcal,
        recipe.servings AS servings,
        recipe_picture.path AS picturePath,
        (
          SELECT
            COUNT(*)
          FROM user_liked_recipe
          WHERE recipe_id = recipe.id
        ) AS likes,
        (
          SELECT
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'id', recipe_tag.id,
                'name', recipe_tag.name
              )
            )
          FROM recipe_has_recipe_tag
          INNER JOIN recipe_tag
            ON recipe_has_recipe_tag.recipe_tag_id = recipe_tag.id
          WHERE recipe_has_recipe_tag.recipe_id = recipe.id
        ) AS tags,
        (
          SELECT
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'id', food.id,
                'name', food.name,
                'quantity', recipe_has_food.quantity
              )
            )
          FROM recipe_has_food
          INNER JOIN food
            ON recipe_has_food.food_id = food.id
          WHERE recipe_has_food.recipe_id = recipe.id
        ) AS ingredients,
        (
          SELECT
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'lexoRank', lexo_rank,
                'description', description
              )
            )
          FROM recipe_step_by_step
          WHERE recipe_id = recipe.id
        ) AS stepBySteps
        ${EXTRA_FIELDS_SQL}
      FROM recipe 
      LEFT JOIN user
        ON recipe.user_person_id = user.person_id
      LEFT JOIN recipe_has_food
        ON recipe.id = recipe_has_food.recipe_id
      LEFT JOIN food
        ON recipe_has_food.food_id = food.id
      LEFT JOIN recipe_picture
        ON recipe.id = recipe_picture.recipe_id
      WHERE recipe.id = ? AND recipe.was_deleted = false
      GROUP BY
        recipe.id,
        user.username,
        recipe.name,
        recipe.servings,
        recipe_picture.path
    `;

    const { id } = params;

    const parameters = [...EXTRA_FIELDS_VALUES, id];

    const [recipe] = await this._entityManager
      .query(RECIPE_SQL, parameters)
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!recipe) throw new NotFoundException();

    if (recipe.picturePath) {
      recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/${recipe.picturePath}`;
    } else {
      recipe.pictureUrl = `${this._cloudinaryService.cloudUrl}/recipes/default/picture`;
    }

    delete recipe.picturePath;

    const data = new RetrieveRecipe_Recipe(recipe);

    return {
      data,
    };
  }

  async retrieveIngredients(
    params: RetrieveIngredientsParamsDto,
    user?: IUser,
  ): Promise<RetrieveIngredientsResponseDto> {
    const { id } = params;

    const INGREDIENTS_SQL = `
      SELECT
        food.id AS id,
        food.name AS name,
        food.kcal AS kcalPer100,
        recipe_has_food.quantity AS quantity,
        food_category.name AS category,
        JSON_ARRAYAGG(
          JSON_OBJECT(
            'id', nutrient.id,
            'name', nutrient.name,
            'quantityPer100', IFNULL(food_has_nutrient.quantity, food_has_nutrient.flag),
            'unit', food_has_nutrient.unit,
            'category', nutrient_category.name,
            'intakes', (
              SELECT
                JSON_ARRAYAGG(
                  JSON_OBJECT(
                    daily_intake_age_group.name, JSON_OBJECT(
                      nutrient_has_daily_intake.gender, JSON_OBJECT(
                        'quantity', nutrient_has_daily_intake.quantity,
                        'unit', nutrient_has_daily_intake.unit
                      )
                    )
                  )
                ) AS intakes
              FROM nutrient_has_daily_intake
              INNER JOIN daily_intake_age_group
                ON nutrient_has_daily_intake.daily_intake_age_group_id = daily_intake_age_group.id
              WHERE nutrient_has_daily_intake.nutrient_id = nutrient.id
            ),
            'personalIntakes', IF(
              nutrient_personal_intake.nutrient_id IS NOT NULL,
              JSON_OBJECT(
                'kcalPerGram', nutrient_personal_intake.kcal_per_gram,
                'tdeePercentage', nutrient_personal_intake.tdee_percentage
              ),
              null
            )
          )
        ) AS nutrients
      FROM recipe
      INNER JOIN recipe_has_food
        ON recipe.id = recipe_has_food.recipe_id
      INNER JOIN food
        ON recipe_has_food.food_id = food.id
      INNER JOIN food_category
        ON food.food_category_id = food_category.id
      INNER JOIN food_has_nutrient
        ON food.id = food_has_nutrient.food_id
      INNER JOIN nutrient
        ON food_has_nutrient.nutrient_id = nutrient.id
      LEFT JOIN nutrient_personal_intake
        ON nutrient.id =  nutrient_personal_intake.nutrient_id
      LEFT JOIN nutrient_category
        ON nutrient.nutrient_category_id = nutrient_category.id
      WHERE recipe.id = ?
      GROUP BY
        food.id,
        food.name,
        food.kcal,
        recipe_has_food.quantity,
        food_category.name
    `;

    const ingredients = await this._entityManager
      .query(INGREDIENTS_SQL, [id])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!ingredients || ingredients.length === 0) throw new NotFoundException();

    let userTDEE: number | null | undefined;

    if (user) userTDEE = await retrieveUserTDEE(this._entityManager, user);

    ingredients.forEach((ingredient: any) => {
      ingredient.nutrients.forEach((nutrient: any, i: number) => {
        const intakes: NutrientIntakesDto = {};

        if (userTDEE && nutrient.personalIntakes) {
          const { kcalPerGram, tdeePercentage } = nutrient.personalIntakes;

          intakes.personal = {
            quantity: Math.round(
              (userTDEE * (tdeePercentage / 100)) / kcalPerGram,
            ),
            unit: 'g',
          };

          nutrient.intakes = intakes;
        }

        delete nutrient.personalIntakes;

        if (!intakes.personal && nutrient.intakes) {
          nutrient.intakes = merge(intakes, ...nutrient.intakes);
        }

        ingredient.nutrients[i] = new NutrientPer100GramsDto(nutrient);
      });

      sortNutrients(ingredient.nutrients);
    });

    return {
      data: ingredients,
    };
  }

  async retrieveNutrients(
    params: RetrieveNutrientsParamsDto,
    user?: IUser,
  ): Promise<RetrieveNutrientsResponseDto> {
    const { id } = params;

    const NUTRIENTS_SQL = `
      SELECT
        nutrient.id AS id,
        nutrient.name AS name, 
        nutrient_category.name AS category,
        JSON_ARRAYAGG(
          JSON_OBJECT(
            'nutrientQuantity', IFNULL(food_has_nutrient.quantity, food_has_nutrient.flag),
            'foodQuantity', recipe_has_food.quantity,
            'unit', food_has_nutrient.unit
          )
        ) AS quantity,
        (
          SELECT
            JSON_ARRAYAGG(
              JSON_OBJECT(
                daily_intake_age_group.name, JSON_OBJECT(
                  nutrient_has_daily_intake.gender, JSON_OBJECT(
                    'quantity', nutrient_has_daily_intake.quantity,
                    'unit', nutrient_has_daily_intake.unit
                  )
                )
              )
            ) AS intakes
          FROM nutrient_has_daily_intake
          INNER JOIN daily_intake_age_group
            ON nutrient_has_daily_intake.daily_intake_age_group_id = daily_intake_age_group.id
          WHERE nutrient_has_daily_intake.nutrient_id = nutrient.id
        ) AS intakes,
        IF(
          nutrient_personal_intake.nutrient_id IS NOT NULL,
          JSON_OBJECT(
            'kcalPerGram', nutrient_personal_intake.kcal_per_gram,
            'tdeePercentage', nutrient_personal_intake.tdee_percentage
          ),
          null
        ) AS personalIntakes
      FROM recipe
      INNER JOIN recipe_has_food
        ON recipe.id = recipe_has_food.recipe_id
      INNER JOIN food_has_nutrient
        ON recipe_has_food.food_id = food_has_nutrient.food_id
      INNER JOIN nutrient
        ON food_has_nutrient.nutrient_id = nutrient.id
      LEFT JOIN nutrient_personal_intake
        ON nutrient.id =  nutrient_personal_intake.nutrient_id
      LEFT JOIN nutrient_category
        ON nutrient.nutrient_category_id = nutrient_category.id
      WHERE recipe.id = ?
      GROUP BY
        nutrient.id,
        nutrient.name, 
        nutrient.nutrient_category_id,
        personalIntakes
    `;

    const nutrients = await this._entityManager
      .query(NUTRIENTS_SQL, [id])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!nutrients || nutrients.length === 0) throw new NotFoundException();

    let userTDEE: number | null | undefined;

    if (user) userTDEE = await retrieveUserTDEE(this._entityManager, user);

    mergeAndSumIntakesAndNutrientsQuantity(nutrients, userTDEE);

    return {
      data: nutrients,
    };
  }

  async createRecipe(
    recipe: CreateRecipeDto,
    user: IUser,
  ): Promise<RetrieveRecipeResponseDto> {
    let recipeId = null;

    await this._entityManager
      .transaction(async (transaction) => {
        const RECIPE_SQL =
          'INSERT INTO recipe (user_person_id, name, servings) VALUES (?, ?, ?)';

        await transaction.query(RECIPE_SQL, [
          user?.id,
          recipe.name,
          recipe.servings,
        ]);

        [{ recipeId }] = await transaction.query(
          'SELECT LAST_INSERT_ID() AS recipeId',
        );

        const INGREDIENTS_SQL = `
          INSERT INTO
            recipe_has_food (recipe_id, food_id, quantity)
          VALUES ${Array(recipe.ingredients.length).fill('(?, ?, ?)').join(',')}
        `;

        await transaction.query(
          INGREDIENTS_SQL,
          recipe.ingredients.flatMap((ingredient) => [
            recipeId,
            ingredient.id,
            ingredient.quantity,
          ]),
        );

        const STEP_BY_STEPS_SQL = `
          INSERT INTO
            recipe_step_by_step (lexo_rank, recipe_id, description)
          VALUES ${Array(recipe.stepBySteps.length)
            .fill('(?, ?, ?)')
            .join(', ')}
        `;

        let lexoRank = LexoRank.middle();

        await transaction.query(
          STEP_BY_STEPS_SQL,
          recipe.stepBySteps.flatMap((stepByStep) => [
            (lexoRank = lexoRank.genNext()).toString(),
            recipeId,
            stepByStep,
          ]),
        );

        if (!recipe.tagsIds || recipe.tagsIds.length === 0) return;

        const TAGS_SQL = `
          INSERT INTO
            recipe_has_recipe_tag (recipe_id, recipe_tag_id)
          VALUES ${Array(recipe.tagsIds.length).fill('(?, ?)').join(',')}
        `;

        await transaction.query(
          TAGS_SQL,
          recipe.tagsIds.flatMap((tagId) => [recipeId, tagId]),
        );
      })
      .catch((error) => {
        if (error.message.includes('FK_RecipeHasRecipeTag_RecipeTag'))
          throw new ConflictException('Tag não existe');

        if (error.message.includes('FK_RecipeHasFood_Food'))
          throw new ConflictException('Ingrediente não existe');

        throw new BadGatewayException();
      });

    if (!recipeId) throw new InternalServerErrorException();

    return await this.retrieveRecipe({ id: recipeId }, user);
  }

  private async _checkIfUserOwnsRecipe(recipeId: number, user: IUser) {
    const CREATOR_SQL =
      'SELECT user_person_id AS id FROM recipe WHERE id = ? AND was_deleted = false';

    const [creator] = await this._entityManager
      .query(CREATOR_SQL, [recipeId])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!creator) throw new NotFoundException();

    if (creator.id !== user?.id) throw new ForbiddenException();
  }

  async uploadRecipePicture(
    params: UploadRecipePictureParamsDto,
    user: IUser,
    image: Express.Multer.File,
  ) {
    const { id } = params;

    await this._checkIfUserOwnsRecipe(id, user);

    const options: UploadImageOptions = {
      folder: `recipes/${id}`,
      fileName: 'picture',
      generateRandomSuffix: false,
    };

    const result = await this._cloudinaryService.uploadImage(image, options);

    const { public_id: imagePath } = result;

    const PICTURE_SQL =
      'INSERT INTO recipe_picture (recipe_id, path) VALUES (?, ?)';

    await this._entityManager
      .query(PICTURE_SQL, [id, imagePath])
      .catch((error) => {
        if (error.message.includes('UQ_RecipePicture_Path')) return;

        throw new BadGatewayException();
      });

    return {
      statusCode: '200',
      message: 'OK',
    };
  }

  async addLikeToRecipe(params: AddLikeToRecipeDto, user: IUser) {
    const { id } = params;
    const { id: userId } = user;

    const LIKE_SQL =
      'INSERT INTO user_liked_recipe (user_person_id, recipe_id) VALUES (?, ?)';

    await this._entityManager.query(LIKE_SQL, [userId, id]).catch((error) => {
      if (error.message.includes('Duplicate'))
        throw new ConflictException('Receita já está favoritada');

      if (error.message.includes('FK_UserLikedRecipe_Recipe'))
        throw new ConflictException('Receita não existe');

      if (error.message.includes('FK_UserLikedRecipe_User'))
        throw new ForbiddenException();

      throw new BadGatewayException();
    });

    return {
      statusCode: 201,
      message: 'Created',
    };
  }

  private _updateIngredients(
    transaction: EntityManager,
    recipeId: number,
    ingredients: UpdateRecipe_Ingredient[],
  ): Promise<any>[] {
    const queries = [];

    const toCreateOrUpdate = ingredients.filter(
      (ingredient) => ingredient.delete !== true,
    );

    const toDelete = ingredients.filter((ingredient) => ingredient.delete);

    if (toCreateOrUpdate.length !== 0) {
      const TO_CREATE_OR_UPDATE_SQL = `
        INSERT INTO recipe_has_food (recipe_id, food_id, quantity)
        VALUES ${Array(toCreateOrUpdate.length).fill('(?, ?, ?)').join(',')}
        ON DUPLICATE KEY
          UPDATE quantity = VALUES(quantity);
      `;

      const query = transaction.query(
        TO_CREATE_OR_UPDATE_SQL,
        toCreateOrUpdate.flatMap((ingredient) => [
          recipeId,
          ingredient.id,
          ingredient.quantity,
        ]),
      );

      queries.push(query);
    }

    if (toDelete.length !== 0) {
      const query = new Promise<void>(async (resolve, reject) => {
        try {
          const TO_DELETE_SQL = `
            DELETE FROM recipe_has_food WHERE recipe_id = ?
              AND food_id IN (${Array(toDelete.length).fill('?').join(',')})
          `;

          await transaction.query(TO_DELETE_SQL, [
            recipeId,
            ...toDelete.map((ingredient) => ingredient.id),
          ]);

          const COUNT_FOODS_SQL = `
            SELECT
              COUNT(*) AS count
            FROM recipe_has_food
            WHERE recipe_id = ?
          `;

          const [{ count }] = await transaction.query(COUNT_FOODS_SQL, [
            recipeId,
          ]);

          if (Number(count) !== 0) resolve();

          throw {
            type: 'Bad request',
            badRequestMessage: 'Receita deve conter pelo menos um ingrediente',
          };
        } catch (error) {
          reject(error);
        }
      });

      queries.push(query);
    }

    return queries;
  }

  private _updateStepBySteps(
    transaction: EntityManager,
    recipeId: number,
    stepBySteps: UpdateRecipe_StepByStep[],
  ): Promise<any>[] {
    const queries = [];

    const toCreateOrUpdate = stepBySteps.filter(
      (stepByStep) => stepByStep.delete !== true,
    );

    const toDelete = stepBySteps.filter((stepByStep) => stepByStep.delete);

    if (toCreateOrUpdate.length !== 0) {
      const TO_CREATE_OR_UPDATE_SQL = `
      INSERT INTO recipe_step_by_step (recipe_id, lexo_rank, description)
        VALUES ${Array(toCreateOrUpdate.length).fill('(?, ?, ?)').join(',')}
        ON DUPLICATE KEY
          UPDATE lexo_rank = VALUES(lexo_rank), description = VALUES(description);
      `;

      const query = transaction.query(
        TO_CREATE_OR_UPDATE_SQL,
        toCreateOrUpdate.flatMap((ingredient) => [
          recipeId,
          ingredient.lexoRank,
          ingredient.description,
        ]),
      );

      queries.push(query);
    }

    if (toDelete.length !== 0) {
      const query = new Promise<void>(async (resolve, reject) => {
        try {
          const TO_DELETE_SQL = `
            DELETE FROM recipe_step_by_step WHERE recipe_id = ?
              AND lexo_rank IN (${Array(toDelete.length).fill('?').join(',')})
          `;

          await transaction.query(TO_DELETE_SQL, [
            recipeId,
            ...toDelete.map((stepByStep) => stepByStep.lexoRank),
          ]);

          const COUNT_STEP_BY_STEPS_SQL = `
            SELECT
              COUNT(*) AS count
            FROM recipe_step_by_step
            WHERE recipe_id = ?
          `;

          const [{ count }] = await transaction.query(COUNT_STEP_BY_STEPS_SQL, [
            recipeId,
          ]);

          if (Number(count) !== 0) resolve();

          throw {
            type: 'Bad request',
            badRequestMessage:
              'Receita deve conter pelo menos um passo a passo',
          };
        } catch (error) {
          reject(error);
        }
      });

      queries.push(query);
    }

    return queries;
  }

  private _updateTags(
    transaction: EntityManager,
    recipeId: number,
    tagsIds: number[],
  ): Promise<void> {
    return new Promise<void>(async (resolve, reject) => {
      try {
        const DELETE_TAGS_SQL =
          'DELETE FROM recipe_has_recipe_tag WHERE recipe_id = ?';

        await transaction.query(DELETE_TAGS_SQL, [recipeId]);

        if (tagsIds.length === 0) return resolve();

        const INSERT_TAGS_SQL = `
          INSERT INTO
            recipe_has_recipe_tag (recipe_id, recipe_tag_id)
          VALUES ${Array(tagsIds.length).fill('(?, ?)').join(',')}
        `;

        await transaction.query(
          INSERT_TAGS_SQL,
          tagsIds.flatMap((tagId) => [recipeId, tagId]),
        );

        resolve();
      } catch (error) {
        reject(error);
      }
    });
  }

  async updateRecipe(
    params: UpdateRecipeParamsDto,
    recipe: UpdateRecipeDto,
    user: IUser,
  ): Promise<RetrieveRecipeResponseDto> {
    if (isEmpty(recipe))
      throw new BadRequestException('Body não deve estar vazio');

    const { id: recipeId } = params;

    await this._checkIfUserOwnsRecipe(recipeId, user);

    await this._entityManager
      .transaction(async (transaction) => {
        const queries = [];

        if (recipe.name || recipe.servings) {
          const recipeFields = [
            recipe.name ? 'name = ?' : null,
            recipe.servings ? 'servings = ?' : null,
          ].filter((field) => !!field);

          const RECIPE_SQL = `
            UPDATE recipe SET
              ${recipeFields.join(',')}
            WHERE id = ?
          `;

          const recipeParameters = [
            recipe.name,
            recipe.servings,
            recipeId,
          ].filter((parameter) => !!parameter);

          const query = transaction.query(RECIPE_SQL, recipeParameters);

          queries.push(query);
        }

        if (recipe.ingredients && recipe.ingredients.length !== 0) {
          const _queries = this._updateIngredients(
            transaction,
            recipeId,
            recipe.ingredients,
          );

          queries.push(..._queries);
        }

        if (recipe.stepBySteps && recipe.stepBySteps.length !== 0) {
          const _queries = this._updateStepBySteps(
            transaction,
            recipeId,
            recipe.stepBySteps,
          );

          queries.push(..._queries);
        }

        if (recipe.tagsIds) {
          const query = this._updateTags(transaction, recipeId, recipe.tagsIds);

          queries.push(query);
        }

        await Promise.all(queries);
      })
      .catch((error) => {
        if (error?.type === 'Bad request')
          throw new BadRequestException(error.badRequestMessage);

        if (error?.message.includes('FK_RecipeHasRecipeTag_RecipeTag'))
          throw new ConflictException('Tag não existe');

        if (error?.message.includes('FK_RecipeHasFood_Food'))
          throw new ConflictException('Ingrediente não existe');

        throw new BadGatewayException();
      });

    return this.retrieveRecipe({ id: recipeId }, user);
  }

  private async _deleteRecipePartially(id: number, userId: number) {
    await this._entityManager
      .transaction(async (transaction) => {
        const RECIPE_CHILD_TABLES_SQL = `
          DELETE
            recipe_has_recipe_tag,
            recipe_step_by_step,
            recipe_picture,
            user_liked_recipe
          FROM recipe
          LEFT JOIN recipe_has_recipe_tag
            ON recipe.id = recipe_has_recipe_tag.recipe_id
          LEFT JOIN recipe_step_by_step
            ON recipe.id = recipe_step_by_step.recipe_id
          LEFT JOIN recipe_picture
            ON recipe.id = recipe_picture.recipe_id
          LEFT JOIN user_liked_recipe
            ON recipe.id = user_liked_recipe.recipe_id
          WHERE recipe.id = ? AND recipe.user_person_id = ?
        `;

        await transaction.query(RECIPE_CHILD_TABLES_SQL, [id, userId]);

        const RECIPE_SQL =
          'UPDATE recipe SET was_deleted = true WHERE id = ? AND user_person_id = ?';

        await transaction.query(RECIPE_SQL, [id, userId]);
      })
      .catch(() => {
        throw new BadGatewayException();
      });
  }

  private async _deleteRecipeCompletely(id: number, userId: number) {
    await this._entityManager
      .transaction(async (transaction) => {
        const RECIPE_CHILD_TABLES_SQL = `
          DELETE
            recipe_has_recipe_tag,
            recipe_has_food,
            recipe_step_by_step,
            recipe_picture,
            user_liked_recipe
          FROM recipe
          LEFT JOIN recipe_has_recipe_tag
            ON recipe.id = recipe_has_recipe_tag.recipe_id
          LEFT JOIN recipe_has_food
            ON recipe.id = recipe_has_food.recipe_id
          LEFT JOIN recipe_step_by_step
            ON recipe.id = recipe_step_by_step.recipe_id
          LEFT JOIN recipe_picture
            ON recipe.id = recipe_picture.recipe_id
          LEFT JOIN user_liked_recipe
            ON recipe.id = user_liked_recipe.recipe_id
          WHERE recipe.id = ? AND recipe.user_person_id = ?
        `;

        await transaction.query(RECIPE_CHILD_TABLES_SQL, [id, userId]);

        const RECIPE_SQL =
          'DELETE FROM recipe WHERE id = ? AND user_person_id = ?';

        await transaction.query(RECIPE_SQL, [id, userId]);
      })
      .catch(() => {
        throw new BadGatewayException();
      });
  }

  async deleteRecipe(
    params: DeleteRecipeParamsDto,
    user: IUser,
  ): Promise<RetrieveRecipeResponseDto> {
    const { id } = params;

    await this._checkIfUserOwnsRecipe(id, user);

    const response = await this.retrieveRecipe({ id }, user);

    const IS_PART_OF_A_MEAL_SQL = `
      SELECT EXISTS(
        SELECT recipe_id FROM meal_has_recipe WHERE recipe_id = ?
      ) AS recipeIsPartOfAMeal
    `;

    const [{ recipeIsPartOfAMeal }] = await this._entityManager
      .query(IS_PART_OF_A_MEAL_SQL, [id])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!!+recipeIsPartOfAMeal) {
      await this._deleteRecipePartially(id, user.id);
    } else {
      await this._deleteRecipeCompletely(id, user.id);
    }

    return response;
  }

  async removeLikeFromRecipe(
    params: RemoveLikeFromRecipeParamsDto,
    user: IUser,
  ) {
    const { id } = params;
    const { id: userId } = user;

    const LIKE_SQL =
      'DELETE FROM user_liked_recipe WHERE user_person_id = ? AND recipe_id = ?';

    await this._entityManager.query(LIKE_SQL, [userId, id]).catch(() => {
      throw new BadGatewayException();
    });

    return {
      statusCode: 200,
      message: 'OK',
    };
  }

  async deleteRecipePicture(params: DeleteRecipePictureParamsDto, user: IUser) {
    const { id } = params;

    await this._checkIfUserOwnsRecipe(id, user);

    const PATH_SQL = 'SELECT path FROM recipe_picture WHERE recipe_id = ?';

    const [{ path }] = await this._entityManager
      .query(PATH_SQL, [id])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!path) {
      return {
        statusCode: 200,
        message: 'OK',
      };
    }

    await this._cloudinaryService.deleteImage(path);

    const PICTURE_SQL = 'DELETE FROM recipe_picture WHERE recipe_id = ?';

    await this._entityManager.query(PICTURE_SQL, [id]).catch(() => {
      throw new BadGatewayException();
    });

    return {
      statusCode: 200,
      message: 'OK',
    };
  }
}
