import { Injectable } from '@nestjs/common';
import { STATUS_INACTIVE } from 'src/constant/global.constant';
import { ReflectionHelper } from '../../../utils/service/reflection-helper.service';

@Injectable()
export class SchoolRepository {
  constructor(private readonly reflectionHelper: ReflectionHelper) {}

  async findAllByOrgId(orgId: number): Promise<any[]> {
    const schoolRepo = this.reflectionHelper.getRepoService('SSOSchool');
    return await schoolRepo.find({
      where: {
        organization_id: orgId,
      },
    });
  }

  async findAllByBrandId(brdId: number): Promise<any[]> {
    const schoolRepo = this.reflectionHelper.getRepoService('SSOSchool');
    return await schoolRepo.find({
      where: {
        brand_id: brdId,
      },
    });
  }

  async getUserContextDropdown(userId: number, appCode: string, org_id) {
    const userRoleRepo =
      this.reflectionHelper.getRepoService('UserRoleMapping');

    const hasOrgAccess = await userRoleRepo
      .createQueryBuilder('urm')
      .where('urm.user_id::text = :userId', { userId: String(userId) })
      .andWhere('urm.appcode = :appCode', { appCode })
      .andWhere('urm.level_type = :levelType', { levelType: 'ORG' })
      .getRawMany();

    let currentORGLevel_id = hasOrgAccess[0]?.urm_level_id;
    if (hasOrgAccess[0]?.urm_level_id == 1) {
      currentORGLevel_id = org_id;
    }

    const listMasterItemsRepo =
      this.reflectionHelper.getRepoService('ListMasterItems');
    const resolvedInactiveStatus = await listMasterItemsRepo.findOne({
      where: {
        code: STATUS_INACTIVE,
        organization_id: org_id,
      },
    });

    if (hasOrgAccess.length > 0) {
      const organizationRepo =
        this.reflectionHelper.getRepoService('OrganizationData');

      const schools = await organizationRepo
        .createQueryBuilder('org')
        .select([
          'org.id AS org_id',
          'org.name AS org_name',
          'org.address AS org_address',
          'org.status AS org_status',

          'brn.id AS brand_id',
          'brn.name AS brand_name',
          'brn.status AS brand_status',

          'school.id AS school_id',
          'school.name AS school_name',
          'school.location AS school_location',
          'school.status AS school_status',
        ])
        // JOIN brand (on the same table)
        .innerJoin(
          'sso_organization',
          'brn',
          "brn.type = 'BRN' AND brn.parent_id::varchar = org.id::varchar",
        )
        // JOIN school table
        .innerJoin(
          'sso_school',
          'school',
          'school.brand_id::varchar = brn.id::varchar',
        )
        .where('org.id = :orgId', { orgId: String(currentORGLevel_id) })
        .distinct(true)
        .getRawMany();

      if (schools.length > 0) {
        const result = {};
        for (const row of schools) {
          const {
            org_id,
            org_name,
            org_address,
            org_status,
            brand_id,
            brand_name,
            brand_status,
            school_id,
            school_name,
            school_location,
            school_status,
          } = row;

          const orgStatus = await listMasterItemsRepo.findOne({
            where: {
              id: org_status,
              organization_id: org_id,
            },
          });

          if (!result[org_id]) {
            result[org_id] = {
              org_id,
              org_name,
              org_address,
              status: orgStatus ? orgStatus.name : org_status,
              type: 'Organization',
              brands: {},
            };
          }

          const brandStatus = await listMasterItemsRepo.findOne({
            where: {
              id: brand_status,
              organization_id: org_id,
            },
          });

          if (!result[org_id].brands[brand_id]) {
            result[org_id].brands[brand_id] = {
              brand_id,
              brand_name,
              status: brandStatus ? brandStatus.name : brand_status,
              type: 'Brand',
              schools: [],
            };
          }

          const schoolStatus = await listMasterItemsRepo.findOne({
            where: {
              id: school_status,
              organization_id: org_id,
            },
          });

          const finalSchoolName =
            schoolStatus?.id === resolvedInactiveStatus?.id
              ? `${school_name} [INACTIVE]`
              : school_name;

          result[org_id].brands[brand_id].schools.push({
            school_id,
            school_name: finalSchoolName,
            school_location,
            status: schoolStatus ? schoolStatus.name : school_status,
            type: 'School',
          });
        }
        return Object.values(result).map((org: any) => ({
          ...org,
          brands: Object.values(org.brands),
        }));
      } else {
        const orgResult = await organizationRepo.findOne({
          where: {
            id: currentORGLevel_id,
          },
        });

        return [
          {
            org_id: currentORGLevel_id,
            org_name: orgResult.name,
            org_address: orgResult.address,
            status: orgResult.status,
            type: orgResult.type || 'Organization',
            brands: [],
          },
        ];
      }
    } else {
      const userRoleMappingRepo =
        this.reflectionHelper.getRepoService('UserRoleMapping');

      const brnMappings = await userRoleMappingRepo
        .createQueryBuilder('urm')
        .select([
          'brand.id AS brand_id',
          'brand.name AS brand_name',
          'brand.status AS brand_status',

          'school.id AS school_id',
          'school.name AS school_name',
          'school.location AS school_location',
          'school.status AS school_status',
        ])
        .innerJoin(
          'sso_organization',
          'brand',
          'brand.id::text = urm.level_id::text',
        )
        .innerJoin(
          'sso_school',
          'school',
          'school.brand_id::text = brand.id::text',
        )
        .where('urm.user_id::text = :userId', { userId: String(userId) })
        .andWhere('urm.appcode = :appCode', { appCode })
        .andWhere('urm.level_type = :levelType', { levelType: 'BRN' })
        .distinct(true)
        .getRawMany();

      const schMappings = await userRoleMappingRepo
        .createQueryBuilder('urm')
        .select([
          'brand.id AS brand_id',
          'brand.name AS brand_name',
          'brand.status AS brand_status',

          'school.id AS school_id',
          'school.name AS school_name',
          'school.location AS school_location',
          'school.status AS school_status',
        ])
        .innerJoin(
          'sso_school',
          'school',
          'school.id::text = urm.level_id::text',
        )
        .innerJoin(
          'sso_organization',
          'brand',
          'brand.id::text = school.brand_id::text',
        )
        .where('urm.user_id::text = :userId', { userId: String(userId) })
        .andWhere('urm.appcode = :appCode', { appCode })
        .andWhere('urm.level_type = :levelType', { levelType: 'SCH' })
        .distinct(true)
        .getRawMany();

      const allMappings = [...brnMappings, ...schMappings];

      const result = {};
      for (const row of allMappings) {
        const {
          brand_id,
          brand_name,
          brand_status,
          school_id,
          school_name,
          school_location,
          school_status,
        } = row;

        if (!result[brand_id]) {
          result[brand_id] = {
            brand_id,
            brand_name,
            status: brand_status,
            type: 'Brand',
            schools: [],
          };
        }

        const finalSchoolName =
          school_status === resolvedInactiveStatus?.id
            ? `${school_name} [INACTIVE]`
            : school_name;

        if (
          school_id &&
          !result[brand_id].schools.some((s) => s.school_id === school_id)
        ) {
          result[brand_id].schools.push({
            school_id,
            school_name: finalSchoolName,
            school_location,
            status: school_status,
            type: 'School',
          });
        }
      }

      return Object.values(result);
    }
  }
}