-- ================================================
-- Migration: SC-010 — Common Invite (email + WhatsApp) master templates + clone to PT_TAT
-- Date: 2026-07-17
--
-- Common Invite is a program-level bulk communication. It notifies the staff who were issued a
-- generated common Zoom link (zoom_generated_registrant_link, source_type = ROLE) of the shared
-- join link + meeting details. It is triggered two ways: automatically after a shared
-- ("same link") session group is created, and manually via POST /session-communication/common-invite/bulk.
--
-- The recipients have no program registration of their own, so all merge fields resolve at the
-- program level (no per-record hdb_program_registration columns). Per-recipient values are
-- supplied by the service via extraMergeContext and read with getMergeContextValue:
--   common_user_name        → the recipient's display name
--   common_zoom_join_link    → the recipient's generated common join link
--   common_meeting_id        → the session's Zoom meeting/webinar id
--   common_meeting_passcode  → the session's Zoom passcode
-- Program-level fields resolve via the shared computed functions (they now fall back to the
-- context program when no registration is present):
--   program_name             → getProgramField:name
--   session_dates            → formatSessionDateAndDaysRange
--   session_days_and_time    → getSessionDaysAndTime
--
-- Prerequisite: file 31 adds the access keys + template keys to their enums (run 31 first).
-- Idempotent — safe to re-run.
-- ================================================

-- ------------------------------------------------
-- PART 1: Master templates
-- ------------------------------------------------

-- 1) Common Invite — Email
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_COMMON_INVITE_EMAIL_SEEKER',
    'Online Session — Common Invite — Email to Seeker',
    'email',
    '2d6f.2a60732b5ed84938.k1.23911f20-81ab-11f1-b203-765e7256bde4.19f6ed2f212',
    '2d6f.2a60732b5ed84938.k1.5e6f3170-80fe-11f1-b203-765e7256bde4.19f6a66ae07',
    NULL, 'general', 'COMMON_INVITE_EMAIL_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "common_user_name",       "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_user_name",       "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "session_dates",          "sourceTable": "computed", "sourceColumn": "formatSessionDateAndDaysRange",                "dataType": "string", "isCommon": true, "isNullable": true},
        {"keyName": "session_days_and_time",  "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime",                        "dataType": "string", "isCommon": true, "isNullable": true},
        {"keyName": "program_name",           "sourceTable": "computed", "sourceColumn": "getProgramField:name",                         "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "common_zoom_join_link",  "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_zoom_join_link",   "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""},
        {"keyName": "common_meeting_id",      "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_meeting_id",       "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""},
        {"keyName": "common_meeting_passcode","sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_meeting_passcode", "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    46, 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) Common Invite — WhatsApp (WATI). template_id holds the WATI template name; no sandbox.
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_COMMON_INVITE_WATI_SEEKER',
    'Online Session — Common Invite — WhatsApp to Seeker',
    'whatsapp',
    'tat_common_invite_v3',
    NULL,
    NULL, 'general', 'COMMON_INVITE_WATI_SEEKER',
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT' AND deleted_at IS NULL),
    '[
        {"keyName": "common_user_name",       "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_user_name",       "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "session_dates",          "sourceTable": "computed", "sourceColumn": "formatSessionDateAndDaysRange",                "dataType": "string", "isCommon": true, "isNullable": true},
        {"keyName": "session_days_and_time",  "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime",                        "dataType": "string", "isCommon": true, "isNullable": true},
        {"keyName": "program_name",           "sourceTable": "computed", "sourceColumn": "getProgramField:name",                         "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "common_zoom_join_link",  "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_zoom_join_link",   "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""},
        {"keyName": "common_meeting_id",      "sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_meeting_id",       "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""},
        {"keyName": "common_meeting_passcode","sourceTable": "computed", "sourceColumn": "getMergeContextValue:common_meeting_passcode", "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""}
    ]'::jsonb,
    47, 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 it per program.
-- ------------------------------------------------
DO $$
DECLARE
    sc_keys TEXT[] := ARRAY[
        'TAT_COMMON_INVITE_EMAIL_SEEKER',
        'TAT_COMMON_INVITE_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;

        FOR prog_rec IN
            SELECT p.id AS program_id
            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 'Common invite templates — cloned: %, skipped (already present): %',
        cloned_count, skipped_count;
END $$;

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
SELECT template_key, template_access_key, template_id, sandbox_template_id, is_active
FROM communication_templates_master
WHERE template_access_key IN ('COMMON_INVITE_EMAIL_SEEKER', 'COMMON_INVITE_WATI_SEEKER')
ORDER BY template_key;
