starr-philproperties/apps/api/models/users/user_groups.mdl.js

/***********************************************************************************************************************************************************************
 * File Name: user_groups.mdl.js
 * Type of Program: Model
 * Description: Sequelize models for `user_groups` and `user_group_members` tables.
 *              Implements a Many-to-Many self-contained group system.
 *              Groups are used as named permission bundles.
 * Author: rgrgogu
 * Date Created: Oct. 6, 2025
 ***********************************************************************************************************************************************************************
 * Change History:
 * DATE                AUTHOR      LOG    DESCRIPTION
 * Oct. 6, 2025        rgrgogu     001    Initial creation - STAR Phase 1
 * May 23, 2026        rgrgogu     002    Added group_code — unique invite code for self-registration
 ***********************************************************************************************************************************************************************
 * HOW TO USE:
 *   const { mdl_UserGroups, mdl_UserGroupMembers } = require('./models/user_groups.mdl');
 ***********************************************************************************************************************************************************************/
const { DataTypes } = require('sequelize');
const sequelize = require('../../config/db.config');
const mdl_Users = require('./users.mdl');

// ─── UserGroups ────────────────────────────────────────────────────────────────
const mdl_UserGroups = sequelize.define('UserGroup', {
  group_id:   { type: DataTypes.BIGINT,      primaryKey: true, autoIncrement: true, label: "Group ID",    order: 1, hidden: true },
  name:       { type: DataTypes.STRING(50),  allowNull: false,                      label: "Group Name",  order: 2 },
  /**
   * group_code — unique, human-readable invite code distributed to users.
   * Used in the self-registration URL: /register?group_code=<value>
   * Automatically uppercased via a beforeValidate hook below.
   * Example: "SALES-2025", "ONBOARD-Q1"
   */
  group_code: { type: DataTypes.STRING(50),  allowNull: true,  unique: true,        label: "Group Code",  order: 3 },
  description:{ type: DataTypes.TEXT,                                                label: "Description", order: 4, hidden: true },
  is_active:  { type: DataTypes.BOOLEAN,     defaultValue: true,                    label: "Active",      order: 5 },

  // ── Audit trails ────────────────────────────────────────────────────────────
  createdBy: { type: DataTypes.BIGINT, allowNull: true, label: "Created By" },
  updatedBy: { type: DataTypes.BIGINT, allowNull: true, label: "Modified By" },
  deletedBy: { type: DataTypes.BIGINT, allowNull: true, label: "Deleted By" },
}, {
  tableName: 'user_groups',
  timestamps: true,
  paranoid:   true,
  hooks: {
    // Always store group_code in uppercase to make lookups case-insensitive
    beforeValidate: (group) => {
      if (group.group_code) group.group_code = group.group_code.toUpperCase().trim();
    },
  },
});

// ─── Junction: UserGroupMembers ────────────────────────────────────────────────
const mdl_UserGroupMembers = sequelize.define('UserGroupMember', {
  group_id:  { type: DataTypes.BIGINT, primaryKey: true },
  user_id:   { type: DataTypes.BIGINT, primaryKey: true },
  joined_at: { type: DataTypes.DATE,   defaultValue: DataTypes.NOW },

  // ── Audit trails ────────────────────────────────────────────────────────────
  createdBy: { type: DataTypes.BIGINT, allowNull: true, label: "Created By" },
  updatedBy: { type: DataTypes.BIGINT, allowNull: true, label: "Modified By" },
  deletedBy: { type: DataTypes.BIGINT, allowNull: true, label: "Deleted By" },
}, {
  tableName: 'user_group_members',
  timestamps: true,
  paranoid:   true,
});

// ─── Associations ──────────────────────────────────────────────────────────────
mdl_Users.belongsToMany(mdl_UserGroups, { through: mdl_UserGroupMembers, foreignKey: 'user_id',  otherKey: 'group_id', as: 'groups'   });
mdl_UserGroups.belongsToMany(mdl_Users, { through: mdl_UserGroupMembers, foreignKey: 'group_id', otherKey: 'user_id',  as: 'members'  });

mdl_UserGroups.hasMany(mdl_UserGroupMembers, { foreignKey: 'group_id' });
mdl_UserGroupMembers.belongsTo(mdl_UserGroups, { foreignKey: 'group_id' });

mdl_Users.hasMany(mdl_UserGroupMembers, { foreignKey: 'user_id' });
mdl_UserGroupMembers.belongsTo(mdl_Users, { foreignKey: 'user_id' });

mdl_UserGroups.belongsTo(mdl_Users, { as: 'creator', foreignKey: 'createdBy' });
mdl_UserGroups.belongsTo(mdl_Users, { as: 'modifier', foreignKey: 'updatedBy' });
mdl_UserGroups.belongsTo(mdl_Users, { as: 'deleter',  foreignKey: 'deletedBy' });

mdl_Users.belongsTo(mdl_Users, { as: 'creator', foreignKey: 'createdBy' });
mdl_Users.belongsTo(mdl_Users, { as: 'modifier', foreignKey: 'updatedBy' });
mdl_Users.belongsTo(mdl_Users, { as: 'deleter',  foreignKey: 'deletedBy' });

module.exports = { mdl_UserGroups, mdl_UserGroupMembers };