  /**
   * API รายละเอียดโครงสร้าง
   *
   * @summary ORG_023 - รายละเอียดโครงสร้าง (ADMIN) #25
   *
   */
  @Get("super-admin/{id}")
  async detailSuperAdmin(@Path() id: string, @Request() request: RequestWithUser) {
    const orgRevision = await this.orgRevisionRepository.findOne({
      where: { id: id },
    });
    if (!orgRevision) return new HttpSuccess([]);

    let rootId: any = null;
    if (!request.user.role.includes("SUPER_ADMIN")) {
      const profile = await this.profileRepo.findOne({
        where: {
          keycloak: request.user.sub,
        },
        select: ["id"],
      });
      if (profile == null) return new HttpSuccess([]);

      const posMaster = await this.posMasterRepository.findOne({
        where:
          orgRevision.orgRevisionIsCurrent && !orgRevision.orgRevisionIsDraft
            ? {
                orgRevisionId: id,
                current_holderId: profile.id,
              }
            : {
                orgRevisionId: id,
                next_holderId: profile.id,
              },
      });
      if (!posMaster) return new HttpSuccess([]);

      rootId = posMaster.orgRootId;
    }

    // OPTIMIZED: Get all position counts in ONE query (closed)
    // const { orgRootMap, orgChild1Map, orgChild2Map, orgChild3Map, orgChild4Map, rootPosMap } =
    //   await getPositionCounts(id);

    // OPTIMIZED: Fetch orgRoot first, then fetch all child levels in parallel
    const orgRootData = await AppDataSource.getRepository(OrgRoot)
      .createQueryBuilder("orgRoot")
      .where("orgRoot.orgRevisionId = :id", { id })
      .andWhere(rootId != null ? `orgRoot.id = :rootId` : "1=1", {
        rootId: rootId,
      })
      .orderBy("orgRoot.orgRootOrder", "ASC")
      .getMany();

    const orgRootIds = orgRootData.map((orgRoot) => orgRoot.id);

    // OPTIMIZED: Fetch all child levels in parallel using orgRevisionId
    // This is faster than sequential queries that depend on parent IDs
    const [orgChild1AllData, orgChild2AllData, orgChild3AllData, orgChild4AllData] = await Promise.all([
      AppDataSource.getRepository(OrgChild1)
        .createQueryBuilder("orgChild1")
        .where("orgChild1.orgRevisionId = :id", { id })
        .orderBy("orgChild1.orgChild1Order", "ASC")
        .getMany(),

      AppDataSource.getRepository(OrgChild2)
        .createQueryBuilder("orgChild2")
        .where("orgChild2.orgRevisionId = :id", { id })
        .orderBy("orgChild2.orgChild2Order", "ASC")
        .getMany(),

      AppDataSource.getRepository(OrgChild3)
        .createQueryBuilder("orgChild3")
        .where("orgChild3.orgRevisionId = :id", { id })
        .orderBy("orgChild3.orgChild3Order", "ASC")
        .getMany(),

      AppDataSource.getRepository(OrgChild4)
        .createQueryBuilder("orgChild4")
        .where("orgChild4.orgRevisionId = :id", { id })
        .orderBy("orgChild4.orgChild4Order", "ASC")
        .getMany(),
    ]);

    // Filter child1 data by orgRootIds (maintains backward compatibility)
    const orgChild1Data = orgRootIds && orgRootIds.length > 0
      ? orgChild1AllData.filter((orgChild1) => orgRootIds.includes(orgChild1.orgRootId))
      : [];

    // Build maps for efficient filtering of deeper levels
    const orgChild1Ids = orgChild1Data.map((orgChild1) => orgChild1.id);
    const orgChild2Data = orgChild1Ids && orgChild1Ids.length > 0
      ? orgChild2AllData.filter((orgChild2) => orgChild1Ids.includes(orgChild2.orgChild1Id))
      : [];

    const orgChild2Ids = orgChild2Data.map((orgChild2) => orgChild2.id);
    const orgChild3Data = orgChild2Ids && orgChild2Ids.length > 0
      ? orgChild3AllData.filter((orgChild3) => orgChild2Ids.includes(orgChild3.orgChild2Id))
      : [];

    const orgChild3Ids = orgChild3Data.map((orgChild3) => orgChild3.id);
    const orgChild4Data = orgChild3Ids && orgChild3Ids.length > 0
      ? orgChild4AllData.filter((orgChild4) => orgChild3Ids.includes(orgChild4.orgChild3Id))
      : [];

    // OPTIMIZED: Build formatted data using pre-calculated counts (no nested queries!)
    const formattedData = orgRootData.map((orgRoot) => {
      // const rootCounts = getCounts(orgRootMap, orgRoot.id);
      // const rootPosCounts = getRootCounts(rootPosMap, orgRoot.id);

      return {
        orgTreeId: orgRoot.id,
        orgLevel: 0,
        orgName: orgRoot.orgRootName,
        orgTreeName: orgRoot.orgRootName,
        orgTreeShortName: orgRoot.orgRootShortName,
        orgTreeCode: orgRoot.orgRootCode,
        orgCode: orgRoot.orgRootCode + "00",
        orgTreeRank: orgRoot.orgRootRank,
        orgTreeRankSub: orgRoot.orgRootRankSub,
        orgRootDnaId: orgRoot.ancestorDNA,
        DEPARTMENT_CODE: orgRoot.DEPARTMENT_CODE,
        DIVISION_CODE: orgRoot.DIVISION_CODE,
        SECTION_CODE: orgRoot.SECTION_CODE,
        JOB_CODE: orgRoot.JOB_CODE,
        orgTreeOrder: orgRoot.orgRootOrder,
        orgTreePhoneEx: orgRoot.orgRootPhoneEx,
        orgTreePhoneIn: orgRoot.orgRootPhoneIn,
        orgTreeFax: orgRoot.orgRootFax,
        orgRevisionId: orgRoot.orgRevisionId,
        orgRootName: orgRoot.orgRootName,
        isDeputy: orgRoot.isDeputy,
        isCommission: orgRoot.isCommission,
        responsibility: orgRoot.responsibility,
        labelName:
          orgRoot.orgRootName + " " + orgRoot.orgRootCode + "00" + " " + orgRoot.orgRootShortName,
        // totalPosition: rootCounts.totalPosition,
        // totalPositionCurrentUse: rootCounts.totalPositionCurrentUse,
        // totalPositionCurrentVacant: rootCounts.totalPositionCurrentVacant,
        // totalPositionNextUse: rootCounts.totalPositionNextUse,
        // totalPositionNextVacant: rootCounts.totalPositionNextVacant,
        // totalRootPosition: rootPosCounts.totalRootPosition,
        // totalRootPositionCurrentUse: rootPosCounts.totalRootPositionCurrentUse,
        // totalRootPositionCurrentVacant: rootPosCounts.totalRootPositionCurrentVacant,
        // totalRootPositionNextUse: rootPosCounts.totalRootPositionNextUse,
        // totalRootPositionNextVacant: rootPosCounts.totalRootPositionNextVacant,
        children: orgChild1Data
          .filter((orgChild1) => orgChild1.orgRootId === orgRoot.id)
          .map((orgChild1) => {
            // const child1Counts = getCounts(orgChild1Map, orgChild1.id);
            // const child1PosKey = `${orgRoot.id}-${orgChild1.id}`;
            // const child1PosCounts = getRootCounts(rootPosMap, child1PosKey);

            return {
              orgTreeId: orgChild1.id,
              orgRootId: orgRoot.id,
              orgLevel: 1,
              orgName: `${orgChild1.orgChild1Name}/${orgRoot.orgRootName}`,
              orgTreeName: orgChild1.orgChild1Name,
              orgTreeShortName: orgChild1.orgChild1ShortName,
              orgTreeCode: orgChild1.orgChild1Code,
              orgCode: orgRoot.orgRootCode + orgChild1.orgChild1Code,
              orgTreeRank: orgChild1.orgChild1Rank,
              orgTreeRankSub: orgChild1.orgChild1RankSub,
              orgRootDnaId: orgRoot.ancestorDNA,
              orgChild1DnaId: orgChild1.ancestorDNA,
              DEPARTMENT_CODE: orgChild1.DEPARTMENT_CODE,
              DIVISION_CODE: orgChild1.DIVISION_CODE,
              SECTION_CODE: orgChild1.SECTION_CODE,
              JOB_CODE: orgChild1.JOB_CODE,
              orgTreeOrder: orgChild1.orgChild1Order,
              orgRootCode: orgRoot.orgRootCode,
              orgTreePhoneEx: orgChild1.orgChild1PhoneEx,
              orgTreePhoneIn: orgChild1.orgChild1PhoneIn,
              orgTreeFax: orgChild1.orgChild1Fax,
              orgRevisionId: orgRoot.orgRevisionId,
              orgRootName: orgRoot.orgRootName,
              responsibility: orgChild1.responsibility,
              isOfficer: orgChild1.isOfficer,
              isInformation: orgChild1.isInformation,
              labelName:
                orgChild1.orgChild1Name +
                " " +
                orgRoot.orgRootCode +
                orgChild1.orgChild1Code +
                " " +
                orgChild1.orgChild1ShortName,
              // totalPosition: child1Counts.totalPosition,
              // totalPositionCurrentUse: child1Counts.totalPositionCurrentUse,
              // totalPositionCurrentVacant: child1Counts.totalPositionCurrentVacant,
              // totalPositionNextUse: child1Counts.totalPositionNextUse,
              // totalPositionNextVacant: child1Counts.totalPositionNextVacant,
              // totalRootPosition: child1PosCounts.totalRootPosition,
              // totalRootPositionCurrentUse: child1PosCounts.totalRootPositionCurrentUse,
              // totalRootPositionCurrentVacant: child1PosCounts.totalRootPositionCurrentVacant,
              // totalRootPositionNextUse: child1PosCounts.totalRootPositionNextUse,
              // totalRootPositionNextVacant: child1PosCounts.totalRootPositionNextVacant,
              children: orgChild2Data
                .filter((orgChild2) => orgChild2.orgChild1Id === orgChild1.id)
                .map((orgChild2) => {
                  // const child2Counts = getCounts(orgChild2Map, orgChild2.id);
                  // const child2PosKey = `${orgRoot.id}-${orgChild1.id}-${orgChild2.id}`;
                  // const child2PosCounts = getRootCounts(rootPosMap, child2PosKey);

                  return {
                    orgTreeId: orgChild2.id,
                    orgRootId: orgChild1.id,
                    orgLevel: 2,
                    orgName: `${orgChild2.orgChild2Name}/${orgChild1.orgChild1Name}/${orgRoot.orgRootName}`,
                    orgTreeName: orgChild2.orgChild2Name,
                    orgTreeShortName: orgChild2.orgChild2ShortName,
                    orgTreeCode: orgChild2.orgChild2Code,
                    orgCode: orgRoot.orgRootCode + orgChild2.orgChild2Code,
                    orgTreeRank: orgChild2.orgChild2Rank,
                    orgTreeRankSub: orgChild2.orgChild2RankSub,
                    orgRootDnaId: orgRoot.ancestorDNA,
                    orgChild1DnaId: orgChild1.ancestorDNA,
                    orgChild2DnaId: orgChild2.ancestorDNA,
                    DEPARTMENT_CODE: orgChild2.DEPARTMENT_CODE,
                    DIVISION_CODE: orgChild2.DIVISION_CODE,
                    SECTION_CODE: orgChild2.SECTION_CODE,
                    JOB_CODE: orgChild2.JOB_CODE,
                    orgTreeOrder: orgChild2.orgChild2Order,
                    orgRootCode: orgRoot.orgRootCode,
                    orgTreePhoneEx: orgChild2.orgChild2PhoneEx,
                    orgTreePhoneIn: orgChild2.orgChild2PhoneIn,
                    orgTreeFax: orgChild2.orgChild2Fax,
                    orgRevisionId: orgRoot.orgRevisionId,
                    orgRootName: orgRoot.orgRootName,
                    responsibility: orgChild2.responsibility,
                    labelName:
                      orgChild2.orgChild2Name +
                      " " +
                      orgRoot.orgRootCode +
                      orgChild2.orgChild2Code +
                      " " +
                      orgChild2.orgChild2ShortName,
                    // totalPosition: child2Counts.totalPosition,
                    // totalPositionCurrentUse: child2Counts.totalPositionCurrentUse,
                    // totalPositionCurrentVacant: child2Counts.totalPositionCurrentVacant,
                    // totalPositionNextUse: child2Counts.totalPositionNextUse,
                    // totalPositionNextVacant: child2Counts.totalPositionNextVacant,
                    // totalRootPosition: child2PosCounts.totalRootPosition,
                    // totalRootPositionCurrentUse: child2PosCounts.totalRootPositionCurrentUse,
                    // totalRootPositionCurrentVacant: child2PosCounts.totalRootPositionCurrentVacant,
                    // totalRootPositionNextUse: child2PosCounts.totalRootPositionNextUse,
                    // totalRootPositionNextVacant: child2PosCounts.totalRootPositionNextVacant,
                    children: orgChild3Data
                      .filter((orgChild3) => orgChild3.orgChild2Id === orgChild2.id)
                      .map((orgChild3) => {
                        // const child3Counts = getCounts(orgChild3Map, orgChild3.id);
                        // const child3PosKey = `${orgRoot.id}-${orgChild1.id}-${orgChild2.id}-${orgChild3.id}`;
                        // const child3PosCounts = getRootCounts(rootPosMap, child3PosKey);

                        return {
                          orgTreeId: orgChild3.id,
                          orgRootId: orgChild2.id,
                          orgLevel: 3,
                          orgName: `${orgChild3.orgChild3Name}/${orgChild2.orgChild2Name}/${orgChild1.orgChild1Name}/${orgRoot.orgRootName}`,
                          orgTreeName: orgChild3.orgChild3Name,
                          orgTreeShortName: orgChild3.orgChild3ShortName,
                          orgTreeCode: orgChild3.orgChild3Code,
                          orgCode: orgRoot.orgRootCode + orgChild3.orgChild3Code,
                          orgTreeRank: orgChild3.orgChild3Rank,
                          orgTreeRankSub: orgChild3.orgChild3RankSub,
                          orgRootDnaId: orgRoot.ancestorDNA,
                          orgChild1DnaId: orgChild1.ancestorDNA,
                          orgChild2DnaId: orgChild2.ancestorDNA,
                          orgChild3DnaId: orgChild3.ancestorDNA,
                          DEPARTMENT_CODE: orgChild3.DEPARTMENT_CODE,
                          DIVISION_CODE: orgChild3.DIVISION_CODE,
                          SECTION_CODE: orgChild3.SECTION_CODE,
                          JOB_CODE: orgChild3.JOB_CODE,
                          orgTreeOrder: orgChild3.orgChild3Order,
                          orgRootCode: orgRoot.orgRootCode,
                          orgTreePhoneEx: orgChild3.orgChild3PhoneEx,
                          orgTreePhoneIn: orgChild3.orgChild3PhoneIn,
                          orgTreeFax: orgChild3.orgChild3Fax,
                          orgRevisionId: orgRoot.orgRevisionId,
                          orgRootName: orgRoot.orgRootName,
                          responsibility: orgChild3.responsibility,
                          labelName:
                            orgChild3.orgChild3Name +
                            " " +
                            orgRoot.orgRootCode +
                            orgChild3.orgChild3Code +
                            " " +
                            orgChild3.orgChild3ShortName,
                          // totalPosition: child3Counts.totalPosition,
                          // totalPositionCurrentUse: child3Counts.totalPositionCurrentUse,
                          // totalPositionCurrentVacant: child3Counts.totalPositionCurrentVacant,
                          // totalPositionNextUse: child3Counts.totalPositionNextUse,
                          // totalPositionNextVacant: child3Counts.totalPositionNextVacant,
                          // totalRootPosition: child3PosCounts.totalRootPosition,
                          // totalRootPositionCurrentUse: child3PosCounts.totalRootPositionCurrentUse,
                          // totalRootPositionCurrentVacant:
                          //   child3PosCounts.totalRootPositionCurrentVacant,
                          // totalRootPositionNextUse: child3PosCounts.totalRootPositionNextUse,
                          // totalRootPositionNextVacant: child3PosCounts.totalRootPositionNextVacant,
                          children: orgChild4Data
                            .filter((orgChild4) => orgChild4.orgChild3Id === orgChild3.id)
                            .map((orgChild4) => {
                              // const child4Counts = getCounts(orgChild4Map, orgChild4.id);
                              // const child4PosKey = `${orgRoot.id}-${orgChild1.id}-${orgChild2.id}-${orgChild3.id}-${orgChild4.id}`;
                              // const child4PosCounts = getRootCounts(rootPosMap, child4PosKey);

                              return {
                                orgTreeId: orgChild4.id,
                                orgRootId: orgChild3.id,
                                orgLevel: 4,
                                orgName: `${orgChild4.orgChild4Name}/${orgChild3.orgChild3Name}/${orgChild2.orgChild2Name}/${orgChild1.orgChild1Name}/${orgRoot.orgRootName}`,
                                orgTreeName: orgChild4.orgChild4Name,
                                orgTreeShortName: orgChild4.orgChild4ShortName,
                                orgTreeCode: orgChild4.orgChild4Code,
                                orgCode: orgRoot.orgRootCode + orgChild4.orgChild4Code,
                                orgTreeRank: orgChild4.orgChild4Rank,
                                orgTreeRankSub: orgChild4.orgChild4RankSub,
                                orgRootDnaId: orgRoot.ancestorDNA,
                                orgChild1DnaId: orgChild1.ancestorDNA,
                                orgChild2DnaId: orgChild2.ancestorDNA,
                                orgChild3DnaId: orgChild3.ancestorDNA,
                                orgChild4DnaId: orgChild4.ancestorDNA,
                                DEPARTMENT_CODE: orgChild4.DEPARTMENT_CODE,
                                DIVISION_CODE: orgChild4.DIVISION_CODE,
                                SECTION_CODE: orgChild4.SECTION_CODE,
                                JOB_CODE: orgChild4.JOB_CODE,
                                orgTreeOrder: orgChild4.orgChild4Order,
                                orgRootCode: orgRoot.orgRootCode,
                                orgTreePhoneEx: orgChild4.orgChild4PhoneEx,
                                orgTreePhoneIn: orgChild4.orgChild4PhoneIn,
                                orgTreeFax: orgChild4.orgChild4Fax,
                                orgRevisionId: orgRoot.orgRevisionId,
                                orgRootName: orgRoot.orgRootName,
                                responsibility: orgChild4.responsibility,
                                labelName:
                                  orgChild4.orgChild4Name +
                                  " " +
                                  orgRoot.orgRootCode +
                                  orgChild4.orgChild4Code +
                                  " " +
                                  orgChild4.orgChild4ShortName,
                                // totalPosition: child4Counts.totalPosition,
                                // totalPositionCurrentUse: child4Counts.totalPositionCurrentUse,
                                // totalPositionCurrentVacant: child4Counts.totalPositionCurrentVacant,
                                // totalPositionNextUse: child4Counts.totalPositionNextUse,
                                // totalPositionNextVacant: child4Counts.totalPositionNextVacant,
                                // totalRootPosition: child4PosCounts.totalRootPosition,
                                // totalRootPositionCurrentUse:
                                //   child4PosCounts.totalRootPositionCurrentUse,
                                // totalRootPositionCurrentVacant:
                                //   child4PosCounts.totalRootPositionCurrentVacant,
                                // totalRootPositionNextUse: child4PosCounts.totalRootPositionNextUse,
                                // totalRootPositionNextVacant:
                                //   child4PosCounts.totalRootPositionNextVacant,
                                children: [],
                              };
                            }),
                        };
                      }),
                  };
                }),
            };
          }),
      };
    });

    return new HttpSuccess(formattedData);
  }
