-- ================================================
-- Migration: Session dates + weekday names for TAT WhatsApp templates
-- Date: 2026-06-21
-- Switches the `session_dates` merge field sourceColumn from
-- `formatSessionDateRange` to `formatSessionDateAndDaysRange` for the
-- WhatsApp templates 'otat_reg_confirmed_v12' and 'otat_reg_acknowledgement_v12'.
-- The new computed function renders the grouped session dates followed by the
-- pluralized weekday names (e.g. "1, 2, 3 Jun 2026, Mondays, Tuesdays, Wednesdays").
--
-- Updates both layers:
--   1. communication_templates_master.merge_field_map (source of truth for clones)
--   2. merge_info_answer_location_map (what the runtime actually reads) for all
--      programs that already have these templates cloned.
-- Safe to re-run (idempotent).
-- ================================================

-- ------------------------------------------------
-- PART 1: Master template merge_field_map (JSONB)
-- ------------------------------------------------
UPDATE communication_templates_master ctm
SET merge_field_map = (
        SELECT jsonb_agg(
            CASE
                WHEN elem->>'keyName' = 'session_dates'
                    THEN jsonb_set(elem, '{sourceColumn}', '"formatSessionDateAndDaysRange"')
                ELSE elem
            END
        )
        FROM jsonb_array_elements(ctm.merge_field_map) AS elem
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE ctm.template_id IN ('otat_reg_confirmed_v12', 'otat_reg_acknowledgement_v12');

-- ------------------------------------------------
-- PART 2: Already-cloned runtime rows (read at send time)
-- ------------------------------------------------
UPDATE merge_info_answer_location_map malm
SET source_column = 'formatSessionDateAndDaysRange',
    updated_at = CURRENT_TIMESTAMP
WHERE malm.key_name = 'session_dates'
  AND malm.source_column = 'formatSessionDateRange'
  AND malm.template_id IN (
        SELECT id
        FROM hdb_communication_templates
        WHERE template_id IN ('otat_reg_confirmed_v12', 'otat_reg_acknowledgement_v12')
    );
