-- ============================================================
-- Migration: Fix Coordinator Travel Plan Templates Merge Fields
-- Date: 2026-04-09
-- Description: Add proper merge field mappings for coordinator travel plan WhatsApp templates
-- ============================================================

-- Update Co-ordinator Travel Plan Change WhatsApp Template (General/Mode Change)
-- Note: coordinator_name has isPerRecipient=true, will be computed per recipient in the sending loop
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "coordinator_name", "sourceTable": "context", "sourceColumn": "perRecipient:coordinatorInfo", "dataType": "string", "isNullable": false, "isPerRecipient": true},
    {"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}
]'::jsonb,
updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

COMMENT ON COLUMN communication_templates_master.merge_field_map IS 'Merge field mappings. Fields with isPerRecipient=true are computed individually for each recipient using context data.';

-- Update Co-ordinator Return Travel Plan Change WhatsApp Template (Flight specific)
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "coordinator_name", "sourceTable": "context", "sourceColumn": "perRecipient:coordinatorInfo", "dataType": "string", "isNullable": false, "isPerRecipient": true},
    {"keyName": "o_return_airline", "sourceTable": "context", "sourceColumn": "flightChange:oldDepartureAirline", "dataType": "string", "isNullable": true},
    {"keyName": "o_return_fligh", "sourceTable": "context", "sourceColumn": "flightChange:oldDepartureFlight", "dataType": "string", "isNullable": true},
    {"keyName": "o_return_time", "sourceTable": "context", "sourceColumn": "flightChange:oldDepartureTime", "dataType": "string", "isNullable": true},
    {"keyName": "n_return_airline", "sourceTable": "context", "sourceColumn": "flightChange:newDepartureAirline", "dataType": "string", "isNullable": true},
    {"keyName": "n_return_fligh", "sourceTable": "context", "sourceColumn": "flightChange:newDepartureFlight", "dataType": "string", "isNullable": true},
    {"keyName": "n_return_time", "sourceTable": "context", "sourceColumn": "flightChange:newDepartureTime", "dataType": "string", "isNullable": true},
    {"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": true},
    {"keyName": "login_user", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false}
]'::jsonb,
updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

-- Update Co-ordinator Onward Travel Plan Change WhatsApp Template (Flight specific)
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "coordinator_name", "sourceTable": "context", "sourceColumn": "perRecipient:coordinatorInfo", "dataType": "string", "isNullable": false, "isPerRecipient": true},
    {"keyName": "o_onward_airline", "sourceTable": "context", "sourceColumn": "flightChange:oldArrivalAirline", "dataType": "string", "isNullable": true},
    {"keyName": "o_onward_fligh", "sourceTable": "context", "sourceColumn": "flightChange:oldArrivalFlight", "dataType": "string", "isNullable": true},
    {"keyName": "o_onward_time", "sourceTable": "context", "sourceColumn": "flightChange:oldArrivalTime", "dataType": "string", "isNullable": true},
    {"keyName": "n_onward_airline", "sourceTable": "context", "sourceColumn": "flightChange:newArrivalAirline", "dataType": "string", "isNullable": true},
    {"keyName": "n_onward_fligh", "sourceTable": "context", "sourceColumn": "flightChange:newArrivalFlight", "dataType": "string", "isNullable": true},
    {"keyName": "n_onward_time", "sourceTable": "context", "sourceColumn": "flightChange:newArrivalTime", "dataType": "string", "isNullable": true},
    {"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": true},
    {"keyName": "login_user", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false}
]'::jsonb,
updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

-- Update Co-ordinator New Travel Plan Made WhatsApp Template
UPDATE communication_templates_master
SET merge_field_map = '[
    {"keyName": "coordinator_name", "sourceTable": "context", "sourceColumn": "perRecipient:coordinatorInfo", "dataType": "string", "isNullable": false, "isPerRecipient": true},
    {"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}
]'::jsonb,
updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD');

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

-- Check the updated merge field maps
SELECT 
    template_key,
    template_name,
    template_access_key,
    jsonb_array_length(merge_field_map) as merge_field_count,
    merge_field_map
FROM communication_templates_master
WHERE template_key IN (
    'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP'
)
ORDER BY template_key;

-- List all merge field key names for coordinator templates
SELECT 
    template_key,
    jsonb_array_elements(merge_field_map)->>'keyName' as field_name
FROM communication_templates_master
WHERE template_key IN (
    'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP'
)
ORDER BY template_key, field_name;

-- ============================================================
-- END OF MIGRATION
-- ============================================================
