package repository

import (
	"bpmsapi/dto"
	"bpmsapi/entity"
	"errors"
	"fmt"
	"log"
	"strings"

	"github.com/jmoiron/sqlx"
)

type AdminActions interface {
	FetchAdminbyMobno(mobno string, languagecode int) (*entity.Admin, error)
	FetchMappedApartements(mobno string, languagecode int) (result *[]entity.Mapper, err error)
	FetchMappedRoles(mobno string, languagecode int) (result *[]entity.Mapper, err error)
	FetchMappedServices(mobno string, languagecode int) (result *[]entity.Mapper, err error)
	FetchAdminList(input entity.GetAdminListInput) (*entity.GetAdminList, error)
	SignUpAdmin(tx *sqlx.Tx, input entity.SignUpAdminInput, img_path string) (bool, error)
	SrqCostUpdate(tx *sqlx.Tx, input entity.UpdateSrqCostInput) ([]entity.ServiceRequest, error)
	CreateChildSrq(tx *sqlx.Tx, input entity.CreateChildServiceRequestInput) ([]entity.ServiceRequest, error)
}

type AdminRepository struct {
	db *sqlx.DB
}

// auth-jwt
func AdminRepositoryInit(db *sqlx.DB) *AdminRepository {
	return &AdminRepository{
		db: db,
	}
}

func (adminRepo *AdminRepository) Begin() (*sqlx.Tx, error) {
	tx, err := adminRepo.db.Beginx()
	if err != nil {
		return nil, err
	}
	return tx, nil
}

func (adminRepo *AdminRepository) FetchAdminbyMobno(mobileno string, languagecode int) (*entity.Admin, error) {
	adminResponse := entity.Admin{}
	err := adminRepo.db.Get(&adminResponse, "SELECT be.Emp_Id, be.Emp_Name, be.Passcode, be.Emp_Email, be.Emp_Mobno, be.Alternate_Mobno, be.Address_Id, be.Emp_Type, be.Emp_DOB, be.Emp_DOJ, be.Emp_DOR, be.Emp_Status, be.Emp_Rejoining, be.Emp_Image, be.Emp_Proof, be.Emp_Proof_Id, be.Vendor_name, be.Is_Available, be.Created_Timestamp, be.Recent_Login,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 be LEFT JOIN BPMS_FEEDBACK fd ON be.Emp_Id = fd.Technician_Id LEFT JOIN BPMS_ADDRESS ad ON be.Address_Id = ad.Address_Id WHERE be.Emp_Mobno=? GROUP BY be.Emp_Id", mobileno)
	adminResponse.Allocated_Apartments, _ = adminRepo.FetchAllocatedApartmentMobno(mobileno, languagecode)
	adminResponse.Mapped_Roles, _ = adminRepo.FetchMappedRoles(mobileno, languagecode)
	adminResponse.Skilled_Services, _ = adminRepo.FetchSkilledServicesMobno(mobileno, languagecode)
	if err != nil {
		log.Print("AdminRepository: FetchAdminbyMobno: fetchingadmin: Input: ", mobileno, " Error: ", err.Error())
		return nil, err
	} else {
		return &adminResponse, nil
	}

}
func (adminRepo *AdminRepository) FetchSkilledServicesMobno(mobno string, languangecode int) (mapped_service []entity.SkilledService, err error) {
	err = adminRepo.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 =? INNER JOIN BPMS_EMPLOYEE e ON bsem.Emp_Id = e.Emp_Id LEFT JOIN BPMS_SERVICE s ON bsem.Service_Id = s.Service_Id WHERE e.Emp_Mobno = ?", languangecode, mobno)
	if err != nil {
		log.Print("AdminRepository: FetchSkilledServicesMobno: input : Mobno ", mobno, " Error:", err.Error())
		return nil, err
	} else {
		return mapped_service, nil
	}
}

func (adminRepo *AdminRepository) FetchAllocatedApartmentMobno(mobno string, languagecode int) (emp_apartments []entity.EmpAllocatedApartment, err error) {
	err = adminRepo.db.Select(&emp_apartments, "SELECT beam.Apartment_Id, bat.Apartment_Name  FROM BPMS_EMP_APARTMENT_MAPPING beam INNER JOIN BPMS_APARTMENT_TRANSLATIONS bat ON beam.Apartment_Id = bat.Apartment_Id AND bat.Language_Code = ? INNER JOIN BPMS_EMPLOYEE e on e.Emp_Id = beam.Emp_Id  WHERE e.Emp_Mobno = ?", languagecode, mobno)
	if err != nil {
		log.Print("AdminRepository: FetchAllocatedApartmentMobno: input : Mobilenumber ", mobno, " Error:", err.Error())
		return nil, err
	} else {
		return emp_apartments, nil
	}
}

func (adminRepo *AdminRepository) FetchMappedApartements(mobno string, languagecode int) (mappedFlats []string, err error) {
	rows, err := adminRepo.db.Query("SELECT atrans.Apartment_Name as Name from BPMS_EMP_APARTMENT_MAPPING amap INNER JOIN BPMS_EMPLOYEE e on e.Emp_Id = amap.Emp_Id INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on amap.Emp_Id = atrans.Apartment_Id and atrans.Language_Code = ? where e.Emp_Mobno = ?", languagecode, mobno)
	for rows.Next() {
		var flat string
		rows.Scan(&flat)
		mappedFlats = append(mappedFlats, flat)
	}
	if err != nil {
		log.Print("AdminRepository: FetchMappedApartements: fetchingapartments: Input: ", mobno, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedFlats, nil
	}
}

func (adminRepo *AdminRepository) FetchMappedServices(mobno string, languagecode int) (mappedServices []string, err error) {
	rows, err := adminRepo.db.Query("SELECT strans.Service_Name as Name from BPMS_SERVICE_EMP_MAPPING smap INNER JOIN BPMS_EMPLOYEE e on e.Emp_Id = smap.Emp_Id INNER JOIN BPMS_SERVICE_TRANSLATIONS strans on smap.Service_Id = strans.Service_Id and strans.Language_Code = ? where e.Emp_Mobno = ?", languagecode, mobno)
	for rows.Next() {
		var service string
		rows.Scan(&service)
		mappedServices = append(mappedServices, service)
	}
	if err != nil {
		log.Print("AdminRepository: FetchMappedServices: fetchingservices: Input: ", mobno, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedServices, nil
	}
}

func (adminRepo *AdminRepository) FetchMappedRoles(mobno string, languagecode int) (mappedRoles []string, err error) {
	rows, err := adminRepo.db.Query("SELECT btrans.Role_Name as Name from BPMS_ROLE_MAPPING rmap INNER JOIN BPMS_EMPLOYEE e on e.Emp_Id = rmap.Emp_Id INNER JOIN BPMS_ROLE_MASTER_TRANSLATIONS btrans on rmap.Role_Id = btrans.Role_Id and btrans.Language_Code = ? where e.Emp_Mobno = ?", languagecode, mobno)
	for rows.Next() {
		var role string
		rows.Scan(&role)
		mappedRoles = append(mappedRoles, role)
	}
	if err != nil {
		log.Print("AdminRepository: FetchMappedRoles: fetchingroles: Input: ", mobno, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedRoles, nil
	}
}

func (adminRepo *AdminRepository) SignUpAdmin(tx *sqlx.Tx, input entity.SignUpAdminInput, img_path string) (bool, error) {
	AddressQuery := "INSERT INTO `BPMS_ADDRESS`(`Country`, `State`, `City`, `Area_Name`, `Address_Line1`, `Address_Line2`, `Pincode`, `Latitude`, `Longitude`) VALUES (?,?,?,?,?,?,?,?,?)"
	addressInsert, err := tx.Exec(AddressQuery, input.Country, input.State, input.City, input.Area_Name, input.Address_Line1, input.Address_Line2, input.Pincode, input.Latitude, input.Longitude)
	if err != nil {
		tx.Rollback()
		log.Println("AdminRepository: SignUpAdmin: Input: ", input, "Error ", err.Error())
		return false, err
	}

	last_addressId, inserr := addressInsert.LastInsertId()
	if inserr != nil {
		log.Println("AdminRepository: SignUpAdmin: Input: ", last_addressId, input, "Error-last-inserted-ID ", inserr.Error())
		return false, inserr
	}
	EmpQuery := "INSERT INTO `BPMS_EMPLOYEE` (`Emp_Name`,`Passcode`, `Emp_Email`, `Emp_Mobno`, `Alternate_Mobno`, `Address_Id`,`Emp_Type`, `Emp_DOB`, `Emp_DOJ`, `Emp_DOR`, `Emp_Status`, `Emp_Image`, `Emp_Proof`, `Emp_Proof_Id`, `Vendor_name`) VALUES(?,?,?,?,?,?,?,?,?,?,'Active',?,?,?,?)"
	insert, err1 := tx.Exec(EmpQuery, input.Name, input.Passcode, input.Email, input.Primary_Contact, input.Emergency_Contact, last_addressId, input.Employee_Type, input.Date_of_Birth, input.Date_of_Joining, input.Date_of_Resigning, img_path, input.Proof, input.Proof_ID, input.Employer_Name)
	if err1 != nil {
		tx.Rollback()
		log.Println("AdminRepository: SignUpAdmin: Input: ", input.Name, input.Role_ID, "Error1 ", err1.Error())
		return false, err1
	}

	last_empid, inserr1 := insert.LastInsertId()
	if inserr1 != nil {
		log.Println("AdminRepository: SignUpAdmin: Input: ", input.Name, input.Role_ID, "Error-last-inserted-ID ", inserr1.Error())
		return false, inserr1
	}

	//handled the comma separated string from input
	if input.Allocated_Apartments != "" {
		allocated_apartments := strings.Split(input.Allocated_Apartments, ",")
		for _, allocated_apartment := range allocated_apartments {
			EmpApartmentQuery := "INSERT INTO `BPMS_EMP_APARTMENT_MAPPING`( `Emp_Id`, `Apartment_Id`) VALUES (?,?)"
			_, err2 := tx.Exec(EmpApartmentQuery, last_empid, allocated_apartment) //
			if err2 != nil {
				tx.Rollback()
				log.Println("AdminRepository: SignUpAdmin: Input: ", last_empid, input.Name, input.Role_ID, "Error2 ", err2.Error())
				return false, err2
			}
		}
	}
	if input.Role_ID == 2 {
		RoleIDs := []int{1, 2}
		for _, roleID := range RoleIDs {
			RoleQuery := "INSERT INTO `BPMS_ROLE_MAPPING`(`Emp_Id`, `Role_Id`) VALUES (?,?)"
			_, err3 := tx.Exec(RoleQuery, last_empid, roleID)
			if err3 != nil {
				tx.Rollback()
				log.Println("AdminRepository: SignUpAdmin: Input: ", last_empid, input.Name, roleID, "Error3 ", err3.Error())
				return false, err3
			}
		}
	} else {
		RoleQuery := "INSERT INTO `BPMS_ROLE_MAPPING`(`Emp_Id`, `Role_Id`) VALUES (?,?)"
		_, err3 := tx.Exec(RoleQuery, last_empid, input.Role_ID)
		if err3 != nil {
			tx.Rollback()
			log.Println("AdminRepository: SignUpAdmin: Input: ", last_empid, input.Name, input.Role_ID, "Error3 ", err3.Error())
			return false, err3
		}
	}
	if input.Skilled_Services != "" {
		service_ids := strings.Split(input.Skilled_Services, ",")
		for _, service_id := range service_ids {
			fmt.Println("service", service_id)
			ServiceQuery := "INSERT INTO `BPMS_SERVICE_EMP_MAPPING`(`Emp_Id`, `Service_Id`) VALUES (?,?)"

			_, err4 := tx.Exec(ServiceQuery, last_empid, service_id)
			if err4 != nil {
				tx.Rollback()
				log.Println("AdminRepository: SignUpAdmin: Input: ", last_empid, input.Name, input.Role_ID, "Error4 ", err4.Error())
				return false, err4
			}
		}
	}
	return true, nil
}

func (adminRepo *AdminRepository) FetchAdminList(input entity.GetAdminListInput) (*entity.GetAdminList, error) {
	var query string
	var err, errCount error
	adminList := entity.GetAdminList{}
	admin_info := []entity.AdminList{}
	count := adminList.Total_Admin

	if input.Emp_Id != 0 {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Emp_Mobno, e.Passcode, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Vendor_Name, 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_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=2 AND e.Emp_Status !='inactive' AND e.Emp_Id = ? GROUP BY e.Emp_Id limit ? offset ? `

		err = adminRepo.db.Select(&admin_info, query, input.Emp_Id, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = adminRepo.db.Get(&count, "SELECT COUNT(brm.Emp_Id) AS Total_Admin FROM BPMS_ROLE_MAPPING brm LEFT JOIN BPMS_EMPLOYEE be ON brm.Emp_Id = be.Emp_Id WHERE brm.Role_Id =2 AND be.Emp_Status !='inactive' AND be.Emp_Id = ?", input.Emp_Id)
		if errCount != nil {
			log.Println("AdminRepository: FetchAdminList: totalAdminCount: Input: Emp_Id:", input.Emp_Id, "  Error:", errCount.Error())
		}

	} else if input.Phone_Number != 0 {

		query = `SELECT e.Emp_Id, e.Emp_Name, e.Emp_Mobno, e.Passcode, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Vendor_Name, 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_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=2 AND e.Emp_Status !='inactive' AND e.Emp_Mobno = ? GROUP BY e.Emp_Id limit ? offset ? `

		err = adminRepo.db.Select(&admin_info, query, input.Phone_Number, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = adminRepo.db.Get(&count, "SELECT COUNT(brm.Emp_Id) AS Total_Admin FROM BPMS_ROLE_MAPPING brm LEFT JOIN BPMS_EMPLOYEE be ON brm.Emp_Id = be.Emp_Id WHERE brm.Role_Id =2 AND be.Emp_Status !='inactive' AND be.Emp_Mobno = ?", input.Phone_Number)
		if errCount != nil {
			log.Println("AdminRepository: FetchAdminList: totalAdminCount: Input: mobno:", input.Phone_Number, "  Error:", errCount.Error())
		}

	} else if input.Action == "all" {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Emp_Mobno, e.Passcode, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Vendor_Name, 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_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 = 2 GROUP BY e.Emp_Id limit ? offset ?`
		err = adminRepo.db.Select(&admin_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = adminRepo.db.Get(&count, "SELECT COUNT(Emp_Id) AS Total_Technician FROM BPMS_ROLE_MAPPING WHERE Role_Id =1")
		if errCount != nil {
			log.Println("AdminRepository: FetchAdminList: totalAdminCount: Error:", errCount.Error())
		}
	} else if input.Action == "inactive" {
		query = `SELECT e.Emp_Id, e.Emp_Name, e.Emp_Mobno, e.Passcode, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Vendor_Name, 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_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 = 2 AND e.Emp_Status ='inactive' GROUP BY e.Emp_Id limit ? offset ?`
		err = adminRepo.db.Select(&admin_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = adminRepo.db.Get(&count, " SELECT COUNT(e.Emp_Id) AS Total_Admin FROM BPMS_EMPLOYEE e INNER JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id  WHERE r.Role_Id =2 AND e.Emp_Status ='inactive' ")
		if errCount != nil {
			log.Println("AdminRepository: FetchAdminList: totalAdminCount: Error:", errCount.Error())
		}
	} else {

		query = `SELECT e.Emp_Id, e.Emp_Name, e.Emp_Mobno, e.Passcode, e.Alternate_Mobno, e.Emp_Email, e.Emp_Proof, e.Emp_Proof_Id, e.Emp_Type, e.Vendor_Name, 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_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 = 2 AND e.Emp_Status !='inactive' GROUP BY e.Emp_Id limit ? offset ?`
		err = adminRepo.db.Select(&admin_info, query, input.PageLimit, (input.Page-1)*input.PageLimit)
		errCount = adminRepo.db.Get(&count, " SELECT COUNT(e.Emp_Id) AS Total_Admin FROM BPMS_EMPLOYEE e INNER JOIN BPMS_ROLE_MAPPING r ON e.Emp_Id = r.Emp_Id  WHERE r.Role_Id =2 AND e.Emp_Status !='inactive' ")
		if errCount != nil {
			log.Println("AdminRepository: FetchAdminList: totalAdminCount: Error:", errCount.Error())
		}
	}
	if err != nil {
		log.Println("AdminRepository: FetchAdminList: Error: ", err.Error())
		return nil, err
	} else {
		log.Println("AdminRepository: FetchAdminList: input", input.Emp_Id)
		for i := range admin_info {
			skilled_service, err1 := adminRepo.FetchSkilledServices(admin_info[i].Emp_Id)

			if err1 != nil {

				log.Print("AdminRepository: FetchAdminList: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			admin_info[i].Skilled_Services = skilled_service

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

			if err1 != nil {

				log.Print("AdminRepository: FetchAdminList: FetchSkilledServices : Error:", err1.Error())
				return nil, err1
			}
			admin_info[i].Allocated_Apartments = allocated_apartment

		}
		adminList.AdminLists = admin_info
		adminList.Page = count / input.PageLimit
		if count%input.PageLimit != 0 {
			adminList.Page++
		}

		adminList.Total_Admin = count
		return &adminList, nil
	}
}
func (adminRepo *AdminRepository) FetchSkilledServices(empid int) (mapped_service []entity.SkilledService, err error) {
	err = adminRepo.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("AdminRepository: FetchSkilledServices: input : EmpId ", empid, " Error:", err.Error())
		return nil, err
	} else {
		return mapped_service, nil
	}
}

func (adminRepo *AdminRepository) FetchAllocatedApartment(empid int) (emp_apartments []entity.EmpAllocatedApartment, err error) {
	err = adminRepo.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("AdminRepository: FetchAllocatedApartment: input : EmpId ", empid, " Error:", err.Error())
		return nil, err
	} else {
		return emp_apartments, nil
	}
}

func (adminRepo *AdminRepository) SrqCostUpdate(tx *sqlx.Tx, input entity.UpdateSrqCostInput) (*entity.UpdateSrqCost, error) {
	var err error
	var totalCost, totalQuoteCost, finalCost, final_cost, dis_amount, discount_amount, amount, srq_tax, tax_percent float64
	//getoldcost
	costDetails := entity.CostInfo{}
	costQueryErr := tx.Get(&costDetails, "SELECT Srq_Service_Cost, Srq_Material_Cost, Srq_Labour_Cost, Srq_Tax, Srq_Tax_Percentage, Srq_Final_Cost, Srq_Discount_Amount, Srq_Other_Cost, Quote_Service_Cost, Quote_Labour_Cost, Quote_Material_Cost, Quote_Tax, Quote_Other_Cost, Srq_Quote_Cost, Srq_Quote_Status,Srq_Special_Discount FROM BPMS_SERVICE_REQUEST WHERE Srq_Id= ?", input.Srq_Id)
	if costQueryErr != nil {
		log.Print("AdminRepository: SrqCostUpdate: getoldcostupdate: input : Srq_Id ", input.Srq_Id, " Error:", costQueryErr.Error())
		return nil, err
	}

	if input.Srq_Status == 1 {
		totalCost = input.SrqServiceCost + input.SrqMaterialCost + input.SrqLabourCost + input.SrqOtherCost
		//promo id
		if input.Promo_Id != 0 {
			promoInfo := entity.Promocode{}
			getPromoQuery := tx.Get(&promoInfo, "SELECT `Discount`, `Max_Discount` FROM `BPMS_PROMOCODE` WHERE `Promocode_Id` = ?", input.Promo_Id)
			if getPromoQuery != nil {
				log.Println("AdminRepository: SrqCostUpdate: fetchingpromoinfo: Input: Promocode:", input.Promo_Id, "  Error:", getPromoQuery.Error())
				return nil, getPromoQuery
			} else {
				//labour + service
				amount = input.SrqLabourCost + input.SrqServiceCost
				//discount percentage
				discount := *promoInfo.Discount
				//max discount amount
				maxDiscount := *promoInfo.Max_Discount
				//discount calculation
				dis_amount = (discount / 100) * amount

				if dis_amount > maxDiscount {
					discount_amount = maxDiscount
				} else {
					discount_amount = dis_amount
				}
				totalCost -= discount_amount
				updatePromoCost := "UPDATE BPMS_SERVICE_REQUEST SET Srq_Discount_Amount = ? WHERE Srq_Id = ?"
				_, err1 := tx.Exec(updatePromoCost, discount_amount, input.Srq_Id)
				if err1 != nil {
					tx.Rollback()
					log.Println("AdminRepository: SrqCostUpdate: discountcostupdate: Input: Srq_Id:", input.Srq_Id, "  Error:", err1.Error())
					return nil, err1
				}
			}

		}
		finalCost = totalCost
		//tax
		tax_percent = input.Srq_Tax_Percentage
		srq_tax = (tax_percent / 100) * finalCost
		final_cost = finalCost + srq_tax - input.SrqSpecialDiscount
		finalQuery := "UPDATE BPMS_SERVICE_REQUEST SET Srq_Service_Cost = IFNULL(?,Srq_Service_Cost), Srq_Material_Cost = IFNULL(?,Srq_Material_Cost), Srq_Labour_Cost =IFNULL(?,Srq_Labour_Cost), Srq_Tax =IFNULL(?,Srq_Tax), Srq_Special_Discount = IFNULL(?,Srq_Special_Discount), Srq_Final_Cost = IFNULL(?,Srq_Final_Cost), Srq_Other_Cost=IFNULL(?,Srq_Other_Cost), Srq_Payment_Mode =IFNULL(?,Srq_Payment_Mode) WHERE Srq_Id = ?"

		_, err2 := tx.Exec(finalQuery, input.SrqServiceCost, input.SrqMaterialCost, input.SrqLabourCost, srq_tax, input.SrqSpecialDiscount, final_cost, input.SrqOtherCost, input.PaymentMode, input.Srq_Id)
		if err2 != nil {
			tx.Rollback()
			log.Println("AdminRepository: SrqCostUpdate: finalcostupdate: Input: Srq_Id:", input.Srq_Id, "  Error:", err2.Error())
			return nil, err2
		}
	}
	//quote amount
	//1-created quote
	if input.Quote_Status == 1 {
		totalQuoteCost = input.QuoteMaterialCost + input.QuoteLabourCost + input.QuoteServiceCost + input.QuoteTax + input.QuoteOtherCost
		updateQuoteQuery := "UPDATE BPMS_SERVICE_REQUEST SET Srq_Quote_Status = ?,Quote_Labour_Cost = ?, Quote_Material_Cost = ?, Quote_Tax =?,Quote_Service_Cost =?, Quote_Other_Cost=?, Srq_Quote_Cost = ? WHERE Srq_Id = ?"
		_, err := tx.Exec(updateQuoteQuery, input.Quote_Status, input.QuoteLabourCost, input.QuoteMaterialCost, input.QuoteTax, input.QuoteServiceCost, input.QuoteOtherCost, totalQuoteCost, input.Srq_Id)
		if err != nil {
			tx.Rollback()
			log.Println("AdminRepository: SrqCostUpdate: quotecostupdate: Input: Srq_Id:", input.Srq_Id, "  Error:", err.Error())
			return nil, err
		}
		// totalCost += totalQuoteCost

	}

	insQuery := "INSERT INTO `BPMS_COST_HISTORY`(`Srq_Id`, `Old_Service_Cost`, `New_Service_Cost`, `Old_Material_Cost`, `New_Material_Cost`, `Old_Labour_Cost`, `New_Labour_Cost`, `Old_Tax`, `New_Tax`, `Old_Srq_Other_Cost`, `New_Srq_Other_Cost`, `Old_Special_Discount`, `New_Special_Discount`, `Old_Final_Cost`, `New_Final_Cost`, `Old_Discount_Amount`, `New_Discount_Amount`, `Old_Quote_Labour_Cost`, `New_Quote_Labour_Cost`, `Old_Quote_Material_Cost`, `New_Quote_Material_Cost`, `Old_Quote_Service_Cost`, `New_Quote_Service_Cost`, `Old_Quote_Tax`, `New_Quote_Tax`, `Old_Quote_Other_Cost`, `New_Quote_Other_Cost`, `Old_Quote_Cost`, `New_Quote_Cost`, `Old_Quote_Status`, `New_Quote_Status`, `Emp_Id`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
	_, errIn := tx.Exec(insQuery, input.Srq_Id, costDetails.Srq_Service_Cost, input.SrqServiceCost, costDetails.Srq_Material_Cost, input.SrqMaterialCost, costDetails.Srq_Labour_Cost, input.SrqLabourCost, costDetails.Srq_Tax, srq_tax, costDetails.Srq_Other_Cost, input.SrqOtherCost, costDetails.SrqSpecialDiscount, input.SrqSpecialDiscount, costDetails.Srq_Final_Cost, final_cost, costDetails.Srq_Discount_Amount, discount_amount, costDetails.Quote_Labour_Cost, input.QuoteLabourCost, costDetails.Quote_Material_Cost, input.QuoteMaterialCost, costDetails.Quote_Service_Cost, input.QuoteServiceCost, costDetails.Quote_Tax, input.QuoteTax, costDetails.Quote_Other_Cost, input.QuoteOtherCost, costDetails.Srq_Quote_Cost, totalQuoteCost, costDetails.Srq_Quote_Status, input.Quote_Status, input.Emp_Id)
	if errIn != nil {
		tx.Rollback()
		log.Println("AdminRepository: SrqCostUpdate: oldcostupdate: Input: ", input.Srq_Id, "Error ", errIn.Error())
		return nil, errIn
	}
	//response
	srq := entity.UpdateSrqCost{}
	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 INNER 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.ServiceRequest, 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("AdminRepository: SrqCostUpdate: fetchingsrq : Srq_Id:", input.Srq_Id, " Error: ", errQuery.Error())
		return nil, errQuery
	} else {
		return &srq, nil
	}
}

func (adminRepo *AdminRepository) CreateChildSrq(tx *sqlx.Tx, input entity.CreateChildServiceRequestInput, img_paths []string) (srq []entity.ServiceRequest, err error) {

	var status entity.Status

	getStatus := "SELECT Srq_Stage_5, Srq_Stage_6 FROM BPMS_SERVICE_REQUEST WHERE Srq_Id = ?"
	err = tx.Get(&status, getStatus, input.Parent_Request)
	if err != nil {
		tx.Rollback()
		log.Println("AdminRepository: CreateChildSrq: gettingstatus : Srq_Id:", input.Parent_Request, " Error: ", err.Error())
		return nil, err
	}

	if status.Srq_Stage_5 == 1 || status.Srq_Stage_6 == 1 {
		var lastInsertId int64
		var inserr error

		query := "INSERT INTO `BPMS_SERVICE_REQUEST`( `Srq_Service`, `Request_Type`, `Parent_Request`, `Srq_Apartment_Id`, `User_Id`, `Srq_Tax_Percentage`, `Srq_Stage_1`, `Srq_Stage_1_ts`, `Srq_Problem_Desc`, `Srq_Problem_Category`, `Srq_Category_others`, `Srq_Visit_Timeslot`, `Srq_Instructions`, `Created_Date`) VALUES (?, ?, ?, ?, ? , ?, 1,CURRENT_TIMESTAMP(), ?, ?, ?, ?,?, CURRENT_TIMESTAMP())"
		insert, err := tx.Exec(query, input.Srq_Service, input.Request_Type, input.Parent_Request, input.Srq_Apartment_Id, input.User_Id, input.Srq_Tax_Percentage, input.Srq_Problem_Desc, input.Srq_Problem_Category, input.Srq_Category_Others, input.Srq_Vist_Timeslot, input.Srq_Instructions)
		if err != nil {
			tx.Rollback()
			log.Println("AdminRepository: CreateChildSrq: childrequest: input: user_id", input.User_Id, " Parent_Request: ", input.Parent_Request, "Error: ", err.Error())
			return nil, err
		}

		lastInsertId, inserr = insert.LastInsertId()
		if inserr != nil {
			log.Println("AdminRepository: CreateChildSrq: childrequest: Error: ", inserr.Error())
			return nil, err
		}

		newkey := fmt.Sprintf("%d", lastInsertId)
		newKeyId := "SRQ-" + newkey

		keyQuery := "UPDATE BPMS_SERVICE_REQUEST SET Srq_Key=? WHERE Srq_Id = ?"
		res, errIn := tx.Exec(keyQuery, newKeyId, lastInsertId)
		if errIn != nil {
			tx.Rollback()
			log.Println("AdminRepository: CreateChildSrq: updatingSrqKey: input: srq_id:", lastInsertId, " Error: ", errIn.Error())
			return nil, errIn
		}

		rowsAffected, _ := res.RowsAffected()
		if rowsAffected == 0 {
			tx.Rollback()
			log.Println("AdminRepository: CreateChildSrq: input: srq_Id:", lastInsertId, " :rowsaffected ")
			return nil, errors.New(dto.ErrorrowsAffectedChildSrq)
		}
		//child key in parent request
		childQuery := "update `BPMS_SERVICE_REQUEST` set Child_Request =? Where Srq_Id = ?"
		_, errC := tx.Exec(childQuery, lastInsertId, input.Parent_Request)
		if errC != nil {
			tx.Rollback()
			log.Println("AdminRepository: CreateChildSrq: updatingchildsrqidParentsrq input: srq_Id:", input.Parent_Request, " : error : ", errC.Error())
			return nil, errC
		}
		//image
		if len(img_paths) > 0 {
			for _, img_path := range img_paths {
				imgQuery := "Insert INTO `BPMS_SRQ_IMAGE` (`Image_Path`,`Srq_Id`) values (?,?)"
				_, errImg := tx.Exec(imgQuery, img_path, lastInsertId)
				if errImg != nil {
					tx.Rollback()
					log.Println("AdminRepository: CreateChildSrq: imageupload: input: ", lastInsertId, " Error: ", errImg.Error())
					return nil, errImg
				}
			}
		}
		//image link
		if input.Srq_Images != "" {
			srq_images := strings.Split(input.Srq_Images, ",")
			for _, image := range srq_images {
				imageQuery := "Insert INTO `BPMS_SRQ_IMAGE` (`Image_Path`,`Srq_Id`) values (?,?)"
				_, errIm := tx.Exec(imageQuery, image, lastInsertId)
				if errIm != nil {
					tx.Rollback()
					log.Println("AdminRepository: CreateChildSrq: imageuploadlinks: input: ", lastInsertId, " Error: ", errIm.Error())
					return nil, errIm
				}
			}

		}

		//other category
		if input.Srq_Category_Others != "" {
			othersQuery := "UPDATE BPMS_SERVICE_REQUEST SET Srq_Problem_Category = 0 WHERE Srq_Id =?"
			resIns, errIns := tx.Exec(othersQuery, lastInsertId)
			if errIns != nil {
				tx.Rollback()
				log.Println("AdminRepository: CreateChildSrq: othercategory: input: ", lastInsertId, " Error: ", errIns.Error())
				return nil, errIns
			}

			rowsAffectedOthers, _ := resIns.RowsAffected()
			if rowsAffectedOthers == 0 {
				tx.Rollback()
				log.Println("AdminRepository: CreateChildSrq: othercategoryinput: srq_id:", lastInsertId, " :rowsaffected")
				return nil, errors.New(dto.ErrorrowsAffectedChildSrq)
			}
		}

		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, getQuery, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, lastInsertId)
		if errQuery != nil {
			log.Println("TechnicianRepository: TechUpdateServiceRequest: fetchingsrq : Srq_Id:", lastInsertId, " Error: ", errQuery.Error())
			return nil, errQuery
		} else {
			return srq, nil
		}

	} else {
		log.Println("AdminRepository: CreateChildSrq: invalidstatus: input: Parent_Srq ", input.Parent_Request)
		return nil, errors.New(dto.ChildParentSrqInvalidStatus)

	}
}

func (adminRepo *AdminRepository) UpdateServiceRequestAdmin(tx *sqlx.Tx, input entity.UpdateServiceRequestAdminInput) (*entity.AdminServiceRequest, error) {

	//assign
	if strings.ToLower(input.Assign) == "technician" {
		fmt.Println(input.Assign)
		fmt.Println(input.Technician_Id)
		fmt.Println(input.Adm_Id)
		if input.Adm_Id == input.Technician_Id {
			statusQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_2`=1, `Srq_Stage_2_ts`= CURRENT_TIMESTAMP(), `Technician_Id` = ?, `Adm_Id` = ? where `Srq_Id` = ?"
			_, err := tx.Exec(statusQuery, input.Technician_Id, input.Adm_Id, input.Srq_Id)
			if err != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: assigntechnician : adm_id: ", input.Adm_Id, ": srq_id:", input.Srq_Id, ": Error: ", err.Error())
				return nil, err
			}

		} else {
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Technician_Id` = ?, `Adm_Id` = ? where `Srq_Id` = ?"
			_, err := tx.Exec(query, input.Technician_Id, input.Adm_Id, input.Srq_Id)
			if err != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: assigntechnician : adm_id: ", input.Adm_Id, ": srq_id:", input.Srq_Id, ": Error: ", err.Error())
				return nil, err
			}
		}
	}

	//status
	if strings.ToLower(input.Action) == "status" {
		if (strings.ToLower(input.Old_status) == "assigned") && (strings.ToLower(input.New_status) == "in-progress") {
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_3`=1 , `Srq_Stage_3_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"
			_, errv := tx.Exec(query, input.Srq_Id)
			if errv != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: Input: srq_id:", input.Srq_Id, "new_status :", input.New_status, "Error :", errv.Error())
				return nil, errv
			}
		} else if (strings.ToLower(input.Old_status) == "in-progress") && (strings.ToLower(input.New_status) == "fixed") {

			var isPinValidated int
			getquery := "SELECT Is_Happycode_Validated FROM `BPMS_SERVICE_REQUEST` WHERE Srq_Id =?"

			errPin := tx.Get(&isPinValidated, getquery, input.Srq_Id)
			if errPin != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: gettinghappycode : Srq_Id:", input.Srq_Id, ": Error: ", errPin.Error())
				return nil, errPin
			} else {
				if isPinValidated == 1 {
					query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_4`=1 , `Srq_Stage_4_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =? AND Is_Happycode_Validated = 1 "
					_, errv := tx.Exec(query, input.Srq_Id)
					if errv != nil {
						tx.Rollback()
						log.Println("AdminRepository: UpdateServiceRequestAdmin: Input:  srq_id:", input.Srq_Id, "new_status :", input.New_status, ": Error: ", errv.Error())
						return nil, errv
					}
				} else {
					log.Println("AdminRepository: UpdateServiceRequestAdmin: validatinghappycode : Srq_Id:", input.Srq_Id)
					return nil, errors.New(" Status change is not applicable. Happy code is not validated")
				}
			}

		} else if (strings.ToLower(input.Old_status) == "in-progress") || (strings.ToLower(input.Old_status) == "fixed") && (strings.ToLower(input.New_status) == "further_action") {
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_5`=1 , `Srq_Stage_5_ts`= CURRENT_TIMESTAMP() WHERE Srq_Id =?"
			_, errv := tx.Exec(query, input.Srq_Id)
			if errv != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: Input:  srq_id:", input.Srq_Id, "new_status :", input.New_status, "Error ", errv.Error())
				return nil, errv
			}

		} else if ((strings.ToLower(input.Old_status) == "fixed") || (strings.ToLower(input.Old_status) == "under_observation") || (strings.ToLower(input.Old_status) == "further_action")) && (strings.ToLower(input.New_status) == "closed") {
			//closed
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_7` = 1,`Srq_Stage_7_ts` = CURRENT_TIMESTAMP() where Srq_Id = ?"
			_, errv := tx.Exec(query, input.Srq_Id)
			if errv != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: Input: srq_id:", input.Srq_Id, "new_status :", input.New_status, ":Error :", errv.Error())
				return nil, errv
			}
		} else if strings.ToLower(input.New_status) == "cancel" {
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_8` = 1,`Srq_Stage_8_ts` = CURRENT_TIMESTAMP() where Srq_Id = ?"
			_, errv := tx.Exec(query, input.Srq_Id)
			if errv != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: Input: srq_id:", input.Srq_Id, "new_status :", input.New_status, ":Error: ", errv.Error())
				return nil, errv
			}
		} else if strings.ToLower(input.Old_status) == "fixed" && strings.ToLower(input.New_status) == "under_observation" {
			query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Stage_6` = 1,`Srq_Stage_6_ts` = CURRENT_TIMESTAMP() where Srq_Id = ?"
			_, errv := tx.Exec(query, input.Srq_Id)
			if errv != nil {
				tx.Rollback()
				log.Println("AdminRepository: UpdateServiceRequestAdmin: srq_id:", input.Srq_Id, "new_status :", input.New_status, ": Error: ", errv.Error())
				return nil, errv
			}
		}
	}
	//timeslot
	if input.Srq_Visit_Timeslot != "" {
		// query := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Visit_Timeslot` = ? where `Srq_Id` = ?"
		timeSlotQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Visit_Timeslot` = ?, Technician_Id =null where `Srq_Id` = ?"
		_, errt := tx.Exec(timeSlotQuery, input.Srq_Visit_Timeslot, input.Srq_Id)
		if errt != nil {
			tx.Rollback()
			log.Println("AdminRepository: UpdateServiceRequestAdmin: updatingtimeslot: srq_id: ", input.Srq_Id, ":Error :", errt.Error())
			return nil, errt
		}
	}
	//comment
	if input.Admin_Comments != "" {
		commentQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET `Admin_Comments` = ? where `Srq_Id` = ?"
		_, errc := tx.Exec(commentQuery, input.Admin_Comments, input.Srq_Id)
		if errc != nil {
			tx.Rollback()
			log.Println("AdminRepository: UpdateServiceRequestAdmin: updatingComment :srq_id: ", input.Srq_Id, ": Error: ", errc.Error())
			return nil, errc
		}
	}
	//payment_mode
	if input.Payment_Mode != "" {
		paymentQuery := "UPDATE `BPMS_SERVICE_REQUEST` SET `Srq_Payment_Mode` = ? where `Srq_Id` = ?"
		_, errp := tx.Exec(paymentQuery, input.Payment_Mode, input.Srq_Id)
		if errp != nil {
			tx.Rollback()
			log.Println("AdminRepository: UpdateServiceRequestAdmin:  updatePaymentMode :srq_id: ", input.Srq_Id, ": Error: ", errp.Error())
			return nil, errp
		}
	}
	srq := entity.AdminServiceRequest{}

	errs := tx.Select(&srq.ServiceList, "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=?", input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Srq_Id)
	if errs != nil {
		log.Println("AdminRepository: UpdateServiceRequestAdmin: fetchingsrqdetails :srq_id: ", input.Srq_Id, ": Error :", errs.Error())
		return nil, errs
	} else {
		log.Println("AdminRepository: UpdateServiceRequestAdmin: fetchingsrqdetails : srq_id: ", input.Srq_Id)
		return &srq, nil
	}
}

func (adminRepo *AdminRepository) AdminDashboard(input entity.AdminDashboardInput) (*entity.AdminDashboardResp, error) {
	// TechnicianAvailability := []entity.TechnicianAvailability{}
	admin_Dashboard := []entity.AdminDashboard{}
	availability := []entity.TechnicianAvailability{}

	adminDashboard := entity.AdminDashboardResp{}
	var err1, err2 error

	err1 = adminRepo.db.Select(&availability, "SELECT emp.Emp_Id,emp.Emp_Name,emp.Is_Available from `BPMS_EMPLOYEE` emp left join `BPMS_EMP_APARTMENT_MAPPING` ea on emp.Emp_Id=ea.Emp_Id where ea.Apartment_Id = ?", input.Apartment_Id)
	if err1 != nil {
		log.Println("AdminRepository: AdminDashboard: Input :", input.Apartment_Id, "Error:", err1.Error())
		return nil, err1
	} else {
		adminDashboard.Availability = availability
	}
	err2 = adminRepo.db.Select(&admin_Dashboard, "SELECT srq.Technician_Id,emp.Emp_Name, DATE_FORMAT(srq.Srq_Stage_7_ts, '%Y-%m') AS Month, COUNT(*) AS Services_Completed FROM `BPMS_SERVICE_REQUEST` srq left join `BPMS_EMPLOYEE` emp on srq.Technician_Id=emp.Emp_Id WHERE srq.Srq_Stage_7 = 1 AND MONTH(srq.Srq_Stage_7_ts) = MONTH(CURRENT_DATE()) AND YEAR(srq.Srq_Stage_7_ts) = YEAR(CURRENT_DATE()) GROUP BY srq.Technician_Id")
	if err2 != nil {
		log.Println("AdminRepository: AdminDashboard: Error:", err2.Error())
		return nil, err2
	} else {
		adminDashboard.Admin_Dashboard = admin_Dashboard
	}
	return &adminDashboard, nil
}
