-- ============================================================
-- Update: bump the WhatsApp (WATI) template_id to the new name for several PT_TAT templates.
-- Single place to maintain all WATI template_id renames (id only — merge fields unchanged).
--
--   template_key                         new template_id
--   -----------------------------------  ------------------------------
--   TAT_WELCOME_WATI_SEEKER              tat_program_welcome_v1
--   TAT_ABSENT_REGULAR_WATI_SEEKER       tat_session_absent_regular_v1
--   TAT_ABSENT_FINAL_WATI_SEEKER         tat_session_absent_final_v1
--   TAT_ABSENT_PRE_FINAL_WATI_SEEKER     tat_session_absent_pre_final_v1
--   TAT_PROGRAM_COMPLETION_WATI_SEEKER   tat_program_completion_v1
--
-- Each row lives in the master (communication_templates_master) and its per-program clones
-- (hdb_communication_templates) — both are updated. Matches on template_key, so re-runs just
-- re-set the same value (idempotent). To add another WATI id rename later, add one row to the
-- VALUES list below.
--
-- NOTE: the Invite (regular/final) WATI templates are updated by their own committed migrations
-- (40/41), which also rebuild the merge field set — they are intentionally not duplicated here.
-- ============================================================

DO $$
DECLARE
    tat_id  BIGINT := (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');
    mapping RECORD;
BEGIN
    FOR mapping IN
        SELECT * FROM (VALUES
            ('TAT_WELCOME_WATI_SEEKER',            'tat_program_welcome_v1'),
            ('TAT_ABSENT_REGULAR_WATI_SEEKER',     'tat_session_absent_regular_v1'),
            ('TAT_ABSENT_FINAL_WATI_SEEKER',       'tat_session_absent_final_v1'),
            ('TAT_ABSENT_PRE_FINAL_WATI_SEEKER',   'tat_session_absent_pre_final_v1'),
            ('TAT_PROGRAM_COMPLETION_WATI_SEEKER', 'tat_program_completion_v1')
        ) AS m(template_key, new_template_id)
    LOOP
        -- Master row
        UPDATE communication_templates_master
        SET template_id = mapping.new_template_id, updated_at = CURRENT_TIMESTAMP
        WHERE template_key = mapping.template_key
          AND template_type = 'whatsapp'
          AND program_type_id = tat_id;

        -- Per-program cloned rows
        UPDATE hdb_communication_templates hct
        SET template_id = mapping.new_template_id, updated_at = CURRENT_TIMESTAMP
        FROM program_v1 p
        JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE hct.program_id = p.id
          AND pt.key = 'PT_TAT'
          AND hct.template_key::text = mapping.template_key
          AND hct.template_type = 'whatsapp';
    END LOOP;
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_id, is_active
FROM communication_templates_master
WHERE template_key IN (
    'TAT_WELCOME_WATI_SEEKER',
    'TAT_ABSENT_REGULAR_WATI_SEEKER',
    'TAT_ABSENT_FINAL_WATI_SEEKER',
    'TAT_ABSENT_PRE_FINAL_WATI_SEEKER',
    'TAT_PROGRAM_COMPLETION_WATI_SEEKER'
)
ORDER BY template_key;

SELECT hct.template_key::text AS template_key, hct.program_id, hct.template_id
FROM hdb_communication_templates hct
WHERE hct.template_key::text IN (
    'TAT_WELCOME_WATI_SEEKER',
    'TAT_ABSENT_REGULAR_WATI_SEEKER',
    'TAT_ABSENT_FINAL_WATI_SEEKER',
    'TAT_ABSENT_PRE_FINAL_WATI_SEEKER',
    'TAT_PROGRAM_COMPLETION_WATI_SEEKER'
)
  AND hct.template_type = 'whatsapp'
ORDER BY hct.template_key::text, hct.program_id;
