starr-philproperties/apps/api/services/certificate-record.service.js

/***********************************************************************************************************************************************************************
 * File Name    : certificate-record.service.js
 * Type         : Service
 * Description  : Resolves or lazily creates the persisted `certificates` row for a
 *                user/course pair (cert_no/ref_no assignment, instructor snapshot).
 *                Shared by the PDF download endpoint (certificate.controller.js) and
 *                the hourly issuance cron (cron/jobs/issue_certificates.cron.js) so
 *                both write through the same cert_no/ref_no sequence.
 *
 * Author: Kenneth Obsequio (@lash0000)
 * Date Created: Jul. 2, 2026
 ***********************************************************************************************************************************************************************/
'use strict';

const { formatDuration } = require('../utils/duration.util');

const {
  Course,
  CourseAssessment,
  QuizAttempt,
  Certificate,
  CourseInstructor,
} = require('../models/courses/courses.associations');

function formatInstructors(rows) {
  const names = rows.map(r => r.display_name);
  if (names.length === 0) return '';
  if (names.length === 1) return names[0];
  if (names.length === 2) return `${names[0]} and ${names[1]}`;
  return `${names[0]}, ${names[1]} and et. al`;
}

// cert_no format: YYYYMM-{userId:6}-{userCertSeq:5}
async function buildCertNo(userId) {
  const count  = await Certificate.count({ where: { user_id: userId } });
  const seq    = String(count + 1).padStart(5, '0');
  const uid    = String(userId).padStart(6, '0');
  const now    = new Date();
  const YYYYMM = `${now.getFullYear()}${String(now.getMonth() + 1).padStart(2, '0')}`;
  return `${YYYYMM}-${uid}-${seq}`;
}

// ref_no format: PP-YYYYMM-{globalSeq:5}  (unique across all certs)
async function buildRefNo() {
  const count  = await Certificate.count();
  const seq    = String(count + 1).padStart(5, '0');
  const now    = new Date();
  const YYYYMM = `${now.getFullYear()}${String(now.getMonth() + 1).padStart(2, '0')}`;
  return `PP-${YYYYMM}-${seq}`;
}

/**
 * Resolves (or creates) the Certificate row for a user/course. Idempotent —
 * safe to call from both the cron job and the on-demand download endpoint.
 * Returns null if the course has no assessment or the user hasn't passed it.
 */
async function ensureCertificateRecord({ userId, courseId }) {
  const course = await Course.findOne({
    where:      { course_id: courseId },
    attributes: ['course_id', 'title', 'duration_seconds'],
    include: [
      { model: CourseAssessment, as: 'assessment', attributes: ['assessment_id'], required: false },
      {
        model:      CourseInstructor,
        as:         'instructors',
        attributes: ['display_name', 'order_index'],
        required:   false,
        order:      [['order_index', 'ASC']],
      },
    ],
  });
  if (!course || !course.assessment) return null;

  const passedAttempt = await QuizAttempt.findOne({
    where: {
      user_id:       userId,
      assessment_id: course.assessment.assessment_id,
      passed:        true,
    },
    order:      [['createdAt', 'DESC']],
    attributes: ['score', 'createdAt'],
  });
  if (!passedAttempt) return null;

  const liveInstructors = formatInstructors(course.instructors ?? []);

  // CockroachDB does not support findOrCreate (uses temp PL/pgSQL functions).
  let cert = await Certificate.findOne({ where: { user_id: userId, course_id: courseId } });
  if (!cert) {
    cert = await Certificate.create({
      user_id:     userId,
      course_id:   courseId,
      cert_no:     await buildCertNo(userId),
      ref_no:      await buildRefNo(),
      instructors: liveInstructors,
      score:       passedAttempt.score ?? null,
      length_str:  formatDuration(course.duration_seconds),
      issued_at:   passedAttempt.createdAt,
    });
  } else if (liveInstructors !== (cert.instructors ?? '')) {
    await cert.update({ instructors: liveInstructors });
  }

  return cert;
}

module.exports = { ensureCertificateRecord, formatInstructors, buildCertNo, buildRefNo };