starr-philproperties/apps/api/services/reading_progress.service.js

/***********************************************************************************************************************************************************************
 * File Name: reading_progress.service.js
 * Type of Program: Service
 * Description: UPSERT-based progress tracking for unit and lesson reading activity.
 *
 *   upsertLessonRead — main entry point, called when a user reads a lesson.
 *                      UPSERTs the lesson row in lesson_reading_progress, then derives and
 *                      UPSERTs the parent unit row in unit_reading_progress.
 *                      Both writes run in a single transaction.
 *
 * UPSERT keys:
 *   lesson_reading_progress → (user_id, lesson_id)
 *   unit_reading_progress   → (user_id, unit_id)
 *
 * Derivation rule:
 *   unit → completed when ALL non-deleted lessons under it have a completed row for this user
 *
 * Author: Kenneth Obsequio (@lash0000)
 * Date Created: Jun. 26, 2026
 ***********************************************************************************************************************************************************************/
'use strict';

const sequelize             = require('../config/db.config');
const LessonReadingProgress = require('../models/courses/lesson_reading_progress.mdl');
const UnitReadingProgress   = require('../models/courses/unit_reading_progress.mdl');
const UnitLesson            = require('../models/courses/unit_lessons.mdl');
const Lesson                = require('../models/courses/lessons.mdl');

// ─── Core UPSERTs ────────────────────────────────────────────────────────────

async function upsertLessonProgress({ userId, courseId, unitId, lessonId, status }, t) {
    const now = new Date();
    const [record] = await LessonReadingProgress.upsert(
        {
            user_id:          userId,
            course_id:        courseId ?? null,   // NULL when read standalone
            unit_id:          unitId ?? null,     // NULL when read standalone
            lesson_id:        lessonId,
            status,
            completed_at:     status === 'completed' ? now : null,
            last_accessed_at: now,
            createdBy:        userId,
            updatedBy:        userId,
        },
        {
            conflictFields: ['user_id', 'lesson_id'],
            returning:      true,
            transaction:    t,
        }
    );
    return record;
}

async function upsertUnitProgress({ userId, courseId, unitId, status }, t) {
    const now = new Date();
    const [record] = await UnitReadingProgress.upsert(
        {
            user_id:          userId,
            course_id:        courseId ?? null,   // NULL when read standalone
            unit_id:          unitId,
            status,
            completed_at:     status === 'completed' ? now : null,
            last_accessed_at: now,
            createdBy:        userId,
            updatedBy:        userId,
        },
        {
            conflictFields: ['user_id', 'unit_id'],
            returning:      true,
            transaction:    t,
        }
    );
    return record;
}

// ─── Derivation helper ────────────────────────────────────────────────────────

// Unit is completed when every non-deleted lesson attached to it (via unit_lessons)
// has a completed row for this user.
async function deriveUnitStatus(userId, unitId, t) {
    const links = await UnitLesson.findAll({
        where:       { unit_id: unitId },
        attributes:  ['lesson_id'],
        transaction: t,
    });
    if (!links.length) return 'in_progress';

    const lessons = await Lesson.findAll({
        where:       { lesson_id: links.map(l => l.lesson_id) },
        attributes:  ['lesson_id'],
        transaction: t,
    });
    if (!lessons.length) return 'in_progress';

    const lessonIds      = lessons.map(l => l.lesson_id);
    const completedCount = await LessonReadingProgress.count({
        where: {
            user_id:   userId,
            lesson_id: lessonIds,
            status:    'completed',
        },
        transaction: t,
    });

    return completedCount === lessons.length ? 'completed' : 'in_progress';
}

// ─── Main entry point ─────────────────────────────────────────────────────────

/**
 * Called when a user reads (or finishes reading) a lesson.
 * Writes two rows in one transaction: lesson → unit.
 *
 * @param {number} userId
 * @param {Object} payload
 * @param {number} payload.courseId      — course BIGINT PK
 * @param {number} payload.unitId        — unit BIGINT PK
 * @param {number} payload.lessonId      — lesson BIGINT PK
 * @param {string} payload.lessonStatus  — 'in_progress' | 'completed'
 * @returns {{ lesson, unit }} — status snapshot for each level
 */
async function upsertLessonRead(userId, { courseId = null, unitId = null, lessonId, lessonStatus = 'in_progress' }) {
    const t = await sequelize.transaction();
    try {
        // 1. Lesson
        await upsertLessonProgress({
            userId,
            courseId,
            unitId,
            lessonId,
            status: lessonStatus,
        }, t);

        // 2. Unit — derived from all sibling lessons (skipped for standalone lesson reads)
        let unitStatus = null;
        if (unitId) {
            unitStatus = await deriveUnitStatus(userId, unitId, t);
            await upsertUnitProgress({
                userId,
                courseId,
                unitId,
                status: unitStatus,
            }, t);
        }

        await t.commit();

        return {
            lesson: { lesson_id: lessonId, status: lessonStatus },
            unit:   unitId ? { unit_id: unitId, status: unitStatus } : null,
        };
    } catch (err) {
        await t.rollback();
        throw err;
    }
}

// ─── Exports ──────────────────────────────────────────────────────────────────

module.exports = {
    upsertLessonRead,
    upsertLessonProgress,
    upsertUnitProgress,
    deriveUnitStatus,
};