-- =====================================================
-- SCRIPT: Create Template from Existing Program
-- Clones sections and questions from a specific program
-- Maps hdb_question to master_question via binding_key
-- =====================================================
select * from program_template;
DO $$
DECLARE
    -- ============ SET THESE VARIABLES ============
    p_source_program_id BIGINT := 1082;                    -- SOURCE PROGRAM ID
    p_program_type_key VARCHAR(50) := 'PT_HDBMSD';         -- TARGET PROGRAM TYPE KEY
    p_template_name VARCHAR(255) := 'HDB Program 25/26 Template'; -- NEW TEMPLATE NAME
    p_template_description TEXT := 'Template cloned from program'; -- DESCRIPTION
    p_template_version INTEGER := 1;                        -- TEMPLATE VERSION
    p_created_by INTEGER := -2;                             -- YOUR USER ID
    
    -- Internal variables
    v_program_type_id INTEGER;
    v_template_id BIGINT;
    v_section_map JSONB := '{}'::jsonb;
    v_question_map JSONB := '{}'::jsonb;
    v_hdb_section_id BIGINT;
    v_template_section_id BIGINT;
    v_master_section_id BIGINT;
    v_master_question_id BIGINT;
    v_sections_cloned INTEGER := 0;
    v_questions_cloned INTEGER := 0;
    v_questions_not_found INTEGER := 0;
    v_total_program_questions INTEGER;
    -- Counter for sections that don't match a known key (fills slot 2, then 6, 7, …)
    v_other_display_order INTEGER := 2;
    v_section_display_order INTEGER;

    -- Record for iteration
    r_program_question RECORD;
    r_section RECORD;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Creating template from program: %', p_source_program_id;
    RAISE NOTICE '======================================';
    
    -- =====================================================
    -- STEP 0: Validate Source Program Exists
    -- =====================================================
    
    SELECT COUNT(*) INTO v_total_program_questions
    FROM hdb_program_question
    WHERE program_id = p_source_program_id
      AND deleted_at IS NULL;
    
    IF v_total_program_questions = 0 THEN
        RAISE EXCEPTION 'Program % has no questions mapped', p_source_program_id;
    END IF;
    
    RAISE NOTICE 'Found % questions in program %', v_total_program_questions, p_source_program_id;
    
    -- =====================================================
    -- STEP 1: Lookup Program Type
    -- =====================================================
    
    SELECT id INTO v_program_type_id
    FROM program_type_v1
    WHERE key = p_program_type_key
      AND deleted_at IS NULL;
    
    IF v_program_type_id IS NULL THEN
        RAISE EXCEPTION 'Program type with key "%" not found', p_program_type_key;
    END IF;
    
    RAISE NOTICE 'Found program type: % (ID: %)', p_program_type_key, v_program_type_id;
    
    -- =====================================================
    -- STEP 2: Create Program Template
    -- =====================================================
    
    INSERT INTO program_template (
        program_type_id,
        name,
        description,
        version,
        status,
        is_active,
        created_at,
        updated_at,
        created_by,
        updated_by
    )
    VALUES (
        v_program_type_id,
        p_template_name,
        p_template_description,
        p_template_version,
        'PUBLISHED'::template_status,
        TRUE,
        NOW(),
        NOW(),
        p_created_by,
        p_created_by
    )
    RETURNING id INTO v_template_id;
    
    RAISE NOTICE 'Created template ID: %', v_template_id;
    
    -- =====================================================
    -- STEP 3: Clone Sections (from program's used sections)
    -- Get unique sections from program questions
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning sections...';
    RAISE NOTICE '======================================';
    
    -- Clone root sections first
    FOR r_section IN
        SELECT DISTINCT 
            hfs.id as hdb_section_id,
            hfs.key as section_key,
            mfs.id as master_section_id,
            mfs.name,
            mfs.description,
            mfs.conditional_config
        FROM hdb_program_question hpq
        INNER JOIN hdb_question hq ON hq.id = hpq.question_id
        INNER JOIN hdb_form_section hfs ON hfs.id = hq.form_section_id
        LEFT JOIN master_form_section mfs ON mfs.section_key = hfs.key
        WHERE hpq.program_id = p_source_program_id
          AND hpq.deleted_at IS NULL
          AND hq.deleted_at IS NULL
          AND hfs.deleted_at IS NULL
          AND hfs.parent_section_id IS NULL  -- Root sections only
        ORDER BY hfs.id
    LOOP
        -- Check if master section exists
        IF r_section.master_section_id IS NULL THEN
            RAISE WARNING 'Section key "%" not found in master_form_section, skipping', r_section.section_key;
            CONTINUE;
        END IF;
        
        -- Determine explicit display order by section key
        v_section_display_order := CASE r_section.section_key
            WHEN 'FS_BASICDETAILS'   THEN 1
            WHEN 'FS_PAYMENTINVOICE' THEN 3
            WHEN 'FS_TRAVELPLAN'     THEN 4
            WHEN 'FS_GOODIES'        THEN 5
            WHEN 'FS_MAHATRIAQUESTIONS' THEN 2
            ELSE v_other_display_order
        END;

        -- Advance the fallback counter (skip over reserved slots 3-5)
        IF r_section.section_key NOT IN ('FS_BASICDETAILS', 'FS_PAYMENTINVOICE', 'FS_TRAVELPLAN', 'FS_GOODIES', 'FS_MAHATRIAQUESTIONS') THEN
            v_other_display_order := v_other_display_order + 1;
            WHILE v_other_display_order = ANY(ARRAY[3, 4, 5]) LOOP
                v_other_display_order := v_other_display_order + 1;
            END LOOP;
        END IF;

        -- Insert template section
        INSERT INTO template_form_section (
            program_template_id,
            master_form_section_id,
            section_key,
            name,
            description,
            parent_section_id,
            conditional_config,
            display_order,
            created_at,
            created_by,
            updated_by
        )
        VALUES (
            v_template_id,
            r_section.master_section_id,
            r_section.section_key,
            r_section.name,
            r_section.description,
            NULL,  -- Root section
            r_section.conditional_config,
            v_section_display_order,
            NOW(),
            p_created_by,
            p_created_by
        )
        RETURNING id INTO v_template_section_id;

        -- Map hdb_section_id -> template_section_id
        v_section_map := jsonb_set(
            v_section_map,
            ARRAY[r_section.hdb_section_id::text],
            to_jsonb(v_template_section_id)
        );

        v_sections_cloned := v_sections_cloned + 1;
        RAISE NOTICE '  ✓ Cloned root section: % (hdb_section=%, template_section=%, display_order=%)',
            r_section.section_key, r_section.hdb_section_id, v_template_section_id, v_section_display_order;
    END LOOP;
    
    -- Clone child sections (with parent references)
    FOR r_section IN
        SELECT DISTINCT 
            hfs.id as hdb_section_id,
            hfs.key as section_key,
            hfs.parent_section_id as hdb_parent_section_id,
            mfs.id as master_section_id,
            mfs.name,
            mfs.description,
            mfs.conditional_config
        FROM hdb_program_question hpq
        INNER JOIN hdb_question hq ON hq.id = hpq.question_id
        INNER JOIN hdb_form_section hfs ON hfs.id = hq.form_section_id
        LEFT JOIN master_form_section mfs ON mfs.section_key = hfs.key
        WHERE hpq.program_id = p_source_program_id
          AND hpq.deleted_at IS NULL
          AND hq.deleted_at IS NULL
          AND hfs.deleted_at IS NULL
          AND hfs.parent_section_id IS NOT NULL  -- Child sections only
        ORDER BY hfs.id
    LOOP
        -- Check if master section exists
        IF r_section.master_section_id IS NULL THEN
            RAISE WARNING 'Section key "%" not found in master_form_section, skipping', r_section.section_key;
            CONTINUE;
        END IF;
        
        -- Get mapped parent section ID
        v_template_section_id := (v_section_map->>r_section.hdb_parent_section_id::text)::BIGINT;
        
        IF v_template_section_id IS NULL THEN
            RAISE WARNING 'Parent section for "%" not found in mapping, skipping', r_section.section_key;
            CONTINUE;
        END IF;
        
        -- Insert template section with parent
        INSERT INTO template_form_section (
            program_template_id,
            master_form_section_id,
            section_key,
            name,
            description,
            parent_section_id,
            conditional_config,
            display_order,
            created_at,
            created_by,
            updated_by
        )
        VALUES (
            v_template_id,
            r_section.master_section_id,
            r_section.section_key,
            r_section.name,
            r_section.description,
            v_template_section_id,  -- Mapped parent
            r_section.conditional_config,
            v_sections_cloned + 1,
            NOW(),
            p_created_by,
            p_created_by
        )
        RETURNING id INTO v_template_section_id;
        
        -- Map hdb_section_id -> template_section_id
        v_section_map := jsonb_set(
            v_section_map,
            ARRAY[r_section.hdb_section_id::text],
            to_jsonb(v_template_section_id)
        );
        
        v_sections_cloned := v_sections_cloned + 1;
        RAISE NOTICE '  ✓ Cloned child section: % (hdb_section=%, template_section=%)', 
            r_section.section_key, r_section.hdb_section_id, v_template_section_id;
    END LOOP;
    
    -- =====================================================
    -- STEP 4: Clone Questions (Map via binding_key)
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning questions (mapping via binding_key)...';
    RAISE NOTICE '======================================';
    
    FOR r_program_question IN
        SELECT 
            hpq.id as program_question_id,
            hpq.question_id as hdb_question_id,
            hpq.display_order,
            hq.binding_key,
            hq.form_section_id as hdb_section_id,
            hq.label as question_text,
            hq.typ as question_type,
            hq.conditional_config as hdb_conditional_config
        FROM hdb_program_question hpq
        INNER JOIN hdb_question hq ON hq.id = hpq.question_id
        WHERE hpq.program_id = p_source_program_id
          AND hpq.deleted_at IS NULL
          AND hq.deleted_at IS NULL
        ORDER BY hpq.display_order, hpq.id
    LOOP
        -- Find master question by binding_key
        SELECT id INTO v_master_question_id
        FROM master_question
        WHERE binding_key = r_program_question.binding_key
          AND deleted_at IS NULL
          AND is_active = TRUE
        LIMIT 1;
        
        IF v_master_question_id IS NULL THEN
            RAISE WARNING '  ✗ Question not found in master: binding_key=%, hdb_question_id=%', 
                r_program_question.binding_key, r_program_question.hdb_question_id;
            v_questions_not_found := v_questions_not_found + 1;
            CONTINUE;
        END IF;
        
        -- Get template section ID from mapping
        v_template_section_id := (v_section_map->>r_program_question.hdb_section_id::text)::BIGINT;
        
        IF v_template_section_id IS NULL THEN
            RAISE WARNING '  ✗ Section not mapped for question: binding_key=%, section_id=%', 
                r_program_question.binding_key, r_program_question.hdb_section_id;
            v_questions_not_found := v_questions_not_found + 1;
            CONTINUE;
        END IF;
        
        -- Insert template question
        INSERT INTO template_question (
            template_form_section_id,
            master_question_id,
            question_code,
            binding_key,
            question_text,
            question_type,
            answer_type,
            answer_location,
            option_config,
            config,
            conditional_config,
            display_order,
            created_at,
            created_by,
            updated_at,
            updated_by
        )
        SELECT 
            v_template_section_id,
            mq.id,
            mq.question_code,
            mq.binding_key,
            mq.question_text,
            mq.question_type,
            mq.answer_type,
            mq.answer_location,
            mq.option_config,
            mq.config,
            COALESCE(r_program_question.hdb_conditional_config, mq.conditional_config),
            r_program_question.display_order,
            NOW(),
            p_created_by,
            NOW(),
            p_created_by
        FROM master_question mq
        WHERE mq.id = v_master_question_id;
        
        v_questions_cloned := v_questions_cloned + 1;
        
        IF v_questions_cloned % 10 = 0 THEN
            RAISE NOTICE '  Progress: % questions cloned...', v_questions_cloned;
        END IF;
    END LOOP;
    
    -- =====================================================
    -- STEP 5: Final Report
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'TEMPLATE CREATION COMPLETE';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Source Program ID: %', p_source_program_id;
    RAISE NOTICE 'New Template ID: %', v_template_id;
    RAISE NOTICE 'Template Name: %', p_template_name;
    RAISE NOTICE 'Template Status: PUBLISHED';
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Total Program Questions: %', v_total_program_questions;
    RAISE NOTICE 'Sections Cloned: %', v_sections_cloned;
    RAISE NOTICE 'Questions Cloned: %', v_questions_cloned;
    RAISE NOTICE 'Questions Not Found: %', v_questions_not_found;
    RAISE NOTICE '--------------------------------------';
    
    IF v_questions_not_found > 0 THEN
        RAISE NOTICE 'WARNING: % questions could not be mapped to master', v_questions_not_found;
        RAISE NOTICE 'Check logs above for details';
    END IF;
    
    IF v_questions_cloned = 0 THEN
        RAISE WARNING 'NO QUESTIONS CLONED! Template may be incomplete.';
    END IF;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'NEXT STEPS:';
    RAISE NOTICE '1. Review template sections and questions';
    RAISE NOTICE '2. Run validation script with template_id=%', v_template_id;
    RAISE NOTICE '3. Publish template when ready';
    RAISE NOTICE '======================================';

        -- ================= UPDATED DETAILS SECTION =================
        RAISE NOTICE '--------------------------------------';
        RAISE NOTICE 'UPDATED DETAILS:';
        RAISE NOTICE 'Template ID: %', v_template_id;
        RAISE NOTICE 'Program Type ID: %', v_program_type_id;
        RAISE NOTICE 'Template Name: %', p_template_name;
        RAISE NOTICE 'Description: %', p_template_description;
        RAISE NOTICE 'Version: %', p_template_version;
        RAISE NOTICE 'Created By: %', p_created_by;
        RAISE NOTICE 'Sections Cloned: %', v_sections_cloned;
        RAISE NOTICE 'Questions Cloned: %', v_questions_cloned;
        RAISE NOTICE 'Questions Not Found: %', v_questions_not_found;
        RAISE NOTICE '--------------------------------------';
    
END $$;