-- ============================================================
-- Migration: Fix MSG91 SMS Templates Merge Fields
-- Purpose: Update empty merge_field_map for MSG91 SMS templates
--          to use dynamic merge info instead of hardcoded parameters
-- Date: 2026-04-09
-- ============================================================

-- 1. Update HDB_RM_REGISTRATION_NOTIFY_SMS template
-- Template 31: Registration completion notification to RM
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "var1", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_RM_REGISTRATION_NOTIFY_SMS'
  AND template_type = 'sms'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

-- 2. Update HDB_RM_BLESS_NOTIFY_SMS template
-- Template 32: Blessed notification to RM with 4 parameters
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "var1", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
    {"keyName": "var2", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "var3", "sourceTable": "computed", "sourceColumn": "getUserFormattedPhone", "dataType": "string", "isNullable": false},
    {"keyName": "var4", "sourceTable": "computed", "sourceColumn": "formatAllocatedProgramDateRange", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_RM_BLESS_NOTIFY_SMS'
  AND template_type = 'sms'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

-- ============================================================
-- Verification Queries
-- ============================================================

-- Verify template 31 merge fields
SELECT 
    id,
    template_key,
    template_name,
    template_type,
    jsonb_array_length(merge_field_map) AS field_count,
    jsonb_pretty(merge_field_map) AS merge_fields
FROM communication_templates_master
WHERE template_key = 'HDB_RM_REGISTRATION_NOTIFY_SMS'
  AND template_type = 'sms';

-- Verify template 32 merge fields
SELECT 
    id,
    template_key,
    template_name,
    template_type,
    jsonb_array_length(merge_field_map) AS field_count,
    jsonb_pretty(merge_field_map) AS merge_fields
FROM communication_templates_master
WHERE template_key = 'HDB_RM_BLESS_NOTIFY_SMS'
  AND template_type = 'sms';

-- ============================================================
-- Notes:
-- ============================================================
-- This migration converts hardcoded MSG91 SMS parameters to dynamic merge info.
-- 
-- Before:
-- - Registration SMS: { var1: registrationName } (hardcoded)
-- - Bless SMS: { var1: rmName, var2: registrationName, var3: userPhone, var4: programDates } (hardcoded)
--
-- After:
-- - Registration SMS: var1 dynamically fetched from hdb_program_registration.full_name
-- - Bless SMS: 
--   * var1: RM name (computed via getRmContactUserName)
--   * var2: Registration name (from hdb_program_registration.full_name)
--   * var3: User phone (computed via getUserFormattedPhone)
--   * var4: Program date range (computed via formatAllocatedProgramDateRange)
--
-- Benefits:
-- 1. No more hardcoded parameters in service code
-- 2. Consistent with WhatsApp/Email merge info approach
-- 3. Easy to modify fields via database without code changes
-- 4. Supports future template variations
-- ============================================================
