-- ============================================================
-- Update: point the WELCOME templates' `session_date` merge field at the
-- `formatSessionDateRange` computed function instead of `getTargetSessionDate`.
--
-- Templates affected (master + per-program cloned rows), PT_TAT:
--   TAT_WELCOME_EMAIL_SEEKER  (email)
--   TAT_WELCOME_WATI_SEEKER   (whatsapp)
--
-- Why: Welcome is a program-level send — it introduces the whole program, not one
-- sitting. `getTargetSessionDate` resolves the ONE target session threaded through
-- extraMergeContext.sessionId and renders a single long date ("6 July 2025"), which
-- on a Welcome send is either arbitrary or empty. `formatSessionDateRange` lists
-- every session of the registrant's (allocated) program, de-duplicated, grouped by
-- month and joined with an "&" before the final day — e.g.
--   "August 18, 19 & 26, September 1, 3 & 5, 2026"
-- and falls back to the program-level date range when no session has a start date.
-- See CommunicationMergeDataService.formatSessionDateRangeField /
-- formatGroupedSessionDates.
--
-- Only the `sourceColumn` changes. keyName, sourceTable ('computed'), dataType,
-- isCommon and isNullable are all left exactly as they were, and no field is added
-- or removed — so the WATI/Zepto templates themselves need no change, only the value
-- substituted into the existing `session_date` variable.
--
-- The field lives in two places:
--   1. communication_templates_master.merge_field_map  (the JSONB array)
--   2. merge_info_answer_location_map                  (one row per cloned template field)
-- Both are updated so existing programs resolve the new function.
--
-- Surgical (touches only `session_date` on only these two templates) and
-- idempotent — safe to re-run.
-- ============================================================

-- 1. Master templates — rewrite just the session_date element's sourceColumn,
--    preserving array order and every other element untouched.
UPDATE communication_templates_master
SET
    merge_field_map = (
        SELECT jsonb_agg(
                   CASE
                       WHEN elem->>'keyName' = 'session_date'
                           THEN jsonb_set(elem, '{sourceColumn}', '"formatSessionDateRange"'::jsonb)
                       ELSE elem
                   END
                   ORDER BY idx
               )
        FROM jsonb_array_elements(merge_field_map) WITH ORDINALITY AS t(elem, idx)
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE template_key IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER')
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
  AND merge_field_map @> '[{"keyName": "session_date"}]'::jsonb;

-- 2. Per-program cloned rows — repoint the session_date mapping.
UPDATE merge_info_answer_location_map
SET source_column = 'formatSessionDateRange',
    updated_at    = CURRENT_TIMESTAMP
WHERE key_name = 'session_date'
  AND template_id IN (
      SELECT hct.id
      FROM hdb_communication_templates hct
      JOIN program_v1 p       ON p.id  = hct.program_id
      JOIN program_type_v1 pt ON pt.id = p.program_type_id
      WHERE pt.key = 'PT_TAT'
        AND hct.template_key::text IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER')
  );

-- ------------------------------------------------
-- Verification — session_date should read formatSessionDateRange everywhere,
-- and no other merge field should have changed.
-- ------------------------------------------------
SELECT template_key,
       template_type,
       elem->>'keyName'      AS key_name,
       elem->>'sourceColumn' AS source_column
FROM communication_templates_master,
     jsonb_array_elements(merge_field_map) AS elem
WHERE template_key IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER')
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
ORDER BY template_key, template_type, key_name;

SELECT hct.template_key::text AS template_key,
       hct.template_type,
       hct.program_id,
       mf.key_name,
       mf.source_column
FROM hdb_communication_templates hct
JOIN merge_info_answer_location_map mf ON mf.template_id = hct.id
WHERE hct.template_key::text IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER')
  AND mf.key_name = 'session_date'
ORDER BY hct.template_key::text, hct.template_type, hct.program_id;
