-- ================================================
-- Migration: PAP-002 — PT_PAP seeker email templates (Zepto) + clone to PT_PAP programs
-- Date: 2026-08-08
--
-- Seeds the six seeker-facing emails for PT_PAP. PAP requires approval
-- (program_type_v1.requires_approval = true, see
-- database/migrations/2026-08-07-01-pt-pap-program-type-workflow.sql), so the flow is:
--
--   1. Registration completion   — seeker submits, registration goes for approval
--   2. Registration approved     — seat allocated, carries the payment link
--   3. Online registration confirmed — online payment captured
--   4. Invoice                   — invoice generated (PDF attached by InvoiceService)
--   5. Offline payment acknowledgement — offline payment submitted, pending realization
--   6. Offline payment confirmed — offline payment realized
--
-- All six reuse send sites that already exist in the application and resolve their template by
-- (program_id, template_access_key, template_type) — so no per-program-type branching is needed
-- in the services. Access key -> send site:
--   1 REGISTRATION_COMPLETED_EMAIL_SEEKER            — RegistrationService (registration submit)
--   2 BLESSED_EMAIL_SEEKER                           — RegistrationApprovalService (approve)
--   3 ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER    — PaymentService.sendOnlinePaymentAckEmail
--   4 INVOICE_EMAIL_SEEKER                           — InvoiceService.generateAndSendInvoice
--   5 PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER   — PaymentService (offline payment submit)
--   6 OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER         — PaymentService.handlePaymentConfirmation
--                                                      (offline branch — NEW, added with this seed)
--
-- Merge fields specific to PAP's residential format, all backed by computed functions added to
-- CommunicationMergeDataService alongside this file:
--   program_dates      formatProgramDayDates  -> "September 28, 29 & 30, 2026"
--   checkin_time_date  getCheckInWindow       -> "Monday, 28 September 2026, from 1:00 p.m. to 3:00 p.m."
--   checkout_time_date getCheckOutDeadline    -> "Wednesday, 30 September 2026, from 11:30 a.m. to 1:00 p.m."
--
-- Those three read the ALLOCATED sub-program (hdb_program_registration.allocated_program_id) and
-- fall back to the parent program when nothing is allocated yet — which is the normal state for
-- template 1, since PAP allocates the seat only at approval.
--
-- program_venue deliberately uses getProgramField:venue (the parent program) rather than the
-- allocated sub-program: PAP's venue is a program-type-level attribute shared by every
-- sub-program, and the parent always resolves — including pre-approval, for template 1.
--
-- Prerequisite: run 55_pap-email-enums.sql first (its enum values must be committed).
-- Idempotent — safe to re-run.
-- ================================================

-- ------------------------------------------------
-- PART 1: Master templates
-- ------------------------------------------------

-- 1. Registration completion — sent when the seeker submits and the registration goes for approval.
--    No allocated sub-program exists at this point, so program_dates falls back to the parent
--    program's span (see header note).
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 (
    'PAP_REGISTRATION_COMPLETED_EMAIL_SEEKER',
    'PAP Registration Completion (Sent for Approval) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.048bef80-9298-11f1-b0ea-525400114fe6.19fddbee678',
    '2d6f.2a60732b5ed84938.k1.caa15aa0-92a4-11f1-b0ea-525400114fe6.19fde129d4a',
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "program_venue", "sourceTable": "computed", "sourceColumn": "getProgramField:venue", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "program_name",  "sourceTable": "computed", "sourceColumn": "getProgramField:name",  "dataType": "string", "isNullable": false},
        {"keyName": "program_dates", "sourceTable": "computed", "sourceColumn": "formatProgramDayDates", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "reg_name",      "sourceTable": "hdb_program_registration", "sourceColumn": "full_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. Registration approved — seat allocated. Carries the payment link so the seeker can pay.
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 (
    'PAP_BLESSED_EMAIL_SEEKER',
    'PAP Registration Approved (Seat Allocated) - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.82f020b0-929a-11f1-b0ea-525400114fe6.19fddcf3e3b',
    '2d6f.2a60732b5ed84938.k1.cfeb1d21-92a4-11f1-b0ea-525400114fe6.19fde12bff1',
    'hdb_blessed',
    'general',
    'BLESSED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_name",     "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "payment_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. Online registration confirmed — online payment captured. The seat is allocated by now, so
--    program_dates / check-in / check-out resolve off the allocated sub-program.
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 (
    'PAP_ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER',
    'PAP Online Registration Confirmed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.4d061b91-929e-11f1-b0ea-525400114fe6.19fdde813c5',
    '2d6f.2a60732b5ed84938.k1.d5b31051-92a4-11f1-b0ea-525400114fe6.19fde12e5d1',
    'online_payment_comfirmed',
    'general',
    'ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_number",         "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "program_venue",      "sourceTable": "computed", "sourceColumn": "getProgramField:venue", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "program_name",       "sourceTable": "computed", "sourceColumn": "getProgramField:name",  "dataType": "string", "isNullable": false},
        {"keyName": "checkout_time_date", "sourceTable": "computed", "sourceColumn": "getCheckOutDeadline",   "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "program_dates",      "sourceTable": "computed", "sourceColumn": "formatProgramDayDates", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkin_time_date",  "sourceTable": "computed", "sourceColumn": "getCheckInWindow",      "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"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": 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. Invoice — InvoiceService attaches the generated invoice PDF to this send.
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 (
    'PAP_INVOICE_EMAIL_SEEKER',
    'PAP Invoice - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.20c6acf1-92a0-11f1-b0ea-525400114fe6.19fddf40d3d',
    '2d6f.2a60732b5ed84938.k1.dc065390-92a4-11f1-b0ea-525400114fe6.19fde130f49',
    'invoice',
    'general',
    'INVOICE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "invoice_sequence_number", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_sequence_number", "dataType": "string", "isNullable": false},
        {"keyName": "program_name",            "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_name",                "sourceTable": "hdb_program_registration", "sourceColumn": "full_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. Offline payment acknowledgement — offline payment submitted, realization pending.
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 (
    'PAP_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    'PAP Offline Payment Acknowledgement - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.03e829e0-92a2-11f1-b0ea-525400114fe6.19fde006b7e',
    '2d6f.2a60732b5ed84938.k1.e2ae94a0-92a4-11f1-b0ea-525400114fe6.19fde133aea',
    'payment_acknowledgement',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_number",         "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "program_venue",      "sourceTable": "computed", "sourceColumn": "getProgramField:venue", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "program_name",       "sourceTable": "computed", "sourceColumn": "getProgramField:name",  "dataType": "string", "isNullable": false},
        {"keyName": "checkout_time_date", "sourceTable": "computed", "sourceColumn": "getCheckOutDeadline",   "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "program_dates",      "sourceTable": "computed", "sourceColumn": "formatProgramDayDates", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkin_time_date",  "sourceTable": "computed", "sourceColumn": "getCheckInWindow",      "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"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": true}
    ]'::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. Offline payment confirmed — offline payment realized. New access key; see file 55.
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 (
    'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
    'PAP Offline Payment Confirmed - Email',
    'email',
    '2d6f.2a60732b5ed84938.k1.7e873230-92a3-11f1-b0ea-525400114fe6.19fde0a1cd3',
    '2d6f.2a60732b5ed84938.k1.e7d8c130-92a4-11f1-b0ea-525400114fe6.19fde135cc3',
    'offline_payment_comfirmed',
    'general',
    'OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_name",     "sourceTable": "hdb_program_registration", "sourceColumn": "full_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;

-- ------------------------------------------------
-- PART 2: Clone each master row to every PT_PAP program and expand its merge_field_map into
-- merge_info_answer_location_map. is_enabled = true — unlike the optional session
-- communications, these six are the core transactional emails of the PAP registration flow and
-- must fire from the moment a program exists.
-- ------------------------------------------------
DO $$
DECLARE
    pap_keys TEXT[] := ARRAY[
        'PAP_REGISTRATION_COMPLETED_EMAIL_SEEKER',
        'PAP_BLESSED_EMAIL_SEEKER',
        'PAP_ONLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL_SEEKER',
        'PAP_INVOICE_EMAIL_SEEKER',
        'PAP_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER'
    ];
    pap_key         TEXT;
    master_rec      RECORD;
    prog_rec        RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    FOREACH pap_key IN ARRAY pap_keys LOOP
        SELECT id, template_id, template_name, template_key, template_type,
               sandbox_template_id, category, template_usage_type,
               template_access_key, merge_field_map, display_order
        INTO master_rec
        FROM communication_templates_master
        WHERE template_key = pap_key AND is_active = true
        LIMIT 1;

        IF master_rec IS NULL THEN
            RAISE EXCEPTION 'Master template % not found — run PART 1 first', pap_key;
        END IF;

        FOR prog_rec IN
            SELECT p.id AS program_id
            FROM program_v1 p
            JOIN program_type_v1 pt ON pt.id = p.program_type_id
            WHERE pt.key        = 'PT_PAP'
              AND pt.deleted_at IS NULL
              AND p.deleted_at  IS NULL
            ORDER BY p.id
        LOOP
            IF EXISTS (
                SELECT 1 FROM hdb_communication_templates h
                WHERE h.program_id         = prog_rec.program_id
                  AND h.template_key::text = pap_key
                  AND h.template_type      = master_rec.template_type
            ) THEN
                skipped_count := skipped_count + 1;
                CONTINUE;
            END IF;

            INSERT INTO hdb_communication_templates (
                template_id, template_name, template_key, template_type,
                sandbox_template_id, category, template_usage_type, template_access_key,
                program_id, is_enabled, display_order, created_at, updated_at
            ) VALUES (
                master_rec.template_id, master_rec.template_name,
                master_rec.template_key::template_key_enum, master_rec.template_type,
                master_rec.sandbox_template_id, master_rec.category, master_rec.template_usage_type,
                master_rec.template_access_key::communication_template_access_key_enum,
                prog_rec.program_id, true, master_rec.display_order,
                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
            )
            RETURNING id INTO new_template_id;

            IF master_rec.merge_field_map IS NOT NULL
               AND jsonb_array_length(master_rec.merge_field_map) > 0 THEN
                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_rec.merge_field_map)
                LOOP
                    INSERT INTO merge_info_answer_location_map (
                        key_name, template_id, is_common, source_table, source_column,
                        data_type, format_type, is_nullable, default_value, description,
                        is_active, created_at, updated_at
                    ) VALUES (
                        merge_field->>'keyName',
                        new_template_id,
                        COALESCE((merge_field->>'isCommon')::boolean,   false),
                        merge_field->>'sourceTable',
                        merge_field->>'sourceColumn',
                        merge_field->>'dataType',
                        merge_field->>'formatType',
                        COALESCE((merge_field->>'isNullable')::boolean, true),
                        merge_field->>'defaultValue',
                        merge_field->>'description',
                        true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    );
                END LOOP;
            END IF;

            cloned_count := cloned_count + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE 'PAP email templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, category, template_id, sandbox_template_id,
       jsonb_array_length(merge_field_map) AS merge_field_count, display_order, is_active
FROM communication_templates_master
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL)
ORDER BY display_order;

SELECT h.program_id, h.template_key, h.template_access_key, h.is_enabled,
       COUNT(m.id) AS merge_field_count
FROM hdb_communication_templates h
LEFT JOIN merge_info_answer_location_map m ON m.template_id = h.id
WHERE h.template_key::text LIKE 'PAP\_%'
GROUP BY h.program_id, h.template_key, h.template_access_key, h.is_enabled
ORDER BY h.program_id, h.template_key;
