# Registration Module - Template Access Key Implementation

## 📋 Summary

Successfully implemented the template access key approach for the registration module to replace hardcoded environment variables with dynamic template fetching based on program ID and template access keys.

---

## 🎯 What Was Implemented

### 1. Helper Method for Template Fetching

Added a centralized helper method `getTemplateByAccessKey()` in `RegistrationService` that:
- Fetches templates dynamically using `programId` and `templateAccessKey`
- **NO fallback to environment variables** - fully database-driven
- Logs errors when templates are not configured for a program
- Returns `null` if template not found
- Supports both EMAIL and WHATSAPP communication types

**Location:** `/src/registration/registration.service.ts`

```typescript
private async getTemplateByAccessKey(
  programId: number,
  templateAccessKey: CommunicationTemplateAccessKeyEnum,
  communicationType: CommunicationTypeEnum,
): Promise<string | null>
```

### 2. Dependency Injection

- Added `CommunicationTemplatesRepository` to the constructor of `RegistrationService`
- Imported required enums: `CommunicationTemplateAccessKeyEnum` and `CommunicationTypeEnum`

### 3. Updated Communication Methods

#### 3.1 Registration Completion Communication (register method)
- **Email:** `CommunicationTemplateAccessKeyEnum.REGISTRATION_COMPLETED`
- **WhatsApp (Seeker):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_COMPLETED`
- **WhatsApp (RM):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_COMPLETED`
- Templates must be configured in database; no env variable fallback

#### 3.2 Registration Completion Communication (update method)
- Same as above, implemented in the update flow when registration status changes from DRAFT to COMPLETED
- Templates must be configured in database; no env variable fallback

#### 3.3 Preference Edited Communication (update method)
- **WhatsApp (RM):** `CommunicationTemplateAccessKeyEnum.PREFERENCE_EDITED`
- Templates must be configured in database; no env variable fallback

#### 3.4 Registration Cancellation Communication
- **Email (Seeker):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- **WhatsApp (Seeker):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- **Email (RM):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- **WhatsApp (RM):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- **Email (Finance):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- **WhatsApp (Finance):** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- Templates must be configured in database; no env variable fallback

#### 3.5 Refund Communication
- **Email:** `CommunicationTemplateAccessKeyEnum.REGISTRATION_REFUND`
- Templates must be configured in database; no env variable fallback
- Sends to multiple finance managers using bulk email

#### 3.6 Registration Cancel Communication (Alternate method)
- **Email:** `CommunicationTemplateAccessKeyEnum.REGISTRATION_CANCELLED`
- Templates must be configured in database; no env variable fallback

---

## 🔄 How It Works

### Flow Diagram

```
┌─────────────────────────────────────────┐
│  Registration Service Method Call       │
│  (register/update/cancel/refund)        │
└──────────────────┬──────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────┐
│  getTemplateByAccessKey(                │
│    programId,                           │
│    templateAccessKey,                   │
│    communicationType                    │
│  )                                      │
└──────────────────┬──────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────┐
│  CommunicationTemplatesRepository       │
│  .findByProgramAndAccessKey()           │
└──────────────────┬──────────────────────┘
                   │
        ┌──────────┴──────────┐
        │                     │
        ▼                     ▼
┌──────────────┐    ┌─────────────────┐
│ Template     │    │ Template NOT    │
│ Found        │    │ Found           │
└──────┬───────┘    └────────┬────────┘
       │                     │
       │                     ▼
       │            ┌─────────────────┐
       │            │ Log Error       │
       │            │ Return NULL     │
       │            └────────┬────────┘
       │                     │
       └─────────┬───────────┘
                 │
                 ▼
┌─────────────────────────────────────────┐
│  Check if templateKey exists            │
│  Skip communication if NULL             │
└──────────────────┬──────────────────────┘
                   │
                   ▼ (if not null)
┌─────────────────────────────────────────┐
│  Prepare Email/WhatsApp Data            │
│  with merge info                        │
└──────────────────┬──────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────┐
│  Send Communication                     │
│  (Email/WhatsApp)                       │
└─────────────────────────────────────────┘
```

### Merge Info Pattern (Already Implemented)

The merge info preparation follows the existing pattern in bulk email:
1. Fetch merge field mappings from `MergeInfoAnswerLocationMap` by templateId
2. Separate common fields (same for all recipients) and per-record fields
3. Use `DataFormatter.format()` to format values based on dataType and formatType
4. Build mergeInfo object for each recipient

**Example from existing code:**
```typescript
const preparedData = await this.prepareRecipientsWithMergeFields(recipients, templateId);
```

---

## 🔑 Template Access Keys Used

| Access Key                          | Purpose                                    | Communication Types |
|-------------------------------------|--------------------------------------------|---------------------|
| `REGISTRATION_COMPLETED`            | Registration completion notification       | Email, WhatsApp     |
| `REGISTRATION_CANCELLED`            | Registration cancellation notification     | Email, WhatsApp     |
| `REGISTRATION_REFUND`               | Refund request notification               | Email               |
| `PREFERENCE_EDITED`                 | Program preference change notification    | WhatsApp            |

---

## ✅ Benefits

1. **Fully Dynamic & Database-Driven:** All templates are fetched from database based on program configuration
2. **No Environment Variable Dependencies:** Eliminates reliance on hardcoded env variables
3. **Clear Error Visibility:** Logs errors when templates are missing, making configuration issues obvious
4. **Program-Specific Templates:** Different programs can use completely different templates
5. **Centralized Logic:** Single helper method for all template fetching reduces code duplication
6. **Merge Info Integration:** Works seamlessly with existing merge field system
7. **Future-Proof:** Easy to add new template access keys without changing core logic
8. **Failsafe Design:** Gracefully skips communication if template not found (no email/WhatsApp sent)

---

## 🧪 Testing Checklist

- [ ] ✅ Test registration completion with templates configured in DB
- [ ] ⚠️ Test registration completion WITHOUT templates (should log error and skip)
- [ ] ✅ Test registration cancellation for seeker, RM, and finance
- [ ] ⚠️ Test cancellation without templates (should log error and skip)
- [ ] ✅ Test refund communication to finance managers
- [ ] ✅ Test preference edited notification to RM
- [ ] ✅ Test with different program configurations
- [ ] ✅ Verify merge info is correctly prepared from database
- [ ] ✅ Verify communication tracking is working
- [ ] ✅ Test both email and WhatsApp communications
- [ ] ✅ Test queued vs direct sending modes
- [ ] 🔥 Verify error logs appear when templates are missing
- [ ] 🔥 Confirm no communications sent when template is NULL

---

## 📝 Files Modified

1. `/src/registration/registration.service.ts`
   - Added `getTemplateByAccessKey()` helper method
   - Updated `register()` method
   - Updated `update()` method
   - Updated `sendCancellationNotifications()` method
   - Updated `sendRefundCommunication()` method
   - Updated `sendRegistrationCancelCommunication()` method
   - Added `CommunicationTemplatesRepository` dependency injection

---

## 🚀 Next Steps

To extend this pattern to other modules (invoice, payment, approval, etc.):

1. **Inject Dependencies:**
   ```typescript
   constructor(
     // ... existing dependencies
     private readonly communicationTemplatesRepository: CommunicationTemplatesRepository,
   ) {}
   ```

2. **Copy Helper Method:**
   Copy the `getTemplateByAccessKey()` method to the service

3. **Replace Environment Variables:**
   Replace all `process.env.ZEPTO_*` and `process.env.WATI_*` with:
   ```typescript
   const templateKey = await this.getTemplateByAccessKey(
     programId,
     CommunicationTemplateAccessKeyEnum.YOUR_ACCESS_KEY,
     CommunicationTypeEnum.EMAIL, // or WHATSAPP
   );
   ```

4. **Use Template Key:**
   ```typescript
   if (templateKey) {
     const emailData: SendSingleEmailDto = {
       templateKey: templateKey,
       // ... rest of the data
     };
     await this.communicationService.sendSingleEmail(emailData);
   }
   ```

   # Merge Fields Comparison Report
## Template Merge Fields: JSON vs Database SQL

Generated: April 6, 2026

---

## Executive Summary

This report compares merge fields defined in two sources:
- **Source 1 (JSON)**: `/database/template-merge-infos.json`
- **Source 2 (SQL)**: `/database/migrations/2026-04-02-communication-templates-seed-complete-v2.sql`

### Key Findings:
1. **Zepto Email Templates**: Significant discrepancies - JSON has much more comprehensive field definitions
2. **WATI WhatsApp Templates**: Multiple templates missing from JSON
3. **MSG91 SMS Templates**: Minimal field definitions in both sources

---

## 📧 ZEPTO EMAIL TEMPLATES

### 1. GENERIC_EMAIL
- **JSON Fields**: `[]` (empty)
- **Database Fields**: `[]` (empty)
- **Status**: ✅ **MATCH**

---

### 2. HDB_BLESSED_EMAIL
**JSON Fields (13 fields):**
- `venue_name`, `s_day`, `hdb_price`, `payment_last_date`, `cash_details`, `e_day`, `reg_name`, `hdb_or_msd`, `hdb_dates`, `hdb_no`, `payment_online_link`, `payment_back_transfer_link`, `payment_cheque_link`

**Database Fields (5 fields):**
- `reg_fullname`, `hdb_msd`, `s_date`, `e_date`, `email_address`

**Differences:**
- ❌ **JSON has 13 fields vs DB has 5 fields**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **JSON has payment links missing in DB**: `payment_online_link`, `payment_back_transfer_link`, `payment_cheque_link`
- ❌ **JSON has venue/price info missing in DB**: `venue_name`, `hdb_price`, `payment_last_date`, `cash_details`
- ❌ **DB has `email_address` missing in JSON**

**Recommendation**: ⚠️ **Use JSON** - More comprehensive with payment links and venue details

---

### 3. HDB_REGISTRATION_COMPLETED_EMAIL / REGISTRATION_COMPLETED_EMAIL
**JSON Fields (2 fields):**
- `reg_name`, `reg_edit_link`

**Database Fields (4 fields):**
- `reg_fullname`, `hdb_msd`, `s_date`, `e_date`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **JSON has `reg_edit_link` missing in DB**
- ❌ **DB has program details missing in JSON**: `hdb_msd`, `s_date`, `e_date`

**Recommendation**: ⚠️ **MERGE BOTH** - JSON has edit link, DB has program details

---

### 4. HDB_INVOICE_EMAIL / INVOICE_EMAIL
**JSON Fields (6 fields):**
- `hdb_msd_no`, `hdb_msd_date`, `hdb_msd_amount`, `reg_name`, `hdb_msd`, `last_date`

**Database Fields (4 fields):**
- `reg_fullname`, `invoice_number`, `invoice_amount`, `invoice_date`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **Field name mismatch**: JSON uses `hdb_msd_amount` while DB uses `invoice_amount`
- ❌ **DB has `invoice_number`, `invoice_date` missing in JSON**
- ❌ **JSON has `hdb_msd_no`, `hdb_msd_date`, `hdb_msd`, `last_date` missing in DB**

**Recommendation**: ⚠️ **Use DB** - Has proper invoice fields (`invoice_number`, `invoice_amount`, `invoice_date`)

---

### 5. HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL / PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL
**JSON Fields (5 fields):**
- `pay_date`, `pay_amount`, `reg_name`, `pay_method`, `hdb_msd`

**Database Fields (3 fields):**
- `reg_fullname`, `payment_amount`, `payment_mode`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **Field name mismatch**: JSON uses `pay_amount` while DB uses `payment_amount`
- ❌ **Field name mismatch**: JSON uses `pay_method` while DB uses `payment_mode`
- ❌ **JSON has `pay_date` and `hdb_msd` missing in DB**

**Recommendation**: ⚠️ **Use JSON** - Has payment date and program name, more comprehensive

---

### 6. HDB_HOLD_EMAIL / HOLD_EMAIL
**JSON Fields (3 fields):**
- `reg_name`, `hdb_msd`, `last_allocated_hdb_msd`

**Database Fields (2 fields):**
- `reg_fullname`, `hdb_msd`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **JSON has `last_allocated_hdb_msd` missing in DB**

**Recommendation**: ⚠️ **Use JSON** - Has last allocated program information

---

### 7. OTP_EMAIL
**JSON Fields (2 fields):**
- `otp`, `userName`

**Database Fields (2 fields):**
- `otp_code`, `user_name`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `otp` while DB uses `otp_code`
- ❌ **Field name mismatch**: JSON uses `userName` (camelCase) while DB uses `user_name` (snake_case)

**Recommendation**: ⚠️ **Standardize to DB** - Use consistent naming: `otp_code`, `user_name`

---

### 8. HDB_BLESSED_NO_PAYMENT_EMAIL / BLESSED_NO_PAYMENT_HDB_EMAIL
**JSON Fields (7 fields):**
- `venue_name`, `s_day`, `e_day`, `reg_name`, `hdb_or_msd`, `hdb_dates`, `hdb_no`

**Database Fields (3 fields):**
- `reg_fullname`, `hdb_msd`, `s_date`

**Differences:**
- ❌ **JSON has 7 fields vs DB has 3 fields**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **JSON has venue and date info missing in DB**: `venue_name`, `s_day`, `e_day`, `hdb_dates`, `hdb_no`
- ❌ **DB missing `e_date` (end date)**

**Recommendation**: ⚠️ **Use JSON** - More comprehensive with venue and complete date range

---

### 9. HDB_RM_HOLD_EMAIL / RM_HOLD_EMAIL
**JSON Fields (4 fields):**
- `reg_name`, `reg_id`, `rm_name`, `hdb_msd`

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **JSON has `reg_id` and `hdb_msd` missing in DB**

**Recommendation**: ⚠️ **Use JSON** - Has registration ID and program name

---

### 10. HDB_REGISTRATION_REFUND_EMAIL / REGISTRATION_REFUND_EMAIL
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `reg_fullname`, `refund_amount`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 11. HDB_BILLING_DETAILS_EDIT_EMAIL / BILLING_DETAILS_EDIT_EMAIL
**JSON Fields (4 fields):**
- `reg_name`, `hdb_msd_no`, `hdb_msd_date`, `billing_edit_link`

**Database Fields (3 fields):**
- `reg_fullname`, `invoice_name`, `invoice_address`

**Differences:**
- ❌ **Completely different field sets**
- ❌ **JSON has program info**: `hdb_msd_no`, `hdb_msd_date`, `billing_edit_link`
- ❌ **DB has invoice details**: `invoice_name`, `invoice_address`

**Recommendation**: ⚠️ **MERGE BOTH** - Both have different critical information

---

### 12. HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL / BILLING_DETAILS_EDIT_REMOVED_EMAIL
**JSON Fields (3 fields):**
- `reg_name`, `hdb_msd_no`, `hdb_msd_date`

**Database Fields (1 field):**
- `reg_fullname`

**Differences:**
- ❌ **JSON has program details missing in DB**: `hdb_msd_no`, `hdb_msd_date`

**Recommendation**: ⚠️ **Use JSON** - Has program context information

---

### 13. HDB_CANCEL_EMAIL / CANCEL_EMAIL
**JSON Fields**: `[]` (empty)

**Database Fields (3 fields):**
- `reg_fullname`, `cancellation_reason`, `hdb_msd`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 14. HDB_EINVOICE_ERROR_EMAIL / EINVOICE_ERROR_EMAIL
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `reg_fullname`, `error_message`

**Differences:**
- ❌ **DB has fields while JSON is empty**
- ⚠️ **JSON has templateId as "unknown"**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete and template ID unknown

---

### 15. HDB_SWAP_DEMAND_EMAIL / SWAP_DEMAND_TEMPLATE
**JSON Fields (2 fields):**
- `reg_name`, `hdb_msd`

**Database Fields (2 fields):**
- `reg_fullname`, `current_program`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `reg_name` while DB uses `reg_fullname`
- ❌ **Field name mismatch**: JSON uses `hdb_msd` while DB uses `current_program`

**Recommendation**: ✅ **Use DB** - More descriptive field name `current_program`

---

## 💬 WATI WHATSAPP TEMPLATES

### 16. HDB_BLESSED_NO_PAYMENT_WHATSAPP / BLESSED_NO_PAYMENT_HDB
**JSON Fields (4 fields):**
- `reg_name`, `hdb_msd`, `s_date`, `e_date`

**Database Fields (2 fields):**
- `reg_fullname`, `hdb_msd`

**Differences:**
- ❌ **JSON has date fields missing in DB**: `s_date`, `e_date`

**Recommendation**: ⚠️ **Use JSON** - Has program dates

---

### 17. HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP / SEEKER_REGISTRATION_COMPLETED
**JSON Fields (1 field):**
- `reg_name`

**Database Fields (2 fields):**
- `reg_fullname`, `hdb_msd`

**Differences:**
- ❌ **DB has `hdb_msd` missing in JSON**

**Recommendation**: ⚠️ **Use DB** - Has program name

---

### 18. HDB_RM_REGISTRATION_COMPLETED_WHATSAPP / RM_REGISTRATION_COMPLETED
**JSON Fields (1 field):**
- `reg_name`

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **DB has `rm_name` missing in JSON**
- ⚠️ **JSON note**: "Not integrated because the template is getting opened in WATI"

**Recommendation**: ⚠️ **Use DB** - Has RM name (critical for RM notifications)

---

### 19. HDB_BLESSED_WHATSAPP / BLESSED_HDB
**JSON Fields (4 fields):**
- `reg_name`, `hbd_msd_variable`, `s_date`, `e_date`

**Database Fields (3 fields):**
- `reg_fullname`, `hdb_msd`, `s_date`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `hbd_msd_variable` (typo?) while DB uses `hdb_msd`
- ❌ **DB missing `e_date` present in JSON**

**Recommendation**: ⚠️ **Use JSON** - Has complete date range, but fix typo `hbd_msd_variable` → `hdb_msd`

---

### 20. HDB_INVOICE_WHATSAPP / INVOICE
**JSON Fields (4 fields):**
- `reg_name`, `hbd_msd_variable`, `s_date`, `e_date`

**Database Fields (2 fields):**
- `reg_fullname`, `invoice_amount`

**Differences:**
- ❌ **JSON has program details missing in DB**: `hbd_msd_variable`, `s_date`, `e_date`
- ❌ **DB has `invoice_amount` missing in JSON**

**Recommendation**: ⚠️ **MERGE BOTH** - Need invoice amount AND program details

---

### 21. HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP / PAYMENT_ACKNOWLEDGEMENT_OFFLINE
**JSON Fields (1 field):**
- `reg_name`

**Database Fields (2 fields):**
- `reg_fullname`, `payment_amount`

**Differences:**
- ❌ **DB has `payment_amount` missing in JSON**

**Recommendation**: ⚠️ **Use DB** - Payment amount is critical

---

### 22. HDB_BLESSED_RM_WHATSAPP / BLESSED_HDB_RM
**JSON Fields (6 fields):**
- `reg_name`, `rm_name`, `reg_mobile`, `hdb_msd_variable`, `s_date`, `e_date`

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **JSON has many fields missing in DB**: `reg_mobile`, `hdb_msd_variable`, `s_date`, `e_date`

**Recommendation**: ⚠️ **Use JSON** - Much more comprehensive

---

### 23. HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP / CO_ORDINATOR_TRAVEL_PLAN_CHANGE
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `coordinator_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 24. HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP / ADMINS_MESSAGE_NOTIFICATION
**JSON Fields (2 fields):**
- `admin_name`, `sender_admin_name`

**Database Fields (1 field):**
- `admin_message`

**Differences:**
- ❌ **Completely different fields**
- ❌ **JSON has admin names, DB has message content**

**Recommendation**: ⚠️ **INVESTIGATE** - These should likely be merged

---

### 25. HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP / RM_PAYMENT_PENDING_REMINDER_LIST
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `rm_name`, `pending_count`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 26. HDB_RM_PREFERENCE_EDITED_WHATSAPP / RM_PREFERENCE_EDITED
**JSON Fields (5 fields):**
- `rm_name`, `reg_name`, `reg_id`, `old_pref`, `new_pref`

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **JSON has preference details missing in DB**: `reg_id`, `old_pref`, `new_pref`

**Recommendation**: ⚠️ **Use JSON** - Has preference change tracking

---

### 27. HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP / CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `coordinator_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 28. HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP / CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `coordinator_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 29. HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP / CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `coordinator_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 30. HDB_SWAP_DEMAND_WHATSAPP / SWAP_DEMAND
**JSON Fields (2 fields):**
- `reg_name`, `hdb_msd`

**Database Fields (2 fields):**
- `reg_fullname`, `current_program`

**Differences:**
- ❌ **Field name mismatch**: JSON uses `hdb_msd` while DB uses `current_program`

**Recommendation**: ✅ **Use DB** - More descriptive field name

---

## 📱 MSG91 SMS TEMPLATES

### 31. HDB_RM_REGISTRATION_NOTIFY_SMS / RM_REG_NOTIFY
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 32. HDB_RM_BLESS_NOTIFY_SMS / RM_BLESS_NOTIFY
**JSON Fields**: `[]` (empty)

**Database Fields (2 fields):**
- `rm_name`, `reg_fullname`

**Differences:**
- ❌ **DB has fields while JSON is empty**

**Recommendation**: ⚠️ **Use DB** - JSON is incomplete

---

### 33. OTP_SMS / OTP
**JSON Fields (1 field):**
- `var1`

**Database Fields (1 field):**
- `otp_code`

**Differences:**
- ❌ **Field name mismatch**: JSON uses generic `var1` while DB uses descriptive `otp_code`

**Recommendation**: ⚠️ **Use DB** - More descriptive naming

---

## 📊 SUMMARY STATISTICS

### By Source Completeness:

| Template Type | Total Templates | JSON More Complete | DB More Complete | Both Equal | Need Merge |
|---------------|----------------|-------------------|------------------|------------|------------|
| **Zepto Email** | 15 | 8 | 4 | 1 | 2 |
| **WATI WhatsApp** | 15 | 4 | 8 | 0 | 3 |
| **MSG91 SMS** | 3 | 0 | 3 | 0 | 0 |
| **TOTAL** | **33** | **12** | **15** | **1** | **5** |

### Critical Issues:

1. **Field Naming Inconsistency**: 
   - JSON predominantly uses `reg_name`
   - DB consistently uses `reg_fullname`
   - **Recommendation**: Standardize to `reg_fullname`

2. **Empty Merge Fields in JSON**:
   - `REGISTRATION_REFUND_EMAIL`
   - `CANCEL_EMAIL`
   - `EINVOICE_ERROR_EMAIL`
   - All coordinator travel plan templates
   - All MSG91 RM notify templates

3. **Empty Merge Fields in Database**:
   - `GENERIC_EMAIL` (intentional - both empty)

4. **Templates Requiring Immediate Attention**:
   - `HDB_BILLING_DETAILS_EDIT_EMAIL` - Completely different fields
   - `HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP` - Different fields
   - `HDB_INVOICE_WHATSAPP` - Need to merge both sources

---

## 🎯 RECOMMENDATIONS

### Priority 1: Critical Data Issues
1. **Update JSON** with missing templates for coordinator travel plans and MSG91
2. **Standardize field naming**: Use `reg_fullname` everywhere
3. **Fix typo**: `hbd_msd_variable` → `hdb_msd`

### Priority 2: Data Completeness
1. **Merge fields for**:
   - `HDB_REGISTRATION_COMPLETED_EMAIL`
   - `HDB_BILLING_DETAILS_EDIT_EMAIL`
   - `HDB_INVOICE_WHATSAPP`
   - `HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP`

### Priority 3: Use Database as Source of Truth
For most templates, the **Database SQL** has more current and accurate data, especially for:
- Recently added templates (travel coordinator, admin notifications)
- Templates with proper field naming conventions
- Templates with essential business fields (amounts, dates, IDs)

### Priority 4: Use JSON for Enhanced Fields
JSON has better data for:
- Payment links and URLs
- Venue information
- Complete date ranges (start + end dates)
- Preference tracking fields

---

## 🔍 FIELD NAMING STANDARDIZATION NEEDED

| JSON Field | Database Field | Recommended Standard |
|------------|---------------|---------------------|
| `reg_name` | `reg_fullname` | `reg_fullname` |
| `otp` | `otp_code` | `otp_code` |
| `userName` | `user_name` | `user_name` |
| `pay_amount` | `payment_amount` | `payment_amount` |
| `pay_method` | `payment_mode` | `payment_mode` |
| `hbd_msd_variable` | `hdb_msd` | `hdb_msd` |
| `current_program` | `hdb_msd` | Depends on context |

---

## ✅ ACTION ITEMS

1. ✅ **Immediate**: Fix JSON empty arrays for 9 templates
2. ✅ **Immediate**: Standardize all `reg_name` → `reg_fullname`
3. ✅ **Immediate**: Fix typo `hbd_msd_variable` → `hdb_msd`
4. ⚠️ **High Priority**: Merge conflicting templates (5 templates)
5. ⚠️ **High Priority**: Validate email templates have payment links
6. ⚠️ **Medium Priority**: Add venue/date fields to database where JSON has them

---

**Report End**


---

## 📚 Related Files

- **Template Repository:** `/src/communication/repositories/communication-templates.repository.ts`
- **Template Enum:** `/src/common/enum/communication-template-access-key.enum.ts`
- **Seed Data:** `/database/migrations/2026-04-02-communication-templates-seed-complete-v2.sql`
- **Implementation Guide:** `/TEMPLATE_ACCESS_KEY_IMPLEMENTATION.md`

---

## 🎯 Status

✅ **COMPLETED** - Registration module fully migrated to database-driven template system:
- ✅ No environment variable dependencies
- ✅ Dynamic template fetching by program ID and access key
- ✅ Merge info prepared dynamically from database
- ✅ Clear error logging when templates are missing
- ✅ Graceful handling when templates not configured