Technical Implementation Notes - Currency Training System

Current Implementation Analysis

The currency system is currently managed through:

1. Database Tables

Main Currency Tables:

• channel_currency_instructor - Instructor currency records
• channel_currency_coach - Coach currency records
• channel_currency_trainer - Trainer currency records
• channel_currency_military - Military currency records
• members table fields: currency_instructor, currency_coach, currency_trainer, currency_military

Checkbox Tables (Track Individual Training Components):

• channel_currency_instructor_checkboxes - Instructor checkbox completions
• channel_currency_coach_checkboxes - Coach checkbox completions
• channel_currency_trainer_checkboxes - Trainer checkbox completions
• channel_currency_military_checkboxes - Military checkbox completions

Note: Flyers are excluded from this new system - they maintain the existing 6-month activity-based currency model.

2. Key Service Files

• api/src/features/shared/currency/service/index.js - Base currency service with renewal date calculations
• api/src/features/account/safety-training/service/*.service.js - Safety training services for each role
• api/src/features/account/safety-training/constant/index.js - Checkbox definitions for each role

3. Current Logic

• Fixed 6-month currency periods (Jan 16 - Jul 15, Jul 16 - Jan 15)
• Currency calculated based on training completion within fixed periods
• When recurrency training starts, system creates checkbox rows for member's current levels
• Checkbox completion determines updated approval levels via getNewApprovalLevel() method
• Expiration triggers deactivation after 6 months

4. Checkbox-Based Training System

Instructor Example (api/src/features/account/safety-training/service/instructor.service.js:221-231):

async getNewApprovalLevel() {
  const passedCheckboxes = this.checkboxes.filter(box => box.passed);
  const lastPassedLevel = passedCheckboxes.length > 0 
    ? passedCheckboxes[passedCheckboxes.length - 1].level || 0
    : 0;

  if (!lastPassedLevel) return 0;

  const approvalLevel = await this.safetyTrainingRepository.getApprovalLevel(lastPassedLevel);
  return approvalLevel?.approval_level_assigned_instructor || 0;
}

Role-Specific Checkboxes:

• Instructors: Level-based training (Level 1-4) + general requirements
• Trainers: Maintain vs Regain checkboxes with supervision requirements
• Coaches: Maintain vs Regain with coaching session requirements
• Military: Maintain vs Regain with military instruction requirements

Required Changes for Relative Currency Periods

1. Database Schema Changes

-- Add recurrency tracking fields to member tables
ALTER TABLE members 
ADD COLUMN currency_recurrent_date INT(11) DEFAULT NULL COMMENT 'Unix timestamp of last recurrency training',
ADD COLUMN currency_base_period VARCHAR(20) DEFAULT 'standard' COMMENT 'standard or recurrent',
ADD COLUMN currency_next_expiry INT(11) DEFAULT NULL COMMENT 'Calculated next expiry date';

-- Add recurrency tracking to currency tables
ALTER TABLE channel_currency_instructor
ADD COLUMN is_recurrent TINYINT(1) DEFAULT 0 COMMENT 'Whether this is recurrent training',
ADD COLUMN previous_expiry INT(11) DEFAULT NULL COMMENT 'Previous expiry date when recurrent taken';

-- Note: Flyer tables are NOT modified as flyers are excluded from this system

-- Similar for coach, trainer, military tables

2. Service Layer Changes

File: api/src/features/shared/currency/service/index.js

Add new methods to handle relative currency periods:

// New method to calculate currency based on recurrent date
calculateRecurrentCurrency(recurrentDate, member) {
  const recurrentTimestamp = new Date(recurrentDate).getTime();
  const now = Date.now();
  const monthsSinceRecurrent = (now - recurrentTimestamp) / (1000 * 60 * 60 * 24 * 30);
  
  // If within 6 months of recurrent, they're current
  if (monthsSinceRecurrent <= 6) {
    // Calculate next expiry relative to recurrent date
    const nextExpiry = new Date(recurrentTimestamp);
    nextExpiry.setMonth(nextExpiry.getMonth() + 6);
    
    // Align to next period boundary (Jan 16 or Jul 16)
    return this.alignToNextPeriodBoundary(nextExpiry);
  }
  
  return 'Expired';
}

// Method to determine if member needs relative currency
getCurrencyMode(member) {
  if (member.currency_recurrent_date && member.currency_base_period === 'recurrent') {
    return 'recurrent';
  }
  return 'standard';
}

// Updated renewal date calculation
currencyRenewalDate(lastEntry, member) {
  const currencyMode = this.getCurrencyMode(member);
  
  if (currencyMode === 'recurrent') {
    return this.calculateRecurrentCurrency(member.currency_recurrent_date, member);
  }
  
  // Existing standard logic
  return this.standardCurrencyRenewalDate(lastEntry, member);
}

3. Safety Training Service Updates

Files: api/src/features/account/safety-training/service/{instructor,trainer,coach,military}.service.js

Modify each service to track recurrent training (excluding flyer):

// Example for InstructorSafetyTrainingService
async saveInstructorMember() {
  const isNewEntry = !this.checkboxes.some(box => box.row_id);
  
  // Check if this is recurrent training (member was expired)
  const wasExpired = await this.checkMemberExpiredStatus(this.req.body.member_id);
  
  if (wasExpired) {
    // Mark as recurrent training
    await this.updateMemberField('currency_recurrent_date', Date.now() / 1000, this.req.body.member_id);
    await this.updateMemberField('currency_base_period', 'recurrent', this.req.body.member_id);
    
    // Add recurrent flag to params
    this.params.is_recurrent = 1;
    this.params.previous_expiry = wasExpired.expiry_date;
  }
  
  // Existing checkbox processing and approval level updates
  await this.updateMemberApprovalLevel();
  
  // Rest of existing logic...
}

// New method to check if member was expired
async checkMemberExpiredStatus(memberId) {
  const lastEntry = await this.safetyTrainingRepository.getLastCurrencyEntry(memberId);
  if (!lastEntry) return null;
  
  const expiryDate = this.currencyService.currencyRenewalDate(lastEntry, this.memberDetails);
  if (expiryDate === 'Expired') {
    return { expired: true, expiry_date: lastEntry.entry_date };
  }
  return null;
}

// Enhanced approval level update for recurrent training
async updateMemberApprovalLevel() {
  const newApprovalLevel = await this.getNewApprovalLevel();
  
  // For recurrent training, level may decrease based on checkbox completion
  if (this.memberDetails.currency_base_period === 'recurrent') {
    // Always update to the level achieved in recurrent training
    await this.updateMemberField('approval_level_instructor', newApprovalLevel, this.req.body.member_id);
  } else {
    // Standard logic - only increase levels
    if (newApprovalLevel > this.memberDetails.approval_level_instructor) {
      await this.updateMemberField('approval_level_instructor', newApprovalLevel, this.req.body.member_id);
    }
  }
  
  return { status: true };
}

4. Cron Job Updates

Files: api/src/features/shared/currency/service/{instructor,trainer,coach,military}.service.js

Update cron jobs to respect relative currency (excluding flyers):

// Example for InstructorService
async getCurrencyMembers(options = {}) {
  // Modified query to include recurrent currency members
  const members = await this.currencyRepository.getInstructorCurrencyMembersWithRecurrent();
  
  // Filter based on currency mode
  return members.filter(member => {
    if (member.currency_base_period === 'recurrent') {
      // Check relative to recurrent date
      return this.isExpiredRelativeToRecurrent(member);
    }
    // Standard 6-month check
    return this.isExpiredStandard(member);
  });
}

isExpiredRelativeToRecurrent(member) {
  const monthsSinceRecurrent = 
    (Date.now() - member.currency_recurrent_date * 1000) / (1000 * 60 * 60 * 24 * 30);
  return monthsSinceRecurrent > 6;
}

// Update deactivation logic
async makeMembersInactive(members) {
  for (const memberId of members) {
    // Standard deactivation
    await this.currencyRepository.deactivateMember(this.membersInactive.query, {member: memberId});
    
    // Clear recurrent status when deactivated
    await this.currencyRepository.updateMemberField('currency_base_period', 'standard', memberId);
    await this.currencyRepository.updateMemberField('currency_recurrent_date', null, memberId);
  }
}

5. Repository/Model Updates

File: api/src/features/shared/currency/model/index.js

Add queries to support recurrent tracking (for instructors, trainers, coaches, military):

static getInstructorCurrencyMembersWithRecurrent(options, membersId, table) {
  const reportAdditionalQuery = `${this.reportAdditionalQuery(table)} last_currency_training`;
  return `
    SELECT
      M.member_id,
      M.screen_name,
      M.currency_instructor,
      M.currency_recurrent_date,
      M.currency_base_period,
      M.currency_next_expiry,
      M.approval_level_instructor,
      -- existing fields...
    FROM members AS M
    -- existing joins...
    WHERE M.role_id IN (8,9,10,12)
    AND M.level_instructor NOT IN (${catLevels.level0}, ${catLevels.afc})
    AND (
      -- Standard currency check
      (M.currency_base_period = 'standard' OR M.currency_base_period IS NULL)
      OR 
      -- Recurrent currency check - expires 6 months from recurrent date
      (M.currency_base_period = 'recurrent' 
       AND M.currency_recurrent_date IS NOT NULL
       AND M.currency_recurrent_date + (6 * 30 * 24 * 60 * 60) < UNIX_TIMESTAMP())
    )
  `;
}

// Similar methods for trainer, coach, military with role-specific conditions

File: api/src/features/account/safety-training/model/index.js

Update checkbox queries to include recurrent tracking:

static getCurrencyCheckboxes(type, dateRange, includeTimezone = false) {
  const tableMap = {
    instructor: {
      checkbox: 'channel_currency_instructor_checkboxes',
      currency: 'channel_currency_instructor'
    },
    trainer: {
      checkbox: 'channel_currency_trainer_checkboxes',
      currency: 'channel_currency_trainer'
    },
    coach: {
      checkbox: 'channel_currency_coach_checkboxes',
      currency: 'channel_currency_coach'
    },
    military: {
      checkbox: 'channel_currency_military_checkboxes',
      currency: 'channel_currency_military'
    }
  };

  const tables = tableMap[type];
  if (!tables) throw new Error(`Invalid currency type: ${type}`);

  return `
    SELECT
        CCC.row_id,
        CCC.entry_id,
        CCC.entry_date,
        CCC.instructor,
        CCC.label,
        CCC.tunnel,
        CCC.passed,
        CCC.level,
        CC.is_recurrent,
        CC.previous_expiry
    FROM ${tables.checkbox} CCC
    JOIN ${tables.currency} CC ON CC.entry_id = CCC.entry_id
    WHERE CC.author_id = :member_id
    ${dateRange}
  `;
}

6. Checkbox Management for Recurrent Training

Default Checkbox Creation Logic: When a member starts recurrency, the system creates checkboxes based on their current level:

// In InstructorSafetyTrainingService
async getInstructorDefaultCheckboxes() {
  const { level_instructor, currency_instructor } = this.member;
  const instructorCheckboxData = _.cloneDeep(instructorCheckbox);

  const checkboxMap = {
    'status-active': currency_instructor === 1,
    'status-inactive': currency_instructor !== 1,
    'meeting': true,
    'level1': this._isLevelAllowed(level_instructor, [catLevels.instructorLevel1, ...]),
    'level2': this._isLevelAllowed(level_instructor, [catLevels.instructorLevel2, ...]),
    'level3': this._isLevelAllowed(level_instructor, [catLevels.instructorLevel3, ...]),
    'level4': this._isLevelAllowed(level_instructor, [catLevels.instructorLevel4])
  };

  return Object.entries(checkboxMap)
    .filter(([_, shouldInclude]) => shouldInclude)
    .map(([id]) => _.find(instructorCheckboxData, { id }))
    .filter(Boolean);
}

7. Migration Strategy

1. Phase 1: Deploy database schema changes
2. Phase 2: Deploy code changes with backward compatibility
3. Phase 3: Run migration script to identify and mark recurrent members
4. Phase 4: Monitor and adjust based on edge cases
5. Phase 5: Verify checkbox-based approval level calculations work correctly

8. Testing Requirements

• Unit tests for new currency calculation methods
• Integration tests for recurrent training flow with checkbox completion
• Test approval level updates based on checkbox completion
• Edge case testing for period boundaries
• Test checkbox creation for different member levels
• Migration testing on staging environment
• Performance testing for modified queries

9. Monitoring & Rollback Plan

• Add logging for currency mode decisions
• Track metrics on recurrent vs standard currency members
• Monitor approval level changes during recurrent training
• Prepare rollback scripts to revert database changes
• Document manual override procedures for edge cases

Key Implementation Considerations

Period Alignment Logic

When a member takes recurrent training, their new currency period should:

1. Start from the recurrent training date
2. Extend for 6 months from that date
3. Align to the next standard period boundary (Jan 16 or Jul 16) if within 30 days

Note: This does NOT apply to flyers - they maintain the existing activity-based currency system.

Checkbox-Based Level Determination

For instructors, trainers, coaches, and military members:

1. When recurrency starts, checkboxes are created based on current certification level
2. Completed checkboxes determine the new approval level via getNewApprovalLevel()
3. Member's approval level may decrease if they don't complete higher-level checkboxes
4. This ensures only competent members retain higher-level privileges

Edge Cases to Handle

• Member takes recurrent training just before a standard period boundary
• Member has multiple expired currencies (instructor + coach + trainer)
• Member transitions from recurrent back to standard currency
• Member fails to complete expected level checkboxes (level downgrade)
• Historical data migration for existing expired members
• Checkbox validation for different approval levels (instructor.service.js:92-120)

API Changes

New endpoints or modifications needed:

• GET /api/member/{id}/currency-status - Include recurrent status and approval levels
• POST /api/safety-training/{type}/recurrent - Mark training as recurrent for specific role
• GET /api/reports/currency - Filter by standard vs recurrent, show approval level changes
• GET /api/safety-training/{type}/checkboxes - Enhanced to show recurrent vs maintain checkboxes

UI/UX Considerations

• Display different expiry dates for standard vs recurrent members
• Show "Recurrent Currency" badge or indicator
• Display current vs new approval levels during recurrency training
• Checkbox interface showing which levels member is eligible for
• Clear messaging about potential level downgrades
• Admin tools to manually adjust currency mode and approval levels if needed

Role-Specific Considerations

Instructors:

• Level-based checkboxes (1-4) determine approval level
• Validation rules prevent unauthorized level assignments
• May require reactivation of flyer currency if inactive

Trainers:

• Maintain vs Regain checkbox sets
• Supervision requirements for regain
• Integration with instructor currency requirements

Coaches:

• Coach spotter skill requirements
• Maintain vs Regain based on activity
• Session count requirements

Military:

• Military-specific skills validation
• Military flag requirement
• Separate checkbox set for military instruction

Flyers:

• EXCLUDED from this system
• Continue using existing activity-based 6-month currency
• No checkbox-based training requirements