-- ================================================
-- Migration: SC-001 — Session Communication email templates seed
-- Date: 2026-07-06
--
-- Seeds the 6 manually-triggered online-session EMAIL templates into
-- communication_templates_master and clones them to every PT_TAT program, mirroring
-- 2026-06-11-02-parental-consent-communication-template.sql.
--
-- Enum values (access keys + template keys) live in 27_session-communication-enums.sql,
-- which must run before this file.
--
--   access key                            template_key                          purpose
--   ------------------------------------  ------------------------------------  -----------------
--   WELCOME_EMAIL_SEEKER                  TAT_WELCOME_EMAIL_SEEKER               Welcome
--   INVITE_REGULAR_SESSION_EMAIL_SEEKER   TAT_INVITE_REGULAR_EMAIL_SEEKER        Invite (regular)
--   INVITE_FINAL_SESSION_EMAIL_SEEKER     TAT_INVITE_FINAL_EMAIL_SEEKER          Invite (final)
--   ABSENT_REGULAR_SESSION_EMAIL_SEEKER   TAT_ABSENT_REGULAR_EMAIL_SEEKER        Absent (regular)
--   ABSENT_FINAL_SESSION_EMAIL_SEEKER     TAT_ABSENT_FINAL_EMAIL_SEEKER          Absent (final)
--   PROGRAM_COMPLETION_EMAIL_SEEKER       TAT_PROGRAM_COMPLETION_EMAIL_SEEKER    Program completion
--   (…_WATI_SEEKER)                       TAT_…_WATI_SEEKER                      WhatsApp variants
--
-- These apply to the PT_TAT program type only (program_type_id = PT_TAT). The FINAL vs
-- REGULAR variant is chosen at send time (see SessionCommunicationService.resolveOccurrence).
--
-- Both channels are seeded: EMAIL (ZeptoMail template_id + sandbox) and WhatsApp
-- (WATI template_type='whatsapp', template_id = WATI template name, no sandbox).
--
-- Merge fields resolve via CommunicationMergeDataService computed functions. The target
-- session is threaded through extraMergeContext.sessionId by SessionCommunicationService,
-- so getTargetSession* functions read THIS session's meeting/date/time fields.
--
-- Assumptions flagged for confirmation (see field comments):
--   • session_login_time = the log-in clock time (session start minus the join lead, IST,
--     e.g. "6:45 p.m. IST"); pre_session_login_time = the join lead in minutes (e.g. 15).
--     Both derive the lead from hdb_online_session.join_opens_minutes_before (default 15).
--   • session_date = "6 July 2025", session_day_date = "Sunday, 6 July 2025" — for the
--     per-session sends (Invite/Absent), which resolve the ONE target session. The two
--     WELCOME templates are program-level and instead use formatSessionDateRange, listing
--     every session of the program grouped by month
--     (e.g. "August 18, 19 & 26, September 1, 3 & 5, 2026") — see file 51.
--   • guideline_pdf (Welcome, singular) → getWelcomeGuidelinesPdfUrl is the program-level
--     welcome guidelines; guidelines_pdf (Invite/Absent, plural) → getSessionGuidelinesPdfUrl
--     is the target session's. Two different variables, two different functions — not a typo.
--   • zoom_join_link / meeting_id / meeting_passcode → getTargetOnlineSessionField reads
--     hdb_online_session.join_url / external_id / password (the session's provisioned
--     shared resource) for the target session.
--   • portal_join_link → getPortalSessionJoinLink returns {SEEKER_FE_BASE_URL}/myspace/zoom.
--   • absentDataTabel → getAbsentSessionsTable currently returns '' (attendance tables
--     not available yet).
--
-- Folded-in later revisions (kept here so a fresh seed lands on the current live state, and
-- so re-running this file can never regress a patched template — the patch files remain for
-- already-seeded databases):
--   • file 46 — session_login_time dropped from the WELCOME templates.
--   • file 51 — WELCOME session_date → formatSessionDateRange.
--   • file 52 — WATI template names (tat_program_welcome_v3,
--     tat_session_invite_regular_v23, tat_session_invite_final_v21); portal_join_link added
--     to both WELCOME templates; session_time + session_login_time added to both invite WATI
--     templates. Invite (Final) WATI carries no program_name (dropped in file 49).
--
-- Idempotent — safe to re-run.
-- ================================================

-- ------------------------------------------------
-- PART 1: Insert master template rows (program_type_id = PT_TAT)
-- ------------------------------------------------

-- 1) Welcome
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_WELCOME_EMAIL_SEEKER',
    'Online Session — Welcome — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.efa4ffb0-7ac5-11f1-b0fe-765e7256bde4.19f41a2832b',
    '2d6f.2a60732b5ed84938.k1.344ad290-7ac9-11f1-b0fe-765e7256bde4.19f41b7ee39',
    NULL, 'general', 'WELCOME_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",              "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",              "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",          "sourceTable": "computed", "sourceColumn": "getProgramField:name",      "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime",     "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "session_date",          "sourceTable": "computed", "sourceColumn": "formatSessionDateRange",    "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link",      "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",  "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    30, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 2) Invite — regular session
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_INVITE_REGULAR_EMAIL_SEEKER',
    'Online Session — Invite (Regular) — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.dac03700-7a92-11f1-b0fe-765e7256bde4.19f4053be70',
    '2d6f.2a60732b5ed84938.k1.a0c94641-7ac4-11f1-b0fe-765e7256bde4.19f4199f0a3',
    NULL, 'general', 'INVITE_REGULAR_SESSION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",                "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",                "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_name",            "sourceTable": "computed", "sourceColumn": "getTargetSessionField:name",             "dataType": "string", "isCommon": true,  "isNullable": false},
        {"keyName": "session_date",            "sourceTable": "computed", "sourceColumn": "getTargetSessionDate",                   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_day_date",        "sourceTable": "computed", "sourceColumn": "getTargetSessionDayDate",                "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_time",            "sourceTable": "computed", "sourceColumn": "getTargetSessionTime",                   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_login_time",      "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginTime",              "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "pre_session_login_time",  "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginLeadMinutes",              "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "zoom_join_link",          "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:join_url",     "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_id",              "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:external_id",       "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_passcode",        "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:password", "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link",        "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",               "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""},
        {"keyName": "remaining_program_dates", "sourceTable": "computed", "sourceColumn": "getTargetRemainingProgramDates",         "dataType": "string", "isCommon": false, "isNullable": true}
    ]'::jsonb,
    31, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 3) Invite — final session (no remaining_program_dates)
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_INVITE_FINAL_EMAIL_SEEKER',
    'Online Session — Invite (Final) — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.97168870-7b81-11f1-8d61-765e7256bde4.19f46705277',
    '2d6f.2a60732b5ed84938.k1.a85b8b60-7b83-11f1-8d61-765e7256bde4.19f467ddf16',
    NULL, 'general', 'INVITE_FINAL_SESSION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",               "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",              "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",           "sourceTable": "computed", "sourceColumn": "getProgramField:name",                  "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_name",           "sourceTable": "computed", "sourceColumn": "getTargetSessionField:name",             "dataType": "string", "isCommon": true,  "isNullable": false},
        {"keyName": "session_day_date",       "sourceTable": "computed", "sourceColumn": "getTargetSessionDayDate",                "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_login_time",     "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginTime",              "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "pre_session_login_time", "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginLeadMinutes",       "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "zoom_join_link",         "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:join_url",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_id",             "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:external_id","dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_passcode",       "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:password",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link",       "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",               "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    32, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 4) Absent — regular session
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_ABSENT_REGULAR_EMAIL_SEEKER',
    'Online Session — Absent (Regular) — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.711f51d0-7b89-11f1-8d61-765e7256bde4.19f46a3c76d',
    '2d6f.2a60732b5ed84938.k1.10c40f60-7b84-11f1-8d61-765e7256bde4.19f46808b56',
    NULL, 'general', 'ABSENT_REGULAR_SESSION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",                   "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",               "sourceTable": "computed", "sourceColumn": "getProgramField:name",             "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "final_session_date",         "sourceTable": "computed", "sourceColumn": "getProgramFinalSessionDate",       "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "final_but_one_session_date", "sourceTable": "computed", "sourceColumn": "getProgramFinalButOneSessionDate", "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "absentDataTabel",            "sourceTable": "computed", "sourceColumn": "getAbsentSessionsTable",           "dataType": "string", "isCommon": false, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    33, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 5) Absent — final session
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_ABSENT_FINAL_EMAIL_SEEKER',
    'Online Session — Absent (Final) — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.3e6e1510-7b83-11f1-8d61-765e7256bde4.19f467b28e1',
    '2d6f.2a60732b5ed84938.k1.ae48e0e1-7b83-11f1-8d61-765e7256bde4.19f467e05ee',
    NULL, 'general', 'ABSENT_FINAL_SESSION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",                   "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",               "sourceTable": "computed", "sourceColumn": "getProgramField:name",             "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "final_session_date",         "sourceTable": "computed", "sourceColumn": "getProgramFinalSessionDate",       "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "final_but_one_session_date", "sourceTable": "computed", "sourceColumn": "getProgramFinalButOneSessionDate", "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "absentDataTabel",            "sourceTable": "computed", "sourceColumn": "getAbsentSessionsTable",           "dataType": "string", "isCommon": false, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    34, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 6) Program completion
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_PROGRAM_COMPLETION_EMAIL_SEEKER',
    'Online Session — Program Completion — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.6ab3fd41-7b8a-11f1-8d61-765e7256bde4.19f46aa2b12',
    '2d6f.2a60732b5ed84938.k1.d038d000-7b8f-11f1-8d61-765e7256bde4.19f46cd8900',
    NULL, 'general', 'PROGRAM_COMPLETION_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",       "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",           "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",   "sourceTable": "computed", "sourceColumn": "getProgramField:name",   "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "total_sessions", "sourceTable": "computed", "sourceColumn": "getProgramSessionCount", "dataType": "number", "isCommon": false, "isNullable": true}
    ]'::jsonb,
    35, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ================================================
-- WhatsApp (WATI) templates. template_type = 'whatsapp'; template_id holds the WATI
-- template name (also used as the send-time templateKey/broadcastName); no sandbox id.
-- Merge fields become the WATI template parameters (resolved per recipient at send time).
-- ================================================

-- 7) Welcome — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_WELCOME_WATI_SEEKER',
    'Online Session — Welcome — WhatsApp to Seeker',
    'whatsapp',
    'tat_program_welcome_v3',
    NULL,
    NULL, 'general', 'WELCOME_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",              "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",              "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",          "sourceTable": "computed", "sourceColumn": "getProgramField:name",      "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime",     "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "session_date",          "sourceTable": "computed", "sourceColumn": "formatSessionDateRange",    "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link",      "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",   "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""},
        {"keyName": "guideline_pdf",         "sourceTable": "computed", "sourceColumn": "getWelcomeGuidelinesPdfUrl","dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    40, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 8) Invite (regular) — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_INVITE_REGULAR_WATI_SEEKER',
    'Online Session — Invite (Regular) — WhatsApp to Seeker',
    'whatsapp',
    'tat_session_invite_regular_v23',
    NULL,
    NULL, 'general', 'INVITE_REGULAR_SESSION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "session_name",     "sourceTable": "computed", "sourceColumn": "getTargetSessionField:name",             "dataType": "string", "isCommon": true,  "isNullable": false},
        {"keyName": "session_day_date", "sourceTable": "computed", "sourceColumn": "getTargetSessionDayDate",                "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "reg_name",         "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",                "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_time",       "sourceTable": "computed", "sourceColumn": "getTargetSessionTime",                   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_login_time", "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginTime",              "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link", "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",               "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""},
        {"keyName": "zoom_join_link",   "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:join_url",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_id",       "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:external_id","dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_passcode", "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:password",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "guidelines_pdf",   "sourceTable": "computed", "sourceColumn": "getSessionGuidelinesPdfUrl",            "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    41, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 9) Invite (final) — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_INVITE_FINAL_WATI_SEEKER',
    'Online Session — Invite (Final) — WhatsApp to Seeker',
    'whatsapp',
    'tat_session_invite_final_v21',
    NULL,
    NULL, 'general', 'INVITE_FINAL_SESSION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "session_name",      "sourceTable": "computed", "sourceColumn": "getTargetSessionField:name",             "dataType": "string", "isCommon": true,  "isNullable": false},
        {"keyName": "session_day_date",  "sourceTable": "computed", "sourceColumn": "getTargetSessionDayDate",                "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "reg_name",          "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",              "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_time",       "sourceTable": "computed", "sourceColumn": "getTargetSessionTime",                   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "session_login_time", "sourceTable": "computed", "sourceColumn": "getTargetSessionLoginTime",              "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "portal_join_link",  "sourceTable": "computed", "sourceColumn": "getPortalSessionJoinLink",               "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""},
        {"keyName": "zoom_join_link",    "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:join_url",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_id",        "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:external_id","dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "meeting_passcode",  "sourceTable": "computed", "sourceColumn": "getTargetOnlineSessionField:password",   "dataType": "string", "isCommon": true,  "isNullable": true},
        {"keyName": "guidelines_pdf",    "sourceTable": "computed", "sourceColumn": "getSessionGuidelinesPdfUrl",            "dataType": "string", "isCommon": true,  "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    42, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 10) Absent (regular) — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_ABSENT_REGULAR_WATI_SEEKER',
    'Online Session — Absent (Regular) — WhatsApp to Seeker',
    'whatsapp',
    'tat_session_absent_regular_v1',
    NULL,
    NULL, 'general', 'ABSENT_REGULAR_SESSION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",                   "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",       "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",               "sourceTable": "computed", "sourceColumn": "getProgramField:name",             "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "final_session_date",         "sourceTable": "computed", "sourceColumn": "getProgramFinalSessionDate",       "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "final_but_one_session_date", "sourceTable": "computed", "sourceColumn": "getProgramFinalButOneSessionDate", "dataType": "string", "isCommon": false, "isNullable": true},
        {"keyName": "absent_session",             "sourceTable": "computed", "sourceColumn": "getAbsentSessionsList",            "dataType": "string", "isCommon": false, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    43, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 11) Absent (final) — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_ABSENT_FINAL_WATI_SEEKER',
    'Online Session — Absent (Final) — WhatsApp to Seeker',
    'whatsapp',
    'tat_session_absent_final_v1',
    NULL,
    NULL, 'general', 'ABSENT_FINAL_SESSION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",     "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",         "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name", "sourceTable": "computed", "sourceColumn": "getProgramField:name",   "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "session_name", "sourceTable": "computed", "sourceColumn": "getTargetSessionField:name", "dataType": "string", "isCommon": true,  "isNullable": false},
        {"keyName": "session_date", "sourceTable": "computed", "sourceColumn": "getTargetSessionDate",   "dataType": "string", "isCommon": true,  "isNullable": true}
    ]'::jsonb,
    44, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- 12) Program completion — WATI
INSERT INTO communication_templates_master (
    template_key, template_name, template_type, template_id, sandbox_template_id,
    category, template_usage_type, template_access_key, program_type_id,
    merge_field_map, display_order, is_active
) VALUES (
    'TAT_PROGRAM_COMPLETION_WATI_SEEKER',
    'Online Session — Program Completion — WhatsApp to Seeker',
    'whatsapp',
    'tat_program_completion_v1',
    NULL,
    NULL, 'general', 'PROGRAM_COMPLETION_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "reg_name",       "sourceTable": "hdb_program_registration", "sourceColumn": "full_name",           "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "program_name",   "sourceTable": "computed", "sourceColumn": "getProgramField:name",   "dataType": "string", "isCommon": false, "isNullable": false},
        {"keyName": "total_sessions", "sourceTable": "computed", "sourceColumn": "getProgramSessionCount", "dataType": "number", "isCommon": false, "isNullable": true}
    ]'::jsonb,
    45, true
) ON CONFLICT (template_key, COALESCE(program_type_id, 0), template_type) DO UPDATE SET
    template_id = EXCLUDED.template_id, sandbox_template_id = EXCLUDED.sandbox_template_id,
    template_access_key = EXCLUDED.template_access_key, merge_field_map = EXCLUDED.merge_field_map,
    updated_at = CURRENT_TIMESTAMP;

-- ------------------------------------------------
-- PART 2: Clone each master row to every PT_TAT program, and expand its
-- merge_field_map into merge_info_answer_location_map.
-- is_enabled = false — admins enable per program when configuring session comms.
-- ------------------------------------------------
DO $$
DECLARE
    sc_keys TEXT[] := ARRAY[
        'TAT_WELCOME_EMAIL_SEEKER',
        'TAT_INVITE_REGULAR_EMAIL_SEEKER',
        'TAT_INVITE_FINAL_EMAIL_SEEKER',
        'TAT_ABSENT_REGULAR_EMAIL_SEEKER',
        'TAT_ABSENT_FINAL_EMAIL_SEEKER',
        'TAT_PROGRAM_COMPLETION_EMAIL_SEEKER',
        'TAT_WELCOME_WATI_SEEKER',
        'TAT_INVITE_REGULAR_WATI_SEEKER',
        'TAT_INVITE_FINAL_WATI_SEEKER',
        'TAT_ABSENT_REGULAR_WATI_SEEKER',
        'TAT_ABSENT_FINAL_WATI_SEEKER',
        'TAT_PROGRAM_COMPLETION_WATI_SEEKER'
    ];
    sc_key          TEXT;
    master_rec      RECORD;
    prog_rec        RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    FOREACH sc_key IN ARRAY sc_keys LOOP
        SELECT id, template_id, template_name, template_key, template_type,
               sandbox_template_id, category, template_usage_type,
               template_access_key, merge_field_map, display_order
        INTO master_rec
        FROM communication_templates_master
        WHERE template_key = sc_key AND is_active = true
        LIMIT 1;

        IF master_rec IS NULL THEN
            RAISE EXCEPTION 'Master template % not found — run PART 1 first', sc_key;
        END IF;

        -- Target PT_TAT programs
        FOR prog_rec IN
            SELECT p.id AS program_id, p.name AS program_name
            FROM program_v1 p
            JOIN program_type_v1 pt ON pt.id = p.program_type_id
            WHERE pt.key        = 'PT_TAT'
              AND pt.deleted_at IS NULL
              AND p.deleted_at  IS NULL
            ORDER BY p.id
        LOOP
            IF EXISTS (
                SELECT 1 FROM hdb_communication_templates h
                WHERE h.program_id          = prog_rec.program_id
                  AND h.template_key::text  = sc_key
                  AND h.template_type       = master_rec.template_type
            ) THEN
                skipped_count := skipped_count + 1;
                CONTINUE;
            END IF;

            INSERT INTO hdb_communication_templates (
                template_id, template_name, template_key, template_type,
                sandbox_template_id, category, template_usage_type, template_access_key,
                program_id, is_enabled, display_order, created_at, updated_at
            ) VALUES (
                master_rec.template_id, master_rec.template_name,
                master_rec.template_key::template_key_enum, master_rec.template_type,
                master_rec.sandbox_template_id, master_rec.category, master_rec.template_usage_type,
                master_rec.template_access_key::communication_template_access_key_enum,
                prog_rec.program_id, false, master_rec.display_order,
                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
            )
            RETURNING id INTO new_template_id;

            IF master_rec.merge_field_map IS NOT NULL
               AND jsonb_array_length(master_rec.merge_field_map) > 0 THEN
                FOR merge_field IN SELECT * FROM jsonb_array_elements(master_rec.merge_field_map)
                LOOP
                    INSERT INTO merge_info_answer_location_map (
                        key_name, template_id, is_common, source_table, source_column,
                        data_type, format_type, is_nullable, default_value, description,
                        is_active, created_at, updated_at
                    ) VALUES (
                        merge_field->>'keyName',
                        new_template_id,
                        COALESCE((merge_field->>'isCommon')::boolean,   false),
                        merge_field->>'sourceTable',
                        merge_field->>'sourceColumn',
                        merge_field->>'dataType',
                        merge_field->>'formatType',
                        COALESCE((merge_field->>'isNullable')::boolean, true),
                        merge_field->>'defaultValue',
                        merge_field->>'description',
                        true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    );
                END LOOP;
            END IF;

            cloned_count := cloned_count + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Session communication templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, template_id, sandbox_template_id, is_active
FROM communication_templates_master
WHERE template_access_key IN (
    'WELCOME_EMAIL_SEEKER', 'WELCOME_WATI_SEEKER',
    'INVITE_REGULAR_SESSION_EMAIL_SEEKER', 'INVITE_REGULAR_SESSION_WATI_SEEKER',
    'INVITE_FINAL_SESSION_EMAIL_SEEKER', 'INVITE_FINAL_SESSION_WATI_SEEKER',
    'ABSENT_REGULAR_SESSION_EMAIL_SEEKER', 'ABSENT_REGULAR_SESSION_WATI_SEEKER',
    'ABSENT_FINAL_SESSION_EMAIL_SEEKER', 'ABSENT_FINAL_SESSION_WATI_SEEKER',
    'PROGRAM_COMPLETION_EMAIL_SEEKER', 'PROGRAM_COMPLETION_WATI_SEEKER'
)
ORDER BY template_key;

SELECT hct.template_key::text AS template_key,
       COUNT(DISTINCT hct.program_id) AS cloned_programs,
       COUNT(mf.id) AS merge_fields
FROM hdb_communication_templates hct
LEFT JOIN merge_info_answer_location_map mf ON mf.template_id = hct.id
WHERE hct.template_access_key IN (
    'WELCOME_EMAIL_SEEKER',
    'INVITE_REGULAR_SESSION_EMAIL_SEEKER', 'INVITE_FINAL_SESSION_EMAIL_SEEKER',
    'ABSENT_REGULAR_SESSION_EMAIL_SEEKER', 'ABSENT_FINAL_SESSION_EMAIL_SEEKER',
    'PROGRAM_COMPLETION_EMAIL_SEEKER'
)
GROUP BY hct.template_key::text
ORDER BY hct.template_key::text;
