import { HttpException, HttpStatus, Inject, Injectable, NotFoundException } from '@nestjs/common';
import { TypeOrmQueryService } from '@nestjs-query/query-typeorm';
import { Designation, Users } from './user.entity';
import { InjectConnection, InjectRepository } from '@nestjs/typeorm';
import { Connection, Not, Repository } from 'typeorm';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { TokenPayload } from 'src/types/token-payload.type';
import { ConfigService } from 'nestjs-config';
import { JwtService } from '@nestjs/jwt';
import { SignupDto, UserUpdateDto, CreateCustomerCarDto, addNotificationDto,addCustomerAddressDto,addCustomerSupportDto,addCustomerFeedbackDto, AddRoleDto, CreateClientDto, UpdateClientDto, CreateVendorDto } from './dto/users.dto';
import { IPaginationOptions, paginate } from 'nestjs-typeorm-paginate';
import { Console } from 'console';
var moment = require('moment');
const axios = require("axios").default;


@Injectable()
export class UsersService extends TypeOrmQueryService<Users>  {
  constructor(
    @InjectRepository(Users)
    private readonly usersRepository: Repository<Users>,
    private readonly jwtService: JwtService,
    private readonly config: ConfigService,
    @Inject(REQUEST) private readonly request: Request,
    @InjectConnection() private readonly connection: Connection
  ) {
    super(usersRepository, { useSoftDelete: true });
  }

  async addRole(data:AddRoleDto) {
    var insert_role_query = "insert into role(role_name) Values ('"+data.role_name+"')";
    var inserted_query = await this.connection.query(insert_role_query);
    if(inserted_query){
      var last_inserted_id = inserted_query.insertId;
      

  }
  return inserted_query;


  }

  async getRoles() {
    try {
      
      const roles = "SELECT * FROM pmt_tool.role;"
      var role_query = await this.connection.query(roles);
      return role_query;
     
    } catch (ex) {
      throw ex;
    }
  }

  async getRole(id:number) {

    try {
      const getRole = "SELECT id,role_name FROM role where id= "+id;
      var role = await this.connection.query(getRole);
        return role;
       
      } catch (ex) {
        throw ex;
      }
  }

  async assignRole(user_id:number,data) {

    const user: Users = new Users();
    user.role_id = data.role_id;

    var update_user = await this.usersRepository.update(user_id, user);
    return "role updated";


  }

  async assign(user_id:number,data) {

    const user: Users = new Users();
    user.department_id = data.department_id;
    user.designation = data.designation;
    var update_user = await this.usersRepository.update(user_id, user);
    return update_user;


  }

  


  async register(data: SignupDto) {
    try {
          if (data.email) {
        data.email = data.email.trim().toLowerCase();

        let userExist = await this.usersRepository.findOne({
          where: { email: data.email }, withDeleted: true
        });
        if(userExist) {
          return "alreadyexist";
        }
      }

      const user: Users = new Users();
      user.full_name = data.full_name?.trim();
      user.contact_no = data.contact_no?.trim();
      user.email = data.email?.toLowerCase();
      user.city = data.city;
      user.nationality = data.nationality;
      user.job_title = data.job_title;
      user.job_type = data.job_type;
      user.location = data.location;
      user.password = data.password;
      
      var execute_user = await this.usersRepository.save(user);
        if (execute_user) {
          var last_inserted_id = user.id;

        } 
  
        return "done";
      

    } catch (err) {
      throw err
    }
  }

  async update(id: number, data, files) {
    try {
      const user: Users = new Users();
      user.full_name = data.full_name?.trim();
      user.contact_no = data.contact_no;
      user.city = data.city;
      user.client_id = data.client_id;
      user.project_id = data.project_id;
    

      await this.usersRepository.update(id, user);

        if (files.length > 0) {
          var select_attachment = "select id from attachment where user_id =" + id + " AND attachment_type = 'profile'";
          var execute_check_att = await this.connection.query(select_attachment);
          if (execute_check_att.length > 0) {
            files.forEach(async file => {
              // var attachmentName = process.env.HOST_FILES+file.filename;
              var attachmentName = 'http://localhost:3000/' + file.filename;
              console.log(attachmentName)

              var insert_attachment_query = "UPDATE attachment set name= '" + attachmentName + "' where user_id=" + id + " AND attachment_type = 'profile'";
              var attachement_execute_query = await this.connection.query(insert_attachment_query);
              return "User has been updated"

            });

          }

        } else {
          console.log("else")
          files.forEach(async file => {

            // var attachmentName = process.env.HOST_FILES+file.filename
            var attachmentName = 'http://localhost:3000/' + file.filename;
            var insert_attachment_query = `INSERT into attachment(name,attachment_type,user_id)VALUES (?,?,?)`;
            var attachement_execute_query = await this.connection.query(insert_attachment_query, [
              attachmentName,
              "profile",
              id

            ]);

            return "User has been updated"


          });


        }
            return await this.getUser(id);
    } catch (ex) {
      throw ex;
    }
  }

  async updateClient(id: number, data: UpdateClientDto) {
    try {
      const update_client_query = "UPDATE clients set client_name= '" + data.client_name + "', user_id=  '" + data.user_id + "' where id=" + id + " ";
      var update= await this.connection.query(update_client_query);
      return update;



    } catch (ex) {
      throw ex;
    }
  }

  async updateVendor(id: number, data:any) {
    try {
     
      const update_vendor_query = "UPDATE vendor set vendor_name= '" + data.vendor_name + "' , user_id='" + data.user_id + "' where id=" + id + " ";
     console.log(update_vendor_query)
      var update= await this.connection.query(update_vendor_query);
      return update;


        


    } catch (ex) {
      throw ex;
    }
  }


  async getUser(id: number) {
    try {
      // const user = await this.usersRepository.findOne({ where: { id:id,status:1 } });
      var selectUserQuery = 'SELECT users.*, role.role_name, department.department_name, project.project_name, clients.client_name, subproject.sub_project_name, vendor.vendor_name'
      selectUserQuery += ' FROM users'
      selectUserQuery += ' LEFT JOIN role ON users.role_id = role.id'
      selectUserQuery += ' LEFT JOIN department ON users.department_id = department.id'
      selectUserQuery += ' LEFT JOIN project ON users.project_id = project.id'
      selectUserQuery += ' LEFT JOIN clients ON users.client_id = clients.id'
      selectUserQuery += ' LEFT JOIN subproject ON users.subproject_id = subproject.id '
      selectUserQuery += ' LEFT JOIN vendor ON users.vendor_id = vendor.id'
      selectUserQuery += ' WHERE users.id =' + id;
    
      var user = await this.connection.query(selectUserQuery);
      
      if (!user) {
        throw new NotFoundException(`User #${id} not found`);
      }
      var selectUserQueryAttachment = 'SELECT attachment.name as profile_iamge from attachment where user_id ='+id+' AND attachment_type = "profile"';
      var userAttachment = await this.connection.query(selectUserQueryAttachment);
      user[0].profile_image = "";
      if(userAttachment.length > 0){
      user[0].profile_image = userAttachment[0].profile_iamge;
      }
      return user[0];
    } catch (ex) {
      throw ex;
    }
  }

  

  async userActivityCount(id: number) {
    try {
      // const user = await this.usersRepository.findOne({ where: { id:id,status:1 } });
      var selectUserQueryActivityTotal = 'SELECT count(id) as totalActivities from activity_user where user_id ='+id;
      var totalUserActivity = await this.connection.query(selectUserQueryActivityTotal);
      
      if (!totalUserActivity) {
        throw new NotFoundException(`User #${id} not found`);
      }
      var selectUserQueryActivityAssigned = 'SELECT count(id) as totalAssignedActivities from activity_user where user_id ='+id+' AND activity_status_user = "assigned"';
      var totalUserActivityAssigned = await this.connection.query(selectUserQueryActivityAssigned);
      totalUserActivity[0].totalAssignedActivities = totalUserActivityAssigned[0].totalAssignedActivities;

      var selectUserQueryActivityCompleted = 'SELECT count(id) as totalCompeletedActivities from activity_user where user_id ='+id+' AND activity_status_user = "completed"';
      var totalUserActivitycompleted = await this.connection.query(selectUserQueryActivityCompleted);
      totalUserActivity[0].totalCompeletedActivities = totalUserActivitycompleted[0].totalCompeletedActivities;
      return totalUserActivity[0];
    } catch (ex) {
      throw ex;
    }
  }

  async getAttendance(id:number,attendanceDate:string){
    try {
      // const user = await this.usersRepository.findOne({ where: { id:id,status:1 } });
      if(attendanceDate !=""){
        var getting_date = moment(attendanceDate).format("YYYY-MM-DD");
        var selectUserQuery = 'SELECT * from attendance where user_id ='+id+' AND attendance_date="'+getting_date+'"';
        var user = await this.connection.query(selectUserQuery);
      }else{
      var selectUserQuery = 'SELECT * from attendance where user_id ='+id;
      var user = await this.connection.query(selectUserQuery);
      }
      if (!user) {
        throw new NotFoundException(`User #${id} not found`);
      }
      return user;
    } catch (ex) {
      throw ex;
    }
  }

  async getAllAttendance(start_date,end_date){
    try {
      var whereClause = " ";
      var start_date_user_attendance = "";
      var end_date_attendance = "";
      if((start_date != "," && start_date != "undefined") && (end_date == "," && end_date != "undefined")){
        start_date_user_attendance = start_date;
        whereClause = ' WHERE DATE_FORMAT(attendance.attendance_date, "%Y-%m-%d") >= "'+start_date_user_attendance+'" ';
        
      }
      if((end_date != "," && end_date != "undefined") && (start_date == "," && start_date != "undefined")){
        end_date_attendance = end_date;
        whereClause = ' WHERE DATE_FORMAT(attendance.attendance_date, "%Y-%m-%d") <= "'+end_date_attendance+'" ';
      }
      if((start_date != "," && start_date != "undefined") && (end_date != "," && end_date != "undefined")){
        start_date_user_attendance = start_date;
        end_date_attendance = end_date;
        whereClause = ' WHERE DATE_FORMAT(attendance.attendance_date, "%Y-%m-%d") BETWEEN "'+start_date_user_attendance+'" AND "'+end_date_attendance+'"';
       
      }
      
      // const user = await this.usersRepository.findOne({ where: { id:id,status:1 } });
      var selectUserQuery = 'SELECT attendance.*,users.fullName,users.id as user_id, ';
      selectUserQuery += " (SELECT name FROM attachment WHERE attachment.attachment_type = 'selfie' AND attachment.user_id = users.id ORDER BY id desc LIMIT 1) AS selfie ";
      selectUserQuery += ' from attendance';
      selectUserQuery += ' LEFT JOIN users ON users.id = attendance.user_id '+whereClause+' ORDER BY attendance.id DESC ';
      console.log("selectUserQuery",selectUserQuery);
      var user = await this.connection.query(selectUserQuery);
      if (!user) {
        throw new NotFoundException(`User's not found`);
      }
      return user;
    } catch (ex) {
      throw ex;
    }
  }

  async getUsers() {
    try {

      // var users = 'SELECT Users.*, role.role_name,department.department_name,project.project_name, clients.client_name, subproject.sub_project_name, vendor.vendor_name FROM Users LEFT JOIN role ON Users.role_id = role.id LEFT JOIN department ON Users.department_id = department_id LEFT JOIN project ON Users.project_id = project.id LEFT JOIN clients ON Users.client_id = clients.id LEFT JOIN subproject ON Users.subproject_id = subproject.id LEFT JOIN vendor ON Users.vendor_id = vendor_id;'
      var selectUserQuery = 'SELECT users.*, role.role_name, department.department_name, project.project_name, clients.client_name, subproject.sub_project_name, vendor.vendor_name'
      selectUserQuery += ' FROM users'
      selectUserQuery += ' LEFT JOIN role ON users.role_id = role.id'
      selectUserQuery += ' LEFT JOIN department ON users.department_id = department.id'
      selectUserQuery += ' LEFT JOIN project ON users.project_id = project.id'
      selectUserQuery += ' LEFT JOIN clients ON users.client_id = clients.id'
      selectUserQuery += ' LEFT JOIN subproject ON users.subproject_id = subproject.id '
      selectUserQuery += ' LEFT JOIN vendor ON users.vendor_id = vendor.id'
      var query = await this.connection.query(selectUserQuery);
      return query;
     
    } catch (ex) {
      throw ex;
    }
  }

  async getClients() {
    try {
      
      var clients = "SELECT clients.id,clients.client_name, clients.status from clients";
      //clients+= "FROM clients ";
      //clients+= "LEFT JOIN project ON clients.project_id = project.id";
   
      console.log(clients);
      var clients_query = await this.connection.query(clients);
      return clients_query;
     
    } catch (ex) {
      throw ex;
    }

  }

  async getClientUser(user_id:number) {
    try {
    const getClient = "SELECT id,client_name,user_id FROM clients where id= "+user_id;
    console.log(getClient)
    var client = await this.connection.query(getClient);
      return client;
     
    } catch (ex) {
      throw ex;
    }
  }

  async getVendorUser(id:number) {
    try {
    const getVendor = "SELECT id,vendor_name,user_id,status FROM vendor where id= "+id;
    console.log(getVendor)
    var vendor = await this.connection.query(getVendor);
      return vendor;
     
    } catch (ex) {
      throw ex;
    }
  }



  async deleteAccount(id: number) {
    try {
      return await this.usersRepository.softDelete(id);
    } catch (ex) {
      throw ex;
    }
  }

  async deleteUser(id: number) {
    try {
      // return await this.usersRepository.delete(id);
      var delete_query = "Update users set status = 0 where id = "+id;
      var delete_query_execute = await this.connection.query(delete_query);
      if(delete_query_execute){
        return "deleted";
      }
    } catch (ex) {
      throw ex;
    }
  }

  async deleteClient(id: number) {
    try {
      var getuser= await this.getClientUser(id);
      console.log(getuser);
      var user_id= getuser[0].user_id;

      var delete_query = "Update users set status = 0 where id = "+user_id;
      var delete_query_execute = await this.connection.query(delete_query);

      var delete_client_query = "Update clients set status = 0 where user_id = "+user_id;
      var delete_Client_query_execute = await this.connection.query(delete_client_query);
      if(delete_Client_query_execute){
        return "deleted";
      }
    } catch (ex) {
      throw ex;
    }
  }

  async deleteVendor(id: number) {
    try {
      var getuser= await this.getVendorUser(id);
      console.log(getuser);
      var user_id= getuser[0].user_id;

      var delete_query = "Update users set status = 0 where id = "+user_id;
      var delete_query_execute = await this.connection.query(delete_query);

      var delete_vendor_query = "Update vendor set status = 0 where id = "+id;
      var delete_vendor_query_execute = await this.connection.query(delete_vendor_query);
      if(delete_vendor_query_execute){
        return "deleted";
      }
    } catch (ex) {
      throw ex;
    }
  }


  async forgotPassword(email: string, password: string) {
    try {
      const user = await this.usersRepository.findOne({
        where: { email: email.toLowerCase() }
      });
      if (!user)
        return "invalid";
        user.password = password;
        await this.usersRepository.update(user.id, user);
        return await this.getUser(user.id);

    } catch (ex) {
      throw ex;
    }
  }




  async login(email: string, password: string, phoneNo: string) {
    try {
      const user = await this.usersRepository.findOne({
        where: { email: email.toLowerCase() }
      });
     
      if (!user)
        throw new HttpException(
          'invalid username/password',
          HttpStatus.BAD_REQUEST
        );
      if (!user.password)
        throw new HttpException(
          'Password not set, please use forgot password to reset password',
          HttpStatus.BAD_REQUEST
        );
      if (!(await user.comparePassword(password)))
        throw new HttpException(
          'invalid username/password',
          HttpStatus.BAD_REQUEST
        );

        var selectUserQueryAttachment = 'SELECT attachment.name as profile_iamge from attachment where user_id ='+user.id+' AND attachment_type = "profile"';
      var userAttachment = await this.connection.query(selectUserQueryAttachment);
       var profile_image = "";
      if(userAttachment.length > 0){
      profile_image = userAttachment[0].profile_iamge;
      }else{
      profile_image =   profile_image;
      }

      const token = this.getJwtToken({
        ...user.toResponseObject()
      });
  
      var generalSubject = "Authentication Code";
        var smsCode = Math.floor(100000 + Math.random() * 900000);
        console.log("smsCode" + smsCode);
        const nowTime = moment().local().format('YYYY-MM-DD H:mm:ss');

      return {
        user: user.toResponseObject(),
        token,
        profile_image: profile_image
      };
    } catch (ex) {
      throw ex;
    }
  }

  private getJwtToken(payload: TokenPayload) {
    try {
      const token = this.jwtService.sign(payload, {
        secret: this.config.get('app.jwtSecret')
      });
      return token;
    } catch (ex) {
      throw ex;
    }
  }

  ////////////////code for otp ////////////////////////////////


  async verifyOTP(data) {
    try {
      const userId = data.userId;
      const OTPCode = data.OTPCode;
      const fcm_token = ''
      var isVerified = 0;
      //console.log("IP " + ip);
      var query_user_login_check = "Select id";
      query_user_login_check += " from users where id = '" + userId + "' AND authCode = '" + OTPCode + "'";
      const query_result_user_login_check = await this.connection.query(query_user_login_check);
  
      if (query_result_user_login_check.length > 0) {
        const id = query_result_user_login_check[0].id;
        var otp_valid = 1;
        var otp_timestamp_get = moment(query_result_user_login_check[0]['authCodeDateTime']).local().format('YYYY-MM-DD HH:mm:ss');
        const updateQuery = `update users SET status = 1,isVerified=1 where id = ${userId}`;
        await this.connection.query(updateQuery);
        var query_user_login = "Select id,isVerified,authCode,authCodeDateTime, firstName, lastName, phoneNo, email,role,hourly_rate,email,country,is_notification,callingCode,cca2,currency,flag,region,subregion,countryName ";
      query_user_login += " from users where id = '" + userId + "' AND authCode = '" + OTPCode + "'";
      const query_result_user_login = await this.connection.query(query_user_login);
        // var getValue = moment(otp_timestamp_get).valueOf();
        // var validTill = (getValue + 60000);
        // var d = moment().local().valueOf();
        // if (d > validTill) {
          
        //   return "expired";
        // }else{
        //   isVerified=1;
        // }
        
        const user = await this.usersRepository.findOne({
          where: { id: userId }
        });
        
        var token = "";
        if (user){
          token = this.getJwtToken({
            ...user.toResponseObject()
          });
        }
        // const updateQuery = `update Users SET authCode = '',status = 1,isVerified=1 where id = ${id}`;
        // await this.connection.query(updateQuery);
        return {
          data: query_result_user_login[0],
          status: true,
          message: "",
          error: false,
          token: token
        }
  
        
        return data;
  
      } else {
        
        return "notfound";
      }
    } catch (error) {
      
      return "error";
    }
  }




 

  //////////////////////// code for update notification status /////////////////////////
  async updateCustomerNotificationStatus(data) {
    console.log("notification_status",data);
    try {
    if(!data.userId && !data.notification_status){
      return "invalid";
    }
    var update_query = "Update users set is_notification = "+data.notification_status+" where id = "+data.userId+"";
    console.log("update_queryupdate_query",update_query);
    return await this.connection.query(update_query);
  } catch (err) {
    throw err
  }
  }

   //////////////////////// code for update customer car /////////////////////////
  async updateCustomerCar(userId,carId,request,files){
    console.log("notification_status",carId);
    try {
    if(!carId){
      return "invalid";
    }
    var update_query = "Update users set is_notification = "+request.notification_status+" where car_id = "+carId;
    console.log("update_queryupdate_query",update_query);
    var update_car_query = "UPDATE customer_car SET make_id="+request.make_id+",model_id="+request.model_id+",year_id="+request.year_id+",plate_number='"+request.plate_number+"',vin_number='"+request.vin_number+"' where id = "+carId;
          // console.log("insert_car_query",insert_car_query);
          var updated_car_query = await this.connection.query(update_car_query);
          if(updated_car_query){
    if(files.length > 0){
      var select_attachment = "select id from attachment where package_id ="+carId;
      var execute_check_att = await this.connection.query(select_attachment);
    if(execute_check_att.length > 0){
      files.forEach(async file => {
        var attachmentName = 'https://backend.xadautos.com/'+file.filename;
        // var attachmentName = 'http://localhost:3000/'+file.filename;
        var insert_attachment_query = "UPDATE attachment set name= '"+attachmentName+"' where car_id="+carId;
            var attachement_execute_query = await this.connection.query(insert_attachment_query);
            if(attachement_execute_query){
             
            }
      });
    }else{
      files.forEach(async file => {
        var attachmentName = 'https://backend.xadautos.com/'+file.filename;
        // var attachmentName = 'http://localhost:3000/'+file.filename;
        var insert_attachment_query = "INSERT into attachment(name,attachment_type,car_id)VALUES('"+attachmentName+"','customer_car',"+carId+")";
              var attachement_execute_query = await this.connection.query(insert_attachment_query);
            if(attachement_execute_query){
             
            }
      });
      

    }
    }
  }
    return "updated";
  } catch (err) {
    throw err
  }
  }

  //////////////////////// code for add notification /////////////////////////
  async addNotification(data: addNotificationDto) {
    try {
      if(data.notification_text == "" || data.notification_phone == ""){
        return "invalid";
      }
    var insert_query = "INSERT into notifications(notification_text,notification_phone,user_id,created_date)VALUES('"+data.notification_text+"','"+data.notification_phone+"',"+data.user_id+",NOW())";
    return await this.connection.query(insert_query);
  } catch (err) {
    throw err
  }
  }

  async addAttendance(request,files) {
    try {
    var user_date = new Date();
    // console.log("user_dateuser_date",user_date);
    var getting_date_time = moment().format("YYYY-MM-DD HH:mm:ss");
    var getting_date = moment().format("YYYY-MM-DD");
    console.log("getting_date",getting_date);
    var response_attendance = "";
    // if (request.user_ids.length > 0) {
      var select_attendance_query = 'Select id,attendance_date from attendance where user_id ='+request.user_id+' AND DATE_FORMAT(attendance_date, "%Y-%m-%d") ="'+getting_date+'"';
      console.log("select_attendance_query",select_attendance_query);
      var attendance_check = await this.connection.query(select_attendance_query);
      if(attendance_check.length > 0){
          if(request.request_type == 'clockout'){
            var update_attendance_query = 'Update attendance set attendance_clockout ="'+getting_date_time+'" WHERE user_id ="'+request.user_id+'" AND DATE_FORMAT(attendance_date, "%Y-%m-%d") ="'+getting_date+'"';
            return await this.connection.query(update_attendance_query);
          }
          if(request.request_type == 'checkout'){
            var update_attendance_query = 'Update attendance set attendance_checkout = "'+getting_date_time+'" WHERE user_id ="'+request.user_id+'" AND DATE_FORMAT(attendance_date, "%Y-%m-%d") ="'+getting_date+'"';
            return await this.connection.query(update_attendance_query);
          }
          if(request.request_type == 'checkin'){
            var update_attendance_query = 'Update attendance set attendance_checkin = "'+getting_date_time+'" WHERE user_id ="'+request.user_id+'" AND DATE_FORMAT(attendance_date, "%Y-%m-%d") ="'+getting_date+'"';
            return await this.connection.query(update_attendance_query);
          }
         
      }else{
            var insert_attendance_query = "insert into attendance(attendance_date,user_id,attendance_clockin) Values ('"+getting_date+"',"+request.user_id+",'"+getting_date_time+"')";
            response_attendance = await this.connection.query(insert_attendance_query);
            var insert_attachment_query = "";
      console.log("files.length",files.length)
      if(files.length > 0 && request.request_type == 'clockin'){
        var select_attachment = "select id from attachment where user_id ="+request.user_id+" AND attachment_type = 'selfie' AND DATE_FORMAT(dateCreated, '%Y-%m-%d') = '"+getting_date+"'";
        var execute_check_att = await this.connection.query(select_attachment);
      if(execute_check_att.length > 0){
        files.forEach(async file => {
          var attachmentName = process.env.HOST_FILES+file.filename;
          // var attachmentName = 'http://localhost:3000/'+file.filename;
          var insert_attachment_query = "UPDATE attachment set name= '"+attachmentName+"' where user_id="+request.user_id+" AND attachment_type = 'selfie' AND dateCreated = '"+getting_date_time+"'";
              var attachement_execute_query = await this.connection.query(insert_attachment_query);
              if(attachement_execute_query){
               
              }
        });
      }else{
        files.forEach(async file => {
          var attachmentName = process.env.HOST_FILES+file.filename;
          // var attachmentName = 'http://localhost:3000/'+file.filename;
          var insert_attachment_query = "INSERT into attachment(name,attachment_type,user_id)VALUES('"+attachmentName+"','selfie',"+request.user_id+")";
                var attachement_execute_query = await this.connection.query(insert_attachment_query);
              if(attachement_execute_query){
               
              }
        });
        
  
      }
      console.log("insert_attachment_query",insert_attachment_query);
      response_attendance
      }
      }
      
      } catch (err) {
        throw err
      }
  }



  mysql_real_escape_string_func(str) {
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
      switch (char) {
        case "\0":
          return "\\0";
        case "\x08":
          return "\\b";
        case "\x09":
          return "\\t";
        case "\x1a":
          return "\\z";
        case "\n":
          return "\\n";
        case "\r":
          return "\\r";
        case "\"":
        case "'":
        case "\\":
        case "%":
          return "\\" + char; // prepends a backslash to backslash, percent,
        // and double/single quotes
      }
    });
  }


   //////////////////////// code for checklist /////////////////////////
   async addCheckList(data) {
    try {
      
      // const staff: Staff = new Staff();
          var insert_checklist_query = "insert into check_list(name,description) Values ('"+this.mysql_real_escape_string_func(data.name)+"','"+this.mysql_real_escape_string_func(data.description)+"')";
          // console.log("insert_car_query",insert_car_query);
          var inserted_query = await this.connection.query(insert_checklist_query);
        if(inserted_query){
           return "done";
        }else{
          return "invalid";
        }
    } catch (err) {
      throw err
    }
  }


  async addUserCheckList(data,files) {
    try {
      var getting_date_time = moment().format("YYYY-MM-DD HH:mm:ss");
      var getting_date = moment().format("YYYY-MM-DD");
      // const staff: Staff = new Staff();
    //   var query_select = "Select id from check_list_user where check_list_id= '"+data.hazard_name+"' AND activity_id ="+data.activity_id+" AND user_id ="+data.user_id+" AND user_id ='"+data.tokenId+"'";
    //  var execute_select_query = await this.connection.query(query_select);
    //  if(execute_select_query.length > 0){
    //   return "already";
    //  }else{
      var check_list_id_check = data.check_list_id;
      var check_list_id = data.check_list_id;
      console.log("check_list_id",check_list_id);
      if(!(Array.isArray(check_list_id_check))){
        check_list_id = check_list_id_check.split(',');
      }
      if (check_list_id.length > 0 && check_list_id[0] != "") {
        var query_select = "Select id from check_list_user_detail where user_id ="+data.user_id+" AND DATE_FORMAT(check_list_date, '%Y-%m-%d') ='"+getting_date+"'";
        var execute_select_query = await this.connection.query(query_select);
        if(execute_select_query.length > 0){
          return "already";
        }else{
        var insert_checklist_detail_query = "insert into check_list_user_detail(vehicle_name,plate_number,remarks,check_list_date,user_id) Values ('"+data.vehicle_name+"','"+data.plate_number+"','"+data.remarks+"','"+getting_date_time+"',"+data.user_id+")";
          // console.log("insert_car_query",insert_car_query);
          var inserted_query_detail_query = await this.connection.query(insert_checklist_detail_query);
          var last_inserted_id_detail = inserted_query_detail_query.insertId;
          if(inserted_query_detail_query){
            if(files.length > 0){
              // var last_inserted_id = inserted_query.insertId;
            files.forEach(async file => {
              var attachmentName = process.env.HOST_FILES+file.filename;
              //  var attachmentName = 'http://localhost:3000/'+file.filename;
              var insert_attachment_query = "UPDATE check_list_user_detail set attachment= '"+attachmentName+"' where id="+last_inserted_id_detail+"";
              var attachement_execute_query = await this.connection.query(insert_attachment_query);
              if(attachement_execute_query){
               
              }
            });
          }
          
        }
      }
        var query_select_check_list = "Select * from check_list where 1=1";
        var execute_select_query_check_list = await this.connection.query(query_select_check_list);
        if(execute_select_query_check_list.length > 0){
          for (let i = 0; i < execute_select_query_check_list.length; i++) {
            var insert_checklist_query = "insert into check_list_user(check_list_id,user_id,check_list_date) Values ("+execute_select_query_check_list[i].id+","+data.user_id+",'"+getting_date_time+"')";
            // console.log("insert_car_query",insert_car_query);
            var inserted_query = await this.connection.query(insert_checklist_query);
          }
        }
          for (let i = 0; i < check_list_id.length; i++) {
        var query_select = "Select id from check_list_user where check_list_id= "+check_list_id[i]+" AND user_id ="+data.user_id+" AND DATE_FORMAT(check_list_date, '%Y-%m-%d') ='"+getting_date+"'";
        var execute_select_query = await this.connection.query(query_select);
        if(execute_select_query.length > 0){
        var update_checklist_query="UPDATE check_list_user set is_included = 1 WHERE user_id = '"+data.user_id+"' AND check_list_date ='"+getting_date_time+"' AND check_list_id = '"+check_list_id[i]+"'";
        await this.connection.query(update_checklist_query);  
      }else{
          var insert_checklist_query = "insert into check_list_user(check_list_id,user_id,check_list_date) Values ("+check_list_id[i]+","+data.user_id+",'"+getting_date_time+"')";
          // console.log("insert_car_query",insert_car_query);
          var inserted_query = await this.connection.query(insert_checklist_query);
          
      }
      }
      return "done";
      }
        return "invalid";
    } catch (err) {
      throw err
    }
  }

  async getChecklist(){
    try {
      
      var getCheckListQuery = 'SELECT * from check_list';
      console.log("getCheckListQuery",getCheckListQuery);
      var executeCheckListQuery = await this.connection.query(getCheckListQuery);
      if(executeCheckListQuery){
        return executeCheckListQuery;
      }else{
        throw new NotFoundException(`check list not found`);
      }
    } catch (ex) {
      throw ex;
    }
  }

  async getUserChecklist(userId:number,checklist_date:string){
    try {
      console.log("checklist_date",checklist_date);
      var whereClause = " ";
      if((checklist_date != "," && checklist_date != "undefined")){
      
        whereClause = ' AND DATE_FORMAT(check_list_date, "%Y-%m-%d") = "'+checklist_date+'" ';
        
      }
      
      var getUserCheckListDetailQuery = 'Select * from check_list_user_detail where check_list_user_detail.user_id ='+userId+whereClause+' ';
      // console.log("getUserCheckListDetailQuery",getUserCheckListDetailQuery);
      var executed_query_check_list_detail = await this.connection.query(getUserCheckListDetailQuery);
      if(executed_query_check_list_detail.length > 0){
      
      var getCheckListQuery = 'SELECT check_list.id,check_list.name,check_list.description,check_list_user.is_included ';
      getCheckListQuery += ' from check_list_user ';
      getCheckListQuery += ' LEFT JOIN check_list ON check_list.id = check_list_user.check_list_id ';
      getCheckListQuery += 'where check_list_user.user_id ='+userId+whereClause+' ';
      console.log("getCheckListQuery",getCheckListQuery);
      var executed_query = await this.connection.query(getCheckListQuery);
      if(executed_query){
        executed_query_check_list_detail[0].check_list_data = executed_query;
      }else{
        executed_query_check_list_detail[0].check_list_data = {};
      }
      return executed_query_check_list_detail;
    }else{
      throw new NotFoundException(`User not found`);
    }
    } catch (ex) {
      throw ex;
    }
  }


  async add(data: CreateClientDto) {
    try {
        const insert_user_query = `
        INSERT INTO clients(client_name,user_id)
        VALUES (?,?)
      `;

      await this.connection.query(insert_user_query, [
        data.client_name,
        data.user_id,
        
      ]);

      return "done";
    } catch (err) {
      throw err
    }
  }


async addVendor(data: CreateVendorDto) {
    try {
      console.log(data)

        const insert_user_query = `
        INSERT INTO vendor(vendor_name,user_id)
        VALUES (?,?)
      `;

      await this.connection.query(insert_user_query, [
        data.vendor_name,
        data.user_id,
               
      ]);

      return "done";
    } catch (err) {
      throw err
    }
  }

 
///////////////////////feedback code starts//////////////////////////////////////
  //////////////////////// code for add feedback /////////////////////////
  async addCustomerFeedbackData(data: addCustomerFeedbackDto) {
    try {
      
      // const staff: Staff = new Staff();
      var getSupportQuery = "select * from customer_feedback where user_id="+data.user_id;
        console.log("getSupportQuery",getSupportQuery);
        var executed_support_query = await this.connection.query(getSupportQuery);
        
          var insert_feedback_query = "insert into customer_feedback(customer_name,remarks,user_id,rating,created_at) Values ('"+data.customer_name+"','"+data.remarks+"',"+data.user_id+",'"+data.rating+"',NOW())";
          // console.log("insert_car_query",insert_car_query);
          var inserted_query = await this.connection.query(insert_feedback_query);
          if(inserted_query){
            var last_inserted_id = inserted_query.insertId;
            
    
        }
        return inserted_query;
      
    } catch (err) {
      throw err
    }
  }











}