-- Seed Data: Communication Templates Master - Complete (Consolidated)
-- Date: 2026-04-02 (Original) / 2026-04-10 (Consolidated)
-- Description: Complete seed data with all fixes consolidated from files 09-14
--              Includes correct template_access_key, merge_field_map, and formatType specifications
--              All program field references use getAllocatedProgramField where appropriate

-- ============================================================
-- ZEPTO EMAIL TEMPLATES (Production & Sandbox)
-- ============================================================

-- 1. Generic Email Template
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'GENERIC_EMAIL',
    'Generic Email Template',
    'email',
    '2d6f.2a60732b5ed84938.k1.2b4f1a40-49b4-11ef-a499-5254000b1a0e.190e49c41e4',
    '2d6f.2a60732b5ed84938.k1.2b4f1a40-49b4-11ef-a499-5254000b1a0e.190e49c41e4',
    'general',
    'general',
    'GENERIC_EMAIL',
    NULL,
    '[]'::jsonb,
    1,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    updated_at = CURRENT_TIMESTAMP;

-- 2. HDB Blessed Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BLESSED_EMAIL',
    'HDB Blessed - Email Notification',
    'email',
    '2d6f.2a60732b5ed84938.k1.ccbdf990-3f71-11ed-8592-525400d6cd4f.18385ee9fa9',
    '2d6f.2a60732b5ed84938.k1.93990780-7764-11f0-b704-86f7e6aa0425.1989dc28ff8',
    'hdb_blessed',
    'general',
    'BLESSED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "venue_name", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "hdb_price", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:base_price", "dataType": "number", "formatType": "currency", "isNullable": true},
        {"keyName": "payment_last_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField: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": "getAllocatedProgramField: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": "getAllocatedProgramField: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,
    2,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 3. Registration Completed Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_REGISTRATION_COMPLETED_EMAIL',
    'HDB Registration Completed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.fe0eca50-3f27-11ed-bf3f-5254004d4100.183840aeb75',
    '2d6f.2a60732b5ed84938.k1.fa8c5b30-7765-11f0-b704-86f7e6aa0425.1989dcbc063',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_edit_link", "sourceTable": "computed", "sourceColumn": "registrationEditLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    3,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 4. Invoice Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_INVOICE_EMAIL',
    'HDB Invoice - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.1e18de20-40c3-11ed-92eb-525400e3c1b1.1838e914502',
    '2d6f.2a60732b5ed84938.k1.22d8f9e0-7766-11f0-b704-86f7e6aa0425.1989dccc87e',
    'invoice',
    'general',
    'INVOICE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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": "hdb_msd_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": "hdb_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "last_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramStartsMinus10Days", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    4,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 5. Payment Acknowledgement Offline Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL',
    'HDB Payment Acknowledgement Offline - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.27b57f20-3f46-11ed-bf3f-5254004d4100.18384d09812',
    '2d6f.2a60732b5ed84938.k1.d12328f0-7765-11f0-b704-86f7e6aa0425.1989dcab0ff',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    5,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 6. Hold Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_HOLD_EMAIL',
    'HDB Hold - Email Notification',
    'email',
    '2d6f.2a60732b5ed84938.k1.de547010-ce95-11f0-aa17-9233b47d379f.19ad92f6191',
    '2d6f.2a60732b5ed84938.k1.83c344a0-7765-11f0-b704-86f7e6aa0425.1989dc8b5ea',
    'hdb_hold',
    'general',
    'HOLD_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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": "last_allocated_hdb_msd", "sourceTable": "computed", "sourceColumn": "allocatedProgramName", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    6,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 7. OTP Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'OTP_EMAIL',
    'OTP Verification - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.5daac090-6207-11f0-a800-525400a229b1.19811bf8b19',
    '',
    'otp',
    'portal',
    'OTP_EMAIL',
    NULL,
    '[
        {"keyName": "otp", "sourceTable": "computed", "sourceColumn": "generateOTP", "dataType": "string", "isNullable": false},
        {"keyName": "userName", "sourceTable": "users", "sourceColumn": "full_name", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    7,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 8. Blessed No Payment Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BLESSED_NO_PAYMENT_EMAIL',
    'HDB Blessed No Payment - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.f7bd1cd0-6ed9-11f0-a238-86f7e6aa0425.19865c8671d',
    '2d6f.2a60732b5ed84938.k1.548d1620-7765-11f0-b704-86f7e6aa0425.1989dc78082',
    'hdb_blessed_no_payment',
    'general',
    'BLESSED_NO_PAYMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "venue_name", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_or_msd", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField: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": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    8,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 9. RM Hold Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_HOLD_EMAIL',
    'HDB RM Hold - Email Notification',
    'email',
    '2d6f.2a60732b5ed84938.k1.e90930f0-7dbd-11f0-8610-525400c09db6.198c75e597f',
    '2d6f.2a60732b5ed84938.k1.7e1e0890-7c2f-11f0-8610-525400c09db6.198bd2b4799',
    'hdb_hold',
    'general',
    'RM_HOLD_EMAIL_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    9,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 10. Registration Refund Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_REGISTRATION_REFUND_EMAIL',
    'HDB Registration Refund - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.bd286f00-7dbd-11f0-8610-525400c09db6.198c75d39f0',
    '2d6f.2a60732b5ed84938.k1.432d0850-7ccd-11f0-8610-525400c09db6.198c1353d55',
    'registration_refund',
    'general',
    'REGISTRATION_REFUND_EMAIL_FINANCE',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[]'::jsonb,
    10,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 11. Billing Details Edit Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BILLING_DETAILS_EDIT_EMAIL',
    'HDB Billing Details Edit - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.00bc8f30-81b0-11f0-94a8-525400c09db6.198e13a17a3',
    '2d6f.2a60732b5ed84938.k1.b4393b20-81a2-11f0-94a8-525400c09db6.198e0e2efd2',
    'billing_details_edit',
    'general',
    'BILLING_DETAILS_EDIT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    11,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 12. Billing Details Edit Removed Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL',
    'HDB Billing Details Edit Removed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.e7540af0-81af-11f0-94a8-525400c09db6.198e139711f',
    '2d6f.2a60732b5ed84938.k1.7c467840-81a7-11f0-94a8-525400c09db6.198e10245c4',
    'billing_details_removed',
    'general',
    'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    12,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 13. Cancel/Cancellation Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_CANCEL_EMAIL',
    'HDB Cancellation - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.6beb0c61-8984-11f0-9c73-525400c09db6.199148a4024',
    '2d6f.2a60732b5ed84938.k1.d3cf9751-8958-11f0-9c73-525400c09db6.199136c8d45',
    'registration_cancelled',
    'general',
    'REGISTRATION_CANCELLED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[]'::jsonb,
    13,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 14. E-Invoice Error Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_EINVOICE_ERROR_EMAIL',
    'HDB E-Invoice Error - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.744b32a0-0b15-11f1-8e75-5254001dc20d.19c65aad2ca',
    '2d6f.2a60732b5ed84938.k1.744b32a0-0b15-11f1-8e75-5254001dc20d.19c65aad2ca',
    'invoice',
    'general',
    'EINVOICE_ERROR_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[]'::jsonb,
    14,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 15. Swap Demand Email
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_SWAP_DEMAND_EMAIL',
    'HDB Swap Demand - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.66f91061-b582-11f0-84b9-765e7256bde4.19a34d8c462',
    '2d6f.2a60732b5ed84938.k1.66f91061-b582-11f0-84b9-765e7256bde4.19a34d8c462',
    'swap_demand',
    'general',
    'SWAP_DEMAND_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    15,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ============================================================
-- WATI WHATSAPP TEMPLATES
-- ============================================================

-- 16. HDB Blessed No Payment WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BLESSED_NO_PAYMENT_WHATSAPP',
    'HDB Blessed No Payment - WhatsApp',
    'whatsapp',
    'hdb_blessed_msg_seeker_no_payment_v2',
    '',
    'hdb_blessed_no_payment',
    'general',
    'BLESSED_NO_PAYMENT_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
    ]'::jsonb,
    16,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 17. Seeker Registration Completed WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP',
    'HDB Seeker Registration Completed - WhatsApp',
    'whatsapp',
    'hdb_registration_complete_v5',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    17,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 18. RM Registration Completed WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_REGISTRATION_COMPLETED_WHATSAPP',
    'HDB RM Registration Completed - WhatsApp',
    'whatsapp',
    'hdb_registration_complete_rm_v2',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    18,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 19. HDB Blessed WhatsApp (Seeker)
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BLESSED_WHATSAPP',
    'HDB Blessed - WhatsApp (Seeker)',
    'whatsapp',
    'hdb_blessed_msg_seeker_v8',
    '',
    'hdb_blessed',
    'general',
    'BLESSED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "s_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_day", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "weekname", "isNullable": false},
        {"keyName": "payment_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    19,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 20. Invoice WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_INVOICE_WHATSAPP',
    'HDB Invoice - WhatsApp',
    'whatsapp',
    'hdb_payment_confirmation_v4',
    '',
    'invoice',
    'general',
    'INVOICE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "hbd_msd_variable", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
    ]'::jsonb,
    20,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 21. Payment Acknowledgement Offline WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP',
    'HDB Payment Acknowledgement Offline - WhatsApp',
    'whatsapp',
    'hdb_offline_payment_ack_v3',
    '',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    21,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 22. HDB Blessed RM WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_BLESSED_RM_WHATSAPP',
    'HDB Blessed - WhatsApp (RM)',
    'whatsapp',
    'hdb_blessed_rm_v4',
    '',
    'hdb_blessed',
    'general',
    'BLESSED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "hdb_msd_variable", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "s_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:starts_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "e_date", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:ends_at", "dataType": "date", "formatType": "custom:DD-MM-YYYY", "isNullable": false}
    ]'::jsonb,
    22,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 23. Co-ordinator Travel Plan Change WhatsApp (General/Mode Change)
-- coordinator_name has isPerRecipient=true for dynamic per-recipient computation
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB Co-ordinator Travel Plan Change - WhatsApp',
    'whatsapp',
    'hdb_co_ordinator_travel_plan_change_dev_v2',
    '',
    'travel_plan_change',
    'general',
    'CO_ORDINATOR_TRAVEL_PLAN_CHANGE_WATI',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    23,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 24. Admin Message Notification WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP',
    'HDB Admin Message Notification - WhatsApp',
    'whatsapp',
    'hdb_admin_message_notification_dev_v1',
    '',
    'admin_message_notification',
    'general',
    'ADMIN_MESSAGE_NOTIFICATION_WATI',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "admin_name", "sourceTable": "users", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "sender_admin_name", "sourceTable": "users", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    24,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 25. RM Payment Pending Reminder WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP',
    'HDB RM Payment Pending Reminder - WhatsApp',
    'whatsapp',
    'hdb_rm_payment_pending_reminder_list_dev_v2',
    '',
    'payment_pending_reminder',
    'general',
    'PAYMENT_PENDING_REMINDER_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    25,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 26. RM Preference Edited WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_PREFERENCE_EDITED_WHATSAPP',
    'HDB RM Preference Edited - WhatsApp',
    'whatsapp',
    'hdb_seeker_preference_change_v1',
    '',
    'preference_edited',
    'general',
    'PREFERENCE_EDITED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"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": "old_pref", "sourceTable": "computed", "sourceColumn": "formatPreferenceForMessage", "dataType": "string", "isNullable": false},
        {"keyName": "new_pref", "sourceTable": "computed", "sourceColumn": "formatPreferenceForMessage", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    26,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 27. Co-ordinator Return Travel Plan Change WhatsApp (Flight specific)
-- coordinator_name has isPerRecipient=true, flight details from context
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB Co-ordinator Return Travel Plan Change - WhatsApp',
    'whatsapp',
    'hdb_msd_return_travel_modification_update_v1',
    '',
    'travel_plan_change',
    'general',
    'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WATI',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    27,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 28. Co-ordinator Onward Travel Plan Change WhatsApp (Flight specific)
-- coordinator_name has isPerRecipient=true, flight details from context
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP',
    'HDB Co-ordinator Onward Travel Plan Change - WhatsApp',
    'whatsapp',
    'hdb_msd_onward_travel_modification_update_v2',
    '',
    'travel_plan_change',
    'general',
    'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WATI',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    28,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 29. Co-ordinator New Travel Plan Made WhatsApp
-- coordinator_name has isPerRecipient=true for dynamic per-recipient computation
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP',
    'HDB Co-ordinator New Travel Plan Made - WhatsApp',
    'whatsapp',
    'hdb_msd_co_ordinator_new_travel_plan_made_v1',
    '',
    'travel_plan_new',
    'general',
    'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE_WATI',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    29,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 30. Swap Demand WhatsApp
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_SWAP_DEMAND_WHATSAPP',
    'HDB Swap Demand - WhatsApp',
    'whatsapp',
    'hdb_registration_swap_demand_v2',
    '',
    'swap_demand',
    'general',
    'SWAP_DEMAND_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    30,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ============================================================
-- MSG91 SMS TEMPLATES
-- ============================================================

-- 31. RM Registration Notify SMS
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_REGISTRATION_NOTIFY_SMS',
    'HDB RM Registration Notify - SMS',
    'sms',
    '68e631ca46a4a32c0b5d6836',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_SMS_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"keyName": "var1", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    31,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 32. RM Bless Notify SMS
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_BLESS_NOTIFY_SMS',
    'HDB RM Bless Notify - SMS',
    'sms',
    '68ee5198ac0f726e41624d23',
    '',
    'hdb_blessed',
    'general',
    'BLESSED_SMS_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    32,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 33. OTP SMS
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'OTP_SMS',
    'OTP Verification - SMS',
    'sms',
    '68760e83d6fc0555bd308332',
    '',
    'otp',
    'portal',
    'OTP_SMS',
    NULL,
    '[
        {"keyName": "var1", "sourceTable": "computed", "sourceColumn": "generateOTP", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    33,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id, merge_field_map, display_order, is_active
) VALUES (
    'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP',
    'HDB RM Payment Pending Reminder - WhatsApp',
    'whatsapp',
    'hdb_rm_payment_pending_reminder_list_dev_v2',
    '',
    'payment_pending_reminder',
    'general',
    'PAYMENT_PENDING_REMINDER_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
    '[
        {"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,
    34,
    true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id,
    sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

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

-- Verify the template was inserted
SELECT
    template_key,
    template_name,
    template_type,
    template_id,
    template_access_key,
    category,
    merge_field_map
FROM communication_templates_master
WHERE template_access_key = 'PAYMENT_PENDING_REMINDER_WATI_RM';

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

-- Check total templates inserted
SELECT COUNT(*) AS total_templates FROM communication_templates_master;

-- Check templates by type
SELECT template_type, COUNT(*) AS count
FROM communication_templates_master
GROUP BY template_type
ORDER BY template_type;

-- Check templates by category
SELECT category, COUNT(*) AS count
FROM communication_templates_master
GROUP BY category
ORDER BY count DESC;

-- Check templates by access key
SELECT template_access_key, COUNT(*) AS count
FROM communication_templates_master
GROUP BY template_access_key
ORDER BY count DESC;

-- Check HDB program templates
SELECT template_key, template_name, template_type, category, template_access_key
FROM communication_templates_master
WHERE program_type_id = 1
ORDER BY display_order;

-- List all templates with their access keys
SELECT
    template_key,
    template_name,
    template_type,
    template_access_key,
    category
FROM communication_templates_master
ORDER BY display_order;

-- ============================================================
-- CONSOLIDATION NOTICE
-- ============================================================
-- This seed data file (08) now includes all corrections and fixes
-- that were previously in separate migration files 09-14.
--
-- All templates have the correct:
-- - template_access_key values (recipient-specific variants)
-- - merge_field_map with getAllocatedProgramField references
-- - formatType specifications (weekname, currency, custom:DD-MM-YYYY)
-- - isPerRecipient flags for coordinator templates
-- - Context-based merge fields for payment reminders
--
-- Changes Applied:
-- ✓ BLESSED_EMAIL: Added s_date, e_date fields with formatType
-- ✓ BLESSED_WHATSAPP: Added s_day, e_day fields with weekname formatType
-- ✓ PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP: Added pay_date, pay_amount, hdb_msd
-- ✓ All allocated program references use getAllocatedProgramField
-- ✓ Coordinator templates have isPerRecipient: true for coordinator_name
-- ✓ Payment reminder template uses context-based rm_name
-- ✓ SMS templates have var1-var4 merge field mappings
--
-- Files consolidated into this seed data:
-- - 09, 10: template_access_key fixes
-- - 11: coordinator travel plan merge fields
-- - 12: payment template and MSG91 SMS merge fields
-- - 13: allocated program field references
-- - 14: is_per_recipient column (added to schema in file 07)
-- ============================================================

-- ============================================================
-- END OF SEED DATA
-- ============================================================
