import { Injectable } from "@nestjs/common"
import { CreateEmployeeDto } from "./dto/create-employee.dto"
import { UpdateEmployeeDto } from "./dto/update-employee.dto"
import { EmployeeRepository } from "./repositories/employee.repository"
import { EmployeeSalaryDetailRepository } from "./repositories/employee-salary-detail.repository"
import { EmployeeInformationRepository } from "./repositories/employee-information.repository"
import { SalarySettingRepository } from "../salary-settings/repositories/salary-setting.repository"
import { AuthRepository } from "../auth/repositories/auth.repository"
import { AuthService } from "../auth/auth.service"
import {
  cleanString,
  errorMessage,
  isEmpty,
  successMessage,
  validationMessage,
} from "../../utils/helpers"
import {
  failureResponse,
  successResponse,
} from "../../common/response/response"
import { code } from "../../common/response/response.code"
import { messageKey } from "../../constants/message-keys"
import { Employee } from "./entities/employee.entity"
import { verifyJwtToken } from "src/utils/jwt"
import { InjectRepository } from "@nestjs/typeorm"
import { LeaveType } from "../leave-types/entities/leave-type.entity"
import { Repository, DataSource } from "typeorm"
import { EmployeeLeaveBalanceRepository } from "../employee-leave-balances/repositories/employee-leave-balance.repository"
import { EmployeeLeaveBalance } from "../employee-leave-balances/entities/employee-leave-balance.entity"
import { EmployeeSalaryHistoryRepository } from "./repositories/employee-salary-history.repository"
import { EmployeeSalaryHistory } from "./entities/employee-salary-history.entity"
import { CompanySettingsRepository } from "../company/repositories/company-settings.repository"
import { UpdateSalaryHistoryDto } from "./dto/update-salary-dto"
import { TimeTrackingRepository } from "../time-tracking/repositories/time-tracking.repository"
import { RoleRepository } from "../role/repositories/role.repository"
import { DepartmentRepository } from "../departments/repositories/department.repository"
import * as XLSX from "xlsx"
import {
  calculateTdsAmount,
  convertSalaryDetailsToBreakdown,
  TaxRegime,
  TdsCalculationInput,
  SalaryBreakdown,
  SALARY_COMPONENT_CODES,
} from "../../utils/tds-calculator"
import { fileStoreLocation } from "../../common/file-upload/file-store-location"
import { ProjectEmployee } from "../projects/entities/project-employee.entity"

// Employee type enum for validation
export enum EmployeeType {
  TRAINEE = "trainee",
  EMPLOYEE = "employee",
}

// Interface for bulk upload response
interface BulkUploadError {
  row_number: number
  employee_name: string
  email: string
  error_message: string
}

interface BulkUploadResponse {
  total_records: number
  success_count: number
  failure_count: number
  errors: BulkUploadError[]
  error_file?: Buffer // Excel file with error rows (will be converted to base64 in controller)
}

@Injectable()
export class EmployeesService {
  constructor(
    private readonly employeeRepository: EmployeeRepository,
    private readonly employeeSalaryDetailRepository: EmployeeSalaryDetailRepository,
    private readonly employeeInformationRepository: EmployeeInformationRepository,
    private readonly salarySettingRepository: SalarySettingRepository,
    private readonly authRepository: AuthRepository,
    private readonly authService: AuthService,
    @InjectRepository(LeaveType)
    private readonly leaveTypeEntityRepository: Repository<LeaveType>,
    private readonly employeeLeaveBalanceRepository: EmployeeLeaveBalanceRepository,
    private readonly employeeSalaryHistoryRepository: EmployeeSalaryHistoryRepository,
    private readonly companySettingsRepository: CompanySettingsRepository,
    private readonly timeTrackingRepository: TimeTrackingRepository,
    private readonly roleRepository: RoleRepository,
    private readonly departmentRepository: DepartmentRepository,
    @InjectRepository(ProjectEmployee)
    private readonly projectEmployeeEntityRepository: Repository<ProjectEmployee>,
    private readonly dataSource: DataSource,
  ) {}

  async create(
    createEmployeeDto: CreateEmployeeDto,
    token: string,
    attachment?: any,
  ) {
    const decoded = verifyJwtToken(token)

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    // Check if employee with same email exists for the company (if email is provided)

    if (createEmployeeDto.email) {
      const existingEmployee = await this.employeeRepository.getByParams({
        where: {
          company_id: decoded.company_id,
          email: createEmployeeDto.email,
        },
        findOne: true,
      })

      if (existingEmployee) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Employee",
            ":field": "email",
          }),
        )
      }
    }

    // Create query runner for transaction
    const queryRunner = this.dataSource.createQueryRunner()
    await queryRunner.connect()
    await queryRunner.startTransaction()

    try {
      // Validate employee_type
      if (
        !Object.values(EmployeeType).includes(
          createEmployeeDto.employee_type as EmployeeType,
        )
      ) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.field_type_validation_error, {
            ":field": "employee_type",
            ":type": "trainee or employee",
          }),
        )
      }

      // Extract salary details and employee information from DTO
      const {
        salary_settings,
        gross_salary,
        employee_type,
        stipend,
        // Employee information fields
        address,
        gender,
        alternate_mobile_number,
        date_of_birth,
        marital_status,
        parents_name,
        parents_contact_number,
        blood_group,
        native_place,
        aadhar_card,
        pan_card_number,
        education_qualification,
        total_work_experience_years,
        ...employeeData
      } = createEmployeeDto

      // Set default gross_salary to 0 if not provided
      const finalGrossSalary = gross_salary || 0

      let basicSalary = 0
      let totalEarnings = 0
      let totalDeductions = 0
      let salaryDetails: any[] = []
      let netPayableSalary = 0

      // Only calculate salary components if gross_salary > 0 and salary_settings provided
      if (finalGrossSalary > 0) {
        // Calculate basic salary using company's basic salary percentage
        basicSalary = await this.calculateBasicSalary(
          finalGrossSalary,
          decoded.company_id,
        )

        // Calculate salary components if salary settings are provided
        if (salary_settings && salary_settings.length > 0) {
          const salaryCalculation = await this.calculateSalaryComponents(
            salary_settings,
            basicSalary,
          )
          totalEarnings = salaryCalculation.totalEarnings
          totalDeductions = salaryCalculation.totalDeductions
          salaryDetails = salaryCalculation.salaryDetails
        } else {
          // If no salary settings, basic salary is the earning
          totalEarnings = basicSalary
        }

        // Calculate net payable salary (will be adjusted for TDS later)
        netPayableSalary = totalEarnings - totalDeductions
      }

      // Initialize TDS amount (will be calculated after salary details are saved)
      let tdsAmount = 0

      let employee: any = new Employee()
      employee = {
        ...employeeData,
        employee_type: employee_type,
        gross_salary: finalGrossSalary,
        basic_salary: basicSalary,
        net_payable_salary: netPayableSalary, // Will be updated after TDS calculation
        stipend: stipend || null,
        regime_type: createEmployeeDto.regime_type || "new",
        tds_amount: 0, // Will be updated after TDS calculation
        status: createEmployeeDto.status || 1,
        company_id: decoded.company_id,
        created_by: decoded.user_id,
      }

      // Save employee with transaction
      const savedEmployee = await this.employeeRepository.save(
        employee,
        null,
        queryRunner.manager,
      )

      let savedUser: any = null

      // Create user entry if email is provided
      if (createEmployeeDto.email) {
        const userData = {
          first_name: createEmployeeDto.first_name,
          last_name: createEmployeeDto.last_name || "",
          email: createEmployeeDto.email,
          phone: createEmployeeDto.contact_no,
          password: null, // No password initially
          role_id: createEmployeeDto.role_id,
          company_id: decoded.company_id,
          employee_id: savedEmployee.id, // Link to employee
          status: 0, // Inactive until password is set
          created_by: decoded.user_id,
        }

        savedUser = await this.authRepository.save(
          userData,
          null,
          queryRunner.manager,
        )
      }

      let resumeUrl = null
      if (attachment) {
        resumeUrl = `${fileStoreLocation.resumes}/${cleanString(attachment.filename)}`
      }

      // Create employee information if any information fields are provided
      if (address || gender || alternate_mobile_number || resumeUrl) {
        const employeeInformationData = {
          employee_id: savedEmployee.id,
          address,
          gender,
          alternate_mobile_number,
          date_of_birth: date_of_birth ? new Date(date_of_birth) : null,
          marital_status,
          parents_name,
          parents_contact_number,
          blood_group,
          native_place,
          aadhar_card,
          pan_card_number,
          education_qualification,
          total_work_experience_years,
          created_by: decoded.user_id,
          resume_url: resumeUrl,
        }

        await this.employeeInformationRepository.save(
          employeeInformationData,
          null,
          queryRunner.manager,
        )
      }

      // Create salary detail entries only if we have salary details
      if (salaryDetails && salaryDetails.length > 0) {
        for (const salaryDetail of salaryDetails) {
          await queryRunner.manager.save("employee_salary_details", {
            employee_id: savedEmployee.id,
            salary_setting_id: salaryDetail.salary_setting_id,
            gross_salary: finalGrossSalary,
            individual_value: salaryDetail.individual_value,
            calculated_amount: salaryDetail.calculated_amount,
            created_by: decoded.user_id,
          })
        }

        // Calculate TDS after salary details are saved
        if (finalGrossSalary > 0) {
          try {
            // Create salary breakdown from salary settings with proper structure
            const salaryBreakdown = await this.createSalaryBreakdownForTds(
              salary_settings || [],
              basicSalary,
              decoded.company_id,
            )

            // Prepare TDS calculation input
            const tdsInput: TdsCalculationInput = {
              employeeId: savedEmployee.id,
              annualCTC: finalGrossSalary * 12,
              regimeType: (createEmployeeDto.regime_type || "new") as TaxRegime,
              salaryBreakdown,
              isMetroCity: false, // Default to non-metro, can be enhanced later
              ltaClaimed: false, // Default to not claimed
              rentPaid: 0, // Default to 0, can be enhanced later
            }

            // Calculate TDS
            const tdsResult = calculateTdsAmount(tdsInput)
            tdsAmount = tdsResult.annualTdsAmount

            // Deduct monthly TDS from net payable salary
            const monthlyTdsAmount = Math.round(tdsAmount / 12)
            netPayableSalary = netPayableSalary - monthlyTdsAmount

            // Update employee record with TDS amount and adjusted net payable salary
            await queryRunner.manager.update("employees", savedEmployee.id, {
              tds_amount: monthlyTdsAmount,
              net_payable_salary: netPayableSalary,
            })
          } catch (error) {
            console.error(
              "Error calculating TDS during employee creation:",
              error,
            )
            // Continue without TDS calculation if there's an error
          }
        }
      }

      // Get company settings for basic salary percentage
      const companySettings: any =
        await this.companySettingsRepository.getByParams({
          where: { company_id: decoded.company_id },
          findOne: true,
        })
      const basicSalaryPercentage =
        companySettings?.basic_salary_percentage || "45"

      // Create salary history record only if gross_salary > 0
      if (finalGrossSalary > 0) {
        await this.createSalaryHistory(
          savedEmployee.id,
          decoded.company_id,
          finalGrossSalary,
          basicSalary,
          totalEarnings,
          totalDeductions,
          netPayableSalary,
          basicSalaryPercentage,
          decoded.user_id,
          new Date(createEmployeeDto.joining_date || new Date()),
          queryRunner.manager,
          createEmployeeDto.regime_type || "new",
        )
      }

      // Create leave balances for all active leave types
      await this.createEmployeeLeaveBalancesWithTransaction(
        savedEmployee.id,
        decoded.company_id,
        decoded.user_id,
        queryRunner.manager,
      )

      // Commit transaction
      await queryRunner.commitTransaction()

      // Send password setup email after successful transaction (outside transaction)
      if (createEmployeeDto.email && savedUser) {
        const employeeName =
          `${createEmployeeDto.first_name} ${createEmployeeDto.last_name || ""}`.trim()

        try {
          await this.authService.sendPasswordSetupEmail(
            savedUser.id,
            employeeName,
            createEmployeeDto.email,
          )
        } catch (error) {
          console.error("Failed to send password setup email:", error)
          // Don't fail the entire employee creation if email fails
        }
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_add, { ":data": "Employee" }),
      )
    } catch (err) {
      // Rollback transaction on error
      await queryRunner.rollbackTransaction()
      console.error("Error creating employee:", err)

      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.exception),
      )
    } finally {
      await queryRunner.release()
    }
  }

  async findAll(query: any = {}, token: string) {
    try {
      const decoded = verifyJwtToken(token)

      if (!decoded) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.invalid_token),
        )
      }
      const {
        page = 1,
        limit = 10,
        search,
        department_id,
        role_id,
        team_lead_id,
        status,
        column_name,
        order = "DESC",
      } = query

      const skip = (page - 1) * limit
      const take = parseInt(limit)

      const orderDirection = isEmpty(order) ? "DESC" : order.toUpperCase()

      // Set up dynamic ordering
      let orderBy: any = { created_at: orderDirection }

      if (!isEmpty(column_name)) {
        switch (column_name) {
          case "name":
          case "employee_name":
            orderBy = {
              first_name: orderDirection,
              last_name: orderDirection,
            }
            break
          case "joining_date":
            orderBy = { joining_date: orderDirection }
            break
          case "created_at":
          default:
            orderBy = { created_at: orderDirection }
            break
        }
      }

      const whereConditions: any = {}
      const searchConditions: any = {}

      // Add filters
      if (decoded.company_id) {
        whereConditions.company_id = decoded.company_id
      }

      if (department_id) {
        whereConditions.department_id = department_id
      }

      if (role_id) {
        whereConditions.role_id = role_id
      }

      if (team_lead_id) {
        whereConditions.team_lead_id = team_lead_id
      }

      if (status) {
        whereConditions.status = status
      }

      // Add search functionality
      if (search) {
        searchConditions.first_name = search
        searchConditions.last_name = search
        searchConditions.email = search
      }

      const employees: any = await this.employeeRepository.getByParams({
        where: whereConditions,
        search: !isEmpty(searchConditions) ? searchConditions : undefined,
        whereNull: ["deleted_at"],
        relations: [
          "company:id,name",
          "department:id,name",
          "role:id,name",
          "teamLead:id,first_name,last_name",
          "employeeInformation",
        ],
        orderBy,
        take,
        skip,
      })

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, { ":data": "Employees" }),
        employees,
      )
    } catch (error) {
      console.log("Error finding employees:", error)
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.exception),
      )
    }
  }

  async findOne(id: number) {
    const employee: any = await this.employeeRepository.getByParams({
      where: { id },
      whereNull: ["deleted_at"],
      relations: [
        "company:id,name",
        "department:id,name",
        "role:id,name",
        "employeeInformation",
      ],
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    // Get salary details
    const salaryDetails = await this.findSalaryDetailsByEmployeeId(id)
    employee.salaryDetails = salaryDetails

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Employee" }),
      employee,
    )
  }

  async update(
    id: number,
    updateEmployeeDto: UpdateEmployeeDto,
    token: string,
    attachment?: any,
  ) {
    const decoded = verifyJwtToken(token)

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    const employee: any = await this.employeeRepository.getByParams({
      where: { id },
      whereNull: ["deleted_at"],
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    // Check duplicate email
    if (updateEmployeeDto.email) {
      const existingEmployee = await this.employeeRepository.getByParams({
        where: {
          company_id: decoded.company_id,
          email: updateEmployeeDto.email,
        },
        whereNull: ["deleted_at"],
        whereNotIn: { id: [id] },
        findOne: true,
      })

      if (existingEmployee) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Employee",
            ":field": "email",
          }),
        )
      }
    }

    // Validate employee_type
    if (
      updateEmployeeDto.employee_type &&
      !Object.values(EmployeeType).includes(
        updateEmployeeDto.employee_type as EmployeeType,
      )
    ) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.field_type_validation_error, {
          ":field": "employee_type",
          ":type": "trainee or employee",
        }),
      )
    }

    // Extract fields
    const {
      salary_settings,
      gross_salary,
      employee_type,
      stipend,
      // Employee information fields
      address,
      gender,
      alternate_mobile_number,
      date_of_birth,
      marital_status,
      parents_name,
      parents_contact_number,
      blood_group,
      native_place,
      aadhar_card,
      pan_card_number,
      education_qualification,
      total_work_experience_years,
      ...employeeData
    } = updateEmployeeDto

    // ✅ Detect REAL salary change
    const previousGrossSalary = employee.gross_salary
    console.log(previousGrossSalary, "previousGrossSalary")
    const isGrossSalaryChanged =
      gross_salary !== undefined &&
      Number(gross_salary) !== Number(previousGrossSalary)

    console.log(isGrossSalaryChanged, "isGrossSalaryChanged")

    let basicSalary = employee.basic_salary
    let netPayableSalary = employee.net_payable_salary
    let totalEarnings = 0
    let totalDeductions = 0

    // Recalculate salary ONLY if needed
    if (
      isGrossSalaryChanged ||
      (salary_settings && salary_settings.length > 0)
    ) {
      const finalGrossSalary =
        gross_salary !== undefined ? gross_salary : employee.gross_salary

      if (finalGrossSalary > 0) {
        basicSalary = await this.calculateBasicSalary(
          finalGrossSalary,
          decoded.company_id,
        )

        if (salary_settings && salary_settings.length > 0) {
          const {
            totalEarnings: newTotalEarnings,
            totalDeductions: newTotalDeductions,
          } = await this.calculateSalaryComponents(salary_settings, basicSalary)

          totalEarnings = newTotalEarnings
          totalDeductions = newTotalDeductions
          netPayableSalary = totalEarnings - totalDeductions
        } else {
          totalEarnings = basicSalary
          netPayableSalary = basicSalary
        }
      } else {
        basicSalary = 0
        totalEarnings = 0
        totalDeductions = 0
        netPayableSalary = 0
      }

      console.log(isGrossSalaryChanged, "isGrossSalaryChanged")

      // ✅ Close old salary history ONLY if salary changed
      if (isGrossSalaryChanged) {
        await this.closeCurrentSalaryRecord(id, new Date(), decoded.user_id)
      }
    }

    // Calculate TDS and update employee record
    let tdsAmount = 0
    let shouldCreateSalaryHistory = false

    if (
      isGrossSalaryChanged ||
      (salary_settings && salary_settings.length > 0) ||
      (updateEmployeeDto.regime_type &&
        updateEmployeeDto.regime_type !== employee.regime_type)
    ) {
      try {
        // Get updated salary details for TDS calculation
        const updatedSalaryDetails =
          await this.findSalaryDetailsByEmployeeId(id)
        const salaryDetailsArray = Array.isArray(updatedSalaryDetails)
          ? updatedSalaryDetails
          : []

        console.log(salaryDetailsArray, "salaryDetailsArray")

        if (salaryDetailsArray && salaryDetailsArray.length > 0) {
          // Convert salary details to breakdown format
          const salaryBreakdown = convertSalaryDetailsToBreakdown(
            salaryDetailsArray,
            basicSalary,
          )

          // Prepare TDS calculation input
          const tdsInput: TdsCalculationInput = {
            employeeId: id,
            annualCTC:
              (gross_salary !== undefined
                ? gross_salary
                : employee.gross_salary) * 12,
            regimeType: (updateEmployeeDto.regime_type ||
              employee.regime_type ||
              "new") as TaxRegime,
            salaryBreakdown,
            isMetroCity: false, // Default to non-metro, can be enhanced later
            ltaClaimed: false, // Default to not claimed
            rentPaid: 0, // Default to 0, can be enhanced later
          }

          // Calculate TDS
          const tdsResult = calculateTdsAmount(tdsInput)
          tdsAmount = tdsResult.annualTdsAmount

          // Update net payable salary by deducting monthly TDS
          const monthlyTdsAmount = Math.round(tdsAmount / 12)
          netPayableSalary = netPayableSalary - monthlyTdsAmount

          shouldCreateSalaryHistory = true
        } else if (isGrossSalaryChanged) {
          // If gross salary changed but no salary details found, still create salary history
          console.log("here inside")
          shouldCreateSalaryHistory = true
          // Keep existing TDS amount or set to 0 if no previous amount
          tdsAmount = employee.tds_amount || 0
        }
      } catch (error) {
        console.error("Error calculating TDS during employee update:", error)
        // Continue without TDS calculation if there's an error
        // Still create salary history if gross salary changed
        if (isGrossSalaryChanged) {
          shouldCreateSalaryHistory = true
          tdsAmount = employee.tds_amount || 0
        }
      }
    } else {
      // Keep existing TDS amount if no salary-related changes
      tdsAmount = employee.tds_amount || 0
    }

    const monthlyTdsAmount = Math.round(tdsAmount / 12)

    // Handle resume upload
    let resumeUrl = null
    if (attachment) {
      resumeUrl = `${fileStoreLocation.resumes}/${cleanString(attachment.filename)}`
    }

    // Update employee record
    Object.assign(employee, {
      ...employeeData,
      ...(employee_type && { employee_type }),
      ...(gross_salary !== undefined && { gross_salary }),
      ...(stipend !== undefined && { stipend }),
      ...(updateEmployeeDto.regime_type && {
        regime_type: updateEmployeeDto.regime_type,
      }),
      basic_salary: basicSalary,
      net_payable_salary: netPayableSalary,
      tds_amount: monthlyTdsAmount,
    })

    await this.employeeRepository.save(employee)

    // Update or create employee information
    const hasEmployeeInformationData =
      address ||
      gender ||
      alternate_mobile_number ||
      date_of_birth ||
      marital_status ||
      parents_name ||
      parents_contact_number ||
      blood_group ||
      native_place ||
      aadhar_card ||
      pan_card_number ||
      education_qualification ||
      total_work_experience_years !== undefined ||
      resumeUrl

    if (hasEmployeeInformationData) {
      // Check if employee information already exists
      const existingEmployeeInfo: any =
        await this.employeeInformationRepository.getByParams({
          where: { employee_id: id },
          whereNull: ["deleted_at"],
          findOne: true,
        })

      if (existingEmployeeInfo) {
        // Update existing employee information
        const updateData: any = {
          id: existingEmployeeInfo.id,
          updated_by: decoded.user_id,
        }

        // Only update fields that are provided
        if (address !== undefined) updateData.address = address
        if (gender !== undefined) updateData.gender = gender
        if (alternate_mobile_number !== undefined)
          updateData.alternate_mobile_number = alternate_mobile_number
        if (date_of_birth !== undefined)
          updateData.date_of_birth = date_of_birth
            ? new Date(date_of_birth)
            : null
        if (marital_status !== undefined)
          updateData.marital_status = marital_status
        if (parents_name !== undefined) updateData.parents_name = parents_name
        if (parents_contact_number !== undefined)
          updateData.parents_contact_number = parents_contact_number
        if (blood_group !== undefined) updateData.blood_group = blood_group
        if (native_place !== undefined) updateData.native_place = native_place
        if (aadhar_card !== undefined) updateData.aadhar_card = aadhar_card
        if (pan_card_number !== undefined)
          updateData.pan_card_number = pan_card_number
        if (education_qualification !== undefined)
          updateData.education_qualification = education_qualification
        if (total_work_experience_years !== undefined)
          updateData.total_work_experience_years = total_work_experience_years
        if (resumeUrl !== null) updateData.resume_url = resumeUrl

        await this.employeeInformationRepository.save(updateData)
      } else {
        // Create new employee information if it doesn't exist
        // Validate mandatory fields for new employee information (or if only resume is provided)
        if ((address && gender && alternate_mobile_number) || resumeUrl) {
          const employeeInformationData = {
            employee_id: id,
            address,
            gender,
            alternate_mobile_number,
            date_of_birth: date_of_birth ? new Date(date_of_birth) : null,
            marital_status,
            parents_name,
            parents_contact_number,
            blood_group,
            native_place,
            aadhar_card,
            pan_card_number,
            education_qualification,
            total_work_experience_years,
            resume_url: resumeUrl,
            created_by: decoded.user_id,
          }

          await this.employeeInformationRepository.save(employeeInformationData)
        }
      }
    }

    // Update salary details (no history impact)
    if (salary_settings && salary_settings.length > 0) {
      const existingSalaryDetails: any =
        await this.findSalaryDetailsByEmployeeId(id)

      for (const detail of existingSalaryDetails || []) {
        await this.employeeSalaryDetailRepository.save({
          id: detail.id,
          deleted_by: decoded.user_id,
          deleted_at: new Date(),
        })
      }

      const { salaryDetails } = await this.calculateSalaryComponents(
        salary_settings,
        basicSalary,
      )

      for (const salaryDetail of salaryDetails) {
        await this.employeeSalaryDetailRepository.save({
          employee_id: id,
          salary_setting_id: salaryDetail.salary_setting_id,
          gross_salary:
            gross_salary !== undefined ? gross_salary : employee.gross_salary,
          individual_value: salaryDetail.individual_value,
          calculated_amount: salaryDetail.calculated_amount,
          created_by: decoded.user_id,
        })
      }
    }

    // ✅ Create NEW salary history if gross salary changed or salary details were processed
    if (
      shouldCreateSalaryHistory &&
      (gross_salary !== undefined ? gross_salary : employee.gross_salary) > 0
    ) {
      const companySettings: any =
        await this.companySettingsRepository.getByParams({
          where: { company_id: decoded.company_id },
          findOne: true,
        })

      const basicSalaryPercentage =
        companySettings?.basic_salary_percentage || "45"

      console.log("Creating salary history with data:", {
        employeeId: id,
        companyId: decoded.company_id,
        grossSalary:
          gross_salary !== undefined ? gross_salary : employee.gross_salary,
        basicSalary,
        totalEarnings: totalEarnings || basicSalary,
        totalDeductions,
        netPayableSalary,
        basicSalaryPercentage,
        regimeType:
          updateEmployeeDto.regime_type || employee.regime_type || "new",
      })

      await this.createSalaryHistory(
        id,
        decoded.company_id,
        gross_salary !== undefined ? gross_salary : employee.gross_salary,
        basicSalary,
        totalEarnings || basicSalary,
        totalDeductions,
        netPayableSalary,
        basicSalaryPercentage,
        decoded.user_id,
        new Date(),
        null,
        updateEmployeeDto.regime_type || employee.regime_type || "new",
      )
    }

    // Email notifications
    if (employee.email) {
      const user: any = await this.authRepository.getByParams({
        where: {
          employee_id: id,
          company_id: decoded.company_id,
        },
        findOne: true,
      })

      if (user) {
        const employeeName = `${employee.first_name} ${
          employee.last_name || ""
        }`.trim()

        try {
          if (!user.password) {
            await this.authService.sendPasswordSetupEmail(
              user.id,
              employeeName,
              employee.email,
            )
          } else if (
            updateEmployeeDto.email &&
            updateEmployeeDto.email !== user.email
          ) {
            // Update email in auth table immediately
            await this.authRepository.save({
              id: user.id,
              email: updateEmployeeDto.email.toLowerCase(),
              updated_by: decoded.user_id,
            })

            await this.authService.sendEmailChangeRequestEmail(
              user.id,
              employeeName,
              user.email,
              updateEmployeeDto.email,
            )
          }
        } catch (error) {
          console.error("Email notification failed:", error)
        }
      }
    }

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_update, { ":data": "Employee" }),
    )
  }

  async remove(id: number, token: string) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    const employee: any = await this.employeeRepository.getByParams({
      where: { id },
      whereNull: ["deleted_at"],
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    // Soft delete by setting deleted_at timestamp and deleted_by
    await this.employeeRepository.save({
      id: employee.id,
      deleted_by: decoded.user_id,
      deleted_at: new Date(),
    })

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_removed, { ":data": "Employee" }),
    )
  }

  async checkEmployeeExist(email: string, companyId: number) {
    const employee = await this.employeeRepository.getByParams({
      where: {
        email: email,
        company_id: companyId,
      },
      findOne: true,
    })

    return !isEmpty(employee)
  }

  private calculateAmount(
    individualValue: string,
    basicSalary: number,
    typeValue: string,
  ): number {
    const value = parseFloat(individualValue)

    if (typeValue === "percentage") {
      // Calculate percentage of basic salary
      return (basicSalary * value) / 100
    } else if (typeValue === "fixed") {
      // Return fixed amount
      return value
    }

    return 0
  }

  /**
   * Calculate basic salary from gross salary using company's basic salary percentage
   */
  async calculateBasicSalary(
    grossSalary: number,
    companyId: number,
  ): Promise<number> {
    const companySettings: any =
      await this.companySettingsRepository.getByParams({
        where: { company_id: companyId },
        findOne: true,
      })

    const basicSalaryPercentage =
      companySettings?.basic_salary_percentage || "45"
    const percentage = parseFloat(basicSalaryPercentage)

    return (grossSalary * percentage) / 100
  }

  /**
   * Calculate salary components (earnings and deductions)
   */
  private async calculateSalaryComponents(
    salarySettings: any[],
    basicSalary: number,
  ): Promise<{
    totalEarnings: number
    totalDeductions: number
    salaryDetails: any[]
  }> {
    let totalEarnings = basicSalary // Start with basic salary as earning
    let totalDeductions = 0
    const salaryDetails = []

    for (const salarySetting of salarySettings) {
      const salarySettingDetails: any =
        await this.salarySettingRepository.getByParams({
          where: { id: salarySetting.salary_setting_id },
          findOne: true,
        })

      if (salarySettingDetails) {
        const calculatedAmount = this.calculateAmount(
          salarySetting.individual_value,
          basicSalary,
          salarySettingDetails.type_value,
        )

        salaryDetails.push({
          salary_setting_id: salarySetting.salary_setting_id,
          individual_value: salarySetting.individual_value,
          calculated_amount: calculatedAmount,
          setting_details: salarySettingDetails,
        })

        // Determine if it's earning or deduction based on salary setting type
        if (salarySettingDetails.type === "earning") {
          totalEarnings += calculatedAmount
        } else if (salarySettingDetails.type === "deduction") {
          totalDeductions += calculatedAmount
        }
      }
    }

    return { totalEarnings, totalDeductions, salaryDetails }
  }

  /**
   * Create salary history record with TDS calculation
   */
  private async createSalaryHistory(
    employeeId: number,
    companyId: number,
    grossSalary: number,
    basicSalary: number,
    totalEarnings: number,
    totalDeductions: number,
    netPayableSalary: number,
    basicSalaryPercentage: string,
    createdBy: number,
    fromDate: Date,
    manager?: any,
    regimeType: string = "new",
  ): Promise<void> {
    // Calculate TDS amount
    let tdsAmount = 0

    try {
      // Get employee salary details for TDS calculation
      const salaryDetails = await this.findSalaryDetailsByEmployeeId(employeeId)
      const salaryDetailsArray = Array.isArray(salaryDetails)
        ? salaryDetails
        : []

      if (salaryDetailsArray && salaryDetailsArray.length > 0) {
        // Convert salary details to breakdown format
        const salaryBreakdown = convertSalaryDetailsToBreakdown(
          salaryDetailsArray,
          basicSalary,
        )

        // Prepare TDS calculation input
        const tdsInput: TdsCalculationInput = {
          employeeId,
          annualCTC: grossSalary * 12, // Convert monthly to annual
          regimeType: regimeType as TaxRegime,
          salaryBreakdown,
          isMetroCity: false, // Default to non-metro, can be enhanced later
          ltaClaimed: false, // Default to not claimed
          rentPaid: 0, // Default to 0, can be enhanced later
        }

        // Calculate TDS
        const tdsResult = calculateTdsAmount(tdsInput)
        tdsAmount = tdsResult.annualTdsAmount

        // Update net payable salary by deducting annual TDS (converted to monthly)
        const monthlyTdsAmount = Math.round(tdsAmount / 12)
        netPayableSalary = netPayableSalary - monthlyTdsAmount
      }
    } catch (error) {
      console.error("Error calculating TDS:", error)
      // Continue without TDS calculation if there's an error
    }

    const monthlyTdsAmount = Math.round(tdsAmount / 12)
    const salaryHistoryData = {
      employee_id: employeeId,
      company_id: companyId,
      from_date: fromDate,
      to_date: null,
      gross_salary: grossSalary,
      basic_salary: basicSalary,
      total_earnings: totalEarnings,
      total_deductions: totalDeductions,
      net_payable_salary: netPayableSalary,
      basic_salary_percentage: basicSalaryPercentage,
      tds_amount: monthlyTdsAmount,
      regime_type: regimeType,
      created_by: createdBy,
    }

    if (manager) {
      await manager.save("employee_salary_history", salaryHistoryData)
    } else {
      await this.employeeSalaryHistoryRepository.save(salaryHistoryData)
    }
  }

  async getMyLeaveBalances(token: string, query: any = {}) {
    const decoded = verifyJwtToken(token)
    const { employee_id } = query

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    const employeeId = employee_id ?? decoded.employee_id

    if (!employeeId) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.user_not_found, { ":data": "Employee" }),
      )
    }

    const currentYear = new Date().getFullYear()

    const leaveTypes = await this.leaveTypeEntityRepository
      .createQueryBuilder("lt")
      .leftJoinAndSelect(
        "employee_leave_balances",
        "elb",
        "elb.employee_id = :empId AND elb.leave_type_id = lt.id AND elb.year = :year AND elb.deleted_at IS NULL",
        { empId: employeeId, year: currentYear },
      )
      .where("lt.company_id = :companyId", { companyId: decoded.company_id })
      .andWhere("lt.status = :status", { status: 1 })
      .andWhere("lt.deleted_at IS NULL")
      .orderBy("lt.name", "ASC")
      .select([
        "lt.id AS id",
        "lt.name AS name",
        "lt.code AS code",
        "lt.annual_allowed_days AS annual_allowed_days",
        "lt.is_paid AS is_paid",
        "lt.carry_forward AS carry_forward",

        // Dynamic fallback values when employee balance does not exist
        "COALESCE(elb.total_allocated, lt.annual_allowed_days) AS total_allocated",
        "COALESCE(elb.used_days, 0) AS used_days",
        "COALESCE(elb.carried_forward_days, 0) AS carried_forward_days",
        "COALESCE(elb.available_days, lt.annual_allowed_days) AS available_days",
        "COALESCE(elb.year, :year) AS year",
      ])
      .setParameter("year", currentYear)
      .getRawMany()

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Leave Balances" }),
      leaveTypes as any,
    )
  }

  /**
   * Create leave balances for an employee for all active leave types
   */
  private async createEmployeeLeaveBalances(
    employeeId: number,
    companyId: number,
    createdBy: number,
  ) {
    const currentYear = new Date().getFullYear()

    // Get all active leave types for the company
    const leaveTypes = await this.leaveTypeEntityRepository.find({
      where: {
        company_id: companyId,
        status: 1,
        deleted_at: null,
      },
    })

    // Create leave balance entry for each leave type
    for (const leaveType of leaveTypes) {
      const existingBalance =
        await this.employeeLeaveBalanceRepository.getByParams({
          where: {
            employee_id: employeeId,
            leave_type_id: leaveType.id,
            year: currentYear,
            company_id: companyId,
          },
          findOne: true,
        })

      // Only create if doesn't exist
      if (isEmpty(existingBalance)) {
        // Calculate carry forward days from previous year
        const carryForwardDays = await this.calculateCarryForwardDays(
          employeeId,
          leaveType.id,
          companyId,
          currentYear,
        )

        const leaveBalance = new EmployeeLeaveBalance()
        leaveBalance.employee_id = employeeId
        leaveBalance.leave_type_id = leaveType.id
        leaveBalance.company_id = companyId
        leaveBalance.year = currentYear
        leaveBalance.total_allocated = leaveType.annual_allowed_days
        leaveBalance.carried_forward_days = carryForwardDays
        leaveBalance.used_days = 0
        leaveBalance.available_days =
          leaveType.annual_allowed_days + carryForwardDays
        leaveBalance.created_by = createdBy

        await this.employeeLeaveBalanceRepository.save(leaveBalance)
      }
    }
  }

  /**
   * Create leave balances for an employee for all active leave types with transaction
   */
  private async createEmployeeLeaveBalancesWithTransaction(
    employeeId: number,
    companyId: number,
    createdBy: number,
    manager: any,
  ) {
    const currentYear = new Date().getFullYear()

    // Get all active leave types for the company
    const leaveTypes = await this.leaveTypeEntityRepository.find({
      where: {
        company_id: companyId,
        status: 1,
        deleted_at: null,
      },
    })

    // Create leave balance entry for each leave type
    for (const leaveType of leaveTypes) {
      const existingBalance = await manager.findOne("employee_leave_balances", {
        where: {
          employee_id: employeeId,
          leave_type_id: leaveType.id,
          year: currentYear,
          company_id: companyId,
          deleted_at: null,
        },
      })

      // Only create if doesn't exist
      if (!existingBalance) {
        // Calculate carry forward days from previous year
        const carryForwardDays = await this.calculateCarryForwardDays(
          employeeId,
          leaveType.id,
          companyId,
          currentYear,
        )

        await manager.save("employee_leave_balances", {
          employee_id: employeeId,
          leave_type_id: leaveType.id,
          company_id: companyId,
          year: currentYear,
          total_allocated: leaveType.annual_allowed_days,
          carried_forward_days: carryForwardDays,
          used_days: 0,
          available_days: leaveType.annual_allowed_days + carryForwardDays,
          created_by: createdBy,
        })
      }
    }
  }

  /**
   * Public method to create leave balances for an existing employee
   */
  async createLeaveBalancesForEmployee(employeeId: number, token: string) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    // Check if employee exists
    const employee = await this.employeeRepository.getByParams({
      where: { id: employeeId, company_id: decoded.company_id },
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    try {
      await this.createEmployeeLeaveBalances(
        employeeId,
        decoded.company_id,
        decoded.user_id,
      )

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_add, { ":data": "Leave Balances" }),
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Find salary details by employee ID
   */
  async findSalaryDetailsByEmployeeId(employeeId: number) {
    return await this.employeeSalaryDetailRepository.getByParams({
      where: {
        employee_id: employeeId,
      },
      whereNull: ["deleted_at"],
      relations: ["salarySetting"],
    })
  }

  /**
   * Find project details by employee ID
   */
  async findProjectDetailsByEmployeeId(employeeId: number) {
    const projectEmployees = await this.projectEmployeeEntityRepository
      .createQueryBuilder("pe")
      .leftJoinAndSelect("pe.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .leftJoinAndSelect("project.project_manager", "project_manager")
      .where("pe.employee_id = :employeeId", { employeeId: employeeId })
      .andWhere("pe.deleted_at IS NULL")
      .andWhere("project.deleted_at IS NULL")
      .getMany()

    return projectEmployees
  }

  /**
   * Get employee projects - API endpoint method
   */
  async getEmployeeProjects(employeeId: number) {
    if (!employeeId || isNaN(employeeId)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.field_required, { ":field": "employee_id" }),
      )
    }

    // Check if employee exists
    const employee = await this.employeeRepository.getByParams({
      where: { id: employeeId },
      whereNull: ["deleted_at"],
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    // Get project details
    const projectDetails = await this.findProjectDetailsByEmployeeId(employeeId)

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, {
        ":data": "Employee Projects",
      }),
      projectDetails as any,
    )
  }

  /**
   * Save salary detail
   */
  async saveSalaryDetail(data: any) {
    return await this.employeeSalaryDetailRepository.save(data)
  }

  /**
   * Remove salary detail
   */
  async removeSalaryDetail(data: any) {
    return await this.employeeSalaryDetailRepository.remove(data)
  }

  /**
   * Update time tracking costs for entries after salary change date
   */
  private async updateTimeTrackingCostsAfterSalaryChange(
    employeeId: number,
    fromDate: Date,
    companyId: number,
  ): Promise<void> {
    try {
      // Get all time tracking entries for the employee from the effective date onwards
      const timeTrackingEntries: any =
        await this.timeTrackingRepository.getByParams({
          where: {
            employee_id: employeeId,
            company_id: companyId,
            start_time: {
              gte: fromDate,
            },
          },
          whereNull: ["deleted_at"],
        })

      const entriesArray = Array.isArray(timeTrackingEntries)
        ? timeTrackingEntries
        : []

      // Update each entry's cost based on the new salary history
      for (const entry of entriesArray) {
        const updatedCost = await this.calculateUpdatedCost(
          entry.start_time,
          entry.end_time,
          entry.total_minutes,
          employeeId,
        )

        // Update the time tracking entry with new cost
        await this.timeTrackingRepository.save({
          id: entry.id,
          cost: updatedCost,
        })
      }
    } catch (error) {
      console.error("Error updating time tracking costs:", error)
      // Don't throw error to prevent salary update from failing
    }
  }

  /**
   * Calculate updated cost for a time tracking entry using salary history
   */
  private async calculateUpdatedCost(
    startTime: Date,
    endTime: Date,
    totalMinutes: number,
    employeeId: number,
  ): Promise<number> {
    try {
      // Get applicable salary for the entry date
      const { gross_salary } = await this.getApplicableSalaryForTimeEntry(
        employeeId,
        startTime,
      )

      if (
        !gross_salary ||
        gross_salary <= 0 ||
        !totalMinutes ||
        totalMinutes <= 0
      ) {
        return 0
      }

      // Get company settings for working hours calculation
      const employee: any = await this.employeeRepository.getByParams({
        where: { id: employeeId },
        whereNull: ["deleted_at"],
        findOne: true,
      })

      if (!employee) {
        return 0
      }

      const companySettings: any =
        await this.companySettingsRepository.getByParams({
          where: { company_id: employee.company_id },
          whereNull: ["deleted_at"],
          findOne: true,
        })

      // Default fallback values
      const workingHoursPerDay = companySettings?.working_hours_per_day || 8
      const workingDaysPerMonth = companySettings?.working_days_per_month || 22

      // Total working hours & minutes per month
      const totalWorkingHoursPerMonth = workingHoursPerDay * workingDaysPerMonth
      const totalWorkingMinutesPerMonth = totalWorkingHoursPerMonth * 60

      // Per-minute rate
      const perMinuteRate = gross_salary / totalWorkingMinutesPerMonth

      // Calculate cost based on actual minutes worked
      const calculatedCost = perMinuteRate * totalMinutes
      return Math.round(calculatedCost * 100) / 100 // round to 2 decimals
    } catch (error) {
      console.error("Error calculating updated cost:", error)
      return 0
    }
  }

  /**
   * Get applicable salary for time entry (similar to time-tracking service method)
   */
  private async getApplicableSalaryForTimeEntry(
    employeeId: number,
    entryDate: Date,
  ): Promise<{ gross_salary: number; basic_salary: number }> {
    // Get all salary history for the employee ordered by from_date DESC
    const salaryHistory: any =
      await this.employeeSalaryHistoryRepository.getByParams({
        where: {
          employee_id: employeeId,
        },
        whereNull: ["deleted_at"],
        orderBy: { from_date: "DESC" },
      })

    const salaryHistoryArray = Array.isArray(salaryHistory) ? salaryHistory : []

    // Find the most recent salary record where from_date <= entryDate
    const applicableSalaryRecord = salaryHistoryArray.find((record) => {
      const fromDate = new Date(record.from_date)
      return fromDate <= entryDate
    })

    if (applicableSalaryRecord) {
      return {
        gross_salary: parseFloat(
          applicableSalaryRecord.gross_salary.toString(),
        ),
        basic_salary: parseFloat(
          applicableSalaryRecord.basic_salary.toString(),
        ),
      }
    }

    // Fallback to employee's current gross salary if no history exists before that date
    const employee: any = await this.employeeRepository.getByParams({
      where: { id: employeeId },
      whereNull: ["deleted_at"],
      findOne: true,
    })

    if (!employee || !employee.gross_salary) {
      return { gross_salary: 0, basic_salary: 0 }
    }

    return {
      gross_salary: parseFloat(employee.gross_salary.toString()),
      basic_salary: parseFloat(employee.basic_salary?.toString() || "0"),
    }
  }

  /**
   * Get current active salary record for an employee
   */
  private async getCurrentSalaryRecord(
    employeeId: number,
  ): Promise<EmployeeSalaryHistory | null> {
    const salaryRecord: any =
      await this.employeeSalaryHistoryRepository.getByParams({
        where: {
          employee_id: employeeId,
          to_date: null,
        },
        whereNull: ["deleted_at"],
        orderBy: { from_date: "DESC" },
        findOne: true,
      })

    return salaryRecord
  }

  /**
   * Close current salary record by setting to_date
   */
  private async closeCurrentSalaryRecord(
    employeeId: number,
    toDate: Date,
    updatedBy: number,
  ): Promise<void> {
    const currentRecord = await this.getCurrentSalaryRecord(employeeId)

    if (currentRecord) {
      await this.employeeSalaryHistoryRepository.save({
        id: currentRecord.id,
        to_date: toDate,
        updated_by: updatedBy,
        updated_at: new Date(),
      })
    }
  }

  /**
   * Get salary history for an employee
   */
  private async getSalaryHistoryForEmployee(
    employeeId: number,
  ): Promise<EmployeeSalaryHistory[]> {
    const salaryHistory: any =
      await this.employeeSalaryHistoryRepository.getByParams({
        where: {
          employee_id: employeeId,
        },
        whereNull: ["deleted_at"],
        orderBy: { from_date: "DESC" },
        relations: ["user:id,first_name,last_name"],
      })

    return Array.isArray(salaryHistory) ? salaryHistory : []
  }

  /**
   * Get salary history for an employee
   */
  async getSalaryHistory(employeeId: number, token: string) {
    const decoded = verifyJwtToken(token)

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    // Check if employee exists and belongs to the company
    const employee = await this.employeeRepository.getByParams({
      where: { id: employeeId, company_id: decoded.company_id },
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    const salaryHistory = await this.getSalaryHistoryForEmployee(employeeId)

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Salary History" }),
      salaryHistory as any,
    )
  }

  async updateSalaryHistory(
    id: number,
    updateSalaryHistoryDto: UpdateSalaryHistoryDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    // Check if employee exists and belongs to the company
    const employee: any = await this.employeeRepository.getByParams({
      where: { id: id, company_id: decoded.company_id },
      findOne: true,
    })

    if (isEmpty(employee)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    try {
      // Get employee salary details for recalculation
      const employeeSalaryDetails: any =
        await this.findSalaryDetailsByEmployeeId(id)

      // Calculate basic salary from new gross salary using company's basic salary percentage
      const basicSalary = await this.calculateBasicSalary(
        updateSalaryHistoryDto.gross_salary,
        decoded.company_id,
      )

      // Get company settings to get basic salary percentage
      const companySettings: any =
        await this.companySettingsRepository.getByParams({
          where: { company_id: decoded.company_id },
          findOne: true,
        })
      const basicSalaryPercentage =
        companySettings?.basic_salary_percentage || "45"

      // Calculate salary components (earnings and deductions) based on employee's salary settings
      const salarySettingsArray = Array.isArray(employeeSalaryDetails)
        ? employeeSalaryDetails
        : []
      const { totalEarnings, totalDeductions } =
        await this.calculateSalaryComponents(salarySettingsArray, basicSalary)

      // Calculate TDS amount
      let tdsAmount = 0
      try {
        // Convert salary details to breakdown format
        const salaryBreakdown = convertSalaryDetailsToBreakdown(
          salarySettingsArray,
          basicSalary,
        )

        // Prepare TDS calculation input
        const tdsInput: TdsCalculationInput = {
          employeeId: id,
          annualCTC: updateSalaryHistoryDto.gross_salary * 12,
          regimeType: (employee.regime_type || "new") as TaxRegime,
          salaryBreakdown,
          isMetroCity: false, // Default to non-metro, can be enhanced later
          ltaClaimed: false, // Default to not claimed
          rentPaid: 0, // Default to 0, can be enhanced later
        }

        // Calculate TDS
        const tdsResult = calculateTdsAmount(tdsInput)
        tdsAmount = tdsResult.annualTdsAmount
      } catch (error) {
        console.error("Error calculating TDS in salary history update:", error)
        // Continue without TDS calculation if there's an error
      }

      // Calculate net payable salary (deduct monthly TDS)
      const monthlyTdsAmount = Math.round(tdsAmount / 12)
      const netPayableSalary =
        totalEarnings - totalDeductions - monthlyTdsAmount

      // Create new salary history entry with calculated values
      await this.employeeSalaryHistoryRepository.save({
        employee_id: id,
        company_id: decoded.company_id,
        basic_salary: basicSalary,
        total_earnings: totalEarnings,
        total_deductions: totalDeductions,
        net_payable_salary: netPayableSalary,
        basic_salary_percentage: basicSalaryPercentage,
        tds_amount: monthlyTdsAmount,
        regime_type: employee.regime_type || "new",
        gross_salary: updateSalaryHistoryDto.gross_salary,
        notes: updateSalaryHistoryDto.notes,
        from_date: updateSalaryHistoryDto.from_date,
        updated_by: decoded.user_id,
        updated_at: new Date(),
      })

      // Update employee record with new calculated salary values
      await this.employeeRepository.save({
        id: id,
        gross_salary: updateSalaryHistoryDto.gross_salary,
        basic_salary: basicSalary,
        net_payable_salary: netPayableSalary,
        tds_amount: monthlyTdsAmount,
        updated_by: decoded.user_id,
        updated_at: new Date(),
      })

      // Update time tracking costs for entries on or after the salary change date
      await this.updateTimeTrackingCostsAfterSalaryChange(
        id,
        updateSalaryHistoryDto.from_date,
        decoded.company_id,
      )

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_update, { ":data": "Salary History" }),
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  async activeInactiveEmployee(id: number, token: string) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    const employee: any = await this.employeeRepository.getByParams({
      where: { id, company_id: decoded.company_id },
      findOne: true,
    })

    if (!employee) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Employee" }),
      )
    }

    employee.status = employee.status === 1 ? 0 : 1
    employee.updated_by = decoded.user_id

    await this.employeeRepository.save(employee)

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_update, { ":data": "Employee" }),
    )
  }

  /**
   * Parse Excel file and extract employee data
   *
   * EXCEL TEMPLATE FORMAT:
   * =====================
   * The Excel file should have the following structure:
  
   * SAMPLE EXCEL STRUCTURE:
   * | NOTE:- role_name and department_name must EXACTLY match names already created in the system.- employee_type allowed values: trainee, employee- joining_date format: YYYY-MM-DD |
   * | first_name | last_name | email            | contact_no  | employee_type | gross_salary/CTC | stipend | joining_date | role_name | status | department_name |
   * | John       | Doe       | john@example.com | +1234567890 | employee      | 50000            |         | 2024-01-15   | Manager   | 1      | IT              |
   * | Jane       | Smith     | jane@example.com | +0987654321 | trainee       | 0                | 5000    | 2024-01-20   | Intern    | 1      | HR              |
   *
   * VALIDATION RULES:
   * - Email must be unique within the company
   * - Employee type must be exactly 'trainee' or 'employee'
   * - Role name must exist in the roles table for the company
   * - Department name must exist in the departments table for the company (if provided)
   * - Dates should be in YYYY-MM-DD format or Excel date format
   * - Salary and stipend should be numeric values
   *
   * COLUMN NAME FLEXIBILITY:
   * - CTC column will be mapped to gross_salary in the database
   * - Supported salary column names: gross_salary, salary, ctc
   */
  private parseExcelFile(buffer: Buffer): any[] {
    try {
      const workbook = XLSX.read(buffer, { type: "buffer" })
      const sheetName = workbook.SheetNames[0]
      const worksheet = workbook.Sheets[sheetName]

      // Convert to JSON with header row as keys
      const jsonData = XLSX.utils.sheet_to_json(worksheet, {
        header: 1,
        defval: null,
      })

      if (jsonData.length < 3) {
        throw new Error(
          "Excel file must contain at least a note row, header row and one data row",
        )
      }

      // Skip the first row (NOTE row) and use the second row as headers
      const headers = jsonData[1] as string[]
      const rows = jsonData.slice(2) // Start from third row (data rows)

      // Map column names (case insensitive)
      const columnMapping: { [key: string]: string } = {
        first_name: "first_name",
        firstname: "first_name",
        "first name": "first_name",
        last_name: "last_name",
        lastname: "last_name",
        "last name": "last_name",
        email: "email",
        email_address: "email",
        contact_no: "contact_no",
        contact: "contact_no",
        phone: "contact_no",
        mobile: "contact_no",
        employee_type: "employee_type",
        type: "employee_type",
        gross_salary: "gross_salary",
        salary: "gross_salary",
        ctc: "gross_salary",
        stipend: "stipend",
        joining_date: "joining_date",
        join_date: "joining_date",
        date_of_joining: "joining_date",
        role_name: "role_name",
        role: "role_name",
        "role name": "role_name",
        status: "status",
        department_name: "department_name",
        department: "department_name",
        "department name": "department_name",
        regime_type: "regime_type",
        // Employee information fields
        address: "address",
        gender: "gender",
        alternate_mobile_number: "alternate_mobile_number",
        "alternate mobile number": "alternate_mobile_number",
        "alternate mobile": "alternate_mobile_number",
        date_of_birth: "date_of_birth",
        "date of birth": "date_of_birth",
        dob: "date_of_birth",
        marital_status: "marital_status",
        "marital status": "marital_status",
        parents_name: "parents_name",
        "parents name": "parents_name",
        "parent name": "parents_name",
        parents_contact_number: "parents_contact_number",
        "parents contact number": "parents_contact_number",
        "parents contact": "parents_contact_number",
        "parent contact": "parents_contact_number",
        blood_group: "blood_group",
        "blood group": "blood_group",
        native_place: "native_place",
        "native place": "native_place",
        aadhar_card: "aadhar_card",
        "aadhar card": "aadhar_card",
        aadhar: "aadhar_card",
        pan_card_number: "pan_card_number",
        "pan card number": "pan_card_number",
        "pan card": "pan_card_number",
        pan: "pan_card_number",
        education_qualification: "education_qualification",
        "education qualification": "education_qualification",
        education: "education_qualification",
        qualification: "education_qualification",
        total_work_experience_years: "total_work_experience_years",
        "total work experience years": "total_work_experience_years",
        "work experience years": "total_work_experience_years",
        "experience years": "total_work_experience_years",
        experience: "total_work_experience_years",
      }

      // Create mapped headers
      const mappedHeaders = headers.map((header) => {
        const normalizedHeader = header?.toString().toLowerCase().trim()
        return columnMapping[normalizedHeader] || normalizedHeader
      })

      // Convert rows to objects
      const employeeData = rows.map((row: any[], index: number) => {
        const employee: any = { row_number: index + 3 } // +3 because index starts at 0, we skip note row and header row

        mappedHeaders.forEach((header, colIndex) => {
          const value = row[colIndex]
          if (value !== null && value !== undefined && value !== "") {
            // Handle specific field transformations
            switch (header) {
              case "status":
                employee[header] = parseInt(value.toString()) || undefined
                break
              case "gross_salary":
              case "stipend":
                employee[header] = parseFloat(value.toString()) || undefined
                break
              case "joining_date":
                // Handle Excel date format
                if (typeof value === "number") {
                  // Excel serial date
                  const date = XLSX.SSF.parse_date_code(value)
                  employee[header] =
                    `${date.y}-${String(date.m).padStart(2, "0")}-${String(date.d).padStart(2, "0")}`
                } else {
                  employee[header] = value.toString()
                }
                break
              default:
                employee[header] = value.toString().trim()
            }
          }
        })

        return employee
      })

      return employeeData.filter((emp) => emp.first_name) // Filter out empty rows
    } catch (error) {
      throw new Error(`Failed to parse Excel file: ${error.message}`)
    }
  }

  /**
   * Generate Excel file with error rows
   */
  private generateErrorExcel(
    errorRows: any[],
    originalHeaders: string[],
  ): Buffer {
    try {
      // Add error_message column to headers
      const headers = [...originalHeaders, "error_message"]

      // Create worksheet data
      const worksheetData = [headers]

      // Add error rows with error messages
      errorRows.forEach((errorRow) => {
        const row = []
        originalHeaders.forEach((header) => {
          row.push(errorRow.originalData[header] || "")
        })
        row.push(errorRow.error_message)
        worksheetData.push(row)
      })

      // Create workbook and worksheet
      const workbook = XLSX.utils.book_new()
      const worksheet = XLSX.utils.aoa_to_sheet(worksheetData)

      // Add worksheet to workbook
      XLSX.utils.book_append_sheet(workbook, worksheet, "Errors")

      // Generate buffer
      return XLSX.write(workbook, { type: "buffer", bookType: "xlsx" })
    } catch (error) {
      console.error("Error generating error Excel file:", error)
      return Buffer.alloc(0)
    }
  }

  /**
   * Lookup role by name for company
   */
  private async lookupRoleByName(
    roleName: string,
    companyId: number,
  ): Promise<number | null> {
    try {
      const role: any = await this.roleRepository.getByParams({
        where: {
          name: roleName.trim(),
          company_id: companyId,
          status: 1,
        },
        whereNull: ["deleted_at"],
        findOne: true,
      })

      return role ? role.id : null
    } catch (error) {
      console.error("Error looking up role:", error)
      return null
    }
  }

  /**
   * Lookup department by name for company
   */
  private async lookupDepartmentByName(
    departmentName: string,
    companyId: number,
  ): Promise<number | null> {
    try {
      const department: any = await this.departmentRepository.getByParams({
        where: {
          name: departmentName.trim(),
          company_id: companyId,
          status: 1,
        },
        whereNull: ["deleted_at"],
        findOne: true,
      })

      return department ? department.id : null
    } catch (error) {
      console.error("Error looking up department:", error)
      return null
    }
  }

  /**
   * Bulk upload employees from Excel file
   */
  async bulkUploadEmployees(fileBuffer: Buffer, token: string) {
    const decoded = verifyJwtToken(token)

    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      // Parse Excel file
      const employeeData = this.parseExcelFile(fileBuffer)

      // Get original headers for error Excel generation
      const workbook = XLSX.read(fileBuffer, { type: "buffer" })
      const sheetName = workbook.SheetNames[0]
      const worksheet = workbook.Sheets[sheetName]
      const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 })
      const originalHeaders = jsonData[1] as string[] // Use second row as headers (skip NOTE row)

      const response: BulkUploadResponse = {
        total_records: employeeData.length,
        success_count: 0,
        failure_count: 0,
        errors: [],
      }

      const errorRowsForExcel: any[] = []

      // Process each employee record
      for (const empData of employeeData) {
        const rowNumber = empData.row_number
        const employeeName =
          `${empData.first_name || ""} ${empData.last_name || ""}`.trim()
        const email = empData.email || ""

        try {
          // Validate required fields
          if (!empData.first_name) {
            throw new Error("First name is required")
          }

          if (!empData.employee_type) {
            throw new Error("Employee type is required")
          }

          if (!empData.role_name) {
            throw new Error("Role name is required")
          }

          // Validate employee_type
          if (
            !Object.values(EmployeeType).includes(
              empData.employee_type as EmployeeType,
            )
          ) {
            throw new Error("Employee type must be 'trainee' or 'employee'")
          }

          // Lookup role by name
          const roleId = await this.lookupRoleByName(
            empData.role_name,
            decoded.company_id,
          )
          if (!roleId) {
            throw new Error(
              `Role '${empData.role_name}' not found for this company`,
            )
          }

          // Lookup department by name (if provided)
          let departmentId: number | undefined = undefined
          if (empData.department_name) {
            departmentId = await this.lookupDepartmentByName(
              empData.department_name,
              decoded.company_id,
            )
            if (!departmentId) {
              throw new Error(
                `Department '${empData.department_name}' not found for this company`,
              )
            }
          }

          // Check if employee with same email exists (if email is provided)
          if (empData.email) {
            const existingEmployee = await this.employeeRepository.getByParams({
              where: {
                company_id: decoded.company_id,
                email: empData.email,
              },
              findOne: true,
            })

            if (existingEmployee) {
              throw new Error(
                `Employee with email ${empData.email} already exists`,
              )
            }
          }

          // Create CreateEmployeeDto object
          const createEmployeeDto: CreateEmployeeDto = {
            first_name: empData.first_name,
            last_name: empData.last_name || undefined,
            email: empData.email || undefined,
            contact_no: empData.contact_no || undefined,
            employee_type: empData.employee_type,
            gross_salary: empData.gross_salary || 0,
            stipend: empData.stipend || undefined,
            joining_date: empData.joining_date || undefined,
            role_id: roleId,
            status: 1,
            department_id: departmentId,
            salary_settings: [], // Empty array for bulk upload, can be enhanced later
            // Employee information fields
            address: empData.address || undefined,
            gender: empData.gender || undefined,
            alternate_mobile_number:
              empData.alternate_mobile_number || undefined,
            date_of_birth: empData.date_of_birth || undefined,
            marital_status: empData.marital_status || undefined,
            parents_name: empData.parents_name || undefined,
            parents_contact_number: empData.parents_contact_number || undefined,
            blood_group: empData.blood_group || undefined,
            native_place: empData.native_place || undefined,
            aadhar_card: empData.aadhar_card || undefined,
            pan_card_number: empData.pan_card_number || undefined,
            education_qualification:
              empData.education_qualification || undefined,
            total_work_experience_years:
              empData.total_work_experience_years || undefined,
          }

          // Create query runner for individual employee transaction
          const queryRunner = this.dataSource.createQueryRunner()
          await queryRunner.connect()
          await queryRunner.startTransaction()

          try {
            // Extract salary details and employee information from DTO
            const {
              salary_settings,
              gross_salary,
              employee_type,
              stipend,
              // Employee information fields
              address,
              gender,
              alternate_mobile_number,
              date_of_birth,
              marital_status,
              parents_name,
              parents_contact_number,
              blood_group,
              native_place,
              aadhar_card,
              pan_card_number,
              education_qualification,
              total_work_experience_years,
              ...employeeDataForSave
            } = createEmployeeDto

            // Set default gross_salary to 0 if not provided
            const finalGrossSalary = gross_salary || 0

            let basicSalary = 0
            let totalEarnings = 0
            let totalDeductions = 0
            let salaryDetails: any[] = []
            let netPayableSalary = 0

            // Only calculate salary components if gross_salary > 0
            if (finalGrossSalary > 0) {
              // Calculate basic salary using company's basic salary percentage
              basicSalary = await this.calculateBasicSalary(
                finalGrossSalary,
                decoded.company_id,
              )

              // Calculate salary components if salary settings are provided
              if (salary_settings && salary_settings.length > 0) {
                const salaryCalculation = await this.calculateSalaryComponents(
                  salary_settings,
                  basicSalary,
                )
                totalEarnings = salaryCalculation.totalEarnings
                totalDeductions = salaryCalculation.totalDeductions
                salaryDetails = salaryCalculation.salaryDetails
              } else {
                // If no salary settings, basic salary is the earning
                totalEarnings = basicSalary
              }

              // Calculate net payable salary
              netPayableSalary = totalEarnings - totalDeductions
            }

            let employee: any = new Employee()
            employee = {
              ...employeeDataForSave,
              employee_type: employee_type,
              gross_salary: finalGrossSalary,
              basic_salary: basicSalary,
              net_payable_salary: netPayableSalary,
              stipend: stipend || null,
              regime_type: "new", // Default to new regime for bulk upload
              tds_amount: 0, // Will be calculated and updated in salary history
              status: 1,
              company_id: decoded.company_id,
              created_by: decoded.user_id,
            }

            // Save employee with transaction
            const savedEmployee = await this.employeeRepository.save(
              employee,
              null,
              queryRunner.manager,
            )

            let savedUser: any = null

            // Create user entry if email is provided
            if (createEmployeeDto.email) {
              const userData = {
                first_name: createEmployeeDto.first_name,
                last_name: createEmployeeDto.last_name || "",
                email: createEmployeeDto.email,
                phone: createEmployeeDto.contact_no,
                password: null, // No password initially
                role_id: createEmployeeDto.role_id,
                company_id: decoded.company_id,
                employee_id: savedEmployee.id, // Link to employee
                status: 0, // Inactive until password is set
                created_by: decoded.user_id,
              }

              savedUser = await this.authRepository.save(
                userData,
                null,
                queryRunner.manager,
              )
            }

            // Create employee information if any information fields are provided
            if (address || gender || alternate_mobile_number) {
              // Validate mandatory fields for employee information
              if (!address || !gender || !alternate_mobile_number) {
                throw new Error(
                  "Address, gender, and alternate mobile number are required for employee information",
                )
              }

              const employeeInformationData = {
                employee_id: savedEmployee.id,
                address,
                gender,
                alternate_mobile_number,
                date_of_birth: date_of_birth ? new Date(date_of_birth) : null,
                marital_status,
                parents_name,
                parents_contact_number,
                blood_group,
                native_place,
                aadhar_card,
                pan_card_number,
                education_qualification,
                total_work_experience_years,
                created_by: decoded.user_id,
              }

              await this.employeeInformationRepository.save(
                employeeInformationData,
                null,
                queryRunner.manager,
              )
            }

            // Create salary detail entries only if we have salary details
            if (salaryDetails && salaryDetails.length > 0) {
              for (const salaryDetail of salaryDetails) {
                await queryRunner.manager.save("employee_salary_details", {
                  employee_id: savedEmployee.id,
                  salary_setting_id: salaryDetail.salary_setting_id,
                  gross_salary: finalGrossSalary,
                  individual_value: salaryDetail.individual_value,
                  calculated_amount: salaryDetail.calculated_amount,
                  created_by: decoded.user_id,
                })
              }

              // Calculate TDS after salary details are saved
              if (finalGrossSalary > 0) {
                try {
                  // Create salary breakdown from salary settings with proper structure
                  const salaryBreakdown =
                    await this.createSalaryBreakdownForTds(
                      salary_settings || [],
                      basicSalary,
                      decoded.company_id,
                    )

                  // Prepare TDS calculation input
                  const tdsInput: TdsCalculationInput = {
                    employeeId: savedEmployee.id,
                    annualCTC: finalGrossSalary * 12,
                    regimeType: "new" as TaxRegime, // Default to new regime for bulk upload
                    salaryBreakdown,
                    isMetroCity: false, // Default to non-metro, can be enhanced later
                    ltaClaimed: false, // Default to not claimed
                    rentPaid: 0, // Default to 0, can be enhanced later
                  }

                  // Calculate TDS
                  const tdsResult = calculateTdsAmount(tdsInput)
                  const tdsAmount = tdsResult.annualTdsAmount

                  // Deduct monthly TDS from net payable salary
                  const monthlyTdsAmount = Math.round(tdsAmount / 12)
                  netPayableSalary = netPayableSalary - monthlyTdsAmount

                  // Update employee record with TDS amount and adjusted net payable salary
                  await queryRunner.manager.update(
                    "employees",
                    savedEmployee.id,
                    {
                      tds_amount: monthlyTdsAmount,
                      net_payable_salary: netPayableSalary,
                    },
                  )
                } catch (error) {
                  console.error(
                    "Error calculating TDS during bulk employee creation:",
                    error,
                  )
                  // Continue without TDS calculation if there's an error
                }
              }
            }

            // Get company settings for basic salary percentage
            const companySettings: any =
              await this.companySettingsRepository.getByParams({
                where: { company_id: decoded.company_id },
                findOne: true,
              })

            const basicSalaryPercentage =
              companySettings?.basic_salary_percentage || "45"

            // Create salary history record only if gross_salary > 0
            if (finalGrossSalary > 0) {
              await this.createSalaryHistory(
                savedEmployee.id,
                decoded.company_id,
                finalGrossSalary,
                basicSalary,
                totalEarnings,
                totalDeductions,
                netPayableSalary,
                basicSalaryPercentage,
                decoded.user_id,
                new Date(createEmployeeDto.joining_date || new Date()),
                queryRunner.manager,
                createEmployeeDto.regime_type || "new",
              )
            }

            // Create leave balances for all active leave types
            await this.createEmployeeLeaveBalancesWithTransaction(
              savedEmployee.id,
              decoded.company_id,
              decoded.user_id,
              queryRunner.manager,
            )

            // Commit transaction
            await queryRunner.commitTransaction()

            // Send password setup email after successful transaction (outside transaction)
            if (createEmployeeDto.email && savedUser) {
              try {
                await this.authService.sendPasswordSetupEmail(
                  savedUser.id,
                  employeeName,
                  createEmployeeDto.email,
                )
              } catch (error) {
                console.error("Failed to send password setup email:", error)
                // Don't fail the entire employee creation if email fails
              }
            }

            response.success_count++
          } catch (err) {
            // Rollback transaction on error
            await queryRunner.rollbackTransaction()
            throw err
          } finally {
            await queryRunner.release()
          }
        } catch (error) {
          // Add error to response
          response.failure_count++
          const errorMessage = error.message || "Unknown error occurred"

          response.errors.push({
            row_number: rowNumber,
            employee_name: employeeName,
            email: email,
            error_message: errorMessage,
          })

          // Store error row data for Excel generation
          errorRowsForExcel.push({
            originalData: empData,
            error_message: errorMessage,
          })
        }
      }

      // Generate error Excel file if there are errors
      if (errorRowsForExcel.length > 0) {
        response.error_file = this.generateErrorExcel(
          errorRowsForExcel,
          originalHeaders,
        )
      }

      return successResponse(
        code.SUCCESS,
        `Bulk upload completed. ${response.success_count} employees created successfully, ${response.failure_count} failed.`,
        response as any,
      )
    } catch (error) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.exception, { ":error": error.message }),
      )
    }
  }

  /**
   * Calculate carry forward days from previous year
   */
  private async calculateCarryForwardDays(
    employeeId: number,
    leaveTypeId: number,
    companyId: number,
    currentYear: number,
  ): Promise<number> {
    try {
      // Get previous year's leave balance
      const previousYear = currentYear - 1
      const previousYearBalance =
        await this.employeeLeaveBalanceRepository.getByParams({
          where: {
            employee_id: employeeId,
            leave_type_id: leaveTypeId,
            company_id: companyId,
            year: previousYear,
          },
          whereNull: ["deleted_at"],
          findOne: true,
        })

      if (isEmpty(previousYearBalance)) {
        return 0
      }

      // Get leave type to check if carry forward is enabled
      const leaveType = await this.leaveTypeEntityRepository.findOne({
        where: {
          id: leaveTypeId,
          company_id: companyId,
          status: 1,
          deleted_at: null,
        },
      })

      if (!leaveType || leaveType.carry_forward !== 1) {
        return 0
      }

      // Calculate unused days from previous year
      const previousBalance = previousYearBalance as EmployeeLeaveBalance
      const unusedDays =
        previousBalance.total_allocated +
        previousBalance.carried_forward_days -
        previousBalance.used_days

      // Return unused days (carry forward amount)
      return Math.max(0, unusedDays)
    } catch (error) {
      console.error("Error calculating carry forward days:", error)
      return 0
    }
  }

  /**
   * Process carry forward for all employees across all companies at year end
   */
  async processYearEndCarryForward(year: number, systemUserId: number = 1) {
    try {
      console.log(
        `🔄 Starting year-end carry forward processing for year ${year}...`,
      )

      // Get all companies that have leave types with carry forward enabled
      const companiesWithCarryForward = await this.dataSource.query(`
        SELECT DISTINCT c.id, c.name 
        FROM companies c
        INNER JOIN leave_types lt ON lt.company_id = c.id
        WHERE c.status = 1 
          AND c.deleted_at IS NULL
          AND lt.carry_forward = 1 
          AND lt.status = 1 
          AND lt.deleted_at IS NULL
      `)

      if (
        !companiesWithCarryForward ||
        companiesWithCarryForward.length === 0
      ) {
        console.log("ℹ️ No companies found with carry forward leave types")
        return {
          success: true,
          message: "No companies found with carry forward leave types",
          data: { processedCompanies: 0, totalProcessed: 0, totalErrors: 0 },
        }
      }

      const nextYear = year + 1
      let totalProcessed = 0
      let totalErrors = 0
      let processedCompanies = 0

      for (const company of companiesWithCarryForward) {
        try {
          console.log(
            `🏢 Processing carry forward for company: ${company.name} (ID: ${company.id})`,
          )

          // Get all active employees for the company
          const employees = await this.employeeRepository.getByParams({
            where: {
              company_id: company.id,
              status: 1,
            },
            whereNull: ["deleted_at"],
          })

          // Get all leave types with carry forward enabled for this company
          const leaveTypesWithCarryForward =
            await this.leaveTypeEntityRepository.find({
              where: {
                company_id: company.id,
                carry_forward: 1,
                status: 1,
                deleted_at: null,
              },
            })

          const employeesArray = Array.isArray(employees) ? employees : []
          let companyProcessedCount = 0
          let companyErrorCount = 0

          for (const employee of employeesArray) {
            for (const leaveType of leaveTypesWithCarryForward) {
              try {
                // Calculate carry forward days
                const carryForwardDays = await this.calculateCarryForwardDays(
                  employee.id,
                  leaveType.id,
                  company.id,
                  nextYear,
                )

                // Check if next year balance already exists
                const existingBalance =
                  await this.employeeLeaveBalanceRepository.getByParams({
                    where: {
                      employee_id: employee.id,
                      leave_type_id: leaveType.id,
                      company_id: company.id,
                      year: nextYear,
                    },
                    whereNull: ["deleted_at"],
                    findOne: true,
                  })

                if (isEmpty(existingBalance)) {
                  // Create new balance for next year with carry forward
                  const newBalance = new EmployeeLeaveBalance()
                  newBalance.employee_id = employee.id
                  newBalance.leave_type_id = leaveType.id
                  newBalance.company_id = company.id
                  newBalance.year = nextYear
                  newBalance.total_allocated = leaveType.annual_allowed_days
                  newBalance.carried_forward_days = carryForwardDays
                  newBalance.used_days = 0
                  newBalance.available_days =
                    leaveType.annual_allowed_days + carryForwardDays
                  newBalance.created_by = systemUserId

                  await this.employeeLeaveBalanceRepository.save(newBalance)
                  companyProcessedCount++
                } else {
                  // Update existing balance with carry forward
                  const existingBalanceEntity =
                    existingBalance as EmployeeLeaveBalance
                  existingBalanceEntity.carried_forward_days = carryForwardDays
                  existingBalanceEntity.available_days =
                    existingBalanceEntity.total_allocated +
                    carryForwardDays -
                    existingBalanceEntity.used_days
                  existingBalanceEntity.updated_by = systemUserId

                  await this.employeeLeaveBalanceRepository.save(
                    existingBalanceEntity,
                  )
                  companyProcessedCount++
                }
              } catch (error) {
                console.error(
                  `❌ Error processing carry forward for employee ${employee.id}, leave type ${leaveType.id}:`,
                  error,
                )
                companyErrorCount++
              }
            }
          }

          totalProcessed += companyProcessedCount
          totalErrors += companyErrorCount
          processedCompanies++

          console.log(
            `✅ Company ${company.name}: ${companyProcessedCount} balances processed, ${companyErrorCount} errors`,
          )
        } catch (error) {
          console.error(`❌ Error processing company ${company.name}:`, error)
          totalErrors++
        }
      }

      const message = `Carry forward processing completed. ${processedCompanies} companies processed, ${totalProcessed} balances processed, ${totalErrors} errors.`
      console.log(`🎉 ${message}`)

      return {
        success: true,
        message,
        data: { processedCompanies, totalProcessed, totalErrors },
      }
    } catch (error) {
      console.error("❌ Error in year-end carry forward processing:", error)
      return {
        success: false,
        message: error.message,
        data: { processedCompanies: 0, totalProcessed: 0, totalErrors: 0 },
      }
    }
  }

  /**
   * Create salary breakdown for TDS calculation with proper salary setting codes
   */
  private async createSalaryBreakdownForTds(
    salarySettings: any[],
    basicSalary: number,
    companyId: number,
  ): Promise<SalaryBreakdown> {
    const breakdown: SalaryBreakdown = {}

    // Add basic salary (always taxable)
    breakdown[SALARY_COMPONENT_CODES.BASIC] = {
      amount: basicSalary,
      is_taxable: true,
    }

    // Process each salary setting
    for (const setting of salarySettings) {
      try {
        // Get the salary setting details with code
        const salarySettingDetails: any =
          await this.salarySettingRepository.getByParams({
            where: { id: setting.salary_setting_id, company_id: companyId },
            findOne: true,
          })

        if (salarySettingDetails && salarySettingDetails.code) {
          // Calculate the amount based on type_value (percentage or fixed)
          const calculatedAmount = this.calculateAmount(
            setting.individual_value,
            basicSalary,
            salarySettingDetails.type_value,
          )

          breakdown[salarySettingDetails.code] = {
            amount: calculatedAmount,
            is_taxable: salarySettingDetails.is_taxable === 1,
          }
        }
      } catch (error) {
        console.error(
          `Error processing salary setting ${setting.salary_setting_id}:`,
          error,
        )
      }
    }

    return breakdown
  }

  /**
   * Assign multiple projects to an employee
   */
  async assignProjects(
    employeeId: number,
    projectIds: number[],
    authorizationHeader: string,
  ) {
    try {
      // Verify JWT token and get user info
      const decoded = verifyJwtToken(authorizationHeader)

      if (!decoded) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.invalid_token),
        )
      }

      // Check if employee exists
      const employee = await this.employeeRepository.getByParams({
        where: { id: employeeId },
        whereNull: ["deleted_at"],
        findOne: true,
      })

      if (!employee) {
        return failureResponse(code.ERROR, errorMessage(messageKey.exception))
      }

      // Get existing project assignments for this employee
      const existingAssignments =
        await this.projectEmployeeEntityRepository.find({
          where: { employee_id: employeeId },
        })

      const existingProjectIds = existingAssignments.map(
        (assignment) => assignment.project_id,
      )

      // Filter out projects that are already assigned
      const newProjectIds = projectIds.filter(
        (projectId) => !existingProjectIds.includes(projectId),
      )

      if (newProjectIds.length === 0) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Projects",
            ":field": "project_ids",
          }),
        )
      }

      // Create new project assignments
      const projectAssignments = newProjectIds.map((projectId) => ({
        project_id: projectId,
        employee_id: employeeId,
        assigned_by: decoded.user_id,
        assigned_at: new Date(),
      }))

      await this.projectEmployeeEntityRepository.save(projectAssignments)

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_add, { ":data": "Project assignments" }),
      )
    } catch (error) {
      console.error("Error assigning projects:", error)
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }
}