-- ============================================================
-- Migration: PAP-005 — Update four PT_PAP WhatsApp template names to their versioned form
-- Date: 2026-08-08
--
--   pap_seat_offered                        -> pap_seat_offered_v2
--   pap_online_registration_confirm         -> pap_online_registration_confirm_v1
--   pap_offline_registration_acknowledgment -> pap_offline_registration_acknowledgment_v1
--   pap_tax_invoice                         -> pap_tax_invoice_v1
--
-- Unchanged (no new version supplied): pap_registration_submit,
-- pap_offline_registration_confirmation.
--
-- For WhatsApp rows, template_id holds the WATI template NAME. The value lives in two places:
--   1. communication_templates_master  (the PT_PAP master row — what future clones copy)
--   2. hdb_communication_templates     (the per-program cloned rows — what sends actually read)
-- Both must be updated, or existing PAP programs keep sending on the old template name.
--
-- Idempotent: each UPDATE matches on the OLD value, so a re-run is a no-op. It is also safe to
-- run before 58_pap-wati-template-seed.sql (nothing matches yet) — 58 has since been updated to
-- seed these names directly, so a fresh database never needs this file.
-- ============================================================

-- ------------------------------------------------
-- 1. Master rows
-- ------------------------------------------------
UPDATE communication_templates_master m
SET template_id = v.new_name,
    updated_at  = CURRENT_TIMESTAMP
FROM (VALUES
    ('PAP_BLESSED_WATI_SEEKER',                        'pap_seat_offered',                        'pap_seat_offered_v2'),
    ('PAP_ONLINE_PAYMENT_COMPLETED_WATI_SEEKER',       'pap_online_registration_confirm',         'pap_online_registration_confirm_v1'),
    ('PAP_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER','pap_offline_registration_acknowledgment', 'pap_offline_registration_acknowledgment_v1'),
    ('PAP_INVOICE_WATI_SEEKER',                        'pap_tax_invoice',                         'pap_tax_invoice_v1')
) AS v(template_key, old_name, new_name)
WHERE m.template_key   = v.template_key
  AND m.template_type  = 'whatsapp'
  AND m.template_id    = v.old_name
  AND m.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL);

-- ------------------------------------------------
-- 2. Per-program cloned rows
-- ------------------------------------------------
UPDATE hdb_communication_templates h
SET template_id = v.new_name,
    updated_at  = CURRENT_TIMESTAMP
FROM (VALUES
    ('PAP_BLESSED_WATI_SEEKER',                        'pap_seat_offered',                        'pap_seat_offered_v2'),
    ('PAP_ONLINE_PAYMENT_COMPLETED_WATI_SEEKER',       'pap_online_registration_confirm',         'pap_online_registration_confirm_v1'),
    ('PAP_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER','pap_offline_registration_acknowledgment', 'pap_offline_registration_acknowledgment_v1'),
    ('PAP_INVOICE_WATI_SEEKER',                        'pap_tax_invoice',                         'pap_tax_invoice_v1')
) AS v(template_key, old_name, new_name),
     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 = v.template_key
  AND h.template_type      = 'whatsapp'
  AND h.template_id        = v.old_name;

-- ------------------------------------------------
-- Verification — every PAP WhatsApp name, master and clones.
-- Expect the four _v* names below plus the two unchanged ones.
-- ------------------------------------------------
SELECT 'master' AS scope, NULL::int AS program_id, template_key, template_id
FROM communication_templates_master
WHERE 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_key::text, h.template_id
FROM hdb_communication_templates h
WHERE h.template_type = 'whatsapp'
  AND h.template_key::text LIKE 'PAP\_%'
ORDER BY scope, program_id, template_key;
