import { Injectable } from "@nestjs/common"
import { ConfigService } from "@nestjs/config"
import { InjectRepository } from "@nestjs/typeorm"
import { fileStoreLocation } from "src/common/file-upload/file-store-location"
import { failureResponse, successResponse } from "src/common/response/response"
import { code } from "src/common/response/response.code"
import { messageKey } from "src/constants/message-keys"
import { formatMessage } from "src/constants/notification-message"
import {
  errorMessage,
  getLatLongFromAddress,
  getPlaceIdFromAddress,
  isEmpty,
  sendEmailNotification,
  successMessage,
  validationMessage,
} from "src/utils/helpers"
import { DataSource, In, Repository } from "typeorm"
import { UserLoginRepository } from "../../auth/repositories/user-login.repository"
import { CityRepository } from "../../city/repositories/city.repository"
import { ClientCompanyContactRepository } from "../../client-company-contacts/repositories/client-company-contacts.repository"
import { ClientCompanyRepository } from "../../clients-companies/repositories/clients-companies.repository"
import { CountryRepository } from "../../country/repositories/country.repository"
import { CustomerTag } from "../../customers-tags/entities/customer_tags.entity"
import { CustomerTagRepository } from "../../customers-tags/repositories/customer_tags.repository"
import { HospitalRepository } from "../../hospitals/repositories/hospital.repository"
import { NotificationService } from "../../notification/v1/notification.service"
import { RATING_TYPES } from "../../rating/constants/rating-type.constants"
import { Rating } from "../../rating/entities/rating.entity"
import { RatingRepository } from "../../rating/repositories/rating.repository"
import { RoleRepository } from "../../role/repositories/role.repository"
import { StateRepository } from "../../state/repositories/state.repository"
import { TeamMember } from "../../team-member/entities/team_member.entity"
import { TeamMemberRepository } from "../../team-member/repositories/team_member.repository"
import { VehicleRepository } from "../../vehicle-type/repositories/vehicle-type.repository"
import { CreateCustomerDto } from "../dto/create-customer.dto"
import { SaveCustomerPhoneNumbersDto } from "../dto/customer-phone-numbers.dto"
import { FilterCustomerDto } from "../dto/filter-customer.dto"
import { UpdateCustomerDto } from "../dto/update-customer.dto"
import { AssignPricingCityByDispatcherDto } from "../dto/assign-pricing-city-by-dispatcher.dto"
import { Customer } from "../entities/customer.entity"
import { CustomerPhoneNumbers } from "../entities/customer_phone_number.entity"
import { CustomerRepository } from "../repositories/customers.repository"
import { PhoneNumbersRepository } from "../repositories/phone-numbers.repository"
import { CustomerMobileLoginDto } from "../dto/create-customer-mobile-login.dto"
import moment from "moment"
import { appSetting } from "src/config/app.config"
import { CustomerLoginRepository } from "../repositories/customer-login.repository"
import { CustomerLogin } from "../entities/customer_login.entity"
import { sendOtpEmailTemplate } from "src/common/emails/templates/send-otp"
import { mailSubject } from "src/common/emails/email-subjects"
import { sendOtpViaSms } from "src/common/sms/send-otp-twilio"
import { randomBytes } from "crypto"
import { CONTACT } from "src/constants/user.constant"
import { CreateCustomerEpisodeDto } from "../dto/customer-episode/create-customer-episode.dto"
import { UpdateCustomerEpisodeDto } from "../dto/customer-episode/update-customer-episode.dto"
import { CreateEpisodeLogDto } from "../dto/episode-log/create-episode-log.dto"
import { UpdateEpisodeLogDto } from "../dto/episode-log/update-episode-log.dto"
import { GenerateUploadUrlDto } from "../dto/episode-log/generate-upload-url.dto"
import { CustomerEpisode } from "../entities/customer_episode.entity"
import { CustomerEpisodeRepository } from "../repositories/customer-episode.repository"
import { EpisodeLogRepository } from "../repositories/episode-log.repository"
import { CloudflareR2Service } from "../../../utils/cloudflare-r2.service"
import { EpisodeLog } from "../entities/episode_log.entity"
import { InvoiceTrips } from "../../invoices/entities/trip-invoice.entity"
import { TripLog } from "../../trips/entities/trip-logs.entity"
import * as ExcelJS from "exceljs"
import { Trip } from "../../trips/entities/trip.entity"
import { STATUS } from "src/constants/trip.constant"

const BACKFILL_DELAY_MS = 150

const buildAddressForBackfill = (
  parts: (string | null | undefined)[],
): string => {
  return parts
    .filter((p): p is string => p != null && String(p).trim() !== "")
    .map((p) => p.trim())
    .join(", ")
}

const sleepBackfill = (ms: number): Promise<void> =>
  new Promise((resolve) => setTimeout(resolve, ms))

@Injectable()
export class CustomersService {
  constructor(
    private readonly customerRepository: CustomerRepository,
    private readonly clientCompanyRepository: ClientCompanyRepository,
    private readonly clientContactRepository: ClientCompanyContactRepository,
    private readonly cityRepository: CityRepository,
    private readonly stateRepository: StateRepository,
    private readonly countryRepository: CountryRepository,
    private readonly hospitalRepository: HospitalRepository,
    private readonly tagRepository: CustomerTagRepository,
    private readonly configService: ConfigService,
    private readonly teamRepository: TeamMemberRepository,
    private readonly roleRepository: RoleRepository,
    private readonly customerPhoneNumberRepository: PhoneNumbersRepository,
    private readonly userLoginRepository: UserLoginRepository,
    private readonly ratingRepository: RatingRepository,
    private readonly vehicleTypeRepository: VehicleRepository,
    private readonly notificationService: NotificationService,
    private readonly customerLoginRepository: CustomerLoginRepository,
    private readonly customerEpisodeRepository: CustomerEpisodeRepository,
    private readonly episodeLogRepository: EpisodeLogRepository,
    private readonly cloudflareR2Service: CloudflareR2Service,
    private dataSource: DataSource,
    @InjectRepository(CustomerPhoneNumbers)
    private readonly customerPhoneNumberModuleRepository: Repository<CustomerPhoneNumbers>,

    @InjectRepository(EpisodeLog)
    private readonly EpisodeLogEntityRepository: Repository<EpisodeLog>,

    @InjectRepository(CustomerEpisode)
    private readonly CustomerEpisodeEntityRepository: Repository<CustomerEpisode>,

    @InjectRepository(TripLog)
    private readonly tripLogEntityRepository: Repository<TripLog>,

    @InjectRepository(Trip)
    private readonly tripEntityRepository: Repository<Trip>,
  ) {}

  async create(createCustomerDto: CreateCustomerDto) {
    if (!isEmpty(createCustomerDto?.client_company_id)) {
      const isClientExist = (await this.clientCompanyRepository.getByParams({
        where: {
          id: createCustomerDto?.client_company_id,
        },
        findOne: true,
      })) as any

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

      // if (isClientExist?.is_medical_patient === true) {
      //   if (isEmpty(createCustomerDto?.client_contact_id)) {
      //     return failureResponse(
      //       code.BAD_REQUEST,
      //       messageKey.client_contact_required_if_client_selected,
      //     )
      //   }
      // }

      // const isClientContactExist =
      //   await this.clientContactRepository.getByParams({
      //     where: {
      //       id: createCustomerDto?.client_contact_id,
      //     },
      //     findOne: true,
      //   })

      // if (
      //   isClientExist?.is_medical_patient === true &&
      //   isEmpty(isClientContactExist)
      // ) {
      //   return failureResponse(
      //     code.VALIDATION,
      //     errorMessage(messageKey.data_not_found, {
      //       ":data": "Client contact",
      //     }),
      //   )
      // }
    }

    const customer = new Customer()

    Object.assign(customer, createCustomerDto)

    const customerData = await this.customerRepository.save(customer)

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

  /**
   * Backfill place_id for customers that have primary_address but no place_id (via Google Geocoding API).
   */
  async backfillPlaceIds() {
    const rows = await this.customerRepository.getForPlaceIdBackfill()

    let updated = 0
    let failed = 0
    let skipped = 0

    for (const row of rows) {
      const fullAddress = buildAddressForBackfill([
        row.primary_address,
        row.secondary_address,
        row.city_name,
        row.state_name,
        row.country_name,
        row.zip_code,
      ])

      if (!fullAddress) {
        skipped++
        continue
      }

      const result = await getPlaceIdFromAddress(fullAddress)
      await sleepBackfill(BACKFILL_DELAY_MS)

      if (!result) {
        failed++
        continue
      }

      try {
        await this.customerRepository.save({ place_id: result.place_id }, {
          id: row.id,
        } as any)
        updated++
      } catch {
        failed++
      }
    }

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_update, { ":data": "Place IDs" }),
      { updated, failed, skipped },
    )
  }

  async assignPricingCityByDispatcher(dto: AssignPricingCityByDispatcherDto) {
    const isPricingCityExist = await this.cityRepository.getByParams({
      where: { id: dto.pricing_city_id },
      findOne: true,
    })

    if (isEmpty(isPricingCityExist)) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.data_not_found, { ":data": "Pricing City" }),
      )
    }

    const dispatcherRoleId: any = await this.roleRepository.getByParams({
      whereLower: { name: "Dispatcher" },
      findOne: true,
      select: ["id"],
    })

    const isDispatcherExist = await this.teamRepository.getByParams({
      where: {
        id: dto.dispatcher_id,
        role_id: dispatcherRoleId?.id,
      },
      findOne: true,
      select: ["id"],
    })

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

    const updateResult = await this.dataSource
      .createQueryBuilder()
      .update(Customer)
      .set({ pricing_city_id: dto.pricing_city_id })
      .where("dispatcher_id = :dispatcherId", {
        dispatcherId: dto.dispatcher_id,
      })
      .execute()

    await this.tripEntityRepository
      .createQueryBuilder()
      .update(Trip)
      .set({ city_id: dto.pricing_city_id })
      .where(
        "customer_id IN (SELECT id FROM customers WHERE dispatcher_id = :dispatcherId)",
      )
      .setParameter("dispatcherId", dto.dispatcher_id)
      .andWhere("status IN (:...tripStatuses)", {
        tripStatuses: [STATUS.ACTIVE, STATUS.REQUESTED_BY_CUSTOMER],
      })
      .execute()

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_update, {
        ":data": "Customers pricing city",
      }),
      {
        updated_count: updateResult.affected ?? 0,
        dispatcher_id: dto.dispatcher_id,
        pricing_city_id: dto.pricing_city_id,
      },
    )
  }

  async findAll(token: any, filterCustomerDto: FilterCustomerDto) {
    // Default pagination values
    const take =
      filterCustomerDto.limit ||
      this.configService.get<number>("APP.pagination.take")
    const skip =
      filterCustomerDto.skip ||
      this.configService.get<number>("APP.pagination.skip")

    const userDetail: any = await this.userLoginRepository.getByParams({
      where: {
        access_token: token,
      },
      findOne: true,
      select: ["id", "user_id"],
      relations: ["user:id,team_member_id,role_id.role:id,name"],
    })

    // Build where clause for filters
    const where: any = {}
    if (filterCustomerDto.country_id) {
      where.country_id = filterCustomerDto.country_id
    }

    if (!isEmpty(filterCustomerDto.is_medical_patient)) {
      where.is_medical_tourist =
        typeof filterCustomerDto?.is_medical_patient === "string"
          ? filterCustomerDto?.is_medical_patient === "true"
          : Boolean(filterCustomerDto?.is_medical_patient)
    }

    if (userDetail?.user?.role?.name.toLowerCase() === "dispatcher") {
      where.dispatcher_id = userDetail.user?.team_member_id
    } else if (!isEmpty(filterCustomerDto.dispatcher_id)) {
      where.dispatcher_id = filterCustomerDto.dispatcher_id
    }

    if (!isEmpty(filterCustomerDto.client_id)) {
      where.client_company_id = filterCustomerDto.client_id
    }

    if (!isEmpty(filterCustomerDto.hospital_id)) {
      where.hospital_id = filterCustomerDto.hospital_id
    }

    if (!isEmpty(filterCustomerDto.prn_number)) {
      where.prn_number = filterCustomerDto.prn_number
    }

    // Build search clause (example: search by customer_name, email, phone_number, prn_number)
    const search: any = {}
    if (filterCustomerDto.search) {
      search.customer_name = filterCustomerDto.search
      search.email = filterCustomerDto.search
      search.phone_number = filterCustomerDto.search
      search.prn_number = filterCustomerDto.search
    }

    // Prepare params for base repo
    const params: any = {
      where,
      search,
      take,
      skip,
      orderBy: { created_at: "DESC" },
      relations: [
        "tags:id,name,type",
        "city:id,name",
        "state:id,name",
        "country:id,name",
        "hospital:id,name,address,email,phone_number",
        "vehicle_type:id,name",
        "dispatcher:id,first_name,last_name",
        "client_company:id,company_name,is_medical_patient",
      ],
    }

    if (!isEmpty(filterCustomerDto.is_direct_customer)) {
      const isDirect =
        typeof filterCustomerDto.is_direct_customer === "string"
          ? filterCustomerDto.is_direct_customer === "true"
          : Boolean(filterCustomerDto.is_direct_customer)

      if (isDirect) {
        params.whereNull = ["client_company_id"]
      } else {
        params.whereNotNull = ["client_company_id"]
      }
    }

    if (filterCustomerDto?.is_customer_dropdown) {
      params.whereNotNull = ["customer_name"]
    }

    const result = await this.customerRepository.getByParams(params)

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

  async findOne(id: number, isCustomer: boolean) {
    const customerDetail = (await this.customerRepository.getByParams({
      where: {
        id: id,
      },
      relations: [
        "tags:id,name,type",
        "city:id,name",
        "state:id,name",
        "country:id,name",
        "pricing_city:id,name",
        "pricing_city.state:id,name",
        "pricing_city.country:id,name",
        "hospital:id,name,address,email,phone_number",
        "vehicle_type:id,name",
        "dispatcher:id,first_name,last_name,email,phone_number,country_code,profile_photo",
        "client_company:id,company_name",
        "client_contact:id,first_name,last_name,email,phone_number,country_code",
        "phone_numbers:id,country_code,phone_number,is_primary,type",
        "escort:id,customer_id,name,relation,gender,date_of_birth,passport_number,country_code,phone_number,email,visa_start_date,visa_end_date,escort_type,is_sponsered",
        "episodes:id,episode_number,start_date,end_date,status.logs:id,log_number,prn_number,reference_number,log_date,log_expiration,status,log_document.hospital:id,name",
      ],
      findOne: true,
    })) as Customer

    if (isEmpty(customerDetail)) {
      return failureResponse(
        code.BAD_REQUEST,
        isCustomer
          ? messageKey.customer_not_found
          : errorMessage(messageKey.data_not_found, {
              ":data": "Customer",
            }),
      )
    }

    const averageRating = await this.ratingRepository.getAverageRating({
      rated_id: id,
      rating_type: RATING_TYPES.DRIVER_TO_CUSTOMER,
    })

    const totalRatings = (await this.ratingRepository.getByParams({
      where: {
        rated_id: id,
        rating_type: RATING_TYPES.DRIVER_TO_CUSTOMER,
      },
      getCountOnly: true,
    })) as number

    return successResponse(
      code.SUCCESS,
      isCustomer
        ? messageKey.customer_not_found
        : successMessage(messageKey.detail_found, {
            ":data": "Customer",
          }),
      {
        ...customerDetail,
        pricing_state: customerDetail.pricing_city?.state || null,
        pricing_country: customerDetail.pricing_city?.country || null,
        need_help_contact: CONTACT,
        average_rating: averageRating || 0,
        total_ratings: totalRatings || 0,
      },
    )
  }

  async update(
    id: number,
    updateCustomerDto: UpdateCustomerDto,
    files?: {
      profile_photo?: Express.Multer.File
      letter_of_guarantee?: Express.Multer.File
    },
    isCustomer?: boolean,
  ) {
    const customer = (await this.customerRepository.getByParams({
      where: {
        id: id,
      },
      findOne: true,
    })) as Customer

    if (isEmpty(customer)) {
      return failureResponse(
        code.VALIDATION,
        isCustomer
          ? messageKey.customer_not_found
          : errorMessage(messageKey.data_not_found, {
              ":data": "Customer",
            }),
      )
    }

    const originalPhoneNumber = customer.phone_number
    const originalCountryCode = customer.country_code
    const originalPricingCityId = customer.pricing_city_id

    // Convert all foreign key fields with value `0` to `null`
    // Reason: In the frontend, `0` is often used to represent "no selection"
    // But in the database, we want to store it as `NULL` to avoid FK constraint issues or invalid references

    const nullableFields = [
      "client_company_id",
      "city_id",
      "state_id",
      "country_id",
      "client_contact_id",
      "hospital_id",
      "vehicle_type_id",
      "dispatcher_id",
    ] as const

    for (const field of nullableFields) {
      if (updateCustomerDto[field] === 0) {
        updateCustomerDto[field] = null
      }
    }

    if (updateCustomerDto?.current_step === 1) {
      if (!isEmpty(updateCustomerDto?.client_company_id)) {
        const isClientExist = (await this.clientCompanyRepository.getByParams({
          where: {
            id: updateCustomerDto?.client_company_id,
          },
          findOne: true,
        })) as any

        if (isEmpty(isClientExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.client_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "Client",
                }),
          )
        }

        // if (isClientExist?.is_medical_patient === true) {
        //   if (isEmpty(updateCustomerDto?.client_contact_id)) {
        //     return failureResponse(
        //       code.BAD_REQUEST,
        //       messageKey.client_contact_required_if_client_selected,
        //     )
        //   }
        // }

        // const isClientContactExist =
        //   await this.clientContactRepository.getByParams({
        //     where: {
        //       id: updateCustomerDto?.client_contact_id,
        //     },
        //     findOne: true,
        //   })

        // if (
        //   isClientExist?.is_medical_patient === true &&
        //   isEmpty(isClientContactExist)
        // ) {
        //   return failureResponse(
        //     code.VALIDATION,
        //     errorMessage(messageKey.data_not_found, {
        //       ":data": "Client contact",
        //     }),
        //   )
        // }
      }
    } else if (updateCustomerDto?.current_step === 2) {
      const isCountryExist = await this.countryRepository.getByParams({
        where: {
          id: updateCustomerDto?.country_id,
        },
        findOne: true,
      })

      if (updateCustomerDto?.country_id === 0) {
        if (isEmpty(isCountryExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.country_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "Country",
                }),
          )
        }
      }

      const isStateExist = await this.stateRepository.getByParams({
        where: {
          id: updateCustomerDto?.state_id,
        },
        findOne: true,
      })

      if (updateCustomerDto?.state_id === 0) {
        if (isEmpty(isStateExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.state_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "State",
                }),
          )
        }
      }

      const isCityExist = await this.cityRepository.getByParams({
        where: {
          id: updateCustomerDto?.city_id,
        },
        findOne: true,
      })

      if (updateCustomerDto?.city_id === 0) {
        if (isEmpty(isCityExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.city_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "City",
                }),
          )
        }
      }

      const isPricingCityExist = await this.cityRepository.getByParams({
        where: { id: updateCustomerDto?.pricing_city_id },
        findOne: true,
      })

      if (
        updateCustomerDto?.pricing_city_id === 0 &&
        isEmpty(isPricingCityExist)
      ) {
        return failureResponse(
          code.VALIDATION,
          errorMessage(messageKey.data_not_found, { ":data": "Pricing City" }),
        )
      }

      const dispatcherRoleId: any = await this.roleRepository.getByParams({
        whereLower: {
          name: "dispatcher",
        },
        findOne: true,
        select: ["id"],
      })

      const isDispatcherExist = (await this.teamRepository.getByParams({
        where: {
          id: updateCustomerDto?.dispatcher_id,
          role_id: dispatcherRoleId?.id,
        },
        findOne: true,
        select: ["id", "first_name", "last_name", "role_id"],
        relations: ["users:id,email,first_name,last_name"],
      })) as TeamMember

      if (updateCustomerDto?.dispatcher_id === 0) {
        if (isEmpty(isDispatcherExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.dispatcher_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "Dispatcher",
                }),
          )
        }
      }

      await this.customerRepository.getByParams({
        where: {
          phone_number: updateCustomerDto?.phone_number?.trim(),
        },
        whereNotIn: {
          id: [id],
        },
        findOne: true,
      })

      const isEmail = await this.customerRepository.getByParams({
        whereLower: {
          email: updateCustomerDto?.email?.trim(),
        },
        whereNotIn: {
          id: [id],
        },
        findOne: true,
      })

      if (!isEmpty(isEmail)) {
        return failureResponse(
          code.VALIDATION,
          isCustomer
            ? messageKey.email_already_exist
            : validationMessage(messageKey.already_exist, {
                ":data": "Email",
              }),
        )
      }
    } else if (updateCustomerDto?.current_step === 3) {
      if (!isEmpty(updateCustomerDto?.hospital_id)) {
        const isPrnNumberExist = await this.customerRepository.getByParams({
          where: {
            prn_number: updateCustomerDto?.prn_number,
          },
          whereNotIn: {
            id: [id],
          },
        })

        if (!isEmpty(isPrnNumberExist)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.card_pan_number_already_exist
              : validationMessage(messageKey.already_exist, {
                  ":data": "Customer PRN number",
                }),
          )
        }

        const hospital = await this.hospitalRepository.getByParams({
          where: {
            id: updateCustomerDto?.hospital_id,
          },
          findOne: true,
        })

        if (isEmpty(hospital)) {
          return failureResponse(
            code.VALIDATION,
            isCustomer
              ? messageKey.hospital_not_found
              : errorMessage(messageKey.data_not_found, {
                  ":data": "Hospital",
                }),
          )
        }
      }

      if (!isEmpty(updateCustomerDto?.vehicle_type_id)) {
        const vehicleType = await this.vehicleTypeRepository.getByParams({
          where: {
            id: updateCustomerDto?.vehicle_type_id,
          },
        })

        if (isEmpty(vehicleType)) {
          return failureResponse(
            code.VALIDATION,
            errorMessage(messageKey.data_not_found, {
              ":data": "Vehicle type",
            }),
          )
        }
      }
    }

    const cleanedPayload = await this.getStepWisePayload(
      updateCustomerDto.current_step,
      updateCustomerDto,
      files,
    )

    Object.assign(customer, cleanedPayload)

    // Tags
    if (!isEmpty(updateCustomerDto.tags)) {
      const tagIdArray = Array.isArray(updateCustomerDto.tags)
        ? updateCustomerDto.tags
        : String(updateCustomerDto.tags)
            .split(",")
            .map((id) => Number(id.trim()))

      const tags: any = await this.tagRepository.getByParams({
        whereIn: { id: tagIdArray },
        select: ["id"],
      })

      customer.tags = tags
    }

    const oldDispatcherId = customer.dispatcher_id
    const customerData = await this.customerRepository.save(customer)

    // If login identifier (phone number) changed, logout customer from all sessions
    const phoneChanged =
      (typeof cleanedPayload.phone_number !== "undefined" &&
        cleanedPayload.phone_number !== originalPhoneNumber) ||
      (typeof cleanedPayload.country_code !== "undefined" &&
        cleanedPayload.country_code !== originalCountryCode)

    if (phoneChanged) {
      // Logout only mobile app sessions (android/ios), web (if any) unaffected
      await this.customerLoginRepository.remove(
        { customer_id: customer.id, device_type: In(["android", "ios"]) },
        undefined,
        false,
      )
    }

    // Notifications for dispatcher assignment
    const newDispatcherId = customerData.dispatcher_id

    console.log(
      `[Customer Update] Customer ID: ${customerData.id}, Old Dispatcher ID: ${oldDispatcherId}, New Dispatcher ID: ${newDispatcherId}`,
    )

    // Send notification if dispatcher changed (different from old) or is being assigned for first time
    // But NOT if dispatcher is the same
    if (newDispatcherId && newDispatcherId !== oldDispatcherId) {
      console.log(
        `[Customer Update] ✅ Dispatcher changed from ${oldDispatcherId} to ${newDispatcherId} - Sending notification`,
      )
      const dispatcherRoleId: any = await this.roleRepository.getByParams({
        whereLower: {
          name: "dispatcher",
        },
        findOne: true,
        select: ["id"],
      })

      if (!dispatcherRoleId) {
        console.error("Dispatcher role not found")
      } else {
        const isDispatcherExist = (await this.teamRepository.getByParams({
          where: {
            id: newDispatcherId,
            role_id: dispatcherRoleId?.id,
          },
          findOne: true,
          relations: ["users"],
        })) as TeamMember

        if (!isDispatcherExist) {
          console.error(`Dispatcher with id ${newDispatcherId} not found`)
        } else if (
          !isDispatcherExist.users ||
          isDispatcherExist.users.length === 0
        ) {
          console.error(
            `Dispatcher with id ${newDispatcherId} has no associated users`,
          )
        } else {
          // Get all user_ids for this dispatcher
          const dispatcherUserIds = isDispatcherExist.users.map(
            (user) => user.id,
          )

          if (dispatcherUserIds.length > 0) {
            console.log(
              `[Customer Update] Sending notification to user_ids: ${dispatcherUserIds.join(", ")} for dispatcher team_member_id: ${newDispatcherId}`,
            )
            await this.notificationService.sendNotification({
              user_ids: dispatcherUserIds,
              title: "New Customer assigned to you",
              message: formatMessage("newCustomerAssigned", {
                CUSTOMER: `"${customerData.customer_name}"`,
              }),
              data: {
                type: "customer",
                customer_id: customerData.id,
              },
            })
            console.log(
              `[Customer Update] ✅ Notification sent successfully to user_ids: ${dispatcherUserIds.join(", ")} for customer ${customerData.id}`,
            )
          } else {
            console.error(
              `[Customer Update] No user_ids found for dispatcher team_member_id: ${newDispatcherId}`,
            )
          }
        }
      }
    } else if (newDispatcherId === oldDispatcherId && newDispatcherId) {
      console.log(
        `[Customer Update] ⏭️  Dispatcher is the same (${newDispatcherId}) - No notification sent (as expected)`,
      )
    } else if (!newDispatcherId) {
      console.log(
        `[Customer Update] ⏭️  No dispatcher assigned - No notification sent`,
      )
    }

    // Update pricing city in all draft trips when customer's pricing city changes
    const newPricingCityId = customerData.pricing_city_id
    if (newPricingCityId && originalPricingCityId !== newPricingCityId) {
      await this.tripEntityRepository
        .createQueryBuilder()
        .update(Trip)
        .set({ city_id: newPricingCityId })
        .where("customer_id = :customerId", { customerId: customerData.id })
        .andWhere("status IN (:...tripStatuses)", {
          tripStatuses: [STATUS.ACTIVE, STATUS.REQUESTED_BY_CUSTOMER],
        })
        .execute()
    }

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

  async getStepWisePayload(
    step: number,
    dto: UpdateCustomerDto,
    files?: {
      profile_photo?: Express.Multer.File
      letter_of_guarantee?: Express.Multer.File
    },
  ) {
    const payload: any = {}

    if (dto.status) {
      payload.status = dto.status
    }

    if (step === 1) {
      Object.assign(payload, {
        client_company_id:
          dto.client_company_id === 0 ? null : dto.client_company_id,
        client_contact_id:
          dto.client_contact_id === 0 ? null : dto.client_contact_id,
        current_step: dto.current_step,
        customer_type: dto?.customer_type || null,
      })
    }

    if (step === 2) {
      if (dto.customer_name !== undefined) {
        payload.customer_name = dto.customer_name
      }
      if (dto.date_of_birth !== undefined) {
        payload.date_of_birth = dto.date_of_birth
      }
      if (dto.gender !== undefined) {
        payload.gender = dto.gender
      }
      if (dto.primary_address !== undefined) {
        payload.primary_address = dto.primary_address
      }
      if (dto.secondary_address !== undefined) {
        payload.secondary_address = dto.secondary_address
      }
      if (dto.city_id !== undefined) {
        payload.city_id = dto.city_id === 0 ? null : dto.city_id
      }
      if (dto.state_id !== undefined) {
        payload.state_id = dto.state_id === 0 ? null : dto.state_id
      }
      if (dto.country_id !== undefined) {
        payload.country_id = dto.country_id === 0 ? null : dto.country_id
      }
      if (dto.dispatcher_id !== undefined) {
        payload.dispatcher_id =
          dto.dispatcher_id === 0 ? null : dto.dispatcher_id
      }
      if (dto.zip_code !== undefined) {
        payload.zip_code = dto.zip_code
      }
      if (dto.email !== undefined) {
        payload.email = dto.email?.trim() || ""
      }
      if (dto.phone_number !== undefined) {
        payload.phone_number = dto.phone_number?.trim() || ""
      }
      if (dto.country_code !== undefined) {
        payload.country_code = dto.country_code
      }
      if (dto.tags !== undefined) {
        payload.tags = dto.tags
      }
      if (dto.visa_start_date !== undefined) {
        payload.visa_start_date = dto.visa_start_date
      }
      if (dto.visa_end_date !== undefined) {
        payload.visa_end_date = dto.visa_end_date
      }
      if (dto.latitude !== undefined) {
        payload.latitude = dto.latitude
      }
      if (dto.longitude !== undefined) {
        payload.longitude = dto.longitude
      }
      if (dto.place_id !== undefined) {
        payload.place_id = dto.place_id
      }
      if (dto.pricing_city_id !== undefined) {
        payload.pricing_city_id = dto.pricing_city_id
      }
      if (dto.current_step !== undefined) {
        payload.current_step = dto.current_step
      }

      if (files?.profile_photo) {
        payload.profile_photo = `${fileStoreLocation.customer_profile_photo}/${files.profile_photo.filename}`
      }
    }

    if (step === 3) {
      Object.assign(payload, {
        hospital_id: dto?.hospital_id === 0 ? null : dto?.hospital_id,
        prn_number: dto?.prn_number,
        department: dto?.department,
        job_title: dto?.job_title,
        vehicle_type_id:
          dto?.vehicle_type_id === 0 ? null : dto?.vehicle_type_id,
        current_step: dto?.current_step,
      })

      if (files?.letter_of_guarantee) {
        payload.letter_of_guarantee = `${fileStoreLocation.letter_of_guarantee}/${files.letter_of_guarantee.filename}`
      } else if (dto.letter_of_guarantee === "") {
        payload.letter_of_guarantee = null
      }
    }

    return payload
  }

  async remove(id: number) {
    const customerDetail = await this.customerRepository.getByParams({
      where: {
        id: id,
      },
      findOne: true,
    })

    if (isEmpty(customerDetail)) {
      return failureResponse(
        code.BAD_REQUEST,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer",
        }),
      )
    }

    return await this.dataSource.transaction(async (manager) => {
      // 1. Get ALL episode IDs directly (flat array)
      const episodeIds: any = await this.customerEpisodeRepository.getByParams({
        where: { customer_id: id },
        select: ["id"],
      })

      const episodeIdList = episodeIds.map((e) => e.id)

      // 2. Delete all logs (NO LOOP, NO MAP)
      if (episodeIdList.length > 0) {
        await this.episodeLogRepository.remove(
          { episode_id: In(episodeIdList) },
          manager,
          false,
        )
      }

      // 3. Delete episodes
      await this.customerEpisodeRepository.remove(
        { customer_id: id },
        manager,
        false,
      )

      // 4. Delete customer's phone numbers
      await this.customerPhoneNumberRepository.remove(
        { customer_id: id },
        manager,
        false,
      )

      // 5. Delete the customer
      await this.customerRepository.remove({ id }, manager, false)

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

  async checkTagExist(name: any, type: any) {
    const isTagExist = await this.tagRepository.getByParams({
      where: {
        name: name,
        type: type,
      },
      findOne: true,
    })

    return isEmpty(isTagExist)
  }

  async createTag(tagData: CustomerTag) {
    const isTagExist = await this.checkTagExist(tagData?.name, tagData?.type)

    if (isTagExist) {
      const tag = new CustomerTag()
      Object.assign(tag, tagData)

      await this.tagRepository.save(tag)
    }
  }

  async getTags(type: any) {
    const tags = await this.tagRepository.getByParams({
      where: {
        type: type,
      },
      select: ["id", "name"],
    })

    return tags
  }

  // async addPhoneNumber(
  //   customer_id: number,
  //   phoneNumberDto: SaveCustomerPhoneNumbersDto,
  // ) {
  //   const customer = await this.customerRepository.getByParams({
  //     where: { id: customer_id },
  //     findOne: true,
  //   })

  //   if (isEmpty(customer)) {
  //     return failureResponse(
  //       code.BAD_REQUEST,
  //       errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
  //     )
  //   }

  //   const phoneEntities = phoneNumberDto.phone_numbers.map((phone) => {
  //     const entity = new CustomerPhoneNumbers()
  //     Object.assign(entity, {
  //       ...phone,
  //       customer_id: customer_id,
  //     })
  //     return entity
  //   })

  //   const isPhoneNumberExist =
  //     await this.customerPhoneNumberRepository.getByParams({
  //       where: {
  //         customer_id: customer_id,
  //       },
  //     })

  //   const savedPhoneNumbers =
  //     await this.customerPhoneNumberRepository.saveMany(phoneEntities)

  //   const is_primary = savedPhoneNumbers.find(
  //     (phone) => phone.is_primary === true,
  //   )

  //   if (is_primary) {
  //     await this.customerRepository.save({
  //       id: customer_id,
  //       phone_number: is_primary.phone_number,
  //       country_code: is_primary.country_code,
  //     })
  //   }

  //   return successResponse(
  //     code.SUCCESS,
  //     successMessage(messageKey.data_add, { ":data": "Phone number" }),
  //     savedPhoneNumbers,
  //   )
  // }

  async getPhoneNumbers(customer_id: number) {
    const customer = await this.customerRepository.getByParams({
      where: { id: customer_id },
      findOne: true,
    })

    if (isEmpty(customer)) {
      return failureResponse(
        code.BAD_REQUEST,
        errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
      )
    }

    const phoneNumbers = await this.customerPhoneNumberRepository.getByParams({
      where: {
        customer_id: customer_id,
      },
    })

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Phone numbers" }),
      phoneNumbers,
    )
  }

  async updatePhoneNumber(
    customer_id: number,
    phoneNumberDto: SaveCustomerPhoneNumbersDto,
  ) {
    const customer = await this.customerRepository.getByParams({
      where: { id: customer_id },
      findOne: true,
    })

    if (isEmpty(customer)) {
      return failureResponse(
        code.BAD_REQUEST,
        errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
      )
    }

    for (const phone of phoneNumberDto.phone_numbers) {
      const existingPhone =
        (await this.customerPhoneNumberRepository.getByParams({
          where: {
            phone_number: phone.phone_number?.trim(),
          },
          findOne: true,
        })) as any

      if (
        !isEmpty(existingPhone) &&
        existingPhone.customer_id !== customer_id
      ) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": `Phone number ${phone.phone_number}`,
          }),
        )
      }
    }

    const primaryCount = phoneNumberDto.phone_numbers.filter(
      (phone) => phone.is_primary === true,
    ).length

    if (primaryCount > 1) {
      return failureResponse(
        code.VALIDATION,
        "Only one phone number can be marked as primary",
      )
    }

    const phoneEntities = phoneNumberDto.phone_numbers.map((phone) => {
      const entity = new CustomerPhoneNumbers()
      Object.assign(entity, {
        ...phone,
        customer_id: customer_id,
      })
      return entity
    })

    await this.customerPhoneNumberModuleRepository.delete({
      customer_id: customer_id,
    })

    const savedPhoneNumbers =
      await this.customerPhoneNumberRepository.saveMany(phoneEntities)

    const is_primary = savedPhoneNumbers.find(
      (phone) => phone.is_primary === true,
    )

    if (is_primary) {
      const existingCustomer = customer as Customer

      const phoneChanged =
        is_primary.phone_number !== existingCustomer.phone_number ||
        is_primary.country_code !== existingCustomer.country_code

      await this.customerRepository.save({
        id: customer_id,
        phone_number: is_primary.phone_number,
        country_code: is_primary.country_code,
      })

      if (phoneChanged) {
        await this.customerLoginRepository.remove({
          customer_id,
          device_type: In(["android", "ios"]),
        })
      }
    }
    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_add, { ":data": "Phone number" }),
    )
  }

  async getRating(customerId: number) {
    try {
      const averageRating = await this.ratingRepository.getAverageRating({
        rated_id: customerId,
        rating_type: RATING_TYPES.DRIVER_TO_CUSTOMER,
      })

      const ratingCount = (await this.ratingRepository.getByParams({
        where: {
          rated_id: customerId,
          rating_type: RATING_TYPES.DRIVER_TO_CUSTOMER,
        },
        getCountOnly: true,
      })) as number

      const ratingsWithTags = (await this.ratingRepository.getByParams({
        where: {
          rated_id: customerId,
          rating_type: RATING_TYPES.DRIVER_TO_CUSTOMER,
        },
      })) as Rating[]

      const tagCount: Record<string, number> = {}
      ratingsWithTags.forEach((r) => {
        if (Array.isArray(r.tags)) {
          r.tags.forEach((tag) => {
            tagCount[tag] = (tagCount[tag] || 0) + 1
          })
        }
      })

      const topTags = Object.entries(tagCount)
        .sort((a, b) => b[1] - a[1])
        .slice(0, 3)
        .map(([tag]) => tag)

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Rating",
        }),
        { ratingCount, averageRating, topTags },
      )
    } catch (error) {
      return failureResponse(code.ERROR, messageKey.something_went_wrong)
    }
  }

  generateRandomString(length: number): string {
    const chars =
      "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()_+=-[]{}|;:,.<>?"
    let result = ""
    for (let i = 0; i < length; i++) {
      result += chars.charAt(Math.floor(Math.random() * chars.length))
    }
    return result
  }

  generateToken(): string {
    const randomString = this.generateRandomString(200)
    const encodedString = Buffer.from(randomString).toString("base64")
    const timestamp = moment().format()
    const stringWithTimestamp = encodedString + timestamp.toString()
    return Buffer.from(stringWithTimestamp).toString("base64")
  }

  generateRefreshToken(): string {
    return randomBytes(64).toString("hex")
  }

  private getRefreshTokenExpiry(): Date {
    return moment.utc().add(7, "days").toDate()
  }

  async getCustomerByToken(token: string): Promise<CustomerLogin | null> {
    const customerLogin = (await this.customerLoginRepository.getByParams({
      where: { access_token: token },
      relations: ["customer:id"],
      findOne: true,
    })) as CustomerLogin

    if (customerLogin) {
      return customerLogin
    }

    return null
  }

  async customerMobileLogin(username: CustomerMobileLoginDto) {
    let user: any

    if (username.email) {
      user = await this.customerRepository.getByParams({
        whereLower: { email: username.email?.trim() || "" },
        findOne: true,
      })
    } else if (username.phone_number) {
      user = await this.customerRepository.getByParams({
        where: {
          country_code: username.country_code,
          phone_number: username.phone_number?.trim() || "",
        },
        findOne: true,
      })
    }

    if (isEmpty(user)) {
      return failureResponse(code.VALIDATION, messageKey.user_not_found)
    }

    // const otp = Math.floor(100000 + Math.random() * 900000).toString()
    // const now = new Date()
    // const expiresAt = new Date(now.getTime() + 2 * 60 * 1000)

    let otp: string
    let expiresAt: Date

    if ((user.email?.trim()?.toLowerCase() || "") === "jack87@yopmail.com") {
      // Fixed OTP for dispatcher and longer expiry if desired
      otp = "123456"
      expiresAt = new Date(Date.now() + 10 * 60 * 1000)
    } else {
      otp = Math.floor(100000 + Math.random() * 900000).toString()
      expiresAt = new Date(Date.now() + 2 * 60 * 1000)
    }

    user.customer_otp = otp
    user.customer_otp_expires_at = expiresAt

    await this.customerRepository.save(user, { customer_otp: otp })

    user.device_id = username.device_id
    user.device_type = username.device_type
    user.os_version = username.os_version

    const accessToken = this.generateToken()
    const refreshToken = this.generateRefreshToken()
    const accessTokenExpiry = moment
      .utc()
      .add(appSetting.access_token_expiry_in_minutes, "minutes")
      .format()
    const refreshTokenExpiry = this.getRefreshTokenExpiry()

    if (user) {
      // Check if user already has an existing login session for this device
      const existingLogin = (await this.customerLoginRepository.getByParams({
        where: {
          customer_id: user.id,
        },
        findOne: true,
      })) as CustomerLogin

      if (existingLogin) {
        await this.customerLoginRepository.save(
          {
            ...existingLogin,
            device_type: username.device_type,
            device_token: username.device_token,
            os_version: username.os_version,
            access_token: accessToken,
            access_token_expire_at: accessTokenExpiry,
            refresh_token: refreshToken,
            refresh_token_expire_at: refreshTokenExpiry,
            deleted_at: null,
          },
          { id: existingLogin.id },
        )
      } else {
        // Create new session
        await this.customerLoginRepository.save({
          customer_id: user.id,
          device_id: username.device_id,
          device_type: username.device_type,
          device_token: username.device_token,
          os_version: username.os_version,
          access_token: accessToken,
          access_token_expire_at: accessTokenExpiry,
          refresh_token: refreshToken,
          refresh_token_expire_at: refreshTokenExpiry,
          deleted_at: null,
        })
      }
    }

    if (username.email) {
      sendEmailNotification(
        user.email,
        sendOtpEmailTemplate(otp),
        mailSubject.otpSubject,
      )
    } else if (username.phone_number) {
      // Send OTP via SMS
      try {
        if (user.phone_number && user.country_code) {
          const phone = `${user.country_code}${user.phone_number}`
          await sendOtpViaSms(phone, otp)
        }
      } catch (smsError) {
        console.log("SMS sending failed:", smsError.message)
      }
    }

    return successResponse(code.SUCCESS, messageKey.otp_sent, {
      user_id: user.id,
      is_document: user.is_document,
    })
  }

  async verifyOtp(otp: string, user_id: number, fcm_token?: string) {
    const user = (await this.customerRepository.getByParams({
      where: {
        id: user_id,
        customer_otp: otp,
      },
      findOne: true,
    })) as any

    if (isEmpty(user)) {
      return failureResponse(code.VALIDATION, messageKey.invalid_otp)
    }

    if (user.customer_otp_expires_at < new Date()) {
      return failureResponse(code.VALIDATION, messageKey.invalid_otp)
    }

    user.customer_otp = null
    user.customer_otp_expires_at = null

    const accessToken = this.generateToken()
    const refreshToken = this.generateRefreshToken()
    const accessTokenExpiry = moment
      .utc()
      .add(appSetting.access_token_expiry_in_minutes, "minutes")
      .format()
    const refreshTokenExpiry = this.getRefreshTokenExpiry()

    const existingLogin = (await this.customerLoginRepository.getByParams({
      where: {
        customer_id: user.id,
      },
      findOne: true,
    })) as CustomerLogin

    // Update with FCM token from header
    await this.customerLoginRepository.save(
      {
        access_token: accessToken,
        access_token_expire_at: accessTokenExpiry,
        refresh_token: refreshToken,
        refresh_token_expire_at: refreshTokenExpiry,
        ...(fcm_token && { fcm_token }), // Add FCM token if provided in header
      },
      { id: existingLogin.id },
    )

    await this.customerRepository.save(user)

    const responseData = {
      id: user.id,
      customer_name: user.customer_name,
      email: user.email,
      country_code: user.country_code,
      phone_number: user.phone_number,
      date_of_birth: user.date_of_birth,
      gender: user.gender,
      primary_address: user.primary_address,
      department: user.department,
      job_title: user.job_title,
      is_medical_tourist: user.is_medical_tourist,
      access_token: accessToken,
      access_token_expire_at: accessTokenExpiry,
      refresh_token: refreshToken,
      refresh_token_expire_at: refreshTokenExpiry,
      fcm_token,
    }

    return successResponse(code.SUCCESS, messageKey.otp_verified, responseData)
  }

  async customerLogout(token: string) {
    const loggedInCustomer = (await this.customerLoginRepository.getByParams({
      where: { access_token: token },
      findOne: true,
    })) as CustomerLogin

    if (isEmpty(loggedInCustomer)) {
      return failureResponse(code.SUCCESS, messageKey.data_not_found)
    }

    await this.customerLoginRepository.remove({ id: loggedInCustomer.id })

    return successResponse(code.SUCCESS, messageKey.logout_success)
  }

  async refreshCustomerAccessToken(refreshToken: string) {
    const customerLogin = (await this.customerLoginRepository.getByParams({
      where: { refresh_token: refreshToken },
      findOne: true,
    })) as CustomerLogin

    if (!customerLogin) {
      return failureResponse(code.UNAUTHORIZED, messageKey.invalid_credentials)
    }

    // Check if refresh token expired
    if (customerLogin.refresh_token_expire_at < new Date()) {
      await this.customerLoginRepository.save(
        { refresh_token: null, refresh_token_expire_at: null },
        { id: customerLogin.id },
      )
      return failureResponse(code.UNAUTHORIZED, messageKey.token_expire)
    }

    // Check if customer is still active
    const customer = (await this.customerRepository.getByParams({
      where: { id: customerLogin.customer_id },
      findOne: true,
    })) as Customer

    if (!customer) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
      )
    }

    // Generate new tokens
    const newAccessToken = this.generateToken()
    const newRefreshToken = this.generateRefreshToken()
    const accessTokenExpiry = moment
      .utc()
      .add(appSetting.access_token_expiry_in_minutes, "minutes")
      .format()
    const refreshTokenExpiry = this.getRefreshTokenExpiry()

    await this.customerLoginRepository.save(
      {
        access_token: newAccessToken,
        access_token_expire_at: accessTokenExpiry,
        refresh_token: newRefreshToken,
        refresh_token_expire_at: refreshTokenExpiry,
      },
      { id: customerLogin.id },
    )

    return successResponse(code.SUCCESS, messageKey.login_success, {
      access_token: newAccessToken,
      access_token_expire_at: accessTokenExpiry,
      refresh_token: newRefreshToken,
      refresh_token_expire_at: refreshTokenExpiry,
    })
  }

  // Customer Episode Methods
  async createCustomerEpisode(
    customerId: number,
    createCustomerEpisodeDto: CreateCustomerEpisodeDto,
  ) {
    // Verify customer exists
    const customer = (await this.customerRepository.getByParams({
      where: { id: customerId },
      findOne: true,
    })) as Customer

    if (!customer) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
      )
    }

    // ✅ Check if episode number already exists for same customer
    const existingEpisode = await this.customerEpisodeRepository.getByParams({
      where: {
        customer_id: customerId,
        episode_number: createCustomerEpisodeDto.episode_number,
      },
      findOne: true,
    })

    if (!isEmpty(existingEpisode)) {
      return failureResponse(
        code.VALIDATION,
        validationMessage(messageKey.already_exist, {
          ":data": "Episode number",
        }),
      )
    }

    // Create episode
    const customerEpisode = new CustomerEpisode()
    Object.assign(customerEpisode, createCustomerEpisodeDto)
    customerEpisode.customer_id = customerId

    const savedEpisode =
      await this.customerEpisodeRepository.save(customerEpisode)

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

  async findAllCustomerEpisodes(customerId: number) {
    // Verify customer exists
    const customer = (await this.customerRepository.getByParams({
      where: { id: customerId },
      findOne: true,
    })) as Customer

    if (!customer) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Customer" }),
      )
    }

    const episodes = (await this.customerEpisodeRepository.getByParams({
      where: { customer_id: customerId },
      relations: ["logs:hospital:id,name"],
      orderBy: { created_at: "DESC" },
    })) as CustomerEpisode[]

    const isCustomerActiveEpisode: any =
      await this.customerEpisodeRepository.getByParams({
        where: {
          customer_id: customerId,
          status: "active",
        },
      })

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, {
        ":data": "Customer episodes",
      }),
      // episodes,
      {
        episodes,
        is_active_episode: isCustomerActiveEpisode.length > 0,
      },
    )
  }

  // End Episode Method
  async endCustomerEpisode(episodeId: number) {
    try {
      const customerEpisode = (await this.customerEpisodeRepository.getByParams(
        {
          where: { id: episodeId },
          findOne: true,
        },
      )) as CustomerEpisode

      if (!customerEpisode) {
        return failureResponse(
          code.DATA_NOT_FOUND,
          errorMessage(messageKey.data_not_found, {
            ":data": "Customer episode",
          }),
        )
      }

      // Set the end date to current date
      customerEpisode.end_date = new Date()
      customerEpisode.status = "inactive"

      const updatedEpisode =
        await this.customerEpisodeRepository.save(customerEpisode)

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_update, { ":data": "Customer episode" }),
        updatedEpisode,
      )
    } catch (error) {
      return failureResponse(
        code.ERROR,
        errorMessage(messageKey.something_went_wrong),
      )
    }
  }

  async findOneCustomerEpisode(episodeId: number) {
    const customerEpisode = (await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      relations: ["customer", "logs:hospital:id,name"],
      findOne: true,
    })) as CustomerEpisode

    if (!customerEpisode) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, {
        ":data": "Customer episode",
      }),
      customerEpisode,
    )
  }

  async updateCustomerEpisode(
    episodeId: number,
    updateCustomerEpisodeDto: UpdateCustomerEpisodeDto,
  ) {
    const customerEpisode = (await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      findOne: true,
    })) as CustomerEpisode

    if (!customerEpisode) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    if (
      updateCustomerEpisodeDto?.end_date &&
      updateCustomerEpisodeDto?.status === "inactive"
    ) {
      const hasMissingInvoices = await this.hasUninvoicedTrips(episodeId)

      if (hasMissingInvoices) {
        return failureResponse(
          code.BAD_REQUEST,
          "Cannot end episode because some trips are not invoiced.",
        )
      }
    }
    Object.assign(customerEpisode, updateCustomerEpisodeDto)
    const updatedEpisode =
      await this.customerEpisodeRepository.save(customerEpisode)

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

  async removeCustomerEpisode(episodeId: number) {
    const customerEpisode = (await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      findOne: true,
    })) as CustomerEpisode

    if (!customerEpisode) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    await this.customerEpisodeRepository.remove({ id: episodeId })

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

  // Episode Log Methods
  async generateEpisodeLogUploadUrl(
    episodeId: number,
    generateUploadUrlDto: GenerateUploadUrlDto,
  ) {
    try {
      // Verify episode exists
      const episode = (await this.customerEpisodeRepository.getByParams({
        where: { id: episodeId },
        findOne: true,
      })) as CustomerEpisode

      if (!episode) {
        return failureResponse(
          code.DATA_NOT_FOUND,
          errorMessage(messageKey.data_not_found, {
            ":data": "Customer episode",
          }),
        )
      }

      // Validate filename
      if (!generateUploadUrlDto.filename.endsWith(".pdf")) {
        return failureResponse(
          code.VALIDATION,
          errorMessage(messageKey.invalid_file_type, {
            file_types: "pdf",
          }),
        )
      }

      // Generate file key
      const timestamp = Date.now()
      const sanitizedFileName = generateUploadUrlDto.filename
        .replace(/[^a-zA-Z0-9.-]/g, "_")
        .replace(/\s+/g, "_")
      const fileKey = `episode-logs/${episodeId}/${timestamp}-${sanitizedFileName}`

      // Generate presigned URL
      const presignedUrl =
        await this.cloudflareR2Service.generatePresignedUploadUrl(
          fileKey,
          generateUploadUrlDto.contentType || "application/pdf",
        )

      // Construct the public URL that will be available after upload
      const publicUrl = `${this.configService.get("CLOUDFLARE_R2")?.publicUrl || process.env.CLOUDFLARE_R2_PUBLIC_URL}/${fileKey}`

      return successResponse(
        code.SUCCESS,
        successMessage(messageKey.data_retrieve, {
          ":data": "Presigned upload URL",
        }),
        {
          uploadUrl: presignedUrl,
          fileKey: fileKey,
          publicUrl: publicUrl,
          expiresIn: 900, // 15 minutes
        },
      )
    } catch (error) {
      return failureResponse(
        code.ERROR,
        errorMessage(messageKey.something_went_wrong),
      )
    }
  }

  async checkEpisodeLogExist(
    logNumber?: string,
    referenceNumber?: string,
    excludeLogId?: number,
  ): Promise<boolean> {
    const whereLikeCondition: any = {}

    if (logNumber) {
      whereLikeCondition.log_number = logNumber
    }

    if (referenceNumber) {
      whereLikeCondition.reference_number = referenceNumber
    }

    const whereCondition: any = {}
    if (excludeLogId) {
      whereCondition["id"] = {
        not: excludeLogId,
      }
    }

    const episodeLog = await this.episodeLogRepository.getByParams({
      where: whereCondition,
      whereLower: whereLikeCondition,
      findOne: true,
    })

    return !isEmpty(episodeLog)
  }

  async createEpisodeLog(
    episodeId: number,
    createEpisodeLogDto: CreateEpisodeLogDto,
  ) {
    // Verify episode exists
    const episode = (await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      findOne: true,
    })) as CustomerEpisode

    if (!episode) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    if (createEpisodeLogDto.log_number) {
      const existingLog = await this.episodeLogRepository.getByParams({
        where: {
          log_number: createEpisodeLogDto.log_number,
        },
        findOne: true,
      })

      if (!isEmpty(existingLog)) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Log number",
          }),
        )
      }
    }

    if (createEpisodeLogDto.reference_number) {
      const existingLog = await this.episodeLogRepository.getByParams({
        where: {
          reference_number: createEpisodeLogDto.reference_number,
        },
      })

      if (!isEmpty(existingLog)) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Reference number",
          }),
        )
      }
    }

    // Validate log_document URL if provided
    if (
      createEpisodeLogDto.log_document &&
      !this.cloudflareR2Service.validateR2Url(createEpisodeLogDto.log_document)
    ) {
      return failureResponse(
        code.VALIDATION,
        errorMessage(messageKey.something_went_wrong, {
          ":error":
            "Invalid document URL. URL must be from authorized R2 bucket.",
        }),
      )
    }

    const episodeLog = new EpisodeLog()
    Object.assign(episodeLog, createEpisodeLogDto)
    episodeLog.episode_id = episodeId

    const savedLog = await this.episodeLogRepository.save(episodeLog)

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

  async findAllEpisodeLogs(episodeId: number) {
    // Verify episode exists
    const episode = (await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      findOne: true,
    })) as CustomerEpisode

    if (!episode) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    const logs = (await this.episodeLogRepository.getByParams({
      where: { episode_id: episodeId },
      relations: ["hospital:id,name"],
      orderBy: { created_at: "DESC" },
    })) as EpisodeLog[]

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

  async findOneEpisodeLog(logId: number) {
    const episodeLog = (await this.episodeLogRepository.getByParams({
      where: { id: logId },
      relations: ["episode", "hospital:id,name"],
      findOne: true,
    })) as EpisodeLog

    if (!episodeLog) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Episode log" }),
      )
    }

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, { ":data": "Episode log" }),
      episodeLog,
    )
  }

  async updateEpisodeLog(
    logId: number,
    updateEpisodeLogDto: UpdateEpisodeLogDto,
  ) {
    const episodeLog = (await this.episodeLogRepository.getByParams({
      where: { id: logId },
      findOne: true,
    })) as EpisodeLog

    if (!episodeLog) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Episode log" }),
      )
    }

    // Only check if log_number is being changed
    if (
      updateEpisodeLogDto.log_number &&
      updateEpisodeLogDto.log_number !== episodeLog.log_number
    ) {
      const isLogExist = await this.checkEpisodeLogExist(
        updateEpisodeLogDto.log_number,
        undefined,
        logId,
      )

      if (isLogExist) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Log number",
          }),
        )
      }
    }

    // Only check if reference_number is being changed
    if (
      updateEpisodeLogDto.reference_number &&
      updateEpisodeLogDto.reference_number !== episodeLog.reference_number
    ) {
      const isRefExist = await this.checkEpisodeLogExist(
        undefined,
        updateEpisodeLogDto.reference_number,
        logId,
      )

      if (isRefExist) {
        return failureResponse(
          code.VALIDATION,
          validationMessage(messageKey.already_exist, {
            ":data": "Reference number",
          }),
        )
      }
    }

    // If a new document URL is provided, validate it and delete the old one
    if (updateEpisodeLogDto.log_document) {
      // Validate new URL
      if (
        !this.cloudflareR2Service.validateR2Url(
          updateEpisodeLogDto.log_document,
        )
      ) {
        return failureResponse(
          code.VALIDATION,
          errorMessage(messageKey.something_went_wrong, {
            ":error":
              "Invalid document URL. URL must be from authorized R2 bucket.",
          }),
        )
      }

      // Delete old file from Cloudflare R2 if it exists and is different
      if (
        episodeLog.log_document &&
        episodeLog.log_document !== updateEpisodeLogDto.log_document
      ) {
        try {
          const oldFileKey = this.cloudflareR2Service.extractKeyFromUrl(
            episodeLog.log_document,
          )
          if (oldFileKey) {
            await this.cloudflareR2Service.deleteFile(oldFileKey)
          }
        } catch (deleteError) {
          // Log error but don't fail the update if deletion fails
          console.error("Failed to delete old file:", deleteError)
        }
      }
    }

    Object.assign(episodeLog, updateEpisodeLogDto)

    const updatedLog = await this.episodeLogRepository.save(episodeLog)

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

  async removeEpisodeLog(logId: number) {
    const episodeLog = (await this.episodeLogRepository.getByParams({
      where: { id: logId },
      findOne: true,
    })) as EpisodeLog

    if (!episodeLog) {
      return failureResponse(
        code.DATA_NOT_FOUND,
        errorMessage(messageKey.data_not_found, { ":data": "Episode log" }),
      )
    }

    await this.episodeLogRepository.remove({ id: logId })

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

  async getActiveEpisodeWithLogs(
    customerId: number,
    skip = 0,
    limit = 10,
    search?: string,
  ) {
    const query = this.EpisodeLogEntityRepository.createQueryBuilder("log")
      .select([
        "log.id",
        "log.episode_id",
        "log.log_number",
        "log.reference_number",
        "log.prn_number",
        "log.hospital_id",
        "log.created_at",
        "log.log_document",
        "hospital.name",
      ])
      .innerJoin(
        (episodeQuery) =>
          episodeQuery
            .from(CustomerEpisode, "episode")
            .where("episode.customer_id = :customerId", { customerId })
            .andWhere("episode.status = :status", { status: "active" })
            .limit(1),
        "episode",
        "episode.id = log.episode_id",
      )
      .leftJoin("log.hospital", "hospital")

    if (search) {
      query.andWhere(
        `(
        CAST(log.episode_id AS TEXT) ILIKE :search OR
        CAST(log.log_number AS TEXT) ILIKE :search OR
        CAST(log.reference_number AS TEXT) ILIKE :search OR
        CAST(log.prn_number AS TEXT) ILIKE :search OR
        CAST(hospital.name AS TEXT) ILIKE :search
      )`,
        { search: `%${search}%` },
      )
    }

    query.orderBy("log.created_at", "DESC").skip(skip).take(limit)

    const [data, total] = await query.getManyAndCount()

    return successResponse(
      code.SUCCESS,
      successMessage(messageKey.data_retrieve, {
        ":data": "Active Episode Logs",
      }),
      { count: total, data },
    )
  }

  async checkEpisodeInvoiceStatus(episodeId: any) {
    const isEpisodeExist = await this.customerEpisodeRepository.getByParams({
      where: { id: episodeId },
      findOne: true,
    })

    if (isEmpty(isEpisodeExist)) {
      return failureResponse(
        code.BAD_REQUEST,
        errorMessage(messageKey.data_not_found, {
          ":data": "Customer episode",
        }),
      )
    }

    const hasMissingInvoices = await this.hasUninvoicedTrips(episodeId)

    if (hasMissingInvoices) {
      return successResponse(
        code.SUCCESS,
        "All trips are invoiced for this episode.",
        { has_uninvoiced_trips: true },
      )
    } else {
      return successResponse(
        code.SUCCESS,
        "Some trips are not invoiced for this episode.",
        { has_uninvoiced_trips: false },
      )
    }
  }

  private async hasUninvoicedTrips(episodeId: number): Promise<boolean> {
    const count = await this.tripLogEntityRepository
      .createQueryBuilder("tl")
      .leftJoin(Trip, "t", "t.id = tl.trip_id")
      .leftJoin(
        InvoiceTrips,
        "it",
        "it.trip_id = tl.trip_id AND it.deleted_at IS NULL",
      )
      .where("tl.episode_id = :episodeId", { episodeId })
      .andWhere("t.status = :status", { status: STATUS.COMPLETED })
      .andWhere("it.trip_id IS NULL") // means invoice is missing
      .getCount()

    return count > 0 // if any missing → block
  }

  async bulkUpload(file: any) {
    const workbook = new ExcelJS.Workbook()
    await workbook.xlsx.load(file.buffer)
    const sheet = workbook.getWorksheet(1)

    const errors: any[] = []
    return await this.dataSource.transaction(async (manager) => {
      for (let rowIndex = 3; rowIndex <= sheet.actualRowCount; rowIndex++) {
        const row = sheet.getRow(rowIndex)

        const payload: any = {
          directCustomer: row.getCell(1).value,
          companyName: row.getCell(2).value,
          patientCoordinator: row.getCell(3).value,
          customerName: row.getCell(4).value,
          dob: row.getCell(5).value,
          gender: row.getCell(6).value,
          address: row.getCell(7).value,
          country: row.getCell(8).value,
          state: row.getCell(9).value,
          city: row.getCell(10).value,
          zip: row.getCell(11).value,
          phone: row.getCell(12).value,
          email: row.getCell(13).value,
          dispatcher: row.getCell(14).value,
          referenceNumber: row.getCell(15).value,
          prnNumber: row.getCell(16).value,
          episode: row.getCell(17).value,
          logNumber: row.getCell(18).value,
          logDate: row.getCell(19).value,
          logExpDate: row.getCell(20).value,
          hospital: row.getCell(21).value,
          visaStart: row.getCell(22).value,
          visaExp: row.getCell(23).value,
          pricingCity: row.getCell(24).value,
          pricingState: row.getCell(25).value,
          pricingCountry: row.getCell(26).value,
        }

        try {
          if (isEmpty(payload?.phone)) {
            throw new Error(`Phone number is required`)
          }

          if (!payload.phone.includes("-")) {
            throw new Error(
              `Phone number must be in the format <dialCode>-<phoneNumber>`,
            )
          }

          const [dialCode, phoneNumber] = payload?.phone.split("-")

          const isCustomerExist = await this.customerRepository.getByParams({
            where: {
              country_code: dialCode,
              phone_number: phoneNumber,
            },
            findOne: true,
          })

          if (!isEmpty(isCustomerExist)) {
            throw new Error(
              `Customer with phone number "${payload?.phone}" already exists`,
            )
          }

          if (!isEmpty(payload?.email)) {
            const isCustomerExist = await this.customerRepository.getByParams({
              where: {
                email: payload.email,
              },
              findOne: true,
            })

            if (!isEmpty(isCustomerExist)) {
              throw new Error(
                `Customer with email "${payload.email}" already exists`,
              )
            }
          }

          if (!payload?.directCustomer) {
            if (isEmpty(payload?.prnNumber)) {
              throw new Error(`PRN Number is required for Direct Customer`)
            }

            if (isEmpty(payload?.companyName)) {
              throw new Error(`Company Name is required for Direct Customer`)
            }

            const isCustomerPRNExist =
              await this.customerRepository.getByParams({
                where: {
                  prn_number: String(payload?.prnNumber).trim(),
                },
                findOne: true,
              })

            if (!isEmpty(isCustomerPRNExist)) {
              throw new Error(
                `Customer with PRN Number "${payload.prnNumber}" already exists`,
              )
            }

            const isCustomerReferenceNumberExist =
              await this.episodeLogRepository.getByParams({
                where: {
                  reference_number: String(payload?.referenceNumber).trim(),
                },
                findOne: true,
              })

            if (!isEmpty(isCustomerReferenceNumberExist)) {
              throw new Error(
                `Episode Log with Reference Number "${payload.referenceNumber}" already exists`,
              )
            }

            const isCustomerLogNumberExist =
              await this.episodeLogRepository.getByParams({
                where: {
                  log_number: String(payload?.logNumber).trim(),
                },
                findOne: true,
              })

            if (!isEmpty(isCustomerLogNumberExist)) {
              throw new Error(
                `Episode Log with Log Number "${payload.logNumber}" already exists`,
              )
            }
          }

          const role: any = await this.roleRepository.getByParams({
            whereLower: { name: "dispatcher" },
            findOne: true,
          })

          const nameParts = payload?.dispatcher.trim().split(/\s+/)

          const firstName = nameParts[0]
          const lastName = nameParts.slice(1).join(" ")

          const teamMember: any = await this.teamRepository.getByParams({
            where: {
              first_name: firstName,
              last_name: lastName,
              role_id: role?.id,
            },
            findOne: true,
          })

          if (isEmpty(teamMember)) {
            throw new Error(
              `Invalid Dispatcher: "${payload.dispatcher}" with role "Dispatcher" not found`,
            )
          }

          if (isEmpty(payload.address)) {
            throw new Error(`Address is required`)
          }

          const coordinate = await getLatLongFromAddress(payload?.address)

          const country: any = await this.countryRepository.getByParams({
            where: { name: payload.country },
            findOne: true,
          })

          if (isEmpty(country)) {
            throw new Error(`Invalid Country: "${payload.country}" not found`)
          }

          const state: any = await this.stateRepository.getByParams({
            where: { name: payload.state, country_id: country.id },
            findOne: true,
          })

          if (isEmpty(state)) {
            throw new Error(
              `Invalid State: "${payload.state}" does not belong to country "${payload.country}"`,
            )
          }

          const city: any = await this.cityRepository.getByParams({
            where: {
              name: payload.city,
              state_id: state.id,
              country_id: country.id,
            },
            findOne: true,
          })

          if (isEmpty(city)) {
            throw new Error(
              `Invalid City: "${payload.city}" not found in State "${payload.state}", Country "${payload.country}"`,
            )
          }

          const countryPricing: any = await this.countryRepository.getByParams({
            where: { name: payload.pricingCountry },
            findOne: true,
          })

          if (isEmpty(countryPricing)) {
            throw new Error(
              `Invalid Country: "${payload.pricingCountry}" not found`,
            )
          }

          const statePricing: any = await this.stateRepository.getByParams({
            where: {
              name: payload.pricingState,
              country_id: countryPricing.id,
            },
            findOne: true,
          })

          if (isEmpty(statePricing)) {
            throw new Error(
              `Invalid State: "${payload.statePricing}" does not belong to country "${payload.countryPricing}"`,
            )
          }

          const cityPricing: any = await this.cityRepository.getByParams({
            where: {
              name: payload?.pricingCity,
              state_id: statePricing.id,
              country_id: countryPricing.id,
            },
            findOne: true,
          })

          if (isEmpty(cityPricing)) {
            throw new Error(`Invalid Pricing City: "${payload.cityPricing}"`)
          }

          const hospital: any = await this.hospitalRepository.getByParams({
            where: { name: payload.hospital },
            findOne: true,
          })

          if (isEmpty(hospital)) {
            throw new Error(`Invalid Hospital: "${payload.hospital}" not found`)
          }

          const company: any = await this.clientCompanyRepository.getByParams({
            where: { company_name: payload?.companyName },
            findOne: true,
          })

          if (isEmpty(company)) {
            throw new Error(
              `Invalid Company: "${payload?.companyName}" not found`,
            )
          }

          let contact = null

          if (!isEmpty(payload?.patientCoordinator)) {
            const nameParts = payload?.patientCoordinator.trim().split(/\s+/)

            // Must have at least First + Last
            if (nameParts.length < 2) {
              throw new Error(
                `Invalid Patient Coordinator name: "${payload.patientCoordinator}". ` +
                  `Provide full name (first & last) or keep it empty.`,
              )
            }

            const firstName = nameParts[0]
            const lastName = nameParts.slice(1).join(" ")

            contact = await this.clientContactRepository.getByParams({
              where: {
                first_name: firstName,
                last_name: lastName,
                client_company_id: company?.id,
              },
              findOne: true,
            })

            if (isEmpty(contact)) {
              throw new Error(
                `Patient Coordinator "${payload.patientCoordinator}" not found under Company "${payload.companyName}"`,
              )
            }
          }

          // -------------------------
          // Create Customer
          // -------------------------
          const customer = await this.customerRepository.save(
            {
              is_medical_tourist: !payload?.directCustomer,
              customer_name: payload?.customerName,
              date_of_birth: payload?.dob,
              gender: payload?.gender,
              primary_address: payload?.address,
              zip_code: payload?.zip,
              country_code: dialCode,
              phone_number: phoneNumber,
              email: payload?.email,
              prn_number: payload?.prnNumber,
              dispatcher_id: teamMember?.id,
              visa_start_date: payload?.visaStart,
              visa_end_date: payload?.visaExp,
              country_id: country?.id,
              state_id: state?.id,
              city_id: city?.id,
              hospital_id: hospital?.id,
              client_company_id: company?.id,
              client_contact_id: contact?.id,
              latitude: coordinate ? coordinate?.latitude : null,
              longitude: coordinate ? coordinate?.longitude : null,
              pricing_city_id: cityPricing?.id,
            },
            undefined,
            manager,
          )

          await this.customerPhoneNumberRepository.save(
            {
              customer_id: customer?.id,
              is_primary: true,
              country_code: dialCode,
              phone_number: phoneNumber,
            },
            undefined,
            manager,
          )

          const episodeId = await this.customerEpisodeRepository.save(
            {
              customer_id: customer?.id,
              episode_number: payload?.episode,
              status: "active",
            },
            undefined,
            manager,
          )

          await this.episodeLogRepository.save(
            {
              episode_id: episodeId?.id,
              log_number: payload?.logNumber,
              reference_number: payload?.referenceNumber,
              prn_number: payload?.prnNumber,
              log_date: payload?.logDate,
              log_expiration: payload?.logExpDate,
              hospital_id: hospital?.id,
            },
            undefined,
            manager,
          )
        } catch (err) {
          // Save full error message including row index
          errors.push({
            row: rowIndex,
            ...payload,
            error: err instanceof Error ? err.message : String(err),
          })
        }
      }

      // -------------------------
      // Generate Error Excel (if any errors)
      // -------------------------

      if (errors.length > 0) {
        const errorWorkbook = new ExcelJS.Workbook()
        const ws = errorWorkbook.addWorksheet("Error Report")

        ws.columns = [
          { header: "Row", key: "row", width: 10 },
          { header: "Direct Customer", key: "directCustomer" },
          { header: "Company Name", key: "companyName" },
          { header: "Patient Co-Ordinator", key: "patientCoordinator" },
          { header: "Customer Name", key: "customerName" },
          { header: "Date of birth", key: "dob" },
          { header: "Gender", key: "gender" },
          { header: "Address", key: "address" },
          { header: "Country", key: "country" },
          { header: "State", key: "state" },
          { header: "City", key: "city" },
          { header: "Zip Code", key: "zip" },
          { header: "Phone Number", key: "phone" },
          { header: "Email", key: "email" },
          { header: "Assign Dispatcher", key: "dispatcher" },
          { header: "Reference Number", key: "referenceNumber" },
          { header: "PRN Number", key: "prnNumber" },
          { header: "Episode", key: "episode" },
          { header: "LOG Number", key: "logNumber" },
          { header: "LOG Date", key: "logDate" },
          { header: "LOG Expire Date", key: "logExpDate" },
          { header: "Hospital", key: "hospital" },
          { header: "Visa Start", key: "visaStart" },
          { header: "Visa Exp", key: "visaExp" },
          { header: "Pricing City", key: "pricingCity" },
          { header: "Pricing State", key: "pricingState" },
          { header: "Pricing Country", key: "pricingCountry" },
          { header: "Error", key: "error", width: 60 },
        ]

        errors.forEach((err) => ws.addRow(err))

        return await errorWorkbook.xlsx.writeBuffer()
      }

      return { success: true, message: "Import completed successfully" }
    })
  }
}