
DO $$
DECLARE
    p_program_type_key VARCHAR(50) := 'PT_HDBMSD';         -- SET PROGRAM TYPE KEY HERE (PT_HDBMSD, PT_TAT, etc.)
    p_name VARCHAR(255) := 'HDB/MSD 25-26';        -- SET TEMPLATE NAME HERE
    p_description TEXT := 'Registration form template for HDB/MSD programs 25-26'; -- SET DESCRIPTION
    p_version INTEGER := 1;                                 -- SET VERSION (default: 1)
    p_created_by INTEGER := -2;                             -- SET YOUR USER ID HERE
    
    v_program_type_id INTEGER;
    v_template_id BIGINT;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Creating program template...';
    RAISE NOTICE '======================================';
    
    -- Lookup program_type_id by key
    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;
    
    -- Create program_template record
    INSERT INTO program_template (
        program_type_id,
        name,
        description,
        version,
        status,
        is_active,
        created_at,
        created_by
    )
    VALUES (
        v_program_type_id,
        p_name,
        p_description,
        p_version,
        'DRAFT'::template_status,
        TRUE,
        NOW(),
        p_created_by
    )
    RETURNING id INTO v_template_id;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Program template created successfully!';
    RAISE NOTICE 'Template ID: %', v_template_id;
    RAISE NOTICE 'Program Type: % (ID: %)', p_program_type_key, v_program_type_id;
    RAISE NOTICE 'Name: %', p_name;
    RAISE NOTICE 'Version: %', p_version;
    RAISE NOTICE 'Status: DRAFT';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'IMPORTANT: Use Template ID % in SCRIPT 1 to clone master data', v_template_id;
    RAISE NOTICE '======================================';
END $$;


-- =====================================================
-- SCRIPT 1: Clone Master to Template (Direct SQL)
-- Usage: Set variables at top, then run entire script
-- PREREQUISITE: Run SCRIPT 0 first to create program_template
-- =====================================================

-- ============ SET THESE VARIABLES ============
DO $$
DECLARE
    p_program_template_id BIGINT := 1;        -- SET YOUR TEMPLATE ID HERE
    p_created_by INTEGER := -2;                 -- SET YOUR USER ID HERE
    
    -- Internal variables
    v_master_section_id BIGINT;
    v_template_section_id BIGINT;
    v_section_map JSONB := '{}'::jsonb;
    v_sections_count INTEGER := 0;
    v_questions_count INTEGER := 0;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Starting clone for template_id: %', p_program_template_id;
    RAISE NOTICE '======================================';
    
    -- =====================================================
    -- STEP 1: Clone Root Sections (no parent)
    -- =====================================================
    
    RAISE NOTICE 'Cloning root sections...';
    
    FOR v_master_section_id IN
        SELECT id FROM master_form_section 
        WHERE parent_section_id IS NULL 
          AND deleted_at IS NULL 
          AND is_active = true
        ORDER BY id
    LOOP
        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
        )
        SELECT 
            p_program_template_id,
            mfs.id,
            mfs.section_key,
            mfs.name,
            mfs.description,
            NULL, -- Root sections have no parent
            mfs.conditional_config,
            v_sections_count + 1,
            NOW(),
            p_created_by,
            p_created_by
        FROM master_form_section mfs
        WHERE mfs.id = v_master_section_id
        RETURNING id INTO v_template_section_id;
        
        -- Map master section ID to template section ID
        v_section_map := jsonb_set(
            v_section_map, 
            ARRAY[v_master_section_id::text], 
            to_jsonb(v_template_section_id)
        );
        
        v_sections_count := v_sections_count + 1;
        RAISE NOTICE '  Cloned root section: master_id=%, template_id=%', v_master_section_id, v_template_section_id;
    END LOOP;
    
    -- =====================================================
    -- STEP 2: Clone Child Sections (with parent references)
    -- =====================================================
    
    RAISE NOTICE 'Cloning child sections...';
    
    FOR v_master_section_id IN
        SELECT id FROM master_form_section 
        WHERE parent_section_id IS NOT NULL 
          AND deleted_at IS NULL 
          AND is_active = true
        ORDER BY id
    LOOP
        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
        )
        SELECT 
            p_program_template_id,
            mfs.id,
            mfs.section_key,
            mfs.name,
            mfs.description,
            (v_section_map->>mfs.parent_section_id::text)::BIGINT, -- Map parent to template section
            mfs.conditional_config,
            v_sections_count + 1,
            NOW(),
            p_created_by,
            p_created_by
        FROM master_form_section mfs
        WHERE mfs.id = v_master_section_id
        RETURNING id INTO v_template_section_id;
        
        -- Map master section ID to template section ID
        v_section_map := jsonb_set(
            v_section_map, 
            ARRAY[v_master_section_id::text], 
            to_jsonb(v_template_section_id)
        );
        
        v_sections_count := v_sections_count + 1;
        RAISE NOTICE '  Cloned child section: master_id=%, template_id=%', v_master_section_id, v_template_section_id;
    END LOOP;
    
    -- =====================================================
    -- STEP 3: Clone Master Questions
    -- =====================================================
    
    RAISE NOTICE 'Cloning questions...';
    
    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,
        display_order,
        created_at,
        created_by,
        updated_at,
        updated_by
    )
    SELECT 
        (v_section_map->>mq.master_form_section_id::text)::BIGINT, -- Map to template section
        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,
        row_number() OVER (PARTITION BY mq.master_form_section_id ORDER BY mq.id), -- Sequential display order
        NOW(),
        p_created_by,
        NOW(),
        p_created_by
    FROM master_question mq
    INNER JOIN master_form_section mfs ON mfs.id = mq.master_form_section_id
    WHERE mq.deleted_at IS NULL 
      AND mq.is_active = true
      AND mfs.deleted_at IS NULL
      AND mfs.is_active = true;
    
    GET DIAGNOSTICS v_questions_count = ROW_COUNT;
    
    -- =====================================================
    -- STEP 4: Report Summary
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Clone completed successfully!';
    RAISE NOTICE 'Template ID: %', p_program_template_id;
    RAISE NOTICE 'Sections cloned: %', v_sections_count;
    RAISE NOTICE 'Questions cloned: %', v_questions_count;
    RAISE NOTICE '======================================';
END $$;


-- =====================================================
-- SCRIPT 2: Publish Template (Direct SQL)
-- Changes status from DRAFT to PUBLISHED (locks entire template)
-- =====================================================

-- ============ SET THESE VARIABLES ============
DO $$
DECLARE
    p_program_template_id BIGINT := 1;        -- SET YOUR TEMPLATE ID HERE
    p_updated_by INTEGER := -2;                 -- SET YOUR USER ID HERE
    
    v_section_count INTEGER;
    v_question_count INTEGER;
    v_current_status template_status;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Publishing template_id: %', p_program_template_id;
    RAISE NOTICE '======================================';
    
    -- Check if template exists and is in DRAFT status
    SELECT status INTO v_current_status
    FROM program_template
    WHERE id = p_program_template_id
      AND deleted_at IS NULL;
    
    IF v_current_status IS NULL THEN
        RAISE EXCEPTION 'Template % not found', p_program_template_id;
    END IF;
    
    IF v_current_status != 'DRAFT' THEN
        RAISE EXCEPTION 'Template % is in % status, can only publish DRAFT templates', p_program_template_id, v_current_status;
    END IF;
    
    -- Update program_template status to PUBLISHED
    UPDATE program_template
    SET 
        status = 'PUBLISHED'::template_status,
        updated_at = NOW(),
        updated_by = p_updated_by
    WHERE id = p_program_template_id;
    
    -- Count sections and questions for reporting
    SELECT COUNT(*) INTO v_section_count
    FROM template_form_section
    WHERE program_template_id = p_program_template_id
      AND deleted_at IS NULL;
    
    SELECT COUNT(tq.id) INTO v_question_count
    FROM template_question tq
    JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
    WHERE tfs.program_template_id = p_program_template_id
      AND tq.deleted_at IS NULL;
    
    RAISE NOTICE 'Template published successfully!';
    RAISE NOTICE 'Template now locked with % sections and % questions', v_section_count, v_question_count;
    RAISE NOTICE '======================================';
END $$;


-- =====================================================
-- SCRIPT 3: Publish Template (Direct SQL)
-- Changes status to PUBLISHED
-- =====================================================

-- ============ SET THESE VARIABLES ============
DO $$
DECLARE
    p_program_template_id BIGINT := 1;        -- SET YOUR TEMPLATE ID HERE
    p_updated_by INTEGER := -2;                 -- SET YOUR USER ID HERE
    
    v_section_count INTEGER;
    v_current_status template_status;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Publishing template_id: %', p_program_template_id;
    RAISE NOTICE '======================================';
    
    -- Check if template exists and is PUBLISHED
    SELECT status INTO v_current_status
    FROM program_template
    WHERE id = p_program_template_id
      AND deleted_at IS NULL;
    
    IF v_current_status IS NULL THEN
        RAISE EXCEPTION 'Template % not found', p_program_template_id;
    END IF;
    
    IF v_current_status != 'PUBLISHED' THEN
        RAISE EXCEPTION 'Template % is in % status, can only archive PUBLISHED templates', p_program_template_id, v_current_status;
    END IF;
    
    -- Update program_template status to PUBLISHED
    UPDATE program_template
    SET 
        status = 'PUBLISHED'::template_status,
        updated_at = NOW(),
        updated_by = p_updated_by
    WHERE id = p_program_template_id;
    
    -- Count sections for reporting
    SELECT COUNT(*) INTO v_section_count
    FROM template_form_section
    WHERE program_template_id = p_program_template_id
      AND deleted_at IS NULL;
    
    RAISE NOTICE 'Template published successfully!';
    RAISE NOTICE 'Template with % sections is now read-only', v_section_count;
    RAISE NOTICE '======================================';
END $$;


-- =====================================================
-- SCRIPT 4: Validate Template Completeness (Direct SQL)
-- Checks if template has all required sections and questions
-- =====================================================

-- ============ SET THESE VARIABLES ============
DO $$
DECLARE
    p_program_template_id BIGINT := 1;        -- SET YOUR TEMPLATE ID HERE
    
    v_section_count INTEGER;
    v_question_count INTEGER;
    v_master_section_count INTEGER;
    v_master_question_count INTEGER;
    v_missing_sections INTEGER;
    v_missing_questions INTEGER;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Validating template_id: %', p_program_template_id;
    RAISE NOTICE '======================================';
    
    -- Count template sections
    SELECT COUNT(*) INTO v_section_count
    FROM template_form_section
    WHERE program_template_id = p_program_template_id
      AND deleted_at IS NULL;
    
    -- Count template questions
    SELECT COUNT(*) INTO v_question_count
    FROM template_question tq
    INNER JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
    WHERE tfs.program_template_id = p_program_template_id
      AND tq.deleted_at IS NULL
      AND tfs.deleted_at IS NULL;
    
    -- Count active master sections
    SELECT COUNT(*) INTO v_master_section_count
    FROM master_form_section
    WHERE is_active = TRUE AND deleted_at IS NULL;
    
    -- Count active master questions
    SELECT COUNT(*) INTO v_master_question_count
    FROM master_question mq
    INNER JOIN master_form_section mfs ON mfs.id = mq.master_form_section_id
    WHERE mq.is_active = TRUE 
      AND mq.deleted_at IS NULL
      AND mfs.is_active = TRUE
      AND mfs.deleted_at IS NULL;
    
    -- Calculate missing counts
    v_missing_sections := GREATEST(0, v_master_section_count - v_section_count);
    v_missing_questions := GREATEST(0, v_master_question_count - v_question_count);
    
    -- Display results
    RAISE NOTICE 'Template Sections: % (Master has %)', v_section_count, v_master_section_count;
    RAISE NOTICE 'Template Questions: % (Master has %)', v_question_count, v_master_question_count;
    
    IF v_missing_sections > 0 OR v_missing_questions > 0 THEN
        RAISE NOTICE 'STATUS: INCOMPLETE';
        RAISE NOTICE 'Missing Sections: %', v_missing_sections;
        RAISE NOTICE 'Missing Questions: %', v_missing_questions;
    ELSE
        RAISE NOTICE 'STATUS: VALID ✓';
        RAISE NOTICE 'Template is complete!';
    END IF;
    
    RAISE NOTICE '======================================';
END $$;
