-- ============================================================
-- Migration: PAP-008 — PT_PAP "seat offered" WhatsApp: name -> v3, plus a reg_email merge field
-- Date: 2026-08-08
--
--   PAP_BLESSED_WATI_SEEKER (access key BLESSED_WATI_SEEKER)
--     template name : pap_seat_offered / pap_seat_offered_v2  ->  pap_seat_offered_v3
--     merge fields  : + reg_email  (hdb_program_registration.email_address)
--
-- The template name is matched from BOTH earlier values so this lands on v3 whether or not
-- 59_update_pap_wati_template_ids.sql has already run.
--
-- A merge field lives in two places and BOTH must be updated:
--   1. communication_templates_master.merge_field_map — the jsonb blueprint future clones expand
--   2. merge_info_answer_location_map                 — one row per cloned template; this is what
--      the merge engine actually reads at send time
-- Updating only the master would leave existing PAP programs sending without reg_email, because
-- 58_pap-wati-template-seed.sql's clone block skips programs whose template row already exists.
--
-- Idempotent: the name UPDATEs match on the old values, and both merge-field steps are guarded
-- against re-adding a reg_email that is already present.
-- ============================================================

-- ------------------------------------------------
-- 1. Master row — template name
-- ------------------------------------------------
UPDATE communication_templates_master
SET template_id = 'pap_seat_offered_v3',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key    = 'PAP_BLESSED_WATI_SEEKER'
  AND template_type   = 'whatsapp'
  AND template_id     IN ('pap_seat_offered', 'pap_seat_offered_v2')
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL);

-- ------------------------------------------------
-- 2. Master row — append reg_email to the merge_field_map blueprint
-- ------------------------------------------------
UPDATE communication_templates_master
SET merge_field_map = merge_field_map || '[
        {"keyName": "reg_email", "sourceTable": "hdb_program_registration", "sourceColumn": "email_address", "dataType": "string", "isNullable": false}
    ]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key    = 'PAP_BLESSED_WATI_SEEKER'
  AND template_type   = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL)
  AND NOT (merge_field_map @> '[{"keyName": "reg_email"}]'::jsonb);

-- ------------------------------------------------
-- 3. Per-program cloned rows — template name
-- ------------------------------------------------
UPDATE hdb_communication_templates h
SET template_id = 'pap_seat_offered_v3',
    updated_at  = CURRENT_TIMESTAMP
FROM program_v1 p
JOIN program_type_v1 pt ON pt.id = p.program_type_id
WHERE h.program_id       = p.id
  AND pt.key             = 'PT_PAP'
  AND pt.deleted_at      IS NULL
  AND p.deleted_at       IS NULL
  AND h.template_key::text = 'PAP_BLESSED_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
  AND h.template_id        IN ('pap_seat_offered', 'pap_seat_offered_v2');

-- ------------------------------------------------
-- 4. Per-program cloned rows — add the reg_email merge field
--    (one row per clone that does not already have it)
-- ------------------------------------------------
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
)
SELECT 'reg_email', h.id, false, 'hdb_program_registration', 'email_address',
       'string', NULL, false, NULL, NULL,
       true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM hdb_communication_templates h
JOIN program_v1 p       ON p.id  = h.program_id
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
  AND h.deleted_at       IS NULL
  AND h.template_key::text = 'PAP_BLESSED_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
  AND NOT EXISTS (
        SELECT 1 FROM merge_info_answer_location_map m
        WHERE m.template_id = h.id
          AND m.key_name    = 'reg_email'
      );

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
-- Expect pap_seat_offered_v3 everywhere, and a merge_field_map of 4 entries on the master.
SELECT 'master' AS scope, NULL::int AS program_id, template_id,
       jsonb_array_length(merge_field_map) AS merge_field_count
FROM communication_templates_master
WHERE template_key    = 'PAP_BLESSED_WATI_SEEKER'
  AND template_type   = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL)
UNION ALL
SELECT 'clone', h.program_id, h.template_id, COUNT(m.id)
FROM hdb_communication_templates h
LEFT JOIN merge_info_answer_location_map m ON m.template_id = h.id
WHERE h.template_key::text = 'PAP_BLESSED_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
GROUP BY h.program_id, h.template_id
ORDER BY scope, program_id;

-- The four merge fields each clone should now carry.
SELECT h.program_id, m.key_name, m.source_table, m.source_column
FROM hdb_communication_templates h
JOIN merge_info_answer_location_map m ON m.template_id = h.id
WHERE h.template_key::text = 'PAP_BLESSED_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
ORDER BY h.program_id, m.key_name;
