import {
  BadGatewayException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import {
  RetrieveFoodsQueryParamsDto,
  RetrieveFoodsResponseDto,
} from './dtos/retrieve-foods.dto';
import { EntityManager } from 'typeorm';
import { SingleIdParamsDto } from '../../shared/dtos/params/single-id-params.dto';
import {
  RetrieveFoodResponseDto,
  RetrieveFood_Food,
} from './dtos/retrieve-food.dto';
import { NutrientPer100GramsDto } from '../../shared/dtos/nutrients/nutrient-per-100-grams.dto';
import { sortNutrients } from '../../shared/functions/nutrients/sort-nutrients.function';
import merge from 'lodash/merge';
import { SimpleFood } from '../../shared/dtos/foods/simple-food.dto';
import { IUser } from '../../shared/decorators/auth/user.decorator';
import { RetrieveFrequentFoods } from './dtos/retrieve-frequent-foods.dto';
import { retrieveUserTDEE } from '../../shared/functions/user/retrieve-user-tdee.function';
import { NutrientIntakesDto } from '../../shared/dtos/nutrients/nutrient-intakes.dto';

@Injectable()
export class FoodsService {
  constructor(private readonly _entityManager: EntityManager) {}

  private async _retrieveFoodsByIds(ids: number[]) {
    const TAGS_BY_ID_SQL = `
      SELECT
        id,
        name,
        IFNULL(kcal, kcal_flag) as kcalPer100
      FROM food
      WHERE id IN (${ids.map(() => '?').join(',')})
    `;

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

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

  async retrieveFoods(
    queryParams: RetrieveFoodsQueryParamsDto,
  ): Promise<RetrieveFoodsResponseDto> {
    const { page, search_by_name: searchByName, ids } = queryParams;

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

    const { items_per_page: foodsPerPage } = queryParams;

    const FOODS_SQL = `
      SELECT
        id,
        name,
        IFNULL(kcal, kcal_flag) as kcalPer100
      FROM food
      ${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(foodsPerPage, (page - 1) * foodsPerPage);

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

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

    const data = foods.map((food: SimpleFood) => new SimpleFood(food));

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

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

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

  private async _retrieveUserFrequentFoods(
    userId: number,
    quantity: number,
  ): Promise<number[]> {
    const USER_FREQUENT_FOODS_SQL = `
      SELECT
        id
      FROM (
          SELECT
            recipe_has_food.food_id AS id
          FROM recipe
          INNER JOIN recipe_has_food
            ON recipe.id = recipe_has_food.recipe_id
          WHERE recipe.user_person_id = ?

          UNION ALL
          
          SELECT
            food_id AS id
          FROM meal
          INNER JOIN meal_has_food
            ON meal.id = meal_has_food.meal_id
          WHERE meal.user_person_id = ?
      ) AS food_occurrences
      GROUP BY id
      ORDER BY COUNT(*) DESC
      LIMIT ?
    `;

    const foods: [{ id: number }] = await this._entityManager
      .query(USER_FREQUENT_FOODS_SQL, [userId, userId, quantity])
      .catch(() => {
        throw new BadGatewayException();
      });

    const ids = foods?.map((foodId) => foodId.id);

    return ids ?? [];
  }

  private async _retrieveGlobalFrequentFoods(
    quantity: number,
  ): Promise<number[]> {
    const GLOBAL_FREQUENT_FOODS_SQL = `
      SELECT
        id
      FROM (
          SELECT food_id AS id FROM recipe_has_food
          UNION ALL
          SELECT food_id AS id FROM meal_has_food
      ) AS food_occurrences
      GROUP BY id
      ORDER BY COUNT(*) DESC
      LIMIT ?
    `;

    const foods: [{ id: number }] = await this._entityManager
      .query(GLOBAL_FREQUENT_FOODS_SQL, [quantity])
      .catch(() => {
        throw new BadGatewayException();
      });

    const ids = foods?.map((foodId) => foodId.id);

    return ids ?? [];
  }

  async retrieveFrequentFoods(user?: IUser): Promise<RetrieveFrequentFoods> {
    const frequentFoodsQuantity = 5;

    let foodIds = [];

    if (user?.id) {
      const ids = await this._retrieveUserFrequentFoods(
        user.id,
        frequentFoodsQuantity,
      );

      foodIds = ids;
    }

    if (foodIds.length < frequentFoodsQuantity) {
      const ids = await this._retrieveGlobalFrequentFoods(
        frequentFoodsQuantity - foodIds.length,
      );

      foodIds = [...foodIds, ...ids];
    }

    if (foodIds.length === 0) return { data: [] };

    const FOODS_SQL = `
      SELECT
        id,
        name,
        IFNULL(kcal, kcal_flag) as kcalPer100
      FROM food
      WHERE id IN (${Array(foodIds.length).fill('?').join(', ')})
      ORDER BY FIELD(id, ${Array(foodIds.length).fill('?').join(', ')});
    `;

    const foods = await this._entityManager
      .query(FOODS_SQL, [...foodIds, ...foodIds])
      .catch(() => {
        throw new BadGatewayException();
      });

    const data = foods.map((food: SimpleFood) => new SimpleFood(food));

    return { data };
  }

  async retrieveFood(
    params: SingleIdParamsDto,
    user?: IUser,
  ): Promise<RetrieveFoodResponseDto> {
    const { id } = params;

    const FOOD_SQL = `
      SELECT
        food.id AS id,
        food.name AS name,
        IFNULL(food.kcal, food.kcal_flag) AS kcalPer100,
        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 food
      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 food.id = ?
    `;

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

    if (!food) throw new NotFoundException();

    let userTDEE: number | null | undefined;

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

    food.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);
      }

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

    sortNutrients(food.nutrients);

    const data = new RetrieveFood_Food(food);

    return {
      data,
    };
  }
}
