-- ============================================================
-- PT_ENTRAINMENT EMAIL TEMPLATES (Zepto)
-- ============================================================

-- GAP-D: New communication categories for waitlist flows
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlisted';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlist_denied';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'waitlist_release';

-- GAP-E: New communication template access keys for waitlist and ENT flows
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_WAITINGLIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'OFFLINE_REGISTRATION_WAITINGLIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENY_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_PAID_DENY_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENIED_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_DENIED_PAID_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_RELEASE_PAYMENT_WATI_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER';


-- 35. ENT 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 (
    'ENT_REGISTRATION_COMPLETED_EMAIL',
    'ENT Registration Completed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.601e64d0-5cc6-11f0-b019-5254001dc20d.197ef51039d',
    '2d6f.2a60732b5ed84938.k1.78b75710-58cc-11f1-91bc-765e7256bde4.19e62facf01',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_venue", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "Email_Data_Program_Date", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "Email_Data_Checkin_and_Checkout", "sourceTable": "computed", "sourceColumn": "getCheckInOut", "dataType": "string", "isNullable": true},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true}
    ]'::jsonb,
    35,
    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;

-- 36. ENT Offline Payment Acknowledgement 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 (
    'ENT_OFFLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL',
    'ENT Offline Payment Acknowledgement - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.63287b10-5cc7-11f0-b019-5254001dc20d.197ef57a541',
    '2d6f.2a60732b5ed84938.k1.b3dc06b0-58cc-11f1-91bc-765e7256bde4.19e62fc529b',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_venue", "sourceTable": "computed", "sourceColumn": "getAllocatedProgramField:venue_name_in_emails", "dataType": "string", "isNullable": true},
        {"keyName": "Email_Data_Program_Date", "sourceTable": "computed", "sourceColumn": "formatProgramDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "Email_Data_Checkin_and_Checkout", "sourceTable": "computed", "sourceColumn": "getCheckInOut", "dataType": "string", "isNullable": true},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true}
    ]'::jsonb,
    36,
    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;

-- 37. ENT Payment Link Email (Blessed)
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 (
    'ENT_PAYMENT_LINK_EMAIL',
    'ENT Payment Link - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.c20f5ce0-5c93-11f0-b019-5254001dc20d.197ee0549ae',
    '2d6f.2a60732b5ed84938.k1.d90eb930-58e2-11f1-91bc-765e7256bde4.19e638d7443',
    'hdb_blessed',
    'general',
    'BLESSED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    37,
    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;

-- 38. ENT Travel Plan 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 (
    'ENT_TRAVEL_PLAN_EMAIL',
    'ENT Travel Plan - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.db7b9ef0-5cc5-11f0-b019-5254001dc20d.197ef4d9e5f',
    '2d6f.2a60732b5ed84938.k1.3e53b610-58e3-11f1-91bc-765e7256bde4.19e63900bf1',
    'travel_plan_change',
    'general',
    'TRAVEL_PLAN_CHANGE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generateTravelPlanLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    38,
    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;

-- 39. ENT Waitlist Release 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 (
    'ENT_WAITLIST_RELEASE_PAYMENT_EMAIL',
    'ENT Waitlist Release (Payment) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.4ae8c790-5c94-11f0-b019-5254001dc20d.197ee08ca89',
    '2d6f.2a60732b5ed84938.k1.94ee6d31-58e3-11f1-91bc-765e7256bde4.19e63924383',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    39,
    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;

-- 40. ENT Waitlist Denied 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 (
    'ENT_WAITLIST_DENIED_EMAIL',
    'ENT Waitlist Denied - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.cb1dfa20-5c94-11f0-b019-5254001dc20d.197ee0c12c2',
    '2d6f.2a60732b5ed84938.k1.b70f9bf0-58e3-11f1-91bc-765e7256bde4.19e6393232f',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    42,
    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;

-- 43. ENT Waitlist Denied (Paid) 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 (
    'ENT_WAITLIST_DENIED_PAID_EMAIL',
    'ENT Waitlist Denied (Paid) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.29ff3901-5c95-11f0-b019-5254001dc20d.197ee0e808a',
    '2d6f.2a60732b5ed84938.k1.dc7ded20-58d3-11f1-91bc-765e7256bde4.19e632b3cf2',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_PAID_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    43,
    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;

-- ============================================================
-- PT_ENTRAINMENT WHATSAPP TEMPLATES (WATI)
-- ============================================================

-- 44. ENT Registration Completed 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 (
    'ENT_REGISTRATION_COMPLETED_WHATSAPP',
    'ENT Registration Completed - WhatsApp (Seeker)',
    'whatsapp',
    'erc_online_reg_confirmation_v1',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    44,
    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;

-- 45. ENT Offline 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 (
    'ENT_OFFLINE_REGISTRATION_COMPLETED_WHATSAPP',
    'ENT Offline Registration Completed - WhatsApp',
    'whatsapp',
    'erc_offline_provisional_v1',
    '',
    'payment_offline',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    45,
    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;

-- 46. ENT 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 (
    'ENT_RM_REGISTRATION_COMPLETED_WHATSAPP',
    'ENT RM Registration Completed - WhatsApp',
    'whatsapp',
    'erc_online_reg_rm_update',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "participant_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "participant_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false},
        {"keyName": "participant_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "payment_mode", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "isNullable": true},
        {"keyName": "order_id", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    46,
    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;

-- 47. ENT Waitlist Registration 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 (
    'ENT_WAITLIST_REGISTRATION_WHATSAPP',
    'ENT Waitlist Registration - WhatsApp',
    'whatsapp',
    'erc_reg_waitlist_trigger_v3',
    '',
    'waitlisted',
    'general',
    'REGISTRATION_WAITINGLIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_batch", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    47,
    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;

-- 48. ENT Offline Registration Waitinglist 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 (
    'ENT_OFFLINE_REGISTRATION_WAITINGLIST_WHATSAPP',
    'ENT Offline Registration Waitinglist - WhatsApp',
    'whatsapp',
    'erc_waiting_list_after_offline_pament_v3',
    '',
    'waitlisted',
    'general',
    'OFFLINE_REGISTRATION_WAITINGLIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "prg_batch", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_orderid", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    48,
    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;

-- 49. ENT Online Payment Waitlisted 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 (
    'ENT_ONLINE_PAYMENT_WAITLISTED_WHATSAPP',
    'ENT Online Payment Waitlisted - WhatsApp',
    'whatsapp',
    'erc_online_paid_but_waitlist_v1',
    '',
    'waitlisted',
    'general',
    'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_orderid", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "order_id", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    49,
    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;

-- 50. ENT Payment Link WhatsApp (Blessed)
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 (
    'ENT_PAYMENT_LINK_WHATSAPP',
    'ENT Payment Link - WhatsApp',
    'whatsapp',
    'erc_entrainment_payment_link_v3',
    '',
    'hdb_blessed',
    'general',
    'BLESSED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "reg_rm", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": true},
        {"keyName": "reg_mobile", "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    50,
    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;

-- 51. ENT Offline Payment Done WhatsApp (Invoice)
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 (
    'ENT_OFFLINE_PAYMENT_DONE_WHATSAPP',
    'ENT Offline Payment Done - WhatsApp',
    'whatsapp',
    'erc_offline_registration_complete',
    '',
    'invoice',
    'general',
    'INVOICE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    51,
    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;

-- 52. ENT Waitlist Release 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 (
    'ENT_WAITLIST_RELEASE_PAYMENT_WHATSAPP',
    'ENT Waitlist Release (Payment) - WhatsApp',
    'whatsapp',
    'erc_entrainment_waitinglist_release_prod_v5',
    '',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false}
    ]'::jsonb,
    52,
    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;

-- 53. ENT Waitlist Deny 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 (
    'ENT_WAITLIST_DENY_WHATSAPP',
    'ENT Waitlist Deny - WhatsApp',
    'whatsapp',
    'erc_entrainment_wailist_deny_v2_25_v1',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_DENY_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    55,
    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;

-- 56. ENT Waitlist Paid Deny 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 (
    'ENT_WAITLIST_PAID_DENY_WHATSAPP',
    'ENT Waitlist Paid Deny - WhatsApp',
    'whatsapp',
    'erc_entrainment_payment_wailist_deny_v2_25_v1',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_PAID_DENY_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    56,
    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
-- ============================================================
