-- Add is_per_recipient column to merge_info_answer_location_map table
-- This column marks fields that need per-recipient computation (e.g., coordinator_name for multiple coordinators)
-- When isPerRecipient=true, the field value should be resolved from extraMergeContext at send time

ALTER TABLE merge_info_answer_location_map 
ADD COLUMN IF NOT EXISTS is_per_recipient BOOLEAN DEFAULT FALSE;

COMMENT ON COLUMN merge_info_answer_location_map.is_per_recipient IS 
'Indicates if this merge field needs per-recipient computation. When true, value is resolved from extraMergeContext.perRecipient at send time';

-- Migration: Fix Payment Reminder Template Merge Fields
-- Date: 2026-04-09
-- Description: Add merge fields for HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP template
--              to match the old scheduler service implementation

-- ============================================================
-- Update HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP merge fields
-- ============================================================

-- Update the template to include rm_name and reg_count merge fields
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "rm_name", "sourceTable": "context", "sourceColumn": "extraMergeContext:rm_name", "dataType": "string", "isNullable": false},
    {"keyName": "reg_count", "sourceTable": "computed", "sourceColumn": "countPendingPayments", "dataType": "number", "isNullable": false}
]'::jsonb,
updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP'
AND template_type = 'whatsapp'
AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

-- Verify the update
SELECT 
    id,
    template_key,
    template_name,
    template_type,
    template_id,
    template_access_key,
    merge_field_map,
    updated_at
FROM communication_templates_master
WHERE template_key = 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP'
AND template_type = 'whatsapp';

