-- =====================================================
-- SCRIPT: Create CUSTOM Program Type Template
-- Based on HDB template structure (04_hdb_template.sql)
-- All 5 sections included — runtime visibility is
-- flag-driven per program, not per template:
--   FS_BASICDETAILS      → always shown
--   FS_MAHATRIAQUESTIONS → shown when requires_approval = true
--   FS_PAYMENTINVOICE    → shown when requires_payment = true
--   FS_TRAVELPLAN        → shown when involves_travel = true
--   FS_GOODIES           → shown when has_goodies = true
--
-- No question exclusions — CUSTOM carries all questions
-- from the source HDB program.
--
-- Final SELECT lists master questions not captured in
-- the template so gaps can be reviewed before publishing.
-- =====================================================

UPDATE master_form_section
SET conditional_config = '[{"metaKey":"requiresApproval","operator":"equals","value":[true]}]'::jsonb
WHERE section_key = 'FS_MAHATRIAQUESTIONS'
  AND deleted_at IS NULL;


DO $$
DECLARE
    -- ============ SET THESE VARIABLES ============
    p_source_program_id   BIGINT      := 1082;               -- SOURCE HDB PROGRAM ID
    p_program_type_key    VARCHAR(50) := 'CUSTOM';            -- CUSTOM program type key
    p_template_name       VARCHAR(255) := 'CUSTOM Program Template v1';
    p_template_description TEXT        := 'Universal CUSTOM program template — all stages included; runtime flag-gating controls which stages are shown per program';
    p_template_version    INTEGER      := 1;
    p_created_by          INTEGER      := -2;

    -- Internal variables
    v_program_type_id         INTEGER;
    v_template_id             BIGINT;
    v_section_map             JSONB    := '{}'::jsonb;
    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;
    v_other_display_order     INTEGER  := 6;
    v_section_display_order   INTEGER;
    v_basic_details_tfs_id    BIGINT;
    v_travel_tfs_id           BIGINT;
    v_max_display_order       INTEGER;
    v_registration_for_whom_mq_id BIGINT;
    v_notes_mq_id             BIGINT;
    v_terms_mq_id             BIGINT;
    v_travel_details_mq_id    BIGINT;
    v_depend_onward_order     INTEGER;

    r_program_question RECORD;
    r_section          RECORD;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Creating CUSTOM 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 source program %', v_total_program_questions, p_source_program_id;

    -- =====================================================
    -- STEP 1: Resolve CUSTOM 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: % — run 2026-05-13-custom-program-type-universal-workflow.sql first', p_program_type_key;
    END IF;

    RAISE NOTICE 'Resolved program type: % (ID: %)', p_program_type_key, v_program_type_id;

    -- =====================================================
    -- STEP 2: Create program 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'::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 root sections
    -- All 5 CUSTOM sections are included in the template;
    -- runtime flag-gating determines which ones render.
    -- =====================================================

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning root sections...';
    RAISE NOTICE '======================================';

    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
        ORDER BY hfs.id
    LOOP
        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;

        v_section_display_order := CASE r_section.section_key
            WHEN 'FS_BASICDETAILS'      THEN 1
            WHEN 'FS_MAHATRIAQUESTIONS' THEN 2
            WHEN 'FS_PAYMENTINVOICE'    THEN 3
            WHEN 'FS_TRAVELPLAN'        THEN 4
            WHEN 'FS_GOODIES'           THEN 5
            ELSE v_other_display_order
        END;

        IF r_section.section_key NOT IN (
            'FS_BASICDETAILS', 'FS_MAHATRIAQUESTIONS',
            'FS_PAYMENTINVOICE', 'FS_TRAVELPLAN', 'FS_GOODIES'
        ) THEN
            v_other_display_order := v_other_display_order + 1;
            WHILE v_other_display_order = ANY(ARRAY[1,2,3,4,5]) LOOP
                v_other_display_order := v_other_display_order + 1;
            END LOOP;
        END IF;

        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;
        RAISE NOTICE '  ✓ Root section: % (hdb_id=%, tpl_id=%, 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
        ORDER BY hfs.id
    LOOP
        IF r_section.master_section_id IS NULL THEN
            RAISE WARNING 'Child section key "%" not found in master_form_section, skipping', r_section.section_key;
            CONTINUE;
        END IF;

        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 mapped, skipping child', r_section.section_key;
            CONTINUE;
        END IF;

        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,
            r_section.conditional_config,
            v_sections_cloned + 1,
            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;
        RAISE NOTICE '  ✓ Child section: % (hdb_id=%, tpl_id=%)',
            r_section.section_key, r_section.hdb_section_id, v_template_section_id;
    END LOOP;

    RAISE NOTICE 'Sections cloned: %', v_sections_cloned;

    -- =====================================================
    -- STEP 4: Clone questions (mapped via binding_key)
    -- No exclusions — CUSTOM carries all questions;
    -- flag-gating at the workflow/stage level controls
    -- which sections a seeker actually sees.
    -- =====================================================

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning questions...';
    RAISE NOTICE '======================================';

    FOR r_program_question IN
        SELECT
            hpq.id            AS program_question_id,
            hpq.question_id   AS hdb_question_id,
            hpq.display_order,
            -- Replace onward/return with dependent variants (same as ENT template)
            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,
            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
        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 '  ✗ Not in master: binding_key=%, hdb_id=%',
                r_program_question.binding_key, r_program_question.hdb_question_id;
            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;

        IF v_template_section_id IS NULL THEN
            RAISE WARNING '  ✗ Section not mapped for: 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 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: Inject registrationForWhom at position 0
    --         in FS_BASICDETAILS (shift existing questions down)
    -- =====================================================

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

    SELECT id INTO v_basic_details_tfs_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_registration_for_whom_mq_id IS NOT NULL AND v_basic_details_tfs_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_basic_details_tfs_id
              AND binding_key              = 'registrationForWhom'
              AND deleted_at              IS NULL
        ) THEN
            UPDATE template_question
            SET display_order = display_order + 1
            WHERE template_form_section_id = v_basic_details_tfs_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_tfs_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_mq_id;

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

    -- =====================================================
    -- STEP 6: Inject notes at the end of FS_BASICDETAILS
    -- =====================================================

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

    IF v_notes_mq_id IS NOT NULL AND v_basic_details_tfs_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_basic_details_tfs_id
              AND binding_key              = 'notes'
              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_tfs_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_tfs_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_notes_mq_id;

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

    -- =====================================================
    -- STEP 7: Inject travelDetails before travelPlanDependOnward
    --         in FS_TRAVELPLAN
    -- =====================================================

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

    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 AND v_travel_tfs_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_travel_tfs_id
              AND binding_key              = 'travelDetails'
              AND deleted_at              IS NULL
        ) THEN
            -- Find the display_order of travelPlanDependOnward to insert just before it
            SELECT display_order INTO v_depend_onward_order
            FROM template_question
            WHERE template_form_section_id = v_travel_tfs_id
              AND binding_key              = 'travelPlanDependOnward'
              AND deleted_at              IS NULL
            LIMIT 1;

            IF v_depend_onward_order IS NULL THEN
                -- travelPlanDependOnward not found — append at end
                SELECT COALESCE(MAX(display_order), -1) + 1
                INTO v_depend_onward_order
                FROM template_question
                WHERE template_form_section_id = v_travel_tfs_id
                  AND deleted_at              IS NULL;
            ELSE
                -- 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_tfs_id
                  AND display_order            >= v_depend_onward_order
                  AND deleted_at              IS NULL;
            END IF;

            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_tfs_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;

            v_questions_cloned := v_questions_cloned + 1;
            RAISE NOTICE 'STEP 7: travelDetails injected at display_order % in FS_TRAVELPLAN (before travelPlanDependOnward)', v_depend_onward_order;
        ELSE
            RAISE NOTICE 'STEP 7: travelDetails already present in FS_TRAVELPLAN, skipping';
        END IF;
    ELSE
        RAISE WARNING 'STEP 7: travelDetails master question or FS_TRAVELPLAN section not found';
    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_tfs_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_basic_details_tfs_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_tfs_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_tfs_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 '======================================';
    RAISE NOTICE 'CUSTOM TEMPLATE CREATION COMPLETE';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Template ID          : %', v_template_id;
    RAISE NOTICE 'Template Name        : %', p_template_name;
    RAISE NOTICE 'Program Type         : % (ID: %)', p_program_type_key, v_program_type_id;
    RAISE NOTICE 'Status               : PUBLISHED';
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Source Program ID    : %', p_source_program_id;
    RAISE NOTICE 'Total Source Qs      : %', 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: % question(s) could not be mapped — check warnings above', v_questions_not_found;
    END IF;

    IF v_questions_cloned = 0 THEN
        RAISE WARNING 'NO QUESTIONS CLONED — template may be incomplete';
    END IF;

    RAISE NOTICE 'Review unclassified master questions in SELECT below.';
    RAISE NOTICE '======================================';

END $$;

-- =====================================================
-- POST-CREATION: Master questions NOT in this template
-- Review this list to decide if any should be added
-- manually before publishing.
-- =====================================================

SELECT
    mq.binding_key,
    mq.question_text,
    mq.question_type,
    mq.answer_type,
    mfs.section_key AS default_section
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 NOT EXISTS (
      SELECT 1
      FROM template_question     tq
      INNER JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
      INNER JOIN program_template      pt  ON pt.id  = tfs.program_template_id
      WHERE pt.name       = 'CUSTOM Program Template v1'
        AND pt.deleted_at IS NULL
        AND tq.binding_key = mq.binding_key
        AND tq.deleted_at IS NULL
  )
ORDER BY mfs.section_key, mq.binding_key;
