
-- ------------------------------------------------
-- PART 1: Master template
-- ------------------------------------------------

-- 1a. Provider template id
UPDATE communication_templates_master
SET template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_id IN (
        '2d6f.2a60732b5ed84938.k1.06b7f160-db44-11ec-9664-525400fa05f6.180f5667b76',
        '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859'
    );

-- 1b. Collapse session_days + session_time into session_days_and_time.
-- Drop both legacy fields (and any stale session_days_and_time to keep re-runs
-- clean), then append the single combined field.
UPDATE communication_templates_master ctm
SET merge_field_map = (
        SELECT COALESCE(jsonb_agg(elem), '[]'::jsonb)
        FROM jsonb_array_elements(ctm.merge_field_map) AS elem
        WHERE elem->>'keyName' NOT IN (
            'session_days', 'session_time', 'session_days_and_time'
        )
    ) || '[
        {"keyName": "session_days_and_time", "sourceTable": "computed", "sourceColumn": "getSessionDaysAndTime", "dataType": "string", "isNullable": true}
    ]'::jsonb,
    updated_at = CURRENT_TIMESTAMP
WHERE ctm.template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859';

-- ------------------------------------------------
-- PART 2: Cloned templates (per-program copies) — provider template id
-- ------------------------------------------------
UPDATE hdb_communication_templates
SET template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859',
    updated_at  = CURRENT_TIMESTAMP
WHERE template_id IN (
        '2d6f.2a60732b5ed84938.k1.06b7f160-db44-11ec-9664-525400fa05f6.180f5667b76',
        '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859'
    );

-- ------------------------------------------------
-- PART 3a: Already-cloned runtime rows — drop the two legacy session fields
-- ------------------------------------------------
DELETE FROM merge_info_answer_location_map
WHERE key_name IN ('session_days', 'session_time')
  AND template_id IN (
        SELECT id
        FROM hdb_communication_templates
        WHERE template_id IN (
            '2d6f.2a60732b5ed84938.k1.06b7f160-db44-11ec-9664-525400fa05f6.180f5667b76',
            '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859'
        )
    );

-- ------------------------------------------------
-- PART 3b: Already-cloned runtime rows — add session_days_and_time to every
-- cloned copy that does not already carry it.
-- ------------------------------------------------
INSERT INTO merge_info_answer_location_map (
    key_name,
    template_id,
    is_common,
    source_table,
    source_column,
    data_type,
    format_type,
    is_nullable,
    is_active,
    is_per_recipient,
    created_at,
    updated_at
)
SELECT
    'session_days_and_time',
    t.id,
    false,
    'computed',
    'getSessionDaysAndTime',
    'string',
    NULL,
    true,
    true,
    false,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM hdb_communication_templates t
WHERE t.template_id IN (
        '2d6f.2a60732b5ed84938.k1.06b7f160-db44-11ec-9664-525400fa05f6.180f5667b76',
        '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859'
    )
  AND NOT EXISTS (
        SELECT 1
        FROM merge_info_answer_location_map m
        WHERE m.template_id = t.id
          AND m.key_name = 'session_days_and_time'
    );

-- ------------------------------------------------
-- Verification
-- ------------------------------------------------
-- SELECT template_key, template_id, sandbox_template_id, merge_field_map
-- FROM communication_templates_master
-- WHERE template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859';
--
-- SELECT template_key, template_id
-- FROM hdb_communication_templates
-- WHERE template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859';
--
-- SELECT m.template_id, m.key_name, m.source_column, m.format_type, m.is_nullable
-- FROM merge_info_answer_location_map m
-- JOIN hdb_communication_templates t ON t.id = m.template_id
-- WHERE t.template_id = '2d6f.2a60732b5ed84938.k1.b18b5490-7165-11f1-9b3d-5254001dc20d.19f04304859'
-- ORDER BY m.template_id, m.key_name;
