
-- 1A. Update master template: Payment Acknowledgement Email
UPDATE communication_templates_master 
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    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": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL'
  AND template_type = 'email';

-- 1B. Update master template: Payment Acknowledgement WhatsApp  
UPDATE communication_templates_master 
SET template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    merge_field_map = '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"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": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP'
  AND template_type = 'whatsapp';

-- 1C. Update master template: HDB Blessed Email - Add day fields
UPDATE communication_templates_master 
SET merge_field_map = '[
        {"keyName": "venue_name", "sourceTable": "computed", "sourceColumn": "getProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_price", "sourceTable": "computed", "sourceColumn": "getProgramField:base_price", "dataType": "number", "formatType": "currency", "isNullable": true},
        {"keyName": "payment_last_date", "sourceTable": "computed", "sourceColumn": "getProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "cash_details", "sourceTable": "computed", "sourceColumn": "cashDetails", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_or_msd", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_dates", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_no", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "payment_online_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "payment_back_transfer_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "payment_cheque_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_BLESSED_EMAIL'
  AND template_type = 'email';

-- 1D. Update master template: HDB Blessed WhatsApp - Add day/date fields
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": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false}
    ]'::jsonb,
    updated_at = NOW()
WHERE template_key = 'HDB_BLESSED_WHATSAPP'
  AND template_type = 'whatsapp';
