-- ================================================
-- Migration: Rename TAT WhatsApp templates + session_time merge field
-- Date: 2026-06-25
--
-- Two changes for each of the two TAT registration WhatsApp templates:
--
--   1. Provider (WATI) template id:
--        otat_reg_confirmed_v12        -> tat_reg_confirmed_v11
--        otat_reg_acknowledgement_v12  -> tat_reg_acknowledgement_v1
--
--   2. Merge field:
--        keyName      session_time   -> session_days_and_time
--        sourceColumn getSessionTime -> getSessionDaysAndTime
--      The new computed function renders the pluralized weekday names followed by
--      the session time in parentheses, e.g.
--        "Mondays, Tuesdays and Wednesdays (7:00 p.m. to 9:00 p.m. IST)".
--
-- Templates are matched by their stable `template_key` (NOT by template_id,
-- which is what this migration changes):
--   TAT_ONLINE_CONFIRMATION_WHATSAPP      (confirmed)
--   TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP  (acknowledgement)
--
-- Updates all three layers:
--   1. communication_templates_master  -> template_id + merge_field_map (JSONB)
--   2. hdb_communication_templates      -> cloned template_id
--   3. merge_info_answer_location_map   -> runtime key_name (what send time reads)
--
-- Safe to re-run (idempotent).
-- ================================================

-- ------------------------------------------------
-- PART 1: Master templates
-- ------------------------------------------------

-- 1a. Provider template id
UPDATE communication_templates_master
SET template_id = 'tat_reg_confirmed_v11',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_ONLINE_CONFIRMATION_WHATSAPP'
  AND template_id IN ('otat_reg_confirmed_v12', 'tat_reg_confirmed_v11');

UPDATE communication_templates_master
SET template_id = 'tat_reg_acknowledgement_v1',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP'
  AND template_id IN ('otat_reg_acknowledgement_v12', 'tat_reg_acknowledgement_v1');

-- 1b. Rename the session_time merge field (keyName + sourceColumn)
UPDATE communication_templates_master ctm
SET merge_field_map = (
        SELECT jsonb_agg(
            CASE
                WHEN elem->>'keyName' = 'session_time'
                    THEN jsonb_set(
                             jsonb_set(elem, '{keyName}', '"session_days_and_time"'),
                             '{sourceColumn}', '"getSessionDaysAndTime"'
                         )
                ELSE elem
            END
        )
        FROM jsonb_array_elements(ctm.merge_field_map) AS elem
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE ctm.template_key IN ('TAT_ONLINE_CONFIRMATION_WHATSAPP', 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP')
  AND ctm.merge_field_map @> '[{"keyName": "session_time"}]';

-- ------------------------------------------------
-- PART 2: Cloned templates (per-program copies)
-- ------------------------------------------------
UPDATE hdb_communication_templates
SET template_id = 'tat_reg_confirmed_v11',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_ONLINE_CONFIRMATION_WHATSAPP'
  AND template_id IN ('otat_reg_confirmed_v12', 'tat_reg_confirmed_v11');

UPDATE hdb_communication_templates
SET template_id = 'tat_reg_acknowledgement_v1',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP'
  AND template_id IN ('otat_reg_acknowledgement_v12', 'tat_reg_acknowledgement_v1');

-- ------------------------------------------------
-- PART 3: Already-cloned runtime rows (read at send time)
-- Rename key_name session_time -> session_days_and_time and repoint source_column
-- getSessionTime -> getSessionDaysAndTime for the clones belonging to these two
-- templates. Matched by clone template_key.
-- ------------------------------------------------
UPDATE merge_info_answer_location_map malm
SET key_name      = 'session_days_and_time',
    source_column = 'getSessionDaysAndTime',
    updated_at    = CURRENT_TIMESTAMP
WHERE malm.key_name = 'session_time'
  AND malm.template_id IN (
        SELECT id
        FROM hdb_communication_templates
        WHERE template_key IN ('TAT_ONLINE_CONFIRMATION_WHATSAPP', 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP')
    );

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
-- SELECT template_key, template_id, merge_field_map
-- FROM communication_templates_master
-- WHERE template_key IN ('TAT_ONLINE_CONFIRMATION_WHATSAPP', 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP');
--
-- SELECT template_key, template_id
-- FROM hdb_communication_templates
-- WHERE template_key IN ('TAT_ONLINE_CONFIRMATION_WHATSAPP', 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP');
--
-- SELECT malm.key_name, malm.source_column, t.template_key, t.template_id
-- FROM merge_info_answer_location_map malm
-- JOIN hdb_communication_templates t ON t.id = malm.template_id
-- WHERE t.template_key IN ('TAT_ONLINE_CONFIRMATION_WHATSAPP', 'TAT_OFFLINE_ACKNOWLEDGEMENT_WHATSAPP')
--   AND malm.key_name IN ('session_time', 'session_days_and_time');
