import {
  HttpException,
  HttpStatus,
  Inject,
  Injectable,
  InternalServerErrorException,
  Logger,
  NotFoundException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Brackets, DataSource, Repository, SelectQueryBuilder } from 'typeorm';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
import {
  getSkipOffset,
  paginate,
} from 'src/config/pagination/pagination.service';
import { SearchAssociatesDto } from './dto/search-associates-dto';
import { SearchDto } from './dto/search-dto';
import { Response } from 'src/config/pagination/paginationDto';
import { VisaServiceMasterEntity } from 'src/entities/VisaServiceMaster.entity';
import { CollegeEntity } from 'src/entities/College.entity';
import { MasteragentEntity } from 'src/entities/Masteragent.entity';
import { CountryEntity } from 'src/entities/Country.entity';
import { VcmCommissionContractInstancesEntity } from 'src/entities/VcmCommissionContractInstances.entity';
import { VcmInstituteCommissionEnquiryEntity } from 'src/entities/VcmInstituteCommissionEnquiry.entity';
import { VcmMasterAgentCommissionEnquiryEntity } from 'src/entities/VcmMasterAgentCommissionEnquiry.entity';
import { SearchInstitutesDto } from './dto/search-institutes-dto';
import { getMonthShortName } from 'src/utils';
import { SearchIntakesDto } from './dto/search-intakes-dto';
import { SearchCommissionInstancesDto } from './dto/search-commission-instances-dto';
import { VmsVisaCasesEntity } from 'src/entities/VmsVisaCases.entity';
import { CourseMasterIntakeYearsEntity } from 'src/entities/CourseMasterIntakeYears.entity';
import { VC_COMMISSION_TYPE } from 'src/constants/visa/visa-commission';
import { SearchVisaCasesDto } from './dto/search-visa-cases-dto';
import { SearchCaseTuitionFeeDto } from './dto/search-case-tuition-fee-dto';
import { VmsCaseOfferLetterInstancesEntity } from 'src/entities/VmsCaseOfferLetterInstances.entity';
import { CreateCommissionEnquiryDto } from './dto/create.dto';
import { plainToInstance } from 'class-transformer';
import { VcmInstituteCommissionEnquiryDetailsEntity } from 'src/entities/VcmInstituteCommissionEnquiryDetails.entity';
import { CenterLocationEntity } from 'src/entities/CenterLocation.entity';
import { CompanyBankAccountEntity } from 'src/entities/CompanyBankAccount.entity';
import { CommissionDetailsTransformer } from '../course-portal/transformers/commission-details.transform';
import { UpdateReceivedStatusDto } from './dto/update-received-status.dto';
import { VcmCommissionResponseReasonEntity } from 'src/entities/VcmCommissionResponseReason.entity';
import { VcmCommissionDifferenceReasonEntity } from 'src/entities/VcmCommissionDifferenceReason.entity';
import { CreateResponseDto } from './dto/create-response.dto';
import { formatDate, getFinancialYear } from 'src/utils/common/global-utility';
import { CommissionReceivedDto } from './dto/create-received.dto';
import { VcmCommissionReceivedEntity } from 'src/entities/VcmCommissionReceived.entity';
import { CreateMaCommissionEnquiryDto } from './dto/create-ma.dto';
import { VcmMasterAgentCommissionEnquiryDetailsEntity } from 'src/entities/VcmMasterAgentCommissionEnquiryDetails.entity';
import { CreateMaResponseDto } from './dto/create-ma-response.dto';
import { VcmCommissionReceivedDetailsEntity } from 'src/entities/VcmCommissionReceivedDetails.entity';
import { VcmCommissionInvoiceEntity } from 'src/entities/VcmCommissionInvoice.entity';
import { SearchCaseCommissionReceivedInfoDto } from './dto/search-case-commission-received-info-dto';
import { VcmSubAgentCommissionDetailsEntity } from 'src/entities/VcmSubAgentCommissionDetails.entity';

/**
 * VisaCommissionEnquiryService - Core service for managing commission enquiries
 *
 * This service handles the complete lifecycle of commission enquiries for visa cases,
 * supporting both institute-based and master agent-based workflows. It manages:
 * - Commission enquiry creation and updates
 * - Commission calculation and validation
 * - Response management for institutes and agents
 * - Commission receipt tracking and reconciliation
 * - Invoice generation and financial tracking
 *
 * Business Domain: Visa Commission Tracking & Reconciliation System
 * - Dual workflow support (Institute Type 1 vs Master Agent Type 2)
 * - Complex commission calculations based on contracts and cases
 * - Financial transaction processing with invoice generation
 * - Response and difference reason management
 * - Comprehensive reporting and audit trails
 *
 * Architecture: Service-oriented with 19 entity integrations
 * - Transaction management for financial operations
 * - Complex query building with TypeORM
 * - Data transformation for UI display
 * - Caching for performance optimization
 *
 * Key Features:
 * - 30+ business methods for complete commission management
 * - Complex commission calculations with tiered structures
 * - Invoice generation with financial year tracking
 * - Multi-association type support
 * - Comprehensive error handling and logging
 */
@Injectable()
export class VisaCommissionEnquiryService {
  // Logger instance for service-specific logging and debugging
  private logger = new Logger(VisaCommissionEnquiryService.name);

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

    // Data transformer for commission details
    private readonly commissionDetailsTransformer: CommissionDetailsTransformer,

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

    // ==================== REPOSITORY INJECTIONS ====================
    // Reference data repositories
    @InjectRepository(VisaServiceMasterEntity)
    private readonly vsmRepo: Repository<VisaServiceMasterEntity>,

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

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

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

    @InjectRepository(VcmCommissionContractInstancesEntity)
    private readonly contractInstanceRepo: Repository<VcmCommissionContractInstancesEntity>,

    // Main commission enquiry repositories
    @InjectRepository(VcmInstituteCommissionEnquiryEntity)
    private readonly vcInstEnquiryRepo: Repository<VcmInstituteCommissionEnquiryEntity>,

    @InjectRepository(VcmMasterAgentCommissionEnquiryEntity)
    private readonly vcMaEnquiryRepo: Repository<VcmMasterAgentCommissionEnquiryEntity>,

    // Supporting entities
    @InjectRepository(VmsVisaCasesEntity)
    private readonly visaCaseRepo: Repository<VmsVisaCasesEntity>,

    @InjectRepository(CourseMasterIntakeYearsEntity)
    private readonly intakeYearRepo: Repository<CourseMasterIntakeYearsEntity>,

    @InjectRepository(VmsCaseOfferLetterInstancesEntity)
    private readonly offerLetterInstanceRepo: Repository<VmsCaseOfferLetterInstancesEntity>,

    // Commission enquiry details repositories
    @InjectRepository(VcmInstituteCommissionEnquiryDetailsEntity)
    private readonly vcInstEnquiryDetailsRepo: Repository<VcmInstituteCommissionEnquiryDetailsEntity>,

    @InjectRepository(VcmMasterAgentCommissionEnquiryDetailsEntity)
    private readonly vcMaEnquiryDetailsRepo: Repository<VcmMasterAgentCommissionEnquiryDetailsEntity>,

    // Financial and invoice repositories
    @InjectRepository(CenterLocationEntity)
    private readonly branchRepo: Repository<CenterLocationEntity>,

    @InjectRepository(CompanyBankAccountEntity)
    private readonly bankRepo: Repository<CompanyBankAccountEntity>,

    @InjectRepository(VcmCommissionInvoiceEntity)
    private readonly invoiceRepo: Repository<VcmCommissionInvoiceEntity>,

    // Response and reason repositories
    @InjectRepository(VcmCommissionResponseReasonEntity)
    private readonly resRepo: Repository<VcmCommissionResponseReasonEntity>,

    @InjectRepository(VcmCommissionDifferenceReasonEntity)
    private readonly diffRepo: Repository<VcmCommissionDifferenceReasonEntity>,

    // Commission received repositories
    @InjectRepository(VcmCommissionReceivedDetailsEntity)
    private readonly redDetailRepo: Repository<VcmCommissionReceivedDetailsEntity>,

    // Sub-agent commission repository
    @InjectRepository(VcmSubAgentCommissionDetailsEntity)
    private readonly vcSubAgentDetailsRepo: Repository<VcmSubAgentCommissionDetailsEntity>,
  ) {}

  /**
   * Find all commission enquiries with advanced filtering and pagination
   *
   * Supports filtering for both institute and master agent enquiry types
   * Includes comprehensive joins for related data
   *
   * @param dto - Search criteria including association type and various filters
   * @returns Paginated response with commission enquiry data
   */
  async findAll(
    dto: SearchDto,
  ): Promise<
    Response<
      | VcmInstituteCommissionEnquiryEntity
      | VcmMasterAgentCommissionEnquiryEntity
    >
  > {
    // Select appropriate repository based on association type
    const repo =
      dto.association_type === '1'
        ? this.vcInstEnquiryRepo
        : dto.association_type === '2'
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');

    // Add master agent join for association type 2
    if (dto.association_type === '2') {
      query
        .leftJoin('main.associate', 'associate')
        .addSelect(['associate.masteragentName', 'associate.uid']);
    }

    // Comprehensive joins for related data
    query
      .leftJoin('main.visa_country', 'country')
      .addSelect('country.name', 'country_name')
      .leftJoin('main.client_country', 'client_country')
      .addSelect('client_country.name', 'client_country_name')
      .leftJoin('main.institute', 'institute')
      .addSelect(['institute.institueName', 'institute.UID'])
      .leftJoin('main.intake', 'intake')
      .addSelect(['intake.intakeyear', 'intake.intakemonth'])
      .leftJoin('main.contract_set', 'contract_set')
      .addSelect(['contract_set.set_name'])
      .leftJoin('main.instance', 'instance')
      .addSelect(['instance.instance'])
      .leftJoin('main.branch', 'branch')
      .addSelect([
        'branch.center_name',
        'branch.address_line_1',
        'branch.tax_number',
      ])
      .leftJoin('main.bank', 'bank')
      .addSelect([
        'bank.bank_name',
        'bank.acc_number',
        'bank.company_bank_address',
        'bank.acc_holder',
        'bank.swift_code',
      ])
      .leftJoin('main.user', 'user')
      .addSelect(['user.fname', 'user.lname', 'user.employeeCode'])
      .leftJoin('main.modified_by', 'modified_by')
      .addSelect([
        'modified_by.fname',
        'modified_by.lname',
        'modified_by.employeeCode',
      ])
      .leftJoin('main.invoice', 'invoice')
      .addSelect(['invoice.invoice_no'])
      .orderBy('main.id', 'DESC');

    // Apply filter conditions
    this.filterWhereConditions(query, dto);

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

    return { list: data, pagination };
  }

  /**
   * Find a single commission enquiry by ID with detailed information
   *
   * @param dto - Contains enquiry ID and association type
   * @returns Detailed commission enquiry information
   */
  async findOne(
    dto: SearchDto,
  ): Promise<
    VcmInstituteCommissionEnquiryEntity | VcmMasterAgentCommissionEnquiryEntity
  > {
    // Select appropriate repository
    const repo =
      dto.association_type === '1'
        ? this.vcInstEnquiryRepo
        : dto.association_type === '2'
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');

    // Add master agent join for association type 2
    if (dto.association_type === '2') {
      query
        .leftJoin('main.associate', 'associate')
        .addSelect(['associate.masteragentName', 'associate.uid']);
    }

    // Comprehensive joins for detailed view
    query
      .leftJoin('main.visa_country', 'country')
      .addSelect('country.name', 'country_name')
      .leftJoin('main.client_country', 'client_country')
      .addSelect('client_country.name', 'client_country_name')
      .leftJoin('main.institute', 'institute')
      .addSelect(['institute.institueName', 'institute.UID'])
      .leftJoin('main.intake', 'intake')
      .addSelect(['intake.intakeyear', 'intake.intakemonth'])
      .leftJoin('main.branch', 'branch')
      .addSelect([
        'branch.center_name',
        'branch.address_line_1',
        'branch.tax_number',
      ])
      .leftJoin('main.bank', 'bank')
      .addSelect([
        'bank.bank_name',
        'bank.acc_number',
        'bank.company_bank_address',
        'bank.acc_holder',
        'bank.swift_code',
      ])
      .leftJoin('main.user', 'user')
      .addSelect(['user.fname', 'user.lname', 'user.employeeCode'])
      .leftJoin('main.modified_by', 'modified_by')
      .addSelect([
        'modified_by.fname',
        'modified_by.lname',
        'modified_by.employeeCode',
      ])
      .orderBy('main.id', 'DESC');

    // Filter by ID
    query.where('main.id = :id', { id: dto.id });

    return await query.getOne();
  }

  /**
   * Apply filter conditions to commission enquiry query
   *
   * Supports 15+ filter criteria including:
   * - Visa service countries
   * - Client countries
   * - Institutes
   * - Intakes
   * - Commission instances
   * - Associates (for master agents)
   * - Invoice branches and bank accounts
   * - Date ranges
   * - User filters
   *
   * @param query - TypeORM query builder
   * @param dto - Search criteria
   */
  private filterWhereConditions(
    query: SelectQueryBuilder<
      | VcmInstituteCommissionEnquiryEntity
      | VcmMasterAgentCommissionEnquiryEntity
    >,
    dto: SearchDto,
  ) {
    // Visa service country filter
    if (dto.visa_service_country_id && dto.visa_service_country_id.length > 0) {
      query.andWhere('main.visa_service_country_id IN (:...visa_country)', {
        visa_country: dto.visa_service_country_id,
      });
    }

    // Client country filter
    if (dto.client_from_country_id && dto.client_from_country_id.length > 0) {
      query.andWhere('main.client_from_country_id IN (:...client_country)', {
        client_country: dto.client_from_country_id,
      });
    }

    // Institute filter
    if (dto.institute_id && dto.institute_id.length > 0) {
      query.andWhere('main.institute_id IN (:...institute_id)', {
        institute_id: dto.institute_id,
      });
    }

    // Intake filter
    if (dto.intake_id && dto.intake_id.length > 0) {
      query.andWhere('main.intake_id IN (:...intake_id)', {
        intake_id: dto.intake_id,
      });
    }

    // Commission instance filter
    if (dto.commission_instance_id && dto.commission_instance_id.length > 0) {
      query.andWhere(
        'main.commission_instance_id IN (:...commission_instance_id)',
        {
          commission_instance_id: dto.commission_instance_id,
        },
      );
    }

    // Associate filter (master agents only)
    if (
      dto.association_type === '2' &&
      dto.associate_id &&
      dto.associate_id.length > 0
    ) {
      query.andWhere('main.associate_id IN (:...associate_id)', {
        associate_id: dto.associate_id,
      });
    }

    // Invoice bank account filter
    if (dto.invoice_bank_account_id && dto.invoice_bank_account_id.length > 0) {
      query.andWhere(
        'main.invoice_bank_account_id IN (:...invoice_bank_account_id)',
        {
          invoice_bank_account_id: dto.invoice_bank_account_id,
        },
      );
    }

    // Invoice branch filter
    if (dto.invoice_branch_id && dto.invoice_branch_id.length > 0) {
      query.andWhere('main.invoice_branch_id IN (:...invoice_branch_id)', {
        invoice_branch_id: dto.invoice_branch_id,
      });
    }

    // Received status filter
    if (dto.received_recorded && dto.received_recorded.length > 0) {
      query.andWhere('main.received_recorded IN (:...received_recorded)', {
        received_recorded: dto.received_recorded,
      });
    }

    // Created by user filter
    if (dto.created_by && dto.created_by.length > 0) {
      query.andWhere('main.by_user IN (:...created_by)', {
        created_by: dto.created_by,
      });
    }

    // Updated by user filter
    if (dto.updated_by && dto.updated_by.length > 0) {
      query.andWhere('main.modified_by_user IN (:...updated_by)', {
        updated_by: dto.updated_by,
      });
    }

    // Creation date range filter
    if (dto.created_on) {
      const { from, to } = dto.created_on;
      if (from && to) {
        query.andWhere('main.created BETWEEN :createdFrom AND :createdTo', {
          createdFrom: from,
          createdTo: to,
        });
      } else if (from) {
        query.andWhere('main.created >= :createdFrom', {
          createdFrom: from,
        });
      } else if (to) {
        query.andWhere('main.created <= :createdTo', {
          createdTo: to,
        });
      }
    }

    // Modification date range filter
    if (dto.updated_on) {
      const { from, to } = dto.updated_on;
      if (from && to) {
        query.andWhere('main.modified BETWEEN :modifiedFrom AND :modifiedTo', {
          modifiedFrom: from,
          modifiedTo: to,
        });
      } else if (from) {
        query.andWhere('main.modified >= :modifiedFrom', {
          modifiedFrom: from,
        });
      } else if (to) {
        query.andWhere('main.modified <= :modifiedTo', {
          modifiedTo: to,
        });
      }
    }
  }

  /**
   * Get visa service countries for a specific visa product
   *
   * @param productId - Visa product ID (e.g., 8 for Study Visa)
   * @returns List of countries where the visa service is offered
   */
  async getVisaCountryList(productId: string): Promise<any> {
    return await this.vsmRepo
      .createQueryBuilder('vsm')
      .innerJoin('vsm.country', 'country')
      .select([
        'country.country_id AS 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 master agents for a specific visa product
   *
   * @param params - Contains product_id for filtering
   * @returns List of master agents associated with the product
   */
  async getAssociateList(params: SearchAssociatesDto): Promise<any> {
    return await this.masterAgentRepo
      .createQueryBuilder('agent')
      .innerJoin('agent.products', 'products')
      .select(['agent.id AS id', 'agent.masteragentName AS name'])
      .where('products.masteragentProductIds = :product_id', {
        product_id: params.product_id,
      })
      .groupBy('agent.id')
      .orderBy('agent.masteragentName', 'ASC')
      .getRawMany();
  }

  /**
   * Get list of all active client countries
   *
   * Used for commission calculations based on student nationality
   *
   * @returns List of active countries with country details
   */
  async getClientCountryList() {
    const data = 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();
    return { list: data };
  }

  /**
   * Get list of institutes with successful visa cases
   *
   * Filters institutes that have successful visa cases for the specified country
   * Used for commission enquiry creation
   *
   * @param params - Contains country_id for filtering
   * @returns List of institutes with formatted names
   */
  async getInstituteList(params: SearchInstitutesDto): Promise<any> {
    const qb = this.collegeRepo
      .createQueryBuilder('clg')
      .innerJoin(
        'college_campus',
        'campus',
        'campus.instituteid = clg.collegeId',
      )
      .innerJoin('course_master', 'cm', 'cm.institueId = clg.collegeId')
      .innerJoin(
        'vms_case_course_preferences',
        'cp',
        'cp.course_id = cm.course_id',
      )
      .innerJoin('vms_visa_cases', 'vc', 'vc.id = cp.visa_case_id')
      .select([
        'clg.collegeId AS id',
        `CONCAT(clg.institueName, ' (', clg.UID, ')') AS name`,
      ])
      .where('campus.country_id = :country_id', {
        country_id: params.country_id,
      })
      // Only include institutes with successful closed visa cases
      .andWhere('vc.visa_status = :visaStatus', { visaStatus: 1 }) // Success
      .andWhere('vc.case_state = :caseState', { caseState: 3 }) // Closed
      .groupBy('clg.collegeId')
      .orderBy('clg.institueName', 'ASC');

    return await qb.getRawMany();
  }

  /**
   * Get intake periods for commission enquiries
   *
   * Filters intakes based on successful visa cases with specific criteria:
   * - Visa cases must be successful and closed
   * - Offer letters must be received
   * - Intake must be in the past
   *
   * @param params - Filter criteria including countries and institute
   * @returns List of intake periods with formatted names
   */
  async getIntakeList(params: SearchIntakesDto): Promise<any> {
    const currentYear = new Date().getFullYear();
    const currentMonth = new Date().getMonth() + 1;

    const qb = this.intakeYearRepo
      .createQueryBuilder('iy')
      .innerJoin(
        'vms_case_offer_letters',
        'ol',
        'ol.intake_year = iy.intakeyear AND ol.intake_month = iy.intakemonth',
      )
      .innerJoin('vms_visa_cases', 'vc', 'vc.id = ol.visa_case_id')
      .innerJoin(
        'vms_case_course_preferences',
        'cp',
        'cp.id = ol.course_preference_id',
      )
      .innerJoin('course_master', 'cm', 'cm.course_id = cp.course_id')
      .innerJoin('college_campus', 'cc', 'cc.instituteid = cm.institueId')
      .innerJoin('course_intake_section', 'cis', 'cis.course_id = cp.course_id')
      .innerJoin(
        'course_intake_section_client',
        'cisc',
        'cisc.intakeSectionId = cis.intakeSectionId',
      )
      .innerJoin(
        'course_master_intake_years',
        'iy2',
        'iy2.intakeSectionId = cis.intakeSectionId AND iy2.intakYId = iy.intakYId',
      )
      .select([
        'iy.intakYId AS id',
        'ol.intake_month AS intake_month',
        'ol.intake_year AS intake_year',
      ])
      // Filter criteria
      .where('vc.visa_status = :visaStatus', { visaStatus: 1 }) // Success
      .andWhere('vc.case_state = :caseState', { caseState: 3 }) // Closed
      .andWhere('ol.ol_status = :olStatus', { olStatus: 2 }) // Received
      .andWhere('cc.country_id = :visaServiceCountryId', {
        visaServiceCountryId: params.visa_service_country_id,
      })
      .andWhere('cisc.countryId = :clientFromCountryId', {
        clientFromCountryId: params.client_from_country_id,
      })
      .andWhere('cm.institueId = :instituteId', {
        instituteId: params.institute_id,
      })
      // Only include past intakes
      .andWhere(
        '(ol.intake_year < :currentYear OR (ol.intake_year = :currentYear AND ol.intake_month < :currentMonth))',
        { currentYear, currentMonth },
      )
      .groupBy('ol.intake_year')
      .addGroupBy('ol.intake_month')
      .orderBy('ol.intake_year', 'DESC')
      .addOrderBy('ol.intake_month', 'DESC');

    const result = await qb.getRawMany();

    // Format results with month names
    return result.map((r) => ({
      id: r.id,
      name: `${getMonthShortName(parseInt(r.intake_month, 10), false)} - ${r.intake_year}`,
    }));
  }

  /**
   * Get commission instances for commission enquiry creation
   *
   * Complex query that:
   * 1. Identifies successful visa cases for specific criteria
   * 2. Retrieves applicable commission contract instances
   * 3. Formats commission details for display
   *
   * @param params - Filter criteria including association type, countries, institute
   * @returns List of commission instances with detailed information
   */
  async getCommissionInstanceList(
    params: SearchCommissionInstancesDto,
  ): Promise<any> {
    // Get intake details
    const intake = await this.getIntakeById(params.intake_id);

    // Find successful visa cases matching criteria
    const cases = await this.visaCaseRepo
      .createQueryBuilder('vc')
      .innerJoin(
        'student_package',
        'sp',
        'sp.studentPackageID = vc.student_package_id',
      )
      .innerJoin('vms_case_offer_letters', 'ol', 'ol.visa_case_id = vc.id')
      .where('vc.visa_status = :visa_status', { visa_status: 1 }) // Success
      .andWhere('vc.case_state = :case_state', { case_state: 3 }) // Closed
      .andWhere('sp.product_id = :product_id', { product_id: 8 }) // Study Visa
      .andWhere('sp.bookedVisaCountry_id = :bookedVisaCountry_id', {
        bookedVisaCountry_id: params.visa_service_country_id,
      })
      .andWhere('sp.pack_country_id = :pack_country_id', {
        pack_country_id: params.client_from_country_id,
      })
      .andWhere('ol.intake_year = :intake_year', {
        intake_year: intake.intake_year,
      })
      .andWhere('ol.intake_month = :intake_month', {
        intake_month: intake.intake_month,
      })
      .select('DISTINCT vc.id', 'case_id')
      .getRawMany();

    const caseIds = cases.map((c) => c.case_id);
    const caseCount = caseIds.length;

    // Validate cases exist
    if (caseCount == 0) throw new Error('Visa Case not exists.');

    // Create commission type mapping
    const VC_COMMISSION_TYPE_MAP = VC_COMMISSION_TYPE.reduce(
      (acc, t) => ({ ...acc, [t.id]: t.name }),
      {} as Record<number, string>,
    );

    // Query commission contract instances
    const qb = this.contractInstanceRepo
      .createQueryBuilder('vcci')
      .select([
        'vcci.id AS id',
        'vcci.commission_type AS commission_type',
        'vcci.commission_amount AS commission_amount',
        'vcci.instance AS instance',
        'vccs.id AS contract_set_id',
        'vccs.set_name AS set_name',
        'vcca.id AS applicable_on_id',
        'vcc.currency AS currency',
      ])
      .innerJoin(
        'vcm_commission_contract_sets',
        'vccs',
        'vccs.id = vcci.commission_contract_set_id',
      )
      .innerJoin(
        'vcm_commission_contract_applicable_on',
        'vcca',
        'vcca.id = vccs.commission_contract_applicable_on_id',
      )
      .innerJoin(
        'vcm_commission_contracts',
        'vcc',
        'vcc.id = vcca.commission_contract_id',
      )
      .where('vcc.association_type = :associationType', {
        associationType: params.association_type,
      })
      .andWhere('vcc.product_id = :productId', { productId: 8 }) // Study Visa
      .andWhere('vcc.visa_service_country_id = :countryId', {
        countryId: params.visa_service_country_id,
      })
      .andWhere('vcc.institute_id = :instituteId', {
        instituteId: params.institute_id,
      });

    // Add associate filter for non-institute associations
    if (params.association_type !== 1 && params.associate_id) {
      qb.andWhere('vcc.associate_id = :associateId', {
        associateId: params.associate_id,
      });
    }

    qb.andWhere('vcc.active = :active', { active: 1 }).andWhere(
      `JSON_CONTAINS(vcc.client_from_countries, :country, '$')`,
      {
        country: JSON.stringify(params.client_from_country_id.toString()),
      },
    );

    const contract_result = await qb.getRawMany();

    // Validate contracts exist
    if (!contract_result.length) {
      throw new NotFoundException('Contract not exists.');
    }

    // Format commission instance information
    const result = contract_result.map((r) => {
      const typeName =
        VC_COMMISSION_TYPE_MAP[Number(r.commission_type)] ?? 'N/A';
      const amount =
        Number(r.commission_type) === 1
          ? `${r.commission_amount} ${r.currency}`
          : `${r.commission_percentage} %`;
      return {
        id: r.id,
        name: `${r.set_name} - ${typeName} - ${amount}`,
      };
    });

    return contract_result;
  }

  /**
   * Get intake details by ID
   *
   * @param intakeId - Intake year ID
   * @returns Intake year and month
   */
  async getIntakeById(intakeId: number) {
    return await this.intakeYearRepo
      .createQueryBuilder('intk')
      .select([
        'intk.intakeyear AS intake_year',
        'intk.intakemonth AS intake_month',
      ])
      .where('intk.intakYId = :id', { id: intakeId })
      .getRawOne();
  }

  /**
   * Get existing visa cases already included in commission enquiries
   *
   * Prevents duplicate inclusion of cases in commission enquiries
   *
   * @param params - Filter criteria including association type
   * @returns Array of visa case IDs already in commission enquiries
   */
  async getExistingCases(params: SearchVisaCasesDto): Promise<number[]> {
    // Repository mapping based on association type
    const repoMap = {
      1: this.vcInstEnquiryDetailsRepo,
      2: this.vcMaEnquiryDetailsRepo,
      3: this.vcSubAgentDetailsRepo,
    };

    const repo = repoMap[params.association_type];

    if (!repo) {
      return [];
    }

    // Query for existing cases with same commission enquiry criteria
    const qry = repo
      .createQueryBuilder('main')
      .select(['main.visa_case_id AS visa_case_id']);

    // Join appropriate enquiry table based on association type
    if (params.association_type === 1) {
      qry.innerJoin(
        'vcm_institute_commission_enquiry',
        'enq',
        'enq.id = main.commission_enquiry_id',
      );
    } else if (params.association_type === 2) {
      qry.innerJoin(
        'vcm_master_agent_commission_enquiry',
        'enq',
        'enq.id = main.commission_enquiry_id',
      );
    } else if (params.association_type === 3) {
      qry.innerJoin(
        'vcm_sub_agent_commission',
        'enq',
        'enq.id = main.sub_agent_commission_id',
      );
    }

    // Apply filters to match commission enquiry criteria
    qry
      .where('enq.visa_service_country_id = :visa_service_country_id', {
        visa_service_country_id: params.visa_service_country_id,
      })
      .andWhere('enq.institute_id = :institute_id', {
        institute_id: params.institute_id,
      })
      .andWhere('enq.client_from_country_id = :client_from_country_id', {
        client_from_country_id: params.client_from_country_id,
      })
      .andWhere('enq.intake_id = :intake_id', {
        intake_id: params.intake_id,
      })
      .andWhere('enq.applicable_on_id = :applicable_on_id', {
        applicable_on_id: params.applicable_on_id,
      })
      .andWhere('enq.contract_set_id = :contract_set_id', {
        contract_set_id: params.contract_set_id,
      })
      .andWhere('enq.commission_instance = :commission_instance', {
        commission_instance: params.commission_instance_id,
      });

    // Add associate filter for non-institute associations
    if (params.association_type !== 1 && params.associate_id) {
      qry.andWhere('enq.associate_id = :associateId', {
        associateId: params.associate_id,
      });
    }

    const rows = await qry.getRawMany();
    return rows.length > 0 ? rows.map((r) => r.visa_case_id) : [];
  }

  /**
   * Get visa cases for commission enquiry creation
   *
   * Filters successful visa cases based on multiple criteria
   * Excludes cases already included in commission enquiries
   *
   * @param params - Filter criteria including association type and various IDs
   * @returns List of visa cases eligible for commission enquiries
   */
  async getVisaCaseList(params: SearchVisaCasesDto): Promise<any> {
    // Get intake details
    const intake = await this.getIntakeById(params.intake_id);

    // Get existing cases to exclude
    const caseIds = await this.getExistingCases(params);

    // Query for eligible visa cases
    const qb = this.visaCaseRepo
      .createQueryBuilder('vc')
      .innerJoin(
        'student_package',
        'sp',
        'sp.studentPackageID = vc.student_package_id',
      )
      .innerJoin('students', 's', 's.id = vc.student_id')
      .innerJoin('vms_case_offer_letters', 'ol', 'ol.visa_case_id = vc.id')
      .innerJoin(
        'vms_case_course_preferences',
        'cp',
        'cp.id = ol.course_preference_id',
      )
      .innerJoin('college_campus', 'cc', 'cc.id = cp.college_campus_id')
      .where('vc.visa_status = :visa_status', { visa_status: 1 }) // Success
      .andWhere('vc.case_state = :case_state', { case_state: 3 }) // Closed
      .andWhere('sp.product_id = :product_id', { product_id: 8 }); // Study Visa

    // Exclude already included cases
    if (caseIds && caseIds.length > 0) {
      qb.andWhere('vc.id NOT IN (:...caseIds)', {
        caseIds,
      });
    }

    // Add master agent filter for association type 2
    if (params.association_type && params.association_type === 2) {
      qb.andWhere('ol.master_agent_id =:associate_id', {
        associate_id: params.associate_id,
      });
    }

    // Apply additional filters
    qb.andWhere('sp.bookedVisaCountry_id = :bookedVisaCountry_id', {
      bookedVisaCountry_id: params.visa_service_country_id,
    })
      .andWhere('sp.pack_country_id = :pack_country_id', {
        pack_country_id: params.client_from_country_id,
      })
      .andWhere('ol.intake_year = :intake_year', {
        intake_year: intake.intake_year,
      })
      .andWhere('ol.intake_month = :intake_month', {
        intake_month: intake.intake_month,
      })
      .andWhere('cc.instituteid = :institute_id', {
        institute_id: params.institute_id,
      })
      .select([
        'vc.id AS id',
        'vc.case_uid AS case_uid',
        's.UID AS student_uid',
        's.fname AS fname',
        's.lname AS lname',
        'vc.visa_status AS visa_status',
        'vc.visa_result_on AS visa_result_on',
        'ol.course_start_date AS course_start_date',
        'ol.course_end_date AS course_end_date',
      ])
      .groupBy('vc.id')
      .orderBy('vc.id', 'ASC');

    const result = await qb.getRawMany();

    // Validate results
    if (!result.length) {
      throw new NotFoundException('Case does not exists.');
    }

    return result;
  }

  /**
   * Get tuition fees for specific visa cases and commission instance
   *
   * Used for commission calculations based on tuition fees
   *
   * @param params - Contains visa case IDs and commission instance
   * @returns Tuition fee information for specified cases
   */
  async getCaseTuitionFeeList(params: SearchCaseTuitionFeeDto): Promise<any> {
    return await this.offerLetterInstanceRepo
      .createQueryBuilder('oli')
      .select(['oli.instance_fee', 'ol.visa_case_id'])
      .innerJoin('oli.offerLetter', 'ol')
      .where('oli.instance_no = :instance_no', {
        instance_no: params.commission_instance,
      })
      .andWhere('ol.visa_case_id IN (:...visa_case_ids)', {
        visa_case_ids: params.visa_case_ids,
      })
      .getRawMany();
  }

  /**
   * Get contract information for commission calculations
   *
   * @param params - Contract search criteria
   * @returns Contract instance information
   */
  async getContractInfo(params: {
    visa_service_country_id: number;
    institute_id: number;
    client_from_country_id: number;
    association_type: number;
  }): Promise<any[]> {
    const contract_result = await this.contractInstanceRepo
      .createQueryBuilder('vcci')
      .select([
        'vcci.id AS id',
        'vcci.commission_type AS commission_type',
        'vcci.commission_percentage AS commission_percentage',
        'vcci.commission_amount AS commission_amount',
        'vccs.set_name AS set_name',
        'vcc.currency AS currency',
      ])
      .innerJoin(
        'vcm_commission_contract_sets',
        'vccs',
        'vccs.id = vcci.commission_contract_set_id',
      )
      .innerJoin(
        'vcm_commission_contracts',
        'vcc',
        'vcc.id = vccs.commission_contract_id',
      )
      .where('vcc.association_type = :associationType', {
        associationType: params.association_type,
      })
      .andWhere('vcc.product_id = :productId', {
        productId: 8, // Study Visa
      })
      .andWhere('vcc.visa_service_country_id = :countryId', {
        countryId: params.visa_service_country_id,
      })
      .andWhere('vcc.associate_id = :associateId', {
        associateId: params.institute_id,
      })
      .andWhere('vcc.active = :active', { active: 1 })
      .andWhere(`JSON_CONTAINS(vcc.client_from_countries, :country, '$')`, {
        country: JSON.stringify(params.client_from_country_id.toString()),
      })
      .getRawMany();

    return contract_result;
  }

  /**
   * Get distinct visa service countries by association type
   *
   * Used for filter dropdown population
   *
   * @param association_type - Association type (1=Institute, 2=Master Agent)
   * @returns Distinct visa service countries
   */
  async fetchServiceCountriesByList(association_type: number) {
    // Select appropriate repository
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    if (!repo) return [];

    const query = repo.createQueryBuilder('main');
    query
      .leftJoin('main.visa_country', 'visa_country')
      .select(['visa_country.country_id AS id', 'visa_country.name AS name'])
      .orderBy('visa_country.name', 'ASC')
      .groupBy('main.visa_service_country_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct client countries by association type
   *
   * @param association_type - Association type
   * @returns Distinct client countries
   */
  async fetchClientCountriesByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    if (!repo) return [];

    const query = repo.createQueryBuilder('main');
    query
      .leftJoin('main.client_country', 'client_country')
      .select([
        'client_country.country_id AS id',
        'client_country.name AS name',
      ])
      .orderBy('client_country.name', 'ASC')
      .groupBy('main.client_from_country_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct institutes by association type
   *
   * @param association_type - Association type
   * @returns Distinct institutes
   */
  async fetchInstitutesByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    if (!repo) return [];

    const query = repo.createQueryBuilder('main');
    query
      .leftJoin('main.institute', 'institute')
      .select([
        'institute.collegeId AS id',
        `CONCAT(institute.institueName, " (", institute.UID, ")") AS name`,
      ])
      .orderBy('institute.institueName', 'ASC')
      .groupBy('main.institute_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct master agents for filter dropdowns
   *
   * @returns Distinct master agents
   */
  async fetchAssociatesByList() {
    const query = this.vcMaEnquiryRepo.createQueryBuilder('main');
    query
      .leftJoin('main.associate', 'associate')
      .select([
        'associate.id AS id',
        `CONCAT(associate.masteragentName, " (", associate.uid, ")") AS name`,
      ])
      .orderBy('associate.masteragentName', 'ASC')
      .groupBy('main.associate_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct intakes by association type
   *
   * @param association_type - Association type
   * @returns Distinct intake periods with formatted names
   */
  async fetchIntakesByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    if (!repo) return [];

    const query = repo.createQueryBuilder('main');
    const results = await query
      .leftJoin('main.intake', 'intake')
      .select('intake.intakYId', 'id')
      .addSelect('intake.intakemonth', 'month')
      .addSelect('intake.intakeyear', 'year')
      .orderBy('intake.intakeyear', 'ASC')
      .addOrderBy('intake.intakemonth', 'ASC')
      .groupBy('main.intake_id')
      .getRawMany();

    // Format results with month names
    return results.map((r) => ({
      id: r.id,
      name: `${getMonthShortName(parseInt(r.month, 10), false)} - ${r.year}`,
    }));
  }

  /**
   * Get distinct commission instances by association type
   *
   * @param association_type - Association type
   * @returns Distinct commission instances
   */
  async fetchInstancesByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');
    query
      .select(['main.commission_instance AS id', 'instance.instance AS name'])
      .leftJoin('main.instance', 'instance')
      .orderBy('instance.instance', 'ASC')
      .groupBy('main.commission_instance');

    return await query.getRawMany();
  }

  /**
   * Get distinct invoice branches by association type
   *
   * @param association_type - Association type
   * @returns Distinct invoice branches
   */
  async fetchInvoiceBranchesByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');
    query
      .innerJoin('main.branch', 'branch')
      .select(['main.invoice_branch_id AS id', 'branch.center_name AS name'])
      .orderBy('branch.center_name', 'ASC')
      .groupBy('main.invoice_branch_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct invoice bank accounts by association type
   *
   * @param association_type - Association type
   * @returns Distinct invoice bank accounts with formatted names
   */
  async fetchInvoiceBankAccountsByList(association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');
    query
      .innerJoin('main.bank', 'bank')
      .select([
        'main.invoice_bank_account_id AS id',
        `CONCAT(bank.acc_number, " - ", bank.bank_name, ", ",bank.company_bank_address) AS name`,
      ])
      .orderBy('bank.bank_name', 'ASC')
      .groupBy('main.invoice_bank_account_id');

    return await query.getRawMany();
  }

  /**
   * Get distinct users who created or updated enquiries
   *
   * @param by - Filter type ('created_by' or 'updated_by')
   * @param association_type - Association type
   * @returns Distinct users with formatted names
   */
  async fetchCreatedByOrUpdatedByUsers(by: string, association_type: number) {
    const repo =
      association_type === 1
        ? this.vcInstEnquiryRepo
        : association_type === 2
          ? this.vcMaEnquiryRepo
          : null;

    const query = repo.createQueryBuilder('main');

    if (by === 'created_by') {
      query
        .leftJoin('main.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('main.modified_by', 'modified_by')
        .select('modified_by.id', 'id')
        .addSelect(
          `CONCAT(modified_by.fname, ' ', modified_by.lname, ' - ', modified_by.employeeCode)`,
          'name',
        )
        .andWhere('main.modified_by IS NOT NULL')
        .orderBy('modified_by.fname', 'ASC')
        .addOrderBy('modified_by.lname', 'ASC')
        .groupBy('modified_by.id');
    }

    return await query.getRawMany();
  }

  /**
   * Create a new commission enquiry with invoice generation
   *
   * This method handles:
   * - Commission enquiry creation (institute or master agent)
   * - Commission enquiry details creation
   * - Invoice generation with financial year tracking
   * - Transaction management for data consistency
   *
   * @param createDto - Commission enquiry data
   * @param userId - User ID for audit tracking
   * @returns Created commission enquiry
   */
  async create(
    createDto: CreateCommissionEnquiryDto | CreateMaCommissionEnquiryDto,
    userId: string,
  ) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();

    try {
      // Determine if this is a master agent commission
      const isMACommission = createDto.association_type === 1 ? false : true;

      // Select appropriate entity class
      const entityClass =
        createDto.association_type === 1
          ? VcmInstituteCommissionEnquiryEntity
          : VcmMasterAgentCommissionEnquiryEntity;

      // Process master agent specific fields
      if (isMACommission) {
        createDto.ma_commission_type =
          (createDto.ma_commission_type as any) || '1';
        createDto.ma_commission_amount =
          parseFloat(createDto.ma_commission_amount as any) || null;
        createDto.ma_commission_percentage =
          parseFloat(createDto.ma_commission_percentage as any) || null;
      }

      // Create main commission enquiry entity
      const enquiryEntity = plainToInstance(
        entityClass as typeof VcmInstituteCommissionEnquiryEntity,
        {
          ...createDto,
          commission_instance: createDto.commission_instance_id,
          by_user: userId,
        },
      );

      const enquiry = await queryRunner.manager.save(enquiryEntity);

      // Create commission enquiry detail items
      const enquiryItems = createDto.items.map((item) => {
        const baseDetails = {
          ...item,
          id: undefined,
          by_user: userId,
          commission_enquiry_id: enquiry.id,
          tuition_fees: parseFloat(item.tuition_fees) || 0,
          commission_amount: parseFloat(item.commission_amount) || 0,
          commission_percentage: parseFloat(item.commission_percentage) || 0,
        };

        if (isMACommission) {
          return plainToInstance(VcmMasterAgentCommissionEnquiryDetailsEntity, {
            ...baseDetails,
            ma_commission_type: (item.ma_commission_type as number) || '1',
            ma_commission_amount: parseFloat(item.ma_commission_amount) || null,
            ma_commission_percentage:
              parseFloat(item.ma_commission_percentage) || null,
          });
        } else {
          return plainToInstance(
            VcmInstituteCommissionEnquiryDetailsEntity,
            baseDetails,
          );
        }
      });

      // Generate invoice if enquiry was created
      if (enquiry) {
        const financialYear = getFinancialYear();
        const branch = await queryRunner.manager.findOne(CenterLocationEntity, {
          where: { center_id: Number(createDto.invoice_branch_id) },
        });

        // Get latest invoice serial number for the branch and financial year
        const invoiceDetails = await queryRunner.manager.findOne(
          VcmCommissionInvoiceEntity,
          {
            where: {
              branch_state_id: Number(branch.state_id),
              financial_year: financialYear,
            },
            order: {
              serial_no: 'DESC',
            },
          },
        );

        // Calculate next serial number
        const serial = invoiceDetails ? invoiceDetails.serial_no + 1 : 1;
        const serialNo = serial < 10 ? `0${serial}` : serial;
        const stateCode = branch?.state_code_prefix;

        // Create invoice entity
        const invoiceEntity = plainToInstance(VcmCommissionInvoiceEntity, {
          enquiry_id: enquiry.id,
          association_type: createDto.association_type,
          branch_state_id: branch.state_id,
          financial_year: financialYear,
          serial_no: serialNo,
          invoice_no: `WOSA${stateCode}${financialYear}/${serialNo}`, // Format: WOSA{STATE}{YEAR}/{SERIAL}
        });

        await queryRunner.manager.save(invoiceEntity);
      }

      // Save enquiry items
      await queryRunner.manager.save(enquiryItems);
      await queryRunner.commitTransaction();
      return enquiry;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error('Create commission enquiry error', error?.stack);
      throw new InternalServerErrorException(
        'Failed to create commission enquiry',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Update an existing commission enquiry
   *
   * @param updateDto - Updated commission enquiry data
   * @param userId - User ID for audit tracking
   * @returns Update result
   */
  async update(
    updateDto: CreateCommissionEnquiryDto | CreateMaCommissionEnquiryDto,
    userId: string,
  ) {
    const id = updateDto.id;
    if (!id) throw new HttpException('Id is required', HttpStatus.BAD_REQUEST);

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

    try {
      const isMACommission = updateDto.association_type === 1 ? false : true;

      // Extract items and commission instance
      const commissionInstanceId = updateDto.commission_instance_id;
      const items = updateDto.items;
      delete updateDto.commission_instance_id;
      delete updateDto.items;

      // Select appropriate entity class
      const entityClass =
        updateDto.association_type === 1
          ? VcmInstituteCommissionEnquiryEntity
          : VcmMasterAgentCommissionEnquiryEntity;

      // Process master agent specific fields
      if (isMACommission) {
        updateDto.ma_commission_type =
          (updateDto.ma_commission_type as any) || '1';
        updateDto.ma_commission_amount =
          parseFloat(updateDto.ma_commission_amount as any) || null;
        updateDto.ma_commission_percentage =
          parseFloat(updateDto.ma_commission_percentage as any) || null;
      }

      delete updateDto.association_type;

      // Create update entity
      const enquiryEntity = plainToInstance(
        entityClass as typeof VcmInstituteCommissionEnquiryEntity,
        {
          ...updateDto,
          commission_instance: commissionInstanceId,
          modified_by_user: userId,
        },
      );

      // Update main enquiry
      const enquiry = await queryRunner.manager.update(
        entityClass,
        { id },
        enquiryEntity,
      );

      // Update enquiry detail items
      const enquiryItems = items.map((item) => {
        const baseDetails = {
          ...item,
          id: Number(item.id) || undefined,
          by_user: userId,
          commission_enquiry_id: id,
          tuition_fees: parseFloat(item.tuition_fees) || 0,
          commission_amount: parseFloat(item.commission_amount) || 0,
          commission_percentage: parseFloat(item.commission_percentage) || 0,
        };

        if (isMACommission) {
          return plainToInstance(VcmMasterAgentCommissionEnquiryDetailsEntity, {
            ...baseDetails,
            ma_commission_type: (item.ma_commission_type as number) || '1',
            ma_commission_amount: parseFloat(item.ma_commission_amount) || null,
            ma_commission_percentage:
              parseFloat(item.ma_commission_percentage) || null,
          });
        } else {
          return plainToInstance(
            VcmInstituteCommissionEnquiryDetailsEntity,
            baseDetails,
          );
        }
      });

      await queryRunner.manager.save(enquiryItems);

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

  /**
   * Get commission enquiry details by ID with transformation
   *
   * Includes comprehensive joins for student, case, and course information
   * Uses transformer for data formatting
   *
   * @param id - Commission enquiry ID
   * @param body - Additional parameters including association type
   * @returns Transformed commission enquiry details
   */
  async findDetailsById(id: string, body: Record<any, any>): Promise<any> {
    // Select appropriate repository based on association type
    const repo =
      (body.association_type as string) === '1'
        ? this.vcInstEnquiryDetailsRepo
        : this.vcMaEnquiryDetailsRepo;

    const query = repo.createQueryBuilder('main');

    // Comprehensive joins for detailed information
    query
      .leftJoin('vms_visa_cases', 'vc', 'main.visa_case_id = vc.id')
      .addSelect(['vc.case_uid'])
      .leftJoin('students', 's', 'vc.student_id = s.id')
      .addSelect(['s.fname', 's.lname', 's.UID'])
      .leftJoin('vms_case_offer_letters', 'ol', 'ol.visa_case_id = vc.id')
      .leftJoin(
        'vms_case_course_preferences',
        'cp',
        'ol.course_preference_id = cp.id',
      )
      .leftJoin('course_master', 'cm', 'cp.course_id = cm.course_id')
      .addSelect(['cm.course_name', 'cm.UID'])
      .leftJoin(
        'vcm_commission_response_reason',
        'r',
        'main.commission_response_reason_id = r.id',
      )
      .addSelect(['r.id', 'r.reason'])
      .leftJoin(
        'vcm_commission_difference_reason',
        'dr',
        'main.commission_difference_reason_id = dr.id',
      )
      .addSelect(['dr.id', 'dr.reason'])
      .where('main.commission_enquiry_id =:id', { id });

    // Apply response status filter if provided
    if (body.commission_response_status_id) {
      query.andWhere(
        new Brackets((qbInner) => {
          qbInner
            .where('main.commission_response_status_id IS NULL ')
            .orWhere('main.commission_response_status_id =:search', {
              search: `1`,
            });
        }),
      );
    }

    query.groupBy('main.id');

    const result = await query.getRawMany();

    // Transform results for UI display
    return this.commissionDetailsTransformer.transform(result);
  }

  /**
   * Get all active invoice branches
   *
   * @returns List of active physical branches
   */
  async fetchInvoiceBranches() {
    return this.branchRepo
      .createQueryBuilder('branch')
      .select(['branch.center_id AS id', 'branch.center_name AS name'])
      .where('branch.active = 1')
      .andWhere('branch.physical_branch = 1')
      .orderBy('branch.center_name')
      .getRawMany();
  }

  /**
   * Get all invoice bank accounts
   *
   * @returns List of bank accounts with formatted names
   */
  async fetchInvoiceBankAccounts() {
    return this.bankRepo
      .createQueryBuilder('bank')
      .select([
        `CONCAT(bank.acc_number, " - ", bank.bank_name, ", ",bank.company_bank_address) AS name`,
        'bank.bank_acc_id AS id',
      ])
      .orderBy('bank.bank_name')
      .getRawMany();
  }

  /**
   * Update commission enquiry received status
   *
   * @param updateStatusDto - Contains ID and received status
   * @param userId - User ID for audit tracking
   * @returns Update result
   */
  async updateReceivedStatus(
    updateStatusDto: UpdateReceivedStatusDto,
    userId: string,
  ): Promise<any> {
    const { id, received } = updateStatusDto;

    return await this.vcInstEnquiryRepo
      .createQueryBuilder()
      .update()
      .set({
        received_recorded: Number(received),
        modified_by_user: Number(userId),
      })
      .where('id =:id', { id })
      .execute();
  }

  /**
   * Get response reason list for commission enquiries
   *
   * @returns List of active response reasons
   */
  async getResponseReasonList() {
    const data = await this.resRepo
      .createQueryBuilder('reason')
      .select(['reason.id AS id', 'reason.reason AS name'])
      .where('reason.active = :active', { active: '1' })
      .orderBy('reason.reason', 'ASC')
      .getRawMany();
    return { list: data };
  }

  /**
   * Get difference reason list for commission enquiries
   *
   * @returns List of active difference reasons
   */
  async getDifferenceReasonList() {
    const data = await this.diffRepo
      .createQueryBuilder('reason')
      .select(['reason.id AS id', 'reason.reason AS name'])
      .where('reason.active = :active', { active: '1' })
      .orderBy('reason.reason', 'ASC')
      .getRawMany();
    return { list: data };
  }

  /**
   * Save response to institute commission enquiry
   *
   * Handles three response types:
   * 1. Simple update (response type 1)
   * 2. Insert new response with revision (response type 2)
   * 3. Rejection with reason (response type 3)
   *
   * @param dto - Response data including items
   * @param userId - User ID for audit tracking
   * @returns Saved response information
   */
  async saveResponse(dto: CreateResponseDto, userId: string): Promise<any> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();
    delete dto.association_type;

    try {
      const { id, commission_instance_id, items, ...rest } = dto;

      if (!id) {
        throw new HttpException('Id is required', HttpStatus.BAD_REQUEST);
      }

      // Update main enquiry entity
      const enquiryEntity = plainToInstance(
        VcmInstituteCommissionEnquiryEntity,
        {
          ...rest,
          accept_status: dto?.accept_status ?? null,
          commission_instance: commission_instance_id,
          modified_by_user: Number(userId),
        },
      );

      await queryRunner.manager.update(
        VcmInstituteCommissionEnquiryEntity,
        { id },
        enquiryEntity,
      );

      // Process each response item
      for (const item of items) {
        delete item.group_id;
        delete item.group_index;
        delete item.group_total;

        const base = {
          commission_date: formatDate(item.commission_date, 'YYYY-MM-DD'),
          commission_response_status_id: Number(item.response),
        };

        switch (item.response) {
          // -----------------------------------
          // CASE 1 → Simple Update
          // -----------------------------------
          case '1':
            await queryRunner.manager.update(
              VcmInstituteCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                modified_by_user: Number(userId),
              },
            );
            break;

          // -----------------------------------
          // CASE 2 → Insert New Response (with revision)
          // -----------------------------------
          case '2': {
            await queryRunner.manager.update(
              VcmInstituteCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                modified_by_user: Number(userId),
              },
            );

            // Create revised item if requested
            if (item.is_revised === '1') {
              const responseItem = plainToInstance(
                VcmInstituteCommissionEnquiryDetailsEntity,
                {
                  ...item,
                  ...base,
                  id: undefined,
                  commission_enquiry_id: Number(id),
                  commission_response_status_id: null,
                  commission_difference_reason_id:
                    item.difference_reason_id === ''
                      ? null
                      : item.difference_reason_id,
                  difference: item.difference === '' ? null : item.difference,
                  by_user: Number(userId),
                  modified_by_user: undefined,
                  tuition_fees: Number(item.tuition_fees) || 0,
                  commission_amount:
                    parseFloat(item.commission_amount as any) || 0,
                  commission_percentage:
                    parseFloat(item.commission_percentage as any) || 0,
                },
              );

              await queryRunner.manager.save(responseItem);
            }
            break;
          }

          // -----------------------------------
          // CASE 3 → Rejection Update
          // -----------------------------------
          case '3':
            await queryRunner.manager.update(
              VcmInstituteCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                commission_response_reason_id: item.response_reason_id
                  ? Number(item.response_reason_id)
                  : null,
                modified_by_user: Number(userId),
              },
            );
            break;

          default:
            this.logger.warn(`Unknown response type: ${item.response}`);
        }
      }

      await queryRunner.commitTransaction();
      return dto;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error(
        'Update institute commission enquiry error',
        error.stack,
      );

      throw new InternalServerErrorException(
        'Failed to update institute commission enquiry',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Save response to master agent commission enquiry
   *
   * Similar to institute response but with master agent specific fields
   *
   * @param dto - Master agent response data
   * @param userId - User ID for audit tracking
   * @returns Saved response information
   */
  async saveResponseMasterAgent(
    dto: CreateMaResponseDto,
    userId: string,
  ): Promise<any> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();
    delete dto.association_type;

    try {
      const { id, commission_instance_id, items, ...rest } = dto;

      if (!id) {
        throw new HttpException('Id is required', HttpStatus.BAD_REQUEST);
      }

      // Update master agent enquiry entity
      const enquiryEntity = plainToInstance(
        VcmMasterAgentCommissionEnquiryEntity,
        {
          ...rest,
          accept_status: dto?.accept_status ?? null,
          commission_instance: commission_instance_id,
          ma_commission_amount:
            parseFloat(dto.ma_commission_amount as any) || 0,
          ma_commission_percentage:
            parseFloat(dto.ma_commission_percentage as any) || 0,
          modified_by_user: Number(userId),
        },
      );

      await queryRunner.manager.update(
        VcmMasterAgentCommissionEnquiryEntity,
        { id },
        enquiryEntity,
      );

      // Process each response item
      for (const item of items) {
        delete item.group_id;
        delete item.group_index;
        delete item.group_total;

        const base = {
          commission_date: formatDate(item.commission_date, 'YYYY-MM-DD'),
          commission_response_status_id: Number(item.response),
        };

        switch (item.response) {
          // CASE 1 → Simple Update
          case '1':
            await queryRunner.manager.update(
              VcmMasterAgentCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                modified_by_user: Number(userId),
              },
            );
            break;

          // CASE 2 → Insert New Response (with revision)
          case '2': {
            await queryRunner.manager.update(
              VcmMasterAgentCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                modified_by_user: Number(userId),
              },
            );

            // Create revised item if requested
            if (item.is_revised === '1') {
              const responseItem = plainToInstance(
                VcmMasterAgentCommissionEnquiryDetailsEntity,
                {
                  ...item,
                  ...base,
                  id: undefined,
                  commission_enquiry_id: Number(id),
                  commission_response_status_id: null,
                  commission_difference_reason_id:
                    item.difference_reason_id === ''
                      ? null
                      : item.difference_reason_id,
                  difference: item.difference === '' ? null : item.difference,
                  by_user: Number(userId),
                  modified_by_user: undefined,
                  tuition_fees: Number(item.tuition_fees) || 0,
                  commission_amount:
                    parseFloat(item.commission_amount as any) || 0,
                  commission_percentage:
                    parseFloat(item.commission_percentage as any) || 0,
                  ma_commission_amount:
                    parseFloat(item.ma_commission_amount as any) || 0,
                  ma_commission_percentage:
                    parseFloat(item.ma_commission_percentage as any) || 0,
                },
              );

              await queryRunner.manager.save(responseItem);
            }
            break;
          }

          // CASE 3 → Rejection Update
          case '3':
            await queryRunner.manager.update(
              VcmMasterAgentCommissionEnquiryDetailsEntity,
              { id: item.id },
              {
                ...base,
                commission_response_reason_id: item.response_reason_id
                  ? Number(item.response_reason_id)
                  : null,
                modified_by_user: Number(userId),
              },
            );
            break;

          default:
            this.logger.warn(`Unknown response type: ${item.response}`);
        }
      }

      await queryRunner.commitTransaction();
      return dto;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error(
        'Update master agent commission enquiry response error',
        error.stack,
      );

      throw new InternalServerErrorException(
        'Failed to update master agent commission enquiry response',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Save commission received information for institute enquiries
   *
   * Creates commission received record and details
   * Updates enquiry received status
   * Calculates INR amounts based on conversion rates
   *
   * @param dto - Commission received data including items
   * @param userId - User ID for audit tracking
   * @returns Saved commission received information
   */
  async saveReceived(dto: CommissionReceivedDto, userId: string): Promise<any> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();

    try {
      const { commission_enquiry_id } = dto;
      const items = dto.items ? JSON.parse(dto?.items) : [];
      delete dto.items;

      if (!commission_enquiry_id) {
        throw new HttpException(
          'Commission Enquiry Id is required',
          HttpStatus.BAD_REQUEST,
        );
      }

      // Update enquiry received status
      const enquiryEntity = plainToInstance(
        VcmInstituteCommissionEnquiryEntity,
        {
          received_recorded: '1',
          modified_by_user: Number(userId),
        },
      );

      await queryRunner.manager.update(
        VcmInstituteCommissionEnquiryEntity,
        { id: commission_enquiry_id },
        enquiryEntity,
      );

      const createDto = dto;

      // Create commission received entity
      const receivedEntity = plainToInstance(VcmCommissionReceivedEntity, {
        ...createDto,
        other_deductions:
          createDto.other_deductions !== '' ? createDto.other_deductions : null,
        by_user: Number(userId),
      });

      const received = await queryRunner.manager.save(receivedEntity);

      // Create commission received detail items
      if (received && items.length > 0) {
        const detailEntities = items
          .filter((item) => item.response === '1') // Only include accepted responses
          .map((item) =>
            plainToInstance(VcmCommissionReceivedDetailsEntity, {
              ...item,
              commission_received_id: received.id,
              our_commission_type: item.commission_type,
              currency: dto.currency,
              our_commission_percentage:
                parseFloat(item.commission_percentage as any) || 0,
              our_commission_amount: item.commission_amount,
              // Calculate INR amount using conversion rate
              our_commission_amount_inr: parseFloat(
                (
                  parseFloat(item.commission_amount) *
                  parseFloat(received.conversion_rate)
                ).toFixed(2),
              ),
              by_user: Number(userId),
            }),
          );

        if (detailEntities.length > 0) {
          await queryRunner.manager.save(detailEntities);
        }
      }

      await queryRunner.commitTransaction();
      return dto;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error(
        'Update institute commission enquiry error',
        error.stack,
      );

      throw new InternalServerErrorException(
        'Failed to update institute commission enquiry',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Save commission received information for master agent enquiries
   *
   * Similar to institute received but with master agent entities
   *
   * @param dto - Master agent commission received data
   * @param userId - User ID for audit tracking
   * @returns Saved commission received information
   */
  async saveReceivedMasterAgent(
    dto: CommissionReceivedDto,
    userId: string,
  ): Promise<any> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();

    try {
      const { commission_enquiry_id } = dto;
      const items = dto.items ? JSON.parse(dto?.items) : [];
      delete dto.items;

      if (!commission_enquiry_id) {
        throw new HttpException(
          'Commission Enquiry Id is required',
          HttpStatus.BAD_REQUEST,
        );
      }

      // Update master agent enquiry received status
      const enquiryEntity = plainToInstance(
        VcmMasterAgentCommissionEnquiryEntity,
        {
          received_recorded: '1',
          modified_by_user: Number(userId),
        },
      );

      await queryRunner.manager.update(
        VcmMasterAgentCommissionEnquiryEntity,
        { id: commission_enquiry_id },
        enquiryEntity,
      );

      const createDto = dto;

      // Create commission received entity
      const receivedEntity = plainToInstance(VcmCommissionReceivedEntity, {
        ...createDto,
        other_deductions:
          createDto.other_deductions !== '' ? createDto.other_deductions : null,
        by_user: Number(userId),
      });

      const received = await queryRunner.manager.save(receivedEntity);

      // Create commission received detail items
      if (received && items.length > 0) {
        const detailEntities = items
          .filter((item) => item.response === '1') // Only include accepted responses
          .map((item) =>
            plainToInstance(VcmCommissionReceivedDetailsEntity, {
              ...item,
              commission_received_id: received.id,
              our_commission_type: item.commission_type,
              currency: dto.currency,
              our_commission_percentage:
                parseFloat(item.commission_percentage as any) || 0,
              our_commission_amount: item.commission_amount,
              // Calculate INR amount using conversion rate
              our_commission_amount_inr: parseFloat(
                (
                  parseFloat(item.commission_amount) *
                  parseFloat(received.conversion_rate)
                ).toFixed(2),
              ),
              by_user: Number(userId),
            }),
          );

        if (detailEntities.length > 0) {
          await queryRunner.manager.save(detailEntities);
        }
      }

      await queryRunner.commitTransaction();
      return dto;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      this.logger.error(
        'Update master agent commission received enquiry received recorded error',
        error.stack,
      );

      throw new InternalServerErrorException(
        'Failed to update master agent commission received enquiry received recorded',
      );
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Get commission received information for specific visa cases
   *
   * Used for reconciliation and reporting
   *
   * @param params - Contains visa case ID for lookup
   * @returns Commission received details for the case
   */
  async getCaseCommissionReceivedInfo(
    params: SearchCaseCommissionReceivedInfoDto,
  ): Promise<any> {
    const query = this.redDetailRepo.createQueryBuilder('main');
    query
      .select([
        'main.tuition_fees AS tuition_fees',
        'main.ma_commission_type AS ma_commission_type',
        'main.ma_commission_percentage AS ma_commission_percentage',
        'main.ma_commission_amount AS ma_commission_amount',
        'main.our_commission_type AS our_commission_type',
        'main.our_commission_percentage AS our_commission_percentage',
        'main.our_commission_amount AS our_commission_amount',
        'main.our_commission_amount_inr AS our_commission_amount_inr',
        'main.currency AS currency',
        'main.created AS created',
      ])
      .leftJoin('vms_visa_cases', 'vc', 'main.visa_case_id = vc.id')
      .addSelect(['vc.case_uid AS case_uid'])
      .leftJoin('students', 's', 'vc.student_id = s.id')
      .addSelect(['s.fname AS fname', 's.lname AS lname', 's.UID AS UID'])
      .where('main.visa_case_id =:visa_case_id', {
        visa_case_id: params.visa_case_id,
      })
      .orderBy('main.id', 'ASC');

    return await query.getRawMany();
  }
}
