-- ============================================================================
-- Generate Template Payload IDs from Program Questions
-- Gets master_question_id and master_form_section_id based on binding keys
-- ============================================================================

-- Step 1: Get all question binding keys from program in display order
WITH program_questions AS (
    SELECT 
        pq.question_id,
        pq.form_section_id,
        pq.display_order,
        q.binding_key,
        fs.key AS section_key
    FROM hdb_program_question pq
    JOIN hdb_question q ON pq.question_id = q.id
    JOIN hdb_form_section fs ON pq.form_section_id = fs.id
    WHERE pq.program_id = 1082  -- Change this program ID
      AND pq.deleted_at IS NULL
      AND q.deleted_at IS NULL
      AND fs.deleted_at IS NULL
    ORDER BY pq.form_section_id, pq.display_order
),

-- Step 2: Find corresponding master questions using binding keys
master_mapping AS (
    SELECT 
        pq.question_id AS program_question_id,
        pq.binding_key,
        pq.section_key,
        pq.display_order,
        mq.id AS master_question_id,
        mq.master_form_section_id,
        mfs.section_key AS master_section_key
    FROM program_questions pq
    JOIN master_question mq ON pq.binding_key = mq.binding_key
        AND mq.is_active = true
        AND mq.deleted_at IS NULL
    JOIN master_form_section mfs ON mq.master_form_section_id = mfs.id
        AND mfs.is_active = true
        AND mfs.deleted_at IS NULL
)

-- Output 1: Master Question IDs Array (for specificQuestionIds)
SELECT 
    'specificQuestionIds' AS field,
    json_agg(master_question_id ORDER BY display_order) AS value
FROM master_mapping

UNION ALL

-- Output 2: Master Section IDs Array (for specificSectionIds)
SELECT 
    'specificSectionIds' AS field,
    json_agg(DISTINCT master_form_section_id ORDER BY master_form_section_id) AS value
FROM master_mapping;

