-- ============================================================
-- Update: Welcome (email + WATI) and Invite (Regular + Final, WATI) — merge-field sets,
-- plus new WATI template names for the three WhatsApp templates.
--
--   template_key                    type      template_id                       merge fields
--   ------------------------------  --------  --------------------------------  ------------------------------
--   TAT_WELCOME_EMAIL_SEEKER        email     (unchanged — Zepto key)           reg_name, program_name,
--                                                                              session_days_and_time,
--                                                                              session_date,
--                                                                              portal_join_link
--   TAT_WELCOME_WATI_SEEKER         whatsapp  tat_program_welcome_v3            program_name, reg_name,
--                                                                              session_date,
--                                                                              session_days_and_time,
--                                                                              portal_join_link,
--                                                                              guideline_pdf
--   TAT_INVITE_REGULAR_WATI_SEEKER  whatsapp  tat_session_invite_regular_v23    session_name,
--                                                                              session_day_date,
--                                                                              session_time,
--                                                                              session_login_time, reg_name,
--                                                                              portal_join_link,
--                                                                              zoom_join_link, meeting_id,
--                                                                              meeting_passcode,
--                                                                              guidelines_pdf
--   TAT_INVITE_FINAL_WATI_SEEKER    whatsapp  tat_session_invite_final_v21      (same set as regular)
--
-- Changes vs the current live state (files 28 / 47 / 48 / 49 / 51):
--   • Welcome email  — ADDS portal_join_link (was: reg_name, program_name,
--                      session_days_and_time, session_date). template_id untouched: no new
--                      Zepto key was supplied, and the row also carries a sandbox_template_id
--                      that must keep pairing with it.
--   • Welcome WATI   — ADDS portal_join_link; template_id v1 → tat_program_welcome_v3.
--                      guideline_pdf keeps its singular spelling (this template's own variable
--                      name; the invite templates' guidelines_pdf is a different variable
--                      backed by a different function — see below).
--   • Invite Regular — ADDS session_time + session_login_time;
--                      template_id tat_session_invite_regular → …_regular_v23.
--   • Invite Final   — ADDS session_time + session_login_time;
--                      template_id …_final_v20 → …_final_v21. Set stays identical to regular's
--                      (program_name was already dropped in file 49).
--   No field is removed from any of the four.
--
-- Notes on the two lookalike pairs, so neither gets "corrected" later:
--   • guideline_pdf  → getWelcomeGuidelinesPdfUrl  (program-level welcome guidelines)
--     guidelines_pdf → getSessionGuidelinesPdfUrl  (the target session's guidelines)
--   • portal_join_link → getPortalSessionJoinLink returns the FULL portal URL
--     (SEEKER_FE_BASE_URL + the fixed join path; session-independent, so it resolves on the
--     program-level Welcome send too). If a WATI template instead carries the seeker FE domain
--     baked into a URL-button, that button's parameter needs the path ONLY — that is
--     getWatiPortalSessionJoinLink (key wati_portal_link), NOT this field. The three WATI
--     templates here follow the existing invite convention: full URL under portal_join_link.
--
-- Each template lives in two places — communication_templates_master (PT_TAT master row) and
-- hdb_communication_templates (per-program cloned rows, whose merge fields are expanded into
-- merge_info_answer_location_map). Both are updated; cloned merge rows are rebuilt from the
-- master map since every key set changed.
--
-- Idempotent — safe to re-run (unconditional set + rebuild from master).
-- ============================================================

-- 1. Master — Welcome (Email). Merge map only; template_id/sandbox_template_id untouched.
UPDATE communication_templates_master
SET
    merge_field_map = '[
        {"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,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_WELCOME_EMAIL_SEEKER'
  AND template_type = 'email'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- 2. Master — Welcome (WATI)
UPDATE communication_templates_master
SET
    template_id     = 'tat_program_welcome_v3',
    merge_field_map = '[
        {"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,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_WELCOME_WATI_SEEKER'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- 3. Master — Invite (Regular, WATI)
UPDATE communication_templates_master
SET
    template_id     = 'tat_session_invite_regular_v23',
    merge_field_map = '[
        {"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,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_INVITE_REGULAR_WATI_SEEKER'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- 4. Master — Invite (Final, WATI) — same merge set as regular
UPDATE communication_templates_master
SET
    template_id     = 'tat_session_invite_final_v21',
    merge_field_map = '[
        {"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,
    updated_at = CURRENT_TIMESTAMP
WHERE template_key = 'TAT_INVITE_FINAL_WATI_SEEKER'
  AND template_type = 'whatsapp'
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT');

-- 5. Per-program cloned rows: rebuild merge_info_answer_location_map from the (now updated)
--    master map for all four templates, and sync template_id for the three WATI ones.
--
--    The Welcome EMAIL clone's template_id is deliberately left alone: it did not change, and
--    email rows pair a production template_id with a sandbox_template_id that is chosen at
--    runtime (EMAIL_USE_SANDBOX) — overwriting one from the master would risk desyncing that pair.
DO $$
DECLARE
    spec          RECORD;
    master_id     TEXT;
    master_map    JSONB;
    hct_rec       RECORD;
    merge_field   JSONB;
    updated_count INT := 0;
BEGIN
    FOR spec IN
        SELECT *
        FROM (VALUES
            ('TAT_WELCOME_EMAIL_SEEKER',       'email',    false),
            ('TAT_WELCOME_WATI_SEEKER',        'whatsapp', true),
            ('TAT_INVITE_REGULAR_WATI_SEEKER', 'whatsapp', true),
            ('TAT_INVITE_FINAL_WATI_SEEKER',   'whatsapp', true)
        ) AS t(template_key, template_type, sync_template_id)
    LOOP
        -- template_type is the communication_type_enum, and spec.template_type comes out of the
        -- VALUES list as text — an enum-vs-text comparison has no operator, so both sides are
        -- compared as text. (The other patch files get away with a bare 'whatsapp' literal only
        -- because an untyped literal coerces to the enum; a typed text column value does not.)
        SELECT template_id, merge_field_map
        INTO master_id, master_map
        FROM communication_templates_master
        WHERE template_key = spec.template_key
          AND template_type::text = spec.template_type
          AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
        LIMIT 1;

        IF master_map IS NULL THEN
            RAISE EXCEPTION 'Master template % (%) not found — run file 28 first',
                spec.template_key, spec.template_type;
        END IF;

        FOR hct_rec 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 = spec.template_key
              AND hct.template_type::text = spec.template_type
        LOOP
            IF spec.sync_template_id THEN
                UPDATE hdb_communication_templates
                SET template_id = master_id, updated_at = CURRENT_TIMESTAMP
                WHERE id = hct_rec.id;
            ELSE
                UPDATE hdb_communication_templates
                SET updated_at = CURRENT_TIMESTAMP
                WHERE id = hct_rec.id;
            END IF;

            DELETE FROM merge_info_answer_location_map WHERE template_id = hct_rec.id;

            FOR merge_field IN SELECT * FROM jsonb_array_elements(master_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',
                    hct_rec.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;

            updated_count := updated_count + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Welcome + Invite templates — cloned rows rebuilt: %', updated_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
-- Master: template ids + the full merge key set per template.
SELECT template_key,
       template_type,
       template_id,
       jsonb_array_length(merge_field_map)                                    AS merge_fields,
       (SELECT STRING_AGG(elem->>'keyName', ', ')
        FROM jsonb_array_elements(merge_field_map) AS elem)                   AS merge_keys
FROM communication_templates_master
WHERE template_key IN (
        'TAT_WELCOME_EMAIL_SEEKER', 'TAT_WELCOME_WATI_SEEKER',
        'TAT_INVITE_REGULAR_WATI_SEEKER', 'TAT_INVITE_FINAL_WATI_SEEKER')
  AND program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT')
ORDER BY template_key, template_type;

-- Cloned rows: every program should show the same key set as its master, and the WATI rows the
-- new template ids.
SELECT hct.template_key::text AS template_key,
       hct.template_type,
       hct.program_id,
       hct.template_id,
       COUNT(mf.id)                                       AS merge_fields,
       STRING_AGG(mf.key_name, ', ' ORDER BY mf.key_name) AS merge_keys
FROM hdb_communication_templates hct
LEFT 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',
        'TAT_INVITE_REGULAR_WATI_SEEKER', 'TAT_INVITE_FINAL_WATI_SEEKER')
GROUP BY hct.template_key::text, hct.template_type, hct.program_id, hct.template_id
ORDER BY hct.template_key::text, hct.template_type, hct.program_id;
