-- ============================================================
-- Remap the reg_email merge field for TAT Offline Payment
-- Realization WhatsApp templates that are ALREADY on _v6.
--
-- 18a gated every statement on template_id = '..._v5', so once a
-- clone was bumped to _v6 the merge-field remap could no longer
-- reach it. This script fixes those rows by gating on the OLD
-- merge-field source instead of the template version.
--
-- reg_email source moves:
--   hdb_program_registration.email_address
--     -> hdb_registration_invoice_detail.invoice_email
--   (key_name stays "reg_email")
--
-- Two locations:
--   1. communication_templates_master.merge_field_map  (inline JSONB)
--   2. merge_info_answer_location_map                   (per-program rows,
--      joined to the clone via template_id = hdb_communication_templates.id)
--
-- Idempotent: gating on the OLD source means re-runs are no-ops.
-- ============================================================

-- 1. Master template (inline JSONB merge_field_map)
UPDATE communication_templates_master
SET
    merge_field_map = (
        SELECT jsonb_agg(
            CASE
                WHEN elem->>'keyName' = 'reg_email' THEN
                    elem
                      || '{"sourceTable":"hdb_registration_invoice_detail","sourceColumn":"invoice_email"}'::jsonb
                ELSE elem
            END
        )
        FROM jsonb_array_elements(merge_field_map) AS elem
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
  AND merge_field_map @> '[{"keyName":"reg_email","sourceTable":"hdb_program_registration"}]'::jsonb;

-- 2. Per-program cloned rows (merge_info_answer_location_map)
UPDATE merge_info_answer_location_map mial
SET
    source_table = 'hdb_registration_invoice_detail',
    source_column = 'invoice_email',
    updated_at = CURRENT_TIMESTAMP
FROM hdb_communication_templates hct
INNER JOIN program_v1 p ON hct.program_id = p.id
INNER JOIN program_type_v1 pt ON p.program_type_id = pt.id
WHERE mial.template_id = hct.id
  AND mial.key_name = 'reg_email'
  AND mial.source_table = 'hdb_program_registration'
  AND pt.key = 'PT_TAT'
  AND hct.template_key::text = 'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP'
  AND hct.template_type = 'whatsapp';
