// Import NestJS decorators, utilities, and exceptions
import {
  Inject,
  Injectable,
  InternalServerErrorException,
  Logger,
} from '@nestjs/common';

// Import TypeORM decorators for dependency injection
import { InjectRepository } from '@nestjs/typeorm';

// Import TypeORM core classes and types
import { DataSource, Repository, SelectQueryBuilder } from 'typeorm';

// Import cache-related dependencies
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';

// Import pagination utilities
import {
  getSkipOffset,
  paginate,
} from 'src/config/pagination/pagination.service';
import { Response } from 'src/config/pagination/paginationDto';

// Import multiple entity definitions (15+ entities!)
import { VisaServiceMasterEntity } from 'src/entities/VisaServiceMaster.entity';
import { CollegeEntity } from 'src/entities/College.entity';
import { CountryEntity } from 'src/entities/Country.entity';
import { VcmCommissionContractInstancesEntity } from 'src/entities/VcmCommissionContractInstances.entity';

// Import DTOs for request validation
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 { VcmSubAgentCommissionEntity } from 'src/entities/VcmSubAgentCommission.entity';
import { B2bAffiliateAccountsEntity } from 'src/entities/B2bAffiliateAccounts.entity';
import { SearchSubAgentCommissionDto } from './dto/search-dto';
import { B2bAffiliateAccountBanksEntity } from 'src/entities/B2bAffiliateAccountBanks.entity';
import { CompanyBankAccountEntity } from 'src/entities/CompanyBankAccount.entity';
import { SearchAssociateBanksDto } from './dto/search-associate-banks-dto';
import { plainToInstance } from 'class-transformer';
import { RemoveAttachmentDto } from './dto/remove-attachment.dto';
import { AddAttachmentDto } from './dto/add-attachment.dto';
import { FileService } from 'src/services/file.service';
import { CreateSubAgentCommissionDto } from './dto/create.dto';
import { VcmSubAgentCommissionDetailsEntity } from 'src/entities/VcmSubAgentCommissionDetails.entity';
import { SearchCommissionReceivedDto } from './dto/search-commission-received-dto';
import { VcmCommissionReceivedDetailsEntity } from 'src/entities/VcmCommissionReceivedDetails.entity';
import { UserDataProps } from 'src/utils/decorators/user-data.decorator';
import { formatDate } from 'src/utils/common/global-utility';

/**
 * SubAgentCommissionService - Service layer for sophisticated visa commission management
 *
 * This is a complex service handling commission calculations for education/visa services.
 * It integrates 15+ entities and implements intricate business logic for commission tracking.
 *
 * Business Domain: Education Visa Commission System
 * - Calculates commissions for student visa applications
 * - Tracks complex relationships between multiple entities
 * - Implements commission contract rules and calculations
 * - Manages commission payments and reconciliation
 *
 * Key Complexity Factors:
 * - 15+ entity repositories injected
 * - Complex multi-entity joins for commission calculations
 * - Sophisticated filtering and search capabilities
 * - Transaction management for financial data integrity
 * - Commission calculation based on multiple parameters
 */
@Injectable()
export class SubAgentCommissionService {
  // Logger instance for service-specific logging
  private logger = new Logger(SubAgentCommissionService.name);

  /**
   * Constructor with extensive dependency injection (15+ repositories!)
   *
   * This demonstrates a complex domain with many interrelated entities.
   */
  constructor(
    private dataSource: DataSource,
    private fileService: FileService,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
    @InjectRepository(VcmSubAgentCommissionEntity)
    private readonly vcsAgentRepo: Repository<VcmSubAgentCommissionEntity>,
    @InjectRepository(VcmSubAgentCommissionDetailsEntity)
    private readonly vcsAgentDetailsRepo: Repository<VcmSubAgentCommissionDetailsEntity>,
    @InjectRepository(VisaServiceMasterEntity)
    private readonly vsmRepo: Repository<VisaServiceMasterEntity>,
    @InjectRepository(CountryEntity)
    private readonly countryRepo: Repository<CountryEntity>,
    @InjectRepository(CollegeEntity)
    private readonly collegeRepo: Repository<CollegeEntity>,
    @InjectRepository(VmsVisaCasesEntity)
    private readonly visaCaseRepo: Repository<VmsVisaCasesEntity>,
    @InjectRepository(CourseMasterIntakeYearsEntity)
    private readonly intakeYearRepo: Repository<CourseMasterIntakeYearsEntity>,
    @InjectRepository(VcmCommissionContractInstancesEntity)
    private readonly contractInstanceRepo: Repository<VcmCommissionContractInstancesEntity>,
    @InjectRepository(B2bAffiliateAccountsEntity)
    private readonly associateRepo: Repository<B2bAffiliateAccountsEntity>,
    @InjectRepository(B2bAffiliateAccountBanksEntity)
    private readonly associateBankRepo: Repository<B2bAffiliateAccountBanksEntity>,
    @InjectRepository(CompanyBankAccountEntity)
    private readonly companyBankRepo: Repository<CompanyBankAccountEntity>,
    @InjectRepository(VcmCommissionReceivedDetailsEntity)
    private readonly crdRepo: Repository<VcmCommissionReceivedDetailsEntity>,
  ) {}

  /**
   * findAll - Retrieves paginated commission records with 12+ joins
   *
   * One of the most complex queries in the system with extensive joins:
   * - Associate information
   * - Visa and client countries
   * - Educational institute
   * - Academic intake
   * - Contract and instance details
   * - Payment mode and bank accounts
   * - Creator and updater user information
   *
   * Performance consideration: This query could be heavy. Consider:
   * - Indexing on frequently filtered columns
   * - Caching strategies
   * - Query optimization
   */
  async findAll(
    dto: SearchSubAgentCommissionDto,
  ): Promise<Response<VcmSubAgentCommissionEntity>> {
    const query = this.vcsAgentRepo.createQueryBuilder('main');

    // Complex join structure (12+ joins)
    query
      .leftJoin('main.associate', 'associate')
      .addSelect(['associate.affiliate_name', 'associate.uid'])
      .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.mode', 'mode')
      .addSelect(['mode.payment_mode_name'])
      .leftJoin('main.from_bank', 'from_bank')
      .addSelect([
        'from_bank.bank_name',
        'from_bank.acc_number',
        'from_bank.company_bank_address',
      ])
      .leftJoin('main.to_bank', 'to_bank')
      .addSelect([
        'to_bank.bank_name',
        'to_bank.acc_number',
        'to_bank.branch_address',
      ])
      .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');

    this.filterWhereConditions(query, dto);

    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 };
  }

  /**
   * filterWhereConditions - Complex filtering with 15+ conditions
   *
   * Handles multi-select filters, date ranges, and text search.
   * Implements comprehensive filtering for commission records.
   */
  private filterWhereConditions(
    query: SelectQueryBuilder<VcmSubAgentCommissionEntity>,
    dto: SearchSubAgentCommissionDto,
  ) {
    // Text search on transaction ID and remarks
    if (dto.search_title && dto.search_title.trim() !== '') {
      const keyword = `%${dto.search_title.trim().toLowerCase()}%`;
      query.andWhere(
        '(LOWER(main.transaction_id) LIKE :keyword OR LOWER(main.remarks) LIKE :keyword)',
        { keyword },
      );
    }

    // Multi-select filters (9 different multi-select filters)
    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,
      });
    }

    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,
      });
    }

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

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

    if (dto.commission_instance && dto.commission_instance.length > 0) {
      query.andWhere('main.commission_instance IN (:...commission_instance)', {
        commission_instance: dto.commission_instance,
      });
    }

    if (dto.associate_id && dto.associate_id.length > 0) {
      query.andWhere('main.associate_id IN (:...associate_id)', {
        associate_id: dto.associate_id,
      });
    }

    if (dto.payment_mode_id && dto.payment_mode_id.length > 0) {
      query.andWhere('main.payment_mode_id IN (:...payment_mode_id)', {
        payment_mode_id: dto.payment_mode_id,
      });
    }

    if (dto.from_bank_account_id && dto.from_bank_account_id.length > 0) {
      query.andWhere(
        'main.from_bank_account_id IN (:...from_bank_account_id)',
        {
          from_bank_account_id: dto.from_bank_account_id,
        },
      );
    }

    if (dto.to_bank_account_id && dto.to_bank_account_id.length > 0) {
      query.andWhere('main.to_bank_account_id IN (:...to_bank_account_id)', {
        to_bank_account_id: dto.to_bank_account_id,
      });
    }

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

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

    // Date range filters (2 date ranges)
    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,
        });
      }
    }

    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,
        });
      }
    }
  }

  /**
   * getVisaCountryList - Retrieves countries for specific visa services
   *
   * Business logic: Only returns countries where a specific visa service is active.
   */
  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();
  }

  /**
   * getAssociateList - Retrieves active affiliates for dropdowns
   */
  async getAssociateList(): Promise<any[]> {
    return this.associateRepo
      .createQueryBuilder('agent')
      .select([
        'agent.id AS id',
        `CONCAT(agent.affiliate_name, " (", agent.uid, ")") AS name`,
      ])
      .where('agent.status = :status', { status: 1 })
      .orderBy('agent.affiliate_name', 'ASC')
      .getRawMany();
  }

  /**
   * getClientCountryList - Retrieves active client countries
   */
  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 };
  }

  /**
   * getInstituteList - Complex institute filtering with multiple joins
   *
   * Business logic: Only returns institutes with successful visa cases
   * in specific countries, and only closed cases.
   *
   * 5-table join for sophisticated filtering.
   */
  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,
      })
      .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();
  }

  /**
   * getIntakeList - Complex intake filtering with 7+ joins
   *
   * Business logic: Returns intakes for specific criteria with past dates only.
   *
   * This query demonstrates sophisticated date-based filtering and
   * complex business rules around academic timelines.
   */
  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',
      ])
      .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,
      })
      .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();

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

  /**
   * getCommissionInstanceList - Most complex method in the service
   *
   * This method implements sophisticated commission calculation logic:
   * 1. Gets intake details
   * 2. Finds successful visa cases matching criteria
   * 3. Calculates commission based on contract rules
   * 4. Returns formatted commission instances
   *
   * Business logic: Commission calculation based on:
   * - Visa service country
   * - Client country
   * - Institute
   * - Intake timing
   * - Contract terms
   *
   * This is the heart of the commission calculation engine.
   */
  async getCommissionInstanceList(
    params: SearchCommissionInstancesDto,
  ): Promise<any> {
    // 1. Get intake details
    const intake = await this.getIntakeById(params.intake_id);

    // 2. 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;

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

    // 3. Map commission types for display
    const VC_COMMISSION_TYPE_MAP = VC_COMMISSION_TYPE.reduce(
      (acc, t) => ({ ...acc, [t.id]: t.name }),
      {} as Record<number, string>,
    );

    // 4. Find contract instances matching criteria
    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: 1 })
      .andWhere('vcc.product_id = :productId', { productId: 8 })
      .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();

    if (!contract_result.length) {
      throw new Error('Contract not exists.');
    }

    // 5. Format commission instances for display
    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 result;
  }

  /**
   * getIntakeById - Helper method to get intake details
   */
  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();
  }

  /**
   * getCompanyBankList - Retrieves company bank accounts
   */
  async getCompanyBankList(): Promise<any> {
    return await this.companyBankRepo
      .createQueryBuilder('cb')
      .select([
        'cb.bank_acc_id AS id',
        `CONCAT(cb.acc_number, ' - ', cb.bank_name, ', ', cb.company_bank_address) AS name`,
      ])
      .andWhere('cb.active = :active', {
        active: 1,
      })
      .getRawMany();
  }

  /**
   * getAssociateBankList - Retrieves affiliate bank accounts
   */
  async getAssociateBankList(params: SearchAssociateBanksDto): Promise<any> {
    return await this.associateBankRepo
      .createQueryBuilder('ab')
      .select([
        'ab.id AS id',
        `CONCAT(ab.acc_number, ' - ', ab.bank_name, ', ', ab.branch_address) AS name`,
      ])
      .where('ab.b2b_affiliate_account_id = :associate_id', {
        associate_id: params.associate_id,
      })
      .andWhere('ab.status = :status', {
        status: 1,
      })
      .getRawMany();
  }

  // ==================== FETCH BY LIST METHODS ====================
  // These methods return distinct values from existing commission records
  // Used for filter dropdowns in search interfaces

  async fetchServiceCountriesByList() {
    const query = this.vcsAgentRepo.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();
  }

  async fetchClientCountriesByList() {
    const query = this.vcsAgentRepo.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();
  }

  async fetchInstitutesByList() {
    const query = this.vcsAgentRepo.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();
  }

  async fetchAssociatesByList() {
    const query = this.vcsAgentRepo.createQueryBuilder('main');

    query
      .leftJoin('main.associate', 'associate')
      .select([
        'associate.id AS id',
        `CONCAT(associate.affiliate_name, " (", associate.uid, ")") AS name`,
      ])
      .orderBy('associate.affiliate_name', 'ASC')
      .groupBy('main.associate_id');

    return await query.getRawMany();
  }

  async fetchIntakesByList() {
    const query = this.vcsAgentRepo.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();

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

  async fetchInstancesByList() {
    const query = this.vcsAgentRepo.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();
  }

  async fetchPaymentModesByList() {
    const query = this.vcsAgentRepo.createQueryBuilder('main');
    query
      .innerJoin('main.mode', 'mode')
      .select(['main.payment_mode_id AS id', 'mode.payment_mode_name AS name'])
      .orderBy('mode.payment_mode_name', 'ASC')
      .groupBy('main.payment_mode_id');
    return await query.getRawMany();
  }

  async fetchFromBankAccountsByList() {
    const query = this.vcsAgentRepo.createQueryBuilder('main');
    query
      .innerJoin('main.from_bank', 'bank')
      .select([
        'main.from_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.from_bank_account_id');

    return await query.getRawMany();
  }

  async fetchToBankAccountsByList() {
    const query = this.vcsAgentRepo.createQueryBuilder('main');
    query
      .innerJoin('main.to_bank', 'ab')
      .select([
        'main.to_bank_account_id AS id',
        `CONCAT(ab.acc_number, ' - ', ab.bank_name, ', ', ab.branch_address) AS name`,
      ])
      .orderBy('ab.bank_name', 'ASC')
      .groupBy('main.to_bank_account_id');

    return await query.getRawMany();
  }

  async fetchCreatedByOrUpdatedByUsers(by: string) {
    const query = this.vcsAgentRepo.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();
  }

  /**
   * getAttachments - Retrieves file attachments for a commission
   */
  async getAttachments(id: string): Promise<any> {
    return await this.vcsAgentRepo
      .createQueryBuilder('main')
      .select(['main.id', 'main.attachment_file'])
      .where('main.id = :id', { id })
      .orderBy('main.id', 'DESC')
      .getOne();
  }

  /**
   * removeAttachments - Removes file attachment with physical file deletion
   */
  async removeAttachments(
    params: RemoveAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;
    repository = this.vcsAgentRepo;
    const record = await repository
      .createQueryBuilder()
      .where('id = :id', { id: params.row_id })
      .getOne();

    if (!record?.attachment_file?.length) return params;
    const updatedAttachments = record.attachment_file.filter(
      (file: string) => file !== params.file_name,
    );

    if (updatedAttachments.length !== record.attachment_file.length) {
      const entity = plainToInstance(VcmSubAgentCommissionEntity, {
        attachment_file: updatedAttachments,
        modified_by_user: userData.id,
      });
      await repository.update({ id: params.row_id }, entity);
      await this.fileService.removeFile(params.file_name);
    }
    return params;
  }

  /**
   * addAttachments - Adds new file attachments
   */
  async addAttachments(
    params: AddAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;

    repository = this.vcsAgentRepo;

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

    const updatedAttachments = record.attachment_file
      ? [...record.attachment_file, ...params.attachment_file]
      : params.attachment_file;

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

    return params;
  }

  /**
   * create - Creates commission with line items using transaction
   *
   * Business logic: Creates main commission record with commission line items.
   * Uses transaction to ensure data consistency between main record and line items.
   */
  async create(createDto: CreateSubAgentCommissionDto, userId: string) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();
    try {
      // Parse commission line items from JSON string
      const items =
        typeof createDto.items === 'string' ? JSON.parse(createDto.items) : [];
      delete createDto.items;

      // Create main commission entity
      const commissionEntity = plainToInstance(VcmSubAgentCommissionEntity, {
        ...createDto,
        commission_instance: createDto.commission_instance_id,
        payment_mode_id: createDto.payment_mode_id ?? 1,
        total_amount_inr: createDto.net_amount,
        net_amount_inr: createDto.net_amount,
        tax_details: createDto.tax_details
          ? JSON.parse(createDto.tax_details as any)
          : null,
        by_user: userId,
        payment_date: formatDate(createDto.payment_date, 'YYYY-MM-DD')
      });

      const commission = await queryRunner.manager.save(commissionEntity);

      // Create commission line items
      const commissionItems = items.map((item) => {
        return plainToInstance(VcmSubAgentCommissionDetailsEntity, {
          ...item,
          visa_case_id: item.id,
          id: undefined,
          commission_received: item.tuition_fees,
          by_user: userId,
          sub_agent_commission_id: commission.id,
        });
      });

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

  /**
   * getCaseCommissionReceivedList - Complex commission receipt tracking
   *
   * Business logic: Tracks commission received for specific visa cases.
   * Uses UNION query to combine commission from different sources.
   *
   * This is a complex query that demonstrates advanced SQL capabilities:
   * - UNION of two subqueries
   * - Multiple parameter bindings
   * - Complex filtering across multiple tables
   */
  async getCaseCommissionReceivedList(
    params: SearchCommissionReceivedDto,
  ): Promise<any> {
    const visaCaseIds = params.visa_case_id;
    const visaServiceCountryId = params.visa_service_country_id;
    const clientCountryId = params.client_from_country_id;
    const instituteId = params.institute_id;
    const intakeId = params.intake_id;
    const applicableOnId = params.applicable_on_id;
    const contractSetId = params.contract_set_id;
    const commissionInstance = params.commission_instance;

    // Subquery 1: Commission from institute commission enquiry
    const subQuery1 = this.crdRepo
      .createQueryBuilder()
      .select([
        'crd.our_commission_amount_inr AS commission_amount',
        'crd.visa_case_id AS visa_case_id',
      ])
      .from('vcm_commission_received_details', 'crd')
      .innerJoin(
        'vcm_commission_received',
        'cr',
        'cr.id = crd.commission_received_id',
      )
      .innerJoin(
        'vcm_institute_commission_enquiry',
        'ice',
        'ice.id = cr.commission_enquiry_id',
      )
      .where('crd.visa_case_id IN (:...visaCaseIds)', { visaCaseIds })
      .andWhere('ice.visa_service_country_id = :visaServiceCountryId', {
        visaServiceCountryId,
      })
      .andWhere('ice.client_from_country_id = :clientCountryId', {
        clientCountryId,
      })
      .andWhere('ice.institute_id = :instituteId', { instituteId })
      .andWhere('ice.intake_id = :intakeId', { intakeId })
      .andWhere('ice.applicable_on_id = :applicableOnId', { applicableOnId })
      .andWhere('ice.contract_set_id = :contractSetId', { contractSetId })
      .andWhere('ice.commission_instance = :commissionInstance', {
        commissionInstance,
      });

    // Subquery 2: Commission from master agent commission enquiry
    const subQuery2 = this.crdRepo
      .createQueryBuilder()
      .select([
        'crd.our_commission_amount_inr AS commission_amount',
        'crd.visa_case_id AS visa_case_id',
      ])
      .from('vcm_commission_received_details', 'crd')
      .innerJoin(
        'vcm_commission_received',
        'cr',
        'cr.id = crd.commission_received_id',
      )
      .innerJoin(
        'vcm_master_agent_commission_enquiry',
        'mace',
        'mace.id = cr.commission_enquiry_id',
      )
      .where('crd.visa_case_id IN (:...visaCaseIds)', { visaCaseIds })
      .andWhere('mace.visa_service_country_id = :visaServiceCountryId', {
        visaServiceCountryId,
      })
      .andWhere('mace.client_from_country_id = :clientCountryId', {
        clientCountryId,
      })
      .andWhere('mace.institute_id = :instituteId', { instituteId })
      .andWhere('mace.intake_id = :intakeId', { intakeId })
      .andWhere('mace.applicable_on_id = :applicableOnId', { applicableOnId })
      .andWhere('mace.contract_set_id = :contractSetId', { contractSetId })
      .andWhere('mace.commission_instance = :commissionInstance', {
        commissionInstance,
      });

    // Combine both subqueries with UNION ALL
    const qb = this.dataSource
      .createQueryBuilder()
      .select('*')
      .from(`(${subQuery1.getQuery()} UNION ALL ${subQuery2.getQuery()})`, 't')
      .setParameters({
        ...subQuery1.getParameters(),
        ...subQuery2.getParameters(),
      });

    return qb.getRawMany();
  }
  async findDetailsById(id: string): Promise<any> {
    const query = this.vcsAgentDetailsRepo.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'])
      .where('main.sub_agent_commission_id =:id', { id });

    query.groupBy('main.id');

    const result = await query.getRawMany();
    console.log('findDetailsById result', result);
    return result.map((r) => ({
      case_uid: r.vc_case_uid,
      student_name: `${r.s_fname} ${r.s_lname} (${r.s_UID})`,
      course_name: `${r.cm_course_name} (${r.cm_UID})`,
      fname: r.s_fname,
      lname: r.s_lname,
      student_uid: r.s_UID,
      id: r.main_id,
      sub_agent_commission_id: r.main_sub_agent_commission_id,
      visa_case_id: r.main_visa_case_id,
      commission_received: r.main_commission_received,
      commission_type: r.main_commission_type,
      commission_percentage: r.main_commission_percentage,
      commission_amount: r.main_commission_amount,
      by_user: r.main_by_user,
      created: r.main_created,
      modified_by_user: r.main_modified_by_user,
      modified: r.main_modified,
      vc_case_uid: r.vc_case_uid,
      s_UID: r.s_UID,
      s_fname: r.s_fname,
      s_lname: r.s_lname,
      cm_UID: r.cm_UID,
      cm_course_name: r.cm_course_name,
    }));
  }
}
