-- ================================================
-- Migration: PAP-004 — PT_PAP seeker WhatsApp (WATI) templates + clone to PT_PAP programs
-- Date: 2026-08-08
--
-- The WhatsApp counterpart of the six emails seeded in 56_pap-email-template-seed.sql. Same six
-- events, same access keys' WATI variants, so both channels fire from the same send sites:
--
--   1. Registration completion        REGISTRATION_COMPLETED_WATI_SEEKER          RegistrationService
--   2. Registration approved          BLESSED_WATI_SEEKER                         RegistrationApprovalService
--   3. Online registration confirmed  ONLINE_PAYMENT_COMPLETED_WATI_SEEKER        PaymentService.handlePaymentConfirmation
--   4. Invoice                        INVOICE_WATI_SEEKER                         InvoiceService.sendInvoiceEmail
--   5. Offline payment acknowledgement PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER PaymentService (offline submit)
--   6. Offline payment confirmed      OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER       PaymentService.handlePaymentConfirmation
--
-- For WhatsApp rows, template_id holds the WATI template NAME (not a Zepto id) and
-- sandbox_template_id is NULL — WATI has no sandbox equivalent. Same convention as the TAT
-- WhatsApp rows in files 18 and 32.
--
-- Merge keyNames differ from the email templates for the same values — the WATI templates use
-- prog_dates / prog_venue / payment_mode where the emails use program_dates / program_venue /
-- pay_method. Seeded exactly as the WATI templates declare them; the sourceColumn behind each is
-- the same computed function, so both channels render identical text.
--
-- Prerequisites: run 55 + 56 (email side, adds the computed-field-backed masters) and
-- 57_pap-wati-enums.sql (its enum values must be committed) first.
-- Idempotent — safe to re-run.
-- ================================================

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

-- 1. Registration completion — seeker submitted, registration is with approval.
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_WATI_SEEKER',
    'PAP Registration Completion (Sent for Approval) - WhatsApp',
    'whatsapp',
    'pap_registration_submit',
    NULL,
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"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,
    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;

-- 2. Registration approved — seat offered, carries the 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 (
    'PAP_BLESSED_WATI_SEEKER',
    'PAP Registration Approved (Seat Offered) - WhatsApp',
    'whatsapp',
    'pap_seat_offered_v3',
    NULL,
    'hdb_blessed',
    'general',
    'BLESSED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"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},
        {"keyName": "payment_link", "sourceTable": "computed", "sourceColumn": "generatePaymentLink", "dataType": "string", "formatType": "url", "isNullable": false},
        {"keyName": "reg_email",    "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "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;

-- 3. Online registration confirmed — online payment captured. Seat is allocated by now, so
--    prog_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_COMPLETED_WATI_SEEKER',
    'PAP Online Registration Confirmed - WhatsApp',
    'whatsapp',
    'pap_online_registration_confirm_v1',
    NULL,
    'online_payment_comfirmed',
    'general',
    'ONLINE_PAYMENT_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"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},
        {"keyName": "reg_number",         "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "payment_mode",       "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "prog_dates",         "sourceTable": "computed", "sourceColumn": "formatProgramDayDates", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "prog_venue",         "sourceTable": "computed", "sourceColumn": "getProgramField:venue", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkin_time_date",  "sourceTable": "computed", "sourceColumn": "getCheckInWindow",      "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkout_time_date", "sourceTable": "computed", "sourceColumn": "getCheckOutDeadline",   "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "reg_email",          "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "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;

-- 4. Invoice — sent alongside the invoice email from InvoiceService.sendInvoiceEmail.
--    invoice_email is the billing email captured on the invoice, which can differ from the
--    registration's own email_address (used by reg_email above).
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_WATI_SEEKER',
    'PAP Tax Invoice - WhatsApp',
    'whatsapp',
    'pap_tax_invoice_v2',
    NULL,
    'invoice',
    'general',
    'INVOICE_WATI_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": "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},
        {"keyName": "invoice_email",           "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_email", "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;

-- 5. Offline payment acknowledgement — offline payment submitted, realization pending.
--    Same merge set as template 3.
--    NOTE: the supplied spec listed "prog_dates" twice for this message and never listed
--    prog_venue. Seeded here as prog_dates + prog_venue, matching template 3 (a JSON object
--    cannot carry a duplicate key, and the WATI template is otherwise identical to the online
--    confirmation). Confirm against the WATI template body before go-live.
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_WATI_SEEKER',
    'PAP Offline Payment Acknowledgement - WhatsApp',
    'whatsapp',
    'pap_offline_registration_acknowledgment_v1',
    NULL,
    'payment_acknowledgement',
    'general',
    'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"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},
        {"keyName": "reg_number",         "sourceTable": "hdb_program_registration", "sourceColumn": "registration_seq_number", "dataType": "string", "isNullable": false},
        {"keyName": "payment_mode",       "sourceTable": "hdb_registration_payment_detail", "sourceColumn": "payment_mode", "dataType": "string", "formatType": "conditional", "isNullable": true},
        {"keyName": "prog_dates",         "sourceTable": "computed", "sourceColumn": "formatProgramDayDates", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "prog_venue",         "sourceTable": "computed", "sourceColumn": "getProgramField:venue", "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkin_time_date",  "sourceTable": "computed", "sourceColumn": "getCheckInWindow",      "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "checkout_time_date", "sourceTable": "computed", "sourceColumn": "getCheckOutDeadline",   "dataType": "string", "isNullable": true, "defaultValue": ""},
        {"keyName": "reg_email",          "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "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;

-- 6. Offline payment confirmed — offline payment realized.
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_WATI_SEEKER',
    'PAP Offline Payment Confirmed - WhatsApp',
    'whatsapp',
    'pap_offline_registration_confirmation',
    NULL,
    'offline_payment_comfirmed',
    'general',
    'OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"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},
        {"keyName": "invoice_email", "sourceTable": "hdb_registration_invoice_detail", "sourceColumn": "invoice_email", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    12, 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, same as the email side — these are the core
-- transactional messages of the PAP registration flow.
-- ------------------------------------------------
DO $$
DECLARE
    pap_keys TEXT[] := ARRAY[
        'PAP_REGISTRATION_COMPLETED_WATI_SEEKER',
        'PAP_BLESSED_WATI_SEEKER',
        'PAP_ONLINE_PAYMENT_COMPLETED_WATI_SEEKER',
        'PAP_INVOICE_WATI_SEEKER',
        'PAP_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_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 WhatsApp templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
END $$;

-- ------------------------------------------------
-- Verification — both channels side by side, one row per event.
-- ------------------------------------------------
SELECT template_access_key, template_type, template_key, 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_type, 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_type, h.template_key, h.template_access_key, h.is_enabled
ORDER BY h.program_id, h.template_type, h.template_key;
