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

/***********************************************************************************************************************************************************************
 * File Name: unit_reading_progress.mdl.js
 * Type of Program: Model
 * Description: Tracks a user's reading progress at the unit level.
 *
 *   UnitReadingProgress — one row per (user, unit).
 *                         UPSERT key: (user_id, unit_id)
 *                         status flips in_progress → completed when all sibling lessons are completed.
 *                         last_accessed_at updated on every UPSERT.
 *
 * Author: Kenneth Obsequio (@lash0000)
 * Date Created: Jun. 26, 2026
 ***********************************************************************************************************************************************************************/
const { DataTypes } = require('sequelize');
const sequelize     = require('../../config/db.config');

const UnitReadingProgress = sequelize.define('UnitReadingProgress', {
    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:  true,   // NULL when the unit is read standalone (outside any course)
        references: { model: 'courses', key: 'course_id' },
        onDelete:   'CASCADE',
    },
    unit_id: {
        type:       DataTypes.BIGINT,
        allowNull:  false,
        references: { model: 'units',   key: 'unit_id'   },
        onDelete:   'CASCADE',
    },
    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:  'unit_reading_progress',
    timestamps: true,
    paranoid:   false,
    indexes: [
        {
            unique: true,
            fields: ['user_id', 'unit_id'],
            name:   'uq_urp_user_unit',
        },
        { fields: ['user_id'],   name: 'idx_urp_user_id'   },
        { fields: ['course_id'], name: 'idx_urp_course_id' },
        { fields: ['unit_id'],   name: 'idx_urp_unit_id'   },
        { fields: ['status'],    name: 'idx_urp_status'    },
    ],
});

module.exports = UnitReadingProgress;