import {
  HttpException,
  HttpStatus,
  Inject,
  Injectable,
  InternalServerErrorException,
  Logger,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Brackets, DataSource, In, Repository } from 'typeorm';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
import { VisaServiceMasterEntity } from 'src/entities/VisaServiceMaster.entity';
import { CollegeEntity } from 'src/entities/College.entity';
import { SearchAssociatesDto } from './dto/search-associates-dto';
import { MasteragentEntity } from 'src/entities/Masteragent.entity';
import { B2bAffiliateAccountsEntity } from 'src/entities/B2bAffiliateAccounts.entity';
import { CountryEntity } from 'src/entities/Country.entity';
import { CollegeCampusEntity } from 'src/entities/CollegeCampus.entity';
import { SearchCollegeCampusDto } from './dto/search-college-campus-dto';
import { StudyLevelMasterEntity } from 'src/entities/StudyLevelMaster.entity';
import { VcmCommissionContractsEntity } from 'src/entities/VcmCommissionContracts.entity';
import { SearchVisaCommissionDto } from './dto/search-dto';
import {
  getSkipOffset,
  paginate,
} from 'src/config/pagination/pagination.service';
import { plainToInstance } from 'class-transformer';
import { CreateCommissionContractDto } from './dto/create-commission-contract.dto';
import { VcmCommissionContractSetsEntity } from 'src/entities/VcmCommissionContractSets.entity';
import { VcmCommissionContractInstancesEntity } from 'src/entities/VcmCommissionContractInstances.entity';
import { Response } from 'src/config/pagination/paginationDto';
import { CourseMasterEntity } from 'src/entities/CourseMaster.entity';
import { SearchCollegeCampusCoursesDto } from './dto/search-college-campus-courses-dto';
import { deepCompareObjectsSmart } from 'src/utils/deep-compare.util';
import { VcmCommissionContractsHistoryEntity } from 'src/entities/VcmCommissionContractsHistory.entity';
import { FileService } from 'src/services/file.service';
import { RemoveAttachmentDto } from './dto/remove-attachment.dto';
import { AddAttachmentDto } from './dto/add-attachment.dto';
import { UpdateContractStatusDto } from './dto/update-status.dto';
import { VcmCommissionContractApplicableOnEntity } from 'src/entities/VcmCommissionContractApplicableOn.entity';
import { UserDataProps } from 'src/utils/decorators/user-data.decorator';
import { CheckDuplicateContractDto } from './dto/check-duplicate-dto';
import { getISTDate } from 'src/utils/datetime/ist';

/**
 * VisaCommissionService - Core service for managing visa commission contracts
 *
 * This service handles the complete lifecycle of commission contracts for visa services,
 * including creation, editing, search, validation, and historical tracking. It supports
 * complex hierarchical contract structures with multi-level configurations.
 *
 * Business Domain: Visa Commission Contract Management System
 * - Manages commission agreements between the company and partners (institutes/agents)
 * - Supports hierarchical contract structures with sets and instances
 * - Tracks contract history and changes for audit purposes
 * - Handles file attachments and contract documentation
 * - Provides comprehensive search and filtering capabilities
 *
 * Key Features:
 * - Complex hierarchical contract management (Contract → Applicable Sets → Sets → Instances)
 * - Historical tracking with deep change detection
 * - Multi-association type support (Institute, Master Agent, Sub-Agent)
 * - Geographical and educational filtering capabilities
 * - File attachment management for contract documentation
 * - Duplicate contract validation
 */
@Injectable()
export class VisaCommissionService {
  // Logger instance for service-specific logging and debugging
  private logger = new Logger(VisaCommissionService.name);

  /**
   * Constructor with dependency injection for all required repositories and services
   * This service integrates with 13 different entities for comprehensive contract management
   */
  constructor(
    // DataSource for transaction management and complex queries
    private dataSource: DataSource,

    // FileService for handling contract document attachments
    private fileService: FileService,

    // Cache manager for performance optimization
    @Inject(CACHE_MANAGER) private cacheManager: Cache,

    // Repository injections for various entities

    // Core business entities
    @InjectRepository(CourseMasterEntity)
    private readonly courseRepo: Repository<CourseMasterEntity>,

    @InjectRepository(VisaServiceMasterEntity)
    private readonly vsmRepo: Repository<VisaServiceMasterEntity>,

    @InjectRepository(CollegeEntity)
    private readonly collegeRepo: Repository<CollegeEntity>,

    @InjectRepository(MasteragentEntity)
    private readonly masterAgentRepo: Repository<MasteragentEntity>,

    @InjectRepository(B2bAffiliateAccountsEntity)
    private readonly b2bAffiliatesAccountRepo: Repository<B2bAffiliateAccountsEntity>,

    @InjectRepository(CountryEntity)
    private readonly countryRepo: Repository<CountryEntity>,

    @InjectRepository(CollegeCampusEntity)
    private readonly campusRepo: Repository<CollegeCampusEntity>,

    @InjectRepository(StudyLevelMasterEntity)
    private readonly slMasterRepo: Repository<StudyLevelMasterEntity>,

    @InjectRepository(VcmCommissionContractsEntity)
    private readonly vcmRepo: Repository<VcmCommissionContractsEntity>,

    // Commission contract structure entities
    @InjectRepository(VcmCommissionContractSetsEntity)
    private readonly vcmSetRepo: Repository<VcmCommissionContractSetsEntity>,

    @InjectRepository(VcmCommissionContractsHistoryEntity)
    private readonly vcmHistoryRepo: Repository<VcmCommissionContractsHistoryEntity>,

    @InjectRepository(StudyLevelMasterEntity)
    private readonly studyLevelRepo: Repository<StudyLevelMasterEntity>,
  ) {}

  /**
   * Find all commission contracts with advanced filtering and pagination
   *
   * This is the primary search method supporting 15+ filter criteria including:
   * - Text search on contract notes
   * - Visa service countries filtering
   * - Associate/institute filtering
   * - Client countries filtering (using JSON field)
   * - Association type filtering (1=Institute, 2=Master Agent, 3=Sub-Agent)
   * - Date range filtering for creation and modification
   * - Status and currency filtering
   * - CSV export support
   *
   * @param searchDto - Search criteria with pagination parameters
   * @returns Paginated response with enriched contract data
   */
  async findAll(
    searchDto: SearchVisaCommissionDto,
  ): Promise<Response<VcmCommissionContractsEntity>> {
    // Create base query with joins for related data
    const query = this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoin('vcm.visa_country', 'country')
      .addSelect('country.name', 'country_name')
      .leftJoin('vcm.user', 'user')
      .addSelect(['user.fname', 'user.lname', 'user.employeeCode'])
      .leftJoin('vcm.modified_by', 'modified_by')
      .addSelect([
        'modified_by.fname',
        'modified_by.lname',
        'modified_by.employeeCode',
      ])
      .leftJoin(
        'vcm.vcmCommissionContractsHistories',
        'vcmCommissionContractsHistories',
      )
      .addSelect(['vcmCommissionContractsHistories.id'])
      .leftJoin('vcm.institute', 'institute')
      .addSelect([
        'institute.collegeId',
        'institute.UID',
        'institute.institueName',
      ])
      .orderBy('vcm.id', 'DESC');

    // Apply various filter conditions based on search parameters
    // Text search on contract notes
    if (searchDto.search_title) {
      query.andWhere('vcm.notes LIKE :search_title', {
        search_title: `%${searchDto.search_title}%`,
      });
    }

    // Filter by visa service countries (multiple selection)
    if (searchDto.visa_country_id && searchDto.visa_country_id.length > 0) {
      query.andWhere('vcm.visa_service_country_id IN (:...visa_country_id)', {
        visa_country_id: searchDto.visa_country_id,
      });
    }

    // Filter by associate/institute IDs
    if (searchDto.associate_id && searchDto.associate_id.length > 0) {
      query.andWhere('vcm.associate_id IN (:...associate_id)', {
        associate_id: searchDto.associate_id,
      });
    }


    if (searchDto.institute_id && searchDto.institute_id.length > 0) {
      query.andWhere('vcm.institute_id IN (:...institute_id)', {
        institute_id: searchDto.institute_id,
      });
    }

    /**
     * Filter by client countries from JSON field
     * Uses JSON_CONTAINS for MySQL JSON field operations
     * Supports multiple country filtering with parameterized queries
     */
    if (
      searchDto.visa_client_country_id &&
      searchDto.visa_client_country_id.length > 0
    ) {
      searchDto.visa_client_country_id.forEach((countryId, index) => {
        query.andWhere(
          `JSON_CONTAINS(vcm.client_from_countries, :countryId${index})`,
          { [`countryId${index}`]: JSON.stringify(countryId) },
        );
      });
    }

    // Filter by association type (1=Institute, 2=Master Agent, 3=Sub-Agent)
    if (searchDto.association_type) {
      query.andWhere('vcm.association_type = :association_type', {
        association_type: searchDto.association_type,
      });
    }

    // Filter by student conditions
    if (searchDto.student_condition && searchDto.student_condition.length > 0) {
      query.andWhere('vcm.student_condition IN (:...student_condition)', {
        student_condition: searchDto.student_condition,
      });
    }

    // Filter by currency
    if (searchDto.currency && searchDto.currency.length > 0) {
      query.andWhere('vcm.currency IN (:...currency)', {
        currency: searchDto.currency,
      });
    }

    // Filter by campus conditions
    if (searchDto.campus_condition && searchDto.campus_condition.length > 0) {
      query.andWhere('vcm.campus_condition IN (:...campus_condition)', {
        campus_condition: searchDto.campus_condition,
      });
    }

    // Filter by study level conditions
    if (
      searchDto.study_level_condition &&
      searchDto.study_level_condition.length > 0
    ) {
      query.andWhere(
        'vcm.study_level_condition IN (:...study_level_condition)',
        {
          study_level_condition: searchDto.study_level_condition,
        },
      );
    }

    // Filter by course conditions
    if (searchDto.course_condition && searchDto.course_condition.length > 0) {
      query.andWhere('vcm.course_condition IN (:...course_condition)', {
        course_condition: searchDto.course_condition,
      });
    }

    // Filter by active status
    if (searchDto.active && searchDto.active.length > 0) {
      query.andWhere('vcm.active IN (:...active)', {
        active: searchDto.active,
      });
    }

    // Filter by creator users
    if (searchDto.created_by && searchDto.created_by.length > 0) {
      query.andWhere('vcm.by_user IN (:...created_by)', {
        created_by: searchDto.created_by,
      });
    }

    // Filter by last modifier users
    if (searchDto.updated_by && searchDto.updated_by.length > 0) {
      query.andWhere('vcm.modified_by_user IN (:...updated_by)', {
        updated_by: searchDto.updated_by,
      });
    }

    // Date range filtering for creation date
    if (searchDto.created_on) {
      const { from, to } = searchDto.created_on;
      if (from && to) {
        query.andWhere(
          'DATE(vcm.created) BETWEEN :createdFrom AND :createdTo',
          {
            createdFrom: from,
            createdTo: to,
          },
        );
      } else if (from) {
        query.andWhere('DATE(vcm.created) >= :createdFrom', {
          createdFrom: from,
        });
      } else if (to) {
        query.andWhere('DATE(vcm.created) <= :createdTo', {
          createdTo: to,
        });
      }
    }

    // Date range filtering for modification date
    if (searchDto.updated_on) {
      const { from, to } = searchDto.updated_on;
      if (from && to) {
        query.andWhere(
          'DATE(vcm.modified) BETWEEN :modifiedFrom AND :modifiedTo',
          {
            modifiedFrom: from,
            modifiedTo: to,
          },
        );
      } else if (from) {
        query.andWhere('DATE(vcm.modified) >= :modifiedFrom', {
          modifiedFrom: from,
        });
      } else if (to) {
        query.andWhere('DATE(vcm.modified) <= :modifiedTo', {
          modifiedTo: to,
        });
      }
    }

    // Apply pagination
    const [skip, limit] = getSkipOffset(searchDto);
    const [data, total] = await query.skip(skip).take(limit).getManyAndCount();
    const pagination = paginate(total, searchDto.per_page, searchDto.page);

    // Enrich data with associate information and CSV formatting if needed
    const finalResponse = await this.getVisaCommissionContract(
      data,
      searchDto.is_csv,
    );

    return { list: finalResponse, pagination };
  }

  /**
   * Enrich commission contract records with associate information
   *
   * This private method adds associate details (Institute/Agent names) to contract records
   * and formats data for CSV export if requested.
   *
   * @param records - Raw contract records from database
   * @param isCsv - Flag indicating if data is being prepared for CSV export
   * @returns Enriched contract records with associate information
   */
  private async getVisaCommissionContract(
    records: VcmCommissionContractsEntity[],
    isCsv: boolean = false,
  ): Promise<
    (VcmCommissionContractsEntity & {
      associate: { id: number; name: string } | null;
    })[]
  > {
    const enrichedRecords = records.map(async (record) => {
      let associate: { id: number; name: string } | null = null;

      // Fetch associate information based on association type
      switch (record.association_type) {
        case 2: // Master Agent
          associate = await this.masterAgentRepo
            .createQueryBuilder('agent')
            .select(['agent.id AS id'])
            .addSelect(
              "CONCAT(agent.masteragentName, ' (', agent.uid, ')')",
              'name',
            )
            .where('agent.id = :id', { id: record.associate_id })
            .getRawOne();
          break;

        case 3: // Sub-Agent (B2B Affiliate)
          associate = await this.b2bAffiliatesAccountRepo
            .createQueryBuilder('b2b')
            .select([
              'b2b.id AS id',
              `CONCAT(b2b.affiliate_name, ' (', b2b.uid, ')') AS name`,
            ])
            .where('b2b.id = :id', { id: record.associate_id })
            .getRawOne();
          break;
        // Association type 1 (Institute) doesn't need separate associate lookup
        // as it's handled by the institute join in the main query
      }

      // For CSV export, convert country IDs to country names
      if (isCsv) {
        const countries = await this.countryRepo
          .createQueryBuilder('country')
          .select('country.name')
          .where('country.country_id IN (:...ids)', {
            ids: record.client_from_countries,
          })
          .getMany();

        // Convert to array of names like ['India', 'USA']
        record.client_from_countries = countries.map((c) => c.name);
      }

      return { ...record, associate };
    });

    return Promise.all(enrichedRecords);
  }

  /**
   * Get list of visa service countries for a specific visa product
   *
   * @param productId - Visa product/type identifier
   * @returns List of countries where this visa service is available
   */
  async getVisaCountryList(productId: string): Promise<any> {
    return await this.vsmRepo
      .createQueryBuilder('vsm')
      .innerJoin('vsm.country', 'country')
      .select([
        'country.country_id AS country_id',
        'country.iso3 AS iso3',
        'country.name AS name',
        'country.country_code AS country_code',
      ])
      .where('vsm.active = :active', { active: '1' })
      .andWhere('vsm.visa_type = :productId', { productId: String(productId) })
      .groupBy('country.country_id')
      .getRawMany();
  }

  /**
   * Get list of active client countries
   *
   * @returns List of all active countries for client filtering
   */
  async getClientCountryList() {
    return await this.countryRepo
      .createQueryBuilder('country')
      .select([
        'country.country_id AS id',
        'country.iso3 AS iso3',
        'country.name AS name',
        'country.country_code AS country_code',
      ])
      .where('country.active = :active', { active: '1' })
      .groupBy('country.country_id')
      .orderBy('country.name', 'ASC')
      .getRawMany();
  }

  /**
   * Get campus list for a specific institute in a country
   *
   * @param params - Contains institute_id and country_id
   * @returns List of campuses for the specified institute and country
   */
  async getInstituteCampusList(
    params: SearchCollegeCampusDto,
  ): Promise<CollegeCampusEntity[]> {
    return await this.campusRepo
      .createQueryBuilder('campus')
      .innerJoin('campus.college', 'college')
      .where('college.active = :active', { active: '1' })
      .andWhere('campus.instituteid = :institute_id', {
        institute_id: params.institute_id,
      })
      .andWhere('campus.country_id = :country_id', {
        country_id: params.country_id,
      })
      .andWhere('campus.campStatus = :status', {
        status: 1,
      })
      .groupBy('campus.id')
      .orderBy('campus.campName', 'ASC')
      .getMany();
  }

  /**
   * Get associate list based on association type
   *
   * Supports three types of associates:
   * 1. Institutes (Colleges/Universities)
   * 2. Master Agents
   * 3. Sub-Agents (B2B Affiliates)
   *
   * @param params - Search parameters including association_type
   * @returns List of associates filtered by type and other criteria
   */
  async getAssociateList(params: SearchAssociatesDto): Promise<any> {
    switch (params.association_type) {
      case '1':
        return await this.fetchInstitutes(params);
      case '2':
        return await this.fetchMasterAgents(params);
      case '3':
        return await this.fetchSubAgents(params);

      default:
        return [];
    }
  }

  /**
   * Get list of study levels for commission contract configuration
   *
   * @returns List of study levels excluding domestic-only levels
   */
  async getStudyLevelList(): Promise<StudyLevelMasterEntity[]> {
    const query = this.slMasterRepo
      .createQueryBuilder('slm')
      .select(['slm.id AS id', 'slm.study_level AS name'])
      .where(
        new Brackets((qb) => {
          qb.where('slm.domestic IS NULL').orWhere(
            'slm.domestic != :domestic',
            { domestic: '1' },
          );
        }),
      )
      .andWhere('slm.active = :active', { active: '1' })
      .orderBy('slm.id', 'DESC');

    return await query.getRawMany();
  }

  /**
   * Fetch institutes (colleges/universities) for association type 1
   *
   * @param params - Search parameters including country_id
   * @returns List of institutes with formatted names
   */
  private async fetchInstitutes(params: SearchAssociatesDto): Promise<any> {
    const qb = this.collegeRepo
      .createQueryBuilder('college')
      .innerJoin('college.campus', 'campus')
      .select([
        'college.collegeId AS id',
        'CONCAT(college.institueName, " (", college.UID, ")") AS name',
      ]);
    if (params.country_id) {
      qb.where('campus.country_id = :country_id', {
        country_id: params.country_id,
      });
    }
    qb.groupBy('college.collegeId').orderBy('college.institueName', 'ASC');
    return await qb.getRawMany();
  }

  /**
   * Fetch master agents for association type 2
   *
   * @param params - Search parameters including product_id
   * @returns List of master agents associated with a specific visa product
   */
  private async fetchMasterAgents(params: SearchAssociatesDto): Promise<any> {
    return await this.masterAgentRepo
      .createQueryBuilder('agent')
      .innerJoin('agent.products', 'products')
      .select(['agent.id AS id'])
      .addSelect("CONCAT(agent.masteragentName, ' (', agent.uid, ')')", 'name')
      .where('products.masteragentProductIds = :product_id', {
        product_id: params.product_id,
      })
      .groupBy('agent.id')
      .orderBy('agent.masteragentName', 'ASC')
      .getRawMany();
  }

  /**
   * Fetch sub-agents (B2B affiliates) for association type 3
   *
   * @param params - Search parameters
   * @returns List of all active sub-agents
   */
  private async fetchSubAgents(params: SearchAssociatesDto): Promise<any> {
    return await this.b2bAffiliatesAccountRepo
      .createQueryBuilder('b2b')
      .select([
        'b2b.id AS id',
        `CONCAT(b2b.affiliate_name, ' (', b2b.uid, ')') AS name`,
      ])
      .groupBy('b2b.id')
      .orderBy('b2b.affiliate_name', 'ASC')
      .getRawMany();
  }

  /**
   * Create a new commission contract with hierarchical structure
   *
   * This method handles the complex creation of a commission contract with:
   * - Main contract details
   * - Applicable sets (campus/study level/course filters)
   * - Commission sets (bonus configurations)
   * - Commission instances (tiered commission structure)
   *
   * Uses transaction to ensure data consistency across all related entities.
   *
   * @param createDto - Contract creation data with hierarchical structure
   * @param userData - User information for audit tracking
   * @returns The created contract entity
   */
  async createCommission(
    createDto: CreateCommissionContractDto,
    userData: UserDataProps,
  ) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();
    try {
      // Remove ID from DTO if present (for new creation)
      delete createDto.id;

      // Create main contract entity
      const contractEntity = plainToInstance(VcmCommissionContractsEntity, {
        ...createDto,
        associate_id:
          createDto.association_type !== '1' ? createDto.associate_id : null,
        by_user: userData.id,
        created: getISTDate(),
        active: '1',
      });

      const contract = await queryRunner.manager.save(contractEntity);
      const allSetEntities = [];
      const allInstanceEntities = [];

      // Process each applicable set in the contract
      for (const applicableSet of createDto.applicable_sets) {
        // Create applicable on entity (filter criteria)
        const applicableOnEntity = plainToInstance(
          VcmCommissionContractApplicableOnEntity,
          {
            commission_contract_id: Number(contract.id),
            no_of_instances: applicableSet.no_of_instances,
            students_per: applicableSet.students_per,
            applicable_campuses: applicableSet.applicable_campuses ?? null,
            applicable_study_levels:
              applicableSet.applicable_study_levels ?? null,
            applicable_courses: applicableSet.applicable_courses ?? null,
            by_user: userData.id,
          },
        );

        const savedApplicableOn =
          await queryRunner.manager.save(applicableOnEntity);

        // Process each commission set within the applicable set
        for (const set of applicableSet.sets) {
          delete set.id; // Remove ID for new set
          set.attachment_file = set.specific_contract;
          const setEntity = plainToInstance(VcmCommissionContractSetsEntity, {
            ...set,
            commission_contract_applicable_on_id: savedApplicableOn.id, // FK relation
            by_user: Number(userData.id),
            min_students: set.min_students ? Number(set.min_students) : null,
            max_students: set.max_students ? Number(set.max_students) : null,
            bonus_type: set.bonus_type ? Number(set.bonus_type) : null,
            bonus_amount: set.bonus_amount ? Number(set.bonus_amount) : null,
          });

          allSetEntities.push(setEntity);
        }
      }

      // Save all commission sets
      const insertedSets = await queryRunner.manager.save(allSetEntities);

      let insertedSetIndex = 0;

      // Process commission instances for each set
      for (const applicableSet of createDto.applicable_sets) {
        for (const set of applicableSet.sets) {
          const insertedSet = insertedSets[insertedSetIndex++];
          let instanceIndex = 1;

          // Create instances for the commission set
          for (const instance of set.instances) {
            delete instance.id; // Remove ID for new instance
            const instanceEntity = plainToInstance(
              VcmCommissionContractInstancesEntity,
              {
                ...instance,
                commission_contract_set_id: insertedSet.id,
                instance: instanceIndex++, // Sequential instance number
                by_user: userData.id,
              },
            );
            allInstanceEntities.push(instanceEntity);
          }
        }
      }

      // Save all commission instances
      if (allInstanceEntities.length > 0) {
        await queryRunner.manager.save(allInstanceEntities);
      }

      await queryRunner.commitTransaction();
      return contract;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error('Create commission contract error', error?.stack);
      throw new InternalServerErrorException(
        'Failed to create commission contract',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Edit an existing commission contract with historical tracking
   *
   * This method handles complex contract updates with:
   * - Deep comparison to detect changes
   * - Historical record creation for audit trail
   * - Transactional updates across all related entities
   * - File attachment management
   *
   * @param updateDto - Updated contract data
   * @param userData - User information for audit tracking
   * @returns Updated contract entity
   */
  async editCommission(
    updateDto: CreateCommissionContractDto,
    userData: UserDataProps,
  ) {
    if (!updateDto.id)
      throw new HttpException('Id is required', HttpStatus.BAD_REQUEST);

    const contractId = updateDto.id;

    // Get existing contract for comparison
    const existing = await this.getContractInfo(contractId);

    // Preserve existing attachment files
    if (existing.attachment_file && existing.attachment_file.length > 0) {
      updateDto.attachment_file = existing.attachment_file;
    }

    const currentDataSets = structuredClone(updateDto);

    // Detect changes between existing and updated contract
    const differences = this.filterChanges(existing, updateDto);

    // If no changes detected, return existing contract
    if (differences.length === 0) return existing;

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

    try {
      const updateData = { ...updateDto };

      // Preserve attachments in update data
      if (existing.attachment_file && existing.attachment_file.length > 0) {
        updateData.attachment_file = existing.attachment_file;
      }

      // Remove nested structures for main contract update
      delete updateData.applicable_sets;

      // Update main contract entity
      const contractEntity = plainToInstance(VcmCommissionContractsEntity, {
        ...updateData,
        associate_id:
          updateDto.association_type !== '1'
            ? updateDto.associate_id
            : null,
        modified_by_user: userData.id,
        modified: getISTDate(),
        active: '1',
      });

      const contract = await queryRunner.manager.update(
        VcmCommissionContractsEntity,
        { id: contractId },
        contractEntity,
      );

      // Process applicable sets updates
      for (const applicableSet of updateDto.applicable_sets) {
        const applicableOnEntity = plainToInstance(
          VcmCommissionContractApplicableOnEntity,
          {
            commission_contract_id: Number(contractId),
            no_of_instances: applicableSet.no_of_instances,
            students_per: applicableSet.students_per,
            applicable_campuses: applicableSet.applicable_campuses ?? null,
            applicable_study_levels:
              applicableSet.applicable_study_levels ?? null,
            applicable_courses: applicableSet.applicable_courses ?? null,
            by_user: userData.id,
          },
        );

        if (applicableSet.id) {
          // Update existing applicable set
          await queryRunner.manager.update(
            VcmCommissionContractApplicableOnEntity,
            { id: applicableSet.id },
            applicableOnEntity,
          );

          // Process commission sets within the applicable set
          for (const set of applicableSet.sets) {
            const vcmCommissionContractInstances = set.instances;
            delete set.attachment_file;
            delete set.instances;
            const setEntity = plainToInstance(VcmCommissionContractSetsEntity, {
              ...set,
              commission_contract_applicable_on_id: applicableSet.id, // FK relation
              modified_by_user: Number(userData.id),
              min_students: set.min_students ? Number(set.min_students) : null,
              max_students: set.max_students ? Number(set.max_students) : null,
              bonus_type: set.bonus_type ? Number(set.bonus_type) : null,
              bonus_amount: set.bonus_amount ? Number(set.bonus_amount) : null,
            });

            if (set.id) {
              // Update existing commission set
              await queryRunner.manager.update(
                VcmCommissionContractSetsEntity,
                { id: set.id },
                setEntity,
              );

              // Process instances within the commission set
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: set.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  // Update existing instance
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  // Create new instance
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            } else {
              // Create new commission set
              delete setEntity.id;
              const newSet = await queryRunner.manager.save(setEntity);
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: newSet.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  // Update existing instance (moved from another set)
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  // Create new instance
                  delete instance.id;
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            }
          }
        } else {
          // Create new applicable set
          delete applicableOnEntity.id;
          const savedApplicableOn =
            await queryRunner.manager.save(applicableOnEntity);

          // Process commission sets for the new applicable set
          for (const set of applicableSet.sets) {
            const vcmCommissionContractInstances = set.instances;
            delete set.attachment_file;
            delete set.instances;
            const setEntity = plainToInstance(VcmCommissionContractSetsEntity, {
              ...set,
              commission_contract_applicable_on_id: savedApplicableOn.id, // FK relation
              modified_by_user: Number(userData.id),
              min_students: set.min_students ? Number(set.min_students) : null,
              max_students: set.max_students ? Number(set.max_students) : null,
              bonus_type: set.bonus_type ? Number(set.bonus_type) : null,
              bonus_amount: set.bonus_amount ? Number(set.bonus_amount) : null,
            });

            if (set.id) {
              // Update existing set (moved from another applicable set)
              await queryRunner.manager.update(
                VcmCommissionContractSetsEntity,
                { id: set.id },
                setEntity,
              );
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: set.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            } else {
              // Create new commission set
              delete setEntity.id;
              const newSet = await queryRunner.manager.save(setEntity);
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: newSet.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  delete instanceEntity.id;
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            }
          }
        }
      }

      // Create historical record for audit trail
      const contractHistoryEntity = plainToInstance(
        VcmCommissionContractsHistoryEntity,
        {
          contract_id: contractId,
          previous_content: existing,
          current_content: currentDataSets,
          by_user: userData.id,
        },
      );
      await queryRunner.manager.save(contractHistoryEntity);

      await queryRunner.commitTransaction();
      return contract;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error('Edit commission contract error', error?.stack);
      throw new InternalServerErrorException(
        'Failed to edit commission contract',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Get distinct visa service countries by association type
   *
   * Used for filter dropdown population in UI
   *
   * @param association_type - Type of association (1, 2, or 3)
   * @returns Distinct countries where contracts exist for the association type
   */
  async fetchServiceCountriesByList(association_type: number) {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoin('vcm.visa_country', 'country')
      .select([
        'DISTINCT vcm.visa_service_country_id AS id',
        'country.name AS name',
      ])
      .where('vcm.association_type = :association_type', { association_type })
      .orderBy('country.name', 'ASC')
      .getRawMany();
  }

  /**
   * Get distinct client countries by association type
   *
   * Uses JSON_TABLE to extract values from JSON array field
   *
   * @param association_type - Type of association (1, 2, or 3)
   * @returns Distinct client countries from JSON field
   */
  async fetchClientCountriesByList(association_type: number) {
    const sql = `SELECT DISTINCT c.country_id AS id, c.name AS name
    FROM vcm_commission_contracts vcm JOIN JSON_TABLE( vcm.client_from_countries,'$[*]' COLUMNS (country_id INT PATH '$')) jt ON TRUE
    JOIN country c ON c.country_id = jt.country_id WHERE vcm.association_type = ? ORDER BY c.name ASC`;

    return await this.vcmRepo.query(sql, [association_type]);
  }

  /**
   * Get distinct institutes by association type
   *
   * @param association_type - Type of association (should be 1 for institutes)
   * @returns Distinct institutes with contracts
   */
  async fetchInstitutesByList(association_type: number) {
    const qb = this.vcmRepo.createQueryBuilder('vcm');

    return await qb
      .innerJoin('college', 'college', 'college.collegeId = vcm.institute_id')
      .select([
        'college.collegeId AS id',
        `CONCAT(college.institueName, " (", college.UID, ")") AS name`,
      ])
      .where('vcm.association_type = :association_type', {
        association_type,
      })
      .groupBy('college.collegeId')
      .orderBy('college.institueName', 'ASC')
      .getRawMany();
  }

  /**
   * Get distinct associates by association type
   *
   * @param association_type - Type of association (2 for master agents, 3 for sub-agents)
   * @returns Distinct associates with contracts
   */
  async fetchAssociatesByList(association_type: number) {
    const qb = this.vcmRepo.createQueryBuilder('vcm');

    switch (association_type) {
      case 2: // master agent
        return await qb
          .innerJoin('masteragent', 'ma', 'ma.id = vcm.associate_id')
          .select([
            'ma.id AS id',
            `CONCAT(ma.masteragentName, " (", ma.uid, ")") AS name`,
          ])
          .where('vcm.association_type = :association_type', {
            association_type,
          })
          .groupBy('ma.id')
          .orderBy('ma.masteragentName', 'ASC')
          .getRawMany();

      case 3: // sub-agent
        return await qb
          .innerJoin(
            'b2b_affiliate_accounts',
            'affiliate',
            'affiliate.id = vcm.associate_id',
          )
          .select([
            'affiliate.id AS id',
            `CONCAT(affiliate.affiliate_name, " (", affiliate.uid, ")") AS name`,
          ])
          .where('vcm.association_type = :association_type', {
            association_type,
          })
          .groupBy('affiliate.id')
          .orderBy('affiliate.affiliate_name', 'ASC')
          .getRawMany();
    }
  }

  /**
   * Get distinct users who created or updated contracts
   *
   * @param by - 'created_by' or 'updated_by'
   * @param association_type - Type of association
   * @returns Distinct users with formatted names
   */
  async fetchCreatedByOrUpdatedByUsers(by: string, association_type: number) {
    const query = this.vcmRepo
      .createQueryBuilder('vcm')
      .where('vcm.association_type = :association_type', { association_type });

    if (by === 'created_by') {
      query
        .leftJoin('vcm.user', 'user')
        .select('user.id', 'id')
        .addSelect(
          `CONCAT(user.fname, ' ', user.lname, ' - ', user.employeeCode)`,
          'name',
        )
        .orderBy('user.fname', 'ASC')
        .addOrderBy('user.lname', 'ASC')
        .groupBy('user.id');
    }

    if (by === 'updated_by') {
      query
        .leftJoin('vcm.modified_by', 'modified_by')
        .select('modified_by.id', 'id')
        .addSelect(
          `CONCAT(modified_by.fname, ' ', modified_by.lname, ' - ', modified_by.employeeCode)`,
          'name',
        )
        .andWhere('vcm.modified_by IS NOT NULL')
        .orderBy('modified_by.fname', 'ASC')
        .addOrderBy('modified_by.lname', 'ASC')
        .groupBy('modified_by.id');
    }

    return await query.getRawMany();
  }

  /**
   * Get countries by IDs
   *
   * @param countries - Array of country IDs
   * @returns Country entities with id and name
   */
  async getCountriesByIds(countries: string[]): Promise<CountryEntity[]> {
    const query = this.countryRepo
      .createQueryBuilder('country')
      .select(['country.country_id AS id', 'country.name AS name'])
      .where('country.country_id IN (:...countries)', { countries })
      .orderBy('country.name', 'ASC');

    return await query.getRawMany();
  }

  /**
   * Get courses filtered by institute, campuses, and study levels
   *
   * Used for contract configuration when selecting applicable courses
   *
   * @param searchCoursesDto - Filter criteria
   * @returns Filtered courses with formatted names
   */
  async getCoursesByInstituteCampusStudyLevels(
    searchCoursesDto: SearchCollegeCampusCoursesDto,
  ) {
    const qb = this.courseRepo
      .createQueryBuilder('cm')
      .leftJoin('college_campus', 'cp', 'cp.instituteid = cm.institueId')
      .leftJoin(
        'course_master_studylevelinfo',
        'sl',
        'sl.course_id = cm.course_id',
      )
      .select([
        'cm.course_id AS id',
        "CONCAT(cm.course_name, ' (', cm.UID, ')') AS name",
      ]);
    if (searchCoursesDto.institute_id) {
      qb.where('cm.institueId = :instituteId', {
        instituteId: searchCoursesDto.institute_id,
      });
    }
    if (searchCoursesDto.campuses && searchCoursesDto.campuses.length > 0) {
      qb.andWhere('cp.id IN (:...campusIds)', {
        campusIds: searchCoursesDto.campuses,
      });
    }
    if (
      searchCoursesDto.study_levels &&
      searchCoursesDto.study_levels.length > 0
    ) {
      qb.andWhere('sl.studylevelId IN (:...studyLevelIds)', {
        studyLevelIds: searchCoursesDto.study_levels,
      });
    }
    return await qb
      .groupBy('cm.course_id')
      .orderBy('cm.course_name', 'ASC')
      .getRawMany();
  }

  /**
   * Get contract attachments including nested set attachments
   *
   * @param id - Contract ID
   * @returns Contract with all attachment files
   */
  async getContractAttachments(id: string): Promise<any> {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .select(['vcm.id', 'vcm.attachment_file'])
      .leftJoinAndSelect(
        'vcm.vcm_commission_contract_applicable_sets',
        'vcm_commission_contract_applicable_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_applicable_sets.vcm_commission_contract_sets',
        'vcm_commission_contract_sets',
      )
      .addSelect([
        'vcm_commission_contract_sets.id',
        'vcm_commission_contract_sets.attachment_file',
        'vcm_commission_contract_sets.set_name',
      ])
      .where('vcm.id = :id', { id })
      .orderBy('vcm.id', 'DESC')
      .getOne();
  }

  /**
   * Get complete contract information with all nested structures
   *
   * @param id - Contract ID
   * @returns Complete contract with applicable sets, commission sets, and instances
   */
  async getContractInfo(id: string): Promise<any> {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoinAndSelect(
        'vcm.vcm_commission_contract_applicable_sets',
        'vcm_commission_contract_applicable_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_applicable_sets.vcm_commission_contract_sets',
        'vcm_commission_contract_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_sets.vcm_commission_contract_instances',
        'vcm_commission_contract_instances',
      )
      .where('vcm.id = :id', { id })
      .orderBy('vcm_commission_contract_applicable_sets.id', 'ASC')
      .addOrderBy('vcm_commission_contract_instances.instance', 'ASC')
      .getOne();
  }

  /**
   * Deep compare existing and updated contract to detect changes
   *
   * This method performs a comprehensive comparison across all levels:
   * 1. Main contract fields
   * 2. Applicable sets (filters)
   * 3. Commission sets (bonus configurations)
   * 4. Commission instances (tiered commission)
   *
   * @param existing - Existing contract from database
   * @param updateDto - Updated contract data
   * @returns Array of detected differences with hierarchical information
   */
  private filterChanges(
    existing: Record<string, any>,
    updateDto: CreateCommissionContractDto,
  ): any[] {
    // Fields to exclude from comparison (audit fields, nested structures)
    const keysToRemove = [
      'active',
      'by_user',
      'created',
      'modified_by_user',
      'modified',
      'vcm_commission_contract_applicable_sets',
      'applicable_sets', // Add this to exclude from main comparison
    ];

    // Merge attachment files for main contract with null checks
    updateDto.attachment_file = Array.from(
      new Set([
        ...(existing.attachment_file || []),
        ...(updateDto.attachment_file || []),
      ]),
    );

    // Compare main contract fields
    const differences = deepCompareObjectsSmart(
      existing,
      updateDto,
      '',
      keysToRemove,
    );

    const allDifferences: any[] = [...(differences || [])];

    // Compare applicable sets with null checks
    const existingApplicableSets =
      existing.vcm_commission_contract_applicable_sets || [];
    const dtoApplicableSets = (updateDto as any).applicable_sets || [];

    const filteredExistingApplicableSetsLength = existingApplicableSets.length;
    const filteredDtoApplicableSetsLength = dtoApplicableSets.length;

    if (
      filteredExistingApplicableSetsLength === filteredDtoApplicableSetsLength
    ) {
      for (let i = 0; i < filteredExistingApplicableSetsLength; i++) {
        const existingApplicableSet = existingApplicableSets[i] || {};
        const dtoApplicableSet = dtoApplicableSets[i] || ({} as any);

        // Compare applicable sets - map DTO properties to database properties
        const applicableSetComparison = {
          existing: {
            students_per: existingApplicableSet.students_per,
            no_of_instances: existingApplicableSet.no_of_instances,
            applicable_study_levels:
              existingApplicableSet.applicable_study_levels,
            applicable_courses: existingApplicableSet.applicable_courses,
            applicable_campuses: existingApplicableSet.applicable_campuses,
          },
          dto: {
            students_per: dtoApplicableSet.students_per,
            no_of_instances: dtoApplicableSet.no_of_instances,
            applicable_study_levels: dtoApplicableSet.applicable_study_levels,
            applicable_courses: dtoApplicableSet.applicable_courses,
            applicable_campuses: dtoApplicableSet.applicable_campuses,
          },
        };

        const applicableSetDifferences = deepCompareObjectsSmart(
          applicableSetComparison.existing,
          applicableSetComparison.dto,
          '',
          ['id', 'commission_contract_id'], // Remove keys that don't exist in DTO
        );

        if (applicableSetDifferences && applicableSetDifferences.length > 0) {
          allDifferences.push(
            ...applicableSetDifferences.map((diff) => ({
              ...diff,
              level: 'applicable_set',
              applicableSetIndex: i,
            })),
          );
        }

        // Compare commission sets within each applicable set
        const existingSets =
          existingApplicableSet.vcm_commission_contract_sets || [];
        const dtoSets = (dtoApplicableSet.sets || []) as any[];

        const filteredExistingSetsLength = existingSets.length;
        const filteredDtoSetsLength = dtoSets.length;

        if (filteredExistingSetsLength === filteredDtoSetsLength) {
          for (let j = 0; j < filteredExistingSetsLength; j++) {
            const existingSet = existingSets[j] || {};
            const dtoSet = dtoSets[j] || ({} as any);

            // Merge attachment files for commission sets with null checks
            if (dtoSet) {
              dtoSet.attachment_file = Array.from(
                new Set([
                  ...(existingSet.attachment_file || []),
                  ...(dtoSet.attachment_file || []),
                ]),
              );
            }

            // Compare commission sets - map DTO properties to database properties
            const setComparison = {
              existing: {
                set_name: existingSet.set_name,
                has_bonus: existingSet.has_bonus,
                bonus_type: existingSet.bonus_type,
                bonus_amount: existingSet.bonus_amount,
                max_students: existingSet.max_students,
                min_students: existingSet.min_students,
                // attachment_file: existingSet.attachment_file,
              },
              dto: {
                set_name: dtoSet.set_name,
                has_bonus: dtoSet.has_bonus,
                bonus_type: dtoSet.bonus_type,
                bonus_amount: dtoSet.bonus_amount,
                max_students: dtoSet.max_students,
                min_students: dtoSet.min_students,
                // attachment_file: dtoSet.attachment_file,
              },
            };

            const setDifferences = deepCompareObjectsSmart(
              setComparison.existing,
              setComparison.dto,
              '',
              ['id', 'commission_contract_applicable_on_id'], // Remove keys that don't exist in DTO
            );

            if (setDifferences && setDifferences.length > 0) {
              allDifferences.push(
                ...setDifferences.map((diff) => ({
                  ...diff,
                  level: 'set',
                  applicableSetIndex: i,
                  setIndex: j,
                })),
              );
            }

            // Compare instances within each commission set
            const existingInstances =
              existingSet.vcm_commission_contract_instances || [];
            const dtoInstances = (dtoSet.instances || []) as any[];

            const filteredExistingInstancesLength = existingInstances.length;
            const filteredDtoInstancesLength = dtoInstances.length;

            if (
              filteredExistingInstancesLength === filteredDtoInstancesLength
            ) {
              for (let k = 0; k < filteredExistingInstancesLength; k++) {
                const existingInstance = existingInstances[k] || {};
                const dtoInstance = dtoInstances[k] || ({} as any);

                // Compare instances - map DTO properties to database properties
                const instanceComparison = {
                  existing: {
                    instance: existingInstance.instance,
                    commission_type: existingInstance.commission_type,
                    commission_amount: existingInstance.commission_amount,
                    commission_percentage:
                      existingInstance.commission_percentage,
                  },
                  dto: {
                    instance: dtoInstance.instance,
                    commission_type: dtoInstance.commission_type,
                    commission_amount: dtoInstance.commission_amount,
                    commission_percentage: dtoInstance.commission_percentage,
                  },
                };

                const instanceDifferences = deepCompareObjectsSmart(
                  instanceComparison.existing,
                  instanceComparison.dto,
                  '',
                  ['id', 'commission_contract_set_id'], // Remove keys that don't exist in DTO
                );

                if (instanceDifferences && instanceDifferences.length > 0) {
                  allDifferences.push(
                    ...instanceDifferences.map((diff) => ({
                      ...diff,
                      level: 'instance',
                      applicableSetIndex: i,
                      setIndex: j,
                      instanceIndex: k,
                    })),
                  );
                }
              }
            } else {
              allDifferences.push({
                level: 'set',
                applicableSetIndex: i,
                setIndex: j,
                message: 'Number of instances changed',
                existingInstancesCount: filteredExistingInstancesLength,
                updateInstancesCount: filteredDtoInstancesLength,
              });
            }
          }
        } else {
          allDifferences.push({
            level: 'applicable_set',
            applicableSetIndex: i,
            message: 'Number of commission sets changed',
            existingSetsCount: filteredExistingSetsLength,
            updateSetsCount: filteredDtoSetsLength,
          });
        }
      }
    } else {
      allDifferences.push({
        level: 'contract',
        message: 'Number of applicable sets changed',
        existingApplicableSetsCount: filteredExistingApplicableSetsLength,
        updateApplicableSetsCount: filteredDtoApplicableSetsLength,
      });
    }

    return allDifferences;
  }

  /**
   * Remove attachment file from contract or commission set
   *
   * Supports removal from:
   * 1. Main contract (contract_id provided)
   * 2. Commission set (set_id provided)
   *
   * Also physically removes the file from storage
   *
   * @param params - Contains file information and target ID
   * @param userData - User information for audit tracking
   * @returns The parameters passed in
   */
  async removeContractAttachments(
    params: RemoveAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;
    let entityClass;
    let idField: string;

    // Determine target entity based on provided ID
    if (params.contract_id) {
      repository = this.vcmRepo;
      entityClass = VcmCommissionContractsEntity;
      idField = 'contract_id';
    } else if (params.set_id) {
      repository = this.vcmSetRepo;
      entityClass = VcmCommissionContractSetsEntity;
      idField = 'set_id';
    } else {
      return params;
    }

    // Get existing record
    const record = await repository
      .createQueryBuilder()
      .where('id = :id', { id: params[idField] })
      .getOne();

    if (!record?.attachment_file?.length) return params;

    // Filter out the specified file
    const updatedAttachments = record.attachment_file.filter(
      (file: string) => file !== params.file_name,
    );

    // If file was found and removed, update the entity
    if (updatedAttachments.length !== record.attachment_file.length) {
      const entity = plainToInstance(entityClass, {
        attachment_file: updatedAttachments,
        modified_by_user: userData.id,
      });
      await repository.update({ id: params[idField] }, entity);

      // Physically remove the file from storage
      await this.fileService.removeFile(params.file_name);
    }

    return params;
  }

  /**
   * Add attachment files to contract or commission set
   *
   * @param params - Contains files and target information
   * @param userData - User information for audit tracking
   * @returns The parameters passed in
   */
  async addContractAttachments(
    params: AddAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;
    let entityClass;

    // Determine target entity based on type
    if (params.type === 'contract') {
      repository = this.vcmRepo;
      entityClass = VcmCommissionContractsEntity;
    } else if (params.type === 'set') {
      repository = this.vcmSetRepo;
      entityClass = VcmCommissionContractSetsEntity;
    } else {
      return params;
    }

    // Get existing record
    const record = await repository
      .createQueryBuilder()
      .where('id = :id', { id: params.id })
      .getOne();

    // Merge existing and new attachments
    const updatedAttachments = record.attachment_file
      ? [...record.attachment_file, ...params.attachment_file]
      : params.attachment_file;

    if (updatedAttachments.length) {
      const entity = plainToInstance(entityClass, {
        attachment_file: updatedAttachments,
        modified_by_user: userData.id,
      });
      await repository.update({ id: params.id }, entity);
    }

    return params;
  }

  /**
   * Get contract edit history with human-readable labels
   *
   * Converts IDs in history records to readable names for:
   * - Courses
   * - Campuses
   * - Study levels
   * - Countries
   *
   * @param contractId - Contract ID
   * @returns History records with readable labels
   */
  async getContractEditHistoryWithLabels(contractId: string): Promise<any[]> {
    // Get raw history records
    const history = await this.vcmHistoryRepo
      .createQueryBuilder('history')
      .leftJoinAndSelect('history.user', 'user')
      .where('history.contract_id = :contractId', { contractId })
      .orderBy('history.id', 'DESC')
      .getMany();

    // Collect all IDs that need to be resolved to names
    const courseIdsSet = new Set<string>();
    const campusIdsSet = new Set<string>();
    const studyLevelIdsSet = new Set<string>();
    const countryIdsSet = new Set<string>();

    history.forEach((item) => {
      const previousContent =
        typeof item.previous_content === 'string'
          ? JSON.parse(item.previous_content)
          : item.previous_content;

      const currentContent =
        typeof item.current_content === 'string'
          ? JSON.parse(item.current_content)
          : item.current_content;

      // Extract IDs from previous content
      previousContent.vcm_commission_contract_applicable_sets?.forEach(
        (set: any) => {
          if (set.applicable_courses)
            set.applicable_courses.forEach((id: string) =>
              courseIdsSet.add(id),
            );
          if (set.applicable_campuses)
            set.applicable_campuses.forEach((id: string) =>
              campusIdsSet.add(id),
            );
          if (set.applicable_study_levels)
            set.applicable_study_levels.forEach((id: string) =>
              studyLevelIdsSet.add(id),
            );
        },
      );

      // Extract IDs from current content
      currentContent.sets?.forEach((set: any) => {
        if (set.applicable_courses)
          set.applicable_courses.forEach((id: string) => courseIdsSet.add(id));
        if (set.applicable_campuses)
          set.applicable_campuses.forEach((id: string) => campusIdsSet.add(id));
        if (set.applicable_study_levels)
          set.applicable_study_levels.forEach((id: string) =>
            studyLevelIdsSet.add(id),
          );
      });

      // Extract country IDs
      previousContent.client_from_countries.forEach((id: string) =>
        countryIdsSet.add(id),
      );
      currentContent.client_from_countries.forEach((id: string) =>
        countryIdsSet.add(id),
      );
    });

    // Convert sets to arrays
    const courseIds = Array.from(courseIdsSet);
    const campusIds = Array.from(campusIdsSet);
    const studyLevelIds = Array.from(studyLevelIdsSet);
    const countryIds = Array.from(countryIdsSet);

    // Batch fetch all reference data in parallel
    const [courses, campuses, studyLevels, countries] = await Promise.all([
      this.courseRepo.findBy({ course_id: In(courseIds) }),
      this.campusRepo.findBy({ id: In(campusIds) }),
      this.studyLevelRepo.findBy({ id: In(studyLevelIds) }),
      this.countryRepo.findBy({ country_id: In(countryIds) }),
    ]);

    // Create lookup maps for ID → Name conversion
    const coursesMapping = courses.reduce<Record<string, string>>((map, c) => {
      map[c.course_id] = c.course_name;
      return map;
    }, {});

    const campusesMapping = campuses.reduce<Record<string, string>>(
      (map, c) => {
        map[c.id] = c.campName;
        return map;
      },
      {},
    );

    const studyLevelsMapping = studyLevels.reduce<Record<string, string>>(
      (map, s) => {
        map[s.id] = s.study_level;
        return map;
      },
      {},
    );

    const countriesMapping = countries.reduce<Record<string, string>>(
      (map, c) => {
        map[c.country_id] = c.name;
        return map;
      },
      {},
    );

    // Transform history records with readable names
    const transformedHistory = history.map((item) => {
      const previousContent =
        typeof item.previous_content === 'string'
          ? JSON.parse(item.previous_content)
          : item.previous_content;

      const currentContent =
        typeof item.current_content === 'string'
          ? JSON.parse(item.current_content)
          : item.current_content;

      // Convert IDs to names in previous content
      const transformedPreviousContent = {
        ...previousContent,
        vcm_commission_contract_applicable_sets:
          previousContent.vcm_commission_contract_applicable_sets?.map(
            (set: any) => ({
              ...set,
              applicable_courses: set?.applicable_courses
                ? set?.applicable_courses
                    .map((id: string) => coursesMapping[id] || id)
                    .join(', ')
                : 'N/A',
              applicable_campuses: set?.applicable_campuses
                ? set?.applicable_campuses
                    .map((id: string) => campusesMapping[id] || id)
                    .join(', ')
                : 'N/A',
              applicable_study_levels: set?.applicable_study_levels
                ? set?.applicable_study_levels
                    .map((id: string) => studyLevelsMapping[id] || id)
                    .join(', ')
                : 'N/A',
            }),
          ),
        client_from_countries: previousContent.client_from_countries
          .map((id: string) => countriesMapping[id] || id)
          .join(', '),
      };

      // Convert IDs to names in current content
      const transformedCurrentContent = {
        ...currentContent,
        applicable_sets: currentContent?.applicable_sets?.map((set: any) => ({
          ...set,
          applicable_courses: set?.applicable_courses
            ? set?.applicable_courses
                .map((id: string) => coursesMapping[id] || id)
                .join(', ')
            : 'N/A',
          applicable_campuses: set?.applicable_campuses
            ? set?.applicable_campuses
                .map((id: string) => campusesMapping[id] || id)
                .join(', ')
            : 'N/A',
          applicable_study_levels: set?.applicable_study_levels
            ? set?.applicable_study_levels
                .map((id: string) => studyLevelsMapping[id] || id)
                .join(', ')
            : 'N/A',
        })),
        client_from_countries: currentContent.client_from_countries
          .map((id: string) => countriesMapping[id] || id)
          .join(', '),
      };

      return {
        ...item,
        previous_content: transformedPreviousContent,
        current_content: transformedCurrentContent,
      };
    });

    return transformedHistory;
  }

  /**
   * Update contract status (activate/deactivate)
   *
   * @param userId - User ID performing the update
   * @param updateStatusDto - Contains contract IDs and action (activate/deactivate)
   * @returns Update result
   */
  async updateContractStatus(
    userId: string,
    updateStatusDto: UpdateContractStatusDto,
  ) {
    const { ids, action } = updateStatusDto;

    return await this.vcmRepo
      .createQueryBuilder()
      .update()
      .set({
        active: action === 'activate' ? 1 : 2, // 1=Active, 2=Inactive
        modified_by_user: Number(userId),
      })
      .where('id IN (:...ids)', { ids })
      .execute();
  }

  /**
   * Get commission set details by ID
   *
   * @param id - Commission set ID
   * @returns Commission set entity
   */
  async getContractSetDetailsById(id: string): Promise<any> {
    return await this.vcmSetRepo
      .createQueryBuilder('set')
      .where('set.id = :id', { id })
      .getOne();
  }

  /**
   * Check if a duplicate contract already exists
   *
   * Validates uniqueness based on multiple criteria:
   * - Product ID
   * - Visa service country
   * - Institute
   * - Association type
   * - Client countries (exact match)
   * - Associate ID (if applicable)
   *
   * @param dto - Contract data to check
   * @returns Boolean indicating if duplicate exists
   */
  async checkDuplicateContract(dto: CheckDuplicateContractDto): Promise<any> {
    const qb = this.vcmRepo
      .createQueryBuilder('c')
      .select('1')
      .where('c.product_id = :product_id', {
        product_id: dto.product_id,
      })
      .andWhere('c.visa_service_country_id = :visa_service_country_id', {
        visa_service_country_id: dto.visa_service_country_id,
      })
      .andWhere('c.institute_id = :institute_id', {
        institute_id: dto.institute_id,
      })
      .andWhere('c.association_type = :association_type', {
        association_type: dto.association_type,
      })
      .andWhere('c.student_condition = :student_condition', {
        student_condition: dto.student_condition,
      })
      // Exact match on client countries JSON array
      .andWhere('JSON_CONTAINS(c.client_from_countries, :countries)', {
        countries: JSON.stringify(dto.client_from_countries),
      });

    // Handle NULL associate_id for institute contracts
    if (dto.associate_id === null || dto.associate_id === undefined) {
      qb.andWhere('c.associate_id IS NULL');
    } else {
      qb.andWhere('c.associate_id = :associate_id', {
        associate_id: dto.associate_id,
      });
    }

    // Exclude current contract when editing
    if (dto.id) {
      qb.andWhere('c.id != :id', { id: dto.id });
    }

    const exists = await qb.getRawOne();
    return !!exists;
  }
}
