-- Migration: Fix Allocated Program Field References
-- Date: 2026-04-09
-- Description: Updates templates to use getAllocatedProgramField instead of getProgramField
--              for blessed/allocated/payment scenarios

-- ============================================================
-- AFFECTED TEMPLATES:
-- 1. Payment Acknowledgement Offline Email (Template #5)
-- 2. RM Hold Email (Template #9)
-- 3. Billing Details Edit Email (Template #11)
-- 4. Billing Details Edit Removed Email (Template #12)
-- 5. Swap Demand Email (Template #15)
-- 6. Swap Demand WhatsApp (Template #30)
-- 7. Payment Pending Reminder WhatsApp (Template #34)
-- ============================================================

BEGIN;

-- 1. Payment Acknowledgement Offline Email
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "pay_date", "sourceTable": "computed", "sourceColumn": "currentDate", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
    {"keyName": "pay_amount", "sourceTable": "computed", "sourceColumn": "calculateTotalPaymentAmount", "dataType": "number", "formatType": "currency", "isNullable": false},
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": false},
    {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'
  AND template_type = 'email';

-- 2. RM Hold Email
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "reg_id", "sourceTable": "hdb_program_registration", "sourceColumn": "id", "dataType": "number", "isNullable": false},
    {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_RM_HOLD_EMAIL'
  AND template_type = 'email';

-- 3. Billing Details Edit Email
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd_no", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd_date", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
    {"keyName": "billing_edit_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_BILLING_DETAILS_EDIT_EMAIL'
  AND template_type = 'email';

-- 4. Billing Details Edit Removed Email
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd_no", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd_date", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL'
  AND template_type = 'email';

-- 5. Swap Demand Email
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_SWAP_DEMAND_EMAIL'
  AND template_type = 'email';

-- 6. Swap Demand WhatsApp
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
    {"keyName": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_SWAP_DEMAND_WHATSAPP'
  AND template_type = 'whatsapp';

-- 7. Payment Pending Reminder WhatsApp
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
    {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false, "isCommon": true},
    {"keyName": "pending_count", "sourceTable": "computed", "sourceColumn": "countPendingPayments", "dataType": "number", "formatType": "integer", "isNullable": false},
    {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false, "isCommon": true}
]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP'
  AND template_type = 'whatsapp';

COMMIT;

-- ============================================================
-- VERIFICATION QUERIES
-- ============================================================

-- Verify all 7 templates were updated correctly
SELECT 
    template_key, 
    template_name, 
    template_type,
    merge_field_map
FROM communication_templates_master
WHERE template_key IN (
    'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL',
    'HDB_RM_HOLD_EMAIL',
    'HDB_BILLING_DETAILS_EDIT_EMAIL',
    'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL',
    'HDB_SWAP_DEMAND_EMAIL',
    'HDB_SWAP_DEMAND_WHATSAPP',
    'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP'
)
ORDER BY template_key;

-- Check for any remaining templates using getProgramField
SELECT 
    template_key, 
    template_name, 
    template_type,
    merge_field_map
FROM communication_templates_master
WHERE merge_field_map::text LIKE '%getProgramField%'
ORDER BY template_key;

-- ============================================================
-- SUMMARY OF CHANGES
-- ============================================================
/*
BEFORE:
All 7 templates were using getProgramField:name which refers to the 
registration's CURRENT program (program_id column), which is the program
they initially registered for.

AFTER:
All 7 templates now use getAllocatedProgramField:name which refers to the
registration's ALLOCATED program (allocated_program_id column), which is the
program they were blessed/assigned to after approval.

WHY THIS MATTERS:
- Payment acknowledgements: Users pay for their ALLOCATED program
- Hold emails: Shows which program they were allocated to before being put on hold
- Billing details: Invoice billing is for the ALLOCATED program
- Swap demands: Users request to swap FROM their allocated program TO another
- Payment reminders: Reminders are about paying for the ALLOCATED program

CONSISTENCY:
This brings these templates in line with:
- Blessed Email (template #2)
- Invoice Email (template #4)
- Hold Email (template #6)
- Blessed No Payment Email (template #8)
- All blessed WhatsApp templates
All of which correctly use getAllocatedProgramField for allocated program details.
*/
