-- ============================================================
-- Update: TAT Invite (Final) — WhatsApp (WATI) template_id bumped
-- from 'tat_session_invite_final_v2' to 'tat_session_invite_final_dev_v1',
-- with a revised merge-field set.
--
-- Merge fields (dev_v1): session_name, session_day_date, reg_name, program_name,
--                        portal_join_link, zoom_join_link, meeting_id,
--                        meeting_passcode, guidelines_pdf, portal_path
--
-- Changes vs v2:
--   • wati_portal_link -> portal_join_link  (source getWatiPortalSessionJoinLink ->
--                         getPortalSessionJoinLink: the FULL portal URL, base + path)
--   • wati_zoom_link   -> zoom_join_link    (source getWatiSessionJoinUrl ->
--                         getTargetOnlineSessionField:join_url: the FULL Zoom join URL)
--   • portal_path (NEW) — the portal join deep-link WITHOUT the DNS (path only,
--                         e.g. "myspace/programs"), sourced from getWatiPortalSessionJoinLink,
--                         exactly like the former wati_portal_link. Used by the dev_v1 WATI
--                         URL-button parameter, which carries only the variable suffix.
--   • session_name / session_day_date / reg_name / program_name / meeting_id /
--     meeting_passcode / guidelines_pdf — retained (same sources).
--
-- The template lives in two places:
--   1. communication_templates_master  (PT_TAT master row)
--   2. hdb_communication_templates      (per-program cloned rows, whose merge fields are
--      expanded into merge_info_answer_location_map — see PART 2 of
--      28_session-communication-templates-seed.sql)
--
-- Both must be updated so existing programs pick up the new template id + merge set.
-- Idempotent — safe to re-run (cloned merge rows are rebuilt from the master map each run).
-- ============================================================

-- 1. Master template — bump template id + replace the merge field map
UPDATE communication_templates_master
SET
    template_id     = 'tat_session_invite_final_dev_v1',
    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": "program_name",      "sourceTable": "computed", "sourceColumn": "getProgramField:name",                  "dataType": "string", "isCommon": false, "isNullable": false},
        {"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": ""},
        {"keyName": "portal_path",       "sourceTable": "computed", "sourceColumn": "getWatiPortalSessionJoinLink",           "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');

-- 2. Per-program cloned rows: bump template id + rebuild merge_info_answer_location_map
--    from the (now updated) master map, since the merge key set changed.
DO $$
DECLARE
    master_map    JSONB;
    hct_rec       RECORD;
    merge_field   JSONB;
    updated_count INT := 0;
BEGIN
    SELECT merge_field_map
    INTO master_map
    FROM communication_templates_master
    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')
    LIMIT 1;

    IF master_map IS NULL THEN
        RAISE EXCEPTION 'Master template TAT_INVITE_FINAL_WATI_SEEKER not found — run file 28 first';
    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 = 'TAT_INVITE_FINAL_WATI_SEEKER'
          AND hct.template_type = 'whatsapp'
    LOOP
        UPDATE hdb_communication_templates
        SET template_id = 'tat_session_invite_final_dev_v1', updated_at = CURRENT_TIMESTAMP
        WHERE id = hct_rec.id;

        -- Rebuild the merge mappings (v2 keys wati_portal_link/wati_zoom_link no longer apply;
        -- portal_join_link/zoom_join_link/portal_path are now in play).
        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;

    RAISE NOTICE '======================================';
    RAISE NOTICE 'TAT_INVITE_FINAL_WATI_SEEKER — cloned rows updated to dev_v1: %', updated_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, template_id, is_active
FROM communication_templates_master
WHERE template_key = 'TAT_INVITE_FINAL_WATI_SEEKER';

SELECT hct.template_key::text AS template_key,
       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 = 'TAT_INVITE_FINAL_WATI_SEEKER'
  AND hct.template_type = 'whatsapp'
GROUP BY hct.template_key::text, hct.program_id, hct.template_id
ORDER BY hct.program_id;
