-- =====================================================
-- SCRIPT: Update Existing Program Questions from Template
-- Purpose: Updates program questions (createdBy = -2) by matching bindingKey
-- Updates: optionConfig, answer_type, templateQuestionId
-- Also updates: templateFormSectionId in hdb_form_section if not set
-- =====================================================

-- ============ SET THESE VARIABLES ============
DO $$
DECLARE
    p_program_template_id BIGINT := 1;        -- SET YOUR TEMPLATE ID HERE (optional - will update all if not set)
    p_system_user_id INTEGER := -2;           -- System user ID (questions created by SQL scripts)
    
    v_questions_updated INTEGER := 0;
    v_sections_updated INTEGER := 0;
    v_row_count INTEGER;
    v_template_question RECORD;
    v_program_question RECORD;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Updating program questions from template...';
    IF p_program_template_id IS NOT NULL THEN
        RAISE NOTICE 'Template ID: %', p_program_template_id;
    ELSE
        RAISE NOTICE 'Updating from ALL templates';
    END IF;
    RAISE NOTICE '======================================';
    
    -- Loop through all template questions
    FOR v_template_question IN
        SELECT 
            tq.id as template_question_id,
            tq.binding_key,
            tq.question_text,
            tq.question_type,
            tq.answer_type,
            tq.answer_location,
            tq.option_config,
            tq.config,
            tq.template_form_section_id
        FROM template_question tq
        INNER JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
        WHERE tq.deleted_at IS NULL
          AND tq.binding_key IS NOT NULL
          AND tq.binding_key != ''
          AND tfs.deleted_at IS NULL
          AND (p_program_template_id IS NULL OR tfs.program_template_id = p_program_template_id)
        ORDER BY tq.id
    LOOP
        -- Update all program questions with matching bindingKey created by system user
        UPDATE hdb_question
        SET 
            option_config = v_template_question.option_config,
            answer_type = v_template_question.answer_type,
            template_question_id = v_template_question.template_question_id,
            updated_at = NOW()
        WHERE binding_key = v_template_question.binding_key
          AND created_by = p_system_user_id
          AND deleted_at IS NULL;
        
        GET DIAGNOSTICS v_row_count = ROW_COUNT;
        v_questions_updated := v_questions_updated + v_row_count;
        
                IF v_row_count > 0 THEN
                        -- Update section's templateFormSectionId if not set
                        UPDATE hdb_form_section fs
                        SET 
                                template_form_section_id = v_template_question.template_form_section_id,
                                updated_at = NOW()
                        FROM hdb_question q
                        WHERE q.form_section_id = fs.id
                            AND q.binding_key = v_template_question.binding_key
                            AND q.created_by = p_system_user_id
                            AND fs.template_form_section_id IS NULL
                            AND fs.deleted_at IS NULL
                            AND q.deleted_at IS NULL;

                        GET DIAGNOSTICS v_row_count = ROW_COUNT;
                        v_sections_updated := v_sections_updated + v_row_count;
                END IF;
    END LOOP;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Update completed successfully!';
    RAISE NOTICE 'Questions updated: %', v_questions_updated;
    RAISE NOTICE 'Sections updated: %', v_sections_updated;
    RAISE NOTICE '======================================';
END $$;
