-- ============================================================
-- Reset: TAT Invite (Regular + Final) — WhatsApp (WATI) template_id + merge-field set.
--
-- Points both invite WATI templates back to their canonical (env-suffix-free) WATI names and
-- sets the agreed merge sets, superseding the env-specific churn (…_dev_v7 / …_dev_v1 /
-- …_v2 / …_uat_v2 from files 39/40/41/43).
--
--   template_key                    template_id (WATI name)      merge fields
--   ------------------------------  ---------------------------  --------------------------------
--   TAT_INVITE_REGULAR_WATI_SEEKER  tat_session_invite_regular   session_name, session_day_date,
--                                                                reg_name, portal_join_link,
--                                                                zoom_join_link, meeting_id,
--                                                                meeting_passcode, guidelines_pdf
--   TAT_INVITE_FINAL_WATI_SEEKER    tat_session_invite_final     (same as regular) + program_name
--
-- vs the previous dev_v7/dev_v1 sets: drops portal_path (and, for regular, program_name).
--
-- 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 the key set changed.
--
-- Idempotent — safe to re-run (unconditional set + rebuild from master).
-- ============================================================

-- 1. Master — Invite (Regular)
UPDATE communication_templates_master
SET
    template_id     = 'tat_session_invite_regular',
    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": "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');

-- 2. Master — Invite (Final) (adds program_name)
UPDATE communication_templates_master
SET
    template_id     = 'tat_session_invite_final',
    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": ""}
    ]'::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');

-- 3. Per-program cloned rows: for both templates, sync template_id from the master row and
--    rebuild merge_info_answer_location_map from the (now updated) master merge map.
DO $$
DECLARE
    sc_keys       TEXT[] := ARRAY['TAT_INVITE_REGULAR_WATI_SEEKER', 'TAT_INVITE_FINAL_WATI_SEEKER'];
    sc_key        TEXT;
    master_id     TEXT;
    master_map    JSONB;
    hct_rec       RECORD;
    merge_field   JSONB;
    updated_count INT := 0;
BEGIN
    FOREACH sc_key IN ARRAY sc_keys LOOP
        SELECT template_id, merge_field_map
        INTO master_id, master_map
        FROM communication_templates_master
        WHERE template_key = sc_key
          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 % not found — run file 28 first', sc_key;
        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 = sc_key
              AND hct.template_type = 'whatsapp'
        LOOP
            UPDATE hdb_communication_templates
            SET template_id = master_id, updated_at = CURRENT_TIMESTAMP
            WHERE id = hct_rec.id;

            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 'TAT invite WATI templates — cloned rows reset: %', updated_count;
    RAISE NOTICE '======================================';
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_id, is_active
FROM communication_templates_master
WHERE template_key IN ('TAT_INVITE_REGULAR_WATI_SEEKER', 'TAT_INVITE_FINAL_WATI_SEEKER')
  AND template_type = 'whatsapp'
ORDER BY template_key;

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 IN ('TAT_INVITE_REGULAR_WATI_SEEKER', '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.template_key::text, hct.program_id;
