starr-philproperties/apps/api/models/courses/course_reading_progress.mdl.js

/***********************************************************************************************************************************************************************
 * File Name: course_reading_progress.mdl.js
 * Type of Program: Model
 * Description: Tracks a user's reading progress through a course hierarchy (course → unit → lesson).
 *
 *   CourseReadingProgress — one row per (user, type, reference).
 *                           UPSERT key: (user_id, type, reference_id)
 *                           reference_id points to course.uuid | unit.uuid | lesson.uuid based on type.
 *                           status flips in_progress → completed when the user finishes the item.
 *                           last_accessed_at updated on every UPSERT.
 *
 * Author: Kenneth Obsequio (@lash0000)
 * Date Created: Jun. 21, 2026
 ***********************************************************************************************************************************************************************/
const { DataTypes } = require('sequelize');
const sequelize = require('../../config/db.config');

const CourseReadingProgress = sequelize.define('CourseReadingProgress', {
    progress_id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
    },
    user_id: {
        type: DataTypes.BIGINT,
        allowNull: false,
        references: { model: 'users', key: 'user_id' },
        onDelete: 'CASCADE',
    },
    course_id: {
        type: DataTypes.BIGINT,
        allowNull: false,
        references: { model: 'courses', key: 'course_id' },
        onDelete: 'CASCADE',
    },
    reference_id: {
        type: DataTypes.UUID,
        allowNull: false,
        comment: 'uuid of the course | unit | lesson depending on type.',
    },
    type: {
        type: DataTypes.ENUM('course', 'unit', 'lesson'),
        allowNull: false,
    },
    status: {
        type: DataTypes.ENUM('in_progress', 'completed'),
        allowNull: false,
        defaultValue: 'in_progress',
    },
    completed_at: {
        type: DataTypes.DATE,
        allowNull: true,
        comment: 'Set when status flips to completed. Null while in_progress.',
    },
    last_accessed_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: DataTypes.NOW,
        comment: 'Updated on every UPSERT.',
    },

    // ── Audit trails ────────────────────────────────────────────────────────
    createdBy: { type: DataTypes.BIGINT, allowNull: true },
    updatedBy: { type: DataTypes.BIGINT, allowNull: true },
}, {
    tableName: 'course_reading_progress',
    timestamps: true,
    paranoid: false,    // progress rows are never soft-deleted
    indexes: [
        {
            unique: true,
            fields: ['user_id', 'type', 'reference_id'],
            name: 'uq_crp_user_type_reference',
        },
        { fields: ['user_id'],   name: 'idx_crp_user_id'   },
        { fields: ['course_id'], name: 'idx_crp_course_id' },
        { fields: ['type'],      name: 'idx_crp_type'       },
        { fields: ['status'],    name: 'idx_crp_status'     },
    ],
});

module.exports = CourseReadingProgress;