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 { moduleNameToAuditEntityType } from "@bish-nest/core/data/module-service/module-audit.util";
import { BNestAppRequestContext } from "@bish-nest/core/request-context/app-request-context";
import { RequestContext } from "@medibloc/nestjs-request-context";
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 { BNestDtoPlainTransformOptions } from "@bish-nest/core/dtos/plain-to-dto";
import { plainToInstance } from "class-transformer";

/**
 * CourseService
 *
 * Admin API — Course (Course). Business Logic Service for RecallAssess.
 */


@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: {
      course_code: string;
      title: string;
      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;

    const finalCourseCode = String(newData.course_code ?? currentCourse.course_code ?? "").trim();
    const finalTitle = String(newData.title ?? currentCourse.title ?? "").trim();

    // Collect missing requirements
    const missingRequirements: string[] = [];
    if (!finalCourseCode) {
      missingRequirements.push("Course Code");
    }
    if (!finalTitle) {
      missingRequirements.push("Title");
    }
    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.`,
      );
    }
  }

  /**
   * Persist first-publish time in DB: set `published_at` on first publish, clear on unpublish,
   * and ignore client-supplied `published_at` except for those transitions.
   */
  private applyPublishedAtForPayload(oldCourse: { is_published: boolean }, data: Record<string, unknown>): void {
    if (!Object.prototype.hasOwnProperty.call(data, "is_published")) {
      delete data["published_at"];
      return;
    }
    const raw = data["is_published"];
    const willPublish = raw === true || raw === "true" || raw === 1 || raw === "1";
    const willUnpublish = raw === false || raw === "false" || raw === 0 || raw === "0";
    const wasPublished = oldCourse.is_published;

    if (willPublish && !wasPublished) {
      data["published_at"] = new Date();
      return;
    }
    if (willUnpublish && wasPublished) {
      data["published_at"] = null;
      return;
    }
    if (willPublish && wasPublished) {
      delete data["published_at"];
      return;
    }
    delete data["published_at"];
  }

  private unwrapDetailPayload<T>(payload: { data: T | T[] }): T | undefined {
    const d = payload.data;
    return Array.isArray(d) ? d[0] : d;
  }

  /** Direct Prisma patch for publish/unpublish — avoids DTO/super.save dropping boolean fields. */
  private buildPublishStatePatch(
    oldCourse: { is_published: boolean },
    data: Record<string, unknown>,
  ): { is_published: boolean; published_at: Date | null } | null {
    if (!Object.prototype.hasOwnProperty.call(data, "is_published")) {
      return null;
    }

    const requestedPublished = this.normalizePublishedFlag(data["is_published"]);
    const transition = this.getPublishStateTransition(!!oldCourse.is_published, requestedPublished);
    if (transition === "publish") {
      return { is_published: true, published_at: new Date() };
    }
    if (transition === "unpublish") {
      return { is_published: false, published_at: null };
    }
    return null;
  }

  private hasSaveableScalarFields(data: Record<string, unknown>): boolean {
    const skip = new Set(["id", "is_published", "published_at"]);
    return Object.entries(data).some(([key, value]) => !skip.has(key) && value !== undefined);
  }

  private normalizePublishedFlag(value: unknown): boolean {
    return value === true || value === "true" || value === 1 || value === "1";
  }

  private getPublishStateTransition(
    wasPublished: boolean,
    nowPublished: boolean,
  ): "publish" | "unpublish" | null {
    if (!wasPublished && nowPublished) {
      return "publish";
    }
    if (wasPublished && !nowPublished) {
      return "unpublish";
    }
    return null;
  }

  private buildPublishStateLogPayload(
    oldCourse: Record<string, unknown>,
    updatedCourse: Record<string, unknown>,
  ): {
    old_data: Record<string, unknown>;
    new_data: Record<string, unknown>;
    changed_fields: Record<string, unknown>;
  } {
    const changed_fields: Record<string, unknown> = {
      is_published: { old: oldCourse["is_published"], new: updatedCourse["is_published"] },
    };
    if (oldCourse["published_at"] !== updatedCourse["published_at"]) {
      changed_fields["published_at"] = {
        old: oldCourse["published_at"] ?? null,
        new: updatedCourse["published_at"] ?? null,
      };
    }
    return {
      old_data: {
        is_published: oldCourse["is_published"],
        published_at: oldCourse["published_at"] ?? null,
        course_code: oldCourse["course_code"],
        title: oldCourse["title"],
      },
      new_data: {
        is_published: updatedCourse["is_published"],
        published_at: updatedCourse["published_at"] ?? null,
        course_code: updatedCourse["course_code"],
        title: updatedCourse["title"],
      },
      changed_fields,
    };
  }

  private resolvePublishTransition(
    oldCourse: { is_published: boolean },
    freshCourse: { is_published: boolean },
  ): "publish" | "unpublish" | null {
    return this.getPublishStateTransition(
      !!oldCourse.is_published,
      this.normalizePublishedFlag(freshCourse.is_published),
    );
  }

  private async logCoursePublishStateTransition(
    id: number,
    oldCourse: Record<string, unknown>,
    updatedCourse: Record<string, unknown>,
    transition: "publish" | "unpublish",
  ): Promise<void> {
    const payload = this.buildPublishStateLogPayload(oldCourse, updatedCourse);
    const logFn =
      transition === "publish"
        ? this.systemLogService.logPublish.bind(this.systemLogService)
        : this.systemLogService.logUnpublish.bind(this.systemLogService);

    await logFn(
      SystemLogEntityType.COURSE,
      id,
      payload.old_data,
      payload.new_data,
      payload.changed_fields,
    );
  }

  private async recordCourseSaveSystemLogs(
    id: number,
    oldCourse: { is_published: boolean },
  ): Promise<void> {
    const freshCourse = await this.prisma.client.course.findUnique({ where: { id } });
    if (!freshCourse) {
      return;
    }

    const oldRecord = oldCourse as Record<string, unknown>;
    const freshRecord = freshCourse as Record<string, unknown>;
    const publishTransition = this.resolvePublishTransition(oldCourse, freshCourse);

    if (publishTransition) {
      await this.logCoursePublishStateTransition(id, oldRecord, freshRecord, publishTransition);
    }

    let changedFields = SystemLogService.calculateChangedFields(oldRecord, freshRecord);
    if (publishTransition) {
      changedFields = this.omitPublishFieldsFromChangedFields(changedFields);
    }

    if (Object.keys(changedFields).length > 0) {
      await this.systemLogService.logUpdate(
        SystemLogEntityType.COURSE,
        id,
        oldRecord,
        freshRecord,
        changedFields,
      );
    }
  }

  private omitPublishFieldsFromChangedFields(
    changed: Record<string, unknown>,
  ): Record<string, unknown> {
    const out = { ...changed };
    delete out["is_published"];
    delete out["published_at"];
    return out;
  }

  /**
   * 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,
    };

    // Legacy / repair: published courses must show a date; fill from audit timestamps if column is null.
    if (
      data.is_published === true &&
      (data.published_at == null || data.published_at === undefined)
    ) {
      data = {
        ...data,
        published_at: data.updated_at ?? data.created_at ?? null,
      };
    }

    const transformed = plainToInstance(moduleCurrentCfg.detailDto, data, BNestDtoPlainTransformOptions);
    const detailRow = transformed as unknown as Record<string, unknown>;
    // Always echo DB publish flags on the DTO instance (detail GET must match list + Prisma).
    detailRow["is_published"] = Boolean(data.is_published);
    detailRow["published_at"] = data.is_published
      ? (detailRow["published_at"] ?? data.published_at ?? data.updated_at ?? data.created_at ?? null)
      : null;
    return this.moduleMethods.getReturnDataForDetail(transformed);
  }

  /**
   * Authoritative publish flags from DB (bypasses DTO/detail GET caching issues on the client).
   */
  async getPublishState(id: number): Promise<DetailResponseDataInterface<unknown>> {
    const row = await this.prisma.client.course.findUnique({
      where: { id },
      select: { id: true, is_published: true, published_at: true },
    });
    if (!row) {
      throw new NotFoundException(`Course with ID ${id} not found`);
    }
    return {
      data: [
        {
          id: row.id,
          is_published: row.is_published,
          published_at: row.published_at,
        },
      ],
    };
  }

  /**
   * Publish course — direct DB update (not generic module save / DTO path).
   */
  async publish(id: number): Promise<DetailResponseDataInterface<any>> {
    const oldCourse = await this.prisma.client.course.findUnique({ where: { id } });
    if (!oldCourse) {
      throw new NotFoundException(`Course with ID ${id} not found`);
    }

    if (oldCourse.is_published) {
      return this.getDetail(id);
    }

    await this.validatePublishRequirements(oldCourse, { is_published: true });

    await this.prisma.client.course.update({
      where: { id },
      data: { is_published: true, published_at: new Date() },
    });

    const response = await this.getDetail(id);
    await this.recordCourseSaveSystemLogs(id, oldCourse);
    return response;
  }

  /**
   * Unpublish course — direct DB update (not generic module save / DTO path).
   */
  async unpublish(id: number): Promise<DetailResponseDataInterface<any>> {
    const oldCourse = await this.prisma.client.course.findUnique({ where: { id } });
    if (!oldCourse) {
      throw new NotFoundException(`Course with ID ${id} not found`);
    }

    if (!oldCourse.is_published) {
      return this.getDetail(id);
    }

    await this.prisma.client.course.update({
      where: { id },
      data: { is_published: false, published_at: null },
    });

    const response = await this.getDetail(id);
    await this.recordCourseSaveSystemLogs(id, oldCourse);
    return response;
  }

  /**
   * 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(
      { course_code: "", title: "", assessment_id: null, knowledge_review_id: null, is_published: false },
      data,
    );

    if (data.is_published === true) {
      data.published_at = new Date();
    } else {
      data.published_at = null;
    }

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

    const freshCourse = await this.prisma.client.course.findUnique({
      where: { id: course.id },
    });
    const freshRecord = (freshCourse ?? course) as Record<string, unknown>;

    if (freshCourse && this.normalizePublishedFlag(freshCourse.is_published)) {
      await this.logCoursePublishStateTransition(
        course.id,
        { ...freshRecord, is_published: false, published_at: null },
        freshRecord,
        "publish",
      );
    }

    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`);
    }

    const dataRecord = data as Record<string, unknown>;

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

    const publishPatch = this.buildPublishStatePatch(oldCourse, dataRecord);
    if (publishPatch) {
      await this.prisma.client.course.update({
        where: { id },
        data: publishPatch,
      });
    }

    delete dataRecord["is_published"];
    delete dataRecord["published_at"];

    const requestContext = RequestContext.get<BNestAppRequestContext>();
    requestContext.moduleAuditSuppressUpdated = true;

    if (this.hasSaveableScalarFields(dataRecord)) {
      this.applyPublishedAtForPayload(oldCourse, dataRecord);
      await super.save(id, data);
    }

    requestContext.moduleAuditSuppressUpdated = false;

    const saveResponse = await this.getDetail(id);
    await this.recordCourseSaveSystemLogs(id, oldCourse);

    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.`,
      );
    }

    this.eventEmitter.emit(`${this.gVars.moduleCurrentCfg.name}.deleted`, {
      entityId: id,
      entityType: moduleNameToAuditEntityType(this.gVars.moduleCurrentCfg.name),
      data: 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);
  }
}