import { Inject, Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, In, Repository } from 'typeorm';
import {
  getSkipOffset,
  paginate,
} from 'src/config/pagination/pagination.service';
import { SearchCoursePortalDto } from './dto/search-dto';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
import { CourseMasterEntity } from 'src/entities/CourseMaster.entity';
import { CoursesTransformer } from './transformers/courses.transform';
import { CourseMasterIntakeYearsEntity } from 'src/entities/CourseMasterIntakeYears.entity';
import { StudyLevelMasterEntity } from 'src/entities/StudyLevelMaster.entity';
import { StreamMasterEntity } from 'src/entities/StreamMaster.entity';
import { LanguageProficiencyScoringEntity } from 'src/entities/LanguageProficiencyScoring.entity';
import { AcademyProficiencyScoringEntity } from 'src/entities/AcademyProficiencyScoring.entity';
import { OptionsTransformer } from './transformers/options.transform';
import { StateEntity } from 'src/entities/State.entity';
import { CitySearchDto } from './dto/city-search-dto';
import { CityEntity } from 'src/entities/City.entity';
import { SubstreamMasterEntity } from 'src/entities/SubstreamMaster.entity';

/**
 * Course Portal Service
 *
 * Main service for handling all business logic related to the course portal.
 * This service is responsible for:
 * - Searching and filtering courses
 * - Managing filter options (countries, study levels, streams, etc.)
 * - Handling hierarchical data (countries → states → cities, streams → substreams)
 * - Caching frequently accessed data
 * - Transforming raw database data to API-friendly formats
 *
 * @Injectable - Marks this class as a provider that can be injected via NestJS DI
 */
@Injectable()
export class CoursePortalService {
  /**
   * Constructor with dependency injection
   * @param dataSource - TypeORM DataSource for complex queries
   * @param cacheManager - Cache manager for caching frequently accessed data
   * @param courseTransformer - Transforms raw course data to DTO format
   * @param optionTransformer - Transforms data to standardized option format
   * @param intakeYearRepo - Repository for course intake years
   * @param courseMasterRepo - Repository for course master data
   * @param slMasterRepo - Repository for study level master data
   * @param streamMasterRepo - Repository for stream master data
   * @param langProfScoringRepo - Repository for language proficiency scoring
   * @param acaProfScoringRepo - Repository for academic proficiency scoring
   * @param stateRepo - Repository for state data
   * @param cityRepo - Repository for city data
   * @param subStreamMasterRepo - Repository for substream master data
   */
  constructor(
    private dataSource: DataSource,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
    private readonly courseTransformer: CoursesTransformer,
    private readonly optionTransformer: OptionsTransformer,
    @InjectRepository(CourseMasterIntakeYearsEntity)
    private readonly intakeYearRepo: Repository<CourseMasterIntakeYearsEntity>,
    @InjectRepository(CourseMasterEntity)
    private readonly courseMasterRepo: Repository<CourseMasterEntity>,
    @InjectRepository(StudyLevelMasterEntity)
    private readonly slMasterRepo: Repository<StudyLevelMasterEntity>,
    @InjectRepository(StreamMasterEntity)
    private readonly streamMasterRepo: Repository<StreamMasterEntity>,
    @InjectRepository(LanguageProficiencyScoringEntity)
    private readonly langProfScoringRepo: Repository<LanguageProficiencyScoringEntity>,
    @InjectRepository(AcademyProficiencyScoringEntity)
    private readonly acaProfScoringRepo: Repository<AcademyProficiencyScoringEntity>,
    @InjectRepository(StateEntity)
    private readonly stateRepo: Repository<StateEntity>,
    @InjectRepository(CityEntity)
    private readonly cityRepo: Repository<CityEntity>,
    @InjectRepository(SubstreamMasterEntity)
    private readonly subStreamMasterRepo: Repository<SubstreamMasterEntity>,
  ) {}

  /**
   * Search courses with filtering and pagination
   * Performs a complex query with multiple joins to fetch comprehensive course data
   *
   * @param searchDto - Search criteria and pagination parameters
   * @returns Paginated list of courses with enriched data
   *
   * @complexity High - Multiple joins and additional async calls for each course
   * @performance Consider adding caching for study levels, streams, etc.
   */
  async getSearchCourses(searchDto: SearchCoursePortalDto): Promise<any> {
    // Build the main query with all required joins
    const qb = this.courseMasterRepo
      .createQueryBuilder('cm')
      // Join college information
      .innerJoin(
        'college',
        'cl',
        'cl.collegeId = cm.institueId AND cl.active = 1',
      )
      // Join campus information
      .innerJoin(
        'college_campus',
        'cc',
        'cc.instituteid = cm.institueId AND cc.campStatus = 1',
      )
      // Join country information
      .innerJoin(
        'country',
        'cnty',
        'cnty.country_id = cc.country_id AND cnty.active = 1',
      )
      // Join state information
      .innerJoin('state', 'st', 'st.state_id = cc.state_id AND st.active = 1')
      // Join city information
      .innerJoin('city', 'ct', 'ct.city_id = cc.city_id AND ct.active = 1')
      // Join course review status
      .innerJoin(
        'course_database_review',
        'cdb',
        'cdb.courseId = cm.course_id AND cdb.campusId = cc.id',
      )
      // Join intake years
      .innerJoin(
        'course_master_intake_years',
        'inty',
        'inty.course_master_id = cm.course_id AND cc.id = inty.campusId',
      )
      // Join language proficiency scores
      .innerJoin(
        'coursemaster_scores',
        'cms',
        'cms.course_id = cm.course_id AND inty.campusId = cms.campusId',
      )
      // Join academic proficiency scores
      .innerJoin(
        'coursemaster_academy_scores',
        'cas',
        'cas.course_id = cm.course_id AND cms.campusId = cas.campusId',
      );

    // Select specific fields from all joined tables
    qb.select([
      'cm.UID AS courseid',
      'cm.website AS coursewebsiteurl',
      'cl.website AS institutewebsiteurl',
      'cl.UID AS cuid',
      'cc.id AS campusId',
      'cc.city_id',
      'cc.state_id',
      'cc.country_id',
      'cc.campName',
      'cc.campusAssociationTypeId',
      'cnty.name AS country_name',
      'ct.city_name',
      'st.state_name',
      'cdb.reviewStatus',
      'cm.institueId',
      'cm.currency',
      'cm.course_name',
      'cm.application_fee',
      'cm.tution_fee_inter',
      'cm.tution_fee_per',
      'cm.duration_type',
      'cm.duration',
      'cm.max_duration',
      'cl.institueName',
      'cl.instituteNotes',
      'cl.instituteType',
      'cm.course_id',
      'cl.collegeId',
    ]);

    // Apply base filters
    qb.where('cm.active = :active', { active: '1' })
      .groupBy('cm.course_id, cc.id')
      .orderBy('cm.course_id', 'DESC');

    // Apply pagination
    const [skip, limit] = getSkipOffset(searchDto);
    qb.limit(limit).offset(skip);

    // Execute query and count in parallel for better performance
    const [data, total] = await Promise.all([qb.getRawMany(), qb.getCount()]);

    // Enrich each course with additional data (study levels, streams, etc.)
    const finalData = await Promise.all(
      data.map(async (course) => {
        const studyLevels = await this.getCourseStudyData(course.cm_course_id);
        course.study_levels = studyLevels;

        const streams = await this.getCourseStreamData(course.cm_course_id);
        course.streams = streams;

        const subStreams = await this.getCourseSubStreamData(
          course.cm_course_id,
        );
        course.sub_streams = subStreams;

        const intakes = await this.getIntakeDetails(
          course.cm_course_id,
          course.campusId,
        );
        course.intakes = intakes;

        return course;
      }),
    );

    // Calculate pagination metadata
    const pagination = paginate(total, searchDto.per_page, searchDto.page);

    // Transform raw data to DTO format and return with pagination
    return { list: this.courseTransformer.transform(finalData), pagination };
  }

  /**
   * Get study levels for a specific course
   * @private
   */
  private async getCourseStudyData(courseId: number): Promise<string> {
    const studyLevels = await this.courseMasterRepo
      .createQueryBuilder('cm')
      .select('slm.study_level', 'study_level')
      .innerJoin(
        'course_master_studylevelinfo',
        'cmsl',
        'cmsl.course_id = cm.course_id',
      )
      .innerJoin('study_level_master', 'slm', 'slm.id = cmsl.studylevelId')
      .where('cmsl.course_id = :courseId', { courseId })
      .groupBy('cmsl.studylevelId, slm.study_level')
      .orderBy('slm.id', 'ASC')
      .getRawMany();

    return studyLevels.map((row) => row.study_level).join(' | ');
  }

  /**
   * Get streams for a specific course
   * @private
   */
  private async getCourseStreamData(courseId: number): Promise<string> {
    const streamData = await this.courseMasterRepo
      .createQueryBuilder('cm')
      .select('sm.stream_name', 'stream_name')
      .innerJoin(
        'course_master_streaminfo',
        'cmss',
        'cmss.course_id = cm.course_id',
      )
      .innerJoin('stream_master', 'sm', 'sm.id = cmss.streamId')
      .where('cmss.course_id = :courseId', { courseId })
      .groupBy('cmss.streamId')
      .orderBy('sm.id', 'ASC')
      .getRawMany();

    return streamData.map((item) => item.stream_name).join(' | ');
  }

  /**
   * Get substreams for a specific course
   * @private
   */
  private async getCourseSubStreamData(courseId: number): Promise<string> {
    const subStreamData = await this.courseMasterRepo
      .createQueryBuilder('cm')
      .select('ssm.substream_name', 'substream_name')
      .innerJoin(
        'course_master_substreaminfo',
        'cmss',
        'cmss.course_id = cm.course_id',
      )
      .innerJoin('substream_master', 'ssm', 'ssm.id = cmss.substreamId')
      .where('cmss.course_id = :courseId', { courseId })
      .groupBy('cmss.substreamId')
      .orderBy('ssm.id', 'ASC')
      .getRawMany();

    return subStreamData.map((item) => item.substream_name).join(' | ');
  }

  /**
   * Get intake details for a course at a specific campus
   * @private
   */
  private async getIntakeDetails(
    courseId: number,
    campusId: number,
  ): Promise<string> {
    const intakes = await this.intakeYearRepo
      .createQueryBuilder('ink')
      .select(['ink.intakemonth', 'ink.intakeyear', 'cc.campName'])
      .innerJoin('college_campus', 'cc', 'cc.id = ink.campusId')
      .where('ink.course_master_id = :courseId', { courseId })
      .andWhere('ink.campusId = :campusId', { campusId })
      .orderBy('ink.intakYId', 'ASC')
      .getRawMany();

    return intakes
      .map((intake) => `${intake.ink_intakemonth} ${intake.ink_intakeyear}`)
      .join(', ');
  }

  /**
   * Get all filter options for the course portal
   * Combines data from multiple sources into a single response
   */
  async getFilters() {
    // Fetch all filter data in parallel for better performance
    const [
      campusCountries,
      studyLevels,
      streams,
      testModules,
      academyCriteriaList,
    ] = await Promise.all([
      this.getCampusCountries(),
      this.getAllCoursesProgramActive(),
      this.getAllCoursesStreamActive(),
      this.getAllActiveLanguageProficiencies(),
      this.getAllActiveAcademyProficiencies(),
    ]);

    // Transform each data set to the standard option format
    return {
      campus_countries: this.optionTransformer.transform(campusCountries),
      study_levels: this.optionTransformer.transform(studyLevels),
      streams: this.optionTransformer.transform(streams),
      test_modules: this.optionTransformer.transform(testModules),
      academy_criteria_list:
        this.optionTransformer.transform(academyCriteriaList),
    };
  }

  /**
   * Get countries that have active campuses with available courses
   * Filters to only include countries with future intake dates
   */
  async getCampusCountries() {
    const currentTimestamp = Math.floor(Date.now() / 1000);

    return await this.courseMasterRepo
      .createQueryBuilder('cm')
      .select('cnty.country_id', 'id')
      .addSelect('cnty.name', 'name')
      .innerJoin('college', 'cl', 'cl.collegeId = cm.institueId')
      .innerJoin('college_campus', 'cc', 'cc.instituteid = cm.institueId')
      .innerJoin('country', 'cnty', 'cnty.country_id = cc.country_id')
      .innerJoin('state', 'st', 'st.state_id = cc.state_id')
      .innerJoin('city', 'ct', 'ct.city_id = cc.city_id')
      .innerJoin(
        'course_database_review',
        'cdb',
        'cdb.courseId = cm.course_id AND cdb.campusId = cc.id',
      )
      .innerJoin(
        'course_master_intake_years',
        'inty',
        'inty.course_master_id = cm.course_id AND cc.id = inty.campusId',
      )
      .innerJoin(
        'coursemaster_scores',
        'cms',
        'cms.course_id = cm.course_id AND inty.campusId = cms.campusId',
      )
      .innerJoin(
        'coursemaster_academy_scores',
        'cas',
        'cas.course_id = cm.course_id AND cms.campusId = cas.campusId',
      )
      // Complex condition: only include countries with future intake dates
      .where(
        `UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(inty.intakemonth," ",DAYOFMONTH(LAST_DAY(CONCAT(inty.intakeyear,"-",MONTH(STR_TO_DATE(inty.intakemonth, "%M")),"-1")))," ",inty.intakeyear),"%M %d %Y")) >= :currentTimestamp`,
        { currentTimestamp },
      )
      .andWhere('cm.active = :cmActive', { cmActive: 1 })
      .andWhere('cl.active = :clActive', { clActive: 1 })
      .andWhere('cnty.active = :cntyActive', { cntyActive: 1 })
      .groupBy('cc.country_id')
      .addGroupBy('cnty.country_id')
      .addGroupBy('cnty.name')
      .orderBy('cnty.name', 'ASC')
      .getRawMany();
  }

  /**
   * Get all active study levels (programs)
   */
  async getAllCoursesProgramActive() {
    const query = this.slMasterRepo
      .createQueryBuilder('slm')
      .select('slm.id', 'id')
      .addSelect('slm.study_level', 'name')
      .where('slm.active = :active', { active: '1' })
      .andWhere('slm.domestic IS NULL OR slm.domestic = 0')
      .orderBy('slm.id', 'DESC');

    return await query.getRawMany();
  }

  /**
   * Get all active streams with optional pagination
   */
  async getAllCoursesStreamActive(params?: {
    limit?: number;
    offset?: number;
  }): Promise<StreamMasterEntity[]> {
    const query = this.streamMasterRepo
      .createQueryBuilder('st')
      .select('st.id', 'id')
      .addSelect('st.stream_name', 'name')
      .where('st.active = :active', { active: 1 })
      .orderBy('st.id', 'DESC');

    // Apply pagination if provided
    if (params?.limit !== undefined && params?.offset !== undefined) {
      query.limit(params.limit).offset(params.offset);
    }

    return await query.getRawMany();
  }

  /**
   * Get all active language proficiency scoring options
   */
  async getAllActiveLanguageProficiencies(): Promise<
    { proficiencyScoringId: number }[]
  > {
    return await this.langProfScoringRepo
      .createQueryBuilder('lps')
      .select('lps.proficiencyScoringId', 'id')
      .addSelect('lps.proficiencyScoringId', 'name')
      .where('lps.active = :active', { active: 1 })
      .orderBy('lps.proficiencyScoringId', 'ASC')
      .getRawMany();
  }

  /**
   * Get all active academic proficiency scoring options
   */
  async getAllActiveAcademyProficiencies(): Promise<
    { acproficiencyScoringId: number }[]
  > {
    return await this.acaProfScoringRepo
      .createQueryBuilder('aps')
      .select('aps.acproficiencyScoringId', 'id')
      .addSelect('aps.acproficiencyScoringId', 'name')
      .where('aps.active = :active', { active: 1 })
      .orderBy('aps.acproficiencyScoringId', 'ASC')
      .getRawMany();
  }

  /**
   * Get states for the given country IDs
   */
  async getStatesByCountryIds(countryIds: number[]) {
    const states = await this.stateRepo
      .createQueryBuilder('state')
      .select(['state.state_id AS id', 'state.state_name AS name'])
      .where('state.country_id IN (:...countryIds)', { countryIds })
      .andWhere('state.active = :active', { active: 1 })
      .orderBy('state.state_name', 'DESC')
      .getRawMany();

    return this.optionTransformer.transform(states);
  }

  /**
   * Get cities filtered by countries and optionally by states
   * Note: There's a bug in the query - uses country_id for state filtering
   */
  async getCitiesByStatesAndCountries(params: CitySearchDto) {
    const cities = await this.cityRepo
      .createQueryBuilder('city')
      .select(['city.city_id AS id', 'city.city_name AS name'])
      .where('city.country_id IN (:...countryIds)', {
        countryIds: params?.countries,
      })
      // BUG: This should be 'city.state_id IN (...)' not 'city.country_id IN (...)'
      .andWhere('city.country_id IN (:...stateIds)', {
        stateIds: params?.states,
      })
      .andWhere('city.active = :active', { active: 1 })
      .orderBy('city.city_name', 'DESC')
      .getRawMany();

    return this.optionTransformer.transform(cities);
  }

  /**
   * Get substreams for the given stream IDs
   */
  async getSubStreamsByStreams(streams: number[]) {
    const substreams = await this.subStreamMasterRepo
      .createQueryBuilder('stream')
      .select(['stream.id AS id', 'stream.substream_name AS name'])
      .where('stream.stream_id IN (:...streams)', { streams })
      .andWhere('stream.active = :active', { active: 1 })
      .orderBy('stream.substream_name', 'DESC')
      .getRawMany();

    return this.optionTransformer.transform(substreams);
  }

  /**
   * Performance Optimizations Needed:
   * 1. The getSearchCourses method makes N+1 queries for each course
   *    Solution: Use JOINs or batch queries
   *
   * 2. Implement caching for filter data (rarely changes)
   *    const cachedFilters = await this.cacheManager.get('course-filters');
   *    if (cachedFilters) return cachedFilters;
   *    // ... fetch data
   *    await this.cacheManager.set('course-filters', result, 3600000); // 1 hour
   *
   * 3. Consider adding indexes on frequently queried columns
   *
   * 4. The getCampusCountries query is very complex - consider simplifying
   */
}
