-- ============================================================
-- Update TAT Offline Payment Realization WhatsApp template_id
-- from v5 -> v6.
--
-- The template lives in two places:
--   1. communication_templates_master  (PT_TAT master row)
--   2. hdb_communication_templates      (per-program cloned rows,
--      one per TAT program — see 09_clone-templates-to-existing-programs.sql)
--
-- Both must be updated so existing programs pick up the new template id.
-- Idempotent: matching on the old value means re-runs are no-ops.
-- ============================================================

-- 1. Master template
UPDATE communication_templates_master
SET
    template_id = 'tat_offline_payment_realization_v6',
    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 template_id = 'tat_offline_payment_realization_v5';

-- 2. Per-program cloned rows (inner TAT tables)
UPDATE hdb_communication_templates hct
SET
    template_id = 'tat_offline_payment_realization_v6',
    updated_at = CURRENT_TIMESTAMP
FROM program_v1 p
INNER JOIN program_type_v1 pt ON p.program_type_id = pt.id
WHERE hct.program_id = p.id
  AND pt.key = 'PT_TAT'
  AND hct.template_key::text = 'TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP'
  AND hct.template_type = 'whatsapp'
  AND hct.template_id = 'tat_offline_payment_realization_v5';
