-- =====================================================
-- SCRIPT: Create ENT Template from Existing Program
-- UPDATED:
--   1) Replaces travelPlanOnward/Return with dependent versions
--   2) Includes registrationForWhom from master_question in FS_BASICDETAILS
--   3) Fixes mobileNumber: replaces phoneNumberType disable condition
--      with registerForWhom / selfRegister disable
--   4) FS_GOODIES removed from cloned sections; only goodiesTshirtApplicable
--      and goodiesTshirtSize are injected at the top of FS_TRAVELPLAN
-- =====================================================

DO $$
DECLARE
    p_source_program_id BIGINT := 1082;
    p_program_type_key VARCHAR(50) := 'PT_ENTRAINMENT';
    p_template_name VARCHAR(255) := 'ENT 25 Template';
    p_template_description TEXT := 'ENT Template - Payment & Basic Details Only';
    p_template_version INTEGER := 1;
    p_created_by INTEGER := -2;

    p_included_sections TEXT[] := ARRAY['FS_PAYMENTINVOICE', 'FS_BASICDETAILS', 'FS_TRAVELPLAN'];

    -- travelPlanOnward/Return excluded — replaced with travelPlanDependOnward/Return
    -- ENT does not include relation (phoneNumberRelation) or phoneNumberType questions
    p_excluded_questions TEXT[] := ARRAY[
        'QK_LAST_HDB',
        'no_of_hdbs',
        'preference',
        'travelPlanOnward',
        'travelPlanReturn',
        'phoneNumberType',
        'phoneNumberRelation',
        'otherRelation'
    ];

    v_program_type_id                   INTEGER;
    v_template_id                       BIGINT;
    v_section_map                       JSONB := '{}'::jsonb;
    v_template_section_id               BIGINT;
    v_master_question_id                BIGINT;
    v_registration_for_whom_id          BIGINT;
    v_basic_details_template_section_id BIGINT;
    v_travel_template_section_id        BIGINT;
    v_registrationForWhom_tq_id         BIGINT;
    v_mobile_tq_id                      BIGINT;
    v_travel_details_mq_id              BIGINT;
    v_depend_onward_order               INTEGER;
    v_sections_cloned                   INTEGER := 0;
    v_questions_cloned                  INTEGER := 0;
    v_questions_excluded                INTEGER := 0;
    v_questions_not_found               INTEGER := 0;
    v_total_program_questions           INTEGER;
    v_is_excluded                       BOOLEAN;
    v_section_display_order             INTEGER;
    v_terms_mq_id                       BIGINT;
    v_max_display_order                 INTEGER;
    r_program_question RECORD;
    r_section          RECORD;
BEGIN

-- =====================================================
-- STEP 0: Validate source program exists
-- =====================================================

SELECT COUNT(*) INTO v_total_program_questions
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
WHERE hpq.program_id = p_source_program_id
  AND hpq.deleted_at IS NULL
  AND hq.deleted_at  IS NULL
  AND hfs.key = ANY(p_included_sections);

RAISE NOTICE 'Source program questions in included sections: %', v_total_program_questions;

-- =====================================================
-- STEP 1: Resolve 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 not found for key: %', p_program_type_key;
END IF;

-- =====================================================
-- STEP 2: Create template record
-- =====================================================

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', TRUE,
    NOW(), NOW(), p_created_by, p_created_by
)
RETURNING id INTO v_template_id;

RAISE NOTICE 'Template created with ID: %', v_template_id;

-- =====================================================
-- STEP 3: Clone sections
-- =====================================================

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 hfs.key = ANY(p_included_sections)
LOOP
    v_section_display_order := CASE r_section.section_key
        WHEN 'FS_BASICDETAILS'   THEN 1
        WHEN 'FS_PAYMENTINVOICE' THEN 3
        WHEN 'FS_TRAVELPLAN'     THEN 4
        ELSE v_sections_cloned + 5
    END;

    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,
        r_section.conditional_config,
        v_section_display_order,
        NOW(),
        p_created_by,
        p_created_by
    )
    RETURNING id INTO v_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;
END LOOP;

RAISE NOTICE 'Sections cloned: %', v_sections_cloned;

-- =====================================================
-- STEP 4: Clone questions
-- =====================================================

FOR r_program_question IN
    SELECT
        hpq.display_order,

        -- Replace travelPlanOnward/Return with their dependent variants
        CASE
            WHEN hq.binding_key = 'travelPlanOnward' THEN 'travelPlanDependOnward'
            WHEN hq.binding_key = 'travelPlanReturn'  THEN 'travelPlanDependReturn'
            ELSE hq.binding_key
        END AS binding_key,

        hq.form_section_id          AS hdb_section_id,
        hfs.key                     AS section_key,
        hq.conditional_config       AS hdb_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
    WHERE hpq.program_id = p_source_program_id
      AND hq.deleted_at  IS NULL
      AND hfs.key = ANY(p_included_sections)
    ORDER BY hpq.display_order
LOOP
    v_is_excluded := r_program_question.binding_key = ANY(p_excluded_questions);

    IF v_is_excluded THEN
        v_questions_excluded := v_questions_excluded + 1;
        CONTINUE;
    END IF;

    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
        v_questions_not_found := v_questions_not_found + 1;
        CONTINUE;
    END IF;

    v_template_section_id := (v_section_map->>r_program_question.hdb_section_id::text)::BIGINT;

    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;
END LOOP;

RAISE NOTICE 'Questions cloned: %, excluded: %, not found: %',
    v_questions_cloned, v_questions_excluded, v_questions_not_found;

-- =====================================================
-- STEP 5: Ensure registrationForWhom in FS_BASICDETAILS
-- =====================================================

SELECT id INTO v_registration_for_whom_id
FROM master_question
WHERE binding_key = 'registrationForWhom'
  AND deleted_at  IS NULL
  AND is_active   = TRUE
LIMIT 1;

IF v_registration_for_whom_id IS NOT NULL THEN
    SELECT id INTO v_basic_details_template_section_id
    FROM template_form_section
    WHERE program_template_id = v_template_id
      AND section_key         = 'FS_BASICDETAILS'
      AND deleted_at          IS NULL
    LIMIT 1;

    IF v_basic_details_template_section_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1
            FROM template_question
            WHERE template_form_section_id = v_basic_details_template_section_id
              AND binding_key              = 'registrationForWhom'
              AND deleted_at              IS NULL
        ) THEN
            -- Shift existing FS_BASICDETAILS questions down to make room at position 0
            UPDATE template_question
            SET display_order = display_order + 1
            WHERE template_form_section_id = v_basic_details_template_section_id
              AND deleted_at               IS NULL;

            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_basic_details_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,
                mq.conditional_config,
                0,
                NOW(),
                p_created_by,
                NOW(),
                p_created_by
            FROM master_question mq
            WHERE mq.id = v_registration_for_whom_id
            RETURNING id INTO v_registrationForWhom_tq_id;

            RAISE NOTICE 'registrationForWhom inserted at display_order 0, tq_id: %', v_registrationForWhom_tq_id;
        ELSE
            SELECT id INTO v_registrationForWhom_tq_id
            FROM template_question
            WHERE template_form_section_id = v_basic_details_template_section_id
              AND binding_key              = 'registrationForWhom'
              AND deleted_at              IS NULL
            LIMIT 1;
        END IF;
    END IF;
END IF;

-- =====================================================
-- STEP 6: Inject goodiesTshirtApplicable + goodiesTshirtSize
--         at the top of FS_TRAVELPLAN (display_order 0, 1)
-- =====================================================

SELECT id INTO v_travel_template_section_id
FROM template_form_section
WHERE program_template_id = v_template_id
  AND section_key         = 'FS_TRAVELPLAN'
  AND deleted_at          IS NULL
LIMIT 1;

IF v_travel_template_section_id IS NOT NULL THEN
    -- Shift all existing FS_TRAVELPLAN questions down by 2
    UPDATE template_question
    SET display_order = display_order + 2
    WHERE template_form_section_id = v_travel_template_section_id
      AND deleted_at               IS NULL;

    -- Insert goodiesTshirtApplicable at position 0
    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_travel_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, mq.conditional_config,
        0, NOW(), p_created_by, NOW(), p_created_by
    FROM master_question mq
    WHERE mq.binding_key = 'goodiesTshirtApplicable'
      AND mq.deleted_at  IS NULL
      AND mq.is_active   = TRUE
    LIMIT 1;

    -- Insert goodiesTshirtSize at position 1
    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_travel_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, mq.conditional_config,
        1, NOW(), p_created_by, NOW(), p_created_by
    FROM master_question mq
    WHERE mq.binding_key = 'goodiesTshirtSize'
      AND mq.deleted_at  IS NULL
      AND mq.is_active   = TRUE
    LIMIT 1;

    RAISE NOTICE 'goodiesTshirtApplicable and goodiesTshirtSize injected at top of FS_TRAVELPLAN';
ELSE
    RAISE NOTICE 'WARNING: FS_TRAVELPLAN section not found — goodies questions not injected';
END IF;


-- =====================================================
-- STEP 6b: Inject travelDetails before travelPlanDependOnward
--          in FS_TRAVELPLAN
-- =====================================================

IF v_travel_template_section_id IS NOT NULL THEN
    SELECT id INTO v_travel_details_mq_id
    FROM master_question
    WHERE binding_key = 'travelDetails'
      AND deleted_at  IS NULL
      AND is_active   = TRUE
    LIMIT 1;

    IF v_travel_details_mq_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_travel_template_section_id
              AND binding_key              = 'travelDetails'
              AND deleted_at              IS NULL
        ) THEN
            -- Find the display_order of travelPlanDependOnward (already shifted +2 by STEP 6)
            SELECT display_order INTO v_depend_onward_order
            FROM template_question
            WHERE template_form_section_id = v_travel_template_section_id
              AND binding_key              = 'travelPlanDependOnward'
              AND deleted_at              IS NULL
            LIMIT 1;

            IF v_depend_onward_order IS NOT NULL THEN
                -- Shift travelPlanDependOnward and everything after it down by 1
                UPDATE template_question
                SET display_order = display_order + 1
                WHERE template_form_section_id = v_travel_template_section_id
                  AND display_order            >= v_depend_onward_order
                  AND deleted_at              IS NULL;

                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_travel_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, mq.conditional_config,
                    v_depend_onward_order, NOW(), p_created_by, NOW(), p_created_by
                FROM master_question mq
                WHERE mq.id = v_travel_details_mq_id;

                RAISE NOTICE 'travelDetails injected at display_order % in FS_TRAVELPLAN', v_depend_onward_order;
            ELSE
                -- travelPlanDependOnward not found — append at end
                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_travel_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, mq.conditional_config,
                    COALESCE((
                        SELECT MAX(display_order) + 1
                        FROM template_question
                        WHERE template_form_section_id = v_travel_template_section_id
                          AND deleted_at IS NULL
                    ), 0),
                    NOW(), p_created_by, NOW(), p_created_by
                FROM master_question mq
                WHERE mq.id = v_travel_details_mq_id;

                RAISE NOTICE 'travelDetails appended to FS_TRAVELPLAN (travelPlanDependOnward not found)';
            END IF;
        END IF;
    END IF;
END IF;


-- =====================================================
-- STEP 7: Fix mobileNumber — replace phoneNumberType disable
--         with registerForWhom / selfRegister disable
-- =====================================================

SELECT tq.id INTO v_mobile_tq_id
FROM template_question tq
INNER JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
WHERE tfs.program_template_id = v_template_id
  AND tq.binding_key           = 'mobileNumber'
  AND tq.deleted_at           IS NULL
LIMIT 1;

IF v_mobile_tq_id IS NOT NULL THEN
    UPDATE template_question
    SET config = jsonb_set(
        COALESCE(config, '{"dependsOn": []}'::jsonb),
        '{dependsOn}',
        (
            -- Keep all existing conditions except phoneNumberType disable,
            -- then append the registerForWhom disable condition
            COALESCE(
                (
                    SELECT jsonb_agg(cond)
                    FROM jsonb_array_elements(
                        COALESCE(config->'dependsOn', '[]'::jsonb)
                    ) AS cond
                    WHERE NOT (
                        cond->>'questionBindingKey' = 'phoneNumberType'
                        OR cond->>'questionBindingKey' = 'phoneNumberRelation'
                    )
                ),
                '[]'::jsonb
            )
            || jsonb_build_array(
                jsonb_build_object(
                    'type',               'disable',
                    'value',              'Register for Myself',
                    'questionBindingKey', 'registerForWhom'
                )
            )
        )
    )
    WHERE id = v_mobile_tq_id;

    RAISE NOTICE 'mobileNumber conditional_config updated with registerForWhom disable';
END IF;


-- =====================================================
-- STEP 8: Inject terms at the end of FS_BASICDETAILS
-- =====================================================

SELECT id INTO v_terms_mq_id
FROM master_question
WHERE binding_key = 'terms'
  AND deleted_at  IS NULL
  AND is_active   = TRUE
LIMIT 1;

IF v_terms_mq_id IS NOT NULL AND v_basic_details_template_section_id IS NOT NULL THEN
    IF NOT EXISTS (
        SELECT 1 FROM template_question
        WHERE template_form_section_id = v_basic_details_template_section_id
          AND binding_key              = 'terms'
          AND deleted_at              IS NULL
    ) THEN
        SELECT COALESCE(MAX(display_order), -1) + 1
        INTO v_max_display_order
        FROM template_question
        WHERE template_form_section_id = v_basic_details_template_section_id
          AND deleted_at              IS NULL;

        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_basic_details_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, mq.conditional_config,
            v_max_display_order, NOW(), p_created_by, NOW(), p_created_by
        FROM master_question mq
        WHERE mq.id = v_terms_mq_id;

        v_questions_cloned := v_questions_cloned + 1;
        RAISE NOTICE 'STEP 8: terms injected at display_order % in FS_BASICDETAILS', v_max_display_order;
    ELSE
        RAISE NOTICE 'STEP 8: terms already present in FS_BASICDETAILS, skipping';
    END IF;
ELSE
    RAISE WARNING 'STEP 8: terms master question or FS_BASICDETAILS section not found';
END IF;


-- =====================================================
-- FINAL SUMMARY
-- =====================================================

RAISE NOTICE '=== ENT Template Creation Complete ===';
RAISE NOTICE 'Template ID       : %', v_template_id;
RAISE NOTICE 'Sections cloned   : %', v_sections_cloned;
RAISE NOTICE 'Questions cloned  : %', v_questions_cloned;
RAISE NOTICE 'Questions excluded: %', v_questions_excluded;
RAISE NOTICE 'Questions not found (skipped): %', v_questions_not_found;

END $$;
