-- ================================================
-- Migration: PAP-010 — PT_PAP RM WhatsApp templates + clone to PT_PAP programs
-- Date: 2026-08-08
--
-- The two Relational-Manager notifications for PT_PAP. Unlike the seeker templates in file 58,
-- these go to the registration's rmContactUser, not the seeker.
--
--   7. Seeker submitted basic details  REGISTRATION_COMPLETED_WATI_RM
--                                      -> RegistrationService.register() / .update()
--   8. Seeker completed payment        REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM
--                                      -> PaymentService.handlePaymentConfirmation
--
-- #8 REQUIRES THE ACCOMPANYING CODE CHANGE. Every existing send site for
-- REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM was gated on `!program.requiresApproval`, so it
-- could never fire for PT_PAP (requires_approval = true). handlePaymentConfirmation now sends it
-- when `isOnlinePayment || requiresApproval` — which adds PAP on both the online-capture and
-- offline-realization paths while leaving non-approval program types exactly as they were (they
-- are still notified at payment INITIATION, so announcing the offline realization too would
-- double-notify them).
--
-- For WhatsApp rows, template_id holds the WATI template NAME and sandbox_template_id is NULL.
--
-- Merge fields:
--   rm_name              getRmContactUserName (computed) — the RM being messaged
--   reg_name / participant_name        hdb_program_registration.full_name
--   reg_email / participant_email      hdb_program_registration.email_address
--   reg_mobile / participant_mobile    hdb_program_registration.mobile_number
--   order_id             hdb_program_registration.registration_seq_number
--                        (same mapping PT_TAT's RM template uses — see file 20)
--   program_name         getProgramField:name
--   payment_mode         hdb_registration_payment_detail.payment_mode
-- The two templates use different keyNames for the same seeker columns (reg_* vs participant_*);
-- seeded exactly as each WATI template declares them.
--
-- Prerequisite: run 63_pap-rm-wati-enums.sql first (its enum values must be committed).
-- Idempotent — safe to re-run.
-- ================================================

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

-- 7. Seeker submitted basic details — registration is now with approval, RM is told it arrived.
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_RM',
    'PAP Seeker Registration Submitted - WhatsApp to RM',
    'whatsapp',
    'pap_rm_registration_completed',
    NULL,
    'registration_completed',
    'general',
    'REGISTRATION_COMPLETED_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "rm_name",      "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "reg_name",     "sourceTable": "hdb_program_registration", "sourceColumn": "full_name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_mobile",   "sourceTable": "hdb_program_registration", "sourceColumn": "mobile_number", "dataType": "string", "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name", "dataType": "string", "isNullable": false},
        {"keyName": "reg_email",    "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    13, 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. Seeker completed payment — online capture or offline realization.
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_WITH_PAYMENT_WATI_RM',
    'PAP Seeker Payment Completed - WhatsApp to RM',
    'whatsapp',
    'pap_rm_payment_completed',
    NULL,
    'registration_completed_with_payment',
    'general',
    'REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL),
    '[
        {"keyName": "rm_name",            "sourceTable": "computed", "sourceColumn": "getRmContactUserName", "dataType": "string", "isNullable": false},
        {"keyName": "program_name",       "sourceTable": "computed", "sourceColumn": "getProgramField:name", "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", "formatType": "conditional", "isNullable": true}
    ]'::jsonb,
    14, 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 seeker templates.
-- ------------------------------------------------
DO $$
DECLARE
    pap_keys TEXT[] := ARRAY[
        'PAP_REGISTRATION_COMPLETED_WATI_RM',
        'PAP_REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM'
    ];
    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 RM WhatsApp templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, category, template_id,
       jsonb_array_length(merge_field_map) AS merge_field_count, display_order, is_active
FROM communication_templates_master
WHERE template_key IN (
        'PAP_REGISTRATION_COMPLETED_WATI_RM',
        'PAP_REGISTRATION_COMPLETED_WITH_PAYMENT_WATI_RM'
      )
  AND 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.template_id, 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\_%WATI\_RM'
GROUP BY h.program_id, h.template_key, h.template_access_key, h.template_id, h.is_enabled
ORDER BY h.program_id, h.template_key;
