starr-philproperties/apps/api/utils/courses/hierarchy.util.js

"use strict";

/***********************************************************************************************************************************************************************
 * File Name: hierarchy.util.js
 * Type of Program: Utility
 * Description: Helpers for the junction-based course hierarchy
 *              (courses ⇄ course_units ⇄ units ⇄ unit_lessons ⇄ lessons).
 *
 *   Sequelize belongsToMany includes surface the junction row under the through-
 *   model key ("CourseUnit" / "UnitLesson"). These helpers flatten that back to
 *   the flat `order_index` field the API has always exposed, so response shapes
 *   stay identical to the pre-junction era.
 *
 * Author: Kenneth Obsequio (@lash0000)
 * Date Created: Jul. 7, 2026
 ***********************************************************************************************************************************************************************/

const sequelize = require("../../config/db.config");

// ── Response flattening ───────────────────────────────────────────────────────

/** Sort lessons by their unit_lessons.order_index and flatten it onto each row. */
function flattenLessons(lessons = []) {
  return [...lessons]
    .map((l) => {
      const { UnitLesson: link, ...rest } = l;
      return { ...rest, order_index: link?.order_index ?? rest.order_index ?? 0 };
    })
    .sort((a, b) => (a.order_index ?? 0) - (b.order_index ?? 0));
}

/** Sort units by their course_units.order_index, flatten it, and recurse into lessons. */
function flattenUnits(units = []) {
  return [...units]
    .map((u) => {
      const { CourseUnit: link, ...rest } = u;
      return {
        ...rest,
        order_index: link?.order_index ?? rest.order_index ?? 0,
        ...(rest.lessons ? { lessons: flattenLessons(rest.lessons) } : {}),
      };
    })
    .sort((a, b) => (a.order_index ?? 0) - (b.order_index ?? 0));
}

// ── Junction row maintenance ──────────────────────────────────────────────────

/** Next order_index for appending to a parent (max + 1, or 0 when empty). */
async function nextOrderIndex(JunctionModel, where, transaction) {
  const max = await JunctionModel.max("order_index", { where, transaction });
  return Number.isFinite(max) ? max + 1 : 0;
}

/**
 * Persist a full ordering: ids[i] gets order_index i.
 * Ignores ids without an existing junction row.
 */
async function reorderJunction(JunctionModel, parentField, parentId, childField, orderedIds = [], transaction) {
  await Promise.all(orderedIds.map((id, i) =>
    JunctionModel.update(
      { order_index: i },
      { where: { [parentField]: parentId, [childField]: id }, transaction }
    )
  ));
}

// ── Structure counts (raw SQL — junction traversals) ──────────────────────────

/** Count non-archived lessons reachable from a course through its attached units. */
async function countCourseLessons(courseId) {
  const [row] = await sequelize.query(`
    SELECT CAST(COUNT(DISTINCT l.lesson_id) AS INTEGER) AS total
    FROM lessons l
    JOIN unit_lessons ul ON ul.lesson_id = l.lesson_id
    JOIN units u         ON u.unit_id = ul.unit_id AND u."deletedAt" IS NULL
    JOIN course_units cu ON cu.unit_id = u.unit_id AND cu.course_id = :courseId
    WHERE l."deletedAt" IS NULL
  `, { replacements: { courseId }, type: sequelize.QueryTypes.SELECT });
  return Number(row?.total ?? 0);
}

/** Non-archived unit ids attached to a course, in course order. */
async function getCourseUnitIds(courseId) {
  const rows = await sequelize.query(`
    SELECT u.unit_id
    FROM units u
    JOIN course_units cu ON cu.unit_id = u.unit_id AND cu.course_id = :courseId
    WHERE u."deletedAt" IS NULL
    ORDER BY cu.order_index ASC
  `, { replacements: { courseId }, type: sequelize.QueryTypes.SELECT });
  return rows.map((r) => r.unit_id);
}

/** Non-archived lesson ids attached to a unit, in unit order. */
async function getUnitLessonIds(unitId) {
  const rows = await sequelize.query(`
    SELECT l.lesson_id
    FROM lessons l
    JOIN unit_lessons ul ON ul.lesson_id = l.lesson_id AND ul.unit_id = :unitId
    WHERE l."deletedAt" IS NULL
    ORDER BY ul.order_index ASC
  `, { replacements: { unitId }, type: sequelize.QueryTypes.SELECT });
  return rows.map((r) => r.lesson_id);
}

module.exports = {
  flattenLessons,
  flattenUnits,
  nextOrderIndex,
  reorderJunction,
  countCourseLessons,
  getCourseUnitIds,
  getUnitLessonIds,
};