import { Injectable } from "@nestjs/common"
import { CreateLeaveRequestDto } from "./dto/create-leave-request.dto"
import { UpdateLeaveRequestDto } from "./dto/update-leave-request.dto"
import { UpdateLeaveRequestStatusDto } from "./dto/update-leave-request-status.dto"
import { LeaveRequestRepository } from "./repositories/leave-request.repository"
import { LeaveRequestApprovalLogRepository } from "./repositories/leave-request-approval-log.repository"
import { EmployeeLeaveBalanceRepository } from "../employee-leave-balances/repositories/employee-leave-balance.repository"
import { EmployeeRepository } from "../employees/repositories/employee.repository"
import { CompanySettingsRepository } from "../company/repositories/company-settings.repository"
import { DataSource } from "typeorm"
import {
  errorMessage,
  isEmpty,
  successMessage,
  validationMessage,
  sendEmailNotification,
} from "../../utils/helpers"
import {
  failureResponse,
  successResponse,
} from "../../common/response/response"
import { code } from "../../common/response/response.code"
import { messageKey } from "../../constants/message-keys"
import {
  LeaveRequest,
  LeaveRequestStatus,
  LeaveDayType,
} from "./entities/leave-request.entity"
import {
  LeaveRequestApprovalLog,
  ApprovalAction,
} from "./entities/leave-request-approval-log.entity"
import { verifyJwtToken } from "src/utils/jwt"
import { EmployeeLeaveBalance } from "../employee-leave-balances/entities/employee-leave-balance.entity"
import { LeaveRequestFiltersDto } from "./dto/leave-request-filters.dto"
import moment from "moment"
import { mailSubject } from "../../common/emails/email-subjects"
import { leaveStatusNotificationEmail } from "../../common/emails/templates/leave-status-notification"
import { leaveApprovalRequiredEmail } from "../../common/emails/templates/leave-approval-required"
import { PermissionRepository } from "../permissions/repositories/permission.repository"
import { RolePermissionRepository } from "../role-permissions/repositories/role-permission.repository"
import { AuthRepository } from "../auth/repositories/auth.repository"
import { LeaveTypeRepository } from "../leave-types/repositories/leave-type.repository"
import { PERMISSIONS } from "../../constants/permissions.constant"
import { EV } from "src/utils/env.values"
import { NotificationService } from "../notification/notification.service"
import {
  notificationTitles,
  notificationMessages,
} from "../../constants/notification.constant"

@Injectable()
export class LeaveRequestsService {
  constructor(
    private readonly leaveRequestRepository: LeaveRequestRepository,
    private readonly leaveRequestApprovalLogRepository: LeaveRequestApprovalLogRepository,
    private readonly employeeLeaveBalanceRepository: EmployeeLeaveBalanceRepository,
    private readonly employeeRepository: EmployeeRepository,
    private readonly companySettingsRepository: CompanySettingsRepository,
    private readonly dataSource: DataSource,
    private readonly permissionRepository: PermissionRepository,
    private readonly rolePermissionRepository: RolePermissionRepository,
    private readonly authRepository: AuthRepository,
    private readonly leaveTypeRepository: LeaveTypeRepository,
    private readonly notificationService: NotificationService,
  ) {}

  /**
   * Helper method to format approval logs with employee names
   */
  private async formatApprovalLogs(leaveRequests: any) {
    if (!leaveRequests?.data) return leaveRequests

    const requests = Array.isArray(leaveRequests.data)
      ? leaveRequests.data
      : [leaveRequests.data]

    // Collect all unique employee IDs from approval logs
    const employeeIds = new Set<number>()

    requests.forEach((request: any) => {
      if (request.approvalLogs) {
        request.approvalLogs.forEach((log: any) => {
          if (log.approved_by) employeeIds.add(log.approved_by)
          if (log.rejected_by) employeeIds.add(log.rejected_by)
        })
      }
    })

    // Fetch employee details if we have IDs
    const employeesMap: { [key: number]: any } = {}
    if (employeeIds.size > 0) {
      const employees = await this.employeeRepository.getByParams({
        whereIn: { id: Array.from(employeeIds) },
        select: ["id", "first_name", "last_name"],
      })

      if (Array.isArray(employees)) {
        employees.forEach((emp: any) => {
          employeesMap[emp.id] = emp
        })
      }
    }

    // Format approval logs with employee names
    requests.forEach((request: any) => {
      if (request.approvalLogs) {
        request.approvalLogs = request.approvalLogs.map((log: any) => ({
          action: log.action,
          approved_by: log.approved_by
            ? {
                id: log.approved_by,
                name: employeesMap[log.approved_by]
                  ? `${employeesMap[log.approved_by].first_name || ""} ${employeesMap[log.approved_by].last_name || ""}`.trim()
                  : "Unknown",
              }
            : null,
          rejected_by: log.rejected_by
            ? {
                id: log.rejected_by,
                name: employeesMap[log.rejected_by]
                  ? `${employeesMap[log.rejected_by].first_name || ""} ${employeesMap[log.rejected_by].last_name || ""}`.trim()
                  : "Unknown",
              }
            : null,
          reject_reason: log.reject_reason,
          created_at: log.created_at,
        }))
      }
    })

    return leaveRequests
  }

  async create(createLeaveRequestDto: CreateLeaveRequestDto, token: string) {
    try {
      const decoded = verifyJwtToken(token)

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

      // Get employee details from token
      const employee = await this.employeeRepository.getByParams({
        where: { id: decoded.employee_id, company_id: decoded.company_id },
        findOne: true,
      })

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

      // Set default leave_day_type if not provided
      const leaveDayType =
        createLeaveRequestDto.leave_day_type || LeaveDayType.FULL

      // Validate dates
      const fromDate = new Date(createLeaveRequestDto.from_date)
      const toDate = new Date(createLeaveRequestDto.to_date)

      if (fromDate > toDate) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.invalid_date_range),
        )
      }

      // Validation rules for half-day leaves
      if (
        leaveDayType === LeaveDayType.FIRST_HALF ||
        leaveDayType === LeaveDayType.SECOND_HALF
      ) {
        // from_date must equal to_date for half-day leaves
        if (fromDate.getTime() !== toDate.getTime()) {
          return failureResponse(
            code.VALIDATION,
            validationMessage(
              "Half-day leaves must have the same from_date and to_date",
            ),
          )
        }
      }

      // Calculate duration if not provided
      let durationDays = createLeaveRequestDto.duration_days
      if (!durationDays) {
        if (
          leaveDayType === LeaveDayType.FIRST_HALF ||
          leaveDayType === LeaveDayType.SECOND_HALF
        ) {
          durationDays = 0.5
        } else {
          const timeDiff = toDate.getTime() - fromDate.getTime()
          durationDays = Math.ceil(timeDiff / (1000 * 3600 * 24)) + 1 // +1 to include both start and end dates
        }
      }

      // Check leave balance
      const currentYear = new Date().getFullYear()

      let leaveBalance = await this.employeeLeaveBalanceRepository.getByParams({
        where: {
          employee_id: decoded.employee_id,
          leave_type_id: createLeaveRequestDto.leave_type_id,
          year: currentYear,
          company_id: decoded.company_id,
        },
        findOne: true,
      })

      if (isEmpty(leaveBalance)) {
        // Auto-create leave balance if it doesn't exist
        const newLeaveBalance = await this.createLeaveBalanceIfNotExists(
          decoded.employee_id,
          createLeaveRequestDto.leave_type_id,
          decoded.company_id,
          currentYear,
          decoded.user_id,
        )

        if (!newLeaveBalance) {
          return failureResponse(
            code.VALIDATION,
            errorMessage(messageKey.leave_balance_not_found),
          )
        }

        // Use the newly created balance
        leaveBalance = newLeaveBalance
      }

      // Calculate actual leave deduction for balance check
      const leaveDeduction =
        leaveDayType === LeaveDayType.FIRST_HALF ||
        leaveDayType === LeaveDayType.SECOND_HALF
          ? 0.5
          : durationDays

      const availableDays =
        parseFloat(
          (leaveBalance as EmployeeLeaveBalance).available_days.toString(),
        ) || 0

      if (availableDays < leaveDeduction) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.insufficient_leave_balance),
        )
      }

      let leaveRequest: any = new LeaveRequest()
      leaveRequest = {
        ...createLeaveRequestDto,
        duration_days: durationDays,
        leave_day_type: leaveDayType,
        employee_id: decoded.employee_id,
        company_id: decoded.company_id,
        status: LeaveRequestStatus.PENDING,
        created_by: decoded.user_id,
      }

      const savedLeaveRequest =
        await this.leaveRequestRepository.save(leaveRequest)

      // Send notification to approvers in background (don't await)
      this.notifyLeaveApprovers(savedLeaveRequest, decoded.company_id).catch(
        (emailError) => {
          console.error(
            "❌ Error sending leave approval notifications:",
            emailError.message,
          )
          // Don't fail the main operation if email fails
        },
      )

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

  async findAll(query: LeaveRequestFiltersDto, token: string) {
    const decoded = verifyJwtToken(token)

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

    const {
      skip = 0,
      take = 10,
      page,
      limit,
      search,
      leave_type_id,
      department_id,
      employee_id,
      status,
      from_date,
      to_date,
      company_id,
      column_name,
      order = "DESC",
      showAll,
      date,
    } = query

    // Handle legacy pagination parameters
    let finalSkip = skip
    let finalTake = take

    if (page && limit) {
      finalSkip = (page - 1) * limit
      finalTake = limit
    } else {
      finalTake = take
    }

    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 "from_date":
          orderBy = { from_date: orderDirection }
          break
        case "to_date":
          orderBy = { to_date: orderDirection }
          break
        case "employee_name":
          orderBy = {
            "employee.first_name": orderDirection,
            "employee.last_name": orderDirection,
          }
          break
        case "leave_type":
          orderBy = { "leaveType.name": orderDirection }
          break
        case "created_at":
        default:
          orderBy = { created_at: orderDirection }
          break
      }
    }

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

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

    // Filter by specific employee if provided
    if (showAll == "true") {
      // Case: showAll = true → allow filtering by passed employee_id only
      if (employee_id) {
        whereConditions.employee_id = employee_id
      }
      // If employee_id not provided → do not add employee filter at all
    } else {
      // Case: showAll = false → apply logged-in user's employee_id by default
      if (decoded.employee_id) {
        whereConditions.employee_id = decoded.employee_id
      }

      // If API explicitly passes employee_id → override
      if (employee_id) {
        whereConditions.employee_id = employee_id
      }
    }

    // Filter by department through employee relationship
    if (department_id) {
      whereConditions["entity_employee.department_id"] = department_id
    }

    if (leave_type_id) {
      whereConditions.leave_type_id = leave_type_id
    }

    if (status !== undefined && status !== null) {
      whereConditions.status = status
    }

    if (date === "today") {
      const todayStart = moment().startOf("day").toDate()
      const todayEnd = moment().endOf("day").toDate()

      whereConditions.from_date = { lte: todayEnd }
      whereConditions.to_date = { gte: todayStart }

      // Optional but usually correct
      whereConditions.status = "approved"
    } else {
      if (from_date) {
        whereConditions.from_date = { gte: new Date(from_date) }
      }

      if (to_date) {
        whereConditions.to_date = { lte: new Date(to_date) }
      }
    }

    // Enhanced search functionality
    if (search) {
      const searchTerms = search.trim().split(" ")

      if (searchTerms.length > 1) {
        // Multi-word search (likely first name + last name)
        searchConditions = {
          "entity_employee.first_name": searchTerms[0],
          "entity_employee.last_name": searchTerms[1],
          "entity_leaveType.name": search,
          reason: search,
        }
      } else {
        // Single word search
        searchConditions = {
          "entity_employee.first_name": search,
          "entity_employee.last_name": search,
          "entity_leaveType.name": search,
          reason: search,
        }
      }
    }

    const leaveRequests: any = await this.leaveRequestRepository.getByParams({
      where: whereConditions,
      search: !isEmpty(searchConditions) ? searchConditions : undefined,
      relations: [
        "company:id,name",
        "employee:id,first_name,last_name,department_id.department:id,name",
        "leaveType:id,name,code",
        "approvedBy:id,first_name,last_name",
        "approvalLogs:id,action,approved_by,rejected_by,reject_reason,created_at",
      ],
      orderBy,
      take: finalTake,
      skip: finalSkip,
    })

    // Format approval logs with employee names
    const formattedLeaveRequests = await this.formatApprovalLogs(leaveRequests)

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Leave Requests" }),
      formattedLeaveRequests,
    )
  }

  async findMyLeaveRequests(query: LeaveRequestFiltersDto, token: string) {
    try {
      const decoded = verifyJwtToken(token)

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

      const {
        skip = 0,
        take = 10,
        page,
        limit,
        search,
        leave_type_id,
        department_id,
        status,
        from_date,
        to_date,
        company_id,
        column_name,
        order = "DESC",
      } = query

      // Handle legacy pagination parameters
      let finalSkip = skip
      let finalTake = take

      if (page && limit) {
        finalSkip = (page - 1) * limit
        finalTake = limit
      } else {
        finalTake = take
      }

      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 "from_date":
            orderBy = { from_date: orderDirection }
            break
          case "to_date":
            orderBy = { to_date: orderDirection }
            break
          case "employee_name":
            orderBy = {
              "employee.first_name": orderDirection,
              "employee.last_name": orderDirection,
            }
            break
          case "leave_type":
            orderBy = { "leaveType.name": orderDirection }
            break
          case "created_at":
          default:
            orderBy = { created_at: orderDirection }
            break
        }
      }

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

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

      // Filter by specific employee if provided
      if (decoded.employee_id) {
        whereConditions.employee_id = decoded.employee_id
      }

      // Filter by department through employee relationship
      if (department_id) {
        whereConditions["entity_employee.department_id"] = department_id
      }

      if (leave_type_id) {
        whereConditions.leave_type_id = leave_type_id
      }

      if (status !== undefined && status !== null) {
        whereConditions.status = status
      }

      if (from_date) {
        whereConditions.from_date = { gte: new Date(from_date) }
      }

      if (to_date) {
        whereConditions.to_date = { lte: new Date(to_date) }
      }

      // Enhanced search functionality
      if (search) {
        const searchTerms = search.trim().split(" ")

        if (searchTerms.length > 1) {
          // Multi-word search (likely first name + last name)
          searchConditions = {
            "entity_employee.first_name": searchTerms[0],
            "entity_employee.last_name": searchTerms[1],
            "entity_leaveType.name": search,
            reason: search,
          }
        } else {
          // Single word search
          searchConditions = {
            "entity_employee.first_name": search,
            "entity_employee.last_name": search,
            "entity_leaveType.name": search,
            reason: search,
          }
        }
      }

      const leaveRequests: any = await this.leaveRequestRepository.getByParams({
        where: whereConditions,
        search: !isEmpty(searchConditions) ? searchConditions : undefined,
        relations: [
          "company:id,name",
          "employee:id,first_name,last_name,department_id.department:id,name",
          "leaveType:id,name,code",
          "approvedBy:id,first_name,last_name",
          "approvalLogs:id,action,approved_by,rejected_by,reject_reason,created_at",
        ],
        orderBy,
        take: finalTake,
        skip: finalSkip,
      })

      // Format approval logs with employee names
      const formattedLeaveRequests =
        await this.formatApprovalLogs(leaveRequests)

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

  async findOne(id: number) {
    const leaveRequest: any = await this.leaveRequestRepository.getByParams({
      where: { id },
      whereNull: ["deleted_at"],
      relations: [
        "company:id,name",
        "employee:id,first_name,last_name",
        "leaveType:id,name,code",
        "approvedBy:id,first_name,last_name",
        "approvalLogs:id,action,approved_by,rejected_by,reject_reason,created_at",
      ],
      findOne: true,
    })

    if (isEmpty(leaveRequest)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Leave Request" }),
      )
    }

    // Format approval logs with employee names
    const formattedLeaveRequest = await this.formatApprovalLogs({
      data: leaveRequest,
    })

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Leave Request" }),
      formattedLeaveRequest.data,
    )
  }

  async update(
    id: number,
    updateLeaveRequestDto: UpdateLeaveRequestDto,
    token: string,
  ) {
    try {
      const decoded = verifyJwtToken(token)

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

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

      if (isEmpty(leaveRequest)) {
        return failureResponse(
          code.VALIDATION,
          errorMessage(messageKey.data_not_found, { ":data": "Leave Request" }),
        )
      }

      // Only allow updates if status is pending
      if (leaveRequest.status !== LeaveRequestStatus.PENDING) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.leave_request_cannot_be_updated),
        )
      }

      // -----------------------------
      // 1️⃣ HANDLE DATE + DURATION
      // -----------------------------

      const fromDate = new Date(
        updateLeaveRequestDto.from_date || leaveRequest.from_date,
      )
      const toDate = new Date(
        updateLeaveRequestDto.to_date || leaveRequest.to_date,
      )

      if (fromDate > toDate) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.invalid_date_range),
        )
      }

      // Get leave_day_type (use existing if not provided in update)
      const leaveDayType =
        updateLeaveRequestDto.leave_day_type ||
        leaveRequest.leave_day_type ||
        LeaveDayType.FULL

      // Validation rules for half-day leaves
      if (
        leaveDayType === LeaveDayType.FIRST_HALF ||
        leaveDayType === LeaveDayType.SECOND_HALF
      ) {
        // from_date must equal to_date for half-day leaves
        if (fromDate.getTime() !== toDate.getTime()) {
          return failureResponse(
            code.VALIDATION,
            validationMessage(
              "Half-day leaves must have the same from_date and to_date",
            ),
          )
        }
      }

      // Recalculate duration days
      let durationDays = updateLeaveRequestDto.duration_days
      if (!durationDays) {
        if (
          leaveDayType === LeaveDayType.FIRST_HALF ||
          leaveDayType === LeaveDayType.SECOND_HALF
        ) {
          durationDays = 0.5
        } else {
          durationDays =
            Math.ceil(
              (toDate.getTime() - fromDate.getTime()) / (1000 * 3600 * 24),
            ) + 1
        }
      }

      // -----------------------------
      // 2️⃣ HANDLE LEAVE BALANCE
      // -----------------------------

      const currentYear = new Date().getFullYear()

      // Leave type might also be updated
      const leaveTypeId =
        updateLeaveRequestDto.leave_type_id || leaveRequest.leave_type_id

      let leaveBalance: any =
        await this.employeeLeaveBalanceRepository.getByParams({
          where: {
            employee_id: decoded.employee_id,
            leave_type_id: leaveTypeId,
            year: currentYear,
            company_id: decoded.company_id,
          },
          findOne: true,
        })

      // Auto-create leave balance if not exists
      if (isEmpty(leaveBalance)) {
        leaveBalance = await this.createLeaveBalanceIfNotExists(
          decoded.employee_id,
          leaveTypeId,
          decoded.company_id,
          currentYear,
          decoded.user_id,
        )

        if (!leaveBalance) {
          return failureResponse(
            code.VALIDATION,
            errorMessage(messageKey.leave_balance_not_found),
          )
        }
      }

      // Calculate actual leave deduction for balance check
      const leaveDeduction =
        leaveDayType === LeaveDayType.FIRST_HALF ||
        leaveDayType === LeaveDayType.SECOND_HALF
          ? 0.5
          : durationDays

      const availableDays =
        parseFloat(leaveBalance.available_days.toString()) || 0

      // Check if enough available days exist
      if (availableDays < leaveDeduction) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.insufficient_leave_balance),
        )
      }

      Object.assign(leaveRequest, updateLeaveRequestDto)
      leaveRequest.duration_days = durationDays
      leaveRequest.updated_by = decoded.user_id

      await this.leaveRequestRepository.save(leaveRequest)

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

  async remove(id: number, token: string) {
    const decoded = verifyJwtToken(token)

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

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

    if (isEmpty(leaveRequest)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Leave Request" }),
      )
    }

    // Only allow deletion if status is pending
    if (leaveRequest.status !== LeaveRequestStatus.PENDING) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.leave_request_cannot_be_deleted),
      )
    }

    await this.leaveRequestRepository.remove({ id: leaveRequest.id })

    await this.leaveRequestRepository.save({
      id: leaveRequest.id,
      deleted_by: decoded.user_id,
    })

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

  async updateStatus(
    id: number,
    updateStatusDto: UpdateLeaveRequestStatusDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

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

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

    if (isEmpty(leaveRequest)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Leave Request" }),
      )
    }

    // Extract user details from JWT payload
    const { role_id, employee_id, user_id, company_id } = decoded

    // bypass already approval and rejection logs
    // // Check if the user has already approved this leave request
    // const existingApprovalLog =
    //   await this.leaveRequestApprovalLogRepository.getByParams({
    //     where: {
    //       leave_request_id: id,
    //       approved_by: employee_id,
    //     },
    //     findOne: true,
    //   })

    // // Check if the user has already rejected this leave request
    // const existingRejectionLog =
    //   await this.leaveRequestApprovalLogRepository.getByParams({
    //     where: {
    //       leave_request_id: id,
    //       rejected_by: employee_id,
    //     },
    //     findOne: true,
    //   })

    // if (existingApprovalLog) {
    //   return failureResponse(
    //     code.VALIDATION,
    //     validationMessage("You have already approved this leave request."),
    //   )
    // }

    // if (existingRejectionLog) {
    //   return failureResponse(
    //     code.VALIDATION,
    //     validationMessage("You have already rejected this leave request."),
    //   )
    // }

    // Get company settings to check final_approval_by
    const companySettings: any =
      await this.companySettingsRepository.getByParams({
        where: { company_id },
        findOne: true,
      })

    // Determine if user can perform final approval
    let canPerformFinalApproval = false

    if (companySettings?.final_approval_by) {
      // If final_approval_by is set, check if current user has that role
      canPerformFinalApproval = role_id === companySettings.final_approval_by
    } else {
      // If final_approval_by is not set, find super_admin role from users table
      const superAdminUser: any = await this.authRepository.getByParams({
        where: {
          company_id,
          slug: "super_admin",
        },
        findOne: true,
        select: ["id", "role_id"],
      })

      if (superAdminUser) {
        canPerformFinalApproval = role_id === superAdminUser.role_id
      }
    }

    // Handle REJECTION logic
    if (updateStatusDto.status === LeaveRequestStatus.REJECTED) {
      // Create approval log for rejection
      const approvalLog = new LeaveRequestApprovalLog()
      approvalLog.leave_request_id = id
      approvalLog.action = ApprovalAction.REJECTED
      approvalLog.rejected_by = employee_id
      approvalLog.reject_reason = updateStatusDto.comments || null
      approvalLog.company_id = company_id
      approvalLog.created_by = user_id

      await this.leaveRequestApprovalLogRepository.save(approvalLog)

      // Update leave request status to REJECTED immediately
      leaveRequest.status = LeaveRequestStatus.REJECTED
      leaveRequest.comments = updateStatusDto.comments
      leaveRequest.updated_by = user_id

      await this.leaveRequestRepository.save(leaveRequest)

      // Send rejection email notification
      try {
        await this.sendLeaveStatusNotification(leaveRequest, decoded)
      } catch (emailError) {
        console.error(
          "❌ Error sending leave rejection notification:",
          emailError.message,
        )
      }

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

    // Handle APPROVAL logic
    if (updateStatusDto.status === LeaveRequestStatus.APPROVED) {
      // Create approval log entry (always create for any approval attempt)
      const approvalLog = new LeaveRequestApprovalLog()
      approvalLog.leave_request_id = id
      approvalLog.action = ApprovalAction.APPROVED
      approvalLog.approved_by = employee_id
      approvalLog.company_id = company_id
      approvalLog.created_by = user_id

      await this.leaveRequestApprovalLogRepository.save(approvalLog)

      // Check if user can perform final approval
      if (!canPerformFinalApproval) {
        // User cannot perform final approval - only log the approval attempt
        // Status remains unchanged (PENDING)
        return successResponse(
          code.SUCCESS,
          successMessage(messageKey.data_update, {
            ":data": "Approval logged. Awaiting final approval.",
          }),
        )
      }

      // User can perform final approval - proceed with full approval logic
      // Check leave balance
      const currentYear = new Date().getFullYear()
      const leaveBalance =
        await this.employeeLeaveBalanceRepository.getByParams({
          where: {
            employee_id: leaveRequest.employee_id,
            leave_type_id: leaveRequest.leave_type_id,
            year: currentYear,
            company_id: decoded.company_id,
          },
          findOne: true,
        })

      if (isEmpty(leaveBalance)) {
        return failureResponse(
          code.VALIDATION,
          errorMessage(messageKey.leave_balance_not_found),
        )
      }

      // Calculate actual leave deduction based on leave_day_type
      const leaveDeduction =
        leaveRequest.leave_day_type === LeaveDayType.FIRST_HALF ||
        leaveRequest.leave_day_type === LeaveDayType.SECOND_HALF
          ? 0.5
          : Math.ceil(parseFloat(leaveRequest.duration_days.toString()))

      const availableDays =
        parseFloat(
          (leaveBalance as EmployeeLeaveBalance).available_days.toString(),
        ) || 0

      if (availableDays < leaveDeduction) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.insufficient_leave_balance),
        )
      }

      // Update leave balance with proper deduction
      const currentUsedDays =
        parseFloat(
          (leaveBalance as EmployeeLeaveBalance).used_days.toString(),
        ) || 0
      const totalAllocated =
        parseFloat(
          (leaveBalance as EmployeeLeaveBalance).total_allocated.toString(),
        ) || 0
      const carriedForward =
        parseFloat(
          (
            leaveBalance as EmployeeLeaveBalance
          ).carried_forward_days.toString(),
        ) || 0

      // Calculate new values
      const newUsedDays = currentUsedDays + leaveDeduction
      const newAvailableDays = totalAllocated + carriedForward - newUsedDays

      // Update the balance with proper numeric values
      ;(leaveBalance as EmployeeLeaveBalance).used_days = newUsedDays
      ;(leaveBalance as EmployeeLeaveBalance).available_days = newAvailableDays
      ;(leaveBalance as EmployeeLeaveBalance).updated_by = decoded.user_id

      await this.employeeLeaveBalanceRepository.save(
        leaveBalance as EmployeeLeaveBalance,
      )

      // Set approval details and update status to APPROVED
      leaveRequest.approved_by = decoded.employee_id
      leaveRequest.approved_at = new Date()
      leaveRequest.status = LeaveRequestStatus.APPROVED
      leaveRequest.comments = updateStatusDto.comments
      leaveRequest.updated_by = decoded.user_id

      await this.leaveRequestRepository.save(leaveRequest)

      // Send email notification to employee
      try {
        await this.sendLeaveStatusNotification(leaveRequest, decoded)
      } catch (emailError) {
        console.error(
          "❌ Error sending leave approval notification:",
          emailError.message,
        )
        // Don't fail the main operation if email fails
      }

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

    // Handle other status updates (if any)
    leaveRequest.status = updateStatusDto.status
    leaveRequest.comments = updateStatusDto.comments
    leaveRequest.updated_by = decoded.user_id

    await this.leaveRequestRepository.save(leaveRequest)

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

  async checkLeaveRequestExist(
    employeeId: number,
    fromDate: Date,
    toDate: Date,
    companyId: number,
  ) {
    // Check for overlapping leave requests using raw query for complex date conditions
    const leaveRequest = await this.dataSource.query(
      `
      SELECT * FROM leave_requests 
      WHERE employee_id = $1 
        AND company_id = $2 
        AND deleted_at IS NULL
        AND status != $3
        AND (
          (from_date <= $4 AND to_date >= $5)
          OR (from_date <= $6 AND to_date >= $7)
          OR (from_date >= $8 AND to_date <= $9)
        )
      LIMIT 1
    `,
      [
        employeeId,
        companyId,
        LeaveRequestStatus.REJECTED,
        toDate,
        fromDate, // Check if existing leave overlaps with new leave
        toDate,
        fromDate, // Check if new leave overlaps with existing leave
        fromDate,
        toDate, // Check if new leave is completely within existing leave
      ],
    )

    return leaveRequest && leaveRequest.length > 0
  }

  /**
   * 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.dataSource.query(
        `SELECT * FROM leave_types WHERE id = $1 AND company_id = $2 AND status = 1 AND deleted_at IS NULL AND carry_forward = 1`,
        [leaveTypeId, companyId],
      )

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

      // Calculate unused days from previous year
      const previousBalance = previousYearBalance as EmployeeLeaveBalance
      const unusedDays =
        previousBalance.total_allocated +
        (previousBalance.carried_forward_days || 0) -
        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
    }
  }

  /**
   * Create leave balance if it doesn't exist for the employee and leave type
   */
  private async createLeaveBalanceIfNotExists(
    employeeId: number,
    leaveTypeId: number,
    companyId: number,
    year: number,
    createdBy: number,
  ): Promise<EmployeeLeaveBalance | null> {
    try {
      // Get leave type details to know the annual allowed days
      const leaveType = await this.dataSource.query(
        `SELECT * FROM leave_types WHERE id = $1 AND company_id = $2 AND status = 1 AND deleted_at IS NULL`,
        [leaveTypeId, companyId],
      )

      if (!leaveType || leaveType.length === 0) {
        return null
      }

      // Calculate carry forward days from previous year
      const carryForwardDays = await this.calculateCarryForwardDays(
        employeeId,
        leaveTypeId,
        companyId,
        year,
      )

      // Create new leave balance
      const leaveBalance = new EmployeeLeaveBalance()
      leaveBalance.employee_id = employeeId
      leaveBalance.leave_type_id = leaveTypeId
      leaveBalance.company_id = companyId
      leaveBalance.year = year
      leaveBalance.total_allocated = leaveType[0].annual_allowed_days
      leaveBalance.carried_forward_days = carryForwardDays
      leaveBalance.used_days = 0
      leaveBalance.available_days =
        leaveType[0].annual_allowed_days + carryForwardDays
      leaveBalance.created_by = createdBy

      return await this.employeeLeaveBalanceRepository.save(leaveBalance)
    } catch (error) {
      return null
    }
  }

  /**
   * Send leave status notification email to employee
   */
  private async sendLeaveStatusNotification(leaveRequest: any, decoded: any) {
    try {
      // Get employee details with email
      const employee: any = await this.employeeRepository.getByParams({
        where: { id: leaveRequest.employee_id },
        select: ["id", "first_name", "last_name", "email"],
        findOne: true,
      })

      if (!employee || !employee.email) {
        console.warn(
          `⚠️ No email found for employee ${leaveRequest.employee_id}`,
        )
        return
      }

      // Get leave type details
      const leaveRequestWithDetails: any =
        await this.leaveRequestRepository.getByParams({
          where: { id: leaveRequest.id },
          relations: ["leaveType"],
          select: [
            "id",
            "from_date",
            "to_date",
            "duration_days",
            "status",
            "comments",
          ],
          findOne: true,
        })

      // Get approver name (if available from decoded token)
      let approverName = null
      if (
        decoded.employee_id &&
        leaveRequest.status === LeaveRequestStatus.APPROVED
      ) {
        // Get approver details
        const approver: any = await this.employeeRepository.getByParams({
          where: { id: decoded.employee_id },
          select: ["first_name", "last_name"],
          findOne: true,
        })
        if (approver) {
          approverName = `${approver.first_name} ${approver.last_name}`
        }
      }

      // Format dates
      const startDate = moment(leaveRequestWithDetails.from_date).format(
        "MMMM DD, YYYY",
      )
      const endDate = moment(leaveRequestWithDetails.to_date).format(
        "MMMM DD, YYYY",
      )

      // Determine email subject
      const isApproved = leaveRequest.status === LeaveRequestStatus.APPROVED
      const emailSubject = isApproved
        ? mailSubject.leaveRequestApproved
        : mailSubject.leaveRequestRejected

      // Generate email content
      const employeeName = `${employee.first_name} ${employee.last_name}`
      const leaveTypeName = leaveRequestWithDetails.leaveType?.name || "Leave"

      const emailHtml = leaveStatusNotificationEmail(
        employeeName,
        leaveTypeName,
        startDate,
        endDate,
        leaveRequestWithDetails.duration_days,
        leaveRequest.status,
        leaveRequest.comments,
        approverName,
      )

      // Send email notification
      await sendEmailNotification(employee.email, emailHtml, emailSubject)

      // Send push notification and save to database
      const notificationTitle = isApproved
        ? notificationTitles.LEAVE_REQUEST_APPROVED
        : notificationTitles.LEAVE_REQUEST_REJECTED

      const notificationMessage = isApproved
        ? notificationMessages.LEAVE_REQUEST_APPROVED(
            leaveTypeName,
            startDate,
            endDate,
          )
        : notificationMessages.LEAVE_REQUEST_REJECTED(
            leaveTypeName,
            startDate,
            endDate,
            leaveRequest.comments,
          )

      await this.notificationService.sendAndSaveNotification({
        title: notificationTitle,
        description: notificationMessage,
        company_id: decoded.company_id,
        employee_id: leaveRequest.employee_id,
        type: "LEAVE_REQUEST",
        data: {
          leave_request_id: leaveRequest.id,
          status: leaveRequest.status,
        },
      })

      console.log(
        `✅ Leave status notification sent to ${employee.email} for ${leaveRequest.status} leave request`,
      )
    } catch (error) {
      console.error(
        `❌ Error sending leave status notification:`,
        error.message,
      )
      throw error
    }
  }

  /**
   * Notify all users with leave approval permissions about a new leave request
   */
  private async notifyLeaveApprovers(leaveRequest: any, companyId: number) {
    try {
      console.log(
        `📧 Sending leave approval notifications for leave request ${leaveRequest.id}`,
      )

      // Step 1: Find the 'approve_leaves' permission ID
      const approveLeavePermission: any =
        await this.permissionRepository.getByParams({
          where: {
            permission_key: PERMISSIONS.LEAVES.APPROVE_LEAVES,
            status: 1,
          },
          select: ["id"],
          findOne: true,
        })

      if (!approveLeavePermission) {
        console.warn("⚠️ No 'approve_leaves' permission found in system")
        return
      }

      // Step 2: Find all roles that have this permission for this company
      const rolePermissions: any =
        await this.rolePermissionRepository.getByParams({
          where: {
            permission_id: approveLeavePermission.id,
            company_id: companyId,
            status: 1,
          },
          select: ["role_id"],
        })

      const rolePermissionsArray = rolePermissions.data || rolePermissions || []

      if (!rolePermissionsArray || rolePermissionsArray.length === 0) {
        console.warn(
          `⚠️ No roles found with 'approve_leaves' permission for company ${companyId}`,
        )
        return
      }

      const roleIds = rolePermissionsArray.map((rp: any) => rp.role_id)

      // Step 3: Find all active users with these roles in the same company
      const approvers: any = await this.authRepository.getByParams({
        where: {
          company_id: companyId,
          status: 1,
          role_id: roleIds.length === 1 ? roleIds[0] : undefined,
        },
        select: ["id", "employee_id", "first_name", "last_name", "email"],
      })

      // Handle multiple role IDs if needed
      let approversArray = approvers.data || approvers || []

      if (roleIds.length > 1) {
        // If multiple roles, we need to use a more complex query
        const approversQuery = `
          SELECT DISTINCT a.id, a.employee_id, a.first_name, a.last_name, a.email
          FROM users a
          WHERE a.company_id = $1 
            AND a.status = 1 
            AND a.deleted_at IS NULL
            AND a.role_id = ANY($2)
        `
        approversArray = await this.dataSource.query(approversQuery, [
          companyId,
          roleIds,
        ])
      }

      if (!approversArray || approversArray.length === 0) {
        console.warn(
          `⚠️ No active users found with approval permissions for company ${companyId}`,
        )
        return
      }

      // Filter out the employee who applied for leave from approvers list
      // This prevents self-approval notifications
      const originalApproversCount = approversArray.length
      approversArray = approversArray.filter(
        (approver: any) => approver.employee_id !== leaveRequest.employee_id,
      )

      if (originalApproversCount > approversArray.length) {
        console.log(
          `ℹ️ Filtered out leave requester (employee_id: ${leaveRequest.employee_id}) from approvers list`,
        )
      }

      if (approversArray.length === 0) {
        console.warn(
          `⚠️ No other approvers found (excluding the leave requester) for company ${companyId}`,
        )
        // Still send employee and admin notifications, just skip approver notifications
      }

      // Get employee and leave type details for the email
      const [employeeResult, leaveTypeResult] = await Promise.all([
        this.employeeRepository.getByParams({
          where: { id: leaveRequest.employee_id },
          select: ["first_name", "last_name"],
          findOne: true,
        }),
        this.leaveTypeRepository.getByParams({
          where: { id: leaveRequest.leave_type_id },
          select: ["name"],
          findOne: true,
        }),
      ])

      const employee = employeeResult as any
      const leaveType = leaveTypeResult as any
      const employeeName =
        `${employee.first_name} ${employee.last_name || ""}`.trim()
      const leaveTypeName = leaveType?.name || "Leave"
      const startDate = moment(leaveRequest.from_date).format("MMMM DD, YYYY")
      const endDate = moment(leaveRequest.to_date).format("MMMM DD, YYYY")

      // Step 4: Send email to each approver (excluding the leave requester)
      let emailsSent = 0
      let emailErrors = 0

      for (const approver of approversArray) {
        try {
          const approverName =
            `${approver.first_name} ${approver.last_name || ""}`.trim()

          const emailHtml = leaveApprovalRequiredEmail(
            approverName,
            employeeName,
            leaveTypeName,
            startDate,
            endDate,
            leaveRequest.duration_days,
            leaveRequest.reason,
            `${EV["FRONT_URL"]}/leave-report`,
          )

          await sendEmailNotification(
            approver.email,
            emailHtml,
            mailSubject.leaveApprovalRequired,
          )

          emailsSent++
          console.log(
            `✅ Leave approval notification sent to ${approver.email} (${approverName})`,
          )
        } catch (error) {
          emailErrors++
          console.error(
            `❌ Failed to send notification to ${approver.email}:`,
            error.message,
          )
        }
      }

      // Send push notifications - Create 2 separate notifications
      try {
        // 1. Employee Notification - for the employee who requested leave
        const employeeNotificationMessage = `Your leave request for ${leaveTypeName} (${leaveRequest.duration_days} days) has been submitted and is pending approval.`

        await this.notificationService.sendAndSaveNotification({
          title: "Leave Request Submitted",
          description: employeeNotificationMessage,
          company_id: companyId,
          employee_id: leaveRequest.employee_id,
          type: "LEAVE_REQUEST",
          data: {
            leave_request_id: leaveRequest.id,
            employee_name: employeeName,
            leave_type: leaveTypeName,
            status: "pending",
          },
        })

        // 2. Admin Notification - for super admin (without employee_id)
        const adminNotificationMessage = `New leave request from ${employeeName} for ${leaveTypeName} (${leaveRequest.duration_days} days) requires approval.`

        await this.notificationService.sendAndSaveNotification({
          title: "Leave Approval Required",
          description: adminNotificationMessage,
          company_id: companyId,
          // No employee_id for admin notification
          type: "LEAVE_REQUEST",
          data: {
            leave_request_id: leaveRequest.id,
            employee_name: employeeName,
            leave_type: leaveTypeName,
            duration_days: leaveRequest.duration_days,
          },
        })

        // 3. Send notification to each approver individually using their employee_id (excluding the leave requester)
        if (approversArray.length > 0) {
          const approverNotificationMessage =
            notificationMessages.LEAVE_REQUEST_SUBMITTED(
              employeeName,
              leaveTypeName,
              leaveRequest.duration_days,
            )

          for (const approver of approversArray) {
            // Only send notification if the user has an associated employee_id
            if (approver.employee_id) {
              await this.notificationService.sendAndSaveNotification({
                title: notificationTitles.LEAVE_APPROVAL_REQUIRED,
                description: approverNotificationMessage,
                company_id: companyId,
                employee_id: approver.employee_id,
                type: "LEAVE_REQUEST",
                data: {
                  leave_request_id: leaveRequest.id,
                  employee_name: employeeName,
                  leave_type: leaveTypeName,
                },
              })
            } else {
              console.warn(
                `⚠️ User ${approver.id} (${approver.email}) has no associated employee_id, skipping notification`,
              )
            }
          }
        } else {
          console.log(
            `ℹ️ No approvers to notify (leave requester excluded from approvers list)`,
          )
        }

        const validApprovers = approversArray.filter(
          (approver: any) => approver.employee_id,
        )
        console.log(
          `📱 Push notifications sent: Employee (1), Admin (1), Approvers (${validApprovers.length})`,
        )
      } catch (notificationError) {
        console.error(
          "❌ Error sending push notifications:",
          notificationError.message,
        )
      }

      console.log(
        `🎉 Leave approval notifications completed. Emails sent: ${emailsSent}, Errors: ${emailErrors}`,
      )
    } catch (error) {
      console.error(
        "❌ Error in leave approval notification process:",
        error.message,
      )
      throw error
    }
  }
}