-- ============================================================
-- PT_TAT EMAIL + WHATSAPP TEMPLATES (Zepto)
-- ============================================================

-- 1. TAT Registration 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 (
    'TAT_REGISTRATION_ACKNOWLEDGEMENT_EMAIL',
    'TAT Registration Acknowledgement - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859',
    '2d6f.2a60732b5ed84938.k1.2ae0fe61-63fd-11f1-9ff7-525400d4bb1c.19eac50e144',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_full_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "session_date", "sourceTable": "computed", "sourceColumn": "formatSessionDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true}
    ]'::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,
    merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 2. TAT Online Payment Completion 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 (
    'TAT_ONLINE_PAYMENT_COMPLETION_EMAIL',
    'TAT Online Payment Completion - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.27cf38c0-716a-11f1-9b3d-5254001dc20d.19f044d864c',
    '2d6f.2a60732b5ed84938.k1.cde33310-63dc-11f1-bad1-765e7256bde4.19eab7cc8c1',
    'payment_acknowledgement',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "reg_number", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "course_full_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "session_date", "sourceTable": "computed", "sourceColumn": "formatSessionDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "pay_method", "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true}
    ]'::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. TAT 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 (
    'TAT_INVOICE_EMAIL',
    'TAT Invoice - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.563c6430-7165-11f1-9b3d-5254001dc20d.19f042df1f3',
    '2d6f.2a60732b5ed84938.k1.97269150-6404-11f1-9ff7-525400d4bb1c.19eac8186e5',
    'invoice',
    'general',
    'INVOICE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "firstPara", "sourceTable": "computed", "sourceColumn": "firstPara", "dataType": "string", "isNullable": true},
        {"keyName": "prg_coursename", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_fullname", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "invoice_number", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_sequence_number", "dataType": "string", "isNullable": false},
        {"keyName": "session_date", "sourceTable": "computed", "sourceColumn": "formatSessionDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true}
    ]'::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. TAT Online Confirmation 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 (
    'TAT_ONLINE_CONFIRMATION_WHATSAPP',
    'TAT Online Confirmation - WhatsApp',
    'whatsapp',
    'tat_reg_confirmed_v11',
    '',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "full_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": "session_dates", "sourceTable": "computed", "sourceColumn": "formatSessionDateAndDaysRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "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;

-- 5. TAT Offline Acknowledgement 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 (
    'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP',
    'TAT Offline Acknowledgement - WhatsApp',
    'whatsapp',
    'tat_reg_acknowledgement_v1',
    '',
    'payment_acknowledgement',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "full_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": "session_dates", "sourceTable": "computed", "sourceColumn": "formatSessionDateAndDaysRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true},
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "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;

-- 6. TAT Offline Payment Realization 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 (
    'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP',
    'TAT Offline Payment Realization - WhatsApp',
    'whatsapp',
    'tat_offline_payment_realization_v6',
    '',
    'invoice',
    'general',
    'INVOICE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"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_email", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_email", "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;

-- 7. TAT Payment Link WhatsApp
-- Dedicated payment-link access key & category (basic registration done, payment pending)
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_LINK_WATI_SEEKER';
ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_link';

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_PAYMENT_LINK_WHATSAPP',
    'TAT Payment Link - WhatsApp',
    'whatsapp',
    'otat_payment_link_msg_v3',
    '',
    'payment_link',
    'general',
    'PAYMENT_LINK_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"keyName": "reg_name", "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "batch_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"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},
        {"keyName": "reg_rm", "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": true}
    ]'::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;



--- update payment link
select * from public.communication_templates_master where template_key = 'TAT_PAYMENT_LINK_WHATSAPP';

UPDATE public.communication_templates_master
SET
    category = 'payment_link',
    template_access_key = 'PAYMENT_LINK_WATI_SEEKER'
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP' and program_type_id in (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- update online payment confirm acknowledgements
select * from public.communication_templates_master where template_key = 'TAT_ONLINE_CONFIRMATION_WHATSAPP';

ALTER TYPE communication_category_enum
ADD VALUE 'online_payment_comfirmed';

ALTER TYPE communication_template_access_key_enum
ADD VALUE 'ONLINE_PAYMENT_COMPLETED_WATI_SEEKER';

UPDATE public.communication_templates_master
set category = 'online_payment_comfirmed',
	template_access_key = 'ONLINE_PAYMENT_COMPLETED_WATI_SEEKER'
WHERE template_key = 'TAT_ONLINE_CONFIRMATION_WHATSAPP' and program_type_id in (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- update offline payment acknowledgements after submission
select * from public.communication_templates_master where 
template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER'


UPDATE public.communication_templates_master
set category = 'payment_offline',
	template_access_key = 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER'
WHERE template_key = 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP' and program_type_id in (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- update offline payment completion acknowledgements

select * from public.communication_templates_master where 
-- template_access_key = 'INVOICE_WATI_SEEKER' and
-- category = 'offline_payment_comfirmed' and
template_key = 'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP'

ALTER TYPE communication_category_enum
ADD VALUE 'offline_payment_comfirmed';

ALTER TYPE communication_template_access_key_enum
ADD VALUE 'OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER';

UPDATE public.communication_templates_master
set category = 'offline_payment_comfirmed',
	template_access_key = 'OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
WHERE template_key = 'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP' and program_type_id in (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');