import { Injectable } from "@nestjs/common"
import { CreateProjectDto } from "./dto/create-project.dto"
import { UpdateProjectDto } from "./dto/update-project.dto"
import { ProjectFiltersDto } from "./dto/project-filters.dto"
import { AssignPartyDto } from "./dto/assign-party.dto"
import { PartyAssignmentFiltersDto } from "./dto/party-assignment-filters.dto"
import { DeletePartyAssignmentDto } from "./dto/delete-party-assignment.dto"
import { ProjectRepository } from "./repositories/project.repository"
import { ProjectEmployeeRepository } from "./repositories/project-employee.repository"
import { ProjectPartyAssignmentRepository } from "./repositories/project-party-assignment.repository"
import {
  errorMessage,
  isEmpty,
  successMessage,
  validationMessage,
} from "../../utils/helpers"
import {
  failureResponse,
  successResponse,
} from "../../common/response/response"
import { code } from "../../common/response/response.code"
import { messageKey } from "../../constants/message-keys"
import { Project } from "./entities/project.entity"
import { ProjectEmployee } from "./entities/project-employee.entity"
import { ProjectPartyAssignment } from "./entities/project-party-assignment.entity"
import { PartyTypeCategory } from "../party-types/entities/party-type.entity"
import { verifyJwtToken } from "src/utils/jwt"
import { TimeTrackingRepository } from "../time-tracking/repositories/time-tracking.repository"
import { TimeTracking } from "../time-tracking/entities/time-tracking.entity"
import { InjectRepository } from "@nestjs/typeorm"
import { Repository, DataSource } from "typeorm"
import { Vendor } from "../vendors/entities/vendor.entity"
import { Contractor } from "../contractors/entities/contractor.entity"
import { Consultant } from "../consultants/entities/consultant.entity"
import { PartyPayment } from "../party-types/entities/party-payment.entity"
import { PartyPaymentHistory } from "../party-types/entities/party-payment-history.entity"
import { NotificationService } from "../notification/notification.service"
import {
  notificationTitles,
  notificationMessages,
} from "../../constants/notification.constant"
import { ProjectCompletionLogRepository } from "./repositories/project-completion-log.repository"
import { ProjectCompletionLog } from "./entities/project-completion-log.entity"
import { UpdateCompletionPercentageDto } from "./dto/update-completion-percentage.dto"
import { PermissionRepository } from "../permissions/repositories/permission.repository"
import { RolePermissionRepository } from "../role-permissions/repositories/role-permission.repository"
import { PERMISSIONS } from "src/constants/permissions.constant"

@Injectable()
export class ProjectsService {
  constructor(
    private readonly projectRepository: ProjectRepository,
    private readonly projectEmployeeRepository: ProjectEmployeeRepository,
    private readonly projectPartyAssignmentRepository: ProjectPartyAssignmentRepository,
    private readonly timeTrackingRepository: TimeTrackingRepository,
    private readonly projectCompletionLogRepository: ProjectCompletionLogRepository,
    @InjectRepository(ProjectEmployee)
    private readonly projectEmployeeEntityRepository: Repository<ProjectEmployee>,
    @InjectRepository(ProjectPartyAssignment)
    private readonly projectPartyAssignmentEntityRepository: Repository<ProjectPartyAssignment>,
    @InjectRepository(TimeTracking)
    private readonly timeTrackingEntityRepository: Repository<TimeTracking>,
    @InjectRepository(Vendor)
    private readonly vendorEntityRepository: Repository<Vendor>,
    @InjectRepository(Contractor)
    private readonly contractorEntityRepository: Repository<Contractor>,
    @InjectRepository(Consultant)
    private readonly consultantEntityRepository: Repository<Consultant>,
    @InjectRepository(Project)
    private readonly projectEntityRepository: Repository<Project>,
    @InjectRepository(PartyPayment)
    private readonly partyPaymentEntityRepository: Repository<PartyPayment>,
    @InjectRepository(PartyPaymentHistory)
    private readonly partyPaymentHistoryRepository: Repository<PartyPaymentHistory>,
    @InjectRepository(ProjectCompletionLog)
    private readonly projectCompletionLogEntityRepository: Repository<ProjectCompletionLog>,
    private readonly notificationService: NotificationService,
    private readonly dataSource: DataSource,
    private readonly permissionRepository: PermissionRepository,
    private readonly rolePermissionRepository: RolePermissionRepository,
  ) {}

  async create(createProjectDto: CreateProjectDto, token: string) {
    const decoded = verifyJwtToken(token)
    if (!decoded) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.invalid_token),
      )
    }

    // Check if project with same name exists for the company
    const existingProject = await this.projectRepository.getByParams({
      where: {
        company_id: decoded.company_id,
        name: createProjectDto.name,
      },
      findOne: true,
    })

    if (existingProject) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.already_exist, {
          ":data": "Project",
          ":field": "name",
        }),
      )
    }

    let project: any = new Project()
    project = {
      ...createProjectDto,
      remaining_budget: createProjectDto.budget,
      threshold_percentage: createProjectDto.threshold_percentage || 85,
      company_id: decoded.company_id,
      created_by: decoded.user_id,
    }

    await this.projectRepository.save(project)

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_add, { ":data": "Project" }),
    )
  }

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

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

    const {
      page = 1,
      limit = 10,
      search,
      status,
      statuses,
      client_id,
      column_name,
      order = "ASC",
      filter_by,
      is_time_tracking,
    } = query

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

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

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

    if (!isEmpty(column_name)) {
      switch (column_name) {
        case "name":
        case "project_name":
          orderBy = { name: orderDirection }
          break
        case "budget":
          orderBy = { budget: orderDirection }
          break
        case "created_at":
        default:
          orderBy = { name: orderDirection }
          break
      }
    }

    const whereConditions: any = {}
    const searchConditions: any = {}
    const statusFilters = (statuses || "")
      .split(",")
      .map((item) => item.trim())
      .filter(Boolean)

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

    if (client_id) {
      whereConditions.client_id = client_id
    }

    if (query.managed_by) {
      whereConditions.managed_by = query.managed_by
    }

    // Add time tracking filter
    if (is_time_tracking && is_time_tracking === "true") {
      // Exclude projects with past end_date
      whereConditions.end_date = {
        gte: new Date(),
      }

      // Exclude completed projects - only if status is not already specified
      if (!status && statusFilters.length === 0) {
        whereConditions.status = {
          not: "Completed",
        }
      } else if (status !== "Completed") {
        whereConditions.status = status
      }
    } else if (status) {
      whereConditions.status = status
    }

    // Add search functionality
    if (search) {
      searchConditions.name = search
    }

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

    if (!manageProjectAssignedEmployeesPermission) {
      console.warn(
        `⚠️ No 'manage_project_assigned_employees' permission found in system`,
      )
      return
    }

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

    let projectIds: number[] = []

    if (
      !rolePermissionForManageProjectAssignedEmployees &&
      decoded.employee_id
    ) {
      const employeeProjects: any =
        await this.projectEmployeeRepository.getByParams({
          where: { employee_id: decoded.employee_id },
          whereNull: ["deleted_at"],
        })

      projectIds = employeeProjects.map((p) => p.project_id)

      if (projectIds.length === 0) {
        // Employee not assigned to any project → return empty list
        return successResponse(
          code.SUCCESS,
          successMessage(messageKey.data_retrieve, { ":data": "Projects" }),
          [] as any,
        )
      }
    }

    let projects: any

    // Handle near_threshold filter with custom query
    if (filter_by === "near_threshold") {
      // Use TypeORM query builder directly for complex condition
      let query = this.projectEntityRepository
        .createQueryBuilder("entity")
        .leftJoinAndSelect("entity.company", "entity_company")
        .leftJoinAndSelect("entity.client", "entity_client")
        .where("entity.remaining_budget <= entity.budget * 0.15")
        .andWhere("entity.remaining_budget >= 0")

      // Apply other where conditions
      Object.entries(whereConditions).forEach(([key, value]) => {
        query = query.andWhere(`entity.${key} = :${key}`, { [key]: value })
      })

      if (statusFilters.length > 0) {
        query = query.andWhere("entity.status IN (:...statusFilters)", {
          statusFilters,
        })
      }

      // Apply search conditions
      if (!isEmpty(searchConditions)) {
        Object.entries(searchConditions).forEach(([key, value]) => {
          if (!isEmpty(value)) {
            query = query.andWhere(`entity.${key} ILIKE :search_${key}`, {
              [`search_${key}`]: `%${value}%`,
            })
          }
        })
      }

      // Apply project ID filter for employees
      if (
        !rolePermissionForManageProjectAssignedEmployees &&
        projectIds.length > 0
      ) {
        query = query.andWhere("entity.id IN (:...projectIds)", { projectIds })
      }

      // Apply time tracking filter for query builder
      if (is_time_tracking && is_time_tracking === "true") {
        // Exclude projects with past end_date
        query = query.andWhere("entity.end_date >= :currentDate", {
          currentDate: new Date(),
        })

        // Exclude completed projects - only if status is not already in whereConditions
        const hasStatusCondition = whereConditions.status
        if (!hasStatusCondition) {
          query = query.andWhere("entity.status != :completedStatus", {
            completedStatus: "Completed",
          })
        } else if (
          typeof whereConditions.status === "string" &&
          whereConditions.status !== "Completed"
        ) {
          // If status is specified and it's not 'Completed', apply it
          query = query.andWhere("entity.status = :statusFilter", {
            statusFilter: whereConditions.status,
          })
          // Remove from whereConditions to avoid duplicate application
          delete whereConditions.status
        }
      }

      // Apply ordering
      Object.entries(orderBy).forEach(([key, value]) => {
        query = query.addOrderBy(`entity.${key}`, value as "ASC" | "DESC")
      })

      // Apply pagination
      if (take && (skip || skip === 0)) {
        const [data, count] = await query
          .take(take)
          .skip(skip)
          .getManyAndCount()
        projects = { count, data }
      } else {
        const data = await query.getMany()
        projects = data
      }
    } else {
      // Use regular repository method for other filters
      const params: any = {
        where: whereConditions,
        search: !isEmpty(searchConditions) ? searchConditions : undefined,
        relations: [
          "company:id,name",
          "client:id,name",
          "project_manager:id,first_name,last_name",
        ],
        orderBy,
        take,
        skip,
      }

      if (
        !rolePermissionForManageProjectAssignedEmployees &&
        projectIds.length > 0
      ) {
        params.whereIn = { id: projectIds }
      }

      if (statusFilters.length > 0) {
        params.whereIn = {
          ...(params.whereIn || {}),
          status: statusFilters,
        }
      }

      projects = await this.projectRepository.getByParams(params)
    }

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

  async findOne(id: number) {
    const project: any = await this.projectRepository.getByParams({
      where: { id },
      whereNull: ["deleted_at"],
      relations: [
        "company:id,name",
        "client:id,name",
        "user:id,first_name,last_name",
        "project_manager:id,first_name,last_name",
      ],
      findOne: true,
    })

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

    // Get budget breakdowns
    const budgetBreakdownByActivity =
      await this.getBudgetBreakdownByActivity(id)
    const budgetBreakdownByEmployee =
      await this.getBudgetBreakdownByEmployee(id)
    const budgetBreakdownByParty: any = await this.getBudgetBreakdownByParty(id)

    // Calculate used budget and remaining budget
    const usedBudget = await this.calculateUsedBudget(id)
    const remainingBudget =
      Number(project.budget) -
      (usedBudget + budgetBreakdownByParty?.totalPartyCost)

    // Update remaining budget in project if it has changed
    if (Number(project.remaining_budget) !== remainingBudget) {
      await this.projectRepository.save({
        id: project.id,
        remaining_budget: remainingBudget,
      })
      project.remaining_budget = remainingBudget
    }

    // Add budget breakdown data to project response
    const projectWithBreakdown = {
      ...project,
      used_budget: usedBudget + (budgetBreakdownByParty?.totalPartyCost || 0),
      budget_breakdown_by_activity: budgetBreakdownByActivity,
      budget_breakdown_by_employee: budgetBreakdownByEmployee,
      budget_breakdown_by_party: budgetBreakdownByParty?.partyBreakdownData,
    }

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

  async update(id: number, updateProjectDto: UpdateProjectDto, token: string) {
    const decoded = verifyJwtToken(token)

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

    const project: any = await this.projectRepository.getByParams({
      where: { id },
      findOne: true,
    })

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

    // Check if project with same name exists for the company (excluding current record)
    if (updateProjectDto.name) {
      const existingProject = await this.projectRepository.getByParams({
        where: {
          company_id: decoded.company_id,
          name: updateProjectDto.name,
        },
        whereNull: ["deleted_at"],
        whereNotIn: { id: [id] },
        findOne: true,
      })

      if (existingProject) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Project",
            ":field": "name",
          }),
        )
      }
    }

    // Check if budget is being increased to reset email flags

    if (
      updateProjectDto.budget &&
      Number(updateProjectDto.budget) > Number(project.budget)
    ) {
      project.is_threshold_mail_sent = 0
      project.is_over_budget_mail_sent = 0
    }

    // Check if status is being changed
    const statusChanged =
      updateProjectDto.status && updateProjectDto.status !== project.status
    const oldStatus = project.status

    // Update the project
    Object.assign(project, updateProjectDto)
    project.updated_by = decoded.user_id

    await this.projectRepository.save(project)

    // Send notifications if status changed
    if (statusChanged) {
      try {
        await this.sendProjectStatusChangeNotifications(
          project,
          oldStatus,
          updateProjectDto.status,
          decoded,
        )
      } catch (notificationError) {
        console.error(
          "❌ Error sending project status change notifications:",
          notificationError.message,
        )
      }
    }

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

  /**
   * Send notifications when project status changes
   */
  private async sendProjectStatusChangeNotifications(
    project: any,
    oldStatus: string,
    newStatus: string,
    decoded: any,
  ) {
    try {
      // Get all employees assigned to this project
      const assignedEmployees: any =
        await this.projectEmployeeRepository.getByParams({
          where: { project_id: project.id },
          whereNull: ["deleted_at"],
          select: ["employee_id"],
        })

      const employeeIds = (
        assignedEmployees.data ||
        assignedEmployees ||
        []
      ).map((assignment: any) => assignment.employee_id)

      // Get admin users (assuming they have specific roles or permissions)
      // For now, let's get users with admin-like roles
      const adminUsers: any = await this.projectEntityRepository.query(
        `
        SELECT DISTINCT u.id as employee_id
        FROM users u
        INNER JOIN roles r ON u.role_id = r.id
        WHERE u.company_id = $1 
          AND u.status = 1 
          AND u.deleted_at IS NULL
          AND (r.name ILIKE '%admin%' OR r.name ILIKE '%manager%')
      `,
        [decoded.company_id],
      )

      const adminEmployeeIds = adminUsers.map((admin: any) => admin.employee_id)

      // Combine employee IDs and remove duplicates
      const allNotificationTargets = [
        ...new Set([...employeeIds, ...adminEmployeeIds]),
      ]

      if (allNotificationTargets.length === 0) {
        console.log(
          "⚠️ No employees or admins found to notify about project status change",
        )
        return
      }

      // Get updater name
      const updater: any = await this.projectEntityRepository.query(
        `
        SELECT CONCAT(first_name, ' ', COALESCE(last_name, '')) as full_name
        FROM users 
        WHERE id = $1
      `,
        [decoded.user_id],
      )

      const updaterName = updater[0]?.full_name || "Someone"

      // Send notifications to assigned employees
      if (employeeIds.length > 0) {
        const employeeNotificationMessage =
          notificationMessages.PROJECT_STATUS_UPDATED(
            project.name,
            oldStatus,
            newStatus,
          )

        // Send notification to each employee individually
        for (const employeeId of employeeIds) {
          await this.notificationService.sendAndSaveNotification({
            title: notificationTitles.PROJECT_STATUS_UPDATED,
            description: employeeNotificationMessage,
            company_id: decoded.company_id,
            employee_id: employeeId,
            type: "PROJECT",
            data: {
              project_id: project.id,
              project_name: project.name,
              old_status: oldStatus,
              new_status: newStatus,
              updated_by: updaterName,
            },
          })
        }

        console.log(
          `📱 Project status change notifications sent to ${employeeIds.length} assigned employees`,
        )
      }

      // Send notifications to admin users
      if (adminEmployeeIds.length > 0) {
        const adminNotificationMessage =
          notificationMessages.PROJECT_STATUS_CHANGED_ADMIN(
            project.name,
            newStatus,
            updaterName,
          )

        // Send notification to each admin individually
        for (const adminEmployeeId of adminEmployeeIds) {
          await this.notificationService.sendAndSaveNotification({
            title: notificationTitles.PROJECT_STATUS_UPDATED,
            description: adminNotificationMessage,
            company_id: decoded.company_id,
            employee_id: adminEmployeeId,
            type: "PROJECT",
            data: {
              project_id: project.id,
              project_name: project.name,
              old_status: oldStatus,
              new_status: newStatus,
              updated_by: updaterName,
            },
          })
        }

        console.log(
          `📱 Project status change notifications sent to ${adminEmployeeIds.length} admin users`,
        )
      }
    } catch (error) {
      console.error(
        "❌ Error in sendProjectStatusChangeNotifications:",
        error.message,
      )
      throw error
    }
  }

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

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

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

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

    await this.projectRepository.remove({ id: project.id })

    await this.projectRepository.save({
      id: project.id,
      deleted_by: decoded.user_id,
    })

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

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

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

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

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

    // Toggle between Planning and In Progress
    project.status = project.status === "Planning" ? "In Progress" : "Planning"
    project.updated_by = decoded.user_id

    await this.projectRepository.save(project)

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

  async checkProjectExist(name: string, companyId: number) {
    const project = await this.projectRepository.getByParams({
      where: {
        name: name,
        company_id: companyId,
      },
      findOne: true,
    })

    return !isEmpty(project)
  }

  async assignEmployee(projectId: number, employeeId: number, token: string) {
    const decoded = verifyJwtToken(token)

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

    const project = await this.projectRepository.getByParams({
      where: { id: projectId, company_id: decoded.company_id },
      findOne: true,
    })

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

    // Check if employee is already assigned to this project
    const existingAssignment = await this.projectEmployeeRepository.getByParams(
      {
        where: {
          project_id: projectId,
          employee_id: employeeId,
        },
        whereNull: ["deleted_at"],
        findOne: true,
      },
    )

    if (existingAssignment) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.already_exist, {
          ":data": "Employee assignment",
          ":field": "project",
        }),
      )
    }

    let projectEmployee: any = new ProjectEmployee()
    projectEmployee = {
      project_id: projectId,
      employee_id: employeeId,
      assigned_by: decoded.user_id,
      assigned_at: new Date(),
      created_by: decoded.user_id,
    }

    await this.projectEmployeeRepository.save(projectEmployee)

    // Send notification to the assigned employee
    try {
      const projectWithClient: any = await this.projectRepository.getByParams({
        where: { id: projectId },
        relations: ["client"],
        select: ["name"],
        findOne: true,
      })

      const clientName = projectWithClient?.client?.name || null

      const notificationMessage = notificationMessages.PROJECT_ASSIGNMENT(
        projectWithClient.name,
        clientName,
      )

      await this.notificationService.sendAndSaveNotification({
        title: notificationTitles.PROJECT_ASSIGNMENT,
        description: notificationMessage,
        company_id: decoded.company_id,
        employee_id: employeeId,
        type: "PROJECT",
        data: {
          project_id: projectId,
          project_name: projectWithClient.name,
          client_name: clientName,
        },
      })

      console.log(
        `📱 Project assignment notification sent to employee ${employeeId}`,
      )
    } catch (notificationError) {
      console.error(
        "❌ Error sending project assignment notification:",
        notificationError.message,
      )
    }

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

  async getProjectEmployees(projectId: number, token: string, query: any) {
    const decoded = verifyJwtToken(token)

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

    const { show_all } = query
    // Check if project exists and belongs to company
    const project = await this.projectRepository.getByParams({
      where: { id: projectId, company_id: decoded.company_id },
      findOne: true,
    })

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

    // Get assigned employees with their details using query builder
    const qb = this.projectEmployeeEntityRepository
      .createQueryBuilder("pe")
      .leftJoinAndSelect(
        "employees",
        "e",
        "e.id = pe.employee_id AND e.deleted_at IS NULL",
      )
      .leftJoinAndSelect(
        "departments",
        "d",
        "d.id = e.department_id AND d.deleted_at IS NULL",
      )
      .leftJoinAndSelect(
        "roles",
        "r",
        "r.id = e.role_id AND r.deleted_at IS NULL",
      )
      .leftJoinAndSelect(
        "time_tracking",
        "tt",
        "tt.project_id = pe.project_id AND tt.employee_id = pe.employee_id AND tt.deleted_at IS NULL",
      )
      .where("pe.project_id = :projectId", { projectId })
      .andWhere("pe.deleted_at IS NULL")

    if (decoded.employee_id && show_all !== "true") {
      qb.andWhere("pe.employee_id = :employeeId", {
        employeeId: decoded.employee_id,
      })
    }

    const employees = await qb
      .select([
        "pe.id AS id",
        "pe.project_id AS project_id",
        "pe.employee_id AS employee_id",
        "pe.assigned_by AS assigned_by",
        "pe.assigned_at AS assigned_at",
        "e.id AS employee_id",
        "e.first_name AS employee_first_name",
        "e.last_name AS employee_last_name",
        "e.email AS employee_email",
        "e.contact_no AS employee_contact_no",
        "d.id AS department_id",
        "d.name AS department_name",
        "r.id AS role_id",
        "r.name AS role_name",
        "COALESCE(SUM(tt.total_minutes), 0) AS total_minutes",
        "COALESCE(SUM(tt.cost), 0) AS total_cost",
      ])
      .groupBy(
        "pe.id, pe.project_id, pe.employee_id, pe.assigned_by, pe.assigned_at, e.id, e.first_name, e.last_name, e.email, e.contact_no, d.id, d.name, r.id, r.name",
      )
      .orderBy("pe.assigned_at", "DESC")
      .getRawMany()

    const employeesWithStats = employees.map((row) => ({
      id: row.id,
      project_id: row.project_id,
      employee_id: row.employee_id,
      assigned_by: row.assigned_by,
      assigned_at: row.assigned_at,
      employee: {
        id: row.employee_id,
        first_name: row.employee_first_name,
        last_name: row.employee_last_name,
        department: row.department_id
          ? {
              id: row.department_id,
              name: row.department_name,
            }
          : null,
        role: row.role_id
          ? {
              id: row.role_id,
              name: row.role_name,
            }
          : null,
        spent_hours: Math.round((Number(row.total_minutes) / 60) * 100) / 100,
        total_cost: Math.round(Number(row.total_cost) * 100) / 100,
        email: row.employee_email,
        contact_no: row.employee_contact_no,
      },
    }))

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, {
        ":data": "Project employees",
      }),
      employeesWithStats as any,
    )
  }

  async removeEmployeeAssignment(
    projectId: number,
    assignmentId: number,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

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

    // Check if project exists and belongs to company
    const project = await this.projectRepository.getByParams({
      where: { id: projectId, company_id: decoded.company_id },
      findOne: true,
    })

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

    // Check if assignment exists
    const assignment = await this.projectEmployeeRepository.getByParams({
      where: { id: assignmentId, project_id: projectId },
      whereNull: ["deleted_at"],
      findOne: true,
    })

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

    // Soft delete the assignment
    await this.projectEmployeeRepository.remove({ id: assignmentId })

    await this.projectEmployeeRepository.save({
      id: assignmentId,
      deleted_by: decoded.user_id,
    })

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

  async assignParty(assignPartyDto: AssignPartyDto, token: string) {
    try {
      const decoded = verifyJwtToken(token)

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

      const { project_id, party_id, party_type } = assignPartyDto

      // Validate input parameters
      if (isNaN(Number(project_id)) || isNaN(Number(party_id))) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.field_type_validation_error, {
            ":field": "project_id or party_id",
            ":type": "integer",
          }),
        )
      }

      // Check if project exists and belongs to company
      const project = await this.projectRepository.getByParams({
        where: { id: Number(project_id), company_id: decoded.company_id },
        findOne: true,
      })

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

      // Validate party exists based on party_type
      let party: any = null
      let partyName = ""

      switch (party_type) {
        case PartyTypeCategory.VENDOR:
          party = await this.vendorEntityRepository.findOne({
            where: { id: Number(party_id) },
          })
          partyName = "Vendor"
          break
        case PartyTypeCategory.CONTRACTOR:
          party = await this.contractorEntityRepository.findOne({
            where: { id: Number(party_id) },
          })
          partyName = "Contractor"
          break
        case PartyTypeCategory.CONSULTANT:
          party = await this.consultantEntityRepository.findOne({
            where: { id: Number(party_id) },
          })
          partyName = "Consultant"
          break
        default:
          return failureResponse(
            code.VALIDATION,
            validationMessage(messageKey.field_enum_validation_error, {
              ":field": "party_type",
              ":values": "CONSULTANT, CONTRACTOR, VENDOR",
            }),
          )
      }

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

      // Check if party is already assigned to this project
      const existingAssignment =
        await this.projectPartyAssignmentRepository.getByParams({
          where: {
            project_id: Number(project_id),
            party_id: Number(party_id),
            party_type: party_type,
          },
          whereNull: ["deleted_at"],
          findOne: true,
        })

      if (existingAssignment) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": `${partyName} assignment`,
            ":field": "project",
          }),
        )
      }

      // Create new assignment
      let projectPartyAssignment: any = new ProjectPartyAssignment()
      projectPartyAssignment = {
        project_id: Number(project_id),
        party_id: Number(party_id),
        party_type: party_type,
        created_by: decoded.user_id,
      }

      await this.projectPartyAssignmentRepository.save(projectPartyAssignment)

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_add, {
          ":data": `${partyName} assignment`,
        }),
      )
    } catch (error) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.exception),
      )
    }
  }

  async getAssignedParties(
    query: PartyAssignmentFiltersDto = {},
    token: string,
  ) {
    try {
      const decoded = verifyJwtToken(token)

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

      const { project_id, party_type } = query

      const whereConditions: any = {}

      // Add filters
      if (project_id && !isNaN(Number(project_id))) {
        // Check if project exists and belongs to company
        const project = await this.projectRepository.getByParams({
          where: { id: Number(project_id), company_id: decoded.company_id },
          findOne: true,
        })

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

        whereConditions.project_id = Number(project_id)
      }

      if (party_type) {
        whereConditions.party_type = party_type
      }

      // Get assignments using query builder for better joins
      let queryBuilder = this.projectPartyAssignmentEntityRepository
        .createQueryBuilder("ppa")
        .leftJoinAndSelect(
          "projects",
          "p",
          "p.id = ppa.project_id AND p.deleted_at IS NULL",
        )
        .where("ppa.deleted_at IS NULL")

      // Apply company filter
      queryBuilder = queryBuilder.andWhere("p.company_id = :companyId", {
        companyId: decoded.company_id,
      })

      // Apply filters
      if (project_id && !isNaN(Number(project_id))) {
        queryBuilder = queryBuilder.andWhere("ppa.project_id = :projectId", {
          projectId: Number(project_id),
        })
      }

      if (party_type) {
        queryBuilder = queryBuilder.andWhere("ppa.party_type = :partyType", {
          partyType: party_type,
        })
      }

      const assignments = await queryBuilder
        .select([
          "ppa.id AS id",
          "ppa.project_id AS project_id",
          "ppa.party_id AS party_id",
          "ppa.party_type AS party_type",
          "ppa.created_at AS assigned_at",
          "p.id AS project_id",
          "p.name AS project_name",
        ])
        .orderBy("ppa.created_at", "DESC")
        .getRawMany()

      // Fetch party details and payment data for each assignment
      const assignmentsWithDetails = await Promise.all(
        assignments.map(async (assignment) => {
          let partyDetails: any = null

          switch (assignment.party_type) {
            case PartyTypeCategory.VENDOR:
              partyDetails = await this.vendorEntityRepository.findOne({
                where: { id: Number(assignment.party_id) },
                select: ["id", "vendor_name", "email", "phone_number", "notes"],
              })
              if (partyDetails) {
                partyDetails.name = partyDetails.vendor_name
              }
              break
            case PartyTypeCategory.CONTRACTOR:
              partyDetails = await this.contractorEntityRepository.findOne({
                where: { id: Number(assignment.party_id) },
                select: [
                  "id",
                  "contractor_name",
                  "email",
                  "phone_number",
                  "notes",
                ],
              })
              if (partyDetails) {
                partyDetails.name = partyDetails.contractor_name
              }
              break
            case PartyTypeCategory.CONSULTANT:
              partyDetails = await this.consultantEntityRepository.findOne({
                where: { id: Number(assignment.party_id) },
                select: [
                  "id",
                  "consultant_name",
                  "email",
                  "phone_number",
                  "notes",
                ],
              })
              if (partyDetails) {
                partyDetails.name = partyDetails.consultant_name
              }
              break
          }

          // Fetch payment data for the party
          const paymentData = await this.partyPaymentEntityRepository.findOne({
            where: {
              party_id: Number(assignment.party_id),
              party_type: assignment.party_type,
              company_id: decoded.company_id,
            },
            select: [
              "payment_type",
              "amount",
              "start_date",
              "end_date",
              "notes",
              "paid_date",
            ],
          })

          return {
            id: assignment.id,
            project_id: assignment.project_id,
            party_id: assignment.party_id,
            party_type: assignment.party_type,
            assigned_at: assignment.assigned_at,
            project: {
              id: assignment.project_id,
              name: assignment.project_name,
            },
            party: partyDetails,
            payment: paymentData || null,
          }
        }),
      )

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Party assignments",
        }),
        assignmentsWithDetails as any,
      )
    } catch (error) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.exception),
      )
    }
  }

  async deletePartyAssignment(
    deletePartyAssignmentDto: DeletePartyAssignmentDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

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

    const { project_id, party_id, party_type } = deletePartyAssignmentDto

    // Validate input parameters
    if (isNaN(Number(project_id)) || isNaN(Number(party_id))) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.field_type_validation_error, {
          ":field": "project_id or party_id",
          ":type": "integer",
        }),
      )
    }

    // Check if project exists and belongs to company
    const project = await this.projectRepository.getByParams({
      where: { id: Number(project_id), company_id: decoded.company_id },
      findOne: true,
    })

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

    // Check if assignment exists
    const assignment = await this.projectPartyAssignmentRepository.getByParams({
      where: {
        project_id: Number(project_id),
        party_id: Number(party_id),
        party_type: party_type,
      },
      whereNull: ["deleted_at"],
      findOne: true,
    })

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

    // Soft delete the assignment
    await this.projectPartyAssignmentRepository.remove({
      id: (assignment as any).id,
    })

    await this.projectPartyAssignmentRepository.save({
      id: (assignment as any).id,
      deleted_by: decoded.user_id,
    })

    let partyName = ""
    switch (party_type) {
      case PartyTypeCategory.VENDOR:
        partyName = "Vendor"
        break
      case PartyTypeCategory.CONTRACTOR:
        partyName = "Contractor"
        break
      case PartyTypeCategory.CONSULTANT:
        partyName = "Consultant"
        break
    }

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_removed, {
        ":data": `${partyName} assignment`,
      }),
    )
  }

  /**
   * Get budget breakdown by activity for a project
   */
  private async getBudgetBreakdownByActivity(projectId: number) {
    const activityBreakdown = await this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .leftJoin(
        "activity_types",
        "at",
        "at.id = tt.activity_type_id AND at.deleted_at IS NULL",
      )
      .where("tt.project_id = :projectId", { projectId })
      .andWhere("tt.deleted_at IS NULL")
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries
      .select([
        "at.name AS activity_name",
        "COALESCE(SUM(tt.total_minutes), 0) AS total_minutes",
        "COALESCE(SUM(tt.cost), 0) AS total_amount",
      ])
      .groupBy("at.id, at.name")
      .orderBy("total_amount", "DESC")
      .getRawMany()

    return activityBreakdown.map((item) => ({
      activity_name: item.activity_name || "Unknown Activity",
      total_hours: Math.round((Number(item.total_minutes) / 60) * 100) / 100,
      total_amount: Math.round(Number(item.total_amount) * 100) / 100,
    }))
  }

  /**
   * Get budget breakdown by employee for a project
   */
  private async getBudgetBreakdownByEmployee(projectId: number) {
    const employeeBreakdown = await this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .leftJoin(
        "employees",
        "e",
        "e.id = tt.employee_id AND e.deleted_at IS NULL",
      )
      .where("tt.project_id = :projectId", { projectId })
      .andWhere("tt.deleted_at IS NULL")
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries
      .select([
        "e.first_name || ' ' || e.last_name AS employee_name",
        "COALESCE(SUM(tt.total_minutes), 0) AS total_minutes",
        "COALESCE(SUM(tt.cost), 0) AS total_amount",
      ])
      .groupBy("e.id, e.first_name, e.last_name")
      .orderBy("total_amount", "DESC")
      .getRawMany()

    return employeeBreakdown.map((item) => ({
      employee_name: item.employee_name || "Unknown Employee",
      total_hours: Math.round((Number(item.total_minutes) / 60) * 100) / 100,
      total_amount: Math.round(Number(item.total_amount) * 100) / 100,
    }))
  }

  /**
   * Get budget breakdown by party (contractors, vendors, consultants) for a project
   */
  private async getBudgetBreakdownByParty(projectId: number) {
    // Get all parties with payments for this project
    const partyBreakdown = await this.partyPaymentEntityRepository
      .createQueryBuilder("pp")
      .where("pp.project_id = :projectId", { projectId })
      .andWhere("pp.deleted_at IS NULL")
      .select([
        "pp.party_id AS party_id",
        "pp.party_type AS party_type",
        "pp.payment_type AS payment_type",
        "pp.amount AS fixed_amount",
      ])
      .getRawMany()

    // Group parties by type and calculate costs
    const partyTypeCosts = {
      CONTRACTOR: 0,
      VENDOR: 0,
      CONSULTANT: 0,
    }

    // Calculate costs for each party
    for (const party of partyBreakdown) {
      let partyCost = 0

      if (party.payment_type === "FIXED") {
        // For FIXED payments, use the direct amount
        partyCost = Number(party.fixed_amount) || 0
      } else if (party.payment_type === "MONTHLY") {
        // For MONTHLY payments, sum all history records
        const historySum = await this.partyPaymentHistoryRepository
          .createQueryBuilder("pph")
          .where("pph.project_id = :projectId", { projectId })
          .andWhere("pph.party_id = :partyId", { partyId: party.party_id })
          .andWhere("pph.party_type = :partyType", {
            partyType: party.party_type,
          })
          .andWhere("pph.deleted_at IS NULL")
          .select("COALESCE(SUM(pph.amount), 0) as total_amount")
          .getRawOne()

        partyCost = Number(historySum?.total_amount) || 0
      }

      // Add to the appropriate party type total
      if (partyTypeCosts.hasOwnProperty(party.party_type)) {
        partyTypeCosts[party.party_type] += partyCost
      }
    }

    const totalPartyCost =
      partyTypeCosts.CONTRACTOR +
      partyTypeCosts.VENDOR +
      partyTypeCosts.CONSULTANT
    // Return breakdown in the same format as other breakdowns
    const partyBreakdownData = [
      {
        party_type: "CONTRACTOR",
        party_name: "Contractors",
        total_amount: Math.round(partyTypeCosts.CONTRACTOR * 100) / 100,
      },
      {
        party_type: "VENDOR",
        party_name: "Vendors",
        total_amount: Math.round(partyTypeCosts.VENDOR * 100) / 100,
      },
      {
        party_type: "CONSULTANT",
        party_name: "Consultants",
        total_amount: Math.round(partyTypeCosts.CONSULTANT * 100) / 100,
      },
    ].filter((item) => item.total_amount > 0)

    // Only return party types with costs
    const partyBreakdownWithTotal = {
      partyBreakdownData,
      totalPartyCost: Math.round(totalPartyCost * 100) / 100,
    }

    return partyBreakdownWithTotal
  }

  /**
   * Calculate total used budget for a project
   */
  private async calculateUsedBudget(projectId: number): Promise<number> {
    // Calculate time tracking costs (existing logic)
    const timeTrackingResult = await this.timeTrackingEntityRepository
      .createQueryBuilder("tt")
      .where("tt.project_id = :projectId", { projectId })
      .andWhere("tt.deleted_at IS NULL")
      .andWhere("tt.end_time IS NOT NULL") // Only completed time entries
      .select("COALESCE(SUM(tt.cost), 0) AS total_used")
      .getRawOne()

    const timeTrackingCost = Number(timeTrackingResult?.total_used || 0)

    // Calculate party costs
    const partyCost = await this.calculatePartyTotalCost(projectId)

    // Return total used budget (time tracking + party costs)
    return Math.round((timeTrackingCost + partyCost) * 100) / 100
  }

  /**
   * Calculate total party costs for a project
   */
  private async calculatePartyTotalCost(projectId: number): Promise<number> {
    // Get all parties with payments for this project
    const partyPayments = await this.partyPaymentEntityRepository
      .createQueryBuilder("pp")
      .where("pp.project_id = :projectId", { projectId })
      .andWhere("pp.deleted_at IS NULL")
      .select(["pp.party_id", "pp.party_type", "pp.payment_type", "pp.amount"])
      .getRawMany()

    let totalPartyCost = 0

    // Calculate cost for each party
    for (const payment of partyPayments) {
      if (payment.payment_type === "FIXED") {
        // For FIXED payments, use the direct amount
        totalPartyCost += Number(payment.amount) || 0
      } else if (payment.payment_type === "MONTHLY") {
        // For MONTHLY payments, sum all history records
        const historySum = await this.partyPaymentHistoryRepository
          .createQueryBuilder("pph")
          .where("pph.project_id = :projectId", { projectId })
          .andWhere("pph.party_id = :partyId", { partyId: payment.party_id })
          .andWhere("pph.party_type = :partyType", {
            partyType: payment.party_type,
          })
          .andWhere("pph.deleted_at IS NULL")
          .select("COALESCE(SUM(pph.amount), 0) as total_amount")
          .getRawOne()

        totalPartyCost += Number(historySum?.total_amount) || 0
      }
    }

    return totalPartyCost
  }

  async updateCompletionPercentage(
    projectId: number,
    updateCompletionPercentageDto: UpdateCompletionPercentageDto,
    token: string,
  ) {
    const decoded = verifyJwtToken(token)

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

    const queryRunner = this.dataSource.createQueryRunner()
    await queryRunner.connect()
    await queryRunner.startTransaction()

    try {
      // Get the current project
      const project: any = await this.projectRepository.getByParams({
        where: { id: projectId, company_id: decoded.company_id },
        findOne: true,
      })

      if (!project) {
        await queryRunner.rollbackTransaction()
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.not_found, { ":item": "Project" }),
        )
      }

      // Check if the new percentage is the same as current
      if (
        (project as Project).completion_percentage ===
        updateCompletionPercentageDto.completion_percentage
      ) {
        await queryRunner.rollbackTransaction()
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist),
        )
      }

      const oldPercentage = (project as Project).completion_percentage
      const newPercentage = updateCompletionPercentageDto.completion_percentage

      // Toggle between Planning and In Progress
      project.completion_percentage = newPercentage
      project.updated_by = decoded.user_id

      await this.projectRepository.save(project)

      // Create completion log entry
      const completionLog = new ProjectCompletionLog()
      completionLog.project_id = projectId
      completionLog.old_percentage = oldPercentage
      completionLog.new_percentage = newPercentage
      completionLog.changed_by = decoded.user_id

      await this.projectCompletionLogRepository.save(completionLog)

      // Commit transaction
      await queryRunner.commitTransaction()

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_update, {
          ":data": "Project completion percentage",
        }),
      )
    } catch (error) {
      await queryRunner.rollbackTransaction()
      return failureResponse(code.ERROR, errorMessage(messageKey.exception))
    } finally {
      await queryRunner.release()
    }
  }
}