-- ============================================================
-- PT_TAT WAITLIST WHATSAPP TEMPLATES (WATI)
-- ============================================================

-- New communication categories for waitlist flows (no-op if already added by 17_master_seed_data_entrainment.sql)
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';

-- New communication template access keys for waitlist flows (no-op if already added)
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_RELEASE_PAYMENT_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';

-- Waitlist email access keys (placement emails are new; release/deny email keys may already exist)
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_WAITINGLIST_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'OFFLINE_REGISTRATION_WAITINGLIST_EMAIL_SEEKER';
ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'WAITLIST_RELEASE_PAYMENT_EMAIL_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';

-- 1. TAT Waitlist Registration WhatsApp (waiting list trigger after registration completed)
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_WAITLIST_REGISTRATION_WHATSAPP',
    'TAT Waitlist Registration - WhatsApp',
    'whatsapp',
    'tat_reg_waitlist_trigger_v1',
    '',
    'waitlisted',
    'general',
    'REGISTRATION_WAITINGLIST_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}
    ]'::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 Offline Registration Waitinglist WhatsApp (paid offline but in waiting list)
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_REGISTRATION_WAITINGLIST_WHATSAPP',
    'TAT Offline Registration Waitinglist - WhatsApp',
    'whatsapp',
    'tat_waiting_list_after_offline_payment_v3',
    '',
    'waitlisted',
    'general',
    'OFFLINE_REGISTRATION_WAITINGLIST_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_orderid", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "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. TAT Online Payment Waitlisted WhatsApp (paid online but in waiting list)
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_WAITLISTED_WHATSAPP',
    'TAT Online Payment Waitlisted - WhatsApp',
    'whatsapp',
    'tat_online_paid_but_waitlist_v1',
    '',
    'waitlisted',
    'general',
    'ONLINE_PAYMENT_DONE_BUT_WAITING_LIST_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": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_orderid", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "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;

-- 4. TAT Waitlist Release WhatsApp (waiting list release message)
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_WAITLIST_RELEASE_WHATSAPP',
    'TAT Waitlist Release - WhatsApp',
    'whatsapp',
    'tat_waitinglist_release_dev_v1',
    '',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_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": "course_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "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. TAT Waitlist Deny WhatsApp (waiting list deny message)
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_WAITLIST_DENY_WHATSAPP',
    'TAT Waitlist Deny - WhatsApp',
    'whatsapp',
    'tat_wailist_deny_v2',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_DENY_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": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField: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. TAT Waitlist Paid Deny WhatsApp (paid waiting list deny message)
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_WAITLIST_PAID_DENY_WHATSAPP',
    'TAT Waitlist Paid Deny - WhatsApp',
    'whatsapp',
    'tat_payment_wailist_deny_v2',
    '',
    'waitlist_denied',
    'general',
    'WAITLIST_PAID_DENY_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": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "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;

-- ============================================================
-- PT_TAT WAITLIST EMAIL TEMPLATES (Zepto)
-- ============================================================

-- 7. TAT Waitlist Registration Email (registration moved to waitlist, payment not done)
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_WAITLIST_REGISTRATION_EMAIL',
    'TAT Waitlist Registration - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.c2d91190-6889-11f1-9635-fae9afc80e45.19eca212929',
    '2d6f.2a60732b5ed84938.k1.180f2641-68a3-11f1-9635-fae9afc80e45.19ecac72ea2',
    'waitlisted',
    'general',
    'REGISTRATION_WAITINGLIST_EMAIL_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}
    ]'::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. TAT Offline Registration Waitinglist Email (paid offline but in waiting list)
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_REGISTRATION_WAITINGLIST_EMAIL',
    'TAT Offline Registration Waitinglist - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.5b066f80-688a-11f1-9635-fae9afc80e45.19eca250e78',
    '2d6f.2a60732b5ed84938.k1.40fdf860-68a3-11f1-9635-fae9afc80e45.19ecac83ae6',
    'waitlisted',
    'general',
    'OFFLINE_REGISTRATION_WAITINGLIST_EMAIL_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_orderid", "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "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. TAT Waitlist Release Email (waiting list release message)
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_WAITLIST_RELEASE_PAYMENT_EMAIL',
    'TAT Waitlist Release (Payment) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.f93292a1-688b-11f1-9635-fae9afc80e45.19eca2fa8ca',
    '2d6f.2a60732b5ed84938.k1.6a385270-68a3-11f1-9635-fae9afc80e45.19ecac94917',
    'waitlist_release',
    'general',
    'WAITLIST_RELEASE_PAYMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),
    '[
        {"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,
    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. TAT Waitlist Denied Email (waiting list deny message)
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_WAITLIST_DENIED_EMAIL',
    'TAT Waitlist Denied - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.2a4c7541-688c-11f1-9635-fae9afc80e45.19eca30ea94',
    '2d6f.2a60732b5ed84938.k1.83deca10-68a3-11f1-9635-fae9afc80e45.19ecac9f131',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_EMAIL_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": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false}
    ]'::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. TAT Waitlist Denied (Paid) Email (paid waiting list deny message)
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_WAITLIST_DENIED_PAID_EMAIL',
    'TAT Waitlist Denied (Paid) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.c7a086e0-6898-11f1-9635-fae9afc80e45.19eca83964e',
    '2d6f.2a60732b5ed84938.k1.9f0d1a31-68a3-11f1-9635-fae9afc80e45.19ecacaa352',
    'waitlist_denied',
    'general',
    'WAITLIST_DENIED_PAID_EMAIL_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": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "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;

-- ============================================================
-- VERIFICATION QUERY
-- ============================================================
-- SELECT template_key, template_id, category, template_access_key, merge_field_map
-- FROM public.communication_templates_master
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
--   AND category IN ('waitlisted', 'waitlist_release', 'waitlist_denied')
-- ORDER BY display_order;
