import { SystemLogService } from "@api/shared/services";
import { buildListResponseData } from "@bish-nest/core";
import { BNestBaseModuleService } from "@bish-nest/core/data/module-service/base-module.service";
import { PaginationOptions } from "@bish-nest/core/data/pagination/pagination-options.interface";
import { DetailResponseDataInterface } from "@bish-nest/core/interfaces/detail-response-data.interface";
import { ListResponseDataInterface } from "@bish-nest/core/interfaces/list-response-data.interface";
import { SaveResponseDataInterface } from "@bish-nest/core/interfaces/save-response-data.interface";
import { BNestPrismaService } from "@bish-nest/core/services";
import { BadRequestException, Injectable, NotFoundException } from "@nestjs/common";
import { MediaName, Prisma, SystemLogEntityType } from "@prisma/client";
import { plainToInstance } from "class-transformer";

@Injectable()
export class CourseService extends BNestBaseModuleService {
  constructor(
    protected prisma: BNestPrismaService,
    private readonly systemLogService: SystemLogService,
  ) {
    super();
  }

  /**
   * Check if course has enrollments
   */
  private async checkCourseEnrollments(courseId: number): Promise<void> {
    const enrollmentCount = await this.prisma.client.learningGroupParticipant.count({
      where: {
        course_id: courseId,
      },
    });

    if (enrollmentCount > 0) {
      throw new BadRequestException(
        `Cannot modify course. This course has ${enrollmentCount} enrolled participant(s). Courses with enrollments cannot be modified.`,
      );
    }
  }

  /**
   * Validate publish requirements: BAT Assessment and Knowledge Review are mandatory for publishing
   * This ensures courses cannot be published without these essential components
   */
  private async validatePublishRequirements(
    currentCourse: { assessment_id: number | null; knowledge_review_id: number | null; is_published: boolean },
    newData: any,
  ): Promise<void> {
    // Helper function to extract ID from relation object or value
    const extractId = (value: any): number | null => {
      if (value === null || value === undefined) {
        return null;
      }
      if (typeof value === "object" && value.id !== undefined) {
        return value.id;
      }
      return typeof value === "number" ? value : null;
    };

    // Only validate if we're actually trying to publish the course (changing from false to true)
    // If is_published is not in newData, we're not changing publish status, so skip validation
    if (newData.is_published === undefined) {
      return;
    }

    // Only validate if we're transitioning from unpublished to published
    // If already published and staying published, or unpublishing, no validation needed
    const isCurrentlyPublished = currentCourse.is_published;
    const willBePublished = newData.is_published === true;

    // Only validate when transitioning from unpublished to published
    if (isCurrentlyPublished || !willBePublished) {
      return;
    }
    // At this point: !isCurrentlyPublished && willBePublished (publishing)

    // Determine final assessment_id (from new data or existing)
    const newAssessmentId = extractId(newData.assessment_id) ?? extractId(newData.assessment);
    const finalAssessmentId = newAssessmentId !== null ? newAssessmentId : currentCourse.assessment_id;

    // Determine final knowledge_review_id (from new data or existing)
    const newKnowledgeReviewId = extractId(newData.knowledge_review_id) ?? extractId(newData.knowledgeReview);
    const finalKnowledgeReviewId =
      newKnowledgeReviewId !== null ? newKnowledgeReviewId : currentCourse.knowledge_review_id;

    // Collect missing requirements
    const missingRequirements: string[] = [];
    if (!finalAssessmentId) {
      missingRequirements.push("BAT Assessment");
    }
    if (!finalKnowledgeReviewId) {
      missingRequirements.push("Knowledge Review");
    }

    // If any requirements are missing, block publishing
    if (missingRequirements.length > 0) {
      throw new BadRequestException(
        `Cannot publish course. The following are required: ${missingRequirements.join(" and ")}. Please add these before publishing the course.`,
      );
    }
  }

  /**
   * Override getList to calculate accurate counts for all courses
   */
  async getList(paginationOptions: PaginationOptions): Promise<ListResponseDataInterface<any>> {
    const { page, limit } = paginationOptions;

    let listOpts: PaginationOptions = { ...paginationOptions };
    const extraAnd: Record<string, unknown>[] = [...(listOpts.additionalWhereAnd ?? [])];

    if (listOpts.where && typeof listOpts.where === "object" && !Array.isArray(listOpts.where)) {
      const w = { ...(listOpts.where as Record<string, { operator: string; value: string }>) };
      let touched = false;
      if (w["course_module_count"]) {
        const spec = w["course_module_count"];
        delete w["course_module_count"];
        touched = true;
        const ids = await this.courseIdsMatchingComputedModuleCount(spec.operator, String(spec.value ?? ""));
        extraAnd.push({ id: { in: ids } });
      }
      if (w["enrollment_count"]) {
        const spec = w["enrollment_count"];
        delete w["enrollment_count"];
        touched = true;
        const ids = await this.courseIdsMatchingComputedEnrollmentCount(spec.operator, String(spec.value ?? ""));
        extraAnd.push({ id: { in: ids } });
      }
      if (touched) {
        listOpts = {
          ...listOpts,
          where: Object.keys(w).length > 0 ? (w as PaginationOptions["where"]) : undefined,
          additionalWhereAnd: extraAnd,
        };
      }
    }

    const rawKw = listOpts.keyword;
    const keywordStr =
      rawKw != null && String(rawKw).trim() !== ""
        ? String(Array.isArray(rawKw) ? (rawKw as unknown[])[0] : rawKw).trim()
        : "";

    const tokens = keywordStr.split(/\s+/).filter(Boolean);
    if (tokens.length === 1 && /^\d+$/.test(tokens[0]!)) {
      const n = Number.parseInt(tokens[0]!, 10);
      if (!Number.isNaN(n) && n >= 0 && n <= 2147483647) {
        const rows = await this.prisma.client.$queryRaw<{ course_id: number }[]>`
          SELECT c.id AS course_id
          FROM course c
          LEFT JOIN course_module_page cmp ON cmp.course_id = c.id
          GROUP BY c.id
          HAVING COUNT(DISTINCT cmp.course_module_id) = ${n}
        `;
        const ids = rows.map((r) => r.course_id);

        const standardKeywordCond = this.dataUtil.getKeywordSearchCondition(keywordStr);
        const moduleCountCond = ids.length > 0 ? { id: { in: ids } } : { id: { in: [] as number[] } };
        const hasStandard =
          standardKeywordCond &&
          typeof standardKeywordCond === "object" &&
          Object.keys(standardKeywordCond as object).length > 0;
        const combined = hasStandard ? { OR: [standardKeywordCond, moduleCountCond] } : moduleCountCond;

        listOpts = {
          ...listOpts,
          keyword: undefined,
          keywordConditionOverride: combined,
        };
      }
    }

    // Get raw data from base method
    const [rawData, totalCount] = await this.moduleMethods.getListRows(listOpts);

    // Extract course IDs from the raw data
    const courseIds = rawData.map((course: any) => course.id).filter((id: any) => id != null);

    // Calculate counts for all courses in parallel using batch queries
    const courseModuleCountsMap = new Map<number, number>();
    const enrollmentCountsMap = new Map<number, number>();

    if (courseIds.length > 0) {
      // Get course module counts for all courses
      // Since CourseModule no longer has course_id, count distinct modules through CourseModulePage
      const courseModulePages = await this.prisma.client.courseModulePage.findMany({
        where: {
          course_id: { in: courseIds },
        },
        select: {
          course_id: true,
          course_module_id: true,
        },
        distinct: ["course_id", "course_module_id"],
      });

      // Count distinct course_module_id per course_id
      courseModulePages.forEach((page) => {
        const currentCount = courseModuleCountsMap.get(page.course_id) || 0;
        courseModuleCountsMap.set(page.course_id, currentCount + 1);
      });

      // Get enrollment counts for all courses
      const enrollmentCounts = await this.prisma.client.learningGroupParticipant.groupBy({
        by: ["course_id"],
        where: {
          course_id: { in: courseIds },
        },
        _count: {
          id: true,
        },
      });

      enrollmentCounts.forEach((item) => {
        enrollmentCountsMap.set(item.course_id, item._count.id);
      });
    }

    // Update each course with accurate counts
    const updatedData = rawData.map((course: any) => ({
      ...course,
      course_module_count: courseModuleCountsMap.get(course.id) ?? 0,
      enrollment_count: enrollmentCountsMap.get(course.id) ?? 0,
    }));

    const moduleCurrentCfg = this.gVars.moduleCurrentCfg;

    // Transform using DTO
    let data: any[] = updatedData;
    if (paginationOptions.vl) {
      if (moduleCurrentCfg.simpleDto) {
        data = updatedData.map((row: any) =>
          plainToInstance(moduleCurrentCfg.simpleDto, row, { excludeExtraneousValues: true }),
        );
      }
    } else {
      if (moduleCurrentCfg.listDto) {
        data = updatedData.map((row: any) =>
          plainToInstance(moduleCurrentCfg.listDto, row, { excludeExtraneousValues: true }),
        );
      }
    }

    return buildListResponseData(data, page || 1, limit || 10, totalCount);
  }

  /**
   * Detail must use live LearningGroupParticipant count for enrollment_count / has_enrollments.
   * List already merges this; raw Course.enrollment_count is not kept in sync when people enroll.
   */
  async getDetailData(id: number): Promise<any> {
    const moduleCurrentCfg = this.gVars.moduleCurrentCfg;
    const data = await this.moduleMethods.getDetailData(id);

    // Course only has a Prisma relation for `mediaImages`; other media buckets are loaded from `Media` table.
    for (const mediaObj of moduleCurrentCfg.mediaArr) {
      const relationName = mediaObj.mediaRelationName;
      if (relationName === "mediaImages") {
        continue;
      }

      data[relationName] = await this.prisma.client.media.findMany({
        where: {
          course_id: id,
          media_name: mediaObj.name as MediaName,
        },
      });

      await this.mediaUtilService.addS3UrlPrefixToMediaArray(data[relationName]);
    }

    return data;
  }

  async getDetail(id: number): Promise<DetailResponseDataInterface<any>> {
    const moduleCurrentCfg = this.gVars.moduleCurrentCfg;
    let data = await this.getDetailData(id);

    const enrollmentCount = await this.prisma.client.learningGroupParticipant.count({
      where: { course_id: id },
    });

    data = {
      ...data,
      enrollment_count: enrollmentCount,
    };

    const transformed = plainToInstance(moduleCurrentCfg.detailDto, data);
    return this.moduleMethods.getReturnDataForDetail(transformed);
  }

  /**
   * Override add method to prevent creation when course has enrollments
   * Note: This protects against edge cases where a course might be created with existing enrollments
   */
  async add(data: any) {
    // If course_id is provided and it's an existing course, check enrollments
    // This prevents creating duplicate records for courses with enrollments
    const courseId = data.id || data.course_id;
    if (courseId && typeof courseId === "number") {
      await this.checkCourseEnrollments(courseId);
    }

    // Validate publish requirements for new course (using empty defaults for current values)
    await this.validatePublishRequirements(
      { assessment_id: null, knowledge_review_id: null, is_published: false },
      data,
    );

    // Proceed with normal add
    const addResponse = await super.add(data);
    const course = addResponse.data;

    // Log the creation
    await this.systemLogService.logInsert(
      SystemLogEntityType.COURSE,
      course.id,
      course as Record<string, unknown>,
    );

    return addResponse;
  }

  /**
   * Override save method to prevent modifications to assessment_id and knowledge_review_id when course has enrollments
   * All other fields can be updated freely
   */
  async save(id: number, data: any): Promise<SaveResponseDataInterface<any>> {
    // Check if course has enrollments
    const enrollmentCount = await this.prisma.client.learningGroupParticipant.count({
      where: {
        course_id: id,
      },
    });

    if (enrollmentCount > 0) {
      // Get the course data using DTO method
      const courseData = await this.moduleMethods.getDetailData(id);

      // Transform using DTO to get current values
      const moduleCurrentCfg = this.gVars.moduleCurrentCfg;
      const courseDto = plainToInstance(moduleCurrentCfg.detailDto, courseData);

      // Helper function to extract ID from relation object or value
      const extractId = (value: any): any => {
        if (value === null || value === undefined) {
          return null;
        }
        // Handle relation objects (assessment, knowledgeReview, Media, etc.)
        if (typeof value === "object" && value.id !== undefined) {
          return value.id;
        }
        return value;
      };

      // Get current values from DTO
      const currentAssessmentId = extractId((courseDto as any).assessment_id);
      const currentKnowledgeReviewId = extractId((courseDto as any).knowledge_review_id);

      // New values from payload only; if not present, treat as "not changed" and keep current
      const hasAssessmentInPayload = data.assessment_id !== undefined || data.assessment !== undefined;
      const hasKnowledgeReviewInPayload =
        data.knowledge_review_id !== undefined || data.knowledgeReview !== undefined;

      const newAssessmentId = hasAssessmentInPayload
        ? (extractId(data.assessment_id) ?? extractId(data.assessment))
        : currentAssessmentId;
      const newKnowledgeReviewId = hasKnowledgeReviewInPayload
        ? (extractId(data.knowledge_review_id) ?? extractId(data.knowledgeReview))
        : currentKnowledgeReviewId;

      // Only validate when payload actually sends these fields and value differs
      const isAssessmentChanging = currentAssessmentId !== newAssessmentId;
      const isKnowledgeReviewChanging = currentKnowledgeReviewId !== newKnowledgeReviewId;

      if (isAssessmentChanging || isKnowledgeReviewChanging) {
        // Check if trying to add Assessment or Knowledge Review
        const isAddingAssessment =
          isAssessmentChanging && newAssessmentId !== null && currentAssessmentId === null;
        const isAddingKnowledgeReview =
          isKnowledgeReviewChanging && newKnowledgeReviewId !== null && currentKnowledgeReviewId === null;

        let errorMessage: string;
        if (isAddingAssessment || isAddingKnowledgeReview) {
          const items = [];
          if (isAddingAssessment) items.push("Assessment");
          if (isAddingKnowledgeReview) items.push("Knowledge Review");
          errorMessage = `Cannot modify ${items.join(" or ")}. This course has ${enrollmentCount} enrolled participant(s). Assessment and Knowledge Review cannot be changed for courses with enrollments.`;
        } else {
          errorMessage = `Cannot modify Assessment or Knowledge Review. This course has ${enrollmentCount} enrolled participant(s). Assessment and Knowledge Review cannot be changed for courses with enrollments.`;
        }

        throw new BadRequestException(errorMessage);
      }
      // When not in payload, CourseSaveDto omits assessment/knowledgeReview (undefined), so Prisma leaves current values unchanged.
    }

    // Get old data before update
    const oldCourse = await this.prisma.client.course.findUnique({
      where: { id },
    });

    if (!oldCourse) {
      throw new NotFoundException(`Course with ID ${id} not found`);
    }

    // Validate publish requirements: BAT Assessment and Knowledge Review are mandatory
    await this.validatePublishRequirements(oldCourse, data);

    // Proceed with normal save
    const saveResponse = await super.save(id, data);
    const updatedCourse = saveResponse.data;

    // Calculate changed fields and log the update
    const changedFields = SystemLogService.calculateChangedFields(
      oldCourse as Record<string, unknown>,
      updatedCourse as Record<string, unknown>,
    );

    await this.systemLogService.logUpdate(
      SystemLogEntityType.COURSE,
      id,
      oldCourse as Record<string, unknown>,
      updatedCourse as Record<string, unknown>,
      changedFields,
    );

    return saveResponse;
  }

  /**
   * Override delete method with cascade delete for all related records
   * This allows deletion of courses that have no enrollments but have related data
   */
  async delete(id: number): Promise<void> {
    // Get course data before deletion
    const course = await this.prisma.client.course.findUnique({
      where: { id },
    });

    if (!course) {
      throw new NotFoundException(`Course with ID ${id} not found`);
    }

    // Check for enrollments first (this validation is also in CourseConfig, but we check here for safety)
    const enrollmentCount = await this.prisma.client.learningGroupParticipant.count({
      where: { course_id: id },
    });

    if (enrollmentCount > 0) {
      throw new BadRequestException(
        `Cannot delete course. This course has ${enrollmentCount} enrolled participant(s). Courses with enrollments cannot be deleted.`,
      );
    }

    // Log the deletion BEFORE deleting (so the course still exists for the log reference)
    await this.systemLogService.logDelete(SystemLogEntityType.COURSE, id, course as Record<string, unknown>);

    // Cascade delete all related records in the correct order (children first)
    await this.cascadeDeleteCourseRelatedRecords(id);

    // Now delete the course itself
    await this.prisma.client.course.delete({
      where: { id },
    });
  }

  /**
   * Cascade delete all records related to a course
   * Order matters: delete children before parents to respect foreign key constraints
   */
  private async cascadeDeleteCourseRelatedRecords(courseId: number): Promise<void> {
    // 1. Delete deepest level records first (assessment results and knowledge review results)
    await this.prisma.client.assessmentResult.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.knowledgeReviewResult.deleteMany({
      where: { course_id: courseId },
    });

    // 2. Delete assessment-related records
    await this.prisma.client.assessmentAnswer.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.assessmentQuestion.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.assessmentParticipant.deleteMany({
      where: { course_id: courseId },
    });

    // 3. Delete knowledge review-related records
    await this.prisma.client.knowledgeReviewAnswer.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.knowledgeReviewQuestion.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.knowledgeReviewParticipant.deleteMany({
      where: { course_id: courseId },
    });

    // 4. Delete assessments and knowledge reviews linked to this course
    await this.prisma.client.assessment.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.knowledgeReview.deleteMany({
      where: { course_id: courseId },
    });

    // 5. Delete course structure (pages before modules)
    await this.prisma.client.courseModulePage.deleteMany({
      where: { course_id: courseId },
    });

    // CourseModule no longer has course_id - find modules through CourseModulePage
    const courseModuleIds = (
      await this.prisma.client.courseModulePage.findMany({
        where: { course_id: courseId },
        select: { course_module_id: true },
        distinct: ["course_module_id"],
      })
    ).map((p) => p.course_module_id);

    if (courseModuleIds.length > 0) {
      await this.prisma.client.courseModule.deleteMany({
        where: { id: { in: courseModuleIds } },
      });
    }

    // 6. Delete other course-related records
    await this.prisma.client.testimonial.deleteMany({
      where: { course_id: courseId },
    });

    await this.prisma.client.learningGroup.deleteMany({
      where: { course_id: courseId },
    });

    // 7. Delete media files linked to this course
    await this.prisma.client.media.deleteMany({
      where: { course_id: courseId },
    });

    // 8. Clear log references (set course_id to null) instead of deleting
    // Audit logs should be preserved for historical purposes
    await this.prisma.client.systemLog.updateMany({
      where: { course_id: courseId },
      data: { course_id: null },
    });

    await this.prisma.client.reportLog.updateMany({
      where: { course_id: courseId },
      data: { course_id: null },
    });

    await this.prisma.client.emailLog.updateMany({
      where: { course_id: courseId },
      data: { course_id: null },
    });

    // 9. Clear the course's assessment and knowledge_review references before deletion
    // (to avoid foreign key issues with the linked assessment/knowledge_review)
    await this.prisma.client.course.update({
      where: { id: courseId },
      data: {
        assessment_id: null,
        knowledge_review_id: null,
      },
    });
  }

  private parseAdvancedSearchInt(raw: string): number {
    return Number.parseInt(
      String(raw ?? "")
        .trim()
        .replace(/\u00a0|\u202f/g, " ")
        .replace(/%/g, "")
        .trim(),
      10,
    );
  }

  /**
   * Advanced search on list "Modules" count — same definition as post-fetch map (distinct course_module_id via pages).
   */
  private async courseIdsMatchingComputedModuleCount(operator: string, rawValue: string): Promise<number[]> {
    const v = String(rawValue ?? "").trim();
    const n = this.parseAdvancedSearchInt(v);

    switch (operator) {
      case "equals":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) = ${n}`,
        );
      case "not":
        if (!Number.isNaN(n)) {
          return this.queryCourseIds(
            Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) <> ${n}`,
          );
        }
        if (!v) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING (COUNT(DISTINCT cmp.course_module_id))::text <> ${v}`,
        );
      case "gt":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) > ${n}`,
        );
      case "gte":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) >= ${n}`,
        );
      case "lt":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) < ${n}`,
        );
      case "lte":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) <= ${n}`,
        );
      case "between": {
        const parts = v.split("...");
        const a = this.parseAdvancedSearchInt(parts[0] ?? "");
        const b = this.parseAdvancedSearchInt(parts[1] ?? "");
        if (Number.isNaN(a) || Number.isNaN(b)) {
          return [];
        }
        const lo = Math.min(a, b);
        const hi = Math.max(a, b);
        return this.queryCourseIds(
          Prisma.sql`HAVING COUNT(DISTINCT cmp.course_module_id) >= ${lo} AND COUNT(DISTINCT cmp.course_module_id) <= ${hi}`,
        );
      }
      case "contains":
        if (!v) {
          return [];
        }
        return this.queryCourseIds(
          Prisma.sql`HAVING POSITION(${v} IN ((COUNT(DISTINCT cmp.course_module_id))::text)) > 0`,
        );
      case "startsWith": {
        if (!v) {
          return [];
        }
        const esc = v.replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
        const pat = `${esc}%`;
        return this.queryCourseIds(
          Prisma.sql`HAVING (COUNT(DISTINCT cmp.course_module_id))::text LIKE ${pat} ESCAPE '\\'`,
        );
      }
      case "endsWith": {
        if (!v) {
          return [];
        }
        const esc = v.replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
        const pat = `%${esc}`;
        return this.queryCourseIds(
          Prisma.sql`HAVING (COUNT(DISTINCT cmp.course_module_id))::text LIKE ${pat} ESCAPE '\\'`,
        );
      }
      default:
        return [];
    }
  }

  /**
   * Advanced search on list "Enrollments" count — rows in learning_group_participant per course (0 when none).
   */
  private async courseIdsMatchingComputedEnrollmentCount(operator: string, rawValue: string): Promise<number[]> {
    const v = String(rawValue ?? "").trim();
    const n = this.parseAdvancedSearchInt(v);

    switch (operator) {
      case "equals":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) = ${n}`,
        );
      case "not":
        if (!Number.isNaN(n)) {
          return this.queryCourseIdsByEnrollment(
            Prisma.sql`HAVING COUNT(lgp.id) <> ${n}`,
          );
        }
        if (!v) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING (COUNT(lgp.id))::text <> ${v}`,
        );
      case "gt":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) > ${n}`,
        );
      case "gte":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) >= ${n}`,
        );
      case "lt":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) < ${n}`,
        );
      case "lte":
        if (Number.isNaN(n)) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) <= ${n}`,
        );
      case "between": {
        const parts = v.split("...");
        const a = this.parseAdvancedSearchInt(parts[0] ?? "");
        const b = this.parseAdvancedSearchInt(parts[1] ?? "");
        if (Number.isNaN(a) || Number.isNaN(b)) {
          return [];
        }
        const lo = Math.min(a, b);
        const hi = Math.max(a, b);
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING COUNT(lgp.id) >= ${lo} AND COUNT(lgp.id) <= ${hi}`,
        );
      }
      case "contains":
        if (!v) {
          return [];
        }
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING POSITION(${v} IN ((COUNT(lgp.id))::text)) > 0`,
        );
      case "startsWith": {
        if (!v) {
          return [];
        }
        const esc = v.replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
        const pat = `${esc}%`;
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING (COUNT(lgp.id))::text LIKE ${pat} ESCAPE '\\'`,
        );
      }
      case "endsWith": {
        if (!v) {
          return [];
        }
        const esc = v.replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
        const pat = `%${esc}`;
        return this.queryCourseIdsByEnrollment(
          Prisma.sql`HAVING (COUNT(lgp.id))::text LIKE ${pat} ESCAPE '\\'`,
        );
      }
      default:
        return [];
    }
  }

  private async queryCourseIds(having: Prisma.Sql): Promise<number[]> {
    const rows = await this.prisma.client.$queryRaw<{ course_id: number }[]>(Prisma.sql`
      SELECT c.id AS course_id
      FROM course c
      LEFT JOIN course_module_page cmp ON cmp.course_id = c.id
      GROUP BY c.id
      ${having}
    `);
    return rows.map((r) => r.course_id);
  }

  private async queryCourseIdsByEnrollment(having: Prisma.Sql): Promise<number[]> {
    const rows = await this.prisma.client.$queryRaw<{ course_id: number }[]>(Prisma.sql`
      SELECT c.id AS course_id
      FROM course c
      LEFT JOIN learning_group_participant lgp ON lgp.course_id = c.id
      GROUP BY c.id
      ${having}
    `);
    return rows.map((r) => r.course_id);
  }
}