import { HttpCodes } from '../../config/Errors';
import { db } from '../../settings';
import { ResultSetHeader, RowDataPacket } from 'mysql2';
import { ErrorResponseC, SuccessResponseC } from '../services.response';
import courseLogs, { ICourseLogs, courseLogger } from './course.logs';
import { formatString } from '../../utils/Strings';
import { VideoServices } from './video.service';
import { DocumentServices } from './document.service';
import { TagServices } from './tag.service';
import { ChapterServices } from './chapter.service';


export class CourseService {
  /**
   * @description  Get a course details by course_id
   * @param course_id  - Number
   * @returns  ResponseT
   */
  static getSnai3iCourseById = async (
    course_id: number
  ): Promise<ResponseT> => {
    try {
      const sqlQuery = 'SELECT * FROM courses WHERE course_id = ?';
      const [[course]]: any = await db.query<RowDataPacket[]>(sqlQuery, [
        course_id,
      ]);
      if (!course) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }
      if (course.type !== 'snai3i') {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }
      const chaptersVideosDocuments =
        await CourseService.getChaptersVideosAndDocumentsByCourse(course_id);
      if (chaptersVideosDocuments instanceof ErrorResponseC) {
        return chaptersVideosDocuments;
      }

      const courseWithChaptersVideosDocuments = {
        ...course,
        chapters: (chaptersVideosDocuments as SuccessResponseC).data,
      };
      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        courseWithChaptersVideosDocuments,
        msg,
        HttpCodes.OK.code
      );
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  Get all active courses without chapters, videos, and documents
   * @returns  ResponseT
   */
  static getActiveSnai3iCourses = async (): Promise<ResponseT> => {
    try {
      const sqlQuery = `SELECT * FROM courses WHERE type = 'snai3i' AND status = 'active'`;
      const [courses] = await db.query<(CourseI & { chaptersCount: string })[]>(
        sqlQuery
      );
      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  Get all courses without chapters, videos, and documents
   * @returns  ResponseT
   */
  static getAllSnai3iCourses = async (): Promise<ResponseT> => {
    try {
      const sqlQuery = `SELECT * FROM courses WHERE type = 'snai3i'`;
      const [courses] = await db.query<(CourseI & { chaptersCount: string })[]>(
        sqlQuery
      );
      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  Get all courses by instructor
   * @param inst_designer_id  - Number
   * @returns  ResponseT
   */
  static getCoursesByInstructor = async (
    inst_designer_id: number
  ): Promise<ResponseT> => {
    try {

      const sqlQuery = `SELECT c.*, p.*, COUNT(o.order_id) as buyersCount
      FROM courses c 
      JOIN market_courses p ON c.course_id = p.course_id 
      LEFT JOIN orders o ON o.course_id = c.course_id
      WHERE c.course_id IN (SELECT course_id FROM market_courses WHERE inst_designer_id = ?)
      Group BY c.course_id
      ORDER BY c.updatedAt DESC`

      const [courses] = await db.query<(CourseI & { chaptersCount: string })[]>(
        sqlQuery,
        [inst_designer_id]
      );

      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  Get all courses by school
   * @param school_id  - Number
   */
  static getCoursesBySchool = async (school_id: number): Promise<ResponseT> => {
    try {
      const sqlQuery = `
      SELECT c.*, p.nb_teachers_accounts, m.* 
      FROM orders o 
      JOIN courses c ON o.course_id = c.course_id 
      JOIN packs p ON o.pack_id = p.pack_id 
      JOIN market_courses m ON m.course_id = o.course_id 
      WHERE o.school_id = ?`;

      const [courses] = await db.query<(CourseI & { chaptersCount: string })[]>(
        sqlQuery,
        [school_id]
      );

      const sqlGetTags = `SELECT c.* FROM categories c JOIN course_categories cc ON c.category_id = cc.category_id WHERE cc.course_id = ?`;
      for (const course of courses) {
        const [tags] = await db.query<TagI[]>(sqlGetTags, [course.course_id]);
        course.tags = tags.map((tag) => tag.name);
      }

      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  Get all courses by teacher
   * @param teacher_id  - Number
   */
  static getCoursesByTeacher = async (
    teacher_id: number
  ): Promise<ResponseT> => {
    try {
      const sqlQuery = `
    SELECT c.*, m.* 
    FROM enrollments e 
    JOIN courses c ON e.course_id = c.course_id 
    JOIN market_courses m ON e.course_id = m.course_id 
    WHERE e.teacher_id = ?`;
      const [courses] = await db.query<(CourseI & { chaptersCount: string })[]>(
        sqlQuery,
        [teacher_id]
      );

      const sqlGetTags = `SELECT c.* FROM categories c JOIN course_categories cc ON c.category_id = cc.category_id WHERE cc.course_id = ?`;
      for (const course of courses) {
        const [tags] = await db.query<TagI[]>(sqlGetTags, [course.course_id]);
        course.tags = tags.map((tag) => tag.name);
      }
      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static getActiveMarketCourses = async (): Promise<ResponseT> => {
    try {
      // only active courses
      const sqlQuery = `SELECT * FROM market_courses m JOIN courses c ON m.course_id = c.course_id WHERE c.status = 'active'`;
      const [courses] = await db.query<MarketCourseI[]>(sqlQuery);

      // get tags for each course
      for (const course of courses) {
        const sqlQueryTags = `SELECT c.* FROM categories c JOIN course_categories cc ON c.category_id = cc.category_id WHERE cc.course_id = ?`;
        const [tags] = await db.query<TagI[]>(sqlQueryTags, [course.course_id]);
        course.tags = tags.map((tag) => tag.name);
      }

      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static getMarketCourses = async (): Promise<ResponseT> => {
    try {
      // only active courses
      const sqlQuery = `SELECT m.*, c.*, COUNT(o.order_id) as buyersCount
      FROM market_courses m 
      JOIN courses c ON m.course_id = c.course_id
      LEFT JOIN orders o ON o.course_id = m.course_id
      GROUP BY m.course_id
      `;
      const [courses] = await db.query<MarketCourseI[]>(sqlQuery);

      // get tags for each course
      for (const course of courses) {
        const sqlQueryTags = `SELECT c.* FROM categories c JOIN course_categories cc ON c.category_id = cc.category_id WHERE cc.course_id = ?`;
        const [tags] = await db.query<TagI[]>(sqlQueryTags, [course.course_id]);
        course.tags = tags.map((tag) => tag.name);
      }

      // sort by updatedAt
      courses.sort((a, b) => {
        return (
          new Date(b.updatedAt).getTime() - new Date(a.updatedAt).getTime()
        );
      });

      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSES_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, courses, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static getMarketCourseById = async (
    course_id: number
  ): Promise<ResponseT> => {
    try {
      const sqlQuery = `SELECT * FROM market_courses m JOIN courses c ON m.course_id = c.course_id WHERE m.course_id = ?`;
      const [[course]] = await db.query<MarketCourseI[]>(sqlQuery, [course_id]);
      if (!course) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }

      // select c.name from Categories c join course_categories cc on c.category_id = cc.category_id where cc.course_id =
      // tags is an array of categories names
      const sqlQueryTags = `SELECT c.* FROM categories c JOIN course_categories cc ON c.category_id = cc.category_id WHERE cc.course_id = ?`;
      const [tags] = await db.query<TagI[]>(sqlQueryTags, [course_id]);
      course.tags = tags.map((tag) => tag.name);

      const resp: ICode<ICourseLogs> = courseLogs.GET_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, course, msg, HttpCodes.OK.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  create a course
   * @param inst_designer_id
   * @param inst_designer_firstName
   * @param inst_designer_lastName
   * @param title
   * @param description
   * @param price
   * @param chapters
   * @param thumbnail
   * @param videoThumbnail
   * @returns
   */
  static createSnai3iCourse = async (
    title: string,
    description: string,
    chapters: ChapterI[],
    thumbnail: string = 'default-thumbnail.jpg'
  ): Promise<ResponseT> => {
    try {
      const totalHours = this.calculateTotalHoursFromChapters(chapters);
      const chaptersCount = chapters.length;
      const type = 'snai3i';

      const courseResponse = await CourseService.createCourseRow(
        title,
        description,
        totalHours,
        type,
        chaptersCount,
        thumbnail
      );
      if (courseResponse instanceof ErrorResponseC) {
        return courseResponse;
      }
      const course = (courseResponse as SuccessResponseC).data as CourseI;
      const courseId = course.course_id;
      const chaptersResponse = await ChapterServices.createChapters(
        courseId,
        chapters
      );
      if (chaptersResponse instanceof ErrorResponseC) {
        return chaptersResponse;
      }
      const resp: ICode<ICourseLogs> = courseLogs.CREATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        {
          ...course,
          chapters,
        },
        msg,
        HttpCodes.Created.code
      );
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static createMarketCourse = async (
    title: string,
    description: string,
    totalHours: number,
    price: number,
    tags: string[],
    chaptersCount: number,
    inst_designer_id: number | null = 48,
    inst_designer_firstName: string | null = 'Snai3i',
    inst_designer_lastName: string | null = 'صنايعي',
    thumbnail: string = 'default-thumbnail.jpg',
    videoThumbnail: string = 'default-thumbnail.jpg',
    status: string = 'pendingCreation'
  ): Promise<ResponseT> => {
    try {
      const type = 'market';
      const courseResponse = await CourseService.createCourseRow(
        title,
        description,
        totalHours,
        type,
        chaptersCount,
        thumbnail,
        status
      );
      if (courseResponse instanceof ErrorResponseC) {
        return courseResponse;
      }
      const course = (courseResponse as SuccessResponseC).data as CourseI;
      const courseId = course.course_id;
      const marketCourseResponse = await CourseService.createMarketCourseRow(
        courseId,
        inst_designer_id!,
        inst_designer_firstName!,
        inst_designer_lastName!,
        price,
        videoThumbnail,
        tags
      );
      if (marketCourseResponse instanceof ErrorResponseC) {
        return marketCourseResponse;
      }
      const marketCourse = (marketCourseResponse as SuccessResponseC)
        .data as MarketCourseI;
      const courseWithMarketCourse = {
        ...course,
        ...marketCourse,
      };
      const resp: ICode<ICourseLogs> = courseLogs.CREATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        courseWithMarketCourse,
        msg,
        HttpCodes.Created.code
      );
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  delete a course
   * @param course_id  - Number
   * @returns  ResponseT
   */
  static deleteCourse = async (course_id: number): Promise<ResponseT> => {
    try {

      const sqlDeleteQuery = 'DELETE FROM courses WHERE course_id = ?';
      const [result]: any = await db.query<ResultSetHeader[]>(sqlDeleteQuery, [
        course_id,
      ]);
      if (result.affectedRows === 0) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }
      const resp: ICode<ICourseLogs> = courseLogs.DELETE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  /**
   * @description  update a course
   * @param course_id  - Number
   * @param course  - CourseI
   * @returns  ResponseT
   */
  static updateCourse = async (
    course_id: number,
    course: CourseI
  ): Promise<ResponseT> => {
    try {
      const { chapters, ...courseData } = course;

      const getOldCourseResponse = await CourseService.getSnai3iCourseById(
        course_id
      );
      if (getOldCourseResponse instanceof ErrorResponseC) {
        return getOldCourseResponse;
      }

      const oldCourse = (getOldCourseResponse as SuccessResponseC)
        .data as CourseI;

      const oldChapters = oldCourse.chapters!;
      const newChapters = chapters!;

      // delete deleted chapters
      const deletedChapters = ChapterServices.identifyDeletedChapters(
        oldChapters,
        newChapters
      );
      if (deletedChapters.length > 0) {
        const deletedChaptersResponse = await ChapterServices.deleteChapters(
          deletedChapters.map((chapter) => chapter.chapter_id)
        );
        if (deletedChaptersResponse instanceof ErrorResponseC) {
          return deletedChaptersResponse;
        }
      }

      // insert added chapters (with videos and documents)
      const addedChapters = ChapterServices.identifyAddedChapters(
        oldChapters,
        newChapters
      );
      if (addedChapters.length > 0) {
        const chaptersResponse = await ChapterServices.createChapters(
          course_id,
          addedChapters
        );
        if (chaptersResponse instanceof ErrorResponseC) {
          return chaptersResponse;
        }
      }

      // update chapters row
      const updatedChapters = ChapterServices.identifyUpdatedChapters(
        oldChapters,
        newChapters
      );
      if (updatedChapters.length > 0) {
        const updateChaptersResponse = await ChapterServices.updateChapters(
          updatedChapters
        );
        if (updateChaptersResponse instanceof ErrorResponseC) {
          return updateChaptersResponse;
        }
      }

      // update videos and documents that are in the updated chapters and not in the added chapters
      const chaptersToCheck = ChapterServices.filterChapters(
        newChapters,
        addedChapters
      );

      // use chaptersToVideosAndDocuments
      const [oldVideos, oldDocuments] =
        CourseService.chaptersToVideosAndDocuments(oldChapters);

      const [newVideos, newDocuments] =
        CourseService.chaptersToVideosAndDocuments(chaptersToCheck);

      const addedVideos: VideoI[] = VideoServices.identifyAddedVideos(
        oldVideos,
        newVideos
      );
      const deletedVideos: VideoI[] = VideoServices.identifyDeletedVideos(
        oldVideos,
        newVideos
      );
      const updatedVideos: VideoI[] = VideoServices.identifyUpdatedVideos(
        oldVideos,
        newVideos
      );
      const addedDocuments: DocumentI[] =
        DocumentServices.identifyAddedDocuments(oldDocuments, newDocuments);
      const deletedDocuments: DocumentI[] =
        DocumentServices.identifyDeletedDocuments(oldDocuments, newDocuments);
      const updatedDocuments: DocumentI[] =
        DocumentServices.identifyUpdatedDocuments(oldDocuments, newDocuments);

      // insert added videos
      if (addedVideos.length > 0) {
        const videosResponse = await VideoServices.insertVideos(addedVideos);
        if (videosResponse instanceof ErrorResponseC) {
          return videosResponse;
        }
      }

      // delete deleted videos
      if (deletedVideos.length > 0) {
        // deleted videos that are in deleted chapters are already deleted so remove them from the list
        const videosToDelete = deletedVideos.filter(
          (video) =>
            !deletedChapters.some(
              (chapter) => chapter.chapter_id === video.chapter_id
            )
        );
        if (videosToDelete.length > 0) {
          const videosResponse = await VideoServices.deleteVideos(
            videosToDelete.map((video) => video.video_id)
          );
          if (videosResponse instanceof ErrorResponseC) {
            return videosResponse;
          }
        }
      }

      // update updated videos
      if (updatedVideos.length > 0) {
        const videosResponse = await VideoServices.updateVideos(updatedVideos);

        if (videosResponse instanceof ErrorResponseC) {
          return videosResponse;
        }
      }

      // insert added documents
      if (addedDocuments.length > 0) {
        const documentsResponse = await DocumentServices.insertDocuments(
          addedDocuments
        );
        if (documentsResponse instanceof ErrorResponseC) {
          return documentsResponse;
        }
      }

      // delete deleted documents
      if (deletedDocuments.length > 0) {
        // deleted documents that are in deleted chapters are already deleted so remove them from the list
        const documentsToDelete = deletedDocuments.filter(
          (document) =>
            !deletedChapters.some(
              (chapter) => chapter.chapter_id === document.chapter_id
            )
        );
        if (documentsToDelete.length > 0) {
          const documentsResponse = await DocumentServices.deleteDocuments(
            documentsToDelete.map((document) => document.document_id)
          );
          if (documentsResponse instanceof ErrorResponseC) {
            return documentsResponse;
          }
        }
      }

      // update updated documents
      if (updatedDocuments.length > 0) {
        const documentsResponse = await DocumentServices.updateDocuments(
          updatedDocuments
        );
        if (documentsResponse instanceof ErrorResponseC) {
          return documentsResponse;
        }
      }

      // update course row
      // title,
      // description,
      // totalHours,
      // thumbnail
      const { title, description, totalHours, thumbnail, status } = courseData;
      const updateCourseResponse = await CourseService.updateCourseRow(
        course_id,
        title,
        description,
        thumbnail!,
        totalHours,
        newChapters.length,
        status
      );
      if (updateCourseResponse instanceof ErrorResponseC) {
        return updateCourseResponse;
      }

      const resp: ICode<ICourseLogs> = courseLogs.UPDATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static updateMarketCourse = async (
    course_id: number,
    title: string,
    description: string,
    totalHours: number,
    price: number,
    tags: string[],
    chaptersCount: number,
    status: string = 'pendingUpdate',
    thumbnail: string = 'default-thumbnail.jpg',
    videoThumbnail: string = 'default-thumbnail.jpg'
  ): Promise<ResponseT> => {
    try {
      const courseResponse = await CourseService.updateCourseRow(
        course_id,
        title,
        description,
        thumbnail,
        totalHours,
        chaptersCount,
        status
      );
      if (courseResponse instanceof ErrorResponseC) {
        return courseResponse;
      }
      const marketCourseResponse = await CourseService.updateMarketCourseRow(
        course_id,
        price,
        videoThumbnail,
        tags
      );
      if (marketCourseResponse instanceof ErrorResponseC) {
        return marketCourseResponse;
      }
      const resp: ICode<ICourseLogs> = courseLogs.UPDATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static updateCourseStatus = async (
    course_id: number,
    status: string
  ): Promise<ResponseT> => {
    try {
      const sqlUpdateQuery = `UPDATE courses SET status = ? WHERE course_id = ?`;
      const [result]: any = await db.query<ResultSetHeader[]>(sqlUpdateQuery, [
        status,
        course_id,
      ]);

      if (result.affectedRows === 0) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }

      const resp: ICode<ICourseLogs> = courseLogs.UPDATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static createCourseRow = async (
    title: string,
    description: string,
    totalHours: number,
    type: string,
    chaptersCount: number,
    thumbnail: string = 'default-thumbnail.jpg',
    status: string = 'active'
  ): Promise<ResponseT> => {
    try {
      const createdAt = new Date();
      const updatedAt = createdAt;
      const sqlInsertQuery = `INSERT INTO courses (title, description, thumbnail, totalHours, status, type, chaptersCount, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
      const [result]: any = await db.query<ResultSetHeader[]>(sqlInsertQuery, [
        title,
        description,
        thumbnail, // || 'default-thumbnail.jpg',
        totalHours,
        status,
        type,
        chaptersCount,
        createdAt,
        updatedAt,
      ]);

      const courseId = result.insertId;
      const course = {
        course_id: courseId,
        title,
        description,
        thumbnail,
        totalHours,
        status,
        type,
        chaptersCount,
        createdAt,
        updatedAt,
      };
      const resp: ICode<ICourseLogs> = courseLogs.CREATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        course,
        msg,
        HttpCodes.Created.code
      );
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static createMarketCourseRow = async (
    course_id: number,
    inst_designer_id: number,
    inst_designer_firstName: string,
    inst_designer_lastName: string,
    price: number,
    videoThumbnail: string,
    tags: string[]
  ): Promise<ResponseT> => {
    try {
      const type = 'market';
      const sqlInsertQuery = `INSERT INTO market_courses (course_id, inst_designer_id, inst_designer_firstName, inst_designer_lastName, price, videoThumbnail) VALUES (?, ?, ?, ?, ?, ?)`;
      const [result]: any = await db.query<ResultSetHeader[]>(sqlInsertQuery, [
        course_id,
        inst_designer_id,
        inst_designer_firstName,
        inst_designer_lastName,
        price,
        videoThumbnail,
      ]);
      // insert tags into categories table
      const tagsResponse = await TagServices.insertTags(course_id, tags);

      if (tagsResponse instanceof ErrorResponseC) {
        return tagsResponse;
      }

      const marketCourse = {
        course_id,
        inst_designer_id,
        inst_designer_firstName,
        inst_designer_lastName,
        price,
        videoThumbnail,
        tags,
      };
      const resp: ICode<ICourseLogs> = courseLogs.CREATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        marketCourse,
        msg,
        HttpCodes.Created.code
      );
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static updateCourseRow = async (
    course_id: number,
    title: string,
    description: string,
    thumbnail: string,
    totalHours: number,
    chaptersCount: number,
    status: string
  ): Promise<ResponseT> => {
    try {
      const sqlUpdateQuery = `UPDATE courses SET title = ?, description = ?, thumbnail = ?, totalHours = ?, chaptersCount = ?, updatedAt = ?, status = ? WHERE course_id = ?`;
      const [result]: any = await db.query<ResultSetHeader[]>(sqlUpdateQuery, [
        title,
        description,
        thumbnail,
        totalHours,
        chaptersCount,
        new Date(),
        status,
        course_id,
      ]);

      if (result.affectedRows === 0) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }

      const resp: ICode<ICourseLogs> = courseLogs.UPDATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static updateMarketCourseRow = async (
    course_id: number,
    price: number,
    videoThumbnail: string,
    tags: string[]
  ): Promise<ResponseT> => {
    try {
      const sqlUpdateQuery = `UPDATE market_courses SET price = ?, videoThumbnail = ? WHERE course_id = ?`;
      const [result]: any = await db.query<ResultSetHeader[]>(sqlUpdateQuery, [
        price,
        videoThumbnail,
        course_id,
      ]);

      if (result.affectedRows === 0) {
        const msg = formatString(courseLogs.COURSE_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.COURSE_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }

      const oldTagsResponse = await TagServices.getTagsByCourseId(course_id);
      if (oldTagsResponse instanceof ErrorResponseC) {
        return oldTagsResponse;
      }
      const oldTags = (oldTagsResponse as SuccessResponseC).data as string[];

      const tagsResponse = await TagServices.updateTags(
        course_id,
        tags,
        oldTags
      );
      if (tagsResponse instanceof ErrorResponseC) {
        return tagsResponse;
      }

      const resp: ICode<ICourseLogs> = courseLogs.UPDATE_COURSE_SUCCESS;
      const msg = formatString(resp.message, {
        courseId: course_id,
      });
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(resp.type, {}, msg, HttpCodes.Accepted.code);
    } catch (err) {
      const msg = formatString(courseLogs.COURSE_ERROR_GENERIC.message, {
        error: (err as Error)?.message || '',
      });
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.COURSE_ERROR_GENERIC.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  static getChaptersVideosAndDocumentsByCourse = async (
    course_id: number
  ): Promise<ResponseT> => {
    try {
      const sqlQueryChapters = `
            SELECT * FROM chapters WHERE course_id = ?;
        `;
      const [chapters]: any = await db.query<RowDataPacket[]>(
        sqlQueryChapters,
        [course_id]
      );
      if (!chapters) {
        const msg = formatString(courseLogs.CHAPTER_ERROR_NOT_FOUND.message, {
          courseId: course_id,
        });
        courseLogger.error(msg);
        return new ErrorResponseC(
          courseLogs.CHAPTER_ERROR_NOT_FOUND.type,
          HttpCodes.NotFound.code,
          msg
        );
      }

      const chaptersId = chapters.map(
        (chapter: ChapterI) => chapter.chapter_id
      );

      const sqlQueryVideos = `SELECT * FROM videos WHERE chapter_id IN (?) ORDER BY position ASC`;
      const sqlQueryDocuments = `SELECT * FROM documents WHERE chapter_id IN (?) ORDER BY position ASC`;

      const [[videos], [documents]]: any = await Promise.all([
        db.query<RowDataPacket[]>(sqlQueryVideos, [chaptersId]),
        db.query<RowDataPacket[]>(sqlQueryDocuments, [chaptersId]),
      ]);

      const chaptersVideosDocuments = chapters.map((chapter: ChapterI) => {
        const chapterVideos = videos.filter(
          (video: VideoI) => video.chapter_id === chapter.chapter_id
        );
        const chapterDocuments = documents.filter(
          (document: DocumentI) => document.chapter_id === chapter.chapter_id
        );
        const docsAndVideosMixed = [...chapterVideos, ...chapterDocuments].sort(
          (a, b) => a.position - b.position
        );
        return {
          ...chapter,
          data: docsAndVideosMixed,
        };
      });

      const resp: ICode<ICourseLogs> =
        courseLogs.GET_VIDEOS_AND_DOCUMENTS_SUCCESS;
      const msg = resp.message;
      courseLogger.info(msg, { type: resp.type });
      return new SuccessResponseC(
        resp.type,
        chaptersVideosDocuments as ChapterI[],
        msg,
        HttpCodes.OK.code
      );
    } catch (err) {
      const msg = formatString(
        courseLogs.GET_VIDEOS_AND_DOCUMENTS_ERROR.message,
        {
          error: (err as Error)?.message || '',
        }
      );
      courseLogger.error(msg, err as Error);
      return new ErrorResponseC(
        courseLogs.GET_VIDEOS_AND_DOCUMENTS_ERROR.type,
        HttpCodes.InternalServerError.code,
        msg
      );
    }
  };

  // filter videos and docs from data array
  static filterVideosAndDocuments = (
    data: (VideoI | DocumentI)[]
  ): [VideoI[], DocumentI[]] => {
    const videos = data.filter(
      (item) => (item as VideoI).videoLength
    ) as VideoI[];
    const documents = data.filter(
      (item) => !(item as VideoI).videoLength
    ) as DocumentI[];
    return [videos, documents];
  };

  // from chapters array data to videos and documents array
  static chaptersToVideosAndDocuments = (
    chapters: ChapterI[]
  ): [VideoI[], DocumentI[]] => {
    const videos: VideoI[] = [];
    const documents: DocumentI[] = [];
    chapters.forEach((chapter) => {
      const [chapterVideos, chapterDocuments] =
        CourseService.filterVideosAndDocuments(chapter.data!);
      videos.push(...chapterVideos);
      documents.push(...chapterDocuments);
    });
    return [videos, documents];
  };

  static calculateTotalHoursFromChapters = (chapters: ChapterI[]): number => {
    const [videos, _] = CourseService.chaptersToVideosAndDocuments(chapters);

    const totalMinutes = videos.reduce(
      (acc, video) => acc + video.videoLength,
      0
    );

    // hours = totalMinutes / 60 (rounded down) but not less than 1
    return Math.max(1, Math.floor(totalMinutes / 60));
  };
}