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

@Injectable()
export class VcElementsService {
  // Logger instance for service-specific logging
  private logger = new Logger(VcElementsService.name);

  constructor(
    private dataSource: DataSource,
    private fileService: FileService,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
    @InjectRepository(CourseMasterEntity)
    private readonly courseRepo: Repository<CourseMasterEntity>,
    @InjectRepository(VisaServiceMasterEntity)
    private readonly vsmRepo: Repository<VisaServiceMasterEntity>,
    @InjectRepository(CollegeEntity)
    private readonly collegeRepo: Repository<CollegeEntity>,
    @InjectRepository(MasteragentEntity)
    private readonly masterAgentRepo: Repository<MasteragentEntity>,
    @InjectRepository(B2bAffiliateAccountsEntity)
    private readonly b2bAffiliatesAccountRepo: Repository<B2bAffiliateAccountsEntity>,
    @InjectRepository(CountryEntity)
    private readonly countryRepo: Repository<CountryEntity>,
    @InjectRepository(CollegeCampusEntity)
    private readonly campusRepo: Repository<CollegeCampusEntity>,
    @InjectRepository(StudyLevelMasterEntity)
    private readonly slMasterRepo: Repository<StudyLevelMasterEntity>,
    @InjectRepository(VcmCommissionContractsEntity)
    private readonly vcmRepo: Repository<VcmCommissionContractsEntity>,
    @InjectRepository(VcmCommissionContractSetsEntity)
    private readonly vcmSetRepo: Repository<VcmCommissionContractSetsEntity>,
    @InjectRepository(VcmCommissionContractsHistoryEntity)
    private readonly vcmHistoryRepo: Repository<VcmCommissionContractsHistoryEntity>,
    @InjectRepository(StudyLevelMasterEntity)
    private readonly studyLevelRepo: Repository<StudyLevelMasterEntity>,
  ) {}

  async findAll(
    searchDto: SearchVisaCommissionDto,
  ): Promise<Response<VcmCommissionContractsEntity>> {
    const query = this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoin('vcm.visa_country', 'country')
      .addSelect('country.name', 'country_name')
      .leftJoin('vcm.user', 'user')
      .addSelect(['user.fname', 'user.lname', 'user.employeeCode'])
      .leftJoin('vcm.modified_by', 'modified_by')
      .addSelect([
        'modified_by.fname',
        'modified_by.lname',
        'modified_by.employeeCode',
      ])
      .leftJoin(
        'vcm.vcmCommissionContractsHistories',
        'vcmCommissionContractsHistories',
      )
      .addSelect(['vcmCommissionContractsHistories.id'])
      .leftJoin('vcm.institute', 'institute')
      .addSelect([
        'institute.collegeId',
        'institute.UID',
        'institute.institueName',
      ])
      .orderBy('vcm.id', 'DESC');

    if (searchDto.search_title) {
      query.andWhere('vcm.notes LIKE :search_title', {
        search_title: `%${searchDto.search_title}%`,
      });
    }

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

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

    if (
      searchDto.visa_client_country_id &&
      searchDto.visa_client_country_id.length > 0
    ) {
      searchDto.visa_client_country_id.forEach((countryId, index) => {
        query.andWhere(
          `JSON_CONTAINS(vcm.client_from_countries, :countryId${index})`,
          { [`countryId${index}`]: JSON.stringify(countryId) },
        );
      });
    }

    if (searchDto.association_type) {
      query.andWhere('vcm.association_type = :association_type', {
        association_type: searchDto.association_type,
      });
    }

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

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

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

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

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

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

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

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

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

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

    const [skip, limit] = getSkipOffset(searchDto);
    const [data, total] = await query.skip(skip).take(limit).getManyAndCount();
    const pagination = paginate(total, searchDto.per_page, searchDto.page);
    const finalResponse = await this.getVisaCommissionContract(
      data,
      searchDto.is_csv,
    );

    return { list: finalResponse, pagination };
  }

  private async getVisaCommissionContract(
    records: VcmCommissionContractsEntity[],
    isCsv: boolean = false,
  ): Promise<
    (VcmCommissionContractsEntity & {
      associate: { id: number; name: string } | null;
    })[]
  > {
    const enrichedRecords = records.map(async (record) => {
      let associate: { id: number; name: string } | null = null;

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

        case 3:
          associate = await this.b2bAffiliatesAccountRepo
            .createQueryBuilder('b2b')
            .select([
              'b2b.id AS id',
              `CONCAT(b2b.affiliate_name, ' (', b2b.uid, ')') AS name`,
            ])
            .where('b2b.id = :id', { id: record.associate_id })
            .getRawOne();
          break;
      }

      if (isCsv) {
        const countries = await this.countryRepo
          .createQueryBuilder('country')
          .select('country.name')
          .where('country.country_id IN (:...ids)', {
            ids: record.client_from_countries,
          })
          .getMany();

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

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

    return Promise.all(enrichedRecords);
  }

  async getVisaCountryList(productId: string): Promise<any> {
    return await this.vsmRepo
      .createQueryBuilder('vsm')
      .innerJoin('vsm.country', 'country')
      .select([
        'country.country_id AS country_id',
        'country.iso3 AS iso3',
        'country.name AS name',
        'country.country_code AS country_code',
      ])
      .where('vsm.active = :active', { active: '1' })
      .andWhere('vsm.visa_type = :productId', { productId: String(productId) })
      .groupBy('country.country_id')
      .getRawMany();
  }

  async getClientCountryList() {
    return await this.countryRepo
      .createQueryBuilder('country')
      .select([
        'country.country_id AS id',
        'country.iso3 AS iso3',
        'country.name AS name',
        'country.country_code AS country_code',
      ])
      .where('country.active = :active', { active: '1' })
      .groupBy('country.country_id')
      .orderBy('country.name', 'ASC')
      .getRawMany();
  }

  async getInstituteCampusList(
    params: SearchCollegeCampusDto,
  ): Promise<CollegeCampusEntity[]> {
    return await this.campusRepo
      .createQueryBuilder('campus')
      .innerJoin('campus.college', 'college')
      .where('college.active = :active', { active: '1' })
      .andWhere('campus.instituteid = :institute_id', {
        institute_id: params.institute_id,
      })
      .andWhere('campus.country_id = :country_id', {
        country_id: params.country_id,
      })
      .groupBy('campus.id')
      .orderBy('campus.campName', 'ASC')
      .getMany();
  }

  async getAssociateList(params: SearchAssociatesDto): Promise<any> {
    const { association_type } = params;

    switch (association_type) {
      case '1':
        return await this.fetchInstitutes(params);
      case '2':
        return await this.fetchMasterAgents(params);
      case '3':
        return await this.fetchSubAgents(params);

      default:
        return [];
    }
  }

  async getStudyLevelList(): Promise<StudyLevelMasterEntity[]> {
    const query = this.slMasterRepo
      .createQueryBuilder('slm')
      .select(['slm.id AS id', 'slm.study_level AS name'])
      .where('slm.domestic IS NULL')
      .orWhere('slm.domestic != :domestic', { domestic: '1' })
      .orderBy('slm.id', 'DESC');

    return await query.getRawMany();
  }

  private async fetchInstitutes(params: SearchAssociatesDto): Promise<any> {
    const { country_id } = params;

    const qb = this.collegeRepo
      .createQueryBuilder('college')
      .innerJoin('college.campus', 'campus')
      .select([
        'college.collegeId AS id',
        'CONCAT(college.institueName, " (", college.UID, ")") AS name',
      ]);
    if (country_id) {
      qb.where('campus.country_id = :country_id', {
        country_id,
      });
    }
    qb.groupBy('college.collegeId').orderBy('college.institueName', 'ASC');
    return await qb.getRawMany();
  }

  private async fetchMasterAgents(params: SearchAssociatesDto): Promise<any> {
    const { product_id } = params;

    return await this.masterAgentRepo
      .createQueryBuilder('agent')
      .innerJoin('agent.products', 'products')
      .select(['agent.id AS id'])
      .addSelect("CONCAT(agent.masteragentName, ' (', agent.uid, ')')", 'name')
      .where('products.masteragentProductIds = :product_id', {
        product_id,
      })
      .groupBy('agent.id')
      .orderBy('agent.masteragentName', 'ASC')
      .getRawMany();
  }

  private async fetchSubAgents(params: SearchAssociatesDto): Promise<any> {
    return await this.b2bAffiliatesAccountRepo
      .createQueryBuilder('b2b')
      .select([
        'b2b.id AS id',
        `CONCAT(b2b.affiliate_name, ' (', b2b.uid, ')') AS name`,
      ])
      .groupBy('b2b.id')
      .orderBy('b2b.affiliate_name', 'ASC')
      .getRawMany();
  }

  async createCommission(
    createDto: CreateCommissionContractDto,
    userData: UserDataProps,
  ) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.startTransaction();
    try {
      delete createDto.id;
      const contractEntity = plainToInstance(VcmCommissionContractsEntity, {
        ...createDto,
        associate_id:
          createDto.association_type !== '1' ? createDto.associate_id : null,
        by_user: userData.id,
        active: '1',
      });

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

      for (const applicableSet of createDto.applicable_sets) {
        const applicableOnEntity = plainToInstance(
          VcmCommissionContractApplicableOnEntity,
          {
            commission_contract_id: Number(contract.id),
            no_of_instances: applicableSet.no_of_instances,
            students_per: applicableSet.students_per,
            applicable_campuses: applicableSet.applicable_campuses ?? null,
            applicable_study_levels:
              applicableSet.applicable_study_levels ?? null,
            applicable_courses: applicableSet.applicable_courses ?? null,
            by_user: userData.id,
          },
        );

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

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

          allSetEntities.push(setEntity);
        }
      }

      const insertedSets = await queryRunner.manager.save(allSetEntities);

      let insertedSetIndex = 0;

      for (const applicableSet of createDto.applicable_sets) {
        for (const set of applicableSet.sets) {
          const insertedSet = insertedSets[insertedSetIndex++];
          let instanceIndex = 1;
          for (const instance of set.instances) {
            delete instance.id;
            const instanceEntity = plainToInstance(
              VcmCommissionContractInstancesEntity,
              {
                ...instance,
                commission_contract_set_id: insertedSet.id,
                instance: instanceIndex++,
                by_user: userData.id,
              },
            );
            allInstanceEntities.push(instanceEntity);
          }
        }
      }

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

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

  async editCommission(
    updateDto: CreateCommissionContractDto,
    userData: UserDataProps,
  ) {
    if (!updateDto.id)
      throw new HttpException('Id is required', HttpStatus.BAD_REQUEST);

    const contractId = updateDto.id;
    const existing = await this.getContractInfo(contractId);
    if (existing.attachment_file && existing.attachment_file.length > 0) {
      updateDto.attachment_file = existing.attachment_file;
    }
    const currentDataSets = structuredClone(updateDto);
    const differences = this.filterChanges(existing, updateDto);
    // console.log(existing, current, differences.length, { differences }); // keep it for debug

    if (differences.length === 0) return existing;

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

    try {
      const updateData = { ...updateDto };
      if (existing.attachment_file && existing.attachment_file.length > 0) {
        updateData.attachment_file = existing.attachment_file;
      }
      delete updateData.applicable_sets;
      const contractEntity = plainToInstance(VcmCommissionContractsEntity, {
        ...updateData,
        associate_id:
          updateDto.association_type !== '1'
            ? updateDto.association_type
            : null,
        modified_by_user: userData.id,
        active: '1',
      });
      const contract = await queryRunner.manager.update(
        VcmCommissionContractsEntity,
        { id: contractId },
        contractEntity,
      );

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

        if (applicableSet.id) {
          await queryRunner.manager.update(
            VcmCommissionContractApplicableOnEntity,
            { id: applicableSet.id },
            applicableOnEntity,
          );

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

            if (set.id) {
              await queryRunner.manager.update(
                VcmCommissionContractSetsEntity,
                { id: set.id },
                setEntity,
              );
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: set.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            } else {
              delete setEntity.id;
              const newSet = await queryRunner.manager.save(setEntity);
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: newSet.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  delete instance.id;
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            }
          }
        } else {
          delete applicableOnEntity.id;
          const savedApplicableOn =
            await queryRunner.manager.save(applicableOnEntity);

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

            if (set.id) {
              await queryRunner.manager.update(
                VcmCommissionContractSetsEntity,
                { id: set.id },
                setEntity,
              );
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: set.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            } else {
              delete setEntity.id;
              const newSet = await queryRunner.manager.save(setEntity);
              let instanceIndex = 1;
              for (const instance of vcmCommissionContractInstances) {
                const instanceEntity = plainToInstance(
                  VcmCommissionContractInstancesEntity,
                  {
                    ...instance,
                    commission_contract_set_id: newSet.id,
                    instance: instanceIndex++,
                    by_user: userData.id,
                  },
                );
                if (instance.id) {
                  await queryRunner.manager.update(
                    VcmCommissionContractInstancesEntity,
                    { id: instance.id },
                    instanceEntity,
                  );
                } else {
                  delete instanceEntity.id;
                  await queryRunner.manager.save(instanceEntity);
                }
              }
            }
          }
        }
      }

      const contractHistoryEntity = plainToInstance(
        VcmCommissionContractsHistoryEntity,
        {
          contract_id: contractId,
          previous_content: existing,
          current_content: currentDataSets,
          by_user: userData.id,
        },
      );
      await queryRunner.manager.save(contractHistoryEntity);

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

  async fetchServiceCountriesByList(association_type: number) {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoin('vcm.visa_country', 'country')
      .select([
        'DISTINCT vcm.visa_service_country_id AS id',
        'country.name AS name',
      ])
      .where('vcm.association_type = :association_type', { association_type })
      .orderBy('country.name', 'ASC')
      .getRawMany();
  }

  async fetchClientCountriesByList(association_type: number) {
    const sql = `SELECT DISTINCT c.country_id AS id, c.name AS name
    FROM vcm_commission_contracts vcm JOIN JSON_TABLE( vcm.client_from_countries,'$[*]' COLUMNS (country_id INT PATH '$')) jt ON TRUE
    JOIN country c ON c.country_id = jt.country_id WHERE vcm.association_type = ? ORDER BY c.name ASC`;

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

  async fetchAssociatesByList(association_type: number) {
    const qb = this.vcmRepo.createQueryBuilder('vcm');

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

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

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

  async fetchCreatedByOrUpdatedByUsers(by: string, association_type: number) {
    const query = this.vcmRepo
      .createQueryBuilder('vcm')
      .where('vcm.association_type = :association_type', { association_type });

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

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

    return await query.getRawMany();
  }

  async getCountriesByIds(countries: string[]): Promise<CountryEntity[]> {
    const query = this.countryRepo
      .createQueryBuilder('country')
      .select(['country.country_id AS id', 'country.name AS name'])
      .where('country.country_id IN (:...countries)', { countries })
      .orderBy('country.name', 'ASC');

    return await query.getRawMany();
  }

  async getCoursesByInstituteCampusStudyLevels(
    searchCoursesDto: SearchCollegeCampusCoursesDto,
  ) {
    const qb = this.courseRepo
      .createQueryBuilder('cm')
      .leftJoin('college_campus', 'cp', 'cp.instituteid = cm.institueId')
      .leftJoin(
        'course_master_studylevelinfo',
        'sl',
        'sl.course_id = cm.course_id',
      )
      .select([
        'cm.course_id AS id',
        "CONCAT(cm.course_name, ' (', cm.UID, ')') AS name",
      ]);
    if (searchCoursesDto.institute_id) {
      qb.where('cm.institueId = :instituteId', {
        instituteId: searchCoursesDto.institute_id,
      });
    }
    if (searchCoursesDto.campuses && searchCoursesDto.campuses.length > 0) {
      qb.andWhere('cp.id IN (:...campusIds)', {
        campusIds: searchCoursesDto.campuses,
      });
    }
    if (
      searchCoursesDto.study_levels &&
      searchCoursesDto.study_levels.length > 0
    ) {
      qb.andWhere('sl.studylevelId IN (:...studyLevelIds)', {
        studyLevelIds: searchCoursesDto.study_levels,
      });
    }
    return await qb
      .groupBy('cm.course_id')
      .orderBy('cm.course_name', 'ASC')
      .getRawMany();
  }

  async getContractAttachments(id: string): Promise<any> {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .select(['vcm.id', 'vcm.attachment_file'])
      .leftJoinAndSelect(
        'vcm.vcm_commission_contract_applicable_sets',
        'vcm_commission_contract_applicable_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_applicable_sets.vcm_commission_contract_sets',
        'vcm_commission_contract_sets',
      )
      .addSelect([
        'vcm_commission_contract_sets.id',
        'vcm_commission_contract_sets.attachment_file',
        'vcm_commission_contract_sets.set_name',
      ])
      .where('vcm.id = :id', { id })
      .orderBy('vcm.id', 'DESC')
      .getOne();
  }

  async getContractInfo(id: string): Promise<any> {
    return await this.vcmRepo
      .createQueryBuilder('vcm')
      .leftJoinAndSelect(
        'vcm.vcm_commission_contract_applicable_sets',
        'vcm_commission_contract_applicable_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_applicable_sets.vcm_commission_contract_sets',
        'vcm_commission_contract_sets',
      )
      .leftJoinAndSelect(
        'vcm_commission_contract_sets.vcm_commission_contract_instances',
        'vcm_commission_contract_instances',
      )
      .where('vcm.id = :id', { id })
      .orderBy('vcm_commission_contract_applicable_sets.id', 'ASC')
      .addOrderBy('vcm_commission_contract_instances.instance', 'ASC')
      .getOne();
  }

  private filterChanges(
    existing: Record<string, any>,
    updateDto: CreateCommissionContractDto,
  ): any[] {
    const keysToRemove = [
      'active',
      'by_user',
      'created',
      'modified_by_user',
      'modified',
      'vcm_commission_contract_applicable_sets',
      'applicable_sets', // Add this to exclude from main comparison
    ];

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

    const differences = deepCompareObjectsSmart(
      existing,
      updateDto,
      '',
      keysToRemove,
    );

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    return allDifferences;
  }

  async removeContractAttachments(
    params: RemoveAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;
    let entityClass;
    let idField: string;

    if (params.contract_id) {
      repository = this.vcmRepo;
      entityClass = VcmCommissionContractsEntity;
      idField = 'contract_id';
    } else if (params.set_id) {
      repository = this.vcmSetRepo;
      entityClass = VcmCommissionContractSetsEntity;
      idField = 'set_id';
    } else {
      return params;
    }

    const record = await repository
      .createQueryBuilder()
      .where('id = :id', { id: params[idField] })
      .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(entityClass, {
        attachment_file: updatedAttachments,
        modified_by_user: userData.id,
      });
      await repository.update({ id: params[idField] }, entity);

      await this.fileService.removeFile(params.file_name);
    }

    return params;
  }

  async addContractAttachments(
    params: AddAttachmentDto,
    userData: UserDataProps,
  ): Promise<any> {
    let repository;
    let entityClass;

    if (params.type === 'contract') {
      repository = this.vcmRepo;
      entityClass = VcmCommissionContractsEntity;
    } else if (params.type === 'set') {
      repository = this.vcmSetRepo;
      entityClass = VcmCommissionContractSetsEntity;
    } else {
      return params;
    }

    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(entityClass, {
        attachment_file: updatedAttachments,
        modified_by_user: userData.id,
      });
      await repository.update({ id: params.id }, entity);
    }

    return params;
  }

  async getContractEditHistoryWithLabels(contractId: string): Promise<any[]> {
    const history = await this.vcmHistoryRepo
      .createQueryBuilder('history')
      .leftJoinAndSelect('history.user', 'user')
      .where('history.contract_id = :contractId', { contractId })
      .orderBy('history.id', 'DESC')
      .getMany();

    const courseIdsSet = new Set<string>();
    const campusIdsSet = new Set<string>();
    const studyLevelIdsSet = new Set<string>();
    const countryIdsSet = new Set<string>();

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

      const currentContent =
        typeof item.current_content === 'string'
          ? JSON.parse(item.current_content)
          : item.current_content;
      // console.log({ previousContent, currentContent });
      previousContent.vcm_commission_contract_applicable_sets?.forEach(
        (set: any) => {
          if (set.applicable_courses)
            set.applicable_courses.forEach((id: string) =>
              courseIdsSet.add(id),
            );
          if (set.applicable_campuses)
            set.applicable_campuses.forEach((id: string) =>
              campusIdsSet.add(id),
            );
          if (set.applicable_study_levels)
            set.applicable_study_levels.forEach((id: string) =>
              studyLevelIdsSet.add(id),
            );
        },
      );

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

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

    const courseIds = Array.from(courseIdsSet);
    const campusIds = Array.from(campusIdsSet);
    const studyLevelIds = Array.from(studyLevelIdsSet);
    const countryIds = Array.from(countryIdsSet);

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

    const coursesMapping = courses.reduce<Record<string, string>>((map, c) => {
      map[c.course_id] = c.course_name;
      return map;
    }, {});

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

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

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

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

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

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

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

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

    return transformedHistory;
  }

  async updateContractStatus(
    userId: string,
    updateStatusDto: UpdateContractStatusDto,
  ) {
    const { ids, action } = updateStatusDto;

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

  async getContractSetDetailsById(id: string): Promise<any> {
    return await this.vcmSetRepo
      .createQueryBuilder('set')
      .where('set.id = :id', { id })
      .getOne();
  }
}
