import { Injectable } from "@nestjs/common"
import { InjectRepository } from "@nestjs/typeorm"
import { Repository } from "typeorm"
import { ReportsRepository } from "./repositories/reports.repository"
import { CompanyProfileRepository } from "../company/repositories/company-profile.repository"
import { CompanySettingsRepository } from "../company/repositories/company-settings.repository"
import { TimeTracking } from "../time-tracking/entities/time-tracking.entity"
import { ClockInRecord } from "../time-tracking/entities/clock-in-record.entity"
import { Project } from "../projects/entities/project.entity"
import { ProjectEmployee } from "../projects/entities/project-employee.entity"
import { LeaveRequest } from "../leave-requests/entities/leave-request.entity"
import { LeaveType } from "../leave-types/entities/leave-type.entity"
import { Employee } from "../employees/entities/employee.entity"
import { PartyPayment } from "../party-types/entities/party-payment.entity"
import { PartyPaymentHistory } from "../party-types/entities/party-payment-history.entity"
import {
  errorMessage,
  successMessage,
  validationMessage,
  convertLocalToUtc,
} from "../../utils/helpers"
import {
  failureResponse,
  successResponse,
} from "../../common/response/response"
import { code } from "../../common/response/response.code"
import { messageKey } from "../../constants/message-keys"
import { verifyJwtToken } from "../../utils/jwt"
import {
  ProjectReportFiltersDto,
  DateRangeType,
} from "./dto/project-report-filters.dto"
import { TimeEntriesReportFiltersDto } from "./dto/time-entries-report-filters.dto"
import {
  ProjectReportResponseDto,
  ChartDataDto,
  ProjectReportSummaryDto,
  ProjectReportChartsDto,
} from "./dto/project-report-response.dto"
import {
  TimeEntriesReportResponseDto,
  TimeEntryDto,
  TimeEntriesReportSummaryDto,
} from "./dto/time-entries-report-response.dto"
import {
  ProjectCostingFiltersDto,
  TimeFrameType,
} from "./dto/project-costing-filters.dto"
import { ProjectAnalyticsFiltersDto } from "./dto/project-analytics-filters.dto"
import { EmployeeProductivityFiltersDto } from "./dto/employee-productivity-filters.dto"
import {
  ProjectCostingResponseDto,
  ProjectCostingSummaryDto,
  CostTrendDataDto,
  ProjectCostDataDto,
} from "./dto/project-costing-response.dto"
import {
  ProjectCostingListResponseDto,
  ProjectCostingListSummaryDto,
  ProjectCostingListEntryDto,
  YearRangeDto,
} from "./dto/project-costing-list-response.dto"
import { TimesheetReportFiltersDto } from "./dto/timesheet-report-filters.dto"
import {
  TimesheetReportResponseDto,
  TimesheetReportSummaryDto,
} from "./dto/timesheet-report-response.dto"
import {
  ClockInReportFiltersDto,
  ClockInStatusType,
} from "./dto/clock-in-report-filters.dto"
import { ClockInReportResponseDto } from "./dto/clock-in-report-response.dto"
import { ProjectReportExportDto } from "./dto/export-filters.dto"
import moment from "moment"
import ExcelJS from "exceljs"
import { ClockInReportExportDto } from "./dto/clock-in-report.dto"
import { RecentProjectDto, RecentLeaveDto } from "./dto/dashboard-response.dto"
import { LeaveAnalyticsFiltersDto } from "./dto/leave-analytics-filters.dto"
import { LeaveAnalyticsResponseDto } from "./dto/leave-analytics-response.dto"
import { ProjectManagementFiltersDto } from "./dto/project-management-filters.dto"
import {
  ProjectManagementResponseDto,
  ProjectManagementMetaDto,
  ProjectManagerDto,
} from "./dto/project-management-response.dto"
import { Client } from "../clients/entities/client.entity"
import * as XLSX from "xlsx"
import { ActivityType } from "../activity-types/entities/activity-type.entity"
import { DataSource } from "typeorm"

// Interface for timesheet bulk upload response
interface TimesheetBulkUploadError {
  row_number: number
  employee_email: string
  project_name: string
  error_message: string
}

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

@Injectable()
export class ReportsService {
  constructor(
    private readonly reportsRepository: ReportsRepository,
    private readonly companyProfileRepository: CompanyProfileRepository,
    private readonly companySettingsRepository: CompanySettingsRepository,
    @InjectRepository(TimeTracking)
    private readonly timeTrackingEntityRepository: Repository<TimeTracking>,
    @InjectRepository(ClockInRecord)
    private readonly clockInRecordRepository: Repository<ClockInRecord>,
    @InjectRepository(Project)
    private readonly projectEntityRepository: Repository<Project>,
    @InjectRepository(ProjectEmployee)
    private readonly projectEmployeeEntityRepository: Repository<ProjectEmployee>,
    @InjectRepository(LeaveRequest)
    private readonly leaveRequestEntityRepository: Repository<LeaveRequest>,
    @InjectRepository(LeaveType)
    private readonly leaveTypeEntityRepository: Repository<LeaveType>,
    @InjectRepository(Employee)
    private readonly employeeEntityRepository: Repository<Employee>,
    @InjectRepository(Client)
    private readonly clientEntityRepository: Repository<Client>,
    @InjectRepository(ActivityType)
    private readonly activityTypeEntityRepository: Repository<ActivityType>,
    @InjectRepository(PartyPayment)
    private readonly partyPaymentEntityRepository: Repository<PartyPayment>,
    @InjectRepository(PartyPaymentHistory)
    private readonly partyPaymentHistoryEntityRepository: Repository<PartyPaymentHistory>,
    @InjectRepository(TimeTracking)
    private readonly timeTrackingRepository: Repository<TimeTracking>,
    private readonly dataSource: DataSource,
  ) {}

  /**
   * Get project analytics data for dashboard charts
   */
  async getProjectAnalytics(
    filters: ProjectAnalyticsFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      // Set default date range to current month if not provided
      const currentDate = new Date()
      const currentMonth = currentDate.getMonth() + 1 // getMonth() returns 0-11
      const currentYear = currentDate.getFullYear()

      const fromMonth = filters.from_month || currentMonth
      const fromYear = filters.from_year || currentYear
      const toMonth = filters.to_month || currentMonth
      const toYear = filters.to_year || currentYear

      // Validate month range (1-12)
      if (fromMonth < 1 || fromMonth > 12 || toMonth < 1 || toMonth > 12) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.field_type_validation_error, {
            ":field": "month",
            ":type": "1-12",
          }),
        )
      }

      // Create date range
      const startDate = new Date(fromYear, fromMonth - 1, 1)
      const endDate = new Date(toYear, toMonth, 0) // last day of month

      // Validate date range
      if (endDate < startDate) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.field_type_validation_error, {
            ":field": "date_range",
            ":type": "end date must be after start date",
          }),
        )
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        startDate,
        endDate,
      }

      // Get all analytics data in parallel
      const [
        topProjectsByEmployees,
        topProjectsByTime,
        topProjectsNearThreshold,
        yearRange,
      ] = await Promise.all([
        this.getTopProjectsByEmployees(reportFilters),
        this.getTopProjectsByTime(reportFilters),
        this.getTopProjectsNearThreshold(reportFilters),
        this.getAvailableYearRange(reportFilters),
      ])

      // Build year range
      const year_range: YearRangeDto = {
        min_year: yearRange.min_year,
        max_year: yearRange.max_year,
      }

      const response = {
        top_projects_by_employees: topProjectsByEmployees,
        top_projects_by_time: topProjectsByTime,
        top_projects_near_threshold: topProjectsNearThreshold,
        year_range,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Project Analytics",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get employee dashboard data with stats, recent projects, and recent leaves
   */
  async getDashboard(token: string): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      // Get all dashboard data in parallel
      const [
        stats,
        recentProjects,
        recentLeaves,
        notClockedInEmployees,
        todaysTimeTracking,
      ] = await Promise.all([
        this.getDashboardStats(decoded.company_id),
        this.getRecentProjects(decoded.employee_id, decoded.company_id),
        this.getRecentLeaves(decoded.employee_id, decoded.company_id),
        this.getNotClockedInEmployees(decoded.company_id),
        this.findTodaysTimeTracking(decoded.employee_id, decoded.company_id),
      ])

      const response: any = {
        stats,
        recent_projects: recentProjects,
        recent_leaves: recentLeaves,
        not_clocked_in_employees: notClockedInEmployees,
        todays_time_tracking: todaysTimeTracking,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, { ":data": "Dashboard" }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get activity percentage data for dashboard pie chart
   */
  async getActivityPercentage(
    filters: ProjectAnalyticsFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      const companyId = decoded.company_id

      // Build date range conditions based on filters
      let dateConditions = ""
      const queryParams: any = { companyId }

      if (filters.from_month && filters.from_year) {
        const fromDate = new Date(filters.from_year, filters.from_month - 1, 1)
        dateConditions += " AND tt.start_time >= :fromDate"
        queryParams.fromDate = fromDate
      }

      if (filters.to_month && filters.to_year) {
        const toDate = new Date(
          filters.to_year,
          filters.to_month,
          0,
          23,
          59,
          59,
        )
        dateConditions += " AND tt.start_time <= :toDate"
        queryParams.toDate = toDate
      }

      // Get productive time tracking entries count
      const productiveTimeTrackingQuery = this.timeTrackingEntityRepository
        .createQueryBuilder("tt")
        .leftJoin("tt.activityType", "at")
        .where("tt.company_id = :companyId", queryParams)
        .andWhere("tt.deleted_at IS NULL")
        .andWhere("at.deleted_at IS NULL")
        .andWhere("at.status = :status", { status: 1 })
        .andWhere("at.is_productive = :isProductive", { isProductive: 1 })
        .select("COUNT(tt.id)", "count")

      // Get non-productive time tracking entries count
      const nonProductiveTimeTrackingQuery = this.timeTrackingEntityRepository
        .createQueryBuilder("tt")
        .leftJoin("tt.activityType", "at")
        .where("tt.company_id = :companyId", queryParams)
        .andWhere("tt.deleted_at IS NULL")
        .andWhere("at.deleted_at IS NULL")
        .andWhere("at.status = :status", { status: 1 })
        .andWhere("at.is_productive = :isProductive", { isProductive: 0 })
        .select("COUNT(tt.id)", "count")

      // Get total time tracking entries count (with valid activity types)
      const totalTimeTrackingQuery = this.timeTrackingEntityRepository
        .createQueryBuilder("tt")
        .leftJoin("tt.activityType", "at")
        .where("tt.company_id = :companyId", queryParams)
        .andWhere("tt.deleted_at IS NULL")
        .andWhere("at.deleted_at IS NULL")
        .andWhere("at.status = :status", { status: 1 })
        .select("COUNT(tt.id)", "count")

      // Add date conditions if provided
      if (dateConditions) {
        productiveTimeTrackingQuery.andWhere(dateConditions.substring(5)) // Remove " AND "
        nonProductiveTimeTrackingQuery.andWhere(dateConditions.substring(5))
        totalTimeTrackingQuery.andWhere(dateConditions.substring(5))
      }

      const [
        productiveTimeTrackingResult,
        nonProductiveTimeTrackingResult,
        totalTimeTrackingResult,
      ] = await Promise.all([
        productiveTimeTrackingQuery.getRawOne(),
        nonProductiveTimeTrackingQuery.getRawOne(),
        totalTimeTrackingQuery.getRawOne(),
      ])

      // Extract counts from query results
      const productiveTimeTrackingCount = Number(
        productiveTimeTrackingResult?.count || 0,
      )
      const nonProductiveTimeTrackingCount = Number(
        nonProductiveTimeTrackingResult?.count || 0,
      )
      const totalTimeTrackingCount = Number(totalTimeTrackingResult?.count || 0)

      // Calculate productivity percentages based on actual time tracking entries
      const productivePercentage =
        totalTimeTrackingCount > 0
          ? Math.round(
              (productiveTimeTrackingCount / totalTimeTrackingCount) * 100,
            )
          : 0

      const nonProductivePercentage =
        totalTimeTrackingCount > 0
          ? Math.round(
              (nonProductiveTimeTrackingCount / totalTimeTrackingCount) * 100,
            )
          : 0

      const response = {
        productive_percentage: productivePercentage,
        non_productive_percentage: nonProductivePercentage,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Activity Percentage",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get employee-wise productive vs non-productive report
   */
  async getEmployeeProductivity(
    filters: EmployeeProductivityFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      const companyId = decoded.company_id

      // Build date range conditions based on filters
      const queryParams: any = { companyId }

      if (filters.from_month && filters.from_year) {
        const fromDate = new Date(filters.from_year, filters.from_month - 1, 1)
        queryParams.fromDate = fromDate
      }

      if (filters.to_month && filters.to_year) {
        const toDate = new Date(
          filters.to_year,
          filters.to_month,
          0,
          23,
          59,
          59,
        )
        queryParams.toDate = toDate
      }

      // If no date filter is provided, default to today's records
      if (
        !filters.from_month &&
        !filters.from_year &&
        !filters.to_month &&
        !filters.to_year
      ) {
        const today = new Date()
        const startOfDay = new Date(
          today.getFullYear(),
          today.getMonth(),
          today.getDate(),
          0,
          0,
          0,
        )
        const endOfDay = new Date(
          today.getFullYear(),
          today.getMonth(),
          today.getDate(),
          23,
          59,
          59,
        )

        queryParams.fromDate = startOfDay
        queryParams.toDate = endOfDay
      }

      // Query to get employee-wise productive and non-productive time using QueryBuilder
      const queryBuilder = this.timeTrackingEntityRepository
        .createQueryBuilder("tt")
        .innerJoin("tt.employee", "e")
        .innerJoin("tt.activityType", "at")
        .select([
          "e.first_name as first_name",
          "e.last_name as last_name",
          "CONCAT(e.first_name, ' ', e.last_name) as employee_name",
          "SUM(CASE WHEN at.is_productive = 1 THEN COALESCE(tt.total_minutes, 0) ELSE 0 END) as productive_minutes",
          "SUM(CASE WHEN at.is_productive = 0 THEN COALESCE(tt.total_minutes, 0) ELSE 0 END) as non_productive_minutes",
          "SUM(COALESCE(tt.total_minutes, 0)) as total_minutes",
        ])
        .where("tt.company_id = :companyId", { companyId })
        .andWhere("tt.deleted_at IS NULL")
        .andWhere("e.deleted_at IS NULL")
        .andWhere("at.deleted_at IS NULL")
        .andWhere("at.status = :status", { status: 1 })
        .andWhere("tt.total_minutes IS NOT NULL")
        .andWhere("tt.total_minutes > 0")
        .groupBy("e.id, e.first_name, e.last_name")
        .having("SUM(COALESCE(tt.total_minutes, 0)) > 0")
        .orderBy("employee_name", "ASC")

      // Add date conditions if they exist
      if (queryParams.fromDate) {
        queryBuilder.andWhere("tt.start_time >= :fromDate", {
          fromDate: queryParams.fromDate,
        })
      }

      if (queryParams.toDate) {
        queryBuilder.andWhere("tt.start_time <= :toDate", {
          toDate: queryParams.toDate,
        })
      }

      // Add employee filter if provided
      if (filters.employee_id) {
        queryBuilder.andWhere("e.id = :employeeId", {
          employeeId: filters.employee_id,
        })
      }

      const rawResults = await queryBuilder.getRawMany()

      // Calculate percentages for each employee
      const employeeProductivityData = rawResults.map((row: any) => {
        const productiveHours = row.productive_minutes
          ? Math.round((row.productive_minutes / 60) * 10) / 10
          : 0
        const nonProductiveHours = row.non_productive_minutes
          ? Math.round((row.non_productive_minutes / 60) * 10) / 10
          : 0
        const totalHours = row.total_minutes
          ? Math.round((row.total_minutes / 60) * 10) / 10
          : 0

        return {
          employee_name: row.employee_name,
          productive_hours: productiveHours,
          non_productive_hours: nonProductiveHours,
          total_hours: totalHours,
        }
      })

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Dashboard list",
        }),
        employeeProductivityData as any,
      )
    } catch (error) {
      console.error("Error in getEmployeeProductivity:", error)
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get project report with charts and summary data
   */
  async getProjectReport(
    filters: ProjectReportFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_MONTH,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        activity_type_id: filters.activity_type_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
      }

      // Get raw data, summary, and party costs
      const [rawData, summaryData, partyCost] = await Promise.all([
        this.getProjectReportData(reportFilters),
        this.getSummaryData(reportFilters),
        this.getPartyCostForPeriod(
          dateRange.start_date,
          dateRange.end_date,
          reportFilters,
        ),
      ])

      // Process data for charts
      const charts = await this.processProjectReportCharts(
        rawData,
        dateRange.start_date,
        dateRange.end_date,
        reportFilters,
      )

      // Build summary (including party costs)
      const timeTrackingCost = parseFloat(summaryData.total_cost || "0")
      const totalCostWithParties = timeTrackingCost + partyCost

      const summary: ProjectReportSummaryDto = {
        total_hours: summaryData.total_minutes
          ? Math.round((summaryData.total_minutes / 60) * 10) / 10
          : 0,
        total_cost: Math.round(totalCostWithParties * 100) / 100,
        total_clients: parseInt(summaryData.total_clients || "0"),
        total_projects: parseInt(summaryData.total_projects || "0"),
        total_employees: parseInt(summaryData.total_employees || "0"),
      }

      const response: ProjectReportResponseDto = {
        summary,
        charts,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, { ":data": "Project Report" }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get time entries report with pagination
   */
  async getTimeEntriesReport(
    filters: TimeEntriesReportFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      const page = filters.page || 1
      const limit = filters.limit || 10
      const skip = (page - 1) * limit

      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_MONTH,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        activity_type_id: filters.activity_type_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        status: filters.status || undefined,
      }

      // Get paginated data, summary, and party costs
      const [{ data, totalCount }, summaryData] = await Promise.all([
        this.getTimeEntriesReportData(reportFilters, skip, limit),
        this.getSummaryData(reportFilters),
      ])

      // Process entries
      const entries: TimeEntryDto[] = data.map((entry) => ({
        id: entry.id,
        employee_name: `${entry.employee?.first_name || ""} ${
          entry.employee?.last_name || ""
        }`.trim(),
        project_name: entry.project?.name || "N/A",
        client_name: entry.project?.client?.name || "N/A",
        activity_type: entry.activityType?.name || "N/A",
        start_time: entry.start_time,
        end_time: entry.end_time,
        total_hours: entry.total_minutes
          ? Math.round((entry.total_minutes / 60) * 10) / 10
          : 0,
        hourly_rate: entry.total_minutes
          ? Math.round((entry.cost / (entry.total_minutes / 60)) * 100) / 100
          : 0,
        total_cost: parseFloat(entry.cost?.toString() || "0"),
        duration: this.formatDuration(entry.total_minutes),
      }))

      // Build summary (including party costs)
      const timeTrackingCost = parseFloat(summaryData.total_cost || "0")

      const summary: TimeEntriesReportSummaryDto = {
        total_entries: parseInt(summaryData.total_entries || "0"),
        total_hours: summaryData.total_minutes
          ? Math.round((summaryData.total_minutes / 60) * 10) / 10
          : 0,
        total_cost: Math.round(timeTrackingCost * 100) / 100,
      }

      const response: TimeEntriesReportResponseDto = {
        summary,
        entries,
        total_count: totalCount,
        current_page: page,
        per_page: limit,
        total_pages: Math.ceil(totalCount / limit),
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Time Entries Report",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get project costing report with summary and trend data
   */
  async getProjectCostingReport(
    filters: ProjectCostingFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      // Set default year to current year if not provided
      const year = filters.year || new Date().getFullYear()
      const timeFrame = filters.time_frame || TimeFrameType.MONTHLY

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        year,
      }

      // Get cost trend and project cost data
      const [costTrendData, projectCostData, totalCost] = await Promise.all([
        this.getCostTrendData(reportFilters, timeFrame),
        this.getProjectCostData(reportFilters),
        this.getTotalCostForYear(reportFilters),
      ])

      // Build summary
      const summary: ProjectCostingSummaryDto = {
        total_cost: totalCost,
      }

      const response: ProjectCostingResponseDto = {
        summary,
        cost_trend: costTrendData,
        cost_by_project: projectCostData,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Project Costing Report",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get project costing list with monthly breakdown
   */
  async getProjectCostingList(
    filters: ProjectCostingFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      // Set default year to current year if not provided
      const year = filters.year || new Date().getFullYear()

      // Parse date range
      // const dateRange = this.parseDateRange(
      //   filters.date_range || DateRangeType.THIS_MONTH,
      //   filters.start_date,
      //   filters.end_date,
      // )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        // start_date: dateRange.start_date,
        // end_date: dateRange.end_date,
        year,
      }

      // Get project costing list data and year range in parallel
      const [projectsData, yearRange] = await Promise.all([
        this.getProjectCostingListData(reportFilters),
        this.getAvailableYearRange(reportFilters),
      ])

      // Build year range
      const year_range: YearRangeDto = {
        min_year: yearRange.min_year,
        max_year: yearRange.max_year,
      }

      // Calculate summary
      const totalProjects = projectsData.length
      const totalCost = projectsData.reduce(
        (sum, project) => sum + project.total_cost,
        0,
      )
      const averageCostPerProject =
        totalProjects > 0 ? totalCost / totalProjects : 0

      // Build summary
      const summary: ProjectCostingListSummaryDto = {
        total_projects: totalProjects,
        total_cost: Math.round(totalCost * 100) / 100,
        average_cost_per_project: Math.round(averageCostPerProject * 100) / 100,
      }

      const response: ProjectCostingListResponseDto = {
        summary,
        projects: projectsData,
        year_range,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Project Costing List",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get available year range from time tracking data
   */
  private async getAvailableYearRange(
    filters: any,
  ): Promise<{ min_year: number; max_year: number }> {
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .select([
        "MIN(EXTRACT(YEAR FROM tt.start_time)) as min_year",
        "MAX(EXTRACT(YEAR FROM tt.start_time)) as max_year",
      ])
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time IS NOT NULL")

    // Apply company filter
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    // Apply employee filter if specified
    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    const result = await query.getRawOne()

    // If no data found, return current year as both min and max
    const currentYear = new Date().getFullYear()
    return {
      min_year: result?.min_year ? parseInt(result.min_year) : currentYear,
      max_year: result?.max_year ? parseInt(result.max_year) : currentYear,
    }
  }

  /**
   * Get timesheet report with summary and paginated entries
   */
  async getTimesheetReport(
    filters: TimesheetReportFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      const page = filters.page || 1
      const limit = filters.limit || 10
      const skip = (page - 1) * limit

      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_WEEK,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        employee_id: filters.employee_id,
        department_id: filters.department_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        search: filters.search,
        status: filters.status || undefined,
      }

      // Get paginated data and summary
      const [{ data, totalCount }, summaryData] = await Promise.all([
        this.getTimesheetEntriesData(reportFilters, skip, limit),
        this.getTimesheetSummaryData(reportFilters),
      ])

      // Process entries
      const entries: any = data.map((entry) => ({
        id: entry.id,
        date: moment(entry.start_time).format("DD/MM/YYYY"),
        employee_id: entry.employee?.id || 0,
        employee_name: `${entry.employee?.first_name || ""} ${
          entry.employee?.last_name || ""
        }`.trim(),
        designation: entry.employee?.role?.name || "N/A",
        department_name: entry.employee?.department?.name || "N/A",
        activity_type: entry.activityType?.name || "N/A",
        is_productive: entry.activityType?.is_productive === 1,
        project_name: entry.project?.name || "N/A",
        clock_in: entry.start_time,
        clock_out: entry.end_time ? entry.end_time : null,
        duration: this.formatDuration(entry.total_minutes),
        description: entry.description || "N/A",
        status: entry.end_time ? "Completed" : "Ongoing",
        cost: entry.cost || 0,
      }))

      // Build summary
      const summary: TimesheetReportSummaryDto = {
        total_records: parseInt(summaryData.total_records || "0"),
        total_hours_all_records: this.formatDuration(
          summaryData.total_minutes || 0,
        ),
        employees_present: parseInt(summaryData.employees_present || "0"),
        total_employees: parseInt(summaryData.total_employees || "0"),
        average_hours_per_employee_per_day: this.formatDuration(
          summaryData.average_minutes || 0,
        ),
      }

      const response: TimesheetReportResponseDto = {
        summary,
        entries,
        total_count: totalCount,
        current_page: page,
        per_page: limit,
        total_pages: Math.ceil(totalCount / limit),
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Timesheet Report",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get clock-in report with paginated entries
   */
  async getClockInReport(
    filters: ClockInReportFiltersDto,
    token: string,
  ): Promise<any> {
    const decoded = verifyJwtToken(token)

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

    try {
      const page = filters.page || 1
      const limit = filters.limit || 10
      const skip = (page - 1) * limit

      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.TODAY,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        employee_id: filters.employee_id,
        department_id: filters.department_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        search: filters.search,
        status: filters.status || ClockInStatusType.ALL,
      }

      // Get paginated data
      const { data, totalCount } = await this.getClockInEntriesData(
        reportFilters,
        skip,
        limit,
      )

      // Process entries with real-time duration calculation
      const entries: any = data.map((entry) => {
        const isOngoing = !entry.clock_out
        let duration = "00:00"

        if (isOngoing) {
          // Calculate duration from clock_in to current time
          const now = moment()
          const clockInTime = moment(entry.clock_in)
          const diffMinutes = now.diff(clockInTime, "minutes")
          duration = this.formatDuration(diffMinutes)
        } else {
          // Use actual duration from database
          duration = this.formatDuration(entry.duration || 0)
        }

        return {
          id: entry.id,
          date: moment(entry.clock_in).format("DD/MM/YYYY"),
          employee_id: entry.employee?.id || 0,
          employee_name: `${entry.employee?.first_name || ""} ${
            entry.employee?.last_name || ""
          }`.trim(),
          designation: entry.employee?.role?.name || "N/A",
          department_name: entry.employee?.department?.name || "N/A",
          clock_in: entry.clock_in,
          clock_out: entry.clock_out ?? null,
          duration: duration,
          status: isOngoing ? "Ongoing" : "Completed",
        }
      })

      const response: ClockInReportResponseDto = {
        entries,
        total_count: totalCount,
        current_page: page,
        per_page: limit,
        total_pages: Math.ceil(totalCount / limit),
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Clock-in Report",
        }),
        response as any,
      )
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Parse date range based on type
   */
  private parseDateRange(
    dateRangeType: DateRangeType,
    customStartDate?: string,
    customEndDate?: string,
  ): { start_date: Date; end_date: Date } {
    if (customStartDate && customEndDate) {
      return {
        start_date: moment(customStartDate, "DD-MM-YYYY")
          .startOf("day")
          .toDate(),
        end_date: moment(customEndDate, "DD-MM-YYYY").endOf("day").toDate(),
      }
    }

    // 👉 2. Else fallback to range types
    const now = moment()

    switch (dateRangeType) {
      case DateRangeType.TODAY:
        return {
          start_date: now.startOf("day").toDate(),
          end_date: now.endOf("day").toDate(),
        }

      case DateRangeType.YESTERDAY:
        return {
          start_date: now.clone().subtract(1, "day").startOf("day").toDate(),
          end_date: now.clone().subtract(1, "day").endOf("day").toDate(),
        }

      case DateRangeType.THIS_WEEK:
        return {
          start_date: now.startOf("week").toDate(),
          end_date: now.endOf("week").toDate(),
        }

      case DateRangeType.LAST_WEEK:
        return {
          start_date: now.clone().subtract(1, "week").startOf("week").toDate(),
          end_date: now.clone().subtract(1, "week").endOf("week").toDate(),
        }

      case DateRangeType.THIS_MONTH:
        return {
          start_date: now.startOf("month").toDate(),
          end_date: now.endOf("month").toDate(),
        }

      case DateRangeType.LAST_MONTH:
        const lastMonth = now.clone().subtract(1, "month")
        return {
          start_date: lastMonth.startOf("month").toDate(),
          end_date: lastMonth.endOf("month").toDate(),
        }

      case DateRangeType.LAST_3_MONTHS:
        return {
          start_date: now
            .clone()
            .subtract(3, "months")
            .startOf("month")
            .toDate(),
          end_date: now.endOf("month").toDate(),
        }

      case DateRangeType.LAST_6_MONTHS:
        return {
          start_date: now
            .clone()
            .subtract(6, "months")
            .startOf("month")
            .toDate(),
          end_date: now.endOf("month").toDate(),
        }

      case DateRangeType.LAST_YEAR:
        return {
          start_date: now.clone().subtract(1, "year").startOf("year").toDate(),
          end_date: now.endOf("year").toDate(),
        }

      default:
        return {
          start_date: now.startOf("month").toDate(),
          end_date: now.endOf("month").toDate(),
        }
    }
  }

  /**
   * Process raw data into chart format
   */
  private async processProjectReportCharts(
    rawData: any[],
    startDate: Date,
    endDate: Date,
    filters: any,
  ): Promise<ProjectReportChartsDto> {
    // Hours by Project
    const projectHours = {}
    const projectCosts = {}
    const activityTypeHours = {}
    const employeeHours = {}
    const dailyHours = {}

    rawData.forEach((entry) => {
      const hours = entry.total_minutes ? entry.total_minutes / 60 : 0
      const cost = parseFloat(entry.cost || "0")
      const projectId = entry.project?.id
      const projectName = entry.project?.name || `Project ${projectId}`
      const activityTypeName = entry.activityType?.name || "Unknown"
      const employeeName =
        `${entry.employee?.first_name || ""} ${
          entry.employee?.last_name || ""
        }`.trim() || "Unknown"
      const date = moment(entry.start_time).format("YYYY-MM-DD")

      // Aggregate by project
      if (projectId) {
        if (!projectHours[projectId]) {
          projectHours[projectId] = { name: projectName, hours: 0 }
          projectCosts[projectId] = { name: projectName, cost: 0 }
        }
        projectHours[projectId].hours += hours
        projectCosts[projectId].cost += cost
      }

      // Aggregate by activity type
      if (!activityTypeHours[activityTypeName]) {
        activityTypeHours[activityTypeName] = {
          name: activityTypeName,
          hours: 0,
        }
      }
      activityTypeHours[activityTypeName].hours += hours

      // Aggregate by employee
      if (!employeeHours[employeeName]) {
        employeeHours[employeeName] = { name: employeeName, hours: 0 }
      }
      employeeHours[employeeName].hours += hours

      // Aggregate by date
      if (!dailyHours[date]) {
        dailyHours[date] = { date, hours: 0 }
      }
      dailyHours[date].hours += hours
    })

    // Convert to chart format
    const hours_by_project: ChartDataDto[] = Object.values(projectHours).map(
      (item: any) => ({
        name: item.name,
        hours: Math.round(item.hours * 10) / 10,
      }),
    )

    const cost_by_project: ChartDataDto[] = Object.values(projectCosts).map(
      (item: any) => ({
        name: item.name,
        cost: Math.round(item.cost * 100) / 100,
      }),
    )

    const hours_by_activity_type: ChartDataDto[] = Object.values(
      activityTypeHours,
    ).map((item: any) => ({
      name: item.name,
      hours: Math.round(item.hours * 10) / 10,
    }))

    const hours_by_employee: ChartDataDto[] = Object.values(employeeHours).map(
      (item: any) => ({
        name: item.name,
        hours: Math.round(item.hours * 10) / 10,
      }),
    )

    const daily_hours_trend: ChartDataDto[] = Object.values(dailyHours)
      .map((item: any) => ({
        name: item.date,
        date: item.date,
        hours: Math.round(item.hours * 10) / 10,
      }))
      .sort((a, b) => a.date.localeCompare(b.date))

    // Get cost breakdown by contributor
    const cost_by_contributor = await this.getCostBreakdownByContributor(
      startDate,
      endDate,
      filters,
    )

    return {
      hours_by_project,
      cost_by_project,
      hours_by_activity_type,
      hours_by_employee,
      daily_hours_trend,
      cost_by_contributor,
    }
  }

  /**
   * Get project report data with aggregations using query builder
   */
  private async getProjectReportData(filters: any) {
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("tt.employee", "employee")
      .leftJoinAndSelect("tt.activityType", "activity_type")
      .leftJoinAndSelect("project.client", "client")
      .select([
        "tt.id",
        "tt.start_time",
        "tt.end_time",
        "tt.total_minutes",
        "tt.cost",
        "project.id",
        "project.name",
        "employee.id",
        "employee.first_name",
        "employee.last_name",
        "activity_type.id",
        "activity_type.name",
        "client.id",
        "client.name",
      ])
      .where("tt.deleted_at IS NULL")

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.activity_type_id) {
      query.andWhere("activity_type.id = :activity_type_id", {
        activity_type_id: filters.activity_type_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      query.andWhere(
        "tt.start_time >= :start_date AND tt.start_time <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    return query.getMany()
  }

  /**
   * Get time entries report data with pagination using query builder
   */
  private async getTimeEntriesReportData(
    filters: any,
    skip: number,
    take: number,
  ) {
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .leftJoinAndSelect("tt.employee", "employee")
      .leftJoinAndSelect("employee.department", "department")
      .leftJoinAndSelect("tt.activityType", "activity_type")
      .where("tt.deleted_at IS NULL")
      .select([
        "tt.id",
        "tt.start_time",
        "tt.end_time",
        "tt.total_minutes",
        "tt.cost",
        "tt.description",
        "project.name",
        "employee.first_name",
        "employee.last_name",
        "department.name",
        "activity_type.name",
        "client.name",
      ])

    // Apply filters (same as project report)
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.activity_type_id) {
      query.andWhere("activity_type.id = :activity_type_id", {
        activity_type_id: filters.activity_type_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      query.andWhere(
        "tt.start_time >= :start_date AND tt.start_time <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    if (filters.status) {
      if (filters.status === "completed") {
        query.andWhere("tt.end_time IS NOT NULL")
      } else if (filters.status === "ongoing") {
        query.andWhere("tt.end_time IS NULL")
      } else if (filters.status === "all") {
        query.andWhere("tt.end_time IS NOT NULL")
      }
    }

    if (filters.status) {
      if (filters.status === "completed") {
        query.andWhere("tt.end_time IS NOT NULL")
      } else if (filters.status === "ongoing") {
        query.andWhere("tt.end_time IS NULL")
      }
    }

    query.orderBy("tt.start_time", "DESC")

    // Get total count
    const totalCount = await query.getCount()

    // Get paginated data
    const data = await query.skip(skip).take(take).getMany()

    return { data, totalCount }
  }

  /**
   * Get aggregated summary data using query builder
   */
  private async getSummaryData(filters: any) {
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("tt.employee", "employee")
      .leftJoinAndSelect("tt.activityType", "activity_type")
      .leftJoinAndSelect("project.client", "client")
      .select([
        "COUNT(DISTINCT tt.id) as total_entries",
        "COUNT(DISTINCT project.id) as total_projects",
        "COUNT(DISTINCT client.id) as total_clients",
        "COUNT(DISTINCT employee.id) as total_employees",
        "SUM(tt.total_minutes) as total_minutes",
        "SUM(tt.cost) as total_cost",
      ])
      .where("tt.deleted_at IS NULL")

    // Apply same filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.activity_type_id) {
      query.andWhere("activity_type.id = :activity_type_id", {
        activity_type_id: filters.activity_type_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      query.andWhere(
        "tt.start_time >= :start_date AND tt.start_time <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    if (filters.status) {
      if (filters.status === "completed") {
        query.andWhere("tt.end_time IS NOT NULL")
      } else if (filters.status === "ongoing") {
        query.andWhere("tt.end_time IS NULL")
      }
    }

    return query.getRawOne()
  }

  /**
   * Get cost trend data based on time frame using query builder
   */
  private async getCostTrendData(
    filters: any,
    timeFrame: TimeFrameType,
  ): Promise<CostTrendDataDto[]> {
    const year = filters.year
    const startDate = moment(`${year}-01-01`).startOf("year")
    const endDate = moment(`${year}-12-31`).endOf("year")

    let dateFormat: string
    let groupByFormat: string
    const periods: moment.Moment[] = []

    // Determine format and periods based on time frame
    switch (timeFrame) {
      case TimeFrameType.MONTHLY:
        dateFormat = "MMM YYYY"
        groupByFormat = "YYYY-MM"
        for (let i = 0; i < 12; i++) {
          periods.push(moment(startDate).add(i, "month"))
        }
        break
      case TimeFrameType.QUARTERLY:
        dateFormat = "[Q]Q YYYY"
        groupByFormat = "YYYY-Q"
        for (let i = 0; i < 4; i++) {
          periods.push(moment(startDate).add(i * 3, "month"))
        }
        break
      case TimeFrameType.YEARLY:
        dateFormat = "YYYY"
        groupByFormat = "YYYY"
        periods.push(moment(startDate))
        break
    }

    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .select([
        `TO_CHAR(tt.start_time, '${groupByFormat === "YYYY-Q" ? 'YYYY-"Q"Q' : "YYYY-MM"}') as period_key`,
        "SUM(tt.cost) as total_cost",
      ])
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", {
        startDate: startDate.toDate(),
      })
      .andWhere("tt.start_time <= :endDate", { endDate: endDate.toDate() })
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    query.groupBy("period_key").orderBy("period_key", "ASC")

    const rawData = await query.getRawMany()

    // Create a map for quick lookup
    const dataMap = new Map()
    rawData.forEach((item) => {
      dataMap.set(item.period_key, parseFloat(item.total_cost || "0"))
    })

    // Generate complete period data with zeros for missing periods
    return periods.map((period) => {
      let periodKey: string
      let periodLabel: string
      let periodStart: string
      let periodEnd: string

      switch (timeFrame) {
        case TimeFrameType.MONTHLY:
          periodKey = period.format("YYYY-MM")
          periodLabel = period.format(dateFormat)
          periodStart = period.startOf("month").format("YYYY-MM-DD")
          periodEnd = period.endOf("month").format("YYYY-MM-DD")
          break
        case TimeFrameType.QUARTERLY:
          const quarter = Math.floor(period.month() / 3) + 1
          periodKey = `${period.year()}-Q${quarter}`
          periodLabel = `Q${quarter} ${period.year()}`
          periodStart = period.startOf("quarter").format("YYYY-MM-DD")
          periodEnd = period.endOf("quarter").format("YYYY-MM-DD")
          break
        case TimeFrameType.YEARLY:
          periodKey = period.format("YYYY")
          periodLabel = period.format(dateFormat)
          periodStart = period.startOf("year").format("YYYY-MM-DD")
          periodEnd = period.endOf("year").format("YYYY-MM-DD")
          break
      }

      return {
        period: periodLabel,
        total_cost: Math.round((dataMap.get(periodKey) || 0) * 100) / 100,
        start_date: periodStart,
        end_date: periodEnd,
      }
    })
  }

  /**
   * Get cost by project data using query builder
   */
  private async getProjectCostData(
    filters: any,
  ): Promise<ProjectCostDataDto[]> {
    const year = filters.year
    const startDate = moment(`${year}-01-01`).startOf("year").toDate()
    const endDate = moment(`${year}-12-31`).endOf("year").toDate()

    // Get time tracking costs by project
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .select([
        "project.id as project_id",
        "project.name as project_name",
        "SUM(tt.cost) as total_cost",
      ])
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries
      .andWhere("project.id IS NOT NULL") // Ensure project exists

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    query.groupBy("project.id, project.name").orderBy("total_cost", "DESC")

    const timeTrackingData = await query.getRawMany()

    // Get party costs for each project and combine with time tracking costs
    const projectCostData = await Promise.all(
      timeTrackingData.map(async (item) => {
        const projectId = parseInt(item.project_id)
        const timeTrackingCost = parseFloat(item.total_cost || "0")

        // Get party costs for this specific project
        const projectFilters = { ...filters, project_id: projectId }
        const partyCost = await this.getPartyCostForPeriod(
          startDate,
          endDate,
          projectFilters,
        )

        const totalCost = timeTrackingCost + partyCost

        return {
          project_id: projectId,
          project_name: item.project_name || "Unknown Project",
          total_cost: Math.round(totalCost * 100) / 100,
        }
      }),
    )

    // Sort by total cost descending and filter out zero costs
    return projectCostData
      .filter((item) => item.total_cost > 0)
      .sort((a, b) => b.total_cost - a.total_cost)
  }

  /**
   * Get total cost for the year
   */
  private async getTotalCostForYear(filters: any): Promise<number> {
    const year = filters.year
    const startDate = moment(`${year}-01-01`).startOf("year").toDate()
    const endDate = moment(`${year}-12-31`).endOf("year").toDate()

    // Get time tracking costs
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .select("SUM(tt.cost) as total_cost")
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    const timeTrackingResult = await query.getRawOne()
    const timeTrackingCost = parseFloat(timeTrackingResult?.total_cost || "0")

    // Get party costs for the same period and filters
    const partyCost = await this.getPartyCostForPeriod(
      startDate,
      endDate,
      filters,
    )

    return Math.round((timeTrackingCost + partyCost) * 100) / 100
  }

  /**
   * Get party costs for a specific period with filters
   */
  private async getPartyCostForPeriod(
    startDate: Date,
    endDate: Date,
    filters: any,
  ): Promise<number> {
    // Build base query for projects that match filters
    let projectIds: number[] = []

    const projectQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .select("p.id")
      .where("p.deleted_at IS NULL")

    // Apply filters to get relevant project IDs
    if (filters.company_id) {
      projectQuery.andWhere("p.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      projectQuery.andWhere("p.client_id = :client_id", {
        client_id: filters.client_id,
      })
    }

    if (filters.project_id) {
      projectQuery.andWhere("p.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    const projects = await projectQuery.getRawMany()

    projectIds = projects.map((p) => p.p_id)

    if (projectIds.length === 0) {
      return 0
    }

    let totalPartyCost = 0

    // Get FIXED payments (created within the date range)
    const fixedPayments = await this.partyPaymentEntityRepository
      .createQueryBuilder("pp")
      .where("pp.project_id IN (:...projectIds)", { projectIds })
      .andWhere("pp.payment_type = :paymentType", { paymentType: "FIXED" })
      .andWhere("pp.deleted_at IS NULL")
      .andWhere("pp.created_at >= :startDate", { startDate })
      .andWhere("pp.created_at <= :endDate", { endDate })
      .select("SUM(pp.amount) as total_amount")
      .getRawOne()

    totalPartyCost += parseFloat(fixedPayments?.total_amount || "0")

    // Get MONTHLY payment histories (billing_month within the date range)
    const monthlyPayments = await this.partyPaymentHistoryEntityRepository
      .createQueryBuilder("pph")
      .where("pph.project_id IN (:...projectIds)", { projectIds })
      .andWhere("pph.deleted_at IS NULL")
      .andWhere("pph.billing_month >= :startDate", { startDate })
      .andWhere("pph.billing_month <= :endDate", { endDate })
      .select("SUM(pph.amount) as total_amount")
      .getRawOne()

    totalPartyCost += parseFloat(monthlyPayments?.total_amount || "0")

    return totalPartyCost
  }

  /**
   * Get cost breakdown by contributor for chart data
   */
  private async getCostBreakdownByContributor(
    startDate: Date,
    endDate: Date,
    filters: any,
  ): Promise<any[]> {
    // Build base query for projects that match filters
    let projectIds: number[] = []

    const projectQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .select(["p.id", "p.name"])
      .where("p.deleted_at IS NULL")

    // Apply filters to get relevant project IDs
    if (filters.company_id) {
      projectQuery.andWhere("p.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      projectQuery.andWhere("p.client_id = :client_id", {
        client_id: filters.client_id,
      })
    }

    if (filters.project_id) {
      projectQuery.andWhere("p.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    const projects = await projectQuery.getRawMany()
    projectIds = projects.map((p) => p.p_id)

    if (projectIds.length === 0) {
      return []
    }

    // Create a map of projects for easy lookup
    const projectMap = {}
    projects.forEach((p) => {
      projectMap[p.p_id] = p.p_name || `Project ${p.p_id}`
    })

    // Get employee costs (time tracking) by project
    const employeeCosts = await this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .leftJoin("tt.project", "project")
      .leftJoin("tt.employee", "employee")
      .select([
        "tt.project_id as project_id",
        "SUM(COALESCE(tt.cost, 0)) as total_cost",
      ])
      .where("tt.project_id IN (:...projectIds)", { projectIds })
      .andWhere("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .groupBy("tt.project_id")
      .getRawMany()

    // Get party costs breakdown by project and party type
    const partyCosts = await this.partyPaymentEntityRepository
      .createQueryBuilder("pp")
      .leftJoin("pp.project", "project")
      .select([
        "pp.project_id as project_id",
        "pp.party_type as party_type",
        "pp.payment_type as payment_type",
        "SUM(COALESCE(pp.amount, 0)) as total_amount",
      ])
      .where("pp.project_id IN (:...projectIds)", { projectIds })
      .andWhere("pp.deleted_at IS NULL")
      .andWhere("pp.payment_type = :paymentType", { paymentType: "FIXED" })
      .andWhere("pp.created_at >= :startDate", { startDate })
      .andWhere("pp.created_at <= :endDate", { endDate })
      .groupBy("pp.project_id, pp.party_type, pp.payment_type")
      .getRawMany()

    // Get monthly payment histories by project and party type
    const monthlyPartyCosts = await this.partyPaymentHistoryEntityRepository
      .createQueryBuilder("pph")
      .leftJoin("pph.project", "project")
      .leftJoin("pph.partyPayment", "pp")
      .select([
        "pph.project_id as project_id",
        "pph.party_type as party_type",
        "SUM(COALESCE(pph.amount, 0)) as total_amount",
      ])
      .where("pph.project_id IN (:...projectIds)", { projectIds })
      .andWhere("pph.deleted_at IS NULL")
      .andWhere("pph.billing_month >= :startDate", { startDate })
      .andWhere("pph.billing_month <= :endDate", { endDate })
      .groupBy("pph.project_id, pph.party_type")
      .getRawMany()

    // Build the cost breakdown data structure
    const costBreakdown = {}

    // Initialize all projects with zero costs
    projectIds.forEach((projectId) => {
      costBreakdown[projectId] = {
        project_name: projectMap[projectId],
        employee_cost: 0,
        contractor_fixed_cost: 0,
        contractor_monthly_cost: 0,
        vendor_fixed_cost: 0,
        vendor_monthly_cost: 0,
        consultant_fixed_cost: 0,
        consultant_monthly_cost: 0,
        total_cost: 0,
      }
    })

    // Add employee costs
    employeeCosts.forEach((item) => {
      const projectId = item.project_id
      if (costBreakdown[projectId]) {
        costBreakdown[projectId].employee_cost = parseFloat(
          item.total_cost || "0",
        )
      }
    })

    // Add fixed party costs
    partyCosts.forEach((item) => {
      const projectId = item.project_id
      const partyType = item.party_type?.toLowerCase()
      const amount = parseFloat(item.total_amount || "0")

      if (costBreakdown[projectId]) {
        switch (partyType) {
          case "contractor":
            costBreakdown[projectId].contractor_fixed_cost += amount
            break
          case "vendor":
            costBreakdown[projectId].vendor_fixed_cost += amount
            break
          case "consultant":
            costBreakdown[projectId].consultant_fixed_cost += amount
            break
        }
      }
    })

    // Add monthly party costs
    monthlyPartyCosts.forEach((item) => {
      const projectId = item.project_id
      const partyType = item.party_type?.toLowerCase()
      const amount = parseFloat(item.total_amount || "0")

      if (costBreakdown[projectId]) {
        switch (partyType) {
          case "contractor":
            costBreakdown[projectId].contractor_monthly_cost += amount
            break
          case "vendor":
            costBreakdown[projectId].vendor_monthly_cost += amount
            break
          case "consultant":
            costBreakdown[projectId].consultant_monthly_cost += amount
            break
        }
      }
    })

    // Calculate total costs and format the final result
    const result = Object.values(costBreakdown).map((item: any) => {
      item.total_cost =
        Math.round(
          (item.employee_cost +
            item.contractor_fixed_cost +
            item.contractor_monthly_cost +
            item.vendor_fixed_cost +
            item.vendor_monthly_cost +
            item.consultant_fixed_cost +
            item.consultant_monthly_cost) *
            100,
        ) / 100

      // Round all individual costs
      item.employee_cost = Math.round(item.employee_cost * 100) / 100
      item.contractor_fixed_cost =
        Math.round(item.contractor_fixed_cost * 100) / 100
      item.contractor_monthly_cost =
        Math.round(item.contractor_monthly_cost * 100) / 100
      item.vendor_fixed_cost = Math.round(item.vendor_fixed_cost * 100) / 100
      item.vendor_monthly_cost =
        Math.round(item.vendor_monthly_cost * 100) / 100
      item.consultant_fixed_cost =
        Math.round(item.consultant_fixed_cost * 100) / 100
      item.consultant_monthly_cost =
        Math.round(item.consultant_monthly_cost * 100) / 100

      return item
    })

    // Filter out projects with zero total cost and sort by total cost descending
    return result
      .filter((item) => item.total_cost > 0)
      .sort((a, b) => b.total_cost - a.total_cost)
  }

  /**
   * Get timesheet entries data with pagination using query builder
   */
  private async getTimesheetEntriesData(
    filters: any,
    skip: number,
    take: number,
  ) {
    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.employee", "employee")
      .leftJoinAndSelect("tt.activityType", "activityType")
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .leftJoinAndSelect("employee.department", "department")
      .leftJoinAndSelect("employee.role", "role")
      .select([
        "tt.id",
        "tt.start_time",
        "tt.end_time",
        "tt.total_minutes",
        "tt.description",
        "employee.id",
        "employee.first_name",
        "employee.last_name",
        "activityType.name",
        "activityType.is_productive",
        "department.name",
        "role.name",
        "tt.cost",
        "project.name",
      ])
      .where("tt.deleted_at IS NULL")

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.department_id) {
      query.andWhere("department.id = :department_id", {
        department_id: filters.department_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", {
        client_id: filters.client_id,
      })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      query.andWhere(
        "tt.start_time >= :start_date AND tt.start_time <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    if (filters.status) {
      if (filters.status === "completed") {
        query.andWhere("tt.end_time IS NOT NULL")
      } else if (filters.status === "ongoing") {
        query.andWhere("tt.end_time IS NULL")
      }
    }

    // Search functionality
    if (filters.search) {
      query.andWhere(
        "(LOWER(employee.first_name) LIKE LOWER(:search) OR LOWER(employee.last_name) LIKE LOWER(:search) OR LOWER(role.name) LIKE LOWER(:search))",
        { search: `%${filters.search}%` },
      )
    }

    query.orderBy("tt.start_time", "DESC")

    // Get total count
    const totalCount = await query.getCount()

    // Get paginated data
    const data = await query.skip(skip).take(take).getMany()

    return { data, totalCount }
  }

  /**
   * Get timesheet summary data using query builder
   */
  private async getTimesheetSummaryData(filters: any) {
    // Get total records and employees present
    const recordsQuery = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.employee", "employee")
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .leftJoinAndSelect("employee.department", "department")
      .leftJoinAndSelect("employee.role", "role")
      .select([
        "COUNT(tt.id) as total_records",
        "COALESCE(SUM(tt.total_minutes), 0) as total_minutes",
        "COUNT(DISTINCT employee.id) as employees_present",
        "AVG(tt.total_minutes) as average_minutes",
      ])
      .where("tt.deleted_at IS NULL")

    // Apply same filters as main query
    if (filters.company_id) {
      recordsQuery.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.employee_id) {
      recordsQuery.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.department_id) {
      recordsQuery.andWhere("department.id = :department_id", {
        department_id: filters.department_id,
      })
    }

    if (filters.client_id) {
      recordsQuery.andWhere("client.id = :client_id", {
        client_id: filters.client_id,
      })
    }

    if (filters.project_id) {
      recordsQuery.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      recordsQuery.andWhere(
        "tt.start_time >= :start_date AND tt.start_time <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    if (filters.status) {
      if (filters.status === "completed") {
        recordsQuery.andWhere("tt.end_time IS NOT NULL")
      } else if (filters.status === "ongoing") {
        recordsQuery.andWhere("tt.end_time IS NULL")
      }
    }

    if (filters.search) {
      recordsQuery.andWhere(
        "(LOWER(employee.first_name) LIKE LOWER(:search) OR LOWER(employee.last_name) LIKE LOWER(:search) OR LOWER(role.name) LIKE LOWER(:search))",
        { search: `%${filters.search}%` },
      )
    }

    // Get total employees in company
    const totalEmployeesQuery = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.employee", "employee")
      .select("COUNT(DISTINCT employee.id) as total_employees")
      .where("tt.company_id = :company_id", { company_id: filters.company_id })
      .andWhere("tt.deleted_at IS NULL")

    const [recordsResult, totalEmployeesResult] = await Promise.all([
      recordsQuery.getRawOne(),
      totalEmployeesQuery.getRawOne(),
    ])

    return {
      total_records: recordsResult?.total_records || "0",
      total_minutes: parseFloat(recordsResult?.total_minutes || "0"),
      employees_present: recordsResult?.employees_present || "0",
      total_employees: totalEmployeesResult?.total_employees || "0",
      average_minutes: parseFloat(recordsResult?.average_minutes || "0"),
    }
  }

  /**
   * Get clock-in entries data with pagination using query builder
   */
  private async getClockInEntriesData(
    filters: any,
    skip: number,
    take: number,
  ) {
    const query = this.clockInRecordRepository
      .createQueryBuilder("cir")
      .leftJoinAndSelect("cir.employee", "employee")
      .leftJoinAndSelect("employee.department", "department")
      .leftJoinAndSelect("employee.role", "role")
      .select([
        "cir.id",
        "cir.clock_in",
        "cir.clock_out",
        "cir.duration",
        "employee.id",
        "employee.first_name",
        "employee.last_name",
        "department.name",
        "role.name",
      ])
      .where("cir.deleted_at IS NULL")

    // Apply filters
    if (filters.company_id) {
      query.andWhere("cir.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("employee.id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.department_id) {
      query.andWhere("department.id = :department_id", {
        department_id: filters.department_id,
      })
    }

    if (filters.start_date && filters.end_date) {
      query.andWhere(
        "cir.clock_in >= :start_date AND cir.clock_in <= :end_date",
        {
          start_date: filters.start_date,
          end_date: filters.end_date,
        },
      )
    }

    // Status filter
    if (filters.status === ClockInStatusType.COMPLETED) {
      query.andWhere("cir.clock_out IS NOT NULL")
    } else if (filters.status === ClockInStatusType.ONGOING) {
      query.andWhere("cir.clock_out IS NULL")
    }
    // For 'all' status, no additional filter needed

    // Search functionality
    if (filters.search) {
      query.andWhere(
        "(LOWER(employee.first_name) LIKE LOWER(:search) OR LOWER(employee.last_name) LIKE LOWER(:search) OR LOWER(role.name) LIKE LOWER(:search) OR LOWER(department.name) LIKE LOWER(:search))",
        { search: `%${filters.search}%` },
      )
    }

    query.orderBy("cir.clock_in", "DESC")

    // Get total count
    const totalCount = await query.getCount()

    // Get paginated data
    const data = await query.skip(skip).take(take).getMany()

    return { data, totalCount }
  }

  /**
   * Get project costing list data with monthly breakdown using query builder
   */
  private async getProjectCostingListData(
    filters: any,
  ): Promise<ProjectCostingListEntryDto[]> {
    // Use date range if provided, otherwise fall back to year
    let startDate: Date
    let endDate: Date

    if (filters.start_date && filters.end_date) {
      startDate = filters.start_date
      endDate = filters.end_date
    } else {
      const year = filters.year
      startDate = moment(`${year}-01-01`).startOf("year").toDate()
      endDate = moment(`${year}-12-31`).endOf("year").toDate()
    }

    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .select([
        "project.id as project_id",
        "project.name as project_name",
        "client.name as client_name",
        "EXTRACT(MONTH FROM tt.start_time) as month",
        "SUM(tt.cost) as monthly_cost",
      ])
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries
      .andWhere("project.id IS NOT NULL") // Ensure project exists

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    query
      .groupBy(
        "project.id, project.name, client.name, EXTRACT(MONTH FROM tt.start_time)",
      )
      .orderBy("project.name", "ASC")
      .addOrderBy("month", "ASC")

    const rawData = await query.getRawMany()

    // Group data by project and organize monthly costs
    const projectsMap = new Map()

    rawData.forEach((item) => {
      const projectId = parseInt(item.project_id)
      const month = parseInt(item.month)
      const cost = parseFloat(item.monthly_cost || "0")

      if (!projectsMap.has(projectId)) {
        projectsMap.set(projectId, {
          project_id: projectId,
          project_name: item.project_name || "Unknown Project",
          client_name: item.client_name || "Unknown Client",
          jan_cost: 0,
          feb_cost: 0,
          mar_cost: 0,
          apr_cost: 0,
          may_cost: 0,
          jun_cost: 0,
          jul_cost: 0,
          aug_cost: 0,
          sep_cost: 0,
          oct_cost: 0,
          nov_cost: 0,
          dec_cost: 0,
          total_cost: 0,
        })
      }

      const project = projectsMap.get(projectId)

      // Map month number to property name
      const monthMapping = {
        1: "jan_cost",
        2: "feb_cost",
        3: "mar_cost",
        4: "apr_cost",
        5: "may_cost",
        6: "jun_cost",
        7: "jul_cost",
        8: "aug_cost",
        9: "sep_cost",
        10: "oct_cost",
        11: "nov_cost",
        12: "dec_cost",
      }

      if (monthMapping[month]) {
        project[monthMapping[month]] = Math.round(cost * 100) / 100
        project.total_cost += cost
      }
    })

    // Convert map to array and round total costs
    return Array.from(projectsMap.values()).map((project) => ({
      ...project,
      total_cost: Math.round(project.total_cost * 100) / 100,
    }))
  }

  /**
   * Get project costing list data by time frame (quarterly/yearly)
   */
  private async getProjectCostingListDataByTimeFrame(
    filters: any,
    timeFrame: TimeFrameType,
  ): Promise<any[]> {
    // Use date range if provided, otherwise fall back to year
    let startDate: Date
    let endDate: Date

    if (filters.start_date && filters.end_date) {
      startDate = filters.start_date
      endDate = filters.end_date
    } else {
      const year = filters.year
      startDate = moment(`${year}-01-01`).startOf("year").toDate()
      endDate = moment(`${year}-12-31`).endOf("year").toDate()
    }

    let groupByFormat: string
    let selectFields: string[]

    if (timeFrame === TimeFrameType.QUARTERLY) {
      groupByFormat = 'YYYY-"Q"Q'
      selectFields = [
        "project.id as project_id",
        "project.name as project_name",
        "client.name as client_name",
        `TO_CHAR(tt.start_time, '${groupByFormat}') as quarter_key`,
        "SUM(tt.cost) as quarter_cost",
      ]
    } else {
      // Yearly
      selectFields = [
        "project.id as project_id",
        "project.name as project_name",
        "client.name as client_name",
        "SUM(tt.cost) as total_cost",
      ]
    }

    const query = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .withDeleted()
      .leftJoinAndSelect("tt.project", "project")
      .leftJoinAndSelect("project.client", "client")
      .select(selectFields)
      .where("tt.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL")
      .andWhere("project.id IS NOT NULL")

    // Apply filters
    if (filters.company_id) {
      query.andWhere("tt.company_id = :company_id", {
        company_id: filters.company_id,
      })
    }

    if (filters.client_id) {
      query.andWhere("client.id = :client_id", { client_id: filters.client_id })
    }

    if (filters.project_id) {
      query.andWhere("project.id = :project_id", {
        project_id: filters.project_id,
      })
    }

    if (filters.employee_id) {
      query.andWhere("tt.employee_id = :employee_id", {
        employee_id: filters.employee_id,
      })
    }

    if (timeFrame === TimeFrameType.QUARTERLY) {
      query
        .groupBy("project.id, project.name, client.name, quarter_key")
        .orderBy("project.name", "ASC")
        .addOrderBy("quarter_key", "ASC")
    } else {
      query
        .groupBy("project.id, project.name, client.name")
        .orderBy("project.name", "ASC")
    }

    const rawData = await query.getRawMany()

    if (timeFrame === TimeFrameType.QUARTERLY) {
      // Group quarterly data by project
      const projectsMap = new Map()

      rawData.forEach((item) => {
        const projectId = parseInt(item.project_id)
        const quarterKey = item.quarter_key // e.g., "2025-Q1"
        const cost = parseFloat(item.quarter_cost || "0")

        if (!projectsMap.has(projectId)) {
          projectsMap.set(projectId, {
            project_id: projectId,
            project_name: item.project_name || "Unknown Project",
            client_name: item.client_name || "Unknown Client",
            q1_cost: 0,
            q2_cost: 0,
            q3_cost: 0,
            q4_cost: 0,
            total_cost: 0,
          })
        }

        const project = projectsMap.get(projectId)

        // Map quarter to property
        if (quarterKey.includes("Q1")) {
          project.q1_cost = Math.round(cost * 100) / 100
        } else if (quarterKey.includes("Q2")) {
          project.q2_cost = Math.round(cost * 100) / 100
        } else if (quarterKey.includes("Q3")) {
          project.q3_cost = Math.round(cost * 100) / 100
        } else if (quarterKey.includes("Q4")) {
          project.q4_cost = Math.round(cost * 100) / 100
        }

        project.total_cost += cost
      })

      // Convert map to array and round total costs
      return Array.from(projectsMap.values()).map((project) => ({
        ...project,
        total_cost: Math.round(project.total_cost * 100) / 100,
      }))
    } else {
      // Yearly data - already aggregated
      return rawData.map((item) => ({
        project_id: parseInt(item.project_id),
        project_name: item.project_name || "Unknown Project",
        client_name: item.client_name || "Unknown Client",
        total_cost: Math.round(parseFloat(item.total_cost || "0") * 100) / 100,
      }))
    }
  }

  /**
   * Format duration from minutes to HH:MM format
   */
  private formatDuration(minutes: number): string {
    if (!minutes || minutes <= 0) return "00:00"

    const hours = Math.floor(minutes / 60)
    const mins = Math.round(minutes % 60)

    return `${hours.toString().padStart(2, "0")}:${mins
      .toString()
      .padStart(2, "0")}`
  }

  /**
   * Get top 5 projects by number of employees assigned
   */
  private async getTopProjectsByEmployees(filters: any): Promise<any[]> {
    const { startDate, endDate } = filters

    const query = this.projectEmployeeEntityRepository
      .createQueryBuilder("pe")
      .leftJoin("pe.project", "p")
      .leftJoin(
        "time_tracking",
        "tt",
        "tt.project_id = p.id AND tt.employee_id = pe.employee_id AND tt.deleted_at IS NULL",
      )
      .select([
        "p.id as project_id",
        "p.name as project_name",
        "COUNT(DISTINCT pe.employee_id) as employee_count",
      ])
      .where("p.company_id = :companyId", { companyId: filters.company_id })
      .andWhere("p.deleted_at IS NULL")
      .andWhere("pe.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL")
      .groupBy("p.id, p.name")
      .orderBy("employee_count", "DESC")
      .limit(5)

    const rawData = await query.getRawMany()

    // Calculate total employees for percentage calculation
    const totalEmployees = rawData.reduce(
      (sum, item) => sum + parseInt(item.employee_count),
      0,
    )

    return rawData.map((item) => ({
      name: item.project_name || "Unknown Project",
      employees: parseInt(item.employee_count),
      percentage:
        totalEmployees > 0
          ? Math.round((parseInt(item.employee_count) / totalEmployees) * 100)
          : 0,
    }))
  }

  /**
   * Get top 5 projects by time spent (monthly, quarterly, and yearly)
   */
  private async getTopProjectsByTime(filters: any): Promise<any> {
    const { startDate, endDate } = filters

    // Base query for the specified time frame
    const baseQuery = this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .leftJoin("tt.project", "p")
      .select([
        "p.id as project_id",
        "p.name as project_name",
        "SUM(tt.total_minutes) as total_minutes",
      ])
      .where("tt.company_id = :companyId", { companyId: filters.company_id })
      .andWhere("tt.deleted_at IS NULL")
      .andWhere("p.deleted_at IS NULL")
      .andWhere("tt.start_time >= :startDate", { startDate })
      .andWhere("tt.start_time <= :endDate", { endDate })
      .andWhere("tt.end_time IS NOT NULL")
      .groupBy("p.id, p.name")
      .orderBy("total_minutes", "DESC")
      .limit(5)

    const timeData = await baseQuery.getRawMany()

    // Process data for the date range
    const totalMinutes = timeData.reduce(
      (sum, item) => sum + parseInt(item.total_minutes || "0"),
      0,
    )

    const processedData = timeData.map((item) => {
      const minutes = parseInt(item.total_minutes || "0")
      return {
        name: item.project_name || "Unknown Project",
        hours: Math.round((minutes / 60) * 100) / 100,
        percentage:
          totalMinutes > 0 ? Math.round((minutes / totalMinutes) * 100) : 0,
      }
    })

    // Return consistent structure for backward compatibility
    return {
      monthly: processedData,
      quarterly: processedData,
      yearly: processedData,
    }
  }

  /**
   * Get top 5 projects near budget threshold
   */
  private async getTopProjectsNearThreshold(filters: any): Promise<any[]> {
    // Get projects near threshold with their spent amounts
    const projectsQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .leftJoin("p.client", "c")
      .leftJoin(
        "time_tracking",
        "tt",
        "tt.project_id = p.id AND tt.deleted_at IS NULL AND tt.end_time IS NOT NULL",
      )
      .select([
        "p.id as project_id",
        "p.name as project_name",
        "c.name as client_name",
        "p.budget as budget",
        "p.remaining_budget as remaining_budget",
        "p.threshold_percentage as threshold_percentage",
        "COALESCE(SUM(tt.cost), 0) as total_spent",
      ])
      .where("p.company_id = :companyId", { companyId: filters.company_id })
      .andWhere("p.deleted_at IS NULL")
      .andWhere("p.budget > 0")
      .andWhere(
        `
        (p.budget - p.remaining_budget) >= 
        (p.budget * (COALESCE(NULLIF(p.threshold_percentage, 0), 85) / 100))
      `,
      )
      .groupBy(
        "p.id, p.name, c.name, p.budget, p.remaining_budget, p.threshold_percentage",
      )
      .orderBy("((p.budget - p.remaining_budget) / p.budget * 100)", "DESC")
      .limit(5)

    const projectsData = await projectsQuery.getRawMany()

    // Get employee contributions for each project
    const projectsWithEmployees = await Promise.all(
      projectsData.map(async (project) => {
        const employeeQuery = this.timeTrackingEntityRepository
          .createQueryBuilder("tt")
          .leftJoin("tt.employee", "e")
          .select([
            "e.id as employee_id",
            "e.first_name as first_name",
            "e.last_name as last_name",
            "SUM(tt.cost) as total_cost",
            "SUM(tt.total_minutes) as total_minutes",
          ])
          .where("tt.project_id = :projectId", {
            projectId: project.project_id,
          })
          .andWhere("tt.deleted_at IS NULL")
          .andWhere("tt.end_time IS NOT NULL")
          .groupBy("e.id, e.first_name, e.last_name")
          .orderBy("total_cost", "DESC")

        const employeeData = await employeeQuery.getRawMany()

        const budget = parseFloat(project.budget || "0")
        const spent = parseFloat(project.total_spent || "0")
        const percentage = budget > 0 ? Math.round((spent / budget) * 100) : 0

        return {
          id: parseInt(project.project_id),
          name: project.project_name || "Unknown Project",
          client: project.client_name || "Unknown Client",
          budgeted: Math.round(budget * 100) / 100,
          spent: Math.round(spent * 100) / 100,
          percentage,
          employees: employeeData.map((emp) => ({
            name:
              `${emp.first_name || ""} ${emp.last_name || ""}`.trim() ||
              "Unknown Employee",
            cost: Math.round(parseFloat(emp.total_cost || "0") * 100) / 100,
            hours:
              Math.round((parseInt(emp.total_minutes || "0") / 60) * 100) / 100,
          })),
        }
      }),
    )

    return projectsWithEmployees
  }

  /**
   * Get currency symbol from company profile
   */
  private async getCurrencySymbol(companyId: number): Promise<string> {
    try {
      const companyProfile: any =
        await this.companyProfileRepository.getByParams({
          where: { company_id: companyId },
          findOne: true,
        })

      return companyProfile?.currency || "₹"
    } catch (error) {
      return "₹" // Default fallback
    }
  }

  async exportProjectReport(
    res: any,
    filters: ProjectReportExportDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_MONTH,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        activity_type_id: filters.activity_type_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
      }

      // Get raw data, summary, and party costs
      const [rawData, summaryData, partyCost] = await Promise.all([
        this.getProjectReportData(reportFilters),
        this.getSummaryData(reportFilters),
        this.getPartyCostForPeriod(
          dateRange.start_date,
          dateRange.end_date,
          reportFilters,
        ),
      ])

      // Process data for charts
      const charts = await this.processProjectReportCharts(
        rawData,
        dateRange.start_date,
        dateRange.end_date,
        reportFilters,
      )

      // Build summary (including party costs)
      const timeTrackingCost = parseFloat(summaryData.total_cost || "0")
      const totalCostWithParties = timeTrackingCost + partyCost

      const summary: ProjectReportSummaryDto = {
        total_hours: summaryData.total_minutes
          ? Math.round((summaryData.total_minutes / 60) * 10) / 10
          : 0,
        total_cost: Math.round(totalCostWithParties * 100) / 100,
        total_clients: parseInt(summaryData.total_clients || "0"),
        total_projects: parseInt(summaryData.total_projects || "0"),
        total_employees: parseInt(summaryData.total_employees || "0"),
      }

      const workbook = new ExcelJS.Workbook()

      // Create Summary sheet
      const summarySheet = workbook.addWorksheet("Summary")
      summarySheet.columns = [
        { header: "Metric", key: "metric", width: 25 },
        { header: "Value", key: "value", width: 20 },
      ]

      summarySheet.addRow({ metric: "Total Hours", value: summary.total_hours })
      summarySheet.addRow({ metric: "Total Cost", value: summary.total_cost })
      summarySheet.addRow({
        metric: "Total Clients",
        value: summary.total_clients,
      })
      summarySheet.addRow({
        metric: "Total Projects",
        value: summary.total_projects,
      })
      summarySheet.addRow({
        metric: "Total Employees",
        value: summary.total_employees,
      })

      // Create Hours by Project sheet
      const hoursProjectSheet = workbook.addWorksheet("Hours by Project")
      hoursProjectSheet.columns = [
        { header: "Project Name", key: "name", width: 30 },
        { header: "Hours", key: "hours", width: 15 },
      ]

      charts.hours_by_project.forEach((item) => {
        hoursProjectSheet.addRow({
          name: item.name,
          hours: item.hours,
        })
      })

      // Create Cost by Project sheet
      const costProjectSheet = workbook.addWorksheet("Cost by Project")
      costProjectSheet.columns = [
        { header: "Project Name", key: "name", width: 30 },
        { header: "Cost", key: "cost", width: 15 },
      ]

      charts.cost_by_project.forEach((item) => {
        costProjectSheet.addRow({
          name: item.name,
          cost: item.cost,
        })
      })

      // Create Hours by Activity Type sheet
      const hoursActivitySheet = workbook.addWorksheet("Hours by Activity Type")
      hoursActivitySheet.columns = [
        { header: "Activity Type", key: "name", width: 30 },
        { header: "Hours", key: "hours", width: 15 },
      ]

      charts.hours_by_activity_type.forEach((item) => {
        hoursActivitySheet.addRow({
          name: item.name,
          hours: item.hours,
        })
      })

      // Create Hours by Employee sheet
      const hoursEmployeeSheet = workbook.addWorksheet("Hours by Employee")
      hoursEmployeeSheet.columns = [
        { header: "Employee Name", key: "name", width: 30 },
        { header: "Hours", key: "hours", width: 15 },
      ]

      charts.hours_by_employee.forEach((item) => {
        hoursEmployeeSheet.addRow({
          name: item.name,
          hours: item.hours,
        })
      })

      // Create Daily Hours Trend sheet
      const dailyTrendSheet = workbook.addWorksheet("Daily Hours Trend")
      dailyTrendSheet.columns = [
        { header: "Date", key: "date", width: 15 },
        { header: "Hours", key: "hours", width: 15 },
      ]

      charts.daily_hours_trend.forEach((item) => {
        dailyTrendSheet.addRow({
          date: item.date,
          hours: item.hours,
        })
      })

      // Set response headers
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      )
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=project-report.xlsx",
      )

      await workbook.xlsx.write(res)
      res.end()
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Export Clock-in Report to Excel
   */
  async exportClockInReport(
    res: any,
    filters: ClockInReportExportDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.TODAY,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        employee_id: filters.employee_id,
        department_id: filters.department_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        search: filters.search,
        status: filters.status || ClockInStatusType.ALL,
      }

      // Get all data without pagination
      const { data } = await this.getClockInEntriesData(
        reportFilters,
        0,
        999999,
      )

      const workbook = new ExcelJS.Workbook()
      const sheet = workbook.addWorksheet("Clock-in Report")

      // Set up columns
      sheet.columns = [
        { header: "Date", key: "date", width: 15 },
        { header: "Employee ID", key: "employee_id", width: 15 },
        { header: "Employee Name", key: "employee_name", width: 25 },
        { header: "Designation", key: "designation", width: 20 },
        { header: "Department", key: "department_name", width: 20 },
        { header: "Clock In", key: "clock_in", width: 20 },
        { header: "Clock Out", key: "clock_out", width: 20 },
        { header: "Duration", key: "duration", width: 15 },
        { header: "Status", key: "status", width: 15 },
      ]

      // Add data rows
      data.forEach((entry) => {
        const isOngoing = !entry.clock_out
        let duration = "00:00"

        if (isOngoing) {
          const now = moment()
          const clockInTime = moment(entry.clock_in)
          const diffMinutes = now.diff(clockInTime, "minutes")
          duration = this.formatDuration(diffMinutes)
        } else {
          duration = this.formatDuration(entry.duration || 0)
        }

        sheet.addRow({
          date: moment(entry.clock_in).format("DD/MM/YYYY"),
          employee_id: entry.employee?.id || 0,
          employee_name: `${entry.employee?.first_name || ""} ${
            entry.employee?.last_name || ""
          }`.trim(),
          designation: entry.employee?.role?.name || "N/A",
          department_name: entry.employee?.department?.name || "N/A",
          clock_in: moment(entry.clock_in)
            .tz("Asia/Kolkata")
            .format("DD/MM/YYYY HH:mm:ss"),
          clock_out: entry.clock_out
            ? moment(entry.clock_out)
                .tz("Asia/Kolkata")
                .format("DD/MM/YYYY HH:mm:ss")
            : "N/A",
          duration: duration,
          status: isOngoing ? "Ongoing" : "Completed",
        })
      })

      // Set response headers
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      )
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=clock-in-report.xlsx",
      )

      await workbook.xlsx.write(res)
      res.end()
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Export Timesheet Report to Excel
   */
  async exportTimesheetReport(
    res: any,
    filters: TimesheetReportFiltersDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

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

    try {
      // Get currency symbol
      const currencySymbol = await this.getCurrencySymbol(decoded.company_id)
      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_WEEK,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        activity_type_id: filters.activity_type_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        status: filters.status || undefined,
      }

      // Get all data without pagination
      const { data } = await this.getTimeEntriesReportData(
        reportFilters,
        0,
        999999,
      )

      const workbook = new ExcelJS.Workbook()
      const sheet = workbook.addWorksheet("Timesheet Report")

      // Set up columns
      const columns: Partial<ExcelJS.Column>[] = [
        { header: "Date", key: "date", width: 15 },
        { header: "Employee Name", key: "employee_name", width: 25 },
        { header: "Project Name", key: "project_name", width: 20 },
        { header: "Department", key: "department", width: 20 },
        { header: "Clock In", key: "clock_in", width: 20 },
        { header: "Clock Out", key: "clock_out", width: 20 },
        { header: "Duration", key: "duration", width: 15 },
        { header: "Description", key: "description", width: 20 },
        { header: "Activity Type", key: "activity_type", width: 20 },
        { header: "Status", key: "status", width: 15 },
      ]

      if (filters.show_cost === "true") {
        columns.push({ header: "Cost", key: "cost", width: 15 })
      }

      sheet.columns = columns

      // Add data rows
      data.forEach((entry) => {
        const row: any = {
          date: moment(entry.start_time).format("DD/MM/YYYY"),
          employee_name: `${entry.employee?.first_name || ""} ${
            entry.employee?.last_name || ""
          }`.trim(),
          project_name: entry.project?.name || "N/A",
          department: entry.employee?.department?.name || "N/A",
          clock_in: moment(entry.start_time)
            .tz("Asia/Kolkata")
            .format("DD/MM/YYYY HH:mm:ss"),
          clock_out: entry.end_time
            ? moment(entry.end_time)
                .tz("Asia/Kolkata")
                .format("DD/MM/YYYY HH:mm:ss")
            : "N/A",
          duration: this.formatDuration(entry.total_minutes),
          description: entry.description || "N/A",
          activity_type: entry.activityType?.name || "N/A",
          status: entry.end_time ? "Completed" : "Ongoing",
        }

        if (filters.show_cost === "true") {
          row.cost = `${currencySymbol}${Math.round(entry.cost || 0)}`
        }

        sheet.addRow(row)
      })

      // Set response headers
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      )
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=timesheet-report.xlsx",
      )

      await workbook.xlsx.write(res)
      res.end()
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Export Project Costing List to Excel
   */
  async exportProjectCostingList(
    res: any,
    filters: ProjectCostingFiltersDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      // Get currency symbol
      const currencySymbol = await this.getCurrencySymbol(decoded.company_id)
      // Set default year to current year if not provided
      const year = filters.year || new Date().getFullYear()
      const timeFrame = filters.time_frame || TimeFrameType.MONTHLY

      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_MONTH,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        year,
      }

      // Get project costing list data based on time frame
      let projectsData
      if (timeFrame === TimeFrameType.MONTHLY) {
        projectsData = await this.getProjectCostingListData(reportFilters)
      } else {
        // For quarterly and yearly, get cost trend data instead
        projectsData = await this.getProjectCostingListDataByTimeFrame(
          reportFilters,
          timeFrame,
        )
      }

      const workbook = new ExcelJS.Workbook()
      const sheet = workbook.addWorksheet("Project Costing List")

      // Dynamic columns based on time frame
      let columns = [
        { header: "Project ID", key: "project_id", width: 15 },
        { header: "Project Name", key: "project_name", width: 30 },
        { header: "Client Name", key: "client_name", width: 25 },
      ]

      // Add time-based columns
      if (timeFrame === TimeFrameType.MONTHLY) {
        columns = columns.concat([
          { header: "Jan Cost", key: "jan_cost", width: 15 },
          { header: "Feb Cost", key: "feb_cost", width: 15 },
          { header: "Mar Cost", key: "mar_cost", width: 15 },
          { header: "Apr Cost", key: "apr_cost", width: 15 },
          { header: "May Cost", key: "may_cost", width: 15 },
          { header: "Jun Cost", key: "jun_cost", width: 15 },
          { header: "Jul Cost", key: "jul_cost", width: 15 },
          { header: "Aug Cost", key: "aug_cost", width: 15 },
          { header: "Sep Cost", key: "sep_cost", width: 15 },
          { header: "Oct Cost", key: "oct_cost", width: 15 },
          { header: "Nov Cost", key: "nov_cost", width: 15 },
          { header: "Dec Cost", key: "dec_cost", width: 15 },
        ])
      } else if (timeFrame === TimeFrameType.QUARTERLY) {
        columns = columns.concat([
          { header: "Q1 Cost", key: "q1_cost", width: 15 },
          { header: "Q2 Cost", key: "q2_cost", width: 15 },
          { header: "Q3 Cost", key: "q3_cost", width: 15 },
          { header: "Q4 Cost", key: "q4_cost", width: 15 },
        ])
      }
      // For yearly, no additional columns needed

      // Add total cost column
      columns.push({ header: "Total Cost", key: "total_cost", width: 15 })

      sheet.columns = columns

      // Add data rows
      projectsData.forEach((project) => {
        const rowData: any = {
          project_id: project.project_id,
          project_name: project.project_name || "Unknown Project",
          client_name: project.client_name || "Unknown Client",
          total_cost: `${currencySymbol}${project.total_cost || 0}`,
        }

        // Add time-based data
        if (timeFrame === TimeFrameType.MONTHLY) {
          Object.assign(rowData, {
            jan_cost: `${currencySymbol}${project.jan_cost || 0}`,
            feb_cost: `${currencySymbol}${project.feb_cost || 0}`,
            mar_cost: `${currencySymbol}${project.mar_cost || 0}`,
            apr_cost: `${currencySymbol}${project.apr_cost || 0}`,
            may_cost: `${currencySymbol}${project.may_cost || 0}`,
            jun_cost: `${currencySymbol}${project.jun_cost || 0}`,
            jul_cost: `${currencySymbol}${project.jul_cost || 0}`,
            aug_cost: `${currencySymbol}${project.aug_cost || 0}`,
            sep_cost: `${currencySymbol}${project.sep_cost || 0}`,
            oct_cost: `${currencySymbol}${project.oct_cost || 0}`,
            nov_cost: `${currencySymbol}${project.nov_cost || 0}`,
            dec_cost: `${currencySymbol}${project.dec_cost || 0}`,
          })
        } else if (timeFrame === TimeFrameType.QUARTERLY) {
          Object.assign(rowData, {
            q1_cost: `${currencySymbol}${project.q1_cost || 0}`,
            q2_cost: `${currencySymbol}${project.q2_cost || 0}`,
            q3_cost: `${currencySymbol}${project.q3_cost || 0}`,
            q4_cost: `${currencySymbol}${project.q4_cost || 0}`,
          })
        }

        sheet.addRow(rowData)
      })

      // Set response headers
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      )
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=project-costing-list.xlsx",
      )

      await workbook.xlsx.write(res)
      res.end()
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Export Project Costing Report to Excel
   */
  async exportProjectCostingReport(
    res: any,
    filters: ProjectCostingFiltersDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    try {
      // Get currency symbol
      const currencySymbol = await this.getCurrencySymbol(decoded.company_id)
      // Set default year to current year if not provided
      const year = filters.year || new Date().getFullYear()
      const timeFrame = filters.time_frame || TimeFrameType.MONTHLY

      // Parse date range
      const dateRange = this.parseDateRange(
        filters.date_range || DateRangeType.THIS_MONTH,
        filters.start_date,
        filters.end_date,
      )

      if (filters.show_all === "true" && !filters.employee_id) {
        filters.employee_id = null
      } else if (decoded.employee_id && filters.show_all !== "true") {
        filters.employee_id = decoded.employee_id
      }

      // Build filter object
      const reportFilters = {
        company_id: filters.company_id || decoded.company_id,
        client_id: filters.client_id,
        project_id: filters.project_id,
        employee_id: filters.employee_id,
        start_date: dateRange.start_date,
        end_date: dateRange.end_date,
        year,
      }

      // Get cost trend and project cost data (same as regular report)
      const [costTrendData, projectCostData, totalCost] = await Promise.all([
        this.getCostTrendData(reportFilters, timeFrame),
        this.getProjectCostData(reportFilters),
        this.getTotalCostForYear(reportFilters),
      ])

      const workbook = new ExcelJS.Workbook()

      // Create Summary sheet
      const summarySheet = workbook.addWorksheet("Summary")
      summarySheet.columns = [
        { header: "Metric", key: "metric", width: 25 },
        { header: "Value", key: "value", width: 20 },
      ]

      summarySheet.addRow({
        metric: "Total Cost for Year " + year,
        value: totalCost,
      })

      // Create Cost Trend sheet
      const costTrendSheet = workbook.addWorksheet("Cost Trend")
      costTrendSheet.columns = [
        { header: "Period", key: "period", width: 20 },
        { header: "Total Cost", key: "total_cost", width: 15 },
        { header: "Start Date", key: "start_date", width: 15 },
        { header: "End Date", key: "end_date", width: 15 },
      ]

      costTrendData.forEach((item) => {
        costTrendSheet.addRow({
          period: item.period,
          total_cost: `${currencySymbol}${item.total_cost}`,
          start_date: item.start_date,
          end_date: item.end_date,
        })
      })

      // Create Project Cost sheet
      const projectCostSheet = workbook.addWorksheet("Cost by Project")
      projectCostSheet.columns = [
        { header: "Project ID", key: "project_id", width: 15 },
        { header: "Project Name", key: "project_name", width: 30 },
        { header: "Total Cost", key: "total_cost", width: 15 },
      ]

      projectCostData.forEach((item) => {
        projectCostSheet.addRow({
          project_id: item.project_id,
          project_name: item.project_name,
          total_cost: `${currencySymbol}${item.total_cost}`,
        })
      })

      // Set response headers
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      )
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=project-costing-report.xlsx",
      )

      await workbook.xlsx.write(res)
      res.end()
    } catch (error) {
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  /**
   * Get dashboard statistics
   */
  private async getDashboardStats(companyId: number): Promise<any> {
    const today = moment().startOf("day").toDate()
    const endOfToday = moment().endOf("day").toDate()

    // Get active projects count (status = "In Progress")
    const activeProjectsQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .where("p.company_id = :companyId", { companyId })
      .andWhere("p.deleted_at IS NULL")
      .andWhere("LOWER(p.status) = LOWER(:status)", { status: "In Progress" })

    // Get approved leaves for today
    const approvedLeavesTodayQuery = this.leaveRequestEntityRepository
      .createQueryBuilder("lr")
      .where("lr.company_id = :companyId", { companyId })
      .andWhere("lr.deleted_at IS NULL")
      .andWhere("lr.status = :status", { status: "approved" })
      .andWhere("lr.from_date <= :today", { today: endOfToday })
      .andWhere("lr.to_date >= :today", { today })

    // Get pending leaves count
    const pendingLeavesQuery = this.leaveRequestEntityRepository
      .createQueryBuilder("lr")
      .where("lr.company_id = :companyId", { companyId })
      .andWhere("lr.deleted_at IS NULL")
      .andWhere("lr.status = :status", { status: "pending" })

    // Get clocked-in users today (have clock_in today but no clock_out)
    const clockedInUsersQuery = this.clockInRecordRepository
      .createQueryBuilder("cir")
      .select("COUNT(DISTINCT cir.employee_id)", "count")
      .where("cir.company_id = :companyId", { companyId })
      .andWhere("cir.deleted_at IS NULL")
      .andWhere("cir.clock_in >= :today", { today })
      .andWhere("cir.clock_in <= :endOfToday", { endOfToday })
      .andWhere("cir.clock_out IS NULL")

    // Get total employees in company
    const totalEmployeesQuery = this.employeeEntityRepository
      .createQueryBuilder("e")
      .where("e.company_id = :companyId", { companyId })
      .andWhere("e.deleted_at IS NULL")
      .andWhere("e.status = :status", { status: 1 })

    const totalProjectsQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .where("p.company_id = :companyId", { companyId })
      .andWhere("p.deleted_at IS NULL")

    // Get projects over budget count (remaining_budget < 0)
    const projectsOverBudgetQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .where("p.company_id = :companyId", { companyId })
      .andWhere("p.deleted_at IS NULL")
      .andWhere("p.remaining_budget < 0")

    const projectsNearThresholdQuery = this.projectEntityRepository
      .createQueryBuilder("p")
      .where("p.company_id = :companyId", { companyId })
      .andWhere("p.deleted_at IS NULL")
      .andWhere(
        `
        (p.budget - p.remaining_budget) >= 
        (p.budget * (COALESCE(NULLIF(p.threshold_percentage, 0), 85) / 100))
      `,
      )
      .andWhere("p.remaining_budget >= 0")

    const notClockedInUsersQuery = this.employeeEntityRepository
      .createQueryBuilder("e")
      .where("e.company_id = :companyId", { companyId })
      .andWhere("e.deleted_at IS NULL")
      .andWhere("e.status = :status", { status: 1 })
      .andWhere(
        "e.id NOT IN (SELECT employee_id FROM clock_in_records WHERE deleted_at IS NULL AND clock_in >= :today AND clock_in <= :endOfToday)",
        { today, endOfToday },
      )
      .andWhere(
        "e.id NOT IN (SELECT employee_id FROM leave_requests WHERE deleted_at IS NULL AND status = 'approved' AND from_date <= :endOfToday AND to_date >= :today)",
        { today, endOfToday },
      )
      .select("COUNT(DISTINCT e.id)", "count")

    const totalClientsQuery = this.clientEntityRepository
      .createQueryBuilder("c")
      .where("c.company_id = :companyId", { companyId })
      .andWhere("c.deleted_at IS NULL")
      .select("COUNT(DISTINCT c.id)", "count")

    const notClockedInUsersResult = await notClockedInUsersQuery.getRawOne()
    const notClockedInUsers = Number(notClockedInUsersResult?.count || 0)

    const [
      activeProjects,
      approvedLeavesToday,
      pendingLeaves,
      totalEmployees,
      totalProjects,
      projectsOverBudget,
      projectsNearThreshold,
      totalClients,
    ] = await Promise.all([
      activeProjectsQuery.getCount(),
      approvedLeavesTodayQuery.getCount(),
      pendingLeavesQuery.getCount(),
      totalEmployeesQuery.getCount(),
      totalProjectsQuery.getCount(),
      projectsOverBudgetQuery.getCount(),
      projectsNearThresholdQuery.getCount(),
      totalClientsQuery.getCount(),
    ])

    const clockedInUsersResult = await clockedInUsersQuery.getRawOne()
    const clockedInUsers = Number(clockedInUsersResult?.count || 0)

    return {
      active_projects: activeProjects,
      approved_leaves_today: approvedLeavesToday,
      pending_leaves: pendingLeaves,
      clocked_in_users: clockedInUsers,
      not_clocked_in_users: notClockedInUsers,
      total_employees: totalEmployees,
      total_projects: totalProjects,
      projects_over_budget: projectsOverBudget,
      projects_near_threshold: projectsNearThreshold,
      total_clients: totalClients,
    }
  }

  /**
   * Get recent projects for the logged-in employee
   */
  private async getRecentProjects(
    employeeId: number,
    companyId: number,
  ): Promise<RecentProjectDto[]> {
    const query = this.projectEmployeeEntityRepository
      .createQueryBuilder("pe")
      .leftJoin("pe.project", "p")
      .leftJoin("p.client", "c")
      .leftJoin(
        "time_tracking",
        "tt",
        "tt.project_id = p.id AND tt.employee_id = :employeeId AND tt.deleted_at IS NULL",
        { employeeId },
      )
      .select([
        "p.id as project_id",
        "p.name as project_name",
        "c.name as client_name",
        "p.status as status",
        "pe.assigned_at as assigned_at",
        "COALESCE(SUM(tt.total_minutes), 0) as total_minutes",
      ])
      .where("pe.employee_id = :employeeId", { employeeId })
      .andWhere("pe.deleted_at IS NULL")
      .andWhere("p.company_id = :companyId", { companyId })
      .andWhere("p.deleted_at IS NULL")
      .groupBy("p.id, p.name, c.name, p.status, pe.assigned_at")
      .orderBy("pe.assigned_at", "DESC")
      .limit(3)

    const rawData = await query.getRawMany()

    return rawData.map((row) => ({
      project_id: parseInt(row.project_id),
      project_name: row.project_name || "Unknown Project",
      client_name: row.client_name || "Unknown Client",
      total_hours: Math.round((Number(row.total_minutes) / 60) * 100) / 100,
      status: row.status || "Unknown",
    }))
  }

  private async getNotClockedInEmployees(companyId: number): Promise<any> {
    const today = moment().startOf("day").toDate()
    const endOfToday = moment().endOf("day").toDate()

    const query = this.employeeEntityRepository
      .createQueryBuilder("e")
      .where("e.company_id = :companyId", { companyId })
      .andWhere("e.deleted_at IS NULL")
      .andWhere("e.status = 1")
      .andWhere(
        "e.id NOT IN (SELECT employee_id FROM clock_in_records WHERE deleted_at IS NULL AND clock_in >= :today AND clock_in <= :endOfToday)",
        { today, endOfToday },
      )
      .andWhere(
        `e.id NOT IN (
          SELECT employee_id
          FROM leave_requests
          WHERE deleted_at IS NULL
            AND status = 'approved'
            AND from_date <= :endOfToday
            AND to_date >= :today
        )`,
        { today, endOfToday },
      )
      .select(["e.id", "e.first_name", "e.last_name"])

    const notClockedInEmployees = await query.getMany()

    return notClockedInEmployees.map((employee) => ({
      employee_id: employee.id,
      employee_name: employee.first_name + " " + employee.last_name,
    }))
  }

  public async findTodaysTimeTracking(employeeId: number, companyId: number) {
    const today = moment().startOf("day").toDate()

    const qb = this.timeTrackingRepository
      .createQueryBuilder("tt")
      .leftJoin("tt.project", "project")
      .leftJoin("tt.activityType", "activityType")
      .select("COALESCE(project.name, '-')", "project_name")
      .addSelect("COALESCE(activityType.name, '-')", "activity_type")
      .addSelect("SUM(tt.total_minutes)", "total_minutes")
      .where("tt.start_time >= :today", { today })
      .andWhere("activityType.status = :status", { status: 1 })
      .andWhere("LOWER(activityType.name) NOT LIKE :breakActivity", {
        breakActivity: "%break%",
      })

    if (companyId) {
      qb.andWhere("tt.company_id = :companyId", { companyId })
    }

    if (employeeId) {
      qb.andWhere("tt.employee_id = :employeeId", { employeeId })
    }

    qb.groupBy("project.id")
      .addGroupBy("project.name")
      .addGroupBy("activityType.id")
      .addGroupBy("activityType.name")

    // Test simple query first to see if there are any records
    const simpleQuery = this.timeTrackingRepository
      .createQueryBuilder("tt")
      .leftJoin("tt.activityType", "activityType")
      .select("COUNT(*)", "count")
      .where("tt.start_time >= :today", { today })
      .andWhere("activityType.status = :status", { status: 1 })
      .andWhere("LOWER(activityType.name) NOT LIKE :breakActivity", {
        breakActivity: "%break%",
      })

    if (companyId) {
      simpleQuery.andWhere("tt.company_id = :companyId", { companyId })
    }

    if (employeeId) {
      simpleQuery.andWhere("tt.employee_id = :employeeId", { employeeId })
    }

    const rawResults = await qb.getRawMany()

    const formattedTimeTrackings = rawResults.map(
      (row: any, index: number) => ({
        id: index + 1, // Generate a sequential ID since we're grouping
        project_name: row.project_name,
        activity_type: row.activity_type,
        total_hours: Math.round((Number(row.total_minutes) / 60) * 100) / 100,
        description: row.description,
      }),
    )

    return formattedTimeTrackings
  }

  /**
   * Get recent leave requests for the logged-in employee
   */
  private async getRecentLeaves(
    employeeId: number,
    companyId: number,
  ): Promise<RecentLeaveDto[]> {
    const query = this.leaveRequestEntityRepository
      .createQueryBuilder("lr")
      .leftJoin("lr.leaveType", "lt")
      .select([
        "lr.id",
        "lr.from_date",
        "lr.to_date",
        "lr.status",
        "lr.reason",
        "lr.created_at",
        "lt.name",
      ])
      .where("lr.employee_id = :employeeId", { employeeId })
      .andWhere("lr.company_id = :companyId", { companyId })
      .andWhere("lr.deleted_at IS NULL")
      .orderBy("lr.created_at", "DESC")
      .limit(3)

    const leaveRequests = await query.getMany()

    return leaveRequests.map((leave) => ({
      leave_id: leave.id,
      from_date: moment(leave.from_date).format("YYYY-MM-DD"),
      to_date: moment(leave.to_date).format("YYYY-MM-DD"),
      type: leave.leaveType?.name || "Unknown",
      status: leave.status.charAt(0).toUpperCase() + leave.status.slice(1),
      reason: leave.reason || "No reason provided",
      applied_date: moment(leave.created_at).format("YYYY-MM-DD"),
    }))
  }

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

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

    try {
      // Parse Excel file
      const timesheetData = this.parseTimesheetExcelFile(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: TimesheetBulkUploadResponse = {
        total_records: timesheetData.length,
        success_count: 0,
        failure_count: 0,
        errors: [],
      }

      const errorRowsForExcel: any[] = []

      // Process each timesheet record
      for (const timesheetEntry of timesheetData) {
        const rowNumber = timesheetEntry.row_number
        const employeeEmail = timesheetEntry.employee_email || ""
        const projectName = timesheetEntry.project_name || ""

        try {
          // Validate required fields
          if (!timesheetEntry.project_name) {
            throw new Error("Project name is required")
          }

          if (!timesheetEntry.employee_email) {
            throw new Error("Employee email is required")
          }

          if (!timesheetEntry.start_time) {
            throw new Error("Start time is required")
          }

          if (!timesheetEntry.end_time) {
            throw new Error("End time is required")
          }

          if (!timesheetEntry.activity_type) {
            throw new Error("Activity type is required")
          }

          // Validate and parse dates
          const { start_time, end_time } = this.parseTimesheetDates(
            timesheetEntry.start_time,
            timesheetEntry.end_time,
          )

          // Validate time range
          if (start_time >= end_time) {
            throw new Error("End time must be after start time")
          }

          // Find employee by email
          const employee = await this.employeeEntityRepository.findOne({
            where: {
              email: timesheetEntry.employee_email,
              company_id: decoded.company_id,
            },
          })

          if (!employee) {
            throw new Error(
              `Employee with email '${timesheetEntry.employee_email}' not found`,
            )
          }

          // Find project by name
          const project = await this.projectEntityRepository.findOne({
            where: {
              name: timesheetEntry.project_name,
              company_id: decoded.company_id,
            },
          })

          if (!project) {
            throw new Error(
              `Project '${timesheetEntry.project_name}' not found`,
            )
          }

          // Find activity type by name
          const activityType = await this.activityTypeEntityRepository.findOne({
            where: {
              name: timesheetEntry.activity_type,
              company_id: decoded.company_id,
            },
          })

          if (!activityType) {
            throw new Error(
              `Activity type '${timesheetEntry.activity_type}' not found`,
            )
          }

          // Check if employee is assigned to project, if not create assignment
          await this.ensureProjectAssignment(
            project.id,
            employee.id,
            decoded.user_id,
          )

          // Calculate time and cost
          const { total_minutes, cost } = await this.calculateTimeAndCost(
            start_time,
            end_time,
            employee.id,
          )

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

          try {
            // Create timesheet entry
            const timesheetRecord = {
              employee_id: employee.id,
              project_id: project.id,
              activity_type_id: activityType.id,
              start_time: start_time,
              end_time: end_time,
              total_minutes: total_minutes,
              cost: cost,
              description: timesheetEntry.description || null,
              company_id: decoded.company_id,
              created_by: decoded.user_id,
            }

            await queryRunner.manager.save("time_tracking", timesheetRecord)

            // Commit transaction
            await queryRunner.commitTransaction()

            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_email: employeeEmail,
            project_name: projectName,
            error_message: errorMessage,
          })

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

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

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

  /**
   * Parse Excel file and extract timesheet data
   *
   * EXCEL TEMPLATE FORMAT:
   * =====================
   * The Excel file should have the following structure:
   *
   * SAMPLE EXCEL STRUCTURE:
   * | NOTE: project_name, employee_email, start_time, end_time, activity_type must match existing records in the system. Time format: YYYY-MM-DD HH:MM |
   * | project_name | employee_email | start_time | end_time | activity_type | description |
   * | Website Redesign | john@company.com | 2026-01-07 09:00 | 2026-01-07 12:00 | Development | Frontend work on homepage |
   * | Mobile App | jane@company.com | 2026-01-07 13:00 | 2026-01-07 17:00 | Testing | Bug testing and fixes |
   *
   * VALIDATION RULES:
   * - Employee email must exist in the company
   * - Project name must exist in the company
   * - Activity type name must exist in the company
   * - Time format must be YYYY-MM-DD HH:MM (e.g., 2026-01-07 13:33)
   * - End time must be after start time
   * - All times are treated as local timezone and converted to UTC for storage
   */
  private parseTimesheetExcelFile(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 } = {
        project_name: "project_name",
        "project name": "project_name",
        projectname: "project_name",
        employee_email: "employee_email",
        "employee email": "employee_email",
        employeeemail: "employee_email",
        email: "employee_email",
        start_time: "start_time",
        "start time": "start_time",
        starttime: "start_time",
        "clock in": "start_time",
        clockin: "start_time",
        end_time: "end_time",
        "end time": "end_time",
        endtime: "end_time",
        "clock out": "end_time",
        clockout: "end_time",
        activity_type: "activity_type",
        "activity type": "activity_type",
        activitytype: "activity_type",
        activity: "activity_type",
        description: "description",
        desc: "description",
        notes: "description",
        comment: "description",
      }

      // Create normalized headers mapping
      const headerMapping: { [key: number]: string } = {}
      headers.forEach((header, index) => {
        if (header) {
          const normalizedHeader = header.toString().toLowerCase().trim()
          const mappedField = columnMapping[normalizedHeader]
          if (mappedField) {
            headerMapping[index] = mappedField
          }
        }
      })

      // Process rows
      const processedData: any[] = []
      rows.forEach((row: any[], rowIndex) => {
        // Skip empty rows
        if (!row || row.every((cell) => !cell)) {
          return
        }

        const rowData: any = {
          row_number: rowIndex + 3, // +3 because we skip NOTE row, header row, and array is 0-indexed
        }

        // Map row data using header mapping
        row.forEach((cellValue, cellIndex) => {
          const fieldName = headerMapping[cellIndex]
          if (fieldName && cellValue !== null && cellValue !== undefined) {
            rowData[fieldName] = cellValue.toString().trim()
          }
        })

        // Only add rows that have at least project_name or employee_email
        if (rowData.project_name || rowData.employee_email) {
          processedData.push(rowData)
        }
      })

      return processedData
    } catch (error) {
      throw new Error(`Failed to parse Excel file: ${error.message}`)
    }
  }

  /**
   * Parse and validate timesheet dates
   */
  private parseTimesheetDates(
    startTimeStr: string,
    endTimeStr: string,
  ): { start_time: Date; end_time: Date } {
    try {
      // Expected format: "2026-01-07 13:33"
      const dateTimeRegex = /^(\d{4})-(\d{2})-(\d{2})\s+(\d{1,2}):(\d{2})$/

      // Parse start time
      const startMatch = startTimeStr.match(dateTimeRegex)
      if (!startMatch) {
        throw new Error(
          `Invalid start time format. Expected format: YYYY-MM-DD HH:MM (e.g., 2026-01-07 13:33)`,
        )
      }

      // Parse end time
      const endMatch = endTimeStr.match(dateTimeRegex)
      if (!endMatch) {
        throw new Error(
          `Invalid end time format. Expected format: YYYY-MM-DD HH:MM (e.g., 2026-01-07 14:35)`,
        )
      }

      // Create Date objects in local timezone first
      const startDateLocal = new Date(
        parseInt(startMatch[1]), // year
        parseInt(startMatch[2]) - 1, // month (0-indexed)
        parseInt(startMatch[3]), // day
        parseInt(startMatch[4]), // hour
        parseInt(startMatch[5]), // minute
      )

      const endDateLocal = new Date(
        parseInt(endMatch[1]), // year
        parseInt(endMatch[2]) - 1, // month (0-indexed)
        parseInt(endMatch[3]), // day
        parseInt(endMatch[4]), // hour
        parseInt(endMatch[5]), // minute
      )

      // Validate dates
      if (isNaN(startDateLocal.getTime())) {
        throw new Error("Invalid start date")
      }

      if (isNaN(endDateLocal.getTime())) {
        throw new Error("Invalid end date")
      }

      // Convert local time to UTC before storing (same as time-tracking service)
      const start_time = convertLocalToUtc(startDateLocal)
      const end_time = convertLocalToUtc(endDateLocal)

      return { start_time, end_time }
    } catch (error) {
      throw new Error(`Date parsing error: ${error.message}`)
    }
  }

  /**
   * Ensure employee is assigned to project, create assignment if not exists
   */
  private async ensureProjectAssignment(
    projectId: number,
    employeeId: number,
    assignedBy: number,
  ): Promise<void> {
    // Check if assignment already exists using query builder
    const existingAssignment = await this.dataSource
      .getRepository(ProjectEmployee)
      .findOne({
        where: {
          project_id: projectId,
          employee_id: employeeId,
        },
      })

    // If assignment doesn't exist, create it
    if (!existingAssignment) {
      const assignment = {
        project_id: projectId,
        employee_id: employeeId,
        assigned_by: assignedBy,
        assigned_at: new Date(),
        created_by: assignedBy,
      }

      await this.dataSource.getRepository(ProjectEmployee).save(assignment)
    }
  }

  /**
   * Calculate time and cost for timesheet entry
   * Reusing logic from time-tracking service
   */
  private async calculateTimeAndCost(
    startTime: Date,
    endTime: Date,
    employeeId: number,
  ): Promise<{ total_minutes: number; cost: number }> {
    const timeDiff = endTime.getTime() - startTime.getTime()
    const total_minutes = Math.floor(timeDiff / (1000 * 60))

    if (total_minutes <= 0) {
      return { total_minutes: 0, cost: 0 }
    }

    // Get employee details
    const employee = await this.employeeEntityRepository.findOne({
      where: { id: employeeId },
    })

    if (!employee || !employee.gross_salary || employee.gross_salary === 0) {
      return { total_minutes, cost: 0 }
    }

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

    const workingHoursPerDay = companySettings?.working_hours_per_day ?? 8
    const workingDaysPerMonth = companySettings?.working_days_per_month ?? 22

    const totalWorkingMinutesPerMonth =
      workingHoursPerDay > 0 && workingDaysPerMonth > 0
        ? workingHoursPerDay * workingDaysPerMonth * 60
        : 0

    if (totalWorkingMinutesPerMonth <= 0) {
      return { total_minutes, cost: 0 }
    }

    const perMinuteRate = employee.gross_salary / totalWorkingMinutesPerMonth
    const calculatedCost = perMinuteRate * total_minutes

    const cost = Number.isFinite(calculatedCost)
      ? Math.round(calculatedCost * 100) / 100
      : 0

    return { total_minutes, cost }
  }

  /**
   * Generate error Excel file for timesheet bulk upload
   */
  private generateTimesheetErrorExcel(
    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.from("Error generating file")
    }
  }

  /**
   * Get leave analytics for dashboard graphs
   * Includes employee-wise leave bifurcation and monthly leave count
   */
  async getLeaveAnalytics(
    filters: LeaveAnalyticsFiltersDto,
    authHeader: string,
  ) {
    try {
      // Verify JWT token and get company_id
      const decodedToken = verifyJwtToken(authHeader)

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

      const company_id = decodedToken.company_id
      const currentYear = new Date().getFullYear()
      const selectedYear = filters.year ? parseInt(filters.year) : currentYear

      // 1. Employee-wise leave bifurcation by leave type
      const employeeWiseQuery = this.leaveRequestEntityRepository
        .createQueryBuilder("lr")
        .leftJoin("lr.employee", "e")
        .leftJoin("lr.leaveType", "lt")
        .select([
          "e.id as employee_id",
          "e.first_name || ' ' || COALESCE(e.last_name, '') as employee_name",
          "lt.id as leave_type_id",
          "lt.code as leave_type_code",
          "lt.name as leave_type_name",
          "SUM(lr.duration_days) as total_days",
        ])
        .where("lr.company_id = :company_id", { company_id })
        .andWhere("lr.status = :status", { status: "approved" })
        .andWhere("EXTRACT(YEAR FROM lr.from_date) = :year", {
          year: selectedYear,
        })
        .groupBy("e.id, lt.id")
        .orderBy("employee_name", "ASC")
        .addOrderBy("lt.code", "ASC")

      const employeeWiseResults = await employeeWiseQuery.getRawMany()

      // Group results by employee
      const employeeMap = new Map()
      let totalLeaveDays = 0

      employeeWiseResults.forEach((row) => {
        const employeeId = row.employee_id
        const totalDays = parseFloat(row.total_days)
        totalLeaveDays += totalDays

        if (!employeeMap.has(employeeId)) {
          employeeMap.set(employeeId, {
            employee_id: employeeId,
            employee_name: row.employee_name,
            total_days: 0,
            leave_types: [],
          })
        }

        const employee = employeeMap.get(employeeId)
        employee.total_days += totalDays
        employee.leave_types.push({
          leave_type_code: row.leave_type_code,
          leave_type_name: row.leave_type_name,
          total_days: totalDays,
        })
      })

      const employeeWiseLeaves = Array.from(employeeMap.values())

      // 2. Monthly leave count with leave type breakdown for all 12 months
      const monthlyQuery = this.leaveRequestEntityRepository
        .createQueryBuilder("lr")
        .leftJoin("lr.leaveType", "lt")
        .select([
          "EXTRACT(MONTH FROM lr.from_date) as month",
          "lt.id as leave_type_id",
          "lt.code as leave_type_code",
          "lt.name as leave_type_name",
          "SUM(lr.duration_days) as total_days",
        ])
        .where("lr.company_id = :company_id", { company_id })
        .andWhere("lr.status = :status", { status: "approved" })
        .andWhere("EXTRACT(YEAR FROM lr.from_date) = :year", {
          year: selectedYear,
        })
        .groupBy("EXTRACT(MONTH FROM lr.from_date), lt.id")
        .orderBy("month", "ASC")
        .addOrderBy("lt.code", "ASC")

      const monthlyResults = await monthlyQuery.getRawMany()

      // Create array for all 12 months with zero defaults
      const monthNames = [
        "January",
        "February",
        "March",
        "April",
        "May",
        "June",
        "July",
        "August",
        "September",
        "October",
        "November",
        "December",
      ]

      // Group monthly results by month
      const monthlyMap = new Map()

      monthlyResults.forEach((row) => {
        const monthNumber = parseInt(row.month)
        const totalDays = parseFloat(row.total_days)

        if (!monthlyMap.has(monthNumber)) {
          monthlyMap.set(monthNumber, {
            month: monthNumber,
            month_name: monthNames[monthNumber - 1],
            total_days: 0,
            leave_types: [],
          })
        }

        const monthData = monthlyMap.get(monthNumber)
        monthData.total_days += totalDays
        monthData.leave_types.push({
          leave_type_code: row.leave_type_code,
          leave_type_name: row.leave_type_name,
          total_days: totalDays,
        })
      })

      // Create complete array for all 12 months (including months with zero leaves)
      const monthlyLeaveCount = Array.from({ length: 12 }, (_, index) => {
        const monthNumber = index + 1
        const monthData = monthlyMap.get(monthNumber)

        return (
          monthData || {
            month: monthNumber,
            month_name: monthNames[index],
            total_days: 0,
            leave_types: [],
          }
        )
      })

      const response: LeaveAnalyticsResponseDto = {
        employee_wise_leaves: employeeWiseLeaves,
        monthly_leave_count: monthlyLeaveCount,
        year: selectedYear,
        total_leave_days: totalLeaveDays,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.leave_analytics_fetched),
        response as any,
      )
    } catch (error) {
      console.error("Error in getLeaveAnalytics:", error)
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }

  async getProjectManagementReport(
    filters: ProjectManagementFiltersDto,
    authorization: string,
  ): Promise<any> {
    try {
      const decodedToken = verifyJwtToken(authorization)

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

      const company_id = decodedToken.company_id

      // Build time tracking query for meta data
      let timeTrackingQuery = this.timeTrackingRepository
        .createQueryBuilder("tt")
        .leftJoin("tt.project", "p")
        .leftJoin("tt.activityType", "at")
        .where("tt.company_id = :company_id", { company_id })

      // Apply same filters to time tracking
      if (filters.client_id) {
        timeTrackingQuery = timeTrackingQuery.andWhere(
          "p.client_id = :client_id",
          { client_id: filters.client_id },
        )
      }

      if (filters.project_id) {
        timeTrackingQuery = timeTrackingQuery.andWhere(
          "tt.project_id = :project_id",
          {
            project_id: filters.project_id,
          },
        )
      }

      if (filters.activities_id) {
        timeTrackingQuery = timeTrackingQuery.andWhere(
          "tt.activity_type_id = :activities_id",
          { activities_id: filters.activities_id },
        )
      }

      if (filters.start_date && filters.end_date) {
        timeTrackingQuery = timeTrackingQuery
          .andWhere("tt.start_time >= :start_date", {
            start_date: filters.start_date,
          })
          .andWhere("tt.start_time <= :end_date", {
            end_date: filters.end_date,
          })
      }

      // Get meta data
      const metaQuery = await timeTrackingQuery
        .select([
          "SUM(tt.total_minutes) / 60.0 as total_hours",
          "SUM(tt.cost) as total_cost",
          "COUNT(DISTINCT p.client_id) as total_clients",
          "COUNT(DISTINCT tt.project_id) as total_projects",
          "COUNT(DISTINCT tt.employee_id) as total_employees",
        ])
        .getRawOne()

      // Get project managers with their project counts
      const managersQuery = this.projectEntityRepository
        .createQueryBuilder("p")
        .leftJoin("p.project_manager", "manager")
        .leftJoin("p.client", "client")
        .where("p.company_id = :company_id", { company_id })
        .andWhere("p.managed_by IS NOT NULL")

      // Apply same filters to managers query
      if (filters.client_id) {
        managersQuery.andWhere("p.client_id = :client_id", {
          client_id: filters.client_id,
        })
      }

      if (filters.project_id) {
        managersQuery.andWhere("p.id = :project_id", {
          project_id: filters.project_id,
        })
      }

      if (filters.start_date && filters.end_date) {
        managersQuery
          .andWhere("p.created_at >= :start_date", {
            start_date: filters.start_date,
          })
          .andWhere("p.created_at <= :end_date", {
            end_date: filters.end_date,
          })
      }

      const managersData = await managersQuery
        .select([
          "manager.first_name as project_managed_by_first_name",
          "manager.last_name as project_managed_by_last_name",
          "SUM(CASE WHEN p.status = 'Planning' THEN 1 ELSE 0 END) as total_planning_projects",
          "SUM(CASE WHEN p.status = 'In Progress' THEN 1 ELSE 0 END) as total_in_progress_projects",
          "SUM(CASE WHEN p.status = 'Completed' THEN 1 ELSE 0 END) as total_completed_projects",
          "SUM(CASE WHEN p.status = 'On Hold' THEN 1 ELSE 0 END) as total_on_hold_projects",
        ])
        .groupBy("manager.id, manager.first_name, manager.last_name")
        .getRawMany()

      // Format the response
      const meta: ProjectManagementMetaDto = {
        total_hours: Number(parseFloat(metaQuery.total_hours).toFixed(2)) || 0,
        total_cost: parseFloat(metaQuery.total_cost) || 0,
        total_clients: parseInt(metaQuery.total_clients) || 0,
        total_projects: parseInt(metaQuery.total_projects) || 0,
        total_employees: parseInt(metaQuery.total_employees) || 0,
      }

      const data: ProjectManagerDto[] = managersData.map((manager) => ({
        project_managed_by_first_name: manager.project_managed_by_first_name,
        project_managed_by_last_name: manager.project_managed_by_last_name,
        total_planning_projects: parseInt(manager.total_planning_projects) || 0,
        total_in_progress_projects:
          parseInt(manager.total_in_progress_projects) || 0,
        total_completed_projects:
          parseInt(manager.total_completed_projects) || 0,
        total_on_hold_projects: parseInt(manager.total_on_hold_projects) || 0,
      }))

      const response: ProjectManagementResponseDto = {
        meta,
        data,
      }

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.project_management_report_fetched),
        response as any,
      )
    } catch (error) {
      console.error("Error in getProjectManagementReport:", error)
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    }
  }
}