-- ============================================================
-- Update: remove the `session_login_time` merge variable from the WELCOME templates
-- (Email + WhatsApp), for PT_TAT.
--
-- The Welcome communication no longer renders a recommended login time, so the merge field is
-- dropped from both channels. Every other Welcome merge field is retained.
--
-- Templates affected (master + per-program cloned rows):
--   TAT_WELCOME_EMAIL_SEEKER  (email)
--   TAT_WELCOME_WATI_SEEKER   (whatsapp)
--
-- 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 cleaned so existing programs stop resolving/sending the variable. Surgical (drops only
-- the one field) and idempotent — safe to re-run (a missing field is a no-op).
-- ============================================================

-- 1. Master templates — strip the session_login_time element from merge_field_map.
UPDATE communication_templates_master
SET
    merge_field_map = (
        SELECT COALESCE(jsonb_agg(elem), '[]'::jsonb)
        FROM jsonb_array_elements(merge_field_map) AS elem
        WHERE elem->>'keyName' <> 'session_login_time'
    ),
    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');

-- 2. Per-program cloned rows — delete the session_login_time mapping.
DELETE FROM merge_info_answer_location_map
WHERE key_name = 'session_login_time'
  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 — no session_login_time should remain for the Welcome templates.
-- ------------------------------------------------
SELECT template_key,
       (SELECT COUNT(*) FROM jsonb_array_elements(merge_field_map) e
        WHERE e->>'keyName' = 'session_login_time') AS master_login_time_fields
FROM communication_templates_master
WHERE template_key IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER');

SELECT hct.template_key::text AS template_key, COUNT(mf.id) AS remaining_login_time_rows
FROM hdb_communication_templates hct
LEFT JOIN merge_info_answer_location_map mf
       ON mf.template_id = hct.id AND mf.key_name = 'session_login_time'
WHERE hct.template_key::text IN ('TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER')
GROUP BY hct.template_key::text
ORDER BY hct.template_key::text;
