-- ================================================
-- Migration: SC-015 — System Links PER-SESSION (email) master template + clone to PT_TAT
--
-- The per-session variant of System Links. Same audience and per-recipient values as the
-- program-level System Links (36_system-links-template-seed.sql) — the program's ADMIN users,
-- with the generated system/placeholder Zoom links (source_type = PLACEHOLDER) as a cumulative
-- HTML table — but scoped to ONE session, used when the program's links are generated per session
-- (SessionLinkModeEnum.PER_SESSION). It is selected by the send carrying a sessionId
-- (see SessionCommunicationService.sendSystemLinksBulk):
--   • no sessionId  → program-level template (SYSTEM_LINKS_EMAIL_ADMIN)
--   • with sessionId → this per-session template (SYSTEM_LINKS_PER_SESSION_EMAIL_ADMIN); the
--                      systemJoiningDetails table is also narrowed to that session's links
--
-- Difference vs the program-level variant: it uses the target-session merge fields
-- (session_name / session_date / session_time — resolved for the send's sessionId) instead of the
-- program aggregates (session_dates / session_days_and_time). admin_user_name and
-- systemJoiningDetails are still supplied by the service via extraMergeContext and read with
-- getMergeContextValue.
--
-- Prerequisite: file 44 adds the access key + template key to their enums (run 44 first).
-- Idempotent — safe to re-run.
-- ================================================

-- ------------------------------------------------
-- PART 1: Master template (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_SYSTEM_LINKS_PER_SESSION_EMAIL_ADMIN',
    'Online Session — System Links (Per Session) — Email to Admin',
    'email',
    '2d6f.2a60732b5ed84938.k1.025a1721-872e-11f1-ad6a-fae9afc80e45.19f92f13991',
    '2d6f.2a60732b5ed84938.k1.51d58870-872e-11f1-ad6a-fae9afc80e45.19f92f34277',
    NULL, 'general', 'SYSTEM_LINKS_PER_SESSION_EMAIL_ADMIN',
    (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": "admin_user_name",       "sourceTable": "computed", "sourceColumn": "getMergeContextValue:admin_user_name",         "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "session_date",          "sourceTable": "computed", "sourceColumn": "getTargetSessionDate",                        "dataType": "string", "isCommon": true, "isNullable": true},
        {"keyName": "program_name",          "sourceTable": "computed", "sourceColumn": "getProgramField:name",                        "dataType": "string", "isCommon": true, "isNullable": false},
        {"keyName": "systemJoiningDetails",  "sourceTable": "computed", "sourceColumn": "getMergeContextValue:systemJoiningDetails",    "dataType": "string", "isCommon": true, "isNullable": true, "defaultValue": ""},
        {"keyName": "session_time",          "sourceTable": "computed", "sourceColumn": "getTargetSessionTime",                        "dataType": "string", "isCommon": true, "isNullable": true}
    ]'::jsonb,
    50, 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 the master 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_key          TEXT := 'TAT_SYSTEM_LINKS_PER_SESSION_EMAIL_ADMIN';
    master_rec      RECORD;
    prog_rec        RECORD;
    merge_field     JSONB;
    new_template_id BIGINT;
    cloned_count    INT := 0;
    skipped_count   INT := 0;
BEGIN
    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;

    RAISE NOTICE 'System links (per session) template — 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 = 'SYSTEM_LINKS_PER_SESSION_EMAIL_ADMIN';
