import { HttpException, HttpStatus, Inject, Injectable, Logger, forwardRef } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { ILike, Repository } from 'typeorm';
import { OrganizationChartBody, OrganizationChartExcelQuery, OrganizationChartTableQuery, OrganizationChartUpdate, TreeNode } from '../dto/organization-chart.dto';
import { OrganizationChart } from '../entities/organization-chart.entity';
import { ClientService } from 'src/client/client.service';
import { SalesForceService } from '../sales-force/sales-force.services';
import { excelToJson } from 'src/util/excelToJson';
import { LinkedDistributorsChart } from '../entities/linked-distributors-chart.entity';
import { SharedService } from 'src/provider/shared-api/shared.services';
import { SalesForce } from '../entities/sales-force.entity';
import * as ExcelJS from 'exceljs';

@Injectable()
export class OrganizationChartService {
  private logger = new Logger(OrganizationChartService.name)
  constructor(
    @InjectRepository(OrganizationChart)
    private readonly organizationChartRepository: Repository<OrganizationChart>,
    @Inject(forwardRef(() => SalesForceService))
    private readonly salesForceService: SalesForceService,
    private readonly clientService: ClientService,
    private readonly sharedService: SharedService,

  ) { }

  async getIdsOrganizationChart(salesForceId) {
    const organizationCharts = await this.organizationChartRepository.findBy({ salesForceId })
    const idOrganizationCharts = organizationCharts.map(item => item.id);

    return idOrganizationCharts
  }

  async createOrganizationChart(body: OrganizationChartBody, clientId: number, authorization: string) {
    const organizationChart = new OrganizationChart();
    organizationChart.accessUserCode = body.accessUserCode;
    organizationChart.internalCode = body.internalCode;
    organizationChart.level = body.level;
    organizationChart.previousId = body.previousId;
    organizationChart.name = body.name;
    organizationChart.color = body.color;
    organizationChart.userName = body.userName;
    organizationChart.salesForceId = body.salesforceId;
    organizationChart.isSensitive = body.isSensitive;
    organizationChart.email = body.email;
    if (body.isSensitive && !body.email) {
      throw new HttpException('email_is_required', HttpStatus.BAD_REQUEST);
    }
    const regexEmail = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
    if (body.email && !regexEmail.test(body.email)) {
      throw new HttpException('email_invalid', HttpStatus.BAD_REQUEST)
    }

    const salesforceLevel = await this.clientService.getClientIdSalesForceLevel(clientId);
    if (!salesforceLevel?.client) {
      throw new HttpException('client_id_not_found', 404);
    }
    const salesForce = await this.salesForceService.getSalesForceId(body.salesforceId, +salesforceLevel.client.mtrixCode);

    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', 404);
    }
    if (body.level === salesForce.countLevel && !!body.previousId) {
      throw new HttpException("previous_id_invalid_there_cant_be_a_previus_id", 504);
    }
    else if (salesForce.countLevel > body.level && !body.previousId) {
      throw new HttpException('previous_id_invalid_there_has_to_be_a_previus_id', 504);
    }

    if (body.previousId) {
      const organizationChartPrevius = await this.organizationChartRepository.findOneBy({ id: body.previousId })

      if (organizationChartPrevius && (body.level + 1 !== organizationChartPrevius.level)) {
        throw new HttpException('previousId_invalid_the_level_of_the_previous_node_has_to_be_higher_only_one_level', 504);
      }
    }

    if (salesforceLevel.countSalesForceLevel < body.level) {
      throw new HttpException('salesforceLevel_invalid_client_has_different_configuration_of_the_data_provided', 504);
    }
    salesForce.canImportExcel = false;
    await this.salesForceService.updateSalesForce(salesForce);
    return this.organizationChartRepository.save(organizationChart)
  }

  async createOrganizationChartInitSalesForce(salesForceId: number, clientId: number) {
    let organizationChartArray: OrganizationChart[] = [];
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);

    for (let i = client.countSalesForceLevel; i > 0; i--) {
      const organizationChart = new OrganizationChart();
      // organizationChart.previousId = ;
      organizationChart.name = 'NC';
      organizationChart.level = i
      organizationChart.salesForceId = salesForceId;
      organizationChartArray.push(organizationChart);

    }
    let previousId = undefined;
    let organizationChartArraySave: OrganizationChart[] = [];
    for await (let organizationChartItem of organizationChartArray) {
      organizationChartItem.previousId = previousId;
      await this.organizationChartRepository.save(organizationChartItem);
      previousId = organizationChartItem.id;
      organizationChartArraySave.push(organizationChartItem);
    }
    return organizationChartArraySave;
    // return this.organizationChartRepository.save(organizationChart);
  }

  async putOrganizationChart(body: OrganizationChartUpdate, clientId: number, id: number) {
    const salesforceLevel = await this.clientService.getClientIdSalesForceLevel(clientId);
    if (!salesforceLevel?.client) {
      throw new HttpException('client_id_not_found', 404);
    }
    const organizationChart = await this.organizationChartRepository.findOneBy({ id })
    organizationChart.accessUserCode = body.accessUserCode;
    organizationChart.internalCode = body.internalCode;
    organizationChart.name = body.name;
    organizationChart.color = body.color;
    organizationChart.userName = body.userName;
    organizationChart.isSensitive = body.isSensitive;
    organizationChart.email = body.email;
    if (body.isSensitive && !body.email) {
      throw new HttpException('email_is_required', HttpStatus.BAD_REQUEST);
    }
    const regexEmail = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
    if (body.email && !regexEmail.test(body.email)) {
      throw new HttpException('email_invalid', HttpStatus.BAD_REQUEST)
    }
    const salesForce = await this.salesForceService.getSalesForceId(organizationChart.salesForceId, +salesforceLevel.client.mtrixCode);

    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', 404);
    }

    salesForce.canImportExcel = false;
    await this.salesForceService.updateSalesForce(salesForce);
    return this.organizationChartRepository.save(organizationChart)
  }
  buildRelations(n: number): string[] {
    if (n <= 0) {
      return [];
    }

    const relation = n === 1 ? 'previusOrganizationChart' : `previusOrganizationChart`;
    const parentRelation = this.buildRelations(n - 1);

    return parentRelation.length > 0
      ? [`${relation}.${parentRelation[0]}`, ...parentRelation]
      : [relation];
  }

  async getOrganizationChart(clientId: any, salesForceId: number, query: OrganizationChartTableQuery) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', HttpStatus.FAILED_DEPENDENCY);
    }
    const take = query.pageRows || 10;
    const page = query.pageNumber || 1;
    const skip = (page - 1) * take;

    const relations = this.buildRelations(salesForce.countLevel).reverse();
    const [organizationCharts, count] = await this.organizationChartRepository.findAndCount({
      where: {
        salesForceId,
        name: ILike(`%${query.name || ''}%`),
        linkedDistributorsChart: {
          distributorId: query.distributorId || undefined,
        },
        level: 1,
      },
      relations,
      order: { id: 'ASC' },
      skip, take
    });
    let namePropertiesLevel = salesForce.salesForceLevelConfig.map(elem => elem.description);
    let headers = [];
    let wch = [];
    namePropertiesLevel.forEach(item => {
      wch = [...wch, { wch: 20 }, { wch: 20 }, { wch: 20 }];
      headers = [item.toUpperCase(), `${item.toUpperCase()} PV`, `${item.toUpperCase()} USERNAME`, `${item.toUpperCase()} EMAIL`, `${item.toUpperCase()} COLOR`, ...headers]
    })
    headers = ['ID', 'SALES FORCE', ...headers]
    const formatTableOrganizationCharts = organizationCharts.map(item => {

      let organization = {
        id: item.id,
        nameSalesForce: salesForce.nameSalesForce,
        name: item.name,
        isSensitive: item.isSensitive,
        userName: item.userName,
        email: item.email,
        color: item.color,
      }
      let count = salesForce.countLevel;
      let previusOrganizationChart = item.previusOrganizationChart
      for (let i = 2; i <= count; i++) {
        if (i < count && !previusOrganizationChart) {
          organization[`nameNivel${i}`] = 'NC';
          organization[`isSensitiveNivel${i}`] = false;
          organization[`userNameNivel${i}`] = '';
          organization[`emailNivel${i}`] = '';
          organization[`colorNivel${i}`] = '';
          throw new HttpException(`not_invalid_sales_force_level_verify_configuration`, HttpStatus.FAILED_DEPENDENCY)
        }
        else {
          organization[`nameNivel${i}`] = previusOrganizationChart.name;
          organization[`isSensitiveNivel${i}`] = previusOrganizationChart.isSensitive;
          organization[`userNameNivel${i}`] = previusOrganizationChart.userName;
          organization[`emailNivel${i}`] = previusOrganizationChart.email;
          organization[`colorNivel${i}`] = previusOrganizationChart.color;
          previusOrganizationChart = previusOrganizationChart?.previusOrganizationChart;
        }
      }
      return organization;
    })

    return {
      recordset: {
        salesForce, headers, organizationCharts:
          formatTableOrganizationCharts
      },
      count
    };
  }

  async getOrganizationChartDistributor(clientId: any, salesForceId: number, query: OrganizationChartTableQuery) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', HttpStatus.FAILED_DEPENDENCY);
    }
    const take = query.pageRows || 10;
    const page = query.pageNumber || 1;
    const skip = (page - 1) * take;

    const queryBuilder = this.organizationChartRepository.createQueryBuilder('oc');
    queryBuilder.leftJoinAndSelect('oc.linkedDistributorsChart', 'linkedDistributorsChart');

    queryBuilder
      .skip(skip)
      .take(+page);
    const [organizationCharts, count] = await queryBuilder.getManyAndCount();

    let namePropertiesLevel = salesForce.salesForceLevelConfig.map(elem => elem.description);
    let headers = [];
    let wch = [];
    namePropertiesLevel.forEach(item => {
      wch = [...wch, { wch: 20 }, { wch: 20 }, { wch: 20 }];
      headers = [item.toUpperCase(), `${item.toUpperCase()} PV`, `${item.toUpperCase()} USERNAME`, `${item.toUpperCase()} EMAIL`, `${item.toUpperCase()} COLOR`, ...headers]
    })
    headers = ['ID', 'SALES FORCE', ...headers]

    return {
      recordset: {
        salesForce, headers, organizationCharts
      },
      count
    };
  }

  async excelReportOrganizationCharts(clientId: any, salesForceId: number, query: OrganizationChartExcelQuery) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', HttpStatus.FAILED_DEPENDENCY);
    }
    const relations = this.buildRelations(salesForce.countLevel).reverse();
    const [organizationCharts, count] = await this.organizationChartRepository.findAndCount({
      where: {
        salesForceId,
        name: ILike(`%${query.name || ''}%`),
        linkedDistributorsChart: {
          distributorId: query.distributorId || undefined,
        },
        level: 1,
      },
      relations,
      order: { id: 'ASC' },
    });

    let namePropertiesLevel = salesForce.salesForceLevelConfig.map(elem => elem.description).reverse();
    let headers = [];

    namePropertiesLevel.forEach(item => {
      headers = [`${item.toUpperCase()}`, `${item.toUpperCase()} PV`, `${item.toUpperCase()} EMAIL`, ...headers]
    })
    headers = ['SALES FORCE', ...headers]

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet(salesForce.id.toString());
    worksheet.addRow(headers);
    worksheet.columns.forEach(function (column, i) {
      column.width = 25;
    });

    organizationCharts.forEach(item => {
      let rowData = [
        salesForce.nameSalesForce,
        item.name,
        item.isSensitive ? 'S' : 'N',
        item.email,
      ];
      let previusOrganizationChart = item.previusOrganizationChart;
      for (let i = 2; i <= salesForce.countLevel; i++) {
        rowData = [...rowData, previusOrganizationChart?.name, previusOrganizationChart?.isSensitive ? 'S' : 'N', previusOrganizationChart?.email];
        previusOrganizationChart = previusOrganizationChart?.previusOrganizationChart;
      }
      worksheet.addRow(rowData);
    });

    // Formatting headers
    worksheet.getRow(1).font = { bold: true };

    // Generate buffer
    const buffer = await workbook.xlsx.writeBuffer();
    return [client.client.name, buffer];
  }

  async getOrganizationChartTree(clientId: any, salesForceId: number) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (!salesForce) {
      throw new HttpException('sales_force_not_found_to_client', HttpStatus.FAILED_DEPENDENCY);
    }
    const organizationCharts = await this.organizationChartRepository.find({ where: { salesForceId }, order: { id: 'ASC' } });
    const tree = this.buildTree(organizationCharts);

    return { salesForce, tree };
  }

  async getOrganizationChartDescendants(salesForceId: number, id) {
    const organizationCharts = await this.organizationChartRepository.find({ where: { salesForceId }, order: { id: 'ASC' } });
    const tree = this.buildTreeDescendents(organizationCharts);
    const descendants = this.getDescendantsIds(id, tree);
    return descendants;
  }

  async createOrganizationChartExcel(salesForceId, clientId, type) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (salesForce.published) {
      throw new HttpException('sales_force_published', HttpStatus.BAD_REQUEST);
    }
    if (!client?.client) {
      throw new HttpException('client_id_not_found', HttpStatus.BAD_REQUEST);
    }
    if (!salesForce) {
      throw new HttpException('sales_force_id_not_found', HttpStatus.BAD_REQUEST);
    }

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet(salesForce.id.toString());

    let namePropertiesLevel = salesForce.salesForceLevelConfig.map(elem => elem.description);
    let headers = [];
    let firstRow = [];
    let secondRow = [];
    let INTERMEDIARIO = '';
    let ID = undefined
    if (type === 'cnpjMtrix') {
      INTERMEDIARIO = 'CNPJ';
      ID =  10000000000000
    }
    else if (type === 'cdMtrix') {
      INTERMEDIARIO = 'CD_INTERMEDIARIO';
      ID =  0
    }
    namePropertiesLevel.forEach(item => {
      headers.push(item.toUpperCase(), `${item.toUpperCase()} PV`, `${item.toUpperCase()} EMAIL`);
      firstRow.push(`${item.toUpperCase()} NC -S`, `S`, `[email protected]`);
      secondRow.push(`${item.toUpperCase()} NC -N`, `N`, ``);
    });
    if (INTERMEDIARIO) {
      headers.push(INTERMEDIARIO);
      firstRow.push(`${ID}`);
      secondRow.push(`${ID+1}`)
    }
    worksheet.addRow(headers);
    worksheet.addRow(firstRow);
    worksheet.addRow(secondRow);
    worksheet.getRow(1).font = { bold: true };
    worksheet.columns.forEach(column => {
      column.width = 25;
    });

    const buffer = await workbook.xlsx.writeBuffer();
    return [client.client.name, buffer]; // Return name and buffer
  }

  validateExcel(data, salesForce, fornecedorParceriaAll, type = undefined) {
    let arrayValue = [];
    let arrayError = [];
    data.forEach((item, numberRow) => {
      let namePropertiesLevel = salesForce.salesForceLevelConfig.map(elem => elem.description.toUpperCase());
      let itemError = [];
      let msgError = [];
      let INTERMEDIARIO = '';
      if (type === 'cnpjMtrix') {
        INTERMEDIARIO = 'CNPJ'
      }
      else if (type === 'cdMtrix') {
        INTERMEDIARIO = 'CD_INTERMEDIARIO'
      }
      else if (!type) {
        INTERMEDIARIO = 'INTERMEDIARIO'
      }

      const distributorParceria = fornecedorParceriaAll.content.find(intermediario =>
        intermediario.cnpjIntermediario === item[INTERMEDIARIO] ||
        intermediario.cdIntermediario === +item[INTERMEDIARIO]);
      namePropertiesLevel.forEach((level, index) => {
        let hash = '';
        namePropertiesLevel.forEach((itemP) => {
          hash = `${hash ? hash + '🤝' : ''}${item[itemP]}`;
        });
        const existItemChart = arrayValue.findIndex(elem => {
          const nodesItem = hash.split('🤝').reverse();;
          nodesItem.splice(namePropertiesLevel.length - (index + 1), nodesItem.length - (namePropertiesLevel.length - (index + 1)));
          const nodesItemjoin = nodesItem.join('🤝');
          const nodesElem = elem.hash.split('🤝').reverse();
          nodesElem.splice(namePropertiesLevel.length - (index + 1), nodesElem.length - (namePropertiesLevel.length - (index + 1)));
          const nodesElemjoin = nodesElem.join('🤝');
          if (elem.NAME_CHART === item[`${level.toUpperCase()}`] &&
            (elem.LEVEL === index + 1) &&
            elem.PREVIUS_NAME_CHART === item[`${namePropertiesLevel[index + 1]}`.toUpperCase()] &&
            nodesItemjoin === nodesElemjoin
          )
            return elem
        })
        if (existItemChart === -1 && !!item[`${level.toUpperCase()}`]) {

          const row = {
            LEVEL: index + 1,
            NAME_CHART: item[`${level}`],
            IS_SENSITIVE: item[`${level} PV`],
            EMAIL: item[`${level} EMAIL`],
            PREVIUS_NAME_CHART: item[`${namePropertiesLevel[index + 1]}`],
            DISTRIBUTORS: index === 0 && item[INTERMEDIARIO] ? [distributorParceria?.cdIntermediario] : [],
            hash,
          };
          arrayValue.push(row);
          if (!row.NAME_CHART) {
            msgError.push('name_chart_required');
            itemError.push(level);
          }
          if (row.IS_SENSITIVE === 'S' && !row.EMAIL) {
            msgError.push('email_is_required_to_lgpd');
            itemError.push(`${level} EMAIL`);
          }
          if (row.IS_SENSITIVE === 'N' && row.EMAIL) {
            msgError.push('email_should_not_be_filled_in');
            itemError.push(`${level} EMAIL`);
          }
          const regexEmail = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
          if (row.EMAIL && !regexEmail.test(row.EMAIL)) {
            msgError.push('email_invalid');
            itemError.push(`${level} EMAIL`);
          }

          if (index === 0 && item[INTERMEDIARIO] && !distributorParceria?.cdIntermediario) {
            msgError.push('distributor_not_found_partnership');
            itemError.push(level);
          }
        }
        else if (index === 0) {
          if (item[INTERMEDIARIO] && !distributorParceria?.cdIntermediario) {
            msgError.push('distributor_not_found');
            itemError.push(level);
          }
          if (!item[`${level.toUpperCase()}`]) {
            msgError.push('name_chart_required');
            itemError.push(level);
          }
          else
            if (item[INTERMEDIARIO])
              arrayValue[existItemChart].DISTRIBUTORS.push(item[INTERMEDIARIO]);
        }
        else if (!item[`${level.toUpperCase()}`]) {
          msgError.push('previus_name_chart_required');
          itemError.push(level);
        }
      })
      if (msgError.length) {
        arrayError.push({
          row: numberRow + 2,
          item: itemError,
          msg: msgError
        })
      }
    })

    return { arrayError, arrayValue }
  }
  async uploadFileType(file: Express.Multer.File,
    clientId: number,
    salesForceId: number,
    type: string,
    token: string
  ) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (salesForce.published) {
      throw new HttpException('sales_force_published', HttpStatus.BAD_REQUEST);
    }
    if (!client?.client) {
      throw new HttpException('client_id_not_found', HttpStatus.BAD_REQUEST);
    }
    if (!salesForce) {
      throw new HttpException('sales_force_id_not_found', HttpStatus.BAD_REQUEST);
    }
    if (!salesForce.canImportExcel) {
      throw new HttpException('sales_force_not_import', HttpStatus.BAD_REQUEST);
    }
    const data: any[] = await excelToJson<any>(file.buffer);
    const arrayLevel = [];
    const arrayDescriptionLevel = [];
    for (let i = salesForce.countLevel; i > 0; i--) {
      arrayLevel.push(i);
    }
    salesForce.salesForceLevelConfig.forEach(itemLevel => {
      arrayDescriptionLevel.push(itemLevel.description)
    })
    const fornecedorParceriaAll = await this.sharedService.getFornecedorParceria(+client.client.mtrixCode, token);

    const { arrayValue, arrayError } = this.validateExcel(data, salesForce, fornecedorParceriaAll, type)
    if (arrayError.length) {
      throw new HttpException(arrayError, 415);
    }
    const queryRunner = this.organizationChartRepository.manager.connection.createQueryRunner();
    try {
      await queryRunner.startTransaction();
      await queryRunner.manager.softDelete(OrganizationChart, { name: 'NC', salesForceId: salesForce.id });
      let organizationChartPrevius: OrganizationChart[] = []
      let linkedDistributorsChartArray: LinkedDistributorsChart[] = []
      for await (let organizationLevel of arrayLevel) {
        const organizationChartsExcel = arrayValue.filter(item => +item.LEVEL === organizationLevel);
        const organizationCharts = organizationChartsExcel.map(item => {
          const previous = organizationChartPrevius.find(elem => {
            const structurePrevius = elem.hash.split('🤝').reverse();
            structurePrevius.splice(structurePrevius.length - (item.LEVEL + 1), structurePrevius.length - (structurePrevius.length - (item.LEVEL + 1)));
            const structurePreviusString = structurePrevius.join('🤝');
            const strutuctOc = item.hash.split('🤝').reverse();
            strutuctOc.splice(strutuctOc.length - (item.LEVEL + 1), strutuctOc.length - (strutuctOc.length - (item.LEVEL + 1)));
            const strutuctOcString = strutuctOc.join('🤝');
            if (
              item.PREVIUS_NAME_CHART === elem.name &&
              elem.level === +item.LEVEL + 1 &&
              structurePreviusString === strutuctOcString)
              return elem;
          })
          const organizationChart = new OrganizationChart();
          organizationChart.name = item.NAME_CHART;
          organizationChart.accessUserCode = item.ACCESS_USER_CODE
          organizationChart.internalCode = item.INTERNAL_CODE;
          organizationChart.level = +item.LEVEL;
          organizationChart.isSensitive = item.IS_SENSITIVE === 'S' ? true : false;
          organizationChart.email = item.EMAIL;
          organizationChart.hash = item.hash;
          if (+item.LEVEL < salesForce.countLevel && !previous?.id) {
            throw new HttpException('invalid_worksheet', HttpStatus.BAD_REQUEST)
          }
          if (organizationChart.isSensitive && !organizationChart.email) {
            throw new HttpException('email_is_required_to_lgpd', HttpStatus.BAD_REQUEST);
          }
          const regexEmail = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
          if (organizationChart.email && !regexEmail.test(organizationChart.email)) {
            throw new HttpException('email_invalid', HttpStatus.BAD_REQUEST)
          }
          organizationChart.previousId = previous?.id;
          organizationChart.userName = item.USER_NAME;
          organizationChart.salesForceId = salesForce.id;
          organizationChart.linkedDistributorsChart = [];
          item.DISTRIBUTORS?.forEach(distID => {
            const linkedDistributorsChart = new LinkedDistributorsChart();
            const distributorParceria = fornecedorParceriaAll.content.find(intermediario =>
              intermediario.cnpjIntermediario === distID ||
              intermediario.cdIntermediario === +distID)
            if (distID) {
              linkedDistributorsChart.distributorId = distributorParceria?.cdIntermediario || distID;
              linkedDistributorsChart.nameDistributor = distributorParceria?.nmIntermediario || 'Não encontrado';
              linkedDistributorsChart.clientId = client.client.id;
              linkedDistributorsChart.level = +item.LEVEL;
              organizationChart.linkedDistributorsChart.push(linkedDistributorsChart);
            }
          })
          return organizationChart;
        })
        organizationChartPrevius = await queryRunner.manager.save(OrganizationChart, organizationCharts);
        if (organizationLevel === 1) {
          organizationCharts.forEach(organizationToLink => {
            organizationToLink.linkedDistributorsChart.forEach(link => {
              link.organizationChartId = organizationToLink.id;
              linkedDistributorsChartArray.push(link);
            })
          })
        }
      }
      if (linkedDistributorsChartArray.length)
        await queryRunner.manager.save(LinkedDistributorsChart, linkedDistributorsChartArray);

      salesForce.canImportExcel = false;
      await queryRunner.manager.save(SalesForce, salesForce);
      await queryRunner.commitTransaction();
    } catch (error) {
      this.logger.error(`uploadFileType.catch, ${clientId}, ${salesForceId}, ${type}`);
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  async uploadFile(file: Express.Multer.File,
    clientId: number,
    salesForceId: number,
    token
  ) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);
    const salesForce = await this.salesForceService.getSalesForceId(salesForceId, +client.client.mtrixCode);
    if (salesForce.published) {
      throw new HttpException('sales_force_published', HttpStatus.BAD_REQUEST);
    }
    if (!client?.client) {
      throw new HttpException('client_id_not_found', HttpStatus.BAD_REQUEST);
    }
    if (!salesForce) {
      throw new HttpException('sales_force_id_not_found', HttpStatus.BAD_REQUEST);
    }
    if (!salesForce.canImportExcel) {
      throw new HttpException('sales_force_not_import', HttpStatus.BAD_REQUEST);
    }
    const data: any[] = await excelToJson<any>(file.buffer);
    const arrayLevel = [];
    const arrayDescriptionLevel = [];
    for (let i = salesForce.countLevel; i > 0; i--) {
      arrayLevel.push(i);
    }
    salesForce.salesForceLevelConfig.forEach(itemLevel => {
      arrayDescriptionLevel.push(itemLevel.description)
    })
    const fornecedorParceriaAll = await this.sharedService.getFornecedorParceria(+client.client.mtrixCode, token);

    const { arrayValue, arrayError } = await this.validateExcel(data, salesForce, fornecedorParceriaAll)
    if (arrayError.length) {
      throw new HttpException(arrayError, 415);
    }
    const queryRunner = this.organizationChartRepository.manager.connection.createQueryRunner();
    try {
      await queryRunner.startTransaction();
      await queryRunner.manager.softDelete(OrganizationChart, { name: 'NC', salesForceId: salesForce.id });
      let organizationChartPrevius: OrganizationChart[] = []
      let linkedDistributorsChartArray: LinkedDistributorsChart[] = []
      for await (let organizationLevel of arrayLevel) {
        const organizationChartsExcel = arrayValue.filter(item => +item.LEVEL === organizationLevel);
        const organizationCharts = organizationChartsExcel.map(item => {
          const previous = organizationChartPrevius.find(elem => {
            const structurePrevius = elem.hash.split('🤝').reverse();
            structurePrevius.splice(structurePrevius.length - (item.LEVEL + 1), structurePrevius.length - (structurePrevius.length - (item.LEVEL + 1)));
            const structurePreviusString = structurePrevius.join('🤝');
            const strutuctOc = item.hash.split('🤝').reverse();
            strutuctOc.splice(strutuctOc.length - (item.LEVEL + 1), strutuctOc.length - (strutuctOc.length - (item.LEVEL + 1)));
            const strutuctOcString = strutuctOc.join('🤝');
            if (
              item.PREVIUS_NAME_CHART === elem.name &&
              elem.level === +item.LEVEL + 1 &&
              structurePreviusString === strutuctOcString)
              return elem;
          })
          const organizationChart = new OrganizationChart();
          organizationChart.name = item.NAME_CHART;
          organizationChart.accessUserCode = item.ACCESS_USER_CODE
          organizationChart.internalCode = item.INTERNAL_CODE;
          organizationChart.level = +item.LEVEL;
          organizationChart.isSensitive = item.IS_SENSITIVE === 'S' ? true : false;
          organizationChart.email = item.EMAIL;
          organizationChart.hash = item.hash;
          if (+item.LEVEL < salesForce.countLevel && !previous?.id) {
            throw new HttpException('invalid_worksheet', HttpStatus.BAD_REQUEST)
          }
          if (organizationChart.isSensitive && !organizationChart.email) {
            throw new HttpException('email_is_required_to_lgpd', HttpStatus.BAD_REQUEST);
          }
          const regexEmail = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
          if (organizationChart.email && !regexEmail.test(organizationChart.email)) {
            throw new HttpException('email_invalid', HttpStatus.BAD_REQUEST)
          }
          organizationChart.previousId = previous?.id;
          organizationChart.userName = item.USER_NAME;
          organizationChart.salesForceId = salesForce.id;
          organizationChart.linkedDistributorsChart = [];
          item.DISTRIBUTORS?.forEach(distID => {
            const linkedDistributorsChart = new LinkedDistributorsChart();
            const distributorParceria = fornecedorParceriaAll.content.find(intermediario =>
              intermediario.cnpjIntermediario === distID ||
              intermediario.cdIntermediario === +distID)
            if (distID) {
              linkedDistributorsChart.distributorId = distributorParceria?.cdIntermediario || distID;
              linkedDistributorsChart.nameDistributor = distributorParceria?.nmIntermediario || 'Não encontrado';
              linkedDistributorsChart.clientId = client.client.id;
              linkedDistributorsChart.level = +item.LEVEL;
              organizationChart.linkedDistributorsChart.push(linkedDistributorsChart);
            }
          })
          return organizationChart;
        })
        organizationChartPrevius = await queryRunner.manager.save(OrganizationChart, organizationCharts);
        if (organizationLevel === 1) {
          organizationCharts.forEach(organizationToLink => {
            organizationToLink.linkedDistributorsChart.forEach(link => {
              link.organizationChartId = organizationToLink.id;
              linkedDistributorsChartArray.push(link);
            })
          })
        }
      }
      if (linkedDistributorsChartArray.length)
        await queryRunner.manager.save(LinkedDistributorsChart, linkedDistributorsChartArray);

      salesForce.canImportExcel = false;
      await queryRunner.manager.save(SalesForce, salesForce);
      await queryRunner.commitTransaction();
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }


  async deleteOrganizationChart(id: number, clientId: number) {
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);

    if (client.client) {
      const crildren = await this.organizationChartRepository.findBy({ previousId: id });
      if (crildren.length > 0) {
        throw new HttpException('unable_to_delete_node_with_bottom_link', 404);
      }

      return await this.organizationChartRepository.softDelete({ id });
    }
    throw new HttpException('client_id_not_found', 404);
  }

  async getOrganizationChartId(id: number, clientId: number) {
    // return  await this.salesForceRepository.findOneBy({ id, supplierId });
    const client = await this.clientService.getClientIdSalesForceLevel(clientId);

    if (client?.client) {
      return await this.organizationChartRepository.findOneBy({ id });
    }
    throw new HttpException('client_id_not_found', 404);
  }

  buildTree(data: OrganizationChart[]) {
    const idToNodeMap = new Map();
    for (const item of data) {
      const { id, previousId } = item;
      let node = new TreeNode();
      node = { ...item, children: [] }
      idToNodeMap.set(id, node);

      if (previousId !== null && idToNodeMap.has(previousId)) {
        idToNodeMap.get(previousId).children.push(node);
      }
    }
    const roots = [];
    for (const node of idToNodeMap.values()) {
      if (!node.previousId || !idToNodeMap.has(node.previousId)) {
        roots.push(node);
      }
    }
    return roots;
  }

  buildTreeDescendents(data: OrganizationChart[]) {
    const idToNodeMap = new Map();
    for (const item of data) {
      const { id, previousId } = item;
      let node = new TreeNode();
      node = { ...item, children: [] }
      idToNodeMap.set(id, node);

      if (previousId !== null && idToNodeMap.has(previousId)) {
        idToNodeMap.get(previousId).children.push(node);
      }
    }
    return idToNodeMap;
  }
  getDescendantsIds(nodeId, roots) {
    const descendants = [];
    const dfs = (node) => {
      descendants.push(node.id);
      for (const child of node.children) {
        dfs(child);
      }
    };
    const startNode = roots.get(nodeId);
    if (startNode) {
      dfs(startNode);
    }
    return descendants;
  }

}