-- =====================================================
-- SCRIPT: Create TAT Template from Existing Program
-- Clones ONLY FS_PAYMENTINVOICE and FS_BASICDETAILS sections
-- Excludes: QK_LAST_HDB, no_of_hdbs, preference, roommate,
--           phoneNumberType, phoneNumberRelation, otherRelation,
--           QK_HDB_ASSOCIATION_SINCE, swipeDate, tidReferenceNo,
--           pro_forma_invoice_name, proFormaBillingAddress, proFormaIsGstRegistered,
--           proFormaGstNumber, proFormaZip, existingProformaInvoice
-- =====================================================

DO $$
DECLARE
    -- ============ SET THESE VARIABLES ============
    p_source_program_id BIGINT := 1082;                    -- SOURCE PROGRAM ID
    p_program_type_key VARCHAR(50) := 'PT_TAT';            -- TAT PROGRAM TYPE KEY
    p_template_name VARCHAR(255) := 'TAT 25/26 Template'; -- NEW TEMPLATE NAME
    p_template_description TEXT := 'This and That Template - Payment & Basic Details Only'; -- DESCRIPTION
    p_template_version INTEGER := 1;                        -- TEMPLATE VERSION
    p_created_by INTEGER := -2;                             -- YOUR USER ID

    -- T&C body markdown stored in config.markdownContent.
    -- Static content — update dates/program name here before running this script.
    -- Supports: ### headers, **bold**, 1. numbered lists, [text](url) links, ![alt](url) images.
    p_terms_markdown_content TEXT := E'I am enrolling myself in the Program conducted by infinitheism. The Program shall have (12 live online sessions of 2 hours each) as provided in the ''Program Schedule'' hereunder.\n\nI understand, acknowledge and agree to the following terms and conditions:\n\n1. The medium of instruction for the Program shall be English.\n\n2. By participating in the Program and accepting these terms and conditions, the Participant is deemed to have voluntarily provided their clear, informed and unambiguous consent to participate in the Program.\n\n3. Participation in the Program is subject to prior registration and full payment of registration fees. Infinitheism reserves the right to deny entry to and registration for the Program to any person, without assigning any reason therefor.\n\n4. Eligibility: Only those individuals who have not previously experienced Higher Deeper Beyond (HDB) / My Sacred Days (MSD) programs conducted by infinitheism and those who have completed sixteen (16) years of age are eligible for registration in the Program.\n\n5. A ''Parental Consent Declaration'' is compulsory for all Participants who are less than eighteen (18) years of age. The ''Parental Consent Declaration'' form is available both on the registration portal accessible through infinitheism''s website and may be sent to the registered email address mentioned above. The Parental Consent Declaration will have to be signed by a parent/guardian of the Participant, and the signed scanned copy of the form should be submitted to Infinitheism either by uploading the same on the registration portal or through email to <a href="mailto:events@infinitheism.com" style="color: blue;">events@infinitheism.com</a> from the registered email address of the Participant on or before 31st July, 2026.\n\n6. The ''Program Schedule'' is as follows:  \n   **Dates:** August 3, 4, 5, 10, 11, 12, 17, 18, 19, 24, 25 & 26, 2026 (Mondays, Tuesdays and Wednesdays)  \n   **Session Time: 7:00 p.m. to 9:00 p.m. IST (2 hours) on each of the above dates.**\n\n7. No reminders will be sent on the ''Program Schedule'' to me and it is my duty to keep track of the dates and time of the Program.\n\n8. Late entry will not be allowed into the sessions, immaterial of whatever the reason may be. As no two watches show the same time, Participants are expected to login to the online sessions of the Program by 6:45 p.m. IST.\n\n9. Me being late or absent to any session, immaterial of what the reasons may be, including force majeure reasons which include but is not limited to acts of god, acts of nature, acts of war, act of governmental body or military authority, functions in the family, illness, network disruption, any other technological snags, fire, insurrection, explosion, flood, storm, pandemic, epidemic, terrorist action, sabotage, accidents, civil unrest or disturbances, embargos, riots, strikes, or any other similar event which is beyond reasonable control will not be allowed under any circumstances.\n\n10. I understand and agree that my eligibility to graduate as an ''Infinitheist'' is subject to my attendance for all the sessions of the Program on time as provided herein.\n\n11. By being absent or late to any one or more of the sessions of the Program, I understand and agree that I will not be allowed to attend that particular session(s) and also the last session i.e., the 12th session of the Program on 26th August, 2026 and thus will not be eligible to graduate as an ''Infinitheist''. Thereby, I will also not be eligible to attend any subsequent programs conducted by infinitheism exclusively for the ''Infinitheist'' fraternity. However, I can attend the other sessions of this program.\n\n12. Participants shall behave respectfully and comply with all instructions of infinitheism during the Program. Any kind of disturbances, misconduct or distractions will not be allowed during the sessions. Infinitheism reserves the right to remove any Participant from the session for misconduct, causing disturbances or distractions, even if the Participant has a valid registration. The Participant shall not be entitled to any refund in such a case.\n\n13. Transfer of registration fee from one Participant to another, if requested before the commencement of the Program, shall be at the sole discretion of infinitheism. All registrations and the fees paid are non-transferable unless otherwise permitted by infinitheism under this Clause.\n\n14. Cancellation of registration for the Program and refund of the registration fee is governed as below;\n\n    <ol style="list-style-type: lower-alpha;">\n      <li>Participants who want to cancel their registrations for whatever reason, are requested to send an email from their registered email address to ''<a href="mailto:events@infinitheism.com" style="color: blue;">events@infinitheism.com</a>'', clearly mentioning their request for cancellation along with their name, mobile number and registration number.</li>\n      <li>Cancellation requests received from the Participants up until 31st July, 2026 will be eligible for a refund of the amount paid by the Participant. Cancellation requests received after the above timeline or a request for refund citing non-attendance due to late entry or whatever reason are not entitled or eligible for a refund of the amount paid, under any circumstances.</li>\n      <li>Refunds will not be paid in cash irrespective of the mode of payment by the Participant at the time of registration.</li>\n    </ol>\n\n15. Recording, capturing, reproducing or storing the Program in any form (including audio, video, screen recording, screenshots or photograph) or through any device or means (including through mobile phones, laptops, tablets, computers, camera, software applications, technology or any other similar device or means) is strictly prohibited. However, infinitheism reserves its right to record the Program for its internal/business purposes. Participants consent to the use of their image and likeness for promotional purposes by accepting the terms contained herein.\n\n16. infinitheism is not responsible for any disruption in experiencing the Program due to any technical glitch, snag or other disturbances from the Participant''s side.\n\n17. All contents of the Program remain the sole and exclusive intellectual property of infinitheism at all times and shall not be reproduced, assigned, licensed, recompiled, or reverse engineered without infinitheism''s written consent.\n\n18. **Collection and usage of personal information:**\n\n    <ol style="list-style-type: lower-alpha;">\n      <li>I consent to the collection, processing, storage, and use of my personal data by infinitheism, in accordance with the applicable laws of India which is inclusive of the Digital Personal Data Protection Act, 2023 and the Information Technology Act, 2000 as amended from time to time and the rules therein.</li>\n      <li>The personal data provided by the Participant in connection with the Program to infinitheism will be solely used to send details and announcements related to the Program to the Participant. In addition, the Participant consents to receive information regarding any future programs conducted by infinitheism. For the avoidance of doubt, the consent obtained herein in relation to future programs is limited to providing information regarding the existence and happening of such future programs, and a separate consent shall be obtained from the Participant before sending any further details, updates or announcements relating to such future programs. Further, the Participant retains the right to withdraw their consent at any time in relation to receiving information regarding future programs conducted by Infinitheism. Any personal data that the Participant provides to infinitheism will be handled only for lawful and legitimate purpose in accordance with applicable laws of India. infinitheism is committed to protecting the Participant''s personal data, and the Participant''s right to privacy. infinitheism''s ''Privacy Policy'' and ''Terms &amp; Conditions'' govern the collection, storing, and processing of the information provided by the Participant.</li>\n      <li>I acknowledge that I have read infinitheism''s ''Privacy Policy'' carefully and it has helped me make informed decisions about sharing my personal information with infinitheism.</li>\n      <li>If the Participant is below 18 years of age, the parent/guardian of such Participant shall have reviewed, understood, and consented to these terms &amp; conditions on behalf of the Participant, for the collection and usage of personal data/information of the said Participant as described herein, through the Parental Consent Declaration form mentioned under Clause.</li>\n      <li>The Participant retains their right to withdraw their consent at any time, without prejudice to the lawfulness of processing carried out prior to such withdrawal, and/or access, correct, and erase the Participant''s personal data.</li>\n      <li>For any concerns or grievances related to the handling of the Participant''s personal data, the Participant may write to the email address ''<a href="mailto:events@infinitheism.com" style="color: blue;">events@infinitheism.com</a>''.</li>\n    </ol>\n\n19. The Participant agrees to indemnify and hold harmless infinitheism and its representatives from any claims arising from breach of these terms and conditions or misuse of the Program content.\n\n20. **Limitation of Liability:** The liability of Infinitheism is limited to an aggregate maximum of registration fee for any and all accepted claims. However, this Clause is not applicable for claiming refund of registration fee due to cancellation of registration or non-attendance or late coming, which is specifically governed by Clause 15.\n\n21. **Governing Law and Jurisdiction:** The provisions of this ''Terms and Conditions'' shall be governed by and construed in accordance with the laws of India, and subject to Clause 24 below, the Parties shall submit to the exclusive jurisdiction of the courts at Chennai, India, in relation to any disputes arising under this document.\n\n22. **Dispute Resolution:**\n\n    All disputes, differences and/or claims arising out of these terms and conditions whether during its subsistence or thereafter shall be settled through negotiation between the authorized representatives of the Parties. On failure of resolution through negotiation within 30 (thirty) days of initiation of negotiations, the Parties shall resolve the same through arbitration in accordance with the provisions of the Arbitration and Conciliation Act, 1996 and any statutory amendments thereto. The arbitration shall be conducted by an independent sole arbitrator appointed by mutual consent of the Parties. The arbitral award rendered by the arbitrator shall be final and binding upon the Parties and may be enforced through any court of competent jurisdiction. The seat and venue of arbitration shall be at Chennai, India. The arbitration proceedings shall be conducted in the English language.\n\n23. These terms and conditions constitute the entire agreement between the    Parties. If any provision is invalid, the remainder shall continue in force.\n\n    I hereby agree to receive communication from Infinitheism through SMS, WhatsApp, email and phone calls or in any other form.\n\n    Please review Infinitheism''s <a href="https://www.infinitheism.com/privacy-policy/" target="_blank" rel="noopener noreferrer" style="color: blue;">Privacy Policy</a> before proceeding.\n\n\n\n\n\n\n\n\n\n';
    
    -- Sections to include (whitelist)
    p_included_sections TEXT[] := ARRAY['FS_PAYMENTINVOICE', 'FS_BASICDETAILS'];
    
    -- Questions to exclude (blacklist by binding_key)
    p_excluded_questions TEXT[] := ARRAY[
        'QK_LAST_HDB', 'no_of_hdbs', 'preference', 'roommate',
        'phoneNumberType', 'phoneNumberRelation', 'otherRelation',
        'QK_HDB_ASSOCIATION_SINCE', 'swipeDate', 'tidReferenceNo',
        'pro_forma_invoice_name', 'proFormaBillingAddress', 'proFormaIsGstRegistered',
        'proFormaGstNumber', 'proFormaZip', 'existingProformaInvoice'
    ];
    
    -- Internal variables
    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_section_id BIGINT;
    v_master_question_id BIGINT;
    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_basic_details_tfs_id        BIGINT;
    v_payment_invoice_tfs_id      BIGINT;
    v_registration_for_whom_mq_id BIGINT;
    v_terms_mq_id                 BIGINT;
    v_max_display_order           INTEGER;

    -- dependsOn repoint (STEP 11): resolve vijjisubbu by phone_number.
    p_viji_phone_number VARCHAR(50) := '8939910100';  -- vijjisubbu identifier
    v_viji_user_id      BIGINT;
    v_viji_count        INTEGER;
    v_dependson_updated INTEGER := 0;

    -- Record for iteration
    r_program_question RECORD;
    r_section RECORD;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Creating TAT template from program: %', p_source_program_id;
    RAISE NOTICE 'Included sections: %', array_to_string(p_included_sections, ', ');
    RAISE NOTICE 'Excluded questions: %', array_to_string(p_excluded_questions, ', ');
    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);  -- Only count questions in included sections
    
    IF v_total_program_questions = 0 THEN
        RAISE EXCEPTION 'Program % has no questions in specified sections', p_source_program_id;
    END IF;
    
    RAISE NOTICE 'Found % questions in included sections', v_total_program_questions;
    
    -- =====================================================
    -- STEP 1: Lookup Program Type
    -- =====================================================
    
    SELECT id INTO v_program_type_id
    FROM program_type_v1
    WHERE key = p_program_type_key
      AND deleted_at IS NULL;
    
    IF v_program_type_id IS NULL THEN
        RAISE EXCEPTION 'Program type with key "%" not found', p_program_type_key;
    END IF;
    
    RAISE NOTICE 'Found program type: % (ID: %)', p_program_type_key, v_program_type_id;
    
    -- =====================================================
    -- STEP 2: Create Program Template
    -- =====================================================
    
    INSERT INTO program_template (
        program_type_id,
        name,
        description,
        version,
        status,
        is_active,
        created_at,
        updated_at,
        created_by,
        updated_by
    )
    VALUES (
        v_program_type_id,
        p_template_name,
        p_template_description,
        p_template_version,
        'PUBLISHED'::template_status,
        TRUE,
        NOW(),
        NOW(),
        p_created_by,
        p_created_by
    )
    RETURNING id INTO v_template_id;
    
    RAISE NOTICE 'Created TAT template ID: %', v_template_id;
    
    -- =====================================================
    -- STEP 3: Clone ONLY Specified Sections
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning sections (filtered)...';
    RAISE NOTICE '======================================';
    
    -- Clone root sections (only from whitelist)
    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
          AND hfs.key = ANY(p_included_sections)  -- ✅ Only included sections
        ORDER BY hfs.id
    LOOP
        -- Check if master section exists
        IF r_section.master_section_id IS NULL THEN
            RAISE WARNING 'Section key "%" not found in master_form_section, skipping', r_section.section_key;
            CONTINUE;
        END IF;
        
        -- Explicit display order: BASICDETAILS=1, PAYMENTINVOICE=3
        v_section_display_order := CASE r_section.section_key
            WHEN 'FS_BASICDETAILS'   THEN 1
            WHEN 'FS_PAYMENTINVOICE' THEN 3
            ELSE v_sections_cloned + 6
        END;

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

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

        v_sections_cloned := v_sections_cloned + 1;
        RAISE NOTICE '  ✓ Cloned section: % (hdb_section=%, template_section=%, display_order=%)',
            r_section.section_key, r_section.hdb_section_id, v_template_section_id, v_section_display_order;
    END LOOP;
    
    -- Clone child sections (only from whitelist)
    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
          AND hfs.key = ANY(p_included_sections)  -- ✅ Only included sections
        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_template_section_id := (v_section_map->>r_section.hdb_parent_section_id::text)::BIGINT;
        
        IF v_template_section_id IS NULL THEN
            RAISE WARNING 'Parent section for "%" not found in mapping, skipping', r_section.section_key;
            CONTINUE;
        END IF;
        
        INSERT 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 '  ✓ Cloned child section: % (hdb_section=%, template_section=%)', 
            r_section.section_key, r_section.hdb_section_id, v_template_section_id;
    END LOOP;
    
    -- =====================================================
    -- STEP 4: Clone Questions (Exclude specific binding_keys)
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning questions (with exclusions)...';
    RAISE NOTICE '======================================';
    
    FOR r_program_question IN
        SELECT 
            hpq.id as program_question_id,
            hpq.question_id as hdb_question_id,
            hpq.display_order,
            hq.binding_key,
            hq.form_section_id as hdb_section_id,
            hq.label as question_text,
            hq.typ as question_type,
            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)  -- ✅ Only included sections
        ORDER BY hpq.display_order, hpq.id
    LOOP
        -- ✅ Check if question should be excluded
        v_is_excluded := r_program_question.binding_key = ANY(p_excluded_questions);
        
        IF v_is_excluded THEN
            RAISE NOTICE '  ⊘ EXCLUDED: binding_key=% (section=%)', 
                r_program_question.binding_key, r_program_question.section_key;
            v_questions_excluded := v_questions_excluded + 1;
            CONTINUE;
        END IF;
        
        -- Find master question by binding_key
        SELECT id INTO v_master_question_id
        FROM master_question
        WHERE binding_key = r_program_question.binding_key
          AND deleted_at IS NULL
          AND is_active = TRUE
        LIMIT 1;
        
        IF v_master_question_id IS NULL THEN
            RAISE WARNING '  ✗ Question not found in master: binding_key=%, hdb_question_id=%', 
                r_program_question.binding_key, r_program_question.hdb_question_id;
            v_questions_not_found := v_questions_not_found + 1;
            CONTINUE;
        END IF;
        
        -- Get template section ID from mapping
        v_template_section_id := (v_section_map->>r_program_question.hdb_section_id::text)::BIGINT;
        
        IF v_template_section_id IS NULL THEN
            RAISE WARNING '  ✗ Section not mapped for question: binding_key=%, section_id=%', 
                r_program_question.binding_key, r_program_question.hdb_section_id;
            v_questions_not_found := v_questions_not_found + 1;
            CONTINUE;
        END IF;
        
        -- Insert template question
        INSERT INTO template_question (
            template_form_section_id,
            master_question_id,
            question_code,
            binding_key,
            question_text,
            question_type,
            answer_type,
            answer_location,
            option_config,
            config,
            conditional_config,
            display_order,
            created_at,
            created_by,
            updated_at,
            updated_by
        )
        SELECT 
            v_template_section_id,
            mq.id,
            mq.question_code,
            mq.binding_key,
            mq.question_text,
            mq.question_type,
            mq.answer_type,
            mq.answer_location,
            mq.option_config,
            mq.config,
            COALESCE(r_program_question.hdb_conditional_config, mq.conditional_config),
            r_program_question.display_order,
            NOW(),
            p_created_by,
            NOW(),
            p_created_by
        FROM master_question mq
        WHERE mq.id = v_master_question_id;
        
        v_questions_cloned := v_questions_cloned + 1;
        
        IF v_questions_cloned % 10 = 0 THEN
            RAISE NOTICE '  Progress: % questions cloned...', v_questions_cloned;
        END IF;
    END LOOP;
    
    -- =====================================================
    -- STEP 5: Inject registrationForWhom at position 0
    --         in FS_BASICDETAILS (shift existing questions down)
    -- =====================================================

    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;

    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;

    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: Reorder FS_BASICDETAILS questions
    -- =====================================================

    IF v_basic_details_tfs_id IS NOT NULL THEN
        UPDATE template_question SET display_order = ord
        FROM (VALUES
            ('registrationForWhom',      0),
            ('name',                     1),
            ('gender',                   2),
            ('countryName',              3),
            ('city',                     4),
            ('otherCityName',            5),
            ('mobileNumber',             6),
            ('dob',                      7),
            ('rmContact',                8),
            ('otherInfinitheismContact', 9),
            ('email',                    10),
            ('profileUrl',              11)
        ) AS desired(bk, ord)
        WHERE template_form_section_id = v_basic_details_tfs_id
          AND binding_key              = desired.bk
          AND deleted_at              IS NULL;

        RAISE NOTICE 'STEP 6: FS_BASICDETAILS display_order updated';
    END IF;

    -- =====================================================
    -- STEP 7: Set DOB question config in FS_BASICDETAILS
    -- =====================================================

    IF v_basic_details_tfs_id IS NOT NULL THEN
        UPDATE template_question
        SET config = jsonb_set(
                        jsonb_set(
                            config,
                            '{pastValidation,maxValue}',
                            '100'::jsonb
                        ),
                        '{pastValidation,minValue}',
                        '16'::jsonb
                     )
        WHERE template_form_section_id = v_basic_details_tfs_id
          AND binding_key              = 'dob'
          AND deleted_at              IS NULL;

        RAISE NOTICE 'STEP 7: DOB question config updated in FS_BASICDETAILS';
    END IF;

    -- =====================================================
    -- STEP 8: Inject terms at the end of FS_BASICDETAILS
    -- =====================================================
    -- question_text (label) = consent checkbox text shown to the participant
    -- config.markdownContent = full T&C body rendered in the confirmation-email PDF
    -- =====================================================


    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,
                'I have read and agree to all the above terms and conditions.',
                'content',
                mq.answer_type,
                mq.answer_location,
                NULL,
                jsonb_build_object(
                    'type',            'content',
                    'contentFormat',   'markdown',
                    'isRequired',      true,
                    'markdownContent', p_terms_markdown_content
                ),
                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 insert';
        END IF;
    ELSE
        RAISE WARNING 'STEP 8: terms master question or FS_BASICDETAILS section not found';
    END IF;

    -- =====================================================
    -- STEP 9: Apply TAT 2026 label & option overrides
    --         (per-template; does not touch master_question)
    -- =====================================================
    -- Source: "TAT Program Variables - 2026" registration-form spec.
    -- terms (T&C body) intentionally NOT changed here — revised content pending.

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

    -- FS_BASICDETAILS label overrides
    IF v_basic_details_tfs_id IS NOT NULL THEN
        UPDATE template_question SET question_text = lbl
        FROM (VALUES
            ('otherCityName',            'City name'),
            ('rmContact',                'Contact person at infinitheism'),
            ('otherInfinitheismContact', 'Other contact person'),
            ('profileUrl',               'Upload photo')
        ) AS overrides(bk, lbl)
        WHERE template_form_section_id = v_basic_details_tfs_id
          AND binding_key              = overrides.bk
          AND deleted_at              IS NULL;

        -- registrationForWhom option labels (keep values self/other — dependsOn relies on them)
        UPDATE template_question
        SET option_config = '[
                {"name": "Register for myself", "type": "string", "order": 0, "value": "self"},
                {"name": "Register for others", "type": "string", "order": 1, "value": "other"}
            ]'::jsonb
        WHERE template_form_section_id = v_basic_details_tfs_id
          AND binding_key              = 'registrationForWhom'
          AND deleted_at              IS NULL;

        RAISE NOTICE 'STEP 9: FS_BASICDETAILS labels & registrationForWhom options updated';
    END IF;

    -- FS_PAYMENTINVOICE label overrides
    IF v_payment_invoice_tfs_id IS NOT NULL THEN
        UPDATE template_question SET question_text = lbl
        FROM (VALUES
            ('invoiceName',         'Name to be mentioned on the invoice'),
            ('invoiceEmail',        'Email address to which invoice to be sent'),
            ('isGstRegistered',     'Are you registered under India GST?'),
            ('invoiceAddress',      'Address to be mentioned on the invoice'),
            ('gstNumber',           'GSTIN to be mentioned on the invoice'),
            ('paymentMode',         'Choose your payment method*'),
            ('invoiceConfirmation', 'I have reviewed and confirm that my invoice information is correct and acknowledge that no changes will be permitted after submission.')
        ) AS overrides(bk, lbl)
        WHERE template_form_section_id = v_payment_invoice_tfs_id
          AND binding_key              = overrides.bk
          AND deleted_at              IS NULL;

        -- isGstRegistered: update ONLY the dialogue text, leave rest of config as-is
        UPDATE template_question
        SET config = jsonb_set(
                        config,
                        '{showDialog,dialogueContent}',
                        to_jsonb('Important! If you do not provide your GSTIN, this transaction will be filed under ‘unregistered’ category in our GST return’. This cannot be changed after submitting this form.'::text)
                     )
        WHERE template_form_section_id = v_payment_invoice_tfs_id
          AND binding_key              = 'isGstRegistered'
          AND deleted_at              IS NULL
          AND config ? 'showDialog';

        RAISE NOTICE 'STEP 9: FS_PAYMENTINVOICE labels & GSTIN dialogue text updated';
    END IF;

    -- =====================================================
    -- STEP 11: Repoint dependsOn value -> vijjisubbu's id for
    --          template_questions whose OWN binding_key is
    --          'otherRM' or 'otherInfinitheismContact'.
    --          Scoped to THIS newly created template (v_template_id) only.
    --          Each dependsOn element's value is rewritten, preserving its
    --          value shape: scalar stays scalar, array stays [id].
    -- =====================================================

    -- Resolve vijjisubbu's id; fail loudly if not found / ambiguous.
    SELECT COUNT(*) INTO v_viji_count
    FROM users
    WHERE phone_number = p_viji_phone_number;

    IF v_viji_count = 0 THEN
        RAISE EXCEPTION 'STEP 11: vijjisubbu not found for phone_number = %', p_viji_phone_number;
    ELSIF v_viji_count > 1 THEN
        RAISE EXCEPTION 'STEP 11: phone_number % matched % users - refusing to proceed', p_viji_phone_number, v_viji_count;
    END IF;

    SELECT id INTO v_viji_user_id
    FROM users
    WHERE phone_number = p_viji_phone_number;

    UPDATE template_question tq
    SET config = jsonb_set(
          tq.config,
          '{dependsOn}',
          (
            SELECT jsonb_agg(
                     CASE
                       WHEN jsonb_typeof(elem->'value') = 'array'
                         THEN jsonb_set(elem, '{value}', to_jsonb(ARRAY[v_viji_user_id]))
                       ELSE jsonb_set(elem, '{value}', to_jsonb(v_viji_user_id))
                     END
                   )
            FROM jsonb_array_elements(tq.config->'dependsOn') AS elem
          )
        )
    FROM template_form_section tfs
    WHERE tq.template_form_section_id = tfs.id
      AND tfs.program_template_id = v_template_id
      AND tq.binding_key IN ('otherRM', 'otherInfinitheismContact')
      AND tq.config ? 'dependsOn'
      AND tq.deleted_at IS NULL;

    GET DIAGNOSTICS v_dependson_updated = ROW_COUNT;

    RAISE NOTICE 'STEP 11: dependsOn repointed to vijjisubbu (id=%) for % question(s)',
        v_viji_user_id, v_dependson_updated;

    -- =====================================================
    -- STEP 10: Final Report
    -- =====================================================

    RAISE NOTICE '======================================';
    RAISE NOTICE 'TAT TEMPLATE CREATION COMPLETE';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Source Program ID: %', p_source_program_id;
    RAISE NOTICE 'New Template ID: %', v_template_id;
    RAISE NOTICE 'Template Name: %', p_template_name;
    RAISE NOTICE 'Program Type: TAT (This And That)';
    RAISE NOTICE 'Template Status: PUBLISHED';
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'FILTERING SUMMARY:';
    RAISE NOTICE 'Included Sections: %', array_to_string(p_included_sections, ', ');
    RAISE NOTICE 'Excluded Questions: %', array_to_string(p_excluded_questions, ', ');
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'RESULTS:';
    RAISE NOTICE 'Total Questions in Source Sections: %', v_total_program_questions;
    RAISE NOTICE 'Sections Cloned: %', v_sections_cloned;
    RAISE NOTICE 'Questions Cloned: %', v_questions_cloned;
    RAISE NOTICE 'Questions Excluded (by filter): %', v_questions_excluded;
    RAISE NOTICE 'Questions Not Found in Master: %', v_questions_not_found;
    RAISE NOTICE '--------------------------------------';
    
    IF v_questions_excluded > 0 THEN
        RAISE NOTICE 'SUCCESS: % questions excluded as requested', v_questions_excluded;
    END IF;
    
    IF v_questions_not_found > 0 THEN
        RAISE NOTICE 'WARNING: % questions could not be mapped to master', v_questions_not_found;
        RAISE NOTICE 'Check logs above for details';
    END IF;
    
    IF v_questions_cloned = 0 THEN
        RAISE WARNING 'NO QUESTIONS CLONED! Template may be incomplete.';
    END IF;
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'TEMPLATE DETAILS:';
    RAISE NOTICE 'Template ID: %', v_template_id;
    RAISE NOTICE 'Program Type ID: %', v_program_type_id;
    RAISE NOTICE 'Created By: %', p_created_by;
    RAISE NOTICE 'Version: %', p_template_version;
    RAISE NOTICE '======================================';
    
END $$;