package repository

import (
	// "bpmsapi/dto"
	"bpmsapi/dto"
	"bpmsapi/entity"
	"bpmsapi/util"
	"bytes"
	"errors"
	"strconv"
	"strings"

	// "errors"
	// "fmt"
	"log"

	"github.com/jmoiron/sqlx"
)

type Technician interface {
	FetchTechnician(Service_Id int, Apartment_Id int, Time_slot string) (*entity.AllTechnicianList, error)
	TechUpdateServiceRequest(tx *sqlx.Tx, input entity.UpdateSrqTechInput, image_path string) (*entity.TechServiceRequest, error)
	GetPinConfirmation(tx *sqlx.Tx, input entity.GetConfirmationPinInput) (*entity.GetConfirmationPin, error)
	FetchTechnicianList(input entity.GetTechnicianListInput) (*entity.GetTechniciansList, error)
	GetConfirmationPin(input entity.GetConfirmationPinInput) (*entity.GetConfirmationPin, error)
	FetchRole(input entity.GetRoleInput) (*entity.GetRole, error)
	FetchAllEmployee(input entity.GetAllEmployeeInput) (*entity.GetAllEmployee, error)
}

type TechnicianRepository struct {
	db *sqlx.DB
}

func TechnicianRepositoryInit(db *sqlx.DB) *TechnicianRepository {
	return &TechnicianRepository{
		db: db,
	}
}
func (technicianRepo *TechnicianRepository) Begin() (*sqlx.Tx, error) {
	tx, err := technicianRepo.db.Beginx()
	if err != nil {
		return nil, err
	}
	return tx, nil
}

// func (technicianRepo *TechnicianRepository) FetchTechnician(input entity.FetchTechnicianInput) ([]entity.TechnicianList, error) {
// 	resp := []entity.TechnicianList{}

// 	fetchQuery := "SELECT e.Emp_Id, e.Emp_Name, e.Emp_Image, (SELECT Count(*) FROM BPMS_SERVICE_REQUEST ss where Technician_Id = e.Emp_Id AND ss.Srq_Stage_4 = 0 AND ss.Srq_Stage_5 = 0 AND ss.Srq_Stage_6 = 0 AND ss.Srq_Stage_7 = 0 AND ss.Srq_Stage_8 = 0) AS Order_Count from BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING s on (e.Emp_Id = s.Emp_Id AND s.Service_Id = ? AND e.Is_Available = 1) INNER JOIN BPMS_ROLE_MAPPING r on (r.Emp_Id = e.Emp_Id AND r.Role_Id = 1) INNER JOIN BPMS_EMP_APARTMENT_MAPPING a on (e.Emp_Id = a.Emp_Id AND a.Apartment_Id = ?) LEFT OUTER JOIN BPMS_SERVICE_REQUEST sr on (e.Emp_Id = sr.Technician_Id and sr.Srq_Visit_Timeslot <= DATE_ADD(?, INTERVAL 1 HOUR) AND sr.Srq_Visit_Timeslot >= DATE_SUB(?, INTERVAL 1 HOUR) AND sr.Srq_Stage_4 !=1 AND sr.Srq_Stage_5 !=1 AND sr.Srq_Stage_6 != 1 AND sr.Srq_Stage_7 != 1 AND sr.Srq_Stage_8 != 1) GROUP BY e.Emp_Id having COUNT(sr.Srq_Id) = 0 order by Order_Count"

//		err := technicianRepo.db.Select(&resp, fetchQuery, input.Service_Id, input.Apartment_Id, input.Time_slot, input.Time_slot)
//		if err != nil {
//			log.Println("TechnicianRepository: FetchTechnician: Input: ", input.Service_Id, input.Apartment_Id, input.Time_slot, " Error ", err.Error())
//			return nil, err
//		} else {
//			log.Println("TechnicianRepository: FetchTechnician: Input: ", input.Service_Id, input.Apartment_Id, input.Time_slot)
//			return resp, nil
//		}
//	}
func (technicianRepo *TechnicianRepository) FetchTechnician(input entity.FetchTechnicianInput) (*entity.AllTechnicianList, error) {
	techList := entity.AllTechnicianList{}
	tech_info := []entity.EmployeeLists{}
	count := techList.Total_Technician
	var err, errCount error
	var fetchQuery string
	if input.Name != "" {
		fetchQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating, (SELECT Count(*) FROM BPMS_SERVICE_REQUEST ss where Technician_Id = e.Emp_Id AND ss.Srq_Stage_4 = 0 AND ss.Srq_Stage_5 = 0 AND ss.Srq_Stage_6 = 0 AND ss.Srq_Stage_7 = 0 AND ss.Srq_Stage_8 = 0) AS Order_Count from BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING s on (e.Emp_Id = s.Emp_Id AND s.Service_Id = ? AND e.Is_Available = 1) INNER JOIN BPMS_ROLE_MAPPING r on (r.Emp_Id = e.Emp_Id AND r.Role_Id = 1) INNER JOIN BPMS_EMP_APARTMENT_MAPPING a on (e.Emp_Id = a.Emp_Id AND a.Apartment_Id = ?) LEFT OUTER JOIN BPMS_SERVICE_REQUEST sr on (e.Emp_Id = sr.Technician_Id and sr.Srq_Visit_Timeslot <= DATE_ADD(?, INTERVAL 1 HOUR) AND sr.Srq_Visit_Timeslot >= DATE_SUB(?, INTERVAL 1 HOUR) AND sr.Srq_Stage_4 !=1 AND sr.Srq_Stage_5 !=1 AND sr.Srq_Stage_6 != 1 AND sr.Srq_Stage_7 != 1 AND sr.Srq_Stage_8 != 1) LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE e.Emp_Name LIKE ? GROUP BY e.Emp_Id having COUNT(sr.Srq_Id) = 0 order by Order_Count limit ? offset ?"
		err = technicianRepo.db.Select(&tech_info, fetchQuery, input.Service_Id, input.Apartment_Id, input.Time_slot, input.Time_slot, "%"+input.Name+"%", input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(*) FROM (SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating, (SELECT Count(*) FROM BPMS_SERVICE_REQUEST ss where Technician_Id = e.Emp_Id AND ss.Srq_Stage_4 = 0 AND ss.Srq_Stage_5 = 0 AND ss.Srq_Stage_6 = 0 AND ss.Srq_Stage_7 = 0 AND ss.Srq_Stage_8 = 0) AS Order_Count from BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING s on (e.Emp_Id = s.Emp_Id AND s.Service_Id = ? AND e.Is_Available = 1) INNER JOIN BPMS_ROLE_MAPPING r on (r.Emp_Id = e.Emp_Id AND r.Role_Id = 1) INNER JOIN BPMS_EMP_APARTMENT_MAPPING a on (e.Emp_Id = a.Emp_Id AND a.Apartment_Id = ?) LEFT OUTER JOIN BPMS_SERVICE_REQUEST sr on (e.Emp_Id = sr.Technician_Id and sr.Srq_Visit_Timeslot <= DATE_ADD(?, INTERVAL 1 HOUR) AND sr.Srq_Visit_Timeslot >= DATE_SUB(?, INTERVAL 1 HOUR) AND sr.Srq_Stage_4 !=1 AND sr.Srq_Stage_5 !=1 AND sr.Srq_Stage_6 != 1 AND sr.Srq_Stage_7 != 1 AND sr.Srq_Stage_8 != 1) LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE e.Emp_Name LIKE ? GROUP BY e.Emp_Id having COUNT(sr.Srq_Id) = 0 order by Order_Count) AS COUNT", input.Service_Id, input.Apartment_Id, input.Time_slot, input.Time_slot, "%"+input.Name+"%")
	} else {
		fetchQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating, (SELECT Count(*) FROM BPMS_SERVICE_REQUEST ss where Technician_Id = e.Emp_Id AND ss.Srq_Stage_4 = 0 AND ss.Srq_Stage_5 = 0 AND ss.Srq_Stage_6 = 0 AND ss.Srq_Stage_7 = 0 AND ss.Srq_Stage_8 = 0) AS Order_Count from BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING s on (e.Emp_Id = s.Emp_Id AND s.Service_Id = ? AND e.Is_Available = 1) INNER JOIN BPMS_ROLE_MAPPING r on (r.Emp_Id = e.Emp_Id AND r.Role_Id = 1) INNER JOIN BPMS_EMP_APARTMENT_MAPPING a on (e.Emp_Id = a.Emp_Id AND a.Apartment_Id = ?) LEFT OUTER JOIN BPMS_SERVICE_REQUEST sr on (e.Emp_Id = sr.Technician_Id and sr.Srq_Visit_Timeslot <= DATE_ADD(?, INTERVAL 1 HOUR) AND sr.Srq_Visit_Timeslot >= DATE_SUB(?, INTERVAL 1 HOUR) AND sr.Srq_Stage_4 !=1 AND sr.Srq_Stage_5 !=1 AND sr.Srq_Stage_6 != 1 AND sr.Srq_Stage_7 != 1 AND sr.Srq_Stage_8 != 1) LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id GROUP BY e.Emp_Id having COUNT(sr.Srq_Id) = 0 order by Order_Count limit ? offset ?"
		err = technicianRepo.db.Select(&tech_info, fetchQuery, input.Service_Id, input.Apartment_Id, input.Time_slot, input.Time_slot, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(*) FROM (SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating, (SELECT Count(*) FROM BPMS_SERVICE_REQUEST ss where Technician_Id = e.Emp_Id AND ss.Srq_Stage_4 = 0 AND ss.Srq_Stage_5 = 0 AND ss.Srq_Stage_6 = 0 AND ss.Srq_Stage_7 = 0 AND ss.Srq_Stage_8 = 0) AS Order_Count from BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING s on (e.Emp_Id = s.Emp_Id AND s.Service_Id = ? AND e.Is_Available = 1) INNER JOIN BPMS_ROLE_MAPPING r on (r.Emp_Id = e.Emp_Id AND r.Role_Id = 1) INNER JOIN BPMS_EMP_APARTMENT_MAPPING a on (e.Emp_Id = a.Emp_Id AND a.Apartment_Id = ?) LEFT OUTER JOIN BPMS_SERVICE_REQUEST sr on (e.Emp_Id = sr.Technician_Id and sr.Srq_Visit_Timeslot <= DATE_ADD(?, INTERVAL 1 HOUR) AND sr.Srq_Visit_Timeslot >= DATE_SUB(?, INTERVAL 1 HOUR) AND sr.Srq_Stage_4 !=1 AND sr.Srq_Stage_5 !=1 AND sr.Srq_Stage_6 != 1 AND sr.Srq_Stage_7 != 1 AND sr.Srq_Stage_8 != 1) LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id GROUP BY e.Emp_Id having COUNT(sr.Srq_Id) = 0 order by Order_Count) AS COUNT", input.Service_Id, input.Apartment_Id, input.Time_slot, input.Time_slot)
	}
	if errCount != nil {
		log.Println("TechnicianRepository: FetchTechnician: totalTechnican :Input: serviceid:", input.Service_Id, "apartmentid: ", input.Apartment_Id, "Error ", errCount.Error())
		return nil, errCount
	}
	if err != nil {
		log.Println("TechnicianRepository: FetchTechnician: available_tech : Input: serviceid:", input.Service_Id, "apartmentid: ", input.Apartment_Id, " Error ", err.Error())
		return nil, err
	} else {
		log.Println("TechnicianRepository: FetchTechnician: Input: serviceid:", input.Service_Id, "apartmentid: ", input.Apartment_Id)

		for i := range tech_info {
			skilled_service, err1 := technicianRepo.FetchSkilledServices(tech_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchTechnician: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			tech_info[i].Skilled_Services = skilled_service

		}
		for i := range tech_info {
			allocated_apartment, err1 := technicianRepo.FetchAllocatedApartment(tech_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchTechnician: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			tech_info[i].Allocated_Apartments = allocated_apartment

		}
		var roles []string
		for i := range tech_info {
			errIn := technicianRepo.db.Select(&roles, "SELECT r.Role_Decription FROM BPMS_ROLE_MASTER r INNER JOIN BPMS_ROLE_MAPPING rm ON r.Role_Id = rm.Role_Id INNER JOIN BPMS_EMPLOYEE e ON rm.Emp_Id = e.Emp_Id WHERE e.Emp_Id =?", (tech_info[i].Emp_Id))
			if errIn != nil {
				log.Print("ProfileRepository: FetchAllEmployee: input : emp_id ", tech_info[i].Emp_Id, " Error:", errIn.Error())
				return nil, errIn
			} else {
				tech_info[i].Role = roles
			}
		}
		techList.TechList = tech_info
		techList.Page = count / input.PageLimit
		if count%input.PageLimit != 0 {
			techList.Page++
		}
		techList.Total_Technician = count
		return &techList, nil
	}
}

func (technicianRepo *TechnicianRepository) FetchTechnicianList(input entity.GetTechnicianListInput) (*entity.GetTechniciansList, error) {
	var query string
	var err, errCount error
	techList := entity.GetTechniciansList{}
	tech_info := []entity.TechniciansList{}
	count := techList.Total_Technician
	if input.Emp_Id != 0 || input.Phone_Number != 0 {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE r.Role_Id = 1 AND e.Emp_Status !='inactive' AND e.Emp_Id = ? OR e.Emp_Mobno = ? GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.Emp_Id, input.Phone_Number, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(brm.Emp_Id) AS Total_Technician FROM BPMS_ROLE_MAPPING brm LEFT JOIN BPMS_EMPLOYEE be ON brm.Emp_Id = be.Emp_Id WHERE be.Emp_Status !='inactive' AND brm.Role_Id =1 AND be.Emp_Id = ? or be.Emp_Mobno = ?", input.Emp_Id, input.Phone_Number)
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Input: Emp_Id:", input.Emp_Id, " mobno:", input.Phone_Number, "  Error:", errCount.Error())
			return nil, errCount
		}
	} else if input.Emp_Name != "" {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE r.Role_Id = 1 AND e.Emp_Status !='inactive' AND e.Emp_Name =? GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.Emp_Name, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(brm.Emp_Id) AS Total_Technician FROM BPMS_ROLE_MAPPING brm LEFT JOIN BPMS_EMPLOYEE be ON brm.Emp_Id = be.Emp_Id WHERE be.Emp_Status !='inactive' AND brm.Role_Id =1 AND be.Emp_Name=?", input.Emp_Name)
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Input: Emp_Name:", input.Emp_Name, " Error:", errCount.Error())
			return nil, errCount
		}
	} else if input.Service_Id != 0 {

		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING s ON e.Emp_Id = s.Emp_Id WHERE e.Emp_Status !='inactive' AND r.Role_Id = 1 AND s.Service_Id =? GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.Service_Id, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(brm.Emp_Id) AS Total_Technician FROM BPMS_ROLE_MAPPING brm LEFT JOIN BPMS_EMPLOYEE be ON brm.Emp_Id = be.Emp_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING s ON brm.Emp_Id = s.Emp_Id WHERE be.Emp_Status !='inactive' AND brm.Role_Id =1 AND s.Service_Id =?", input.Service_Id)
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Input: service_id:", input.Service_Id, " Error:", errCount.Error())
			return nil, errCount
		}

	} else if input.Action == "all" {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id  WHERE r.Role_Id = 1 GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(Emp_Id) AS Total_Technician FROM BPMS_ROLE_MAPPING WHERE Role_Id =1")
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Error:", errCount.Error())
			return nil, errCount
		}
	} else if input.Action == "inactive" {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id  WHERE e.Emp_Status ='inactive' AND r.Role_Id = 1 GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(e.Emp_Id) AS Total_Technician FROM BPMS_EMPLOYEE e INNER JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id  WHERE r.Role_Id =1 AND e.Emp_Status ='inactive' ")
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Error:", errCount.Error())
			return nil, errCount
		}
	} else {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Vendor_Name, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e LEFT JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id  WHERE e.Emp_Status !='inactive' AND r.Role_Id = 1 GROUP BY e.Emp_Id limit ? offset ?`
		err = technicianRepo.db.Select(&tech_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = technicianRepo.db.Get(&count, "SELECT COUNT(e.Emp_Id) AS Total_Technician FROM BPMS_EMPLOYEE e INNER JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id  WHERE r.Role_Id =1 AND e.Emp_Status !='inactive' ")
		if errCount != nil {
			log.Println("TechnicianRepository: FetchTechnicianList: totalTechnicianCount: Error:", errCount.Error())
			return nil, errCount
		}
	}
	if err != nil {
		log.Println("TechnicianRepository: FetchTechnicianList: Error: ", err.Error())
		return nil, err
	} else {
		log.Println("TechnicianRepository: FetchTechnicianList: input", input.Emp_Id)
		for i := range tech_info {
			skilled_service, err1 := technicianRepo.FetchSkilledServices(tech_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchTechnicianList: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			tech_info[i].Skilled_Services = skilled_service

		}
		for i := range tech_info {
			allocated_apartment, err1 := technicianRepo.FetchAllocatedApartment(tech_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchTechnicianList: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			tech_info[i].Allocated_Apartments = allocated_apartment

		}
		techList.TechniciansLists = tech_info
		techList.Page = count / input.PageLimit
		if count%input.PageLimit != 0 {
			techList.Page++
		}
		techList.Total_Technician = count
		return &techList, nil
	}
}

func (technicianRepo *TechnicianRepository) FetchSkilledServices(empid int) (mapped_service []entity.SkilledService, err error) {
	err = technicianRepo.db.Select(&mapped_service, "SELECT bsem.Service_Id, bst.Service_Name, s.Service_Image_Small, s.Service_Image_large FROM BPMS_SERVICE_EMP_MAPPING bsem INNER JOIN BPMS_SERVICE_TRANSLATIONS bst ON bsem.Service_Id = bst.Service_Id AND bst.Language_code =1 LEFT JOIN BPMS_SERVICE s ON bsem.Service_Id =s.Service_Id WHERE bsem.Emp_Id = ?", empid)
	if err != nil {
		log.Print("TechnicianRepository: FetchSkilledServices: input : EmpId ", empid, " Error:", err.Error())
		return nil, err
	} else {
		return mapped_service, nil
	}
}

func (technicianRepo *TechnicianRepository) FetchAllocatedApartment(empid int) (emp_apartments []entity.EmpAllocatedApartment, err error) {

	err = technicianRepo.db.Select(&emp_apartments, "SELECT beam.Apartment_Id, bat.Apartment_Name, ba.Apartment_Image FROM BPMS_EMP_APARTMENT_MAPPING beam INNER JOIN BPMS_APARTMENT_TRANSLATIONS bat ON beam.Apartment_Id = bat.Apartment_Id AND bat.Language_Code = 1 INNER JOIN BPMS_APARTMENT ba ON beam.Apartment_Id = ba.Apartment_Id WHERE beam.Emp_Id = ?", empid)
	if err != nil {
		log.Print("TechnicianRepository: FetchAllocatedApartment: input : EmpId ", empid, " Error:", err.Error())
		return nil, err
	} else {
		return emp_apartments, nil
	}
}

func (technicianRepo *TechnicianRepository) TechUpdateServiceRequest(tx *sqlx.Tx, input entity.UpdateSrqTechInput, image_path string) (*entity.TechServiceRequest, error) {

	if input.Admin_Id != 0 {
		adminquery := "UPDATE `BPMS_SERVICE_REQUEST` SET Adm_Id =? WHERE Srq_Id =?"
		_, err := tx.Exec(adminquery, input.Admin_Id, input.Srq_Id)
		if err != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: updateadminid :input: Srq_Id:", input.Srq_Id, " adm_id:", input.Admin_Id, "Error: ", err.Error())
			return nil, err
		}

	}
	if input.Acknowledge != "" {
		var stage_2 int
		query := "SELECT Srq_Stage_2 FROM `BPMS_SERVICE_REQUEST` WHERE Srq_Id =? "
		errr := tx.Get(&stage_2, query, input.Srq_Id)
		if errr != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: gettingstatus : Srq_Id:", input.Srq_Id, " Error: ", errr.Error())
			return nil, errr
		}

		if stage_2 == 0 {
			var queryAck string
			if strings.ToLower(input.Acknowledge) == "accept" {
				queryAck = "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_2`=1, `Srq_Stage_2_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"
			} else if strings.ToLower(input.Acknowledge) == "reject" {
				queryAck = "UPDATE `BPMS_SERVICE_REQUEST` SET `Technician_Id`=null WHERE Srq_Id =?"
			}

			_, errIn := tx.Exec(queryAck, input.Srq_Id)
			if errIn != nil {
				tx.Rollback()
				log.Println("TechnicianRepository: TechUpdateServiceRequest: acknowledge :input: Srq_Id:", input.Srq_Id, " Error: ", errIn.Error())
				return nil, errIn
			}

		} else {
			log.Println("TechnicianRepository: TechUpdateServiceRequest: acknowledge : Srq_Id:", input.Srq_Id, ": acknowledge:", input.Acknowledge)
			return nil, errors.New(dto.ErrorInvalidUpdateSrqTech)
		}
	}

	if input.NewStatus != "" {
		var statusQuery, new_status string
		new_status = strings.ToLower(input.NewStatus)
		switch new_status {
		//3
		case "in-progress":
			if input.OldStatus == "assigned" {
				statusQuery = "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_3`=1 , `Srq_Stage_3_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"
			}
			//4
		case "fixed":
			if input.OldStatus == "in-progress" {
				var pinValidated int
				getquery := "SELECT Is_Happycode_Validated FROM `BPMS_SERVICE_REQUEST` WHERE Srq_Id =?"

				errPin := tx.Get(&pinValidated, getquery, input.Srq_Id)
				if errPin != nil {
					tx.Rollback()
					log.Println("TechnicianRepository: TechUpdateServiceRequest: gettingstatus : Srq_Id:", input.Srq_Id, " Error: ", errPin.Error())
					return nil, errPin
				}
				if pinValidated == 1 {
					statusQuery = "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_4`=1 , `Srq_Stage_4_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =? AND Is_Happycode_Validated = 1 "
				} else {
					log.Println("TechnicianRepository: TechUpdateServiceRequest: gettingstatus : Srq_Id:", input.Srq_Id)
					return nil, errors.New(" status change is not applicable. Happy code is not validated")
				}

			}
			//5
		case "further action":
			statusQuery = "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_5`=1 , `Srq_Stage_5_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"
		//8
		case "cancel":
			statusQuery = "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_8`=1 , `Srq_Stage_8_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"

		default:
			log.Println("TechnicianRepository: TechUpdateServiceRequest: updatingstatus :invalidstatus : Srq_Id:", input.Srq_Id, ": Status:", input.NewStatus)
			return nil, errors.New(dto.UpdateSrqTechNewStatus)
		}
		_, err := tx.Exec(statusQuery, input.Srq_Id)
		if err != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: updatingstatus :input: Srq_Id:", input.Srq_Id, ": Status:", input.NewStatus, " Error: ", err.Error())
			return nil, err
		}

	}

	if input.TimeSlot != "" {
		var stage_3 int
		query := "SELECT Srq_Stage_3 FROM `BPMS_SERVICE_REQUEST` WHERE Srq_Id =? "
		err := tx.Get(&stage_3, query, input.Srq_Id)
		if err != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: gettingstatus : Srq_Id:", input.Srq_Id, " Error: ", err.Error())
			return nil, err
		}

		if stage_3 == 0 {
			timeSlotQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET Srq_Visit_Timeslot =? WHERE Srq_Id =?"
			_, err1 := tx.Exec(timeSlotQuery, input.TimeSlot, input.Srq_Id)
			if err1 != nil {
				tx.Rollback()
				log.Println("TechnicianRepository: TechUpdateServiceRequest: updatetimeslot :input: Srq_Id:", input.Srq_Id, " timeslot:", input.TimeSlot, "Error: ", err1.Error())
				return nil, err1
			}

		} else {
			log.Println("TechnicianRepository: TechUpdateServiceRequest: updatetimeslot : Srq_Id:", input.Srq_Id, ": timeslot:", input.TimeSlot)
			return nil, errors.New(dto.ErrorInvalidUpdateSrqTech)
		}
	}

	if image_path != "" {
		imgQuery := "Insert INTO `BPMS_SRQ_IMAGE` (`Image_Path`,`Srq_Id`) values (?,?)"
		_, errImg := tx.Exec(imgQuery, image_path, input.Srq_Id)
		if errImg != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: insertingimage: input: ", input.Srq_Id, " Error: ", errImg.Error())
			return nil, errImg
		}

	}

	if input.Comments != "" {
		log.Println("TechnicianRepository: TechUpdateServiceRequest: srq_id :", input.Srq_Id, "comments: ", input.Comments)
		commentsQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET Technician_Comments = ? WHERE Srq_Id = ?"
		_, err2 := tx.Exec(commentsQuery, input.Comments, input.Srq_Id)
		if err2 != nil {
			tx.Rollback()
			log.Println("TechnicianRepository: TechUpdateServiceRequest: updatingtechcomments :input: Srq_Id:", input.Srq_Id, " Comment:", input.Comments, "Error: ", err2.Error())
			return nil, err2
		}

	}
	srq := entity.TechServiceRequest{}

	getQuery := "SELECT bsr.Srq_Id, bsr.Srq_Key, bsr.Srq_Service, bst.Service_Name, bs.Service_Description, bs.Service_Image_Large, bs.Service_Image_Small, bsr.Request_Type, bsr.Parent_Request, bsr.Child_Request, bsr.Srq_Apartment_Id, bat.Apartment_Name,ba.Apartment_Image, bsat.State_Name, bct.City_Name, bsr.User_Id, bu.User_Name, bu.User_Mobno, bu.User_Image, buam.Block, buam.Door_No, bsr.Adm_Id, be.Emp_Name AS Admin_Name, be.Emp_Mobno AS Admin_Mobno, bsr.Admin_Comments, bsr.Technician_Id, bet.Emp_Name As Technician_Name, bet.Emp_Mobno AS Technician_Mobno, bet.Emp_Image AS Tech_Image, bsr.Technician_Comments, bpm.Promocode_Id, bp.Name, bp.Discount, bp.Max_Discount, bsr.Srq_Service_Cost,bsr.Srq_Material_Cost, bsr.Srq_Labour_Cost, bsr.Srq_Tax_Percentage, bsr.Srq_Tax, bsr.Srq_Discount_Amount, bsr.Srq_Other_Cost, bsr.Srq_Special_Discount, bsr.Srq_Final_Cost, bsr.Quote_Service_Cost,bsr.Quote_Labour_Cost, bsr.Quote_Material_Cost, bsr.Quote_Tax, bsr.Quote_Other_Cost, bsr.Srq_Quote_Cost, bsr.Srq_Stage_1, bsr.Srq_Stage_1_ts, bsr.Srq_Stage_2, bsr.Srq_Stage_2_ts, bsr.Srq_Stage_3, bsr.Srq_Stage_3_ts, bsr.Srq_Stage_4, bsr.Srq_Stage_4_ts, bsr.Srq_Stage_5, bsr.Srq_Stage_5_ts, bsr.Srq_Stage_6, bsr.Srq_Stage_6_ts, bsr.Srq_Stage_7, bsr.Srq_Stage_7_ts, bsr.Srq_Stage_8, bsr.Srq_Stage_8_ts, bsr.Srq_Stage_9, bsr.Srq_Stage_9_ts, bsr.Is_Happycode_Validated, bsr.Srq_Payment_Mode, bsr.Srq_Payment_Status, bsr.Srq_Quote_Status, bsr.Srq_Problem_Desc, bsr.Srq_Problem_Category,bpct.Problem_Name, bsr.Srq_Category_others,GROUP_CONCAT(bsi.Image_Path) As SRQ_Image, bsr.Srq_Visit_Timeslot, bsr.Srq_Instructions, bsr.Created_Date, bf.Feedback, bf.Rating FROM BPMS_SERVICE_REQUEST bsr INNER JOIN BPMS_SERVICE_TRANSLATIONS bst ON bsr.Srq_Service = bst.Service_Id And bst.Language_Code =? INNER JOIN BPMS_SERVICE bs ON bsr.Srq_Service = bs.Service_Id LEFT JOIN BPMS_APARTMENT ba ON bsr.Srq_Apartment_Id = ba.Apartment_Id INNER JOIN BPMS_APARTMENT_TRANSLATIONS bat ON bsr.Srq_Apartment_Id = bat.Apartment_Id AND bat.Language_Code =? INNER JOIN BPMS_STATE_TRANSLATIONS bsat ON ba.State = bsat.State_Id And bsat.Language_Code =? INNER JOIN BPMS_CITY bc ON ba.State = bc.State_Id INNER JOIN BPMS_CITY_TRANSLATIONS bct ON bc.City_Id = bct.City_Id AND bct.Language_Code =? INNER JOIN BPMS_USER bu ON bsr.User_Id = bu.User_Id LEFT JOIN BPMS_PROMO_MAPPING bpm ON bsr.Srq_Id = bpm.Srq_Id LEFT JOIN BPMS_PROMOCODE bp ON bpm.Promocode_Id = bp.Promocode_Id LEFT JOIN BPMS_PROBLEM_CATEGORIES_TRANSLATIONS bpct ON bsr.Srq_Problem_Category = bpct.Problem_Id AND bpct.Language_Code =? LEFT JOIN BPMS_FEEDBACK bf ON bsr.Srq_Id = bf.Service_Id LEFT JOIN BPMS_EMPLOYEE be ON bsr.Adm_Id = be.Emp_Id LEFT JOIN BPMS_EMPLOYEE bet ON bsr.Technician_Id = bet.Emp_Id LEFT JOIN BPMS_SRQ_IMAGE bsi ON bsr.Srq_Id = bsi.Srq_Id INNER JOIN BPMS_USER_APARTMENT_MAPPING buam ON bsr.Srq_Apartment_Id = buam.Apartment_Id AND bsr.User_Id = buam.User_Id WHERE bsr.Srq_Id=?"
	errQuery := tx.Select(&srq.ServiceList, getQuery, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Srq_Id)
	if errQuery != nil {
		tx.Rollback()
		log.Println("TechnicianRepository: TechUpdateServiceRequest: fetchingsrq : Srq_Id:", input.Srq_Id, " Error: ", errQuery.Error())
		return nil, errQuery
	} else {
		return &srq, nil
	}

}
func (technicianRepo *TechnicianRepository) GetPinConfirmation(tx *sqlx.Tx, input entity.GetConfirmationPinInput) (*entity.GetConfirmationPin, error) {

	srq := entity.GetConfirmationPin{}

	var status entity.Status
	var err error

	getStatus := "SELECT Srq_Stage_3, Srq_Stage_4 FROM BPMS_SERVICE_REQUEST WHERE Srq_Id = ?"
	err = tx.Get(&status, getStatus, input.Srq_Id)
	if err != nil {
		tx.Rollback()
		log.Println("TechnicianRepository: TechUpdateServiceRequest: gettingstatus : Srq_Id:", input.Srq_Id, " Error: ", err.Error())
		return nil, err
	}

	if status.Srq_Stage_3 == 1 && status.Srq_Stage_4 == 0 {

		if strings.ToLower(input.Action) == "pin_generate" {

			srq.Pin = util.GenerateOTP()
			srq.Message = "Pin generated successfully"

			log.Println("TechnicianRepository: TechUpdateServiceRequest: pingeneration : Srq_Id:", input.Srq_Id, " pin: ", srq.Pin)
			return &srq, nil

		} else if strings.ToLower(input.Action) == "pin_validate" {

			validateQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET Is_Happycode_Validated = 1 WHERE Srq_Id = ?"
			_, err1 := tx.Exec(validateQuery, input.Srq_Id)
			if err1 != nil {
				tx.Rollback()
				log.Println("TechnicianRepository: TechUpdateServiceRequest: pinvalidation :input: Srq_Id:", input.Srq_Id, "Error: ", err1.Error())
				return nil, err1
			}

			srq.Message = "Pin validated successfully"
			log.Println("TechnicianRepository: TechUpdateServiceRequest: pinvalidation : Srq_Id:", input.Srq_Id)
			return &srq, nil

		}

	} else {
		log.Println("TechnicianRepository: TechUpdateServiceRequest: pingeneration : Srq_Id:", input.Srq_Id)
		return nil, errors.New(dto.ErrorInvalidActionPin)
	}
	return nil, errors.New("this action is not applicable")
}
func (technicianRepo *TechnicianRepository) FetchRole(input entity.GetRoleInput) (*entity.GetRole, error) {
	mapped_role := entity.GetRole{}
	var roles []string
	var passcode string
	err1 := technicianRepo.db.Get(&passcode, "SELECT Passcode FROM BPMS_EMPLOYEE WHERE Emp_Mobno =?", input.Phone_Number)
	if err1 != nil {
		log.Print("TechnicianRepository: FetchRole: input : phone_number ", input.Phone_Number, " Error:", err1.Error())
		return nil, err1
	} else {
		mapped_role.Passcode = &passcode
	}

	err := technicianRepo.db.Select(&roles, "SELECT r.Role_Decription FROM BPMS_ROLE_MASTER r INNER JOIN BPMS_ROLE_MAPPING rm ON r.Role_Id = rm.Role_Id INNER JOIN BPMS_EMPLOYEE e ON rm.Emp_Id = e.Emp_Id WHERE Emp_Mobno =?", input.Phone_Number)
	if err != nil {
		log.Print("TechnicianRepository: FetchRole: input : phone_number ", input.Phone_Number, " Error:", err.Error())
		return nil, err
	} else {
		mapped_role.Mapped_Role = roles

	}
	return &mapped_role, nil
}

// func (technicianRepo *TechnicianRepository) FetchAllEmployee(input entity.GetAllEmployeeInput) (*entity.GetAllEmployee, error) {
// 	var getQuery string
// 	var err, errCount error
// 	empList := entity.GetAllEmployee{}
// 	emp_info := []entity.EmployeeLists{}
// 	count := empList.Total_Employee
// 	//service id
// 	if input.Service_Id != 0 {
// 		getQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id WHERE bsem.Service_Id =? GROUP BY e.Emp_Id limit ? offset ?"
// 		err = technicianRepo.db.Select(&emp_info, getQuery, input.Service_Id, input.PageLimit, (input.Page-1)*input.PageLimit)
// 		errCount = technicianRepo.db.Get(&count, "SELECT count(e.Emp_Id) as Total_Employee FROM BPMS_EMPLOYEE e INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id WHERE bsem.Service_Id =?", input.Service_Id)
// 		//apartment id
// 	} else if input.Srq_Apartment_Id != 0 {
// 		getQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_EMP_APARTMENT_MAPPING beam ON e.Emp_Id = beam.Emp_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id WHERE beam.Apartment_Id =? OR bsem.Service_Id =? GROUP BY e.Emp_Id limit ? offset ?"
// 		err = technicianRepo.db.Select(&emp_info, getQuery, input.Srq_Apartment_Id, input.PageLimit, (input.Page-1)*input.PageLimit)
// 		errCount = technicianRepo.db.Get(&count, "SELECT count(e.Emp_Id) as Total_Employee FROM BPMS_EMPLOYEE e INNER JOIN BPMS_EMP_APARTMENT_MAPPING beam ON e.Emp_Id = beam.Emp_Id WHERE beam.Apartment_Id =?", input.Srq_Apartment_Id)
// 		//available emp
// 	} else if input.IsAbsent == 1 {
// 		getQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE e.Is_Available =1 GROUP BY e.Emp_Id limit ? offset ?"
// 		err = technicianRepo.db.Select(&emp_info, getQuery, input.PageLimit, (input.Page-1)*input.PageLimit)
// 		errCount = technicianRepo.db.Get(&count, "SELECT count(Emp_Id) as Total_Employee FROM BPMS_EMPLOYEE WHERE  Is_Available =1")

// 	} else if input.Name != "" {
// 		getQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id WHERE e.Emp_Name LIKE ? GROUP BY e.Emp_Id limit ? offset ?"
// 		err = technicianRepo.db.Select(&emp_info, getQuery, "%"+input.Name+"%", input.PageLimit, (input.Page-1)*input.PageLimit)
// 		errCount = technicianRepo.db.Get(&count, "SELECT count(Emp_Id) as Total_Employee FROM BPMS_EMPLOYEE WHERE Emp_Name LIKE ?", "%"+input.Name+"%")
// 	}
// 	// else {
// 	// 	getQuery = "SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id GROUP BY e.Emp_Id limit ? offset ?"
// 	// 	err = technicianRepo.db.Select(&emp_info, getQuery, input.PageLimit, (input.Page-1)*input.PageLimit)
// 	// 	errCount = technicianRepo.db.Get(&count, "SELECT count(Emp_Id) as Total_Employee FROM BPMS_EMPLOYEE")
// 	// }
// 	if errCount != nil {
// 		log.Println("TechnicianRepository: FetchAllEmployee: totalcount : Error: ", errCount.Error())
// 		return nil, err
// 	}
// 	if err != nil {
// 		log.Println("TechnicianRepository: FetchAllEmployee: Error: ", err.Error())
// 		return nil, err
// 	} else {

// 		for i := range emp_info {
// 			skilled_service, err1 := technicianRepo.FetchSkilledServices(emp_info[i].Emp_Id)

// 			if err1 != nil {

// 				log.Print("TechnicianRepository: FetchAllEmployee: FetchSkilledServices : Error:", err1.Error())
// 				return nil, err1
// 			}
// 			emp_info[i].Skilled_Services = skilled_service

// 		}
// 		for i := range emp_info {
// 			allocated_apartment, err1 := technicianRepo.FetchAllocatedApartment(emp_info[i].Emp_Id)

// 			if err1 != nil {

// 				log.Print("TechnicianRepository: FetchAllEmployee: FetchAllocatedApartments : Error:", err1.Error())
// 				return nil, err1
// 			}
// 			emp_info[i].Allocated_Apartments = allocated_apartment

// 		}
// 		var roles []string
// 		for i := range emp_info {
// 			errIn := technicianRepo.db.Select(&roles, "SELECT r.Role_Decription FROM BPMS_ROLE_MASTER r INNER JOIN BPMS_ROLE_MAPPING rm ON r.Role_Id = rm.Role_Id INNER JOIN BPMS_EMPLOYEE e ON rm.Emp_Id = e.Emp_Id WHERE e.Emp_Id =?", (emp_info[i].Emp_Id))
// 			if errIn != nil {
// 				log.Print("TechnicianRepository: FetchAllEmployee: input : emp_id ", emp_info[i].Emp_Id, " Error:", errIn.Error())
// 				return nil, errIn
// 			} else {
// 				emp_info[i].Role = roles
// 			}
// 		}

// 		empList.EmployeeList = emp_info
// 		empList.Page = count / input.PageLimit
// 		if count%input.PageLimit != 0 {
// 			empList.Page++
// 		}
// 		empList.Total_Employee = count
// 		return &empList, nil
// 	}
// }

// a:="SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_EMP_APARTMENT_MAPPING beam ON e.Emp_Id = beam.Emp_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id"

func (technicianRepo *TechnicianRepository) FetchAllEmployee(input entity.GetAllEmployeeInput) (*entity.GetAllEmployee, error) {
	var getQuery, countQuery bytes.Buffer
	var err, errCount error

	empList := entity.GetAllEmployee{}
	emp_info := []entity.EmployeeLists{}
	count := empList.Total_Employee

	getQuery.WriteString("SELECT e.Emp_Id, e.Emp_Name, e.Passcode, e.Emp_Mobno, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Emp_DOJ, e.Emp_DOR, e.Emp_Image, ad.Address_Line1, ad.Address_Line2, ad.Area_Name, ad.City, ad.State, ad.Country, ad.Pincode, AVG(fd.Rating) AS Average_Rating FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_EMP_APARTMENT_MAPPING beam ON e.Emp_Id = beam.Emp_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id WHERE e.Emp_Status !='inactive'")
	countQuery.WriteString("SELECT COUNT(DISTINCT e.Emp_ID) FROM BPMS_EMPLOYEE e  LEFT JOIN BPMS_FEEDBACK fd ON e.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON e.Address_Id = ad.Address_Id INNER JOIN BPMS_EMP_APARTMENT_MAPPING beam ON e.Emp_Id = beam.Emp_Id INNER JOIN BPMS_SERVICE_EMP_MAPPING bsem ON e.Emp_Id = bsem.Emp_Id WHERE e.Emp_Status !='inactive'")
	if input.Name != "" || input.Srq_Apartment_Id != 0 || input.Service_Id != 0 || input.IsAbsent != 0 {
		getQuery.WriteString("  AND ")
		countQuery.WriteString(" AND ")
		var srq_apartment_id, service_id, is_absent string

		if input.Srq_Apartment_Id != 0 {
			srq_apartment_id = strconv.Itoa(input.Srq_Apartment_Id)
		} else {
			srq_apartment_id = "NULL"
		}

		if input.Service_Id != 0 {
			service_id = strconv.Itoa(input.Service_Id)
		} else {
			service_id = "NULL"
		}

		if input.IsAbsent != 0 {
			is_absent = strconv.Itoa(input.IsAbsent)
		} else {
			is_absent = "NULL"
		}

		getQuery.WriteString("(" + srq_apartment_id + " IS NULL OR beam.Apartment_Id = " + srq_apartment_id + " ) AND  (" + service_id + "  IS NULL OR bsem.Service_Id = " + service_id + " ) AND  (" + is_absent + "  IS NULL OR e.Is_Available  = " + is_absent + ")")
		countQuery.WriteString("(" + srq_apartment_id + " IS NULL OR beam.Apartment_Id = " + srq_apartment_id + " ) AND  (" + service_id + "  IS NULL OR bsem.Service_Id = " + service_id + " ) AND  (" + is_absent + "  IS NULL OR e.Is_Available  = " + is_absent + ")")

		if input.Name != "" {
			getQuery.WriteString(" AND e.Emp_Name LIKE '%" + input.Name + "%' ")
			countQuery.WriteString(" AND e.Emp_Name LIKE '%" + input.Name + "%' ")
		}

	}
	errCount = technicianRepo.db.Get(&count, countQuery.String())
	getQuery.WriteString(" GROUP BY e.Emp_Id limit ? offset ?")

	err = technicianRepo.db.Select(&emp_info, getQuery.String(), input.PageLimit, (input.Page-1)*input.PageLimit)

	if errCount != nil {
		log.Println("TechnicianRepository: FetchAllEmployee: totalcount : Error: ", errCount.Error())
		return nil, err
	}
	if err != nil {
		log.Println("TechnicianRepository: FetchAllEmployee: Error: ", err.Error())
		return nil, err
	} else {

		for i := range emp_info {
			skilled_service, err1 := technicianRepo.FetchSkilledServices(emp_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchAllEmployee: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			emp_info[i].Skilled_Services = skilled_service

		}
		for i := range emp_info {
			allocated_apartment, err1 := technicianRepo.FetchAllocatedApartment(emp_info[i].Emp_Id)

			if err1 != nil {

				log.Print("TechnicianRepository: FetchAllEmployee: FetchAllocatedApartments : Error:", err1.Error())
				return nil, err1
			}
			emp_info[i].Allocated_Apartments = allocated_apartment

		}
		var roles []string
		for i := range emp_info {
			errIn := technicianRepo.db.Select(&roles, "SELECT r.Role_Decription FROM BPMS_ROLE_MASTER r INNER JOIN BPMS_ROLE_MAPPING rm ON r.Role_Id = rm.Role_Id INNER JOIN BPMS_EMPLOYEE e ON rm.Emp_Id = e.Emp_Id WHERE e.Emp_Id =?", (emp_info[i].Emp_Id))
			if errIn != nil {
				log.Print("TechnicianRepository: FetchAllEmployee: input : emp_id ", emp_info[i].Emp_Id, " Error:", errIn.Error())
				return nil, errIn
			} else {
				emp_info[i].Role = roles
			}
		}

		empList.EmployeeList = emp_info
		empList.Page = count / input.PageLimit
		if count%input.PageLimit != 0 {
			empList.Page++
		}
		empList.Total_Employee = count
		return &empList, nil
	}
}
