-- ================================================
-- PT_ENTRAINMENT Program Template Refresh
-- 2026-08-10
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- No schema changes. Seed data only.
-- ================================================
--
-- WHY: PT_ENTRAINMENT (program_type_v1.key = 'PT_ENTRAINMENT') is a live, existing residential
-- program type whose registration form (program_template + template_form_section +
-- template_question) was originally seeded by
-- database/program-configuration/06_ent_template.sql. That script clones its content live from
-- a source program's legacy question data (hdb_program_question / hdb_question /
-- hdb_form_section for p_source_program_id = 1082), resolving canonical field metadata via
-- master_question / master_form_section by binding_key / section_key.
--
-- This migration supersedes that seed: it soft-deletes PT_ENTRAINMENT's CURRENT live
-- program_template tree (and its template_form_section / template_question children), then
-- recreates a brand-new tree reproducing the exact same sections/questions/config, so
-- PT_ENTRAINMENT ends up with exactly one live, fresh template tree (new surrogate IDs,
-- deleted_at IS NULL). This mirrors the pattern just established for the new program type
-- PT_PAP in database/migrations/2026-08-07-02-pt-pap-program-template.sql — same
-- header/DECLARE/RAISE NOTICE conventions, same p_created_by := -2 sentinel admin user id, same
-- NOW() timestamp convention, same jsonb-based section_map ID-wiring technique (STEP 3 below,
-- keyed by legacy hdb_form_section.id, deduped by section_key via DISTINCT ON — the same fix
-- PAP's own migration applied over 06_ent_template.sql's plain DISTINCT, to guard against
-- multiple legacy hdb_form_section rows sharing one key).
--
-- CONTENT SOURCE: STEP 2 below is 06_ent_template.sql's own cloning logic (STEPs 0–8),
-- re-run in place, with the section-ID-wiring hardened as above. Re-running the identical
-- cloning logic against the same static source (program 1082's legacy question data, which
-- does not change) reproduces byte-for-byte equivalent labels/types/validation
-- config/dependsOn/options/ordering — only the surrogate IDs and timestamps are new, exactly as
-- the task requires. Nothing here is invented; every field value traces back to
-- 06_ent_template.sql's own STEP 2–8 SELECTs/INSERTs, reproduced verbatim below.
--
-- IN SCOPE: program_template, template_form_section, template_question rows scoped to
-- PT_ENTRAINMENT only.
-- OUT OF SCOPE (never touched): master_form_section / master_question — the shared question
-- library used by ALL program types. Confirmed by reading
-- database/program-configuration/17_master_seed_data_entrainment.sql (touches only
-- communication_templates_master / program_type_v1 / program_v1 — email/WhatsApp templates,
-- unrelated to the registration form) and
-- database/program-configuration/09_clone-templates-to-existing-programs.sql (touches only
-- hdb_communication_templates / merge_info_answer_location_map — also communication templates,
-- not the form template tables). Neither file touches program_template / template_form_section
-- / template_question or master_form_section / master_question, so neither is in scope here.
--
-- UPDATE (per explicit instruction): the pro-forma invoice fields (existingProformaInvoice,
-- pro_forma_invoice_name, proFormaBillingAddress, proFormaIsGstRegistered, proFormaZip,
-- proFormaGstNumber) are now blacklisted in STEP 2's p_excluded_questions, so ENT no longer
-- collects a separate pro-forma billing identity. See the DECLARE block comment above that
-- array for the full rationale. The FS_PAYMENTINVOICE fields that prefill FROM them
-- (invoiceName, gstNumber, zip, isGstRegistered, invoiceAddress) are unaffected — only the
-- pro-forma source fields and the "use existing pro-forma details" toggle are removed.
--
-- OPEN FLAGS — please double-check before running:
--   1. 06_ent_template.sql's own p_included_sections is ONLY
--      ['FS_PAYMENTINVOICE', 'FS_BASICDETAILS', 'FS_TRAVELPLAN'] — three sections. Its header
--      explicitly says FS_GOODIES was REMOVED from the cloned sections (goodiesTshirtApplicable
--      / goodiesTshirtSize questions are injected directly into FS_TRAVELPLAN instead, see STEP
--      6 below), and FS_TRAVELINFO never appears anywhere in 06_ent_template.sql or any other
--      PT_ENTRAINMENT-touching file in this repo (confirmed by grepping
--      database/migrations/**, database/program-configuration/** for
--      'PT_ENTRAINMENT'/'FS_TRAVELINFO'/'FS_GOODIES'). So this migration reproduces exactly 3
--      sections (FS_BASICDETAILS, FS_PAYMENTINVOICE, FS_TRAVELPLAN), NOT 5 — if
--      FS_TRAVELINFO/FS_GOODIES are expected to exist on PT_ENTRAINMENT's live form today,
--      that would mean either (a) a separate, later migration added them outside this repo's
--      tracked SQL history, or (b) they were never actually part of PT_ENTRAINMENT's form and
--      the assumption they exist is stale. Confirm against the live DB before running.
--   2. Sentinel admin user id (p_created_by = -2) and source program id (p_source_program_id =
--      1082) are taken verbatim from 06_ent_template.sql / the PT_PAP migration — not
--      independently re-verified against the live DB here.
-- ================================================

-- =====================================================
-- STEP 1: Soft-delete PT_ENTRAINMENT's current live program_template tree
-- Cascades deleted_at = NOW() from program_template -> template_form_section ->
-- template_question. Every statement is scoped by "deleted_at IS NULL", so this is idempotent
-- and safe to re-run (a second run simply finds nothing left to soft-delete).
-- =====================================================

UPDATE template_question
SET deleted_at = NOW()
WHERE deleted_at IS NULL
  AND template_form_section_id IN (
      SELECT tfs.id
      FROM template_form_section tfs
      INNER JOIN program_template pt ON pt.id = tfs.program_template_id
      WHERE tfs.deleted_at IS NULL
        AND pt.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
        AND pt.deleted_at IS NULL
  );

UPDATE template_form_section
SET deleted_at = NOW()
WHERE deleted_at IS NULL
  AND program_template_id IN (
      SELECT id
      FROM program_template
      WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
        AND deleted_at IS NULL
  );

UPDATE program_template
SET deleted_at = NOW()
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
  AND deleted_at IS NULL;

-- =====================================================
-- STEP 2: Recreate PT_ENTRAINMENT's template tree from scratch
-- Reproduces 06_ent_template.sql's STEP 0–8 cloning logic verbatim (same source program,
-- same inclusions/exclusions/injections), with STEP 3's section mapping hardened to key off
-- section_key (DISTINCT ON, mapping every legacy hdb_form_section.id sharing that key) rather
-- than plain DISTINCT on the full row — the same robustness fix already applied in
-- database/migrations/2026-08-07-02-pt-pap-program-template.sql's own STEP 3.
-- =====================================================

DO $$
DECLARE
    -- ============ SOURCE / TARGET ============
    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;
    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.
    -- QK_HDB_ASSOCIATION_SINCE ("Since when have you been associated with infinitheism?")
    -- excluded per R1024 — same exclusion 05_tat_template.sql already applies for PT_TAT.
    -- Pro-forma invoice fields excluded per explicit instruction — ENT does not collect a
    -- separate pro-forma billing identity; these binding keys carry hdb_form_section.key =
    -- 'FS_BASICDETAILS' in program 1082's legacy data (confirmed in
    -- database/migrations/2026-08-07-02-pt-pap-program-template.sql's own header), not
    -- FS_PAYMENTINVOICE, so they must be blacklisted here rather than dropped by omitting a
    -- section. The FS_PAYMENTINVOICE fields that prefill FROM them (invoiceName, gstNumber,
    -- zip, isGstRegistered, invoiceAddress) are left in place — this only removes the
    -- pro-forma source fields and the toggle that reuses them, not the actual invoice section.
    p_excluded_questions TEXT[] := ARRAY[
        'QK_LAST_HDB',
        'QK_HDB_ASSOCIATION_SINCE',
        'no_of_hdbs',
        'preference',
        'travelPlanOnward',
        'travelPlanReturn',
        'swipeDate', 'tidReferenceNo',
        'phoneNumberType',
        'phoneNumberRelation',
        'otherRelation',
        'existingProformaInvoice',
        'pro_forma_invoice_name',
        'proFormaBillingAddress',
        'proFormaIsGstRegistered',
        'proFormaZip',
        'proFormaGstNumber',
        'visa',
        'passport',
        'internationalId',
        'goodiesTshirtApplicable',
        'uploadOnwardJourneyTicket',
        'onwardInfo',
        'returnInfo',
        'uploadReturnJourneyTicket',
        'alternatePhoneNumber'
    ];

    v_program_type_id                   INTEGER;
    v_template_id                       BIGINT;
    v_section_map                       JSONB := '{}'::jsonb;
    v_hdb_section_id                    BIGINT;
    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

    RAISE NOTICE '======================================';
    RAISE NOTICE 'Refreshing PT_ENTRAINMENT 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 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);

    IF v_total_program_questions = 0 THEN
        RAISE EXCEPTION 'Program % has no questions in specified sections — cannot refresh PT_ENTRAINMENT template from it', p_source_program_id;
    END IF;

    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 — version auto-increments off whatever's already there
    -- for PT_ENTRAINMENT (the STEP 1 soft-delete above leaves the old row's version intact but
    -- deleted_at-marked), matching the versioning approach in
    -- 2026-08-07-02-pt-pap-program-template.sql's own STEP 2.
    -- =====================================================

    SELECT COALESCE(MAX(version), 0) + 1 INTO p_template_version
    FROM program_template
    WHERE program_type_id = v_program_type_id;

    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 'Template created with ID: % (version %)', v_template_id, p_template_version;

    -- =====================================================
    -- STEP 3: Clone sections — deduped by section_key (DISTINCT ON), not by hdb_section_id, so
    -- multiple legacy hdb_form_section rows sharing one key (if any) all map to a single new
    -- template_form_section. Every matching legacy hdb_form_section.id is mapped into
    -- v_section_map so STEP 4 finds a home for every question regardless of which duplicate
    -- row it's attached to.
    -- =====================================================

    FOR r_section IN
        SELECT DISTINCT ON (hfs.key)
            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.key = ANY(p_included_sections)
        ORDER BY hfs.key, hfs.id
    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;

        -- Map every legacy hdb_form_section.id sharing this key to the single new section.
        FOR v_hdb_section_id IN
            SELECT DISTINCT hfs2.id
            FROM hdb_form_section hfs2
            WHERE hfs2.key = r_section.section_key
        LOOP
            v_section_map := jsonb_set(
                v_section_map,
                ARRAY[v_hdb_section_id::text],
                to_jsonb(v_template_section_id)
            );
        END LOOP;

        v_sections_cloned := v_sections_cloned + 1;

        IF r_section.section_key = 'FS_BASICDETAILS' THEN
            v_basic_details_template_section_id := v_template_section_id;
        END IF;
        IF r_section.section_key = 'FS_TRAVELPLAN' THEN
            v_travel_template_section_id := v_template_section_id;
        END IF;
    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 hpq.deleted_at  IS NULL
          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;

        IF v_template_section_id IS NULL THEN
            v_questions_not_found := v_questions_not_found + 1;
            CONTINUE;
        END IF;

        IF EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_template_section_id
              AND binding_key              = r_program_question.binding_key
              AND deleted_at              IS NULL
        ) THEN
            CONTINUE; -- idempotency: skip on re-run
        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;
    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 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              = '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;

            v_questions_cloned := v_questions_cloned + 1;
            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;

    -- =====================================================
    -- STEP 6: Inject goodiesTshirtSize at the top of FS_TRAVELPLAN (display_order 0)
    -- goodiesTshirtApplicable is REMOVED per explicit instruction — only the size question is
    -- injected now. See the header UPDATE note above.
    -- =====================================================

    IF v_travel_template_section_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              = 'goodiesTshirtSize'
              AND deleted_at              IS NULL
        ) THEN
            -- Shift all existing FS_TRAVELPLAN questions down by 1
            UPDATE template_question
            SET display_order = display_order + 1
            WHERE template_form_section_id = v_travel_template_section_id
              AND deleted_at               IS NULL;

            -- Insert goodiesTshirtSize 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 = 'goodiesTshirtSize'
              AND mq.deleted_at  IS NULL
              AND mq.is_active   = TRUE
            LIMIT 1;

            v_questions_cloned := v_questions_cloned + 1;
            RAISE NOTICE 'goodiesTshirtSize injected at top of FS_TRAVELPLAN (goodiesTshirtApplicable excluded per explicit instruction)';
        END IF;
    ELSE
        RAISE WARNING 'FS_TRAVELPLAN section not found — goodiesTshirtSize 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 +1 by STEP 6;
                -- STEP 6c below pins the final order for the whole section regardless)
                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;

                    v_questions_cloned := v_questions_cloned + 1;
                    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;

                    v_questions_cloned := v_questions_cloned + 1;
                    RAISE NOTICE 'travelDetails appended to FS_TRAVELPLAN (travelPlanDependOnward not found)';
                END IF;
            END IF;
        END IF;
    END IF;

    -- =====================================================
    -- STEP 6c: Pin explicit display_order for the LEAD fields of FS_TRAVELPLAN only
    -- Supersedes whatever interim ordering STEP 6/6b's shift-based inserts left in place, for
    -- just these five — per explicit instruction: travelInfoType (ID type), pictureUrl (ID
    -- Front/Front+Back), idBack (ID only Back), then goodiesTshirtSize, then travelDetails.
    -- Everything else in the section is left on its default (cloned) order — not pinned here,
    -- per explicit instruction.
    -- =====================================================

    IF v_travel_template_section_id IS NOT NULL THEN
        UPDATE template_question tq
        SET display_order = ord.position
        FROM (VALUES
            ('travelInfoType',            0),
            ('pictureUrl',                1),
            ('idBack',                    2),
            ('goodiesTshirtSize',         3),
            ('travelDetails',             4)
        ) AS ord(binding_key, position)
        WHERE tq.template_form_section_id = v_travel_template_section_id
          AND tq.binding_key              = ord.binding_key
          AND tq.deleted_at              IS NULL;

        RAISE NOTICE 'FS_TRAVELPLAN display_order pinned to explicit sequence';
    END IF;

    -- =====================================================
    -- STEP 6d: Strip dependsOn from the lead FS_TRAVELPLAN fields
    -- travelInfoType, pictureUrl, and idBack each depend on 'internationalId' (see
    -- master_question.config), and goodiesTshirtSize depends on 'goodiesTshirtApplicable' —
    -- both source questions are in p_excluded_questions above, so on this template these
    -- dependsOn conditions would reference a question that doesn't exist. Per explicit
    -- instruction, dependsOn is removed entirely (not set to []) so these fields always
    -- display unconditionally.
    -- =====================================================

    IF v_travel_template_section_id IS NOT NULL THEN
        UPDATE template_question tq
        SET config     = tq.config - 'dependsOn',
            updated_at = NOW(),
            updated_by = p_created_by
        WHERE tq.binding_key IN ('travelInfoType', 'pictureUrl', 'idBack', 'goodiesTshirtSize')
          AND tq.deleted_at IS NULL
          AND tq.template_form_section_id = v_travel_template_section_id;

        RAISE NOTICE 'dependsOn stripped from travelInfoType/pictureUrl/idBack/goodiesTshirtSize';
    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 '=== PT_ENTRAINMENT Template Refresh 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;

    IF v_questions_cloned = 0 THEN
        RAISE WARNING 'NO QUESTIONS CLONED! Template may be incomplete.';
    END IF;

END $$;

-- =====================================================
-- VERIFICATION QUERIES (commented out — uncomment to run manually after migration)
-- =====================================================

-- -- Confirm exactly one live (non-deleted) program_template row remains for PT_ENTRAINMENT
-- SELECT id, name, version, status, is_active, created_at, deleted_at
-- FROM program_template
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
-- ORDER BY created_at DESC;

-- -- Row counts for the new (live) tree only
-- SELECT
--     pt.id                                                          AS program_template_id,
--     pt.version,
--     COUNT(DISTINCT tfs.id)                                         AS live_sections,
--     COUNT(tq.id)                                                   AS live_questions
-- FROM program_template pt
-- LEFT JOIN template_form_section tfs
--     ON tfs.program_template_id = pt.id AND tfs.deleted_at IS NULL
-- LEFT JOIN template_question tq
--     ON tq.template_form_section_id = tfs.id AND tq.deleted_at IS NULL
-- WHERE pt.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
--   AND pt.deleted_at IS NULL
-- GROUP BY pt.id, pt.version;

-- -- Section keys + question counts on the new live tree
-- SELECT tfs.section_key, tfs.display_order, COUNT(tq.id) AS question_count
-- FROM template_form_section tfs
-- INNER JOIN program_template pt ON pt.id = tfs.program_template_id
-- LEFT JOIN template_question tq ON tq.template_form_section_id = tfs.id AND tq.deleted_at IS NULL
-- WHERE pt.program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
--   AND pt.deleted_at IS NULL
--   AND tfs.deleted_at IS NULL
-- GROUP BY tfs.section_key, tfs.display_order
-- ORDER BY tfs.display_order;

-- -- Confirm the old tree is fully soft-deleted (should return 0 rows for all three)
-- SELECT COUNT(*) FROM program_template
-- WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT')
--   AND deleted_at IS NULL
-- HAVING COUNT(*) > 1; -- more than one live row would indicate a problem
