-- ============================================================
-- Migration: PAP-007 — Delete the PT_PAP "offline payment confirmed" communication (both channels)
-- Date: 2026-08-08
--
--   PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER  (email,    pap offline confirmed)
--   PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER   (whatsapp, pap_offline_registration_confirmation)
--
-- Hard delete — the rows are physically removed. THERE IS NO UNDO; take a backup of the three
-- tables below if this is being run against production.
--
-- Scoped to PT_PAP by the PAP_ template_key prefix. The underlying access keys
-- (OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER / _WATI_SEEKER) are shared with other program types —
-- notably PT_TAT's TAT_OFFLINE_PAYMENT_REALIZATION_WHATSAPP — so nothing here touches them.
--
-- Order matters: merge_info_answer_location_map.template_id is a NOT NULL FK to
-- hdb_communication_templates.id with no ON DELETE CASCADE, so the children must go first or the
-- second DELETE fails on a foreign-key violation.
--
-- No deleted_at guard anywhere — rows already soft-deleted by an earlier run are removed too.
--
-- Once removed, the send sites log "template not configured, skipping" and move on. No error.
-- Idempotent: a re-run deletes nothing.
--
-- Seeds 56 and 58 have already had these two templates removed, so nothing recreates them.
-- ============================================================

-- ------------------------------------------------
-- 1. Merge-field rows belonging to the cloned templates (FK children — must go first)
-- ------------------------------------------------
DELETE FROM merge_info_answer_location_map m
USING hdb_communication_templates h
WHERE m.template_id = h.id
  AND h.template_key::text IN (
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
      );

-- ------------------------------------------------
-- 2. Per-program cloned rows
-- ------------------------------------------------
DELETE FROM hdb_communication_templates h
USING 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 h.template_key::text IN (
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
      );

-- ------------------------------------------------
-- 3. Master rows
-- ------------------------------------------------
DELETE FROM communication_templates_master
WHERE template_key IN (
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
      )
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP');

-- ------------------------------------------------
-- Verification — all three counts must be 0.
-- ------------------------------------------------
SELECT 'master rows' AS what, COUNT(*) AS remaining
FROM communication_templates_master
WHERE template_key IN (
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
      )
UNION ALL
SELECT 'cloned rows', COUNT(*)
FROM hdb_communication_templates
WHERE template_key::text IN (
        'PAP_OFFLINE_PAYMENT_COMPLETED_EMAIL_SEEKER',
        'PAP_OFFLINE_PAYMENT_COMPLETED_WATI_SEEKER'
      )
UNION ALL
SELECT 'orphaned merge fields', COUNT(*)
FROM merge_info_answer_location_map m
LEFT JOIN hdb_communication_templates h ON h.id = m.template_id
WHERE h.id IS NULL;

-- PT_PAP's surviving templates — expect 5 email + 5 whatsapp, none of them the offline-confirmed pair.
SELECT template_type, template_key, template_access_key, display_order
FROM communication_templates_master
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_PAP' AND deleted_at IS NULL)
ORDER BY template_type, display_order;
