-- ============================================================
-- Migration: PAP-006 — PT_PAP Invoice WhatsApp template name v1 -> v2
-- Date: 2026-08-08
--
--   pap_tax_invoice_v1 -> pap_tax_invoice_v2   (PAP_INVOICE_WATI_SEEKER)
--
-- Standalone bump for databases that already ran 59_update_pap_wati_template_ids.sql while it
-- still landed on v1. For WhatsApp rows template_id holds the WATI template NAME, and it lives in
-- two places — the master row (what future clones copy) and the per-program cloned rows (what the
-- sends actually read). Both are updated here; updating only the master would leave every
-- existing PAP program still sending on v1.
--
-- Idempotent: both UPDATEs match on the old value, so a re-run is a no-op.
-- ============================================================

-- 1. Master row
UPDATE communication_templates_master
SET template_id = 'pap_tax_invoice_v2',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key    = 'PAP_INVOICE_WATI_SEEKER'
  AND template_type   = 'whatsapp'
  AND template_id     = 'pap_tax_invoice_v1'
  AND 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 = 'pap_tax_invoice_v2',
    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_INVOICE_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
  AND h.template_id        = 'pap_tax_invoice_v1';

-- ------------------------------------------------
-- Verification — expect pap_tax_invoice_v2 on the master row and on every PAP program's clone.
-- ------------------------------------------------
SELECT 'master' AS scope, NULL::int AS program_id, template_id
FROM communication_templates_master
WHERE template_key    = 'PAP_INVOICE_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
FROM hdb_communication_templates h
WHERE h.template_key::text = 'PAP_INVOICE_WATI_SEEKER'
  AND h.template_type      = 'whatsapp'
ORDER BY scope, program_id;
