-- ================================================
-- Migration: TAT Invoice Email — swap prg_programdates for session fields
-- Date: 2026-06-22
-- For the TAT_INVOICE_EMAIL template (PT_TAT), removes the legacy
-- `prg_programdates` merge field and adds `session_date`, `session_days`
-- and `session_time` — matching the session-based fields already used by
-- the TAT registration / payment acknowledgement emails.
--
--   session_days → getSessionDays           (e.g. "Mondays, Tuesdays")
--   session_date → formatSessionDateRange   (e.g. "1, 2, 3 Jun 2026")
--   session_time → getSessionTime           (e.g. "07:00 p.m. to 09:00 p.m.")
--
-- Updates both layers:
--   1. communication_templates_master.merge_field_map (source of truth for clones)
--   2. merge_info_answer_location_map (what the runtime reads) for all
--      programs that already have this template cloned.
-- Safe to re-run (idempotent).
-- ================================================

-- ------------------------------------------------
-- PART 1: Master template merge_field_map (JSONB)
-- Drop prg_programdates (and any stale session_* to keep re-runs clean),
-- then append the three session fields.
-- ------------------------------------------------
UPDATE communication_templates_master ctm
SET merge_field_map = (
        SELECT COALESCE(jsonb_agg(elem), '[]'::jsonb)
        FROM jsonb_array_elements(ctm.merge_field_map) AS elem
        WHERE elem->>'keyName' NOT IN (
            'prg_programdates', 'session_days', 'session_date', 'session_time'
        )
    ) || '[
        {"keyName": "session_days", "sourceTable": "computed", "sourceColumn": "getSessionDays", "dataType": "string", "isNullable": true},
        {"keyName": "session_date", "sourceTable": "computed", "sourceColumn": "formatSessionDateRange", "dataType": "string", "formatType": "custom:DD-MM-YYYY", "isNullable": false},
        {"keyName": "session_time", "sourceTable": "computed", "sourceColumn": "getSessionTime", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE ctm.template_key = 'TAT_INVOICE_EMAIL'
  AND ctm.program_type_id IN (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- ------------------------------------------------
-- PART 2a: Already-cloned runtime rows — drop prg_programdates
-- ------------------------------------------------
DELETE FROM merge_info_answer_location_map
WHERE key_name = 'prg_programdates'
  AND template_id IN (
        SELECT id
        FROM hdb_communication_templates
        WHERE template_key::text = 'TAT_INVOICE_EMAIL'
    );

-- ------------------------------------------------
-- PART 2b: Already-cloned runtime rows — add the three session fields
-- to every cloned TAT_INVOICE_EMAIL that does not already carry them.
-- ------------------------------------------------
INSERT INTO merge_info_answer_location_map (
    key_name,
    template_id,
    is_common,
    source_table,
    source_column,
    data_type,
    format_type,
    is_nullable,
    is_active,
    is_per_recipient,
    created_at,
    updated_at
)
SELECT
    field.key_name,
    t.id,
    false,
    field.source_table,
    field.source_column,
    field.data_type,
    field.format_type,
    field.is_nullable,
    true,
    false,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM hdb_communication_templates t
CROSS JOIN (
    VALUES
        ('session_days', 'computed', 'getSessionDays',         'string', NULL,                 true),
        ('session_date', 'computed', 'formatSessionDateRange', 'string', 'custom:DD-MM-YYYY',  false),
        ('session_time', 'computed', 'getSessionTime',         'string', NULL,                 true)
) AS field(key_name, source_table, source_column, data_type, format_type, is_nullable)
WHERE t.template_key::text = 'TAT_INVOICE_EMAIL'
  AND NOT EXISTS (
        SELECT 1
        FROM merge_info_answer_location_map m
        WHERE m.template_id = t.id
          AND m.key_name = field.key_name
    );

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------

-- Master merge_field_map after change
SELECT template_key, merge_field_map
FROM communication_templates_master
WHERE template_key = 'TAT_INVOICE_EMAIL'
  AND program_type_id IN (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- Runtime merge rows for cloned TAT_INVOICE_EMAIL templates
SELECT m.template_id, m.key_name, m.source_column, m.format_type, m.is_nullable
FROM merge_info_answer_location_map m
JOIN hdb_communication_templates t ON t.id = m.template_id
WHERE t.template_key::text = 'TAT_INVOICE_EMAIL'
ORDER BY m.template_id, m.key_name;


--- disable TAT_PAYMENT_LINK_WHATSAPP communication
SELECT * FROM public.communication_templates_master WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP';

UPDATE public.communication_templates_master
SET is_active = false
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP';

SELECT * FROM public.hdb_communication_templates WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP'

SELECT * FROM public.merge_info_answer_location_map WHERE template_id IN (SELECT id FROM public.hdb_communication_templates
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP');

DELETE FROM public.merge_info_answer_location_map
WHERE template_id IN (SELECT id FROM public.hdb_communication_templates
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP');
 

DELETE FROM public.hdb_communication_templates
WHERE template_key = 'TAT_PAYMENT_LINK_WHATSAPP';