import {
  BadGatewayException,
  ConflictException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { IUser } from '../../shared/decorators/auth/user.decorator';
import { EntityManager } from 'typeorm';
import { RetrieveUserResponseDto } from './dtos/retrieve-user.dto';
import { JWTService } from '../../services/authentication/jwt/jwt.service';
import { CreateUserDto } from './dtos/create-user.dto';
import { Payload } from '../../services/authentication/jwt/interfaces/Payload';
import * as argon2 from 'argon2';
import { UploadImageOptions } from '../../services/image-management/cloudinary/interfaces/upload-image-options.interface';
import { CloudinaryService } from '../../services/image-management/cloudinary/cloudinary.service';
import { ExistsByEmailDto } from './dtos/exists-by-email.dto';
import { UpdateUserDto } from './dtos/update-user.dto';
import { SingleUsernameDto } from '../../shared/dtos/users/single-username.dto';
import { BMR_Formulas } from '../../shared/functions/user/bmr-formulas.function';
import { getAge } from '../../shared/functions/user/get-age.function';
import { TDEE_Formula } from '../../shared/functions/user/tdee-formula.funtion';
import { GoogleAuthService } from '../../services/authentication/google-auth/google-auth.service';
import { SimpleNumberDto } from '../../shared/dtos/simple-number.dto';
import { retrieveUserTDEE } from '../../shared/functions/user/retrieve-user-tdee.function';
import { RetrievePersonalIntakes } from './dtos/retrieve-personal-intakes.dto';

@Injectable()
export class UserService {
  constructor(
    private readonly _entityManager: EntityManager,
    private readonly _jwtService: JWTService,
    private readonly _cloudinaryService: CloudinaryService,
    private readonly _googleAuthService: GoogleAuthService,
  ) {}

  async retrieveUser(userPayload: IUser): Promise<RetrieveUserResponseDto> {
    const { id } = userPayload;

    const SQL = `
      SELECT
        person.id AS id,
        person.full_name AS fullName,
        person.weight AS weight,
        person.height AS height,
        person.birth_date AS birthDate,
        person.gender AS gender,
        person.goal_id AS goalId,
        person.physical_activity_id AS physicalActivityId,
        user.username AS username,
        user.email AS email,
        user.profile_picture_path AS profilePicturePath,
        goal.tdee_offset AS tdeeOffset,
        physical_activity.tdee_multiplier AS tdeeMultiplier
      FROM person
      INNER JOIN user
        ON person.id = user.person_id
      INNER JOIN physical_activity
        ON person.physical_activity_id = physical_activity.id
      INNER JOIN goal
        ON person.goal_id = goal.id
      WHERE person.id = ?
    `;

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

    if (!user) throw new NotFoundException();

    const age = getAge(user.birthDate);

    const heightInCentimeters = user.height * 100;

    user.basalMetabolicRate = Math.round(
      BMR_Formulas[user.gender](user.weight, heightInCentimeters, age),
    );

    user.totalDailyEnergyExpenditure = Math.round(
      TDEE_Formula(
        user.basalMetabolicRate,
        user.tdeeMultiplier,
        user.tdeeOffset,
      ),
    );

    if (user.profilePicturePath) {
      user.profilePictureUrl = `${this._cloudinaryService.cloudUrl}/${user.profilePicturePath}`;
    } else {
      user.profilePictureUrl = `${this._cloudinaryService.cloudUrl}/users/default/avatar`;
    }

    delete user.profilePicturePath;
    delete user.tdeeMultiplier;
    delete user.tdeeOffset;

    return { data: user };
  }

  async retrievePersonalIntakes(
    userPayload: IUser,
  ): Promise<RetrievePersonalIntakes> {
    const SQL = `
      SELECT
        nutrient.id AS id,
        nutrient.name AS name,
        nutrient_personal_intake.kcal_per_gram AS kcalPerGram,
        nutrient_personal_intake.tdee_percentage AS tdeePercentage
      FROM nutrient
      INNER JOIN nutrient_personal_intake
        ON nutrient.id = nutrient_personal_intake.nutrient_id
    `;

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

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

    const userTDEE = await retrieveUserTDEE(this._entityManager, userPayload);

    const data = nutrients.map((nutrient) => {
      const { id, name, kcalPerGram, tdeePercentage } = nutrient;

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

      return { id, name, intake };
    });

    return { data };
  }

  async countLikedRecipes(user: IUser): Promise<SimpleNumberDto> {
    const { id } = user;

    const COUNT_SQL =
      'SELECT COUNT(*) AS count FROM user_liked_recipe WHERE user_person_id = ?';

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

    return {
      data: Number(count),
    };
  }

  async existsByUsername(user: SingleUsernameDto) {
    const { username } = user;

    const SQL =
      'SELECT EXISTS(SELECT * FROM user WHERE username = ?) AS existsAsBinaryString';

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

    const exists = !!+existsAsBinaryString;

    if (!exists) throw new NotFoundException();

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

  async existsByEmail(user: ExistsByEmailDto) {
    const { email } = user;

    const SQL =
      'SELECT EXISTS(SELECT * FROM user WHERE email = ?) AS existsAsBinaryString';

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

    const exists = !!+existsAsBinaryString;

    if (!exists) throw new NotFoundException();

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

  private async _createUserQuery(
    user: CreateUserDto,
    googleId?: string,
  ): Promise<number> {
    return await this._entityManager
      .transaction(async (transaction) => {
        const PERSON_SQL =
          'INSERT INTO person (full_name, weight, height, birth_date, gender, goal_id, physical_activity_id) VALUES (?, ?, ?, ?, ?, ?, ?)';

        await transaction.query(PERSON_SQL, [
          user.fullName,
          user.weight,
          user.height,
          user.birthDate,
          user.gender,
          user.goalId,
          user.physicalActivityId,
        ]);

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

        const USER_SQL =
          'INSERT INTO user(person_id, google_id, username, email, password) VALUES (?, ?, ?, ?, ?)';

        const password = user.password
          ? await argon2.hash(user.password)
          : null;

        await transaction.query(USER_SQL, [
          id,
          googleId,
          user.username,
          user.email,
          password,
        ]);

        return Number(id);
      })
      .catch((error) => {
        if (error.message.includes('UQ_User_Username'))
          throw new ConflictException('Username já está em uso');

        if (error.message.includes('UQ_User_Email'))
          throw new ConflictException('Email já está em uso');

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

        if (error.message.includes('FK_Person_PhysicalActivity'))
          throw new ConflictException('Nível de atividade física não existe');

        throw new BadGatewayException();
      });
  }

  async _getUserByGoogleAccount(googleId: string, email: string) {
    const GOOGLE_ID_SQL =
      'SELECT person_id AS id FROM user WHERE google_id = ?';

    let [user] = await this._entityManager
      .query(GOOGLE_ID_SQL, [googleId])
      .catch(() => {
        throw new BadGatewayException();
      });

    if (!user && email) {
      const GOOGLE_EMAIL_SQL =
        'SELECT person_id AS id FROM user WHERE email = ?';

      [user] = await this._entityManager
        .query(GOOGLE_EMAIL_SQL, [email])
        .catch(() => {
          throw new BadGatewayException();
        });

      if (user) {
        const { id } = user;

        const UPDATE_GOOGLE_ID_SQL =
          'UPDATE user SET google_id = ? WHERE person_id = ?';

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

    return user;
  }

  async createUser(user: CreateUserDto) {
    const { googleToken } = user;

    const { googleId = null, email = null } = googleToken
      ? await this._googleAuthService.verify(googleToken)
      : {};

    const userByGoogleAccount = googleId
      ? await this._getUserByGoogleAccount(googleId, email)
      : null;

    if (userByGoogleAccount) {
      const { id } = userByGoogleAccount;

      const payload: Payload = { id, googleId };

      const accessToken = await this._jwtService.generateToken(payload);

      return {
        data: { accessToken },
      };
    }

    if (email) user.email = email;

    const id = await this._createUserQuery(user, googleId);

    const payload: Payload = { id, googleId };

    const accessToken = await this._jwtService.generateToken(payload);

    return {
      data: { accessToken },
    };
  }

  async updateUser(
    user: UpdateUserDto,
    userPayload: IUser,
  ): Promise<RetrieveUserResponseDto> {
    await this._entityManager
      .transaction(async (transaction) => {
        const { id } = userPayload;

        const personFields = [
          user.fullName ? 'full_name = ?' : null,
          user.weight ? 'weight = ?' : null,
          user.height ? 'height = ?' : null,
          user.birthDate ? 'birth_date = ?' : null,
          user.gender ? 'gender = ?' : null,
          user.goalId ? 'goal_id = ?' : null,
          user.physicalActivityId ? 'physical_activity_id = ?' : null,
        ].filter((field) => !!field);

        if (personFields.length > 0) {
          const PERSON_SQL = `
            UPDATE person SET
              ${personFields.join(',')}
            WHERE id = ?
          `;

          await transaction.query(
            PERSON_SQL,
            [
              user.fullName,
              user.weight,
              user.height,
              user.birthDate,
              user.gender,
              user.goalId,
              user.physicalActivityId,
              id,
            ].filter((field) => !!field),
          );
        }

        const userFields = [
          user.username ? 'username = ?' : null,
          user.email ? 'email = ?' : null,
          user.password ? 'password = ?' : null,
        ].filter((field) => !!field);

        if (userFields.length > 0) {
          const USER_SQL = `
            UPDATE user SET
              ${userFields.join(',')}
            WHERE person_id = ?
          `;

          const password = user.password
            ? await argon2.hash(user.password)
            : null;

          await transaction.query(
            USER_SQL,
            [user.username, user.email, password, id].filter(
              (field) => !!field,
            ),
          );
        }
      })
      .catch((error) => {
        if (error.message.includes('UQ_User_Username'))
          throw new ConflictException('Username já está em uso');

        if (error.message.includes('UQ_User_Email'))
          throw new ConflictException('Email já está em uso');

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

        if (error.message.includes('FK_Person_PhysicalActivity'))
          throw new ConflictException('Nível de atividade física não existe');

        throw new BadGatewayException();
      });

    return this.retrieveUser(userPayload);
  }

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

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

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

    const { public_id: imagePath } = result;

    const AVATAR_SQL =
      'UPDATE user SET profile_picture_path = ? WHERE person_id = ?';

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

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

  async deleteAvatar(user: IUser) {
    const { id } = user;

    const GET_AVATAR_SQL =
      'SELECT profile_picture_path AS path FROM user WHERE person_id = ?';

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

    if (!path) throw new NotFoundException();

    const result = await this._cloudinaryService.deleteImage(path);

    if (!result) throw new NotFoundException();

    const DELETE_AVATAR_SQL =
      'UPDATE user SET profile_picture_path = null WHERE person_id = ?';

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

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

  private async _deleteRecipesThatAreInUse(
    transaction: EntityManager,
    userId: number,
  ) {
    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 IN (
        SELECT
          _recipe.id
        FROM recipe AS _recipe
        INNER JOIN meal_has_recipe
          ON _recipe.id = meal_has_recipe.recipe_id
        WHERE _recipe.user_person_id = recipe.user_person_id
      ) AND recipe.user_person_id = ?
    `;

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

    const RECIPE_SQL = `
      UPDATE
        recipe
      SET
        recipe.was_deleted = true,
        recipe.user_person_id = null
      WHERE id IN (
        SELECT
          _recipe.id
        FROM (
          SELECT
            __recipe.id AS id
          FROM recipe AS __recipe
          INNER JOIN meal_has_recipe
            ON __recipe.id = meal_has_recipe.recipe_id
          WHERE __recipe.user_person_id = recipe.user_person_id
        ) AS _recipe
      ) AND recipe.user_person_id = ?
    `;

    await transaction.query(RECIPE_SQL, [userId]);
  }

  private async _deleteRecipesThatAreNotInUse(
    transaction: EntityManager,
    userId: number,
  ) {
    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 IN (
        SELECT
          _recipe.id
        FROM recipe AS _recipe
        WHERE recipe.id NOT IN (
          SELECT
            __recipe.id
          FROM recipe AS __recipe
          INNER JOIN meal_has_recipe
            ON __recipe.id = meal_has_recipe.recipe_id
          WHERE __recipe.user_person_id = _recipe.user_person_id
        ) AND _recipe.user_person_id = recipe.user_person_id
      ) AND recipe.user_person_id = ?
    `;

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

    const RECIPE_SQL = `
      DELETE FROM recipe WHERE recipe.id IN (
        SELECT
          _recipe.id
        FROM (
          SELECT
            __recipe.id AS id
          FROM recipe AS __recipe
          WHERE recipe.id NOT IN (
            SELECT
              ___recipe.id
            FROM recipe AS ___recipe
            INNER JOIN meal_has_recipe
              ON ___recipe.id = meal_has_recipe.recipe_id
            WHERE ___recipe.user_person_id = __recipe.user_person_id
          ) AND __recipe.user_person_id = recipe.user_person_id
        ) AS _recipe
      ) AND recipe.user_person_id = ?
    `;

    await transaction.query(RECIPE_SQL, [userId]);
  }

  async deleteUser(user: IUser) {
    const { id } = user;

    const response = await this.retrieveUser(user);

    await this._entityManager
      .transaction(async (transaction) => {
        const MEAL_CHILD_TABLES_SQL = `
          DELETE
            meal_has_food,
            meal_has_recipe
          FROM meal
          LEFT JOIN meal_has_food
            ON meal.id = meal_has_food.meal_id
          LEFT JOIN meal_has_recipe
            ON meal.id = meal_has_recipe.meal_id
          WHERE user_person_id = ?
        `;

        await transaction.query(MEAL_CHILD_TABLES_SQL, [id]);

        const MEAL_SQL = 'DELETE FROM meal WHERE user_person_id = ?';

        await transaction.query(MEAL_SQL, [id]);

        const RECIPE_LIKES_SQL =
          'DELETE FROM user_liked_recipe WHERE user_person_id = ?';

        await transaction.query(RECIPE_LIKES_SQL, [id]);

        await this._deleteRecipesThatAreInUse(transaction, id);

        await this._deleteRecipesThatAreNotInUse(transaction, id);

        const PERSON_CHILD_TABLES_SQL = 'DELETE FROM user WHERE person_id = ?';

        await transaction.query(PERSON_CHILD_TABLES_SQL, [id]);

        const PERSON_SQL = 'DELETE FROM person WHERE id = ?';

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

    return response;
  }
}
