-- =====================================================
-- PT_ENTRAINMENT — Program Date merge field format update (R1023)
-- 2026-08-20
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- Config only. Idempotent (re-running produces the same end state).
-- =====================================================
--
-- Standalone patch for the two LIVE PT_ENTRAINMENT communication_templates_master rows
-- (from database/program-configuration/17_master_seed_data_entrainment.sql, already executed)
-- whose merge_field_map still points "Email_Data_Program_Date" at the shared
-- formatProgramDateRange computed field (short "DD-MM-YYYY" format, also used by HDB/MSD).
-- Repoints it to the new formatEntrainmentProgramDateRange computed field
-- (src/communication/service/communication-merge-data.service.ts), which formats as the full
-- weekday date, e.g. "Thursday, 04 September 2025 to Sunday, 07 September 2025" — per explicit
-- instruction this format change is Entrainment-only; formatProgramDateRange itself (and
-- HDB/MSD's short format) is untouched.
--
-- Not editing 05master-communications-full-refresh.sql's own (already-corrected) copies in
-- place — that file is a full soft-delete-and-recreate of all 23 PT_ENTRAINMENT communication
-- templates and hasn't run yet; this patch targets the two rows as they exist RIGHT NOW on any
-- environment that has only run file 17, same rationale as
-- database/migrations/2026-08-08-02-pt-pap-success-screen-copy-update.sql patching PT_PAP's
-- already-executed workflow file rather than the draft superseding it.
--
-- Scope — 2 rows, both program_type_id = PT_ENTRAINMENT, both keyName "Email_Data_Program_Date":
--   1) ENT_REGISTRATION_COMPLETED_EMAIL (template_access_key REGISTRATION_COMPLETED_EMAIL_SEEKER)
--   2) ENT_OFFLINE_PAYMENT_ACKNOWLEDGEMENT_EMAIL (template_access_key
--      PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER)
-- No other row in this table has keyName "Email_Data_Program_Date" AND
-- sourceColumn "formatProgramDateRange" scoped to PT_ENTRAINMENT (confirmed by grep against both
-- file 17 and the not-yet-run full-refresh draft), so the WHERE clause below (program_type_id +
-- keyName + sourceColumn match) cannot touch HDB/MSD/PAP/TAT rows even without an explicit
-- template_key filter.
--
-- Targets only the matching element inside merge_field_map (a jsonb array), via
-- jsonb_agg + CASE over jsonb_array_elements, leaving every other element (reg_fullname,
-- course_name, Email_Data_Checkin_and_Checkout, pay_method, etc.) untouched. Mirrors PT_PAP's
-- success-screen-copy-update.sql style.
--
-- PART 2 covers the per-program CLONES: communication_templates_master.merge_field_map is a
-- jsonb blueprint that database/program-configuration/09_clone-templates-to-existing-programs.sql
-- (and the live clone-on-program-create flow) explodes into one normalized row per key in
-- merge_info_answer_location_map, keyed by template_id -> hdb_communication_templates.id — NOT
-- by program_type, so PART 1 alone does not affect any Entrainment program whose templates were
-- already cloned before this patch runs. PART 2 updates those existing clone rows directly
-- (source_column / format_type columns, not jsonb), scoped through hdb_communication_templates
-- -> program_v1 -> program_type_v1 = 'PT_ENTRAINMENT', so HDB/MSD/PAP/TAT clones are unaffected
-- regardless of key_name collisions in that table (key_name is UNIQUE per the entity, but scoped
-- per template_id, so this cannot cross into another program type's row).
-- =====================================================

-- Preview: master rows about to be updated
SELECT id, template_key, template_access_key, merge_field_map
FROM communication_templates_master
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
  AND merge_field_map @> '[{"keyName": "Email_Data_Program_Date", "sourceColumn": "formatProgramDateRange"}]'::jsonb;

-- PART 1: communication_templates_master (the blueprint used by any FUTURE clone)
UPDATE communication_templates_master
SET merge_field_map = (
        SELECT jsonb_agg(
            CASE
                WHEN elem->>'keyName' = 'Email_Data_Program_Date'
                     AND elem->>'sourceColumn' = 'formatProgramDateRange' THEN
                    jsonb_set(
                        jsonb_set(
                            elem,
                            '{sourceColumn}',
                            to_jsonb('formatEntrainmentProgramDateRange'::text)
                        ),
                        '{formatType}',
                        to_jsonb('custom:full-date'::text)
                    )
                ELSE elem
            END
        )
        FROM jsonb_array_elements(merge_field_map) AS elem
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
  AND merge_field_map @> '[{"keyName": "Email_Data_Program_Date", "sourceColumn": "formatProgramDateRange"}]'::jsonb;

-- Preview: existing per-program clone rows about to be updated
SELECT m.id, m.key_name, m.source_column, m.format_type, hct.program_id, hct.template_key
FROM merge_info_answer_location_map m
JOIN hdb_communication_templates hct ON hct.id = m.template_id
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_ENTRAINMENT'
  AND m.key_name = 'Email_Data_Program_Date'
  AND m.source_column = 'formatProgramDateRange';

-- PART 2: merge_info_answer_location_map (the already-cloned per-program rows)
UPDATE merge_info_answer_location_map m
SET source_column = 'formatEntrainmentProgramDateRange',
    format_type = 'custom:full-date',
    updated_at = CURRENT_TIMESTAMP
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 m.template_id = hct.id
  AND pt.key = 'PT_ENTRAINMENT'
  AND m.key_name = 'Email_Data_Program_Date'
  AND m.source_column = 'formatProgramDateRange';

-- =====================================================
-- VERIFICATION QUERY (commented out — uncomment to run manually after migration)
-- =====================================================
-- SELECT id, template_key, template_access_key, merge_field_map
-- FROM communication_templates_master
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
--   AND merge_field_map @> '[{"keyName": "Email_Data_Program_Date"}]'::jsonb;
--
-- SELECT m.id, m.key_name, m.source_column, m.format_type, hct.program_id
-- FROM merge_info_answer_location_map m
-- JOIN hdb_communication_templates hct ON hct.id = m.template_id
-- 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_ENTRAINMENT' AND m.key_name = 'Email_Data_Program_Date';
