-- ============================================================
-- PT_TAT — RM Registration Completed WhatsApp (WATI)
-- ------------------------------------------------------------
-- RM communication sent when a seeker's registration is completed
-- with payment (online/offline) for TAT programs.
-- Recipient: Relational Manager (RM).
-- Mirrors ENT_RM_REGISTRATION_COMPLETED_WHATSAPP (file 17, template #46).
--
-- Access key REGISTRATION_COMPLETED_WATI_RM already exists in
-- communication_template_access_key_enum (file 07) and is shared across
-- program types — no ALTER TYPE required here.
-- ============================================================
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'registration_completed_with_payment';

ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM';


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 (
    'TAT_RM_REGISTRATION_COMPLETED_WITH_PAYMENT_WHATSAPP',
    'TAT RM Registration Completed With Payment - WhatsApp',
    'whatsapp', 
    'tat_reg_completion_rm_update',
    '',
    'registration_completed_with_payment',
    'general',
    'REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "rm_name", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "order_id", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "participant_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "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}
    ]'::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;

-- ============================================================
-- VERIFICATION
-- ============================================================
SELECT template_key, template_name, template_type, template_id, template_access_key, category, merge_field_map
FROM communication_templates_master
WHERE template_key = 'TAT_RM_REGISTRATION_COMPLETED_WITH_PAYMENT_WHATSAPP'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');
