-- ================================================
-- PT_PAP Program Template Seed
-- 2026-08-07
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- No schema changes. Seed data only.
-- ================================================
--
-- Creates program_template + template_form_section + template_question rows for PT_PAP.
-- Companion to database/migrations/2026-08-07-01-pt-pap-program-type-workflow.sql, whose
-- header explicitly scoped program_template/template_form_section OUT — this is that
-- follow-up. Requires PT_PAP to already exist in program_type_v1 (run that file first).
--
-- SOURCE MECHANISM — mirrors database/program-configuration/05_tat_template.sql exactly
-- (confirmed by re-reading it in full, per explicit correction; two earlier drafts of this
-- file got this wrong — first cloning from the raw master_question library, then from
-- PT_HDBMSD's already-built program_template rows). The real, established pattern in this
-- repo (used by BOTH database/program-configuration/04_hdb_template.sql for HDB's own
-- template AND 05_tat_template.sql for TAT's) is: clone from an actual, already-configured
-- PROGRAM's legacy question data — hdb_program_question / hdb_question / hdb_form_section
-- for a specific p_source_program_id — using master_form_section / master_question only to
-- resolve canonical metadata (name/description/config) by key/binding_key. This migration
-- follows that same mechanism:
--   p_source_program_id := 1082 — THE SAME source program 04_hdb_template.sql itself used
--     to build PT_HDBMSD's template. Reused here since it's the one program in this repo's
--     history confirmed to have real FS_BASICDETAILS/FS_PAYMENTINVOICE/FS_TRAVELPLAN data
--     (04_hdb_template.sql's own template covers all 5 HDB sections from this program).
--     FLAG: this is a live-DB fact I cannot re-verify from static files — confirm program
--     1082 still exists (not deleted) and still has questions in all three included
--     sections before running this migration; database/program-configuration/04_hdb_template.sql:14
--     is the citation for why 1082 was chosen.
--
-- Sourcing decisions, per explicit instruction:
--   - Sections: PT_HDBMSD's structure, MINUS FS_MAHATRIAQUESTIONS and FS_GOODIES — the same
--     two exclusions already made at the workflow layer in the companion migration (see its
--     header). Only FS_BASICDETAILS, FS_PAYMENTINVOICE, FS_TRAVELPLAN are cloned.
--   - FS_BASICDETAILS fields (per the reference registration-form screenshot, WhatsApp Image
--     2026-08-07 at 9.48.54 PM): ONLY the fields on that form are included — this section
--     uses a WHITELIST (p_basicdetails_included_binding_keys), unlike PAYMENTINVOICE/TRAVELPLAN
--     below, which keep the blacklist approach and clone everything from program 1082 minus
--     the curated exclusions. The whitelist is: name, mobileNumber, email, gender, dob, city,
--     countryName, otherCityName (city's own conditional fallback field — included alongside
--     city/countryName as the "location fields" for this form), rmContact +
--     otherInfinitheismContact (the "infinitheism contact" field and its conditional
--     free-text fallback), profileUrl (Photo), and the pro-forma invoice fields roommate,
--     pro_forma_invoice_name, proFormaBillingAddress, proFormaIsGstRegistered, proFormaZip,
--     proFormaGstNumber — confirmed live (per explicit correction) that in program 1082's
--     own legacy data these actually carry hdb_form_section.key = 'FS_BASICDETAILS', NOT
--     'FS_PAYMENTINVOICE' as their "(pro-forma)"-labeled question text would suggest, so they
--     MUST be in this whitelist (not the FS_PAYMENTINVOICE blacklist below) or STEP 4's
--     section_key-gated branch would silently drop them entirely. "terms" is excluded from
--     this whitelist too — it gets its own template_question via STEP 5 instead, see bullet
--     below. Also explicitly reviewed and EXCLUDED (not accidental omissions):
--     phoneNumberType, phoneNumberRelation, otherRelation — program 1082's own
--     mobileNumber-adjacent sub-questions (is this number yours / whose is it / relation
--     free-text), not present on the reference screenshot, per explicit instruction.
--   - Organisation fields: the screenshot's "Name of your organisation" and "Brief
--     description of your organisation/business and your role" have NO existing
--     master_question — confirmed by grepping binding_key/question_text across every
--     database/*.sql seed and hdb_question migration for 'organi'/'company'/'business'.
--     STEP 4b below creates two brand-new master_question rows (binding_keys
--     'organisationName' and 'organisationDescription') under FS_BASICDETAILS and clones
--     them into the template right after the whitelisted fields. Both now use real
--     answer_location values ('hdb_program_registration.organisation_name' /
--     '...organisation_description') — per explicit instruction, these are dedicated
--     columns added by the companion migration
--     database/migrations/2026-08-07-03-pt-pap-registration-organisation-fields.sql, and
--     ProgramRegistration entity (src/common/entities/program-registration.entity.ts) was
--     updated to match. Run that migration (and confirm the entity change is deployed)
--     BEFORE this one — registration.repository.ts resolves answer_location via live
--     TypeORM EntityMetadata, so a column that exists in the DB without a matching entity
--     property (or vice versa) causes the answer to be silently dropped, not an error.
--   - Payment form: left untouched, cloned in full from HDB (program 1082) via the existing
--     blacklist below, per explicit instruction ("Payment Form can be similar to HDB, with
--     all controls in place").
--   - Terms & Conditions (STEP 5): a 'terms' question IS injected into FS_BASICDETAILS for
--     PAP, per explicit instruction, using PAP-specific copy (p_terms_label /
--     p_terms_markdown_content in the DECLARE block). FLAG: legal review status of this copy
--     has not been confirmed here — verify with Legal/Product before this goes live, same as
--     every other FLAG in this file. This supersedes TWO earlier decisions: (a) an even
--     earlier draft of this migration that injected TAT-style placeholder T&C content
--     (isRequired = false, never legally reviewed) — that placeholder is NOT what's used here;
--     and (b) a later draft that excluded 'terms' entirely pending real copy — PAP-specific
--     copy has since been provided, so STEP 5 now injects it directly, following the same
--     mechanism as 05_tat_template.sql's own STEP 8.
--   - DOB age range 18-100 (STEP 4c): mirrors 05_tat_template.sql's STEP 7 pattern
--     (config.pastValidation.minValue/maxValue), per explicit instruction to follow that same
--     mechanism. minValue is 18 here vs. TAT's 16, consistent with PT_PAP's
--     allows_minors = false (see companion workflow migration).
--   - Excluded questions (blacklist by binding_key), applies to FS_PAYMENTINVOICE and
--     FS_TRAVELPLAN only (FS_BASICDETAILS uses the whitelist above instead): a curated,
--     explicit subset — see the array in the DECLARE block for the current set; do not treat
--     TAT's own 16-item list as a default to fall back to, each key was a deliberate
--     per-field decision for PAP.
--
-- NOT replicated from 05_tat_template.sql (TAT-2026-specific business customizations, not
-- requested for PAP): STEP 5 (inject registrationForWhom at position 0), STEP 6 (explicit
-- FS_BASICDETAILS field reorder), STEP 7 (DOB min/max age config), STEP 9 (TAT-specific
-- label overrides + GSTIN dialogue text), STEP 11 (repoint dependsOn to a specific user,
-- "vijjisubbu"). If PAP needs any equivalent customization, that's a separate, explicit
-- follow-up — flagging so it isn't assumed to already be covered here.
--
-- Could not confirm "HDB template id 14" / "TAT's latest template" from static files (see
-- prior investigation) — irrelevant now that sourcing is program-id-based like TAT's own
-- script, not template-id-based.
--
-- The template row is created with status = PUBLISHED (see STEP 2), matching TAT's own
-- script's behavior, per explicit instruction — this means the template, including its T&C
-- question (STEP 5), is immediately live/usable rather than gated behind a separate publish
-- step.
-- ================================================

DO $$
DECLARE
    -- ============ SOURCE / TARGET ============
    p_source_program_id  BIGINT      := 1082;      -- see FLAG above — same program HDB's own template was built from
    p_program_type_key   VARCHAR(50) := 'PT_PAP';
    p_template_name      VARCHAR(255) := 'PAP Template';
    p_template_description TEXT := 'Registration form template for Profit and Peace programs — cloned from PT_HDBMSD''s source program (minus Mahatria questions and goodies), with FS_BASICDETAILS restricted to the reference registration-form fields plus a PAP-specific Terms & Conditions question.';
    p_template_version   INTEGER := 1;
    p_created_by         INTEGER := -2;

    -- Sections to include (whitelist) — HDB/MSD structure minus FS_MAHATRIAQUESTIONS and
    -- FS_GOODIES, per explicit instruction.
    p_included_sections TEXT[] := ARRAY['FS_BASICDETAILS', 'FS_PAYMENTINVOICE', 'FS_TRAVELPLAN'];

    -- Questions to exclude (blacklist by binding_key) — applies to FS_PAYMENTINVOICE and
    -- FS_TRAVELPLAN only. Explicit, curated set for PAP.
    p_excluded_questions TEXT[] := ARRAY[
        'QK_LAST_HDB', 'no_of_hdbs', 'preference',
        'QK_HDB_ASSOCIATION_SINCE', 'swipeDate', 'tidReferenceNo'
    ];

    -- FS_BASICDETAILS whitelist (by binding_key) — ONLY these are cloned into FS_BASICDETAILS,
    -- per the reference registration-form screenshot. See header note above for the mapping
    -- from each screenshot field to its binding_key.
    p_basicdetails_included_binding_keys TEXT[] := ARRAY[
        'name', 'mobileNumber', 'email', 'gender', 'dob',
        'city', 'countryName', 'otherCityName',
        'rmContact', 'otherInfinitheismContact',
        'profileUrl',
        'roommate', 'pro_forma_invoice_name', 'proFormaBillingAddress',
        'proFormaIsGstRegistered', 'proFormaZip', 'proFormaGstNumber'
    ];

    -- New, PAP-only organisation questions (STEP 4b) — no legacy hdb_question source exists
    -- for either, see header note above.
    p_org_name_binding_key        VARCHAR(100) := 'organisationName';
    p_org_name_question_text      TEXT := 'Name of your organisation';
    p_org_description_binding_key VARCHAR(100) := 'organisationDescription';
    p_org_description_question_text TEXT := 'Brief description of your organisation/business and your role';

    -- PAP Terms & Conditions content (STEP 5) — PAP-specific copy, added per explicit
    -- instruction. FLAG: legal review status not confirmed, see header note above. Supersedes
    -- this file's earlier "excluded entirely" decision
    -- (see the header/STEP 5 comments below, now updated) — mirrors 05_tat_template.sql's
    -- STEP 8 pattern (own label + own markdownContent, referencing the shared, global 'terms'
    -- master_question row rather than cloning HDB's own terms content from program 1082).
    p_terms_label            TEXT := ' I have read and agree to all the above ‘Terms & Conditions’. ';
    p_terms_markdown_content TEXT := E'For the purpose of clarity, the term "**infinitheism**" refers to "**Infinitheism Spiritual Foundation Private Limited**" and the term "**I/me/my/myself/Participant**" refers to the person enrolled in the ''Profit And Peace (PAP) Sep-2026'' residential training program. ("**Program**").\n\n1. By accepting these ''Terms & Conditions'', through the online registration, the Participant is deemed to have voluntarily provided their clear, informed and unambiguous consent to adhere to these terms and conditions of this Program.\n\n2. This residential program, ''Profit and Peace'' (PAP), is on September 28, 29 & 30, 2026 (Monday, Tuesday and Wednesday).\n\n3. Venue: Radisson Blu Resort Temple Bay, 57, Kovalam Road, Mahabalipuram, Tamil Nadu – 603104.\n\n4. Check-in Time: Monday, 28 September 2026, from 1:00 p.m. to 3:00 p.m.\n\n5. Check-out Time: Wednesday, 30 September 2026 11:30 a.m. to 1:00 p.m.\n\n6. Latecomers will not be allowed, immaterial of what the reasons may be. The participant is expected to check-in well before 3:00 p.m.\n\n7. The package includes 2 nights on a double occupancy basis at Radisson Blu Resort Temple Bay, Mahabalipuram, including all meals starting from high tea on 28 September to lunch on 30 September 2026.\n\n8. The participant''s stay will be on a double occupancy basis at the above venue. Room preference or room change request will not be entertained. Room allotted by infinitheism will be final.\n\n9. Any other expenses incurred by the participant apart from what is provided in the package will have to be settled by the participant directly to the resort.\n\n10. It is mandatory to attend all sessions.\n\n11. The medium of instruction will be English.\n\n12. The dress code will be formals / smart casuals; also carry comfortable clothing.\n\n13. Smoking and consumption of alcohol is strictly prohibited within the premises.\n\n14. Usage of mobile phones or any other devices causing disturbances will not be allowed during the sessions.\n\n15. Recording the event in any form (audio or video) and photography (mobile or camera) is strictly prohibited.\n\n16. The participant will not be allowed to leave the hall, during the sessions.\n\n17. 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\n18. 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\n19. 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\n20. Cancellation and refund policy:\n\n    <ol style="list-style-type: lower-alpha; margin-left: -20px; padding-left: 60px;">\n      <li style="margin-bottom: 8px;">Cancellation requests received up to 25 September 2026, will be eligible for a refund of the amount paid.</li>\n      <li style="margin-bottom: 8px;">Cancellation requests received after 25 September 2026 and/or being absent or late to the program due to whatever reasons will not be eligible for any refund.</li>\n      <li style="margin-bottom: 8px;">Refunds will not be paid by cash.</li>\n    </ol>\n\n21. Acceptance of registration and allowance of entry to the program will be at the sole discretion of ''infinitheism''.\n\n22. **Collection and usage of personal information:**\n\n    <ol style="list-style-type: lower-alpha; margin-left: -20px; padding-left: 60px;">\n      <li style="margin-bottom: 8px;">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 style="margin-bottom: 8px;">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 style="margin-bottom: 8px;">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 style="margin-bottom: 8px;">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 6.</li>\n      <li style="margin-bottom: 8px;">For any concerns or grievances related to the handling of the Participant''s personal data, the Participant may write to the email address ''events@infinitheism.com''.</li>\n    </ol>\n\n23. **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 a refund of registration fee due to cancellation of registration or non-attendance or late coming, which is specifically governed by Clause 15.\n\n24. **Governing Law and Jurisdiction:** The provisions of this ''Terms & Conditions'' shall be governed by and construed in accordance with the laws of India, and subject to Clause 22 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\n25. **Dispute Resolution:**\n\n    All disputes, differences and/or claims arising out of these ''Terms & 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\n26. These ''Terms & Conditions'' constitute the entire agreement between the Parties. If any provision is invalid, the remainder shall continue in force.\n\nI hereby agree to receive communication from infinitheism through SMS, WhatsApp, email\nand phone calls or in any other form.\n\n\n\n';

    -- Internal variables (mirrors 05_tat_template.sql's naming)
    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_terms_mq_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_basic_details_master_section_id BIGINT;
    v_max_display_order    INTEGER;

    -- STEP 4b (organisation questions) locals
    v_org_name_mq_id        BIGINT;
    v_org_description_mq_id BIGINT;
    v_next_question_code    TEXT;

    r_program_question RECORD;
    r_section RECORD;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Creating PAP template from program: %', p_source_program_id;
    RAISE NOTICE 'Included sections: %', array_to_string(p_included_sections, ', ');
    RAISE NOTICE 'FS_BASICDETAILS whitelist: %', array_to_string(p_basicdetails_included_binding_keys, ', ');
    RAISE NOTICE 'Excluded questions (FS_PAYMENTINVOICE/FS_TRAVELPLAN): %', 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);

    IF v_total_program_questions = 0 THEN
        RAISE EXCEPTION 'Program % has no questions in specified sections — cannot clone PAP template from it', 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 — run 2026-08-07-01-pt-pap-program-type-workflow.sql first', 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 — ALWAYS inserts a new row, matching TAT's/HDB's own
    -- scripts (neither checks "does a template already exist"; each run just creates
    -- another one). Version auto-increments off whatever's already there for PT_PAP, so
    -- re-running this migration is safe and simply produces a new version rather than
    -- silently reusing (and potentially mixing with) a stale template from an earlier run.
    -- Status = PUBLISHED, matching TAT's own script, per explicit instruction — despite the
    -- STEP 5 T&C copy's legal review status not being confirmed here, see header note above.
    -- =====================================================
    SELECT COALESCE(MAX(version), 0) + 1 INTO p_template_version
    FROM program_template
    WHERE program_type_id = v_program_type_id AND deleted_at IS NULL;

    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 PAP template ID: % (version %)', v_template_id, p_template_version;

    -- =====================================================
    -- STEP 3: Clone ONLY Specified Sections (root, then child)
    -- =====================================================
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Cloning sections (filtered)...';
    RAISE NOTICE '======================================';

    -- Root sections (only from whitelist). Deduped by section_key, NOT by hdb_section_id —
    -- the legacy hdb_form_section table can have MULTIPLE rows sharing the same key (e.g.
    -- two separate FS_TRAVELPLAN rows, confirmed live: program_template 38 originally got
    -- two duplicate FS_TRAVELPLAN template_form_section rows from this exact issue when the
    -- old DISTINCT-on-full-row query treated them as different because hfs.id differed).
    -- One template_form_section is created per section_key; every matching legacy
    -- hdb_form_section.id (there may be several) is mapped to that same new section below,
    -- 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.deleted_at IS NULL
          AND hfs.parent_section_id IS NULL
          AND hfs.key = ANY(p_included_sections)
        ORDER BY hfs.key, 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;

        -- Explicit display order for PAP's own template: BASICDETAILS=1, PAYMENTINVOICE=2,
        -- TRAVELPLAN=3 (sequential — this is a fresh template, not reusing HDB's numbering).
        v_section_display_order := CASE r_section.section_key
            WHEN 'FS_BASICDETAILS'   THEN 1
            WHEN 'FS_PAYMENTINVOICE' THEN 2
            WHEN 'FS_TRAVELPLAN'     THEN 3
            ELSE v_sections_cloned + 4
        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
              AND hfs2.parent_section_id IS NULL
              AND hfs2.deleted_at IS NULL
        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;
        RAISE NOTICE '  Cloned section: % (template_section=%, display_order=%)',
            r_section.section_key, v_template_section_id, v_section_display_order;

        IF r_section.section_key = 'FS_BASICDETAILS' THEN
            v_basic_details_tfs_id := v_template_section_id;
            v_basic_details_master_section_id := r_section.master_section_id;
        END IF;
    END LOOP;

    -- Child sections (only from whitelist) — none of our 3 included sections have children
    -- today, but this pass is kept for structural fidelity with 05_tat_template.sql / future
    -- master_form_section changes; it's a no-op if nothing matches. Deduped by section_key
    -- for the same reason as the root-section loop above (duplicate hdb_form_section rows
    -- sharing a key).
    FOR r_section IN
        SELECT DISTINCT ON (hfs.key)
            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)
        ORDER BY hfs.key, 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;

        FOR v_hdb_section_id IN
            SELECT DISTINCT hfs2.id
            FROM hdb_form_section hfs2
            WHERE hfs2.key = r_section.section_key
              AND hfs2.parent_section_id IS NOT NULL
              AND hfs2.deleted_at IS NULL
        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;
        RAISE NOTICE '  Cloned child section: % (template_section=%)',
            r_section.section_key, 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)
        ORDER BY hpq.display_order, hpq.id
    LOOP
        IF r_program_question.section_key = 'FS_BASICDETAILS' THEN
            -- Whitelist mode: only clone fields on the reference registration-form screenshot.
            -- 'terms' is never in the whitelist, so HDB's own terms question is excluded here
            -- too — PAP gets its own 'terms' template_question from STEP 5 below instead,
            -- with PAP-specific label/content, not a clone of program 1082's.
            v_is_excluded := NOT (r_program_question.binding_key = ANY(p_basicdetails_included_binding_keys));
        ELSE
            -- Blacklist mode (FS_PAYMENTINVOICE, FS_TRAVELPLAN): clone everything from HDB
            -- except the curated exclusions. 'terms' is excluded here too, for the same reason
            -- (STEP 5 injects PAP's own copy instead).
            v_is_excluded := r_program_question.binding_key = ANY(p_excluded_questions)
                             OR r_program_question.binding_key = 'terms';
        END IF;

        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;

        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;

        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;

        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;

    -- Sanity check: the whitelist above only clones a binding_key if it ALSO exists on source
    -- program 1082 — if program 1082 itself never had a question for one of these keys (e.g.
    -- HDB's own basic details may not include a country selector), it silently never entered
    -- the r_program_question loop at all and the ordinary v_questions_not_found counter above
    -- never sees it. Surface that gap explicitly here rather than leaving it silent.
    IF v_basic_details_tfs_id IS NOT NULL THEN
        DECLARE
            v_missing_keys TEXT[];
        BEGIN
            SELECT ARRAY_AGG(wanted)
            INTO v_missing_keys
            FROM UNNEST(p_basicdetails_included_binding_keys) AS wanted
            WHERE NOT EXISTS (
                SELECT 1 FROM template_question
                WHERE template_form_section_id = v_basic_details_tfs_id
                  AND binding_key = wanted
                  AND deleted_at IS NULL
            );

            IF v_missing_keys IS NOT NULL AND array_length(v_missing_keys, 1) > 0 THEN
                RAISE WARNING 'FS_BASICDETAILS whitelist keys not found on source program %, NOT cloned: %',
                    p_source_program_id, array_to_string(v_missing_keys, ', ');
            END IF;
        END;
    END IF;

    -- =====================================================
    -- STEP 4b: Create + clone the two PAP-only organisation questions into FS_BASICDETAILS.
    -- Neither exists in master_question yet (see header note), so — unlike STEP 4 above —
    -- there's no legacy hdb_question/hdb_program_question row to clone from. Each is
    -- inserted into master_question once (idempotent, guarded by binding_key existence,
    -- mirroring 03_5_add_conditional_config_and_registration_for_whom.sql's
    -- registrationForWhom pattern), then cloned into the template like any other question.
    -- answer_location points at the two new hdb_program_registration columns added by
    -- database/migrations/2026-08-07-03-pt-pap-registration-organisation-fields.sql — run
    -- that migration (and confirm ProgramRegistration entity has the matching
    -- organisationName/organisationDescription @Column properties) BEFORE this one, or these
    -- answers will be silently dropped at write time (registration.repository.ts resolves
    -- answer_location via live TypeORM EntityMetadata, not raw SQL).
    -- =====================================================
    IF v_basic_details_tfs_id IS NOT NULL AND v_basic_details_master_section_id IS NOT NULL THEN
        -- organisationName
        SELECT id INTO v_org_name_mq_id
        FROM master_question
        WHERE binding_key = p_org_name_binding_key AND deleted_at IS NULL
        LIMIT 1;

        IF v_org_name_mq_id IS NULL THEN
            SELECT 'Q_' || LPAD((COALESCE(MAX(SUBSTRING(question_code FROM 3)::INTEGER), 0) + 1)::TEXT, 10, '0')
            INTO v_next_question_code
            FROM master_question
            WHERE question_code ~ '^Q_[0-9]+$';

            INSERT INTO master_question (
                question_code, binding_key, master_form_section_id, question_text,
                question_type, answer_type, answer_location, option_config, config,
                conditional_config, is_active, created_at, updated_at, created_by, updated_by
            )
            VALUES (
                v_next_question_code, p_org_name_binding_key, v_basic_details_master_section_id,
                p_org_name_question_text, 'text', 'string', 'hdb_program_registration.organisation_name', NULL,
                '{"isRequired": true, "minCharacter": 2, "maxCharacters": 99}'::jsonb, -- length limits per convention: matches pro_forma_invoice_name's name-field pattern (minCharacter 3/maxCharacters 99 on program 1082) rather than roommate's shorter person-name limits (2/30), since an organisation name can run longer than a person's name.
                NULL, TRUE, NOW(), NOW(), p_created_by, p_created_by
            )
            RETURNING id INTO v_org_name_mq_id;

            RAISE NOTICE 'STEP 4b: created master_question "%" (id %)', p_org_name_binding_key, v_org_name_mq_id;
        END IF;

        -- organisationDescription
        SELECT id INTO v_org_description_mq_id
        FROM master_question
        WHERE binding_key = p_org_description_binding_key AND deleted_at IS NULL
        LIMIT 1;

        IF v_org_description_mq_id IS NULL THEN
            SELECT 'Q_' || LPAD((COALESCE(MAX(SUBSTRING(question_code FROM 3)::INTEGER), 0) + 1)::TEXT, 10, '0')
            INTO v_next_question_code
            FROM master_question
            WHERE question_code ~ '^Q_[0-9]+$';

            INSERT INTO master_question (
                question_code, binding_key, master_form_section_id, question_text,
                question_type, answer_type, answer_location, option_config, config,
                conditional_config, is_active, created_at, updated_at, created_by, updated_by
            )
            VALUES (
                v_next_question_code, p_org_description_binding_key, v_basic_details_master_section_id,
                p_org_description_question_text, 'textarea', 'string', 'hdb_program_registration.organisation_description', NULL,
                '{"isRequired": true, "minCharacter": 10, "maxCharacters": 300}'::jsonb, -- length limits per convention: every other textarea question in this codebase (the FS_MAHATRIAQUESTIONS set, database/date-wise-scripts.sql) uses minCharacter 1/maxCharacters 111, but those are single-topic reflections; this field asks for TWO things (org description AND role), so it's sized larger (300) rather than reusing 111 verbatim — OPEN: confirm exact limit with Product if 300 feels off.
                NULL, TRUE, NOW(), NOW(), p_created_by, p_created_by
            )
            RETURNING id INTO v_org_description_mq_id;

            RAISE NOTICE 'STEP 4b: created master_question "%" (id %)', p_org_description_binding_key, v_org_description_mq_id;
        END IF;

        -- Clone both into the template, right after the whitelisted fields, in screenshot order.
        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;

        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_basic_details_tfs_id
              AND binding_key = p_org_name_binding_key AND deleted_at IS NULL
        ) THEN
            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_org_name_mq_id;

            v_questions_cloned := v_questions_cloned + 1;
            v_max_display_order := v_max_display_order + 1;
        END IF;

        IF NOT EXISTS (
            SELECT 1 FROM template_question
            WHERE template_form_section_id = v_basic_details_tfs_id
              AND binding_key = p_org_description_binding_key AND deleted_at IS NULL
        ) THEN
            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_org_description_mq_id;

            v_questions_cloned := v_questions_cloned + 1;
        END IF;
    ELSE
        RAISE WARNING 'STEP 4b: FS_BASICDETAILS section not found — organisation questions NOT added';
    END IF;

    -- =====================================================
    -- STEP 4c: Set DOB question config in FS_BASICDETAILS — age range 18 to 100 years,
    -- mirroring 05_tat_template.sql's STEP 7 pattern (config.pastValidation.minValue/maxValue),
    -- per explicit instruction. TAT itself uses minValue=16; PAP uses minValue=18 since
    -- allows_minors = false on PT_PAP's program_type_v1 row (see companion workflow
    -- migration), so no under-18 registrant should be able to submit this form at all. Kept
    -- in sync with database/migrations/2026-08-07-04-pt-pap-basicdetails-field-config-update.sql,
    -- which re-applies this same 18-100 range independently.
    -- =====================================================
    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}',
                        '18'::jsonb
                     ),
            updated_at = NOW(),
            updated_by = p_created_by
        WHERE template_form_section_id = v_basic_details_tfs_id
          AND binding_key              = 'dob'
          AND deleted_at              IS NULL;

        RAISE NOTICE 'STEP 4c: DOB question config updated in FS_BASICDETAILS (age 18-100)';
    END IF;

    -- =====================================================
    -- STEP 5: Terms & Conditions — inject at the end of FS_BASICDETAILS, per explicit
    -- instruction, now that PAP-specific T&C copy has been provided. FLAG: legal review status
    -- not confirmed, see header note above. This supersedes the earlier "excluded entirely"
    -- decision (see the superseded note at the
    -- bottom of this file). Mirrors 05_tat_template.sql's STEP 8 exactly: binding_key 'terms'
    -- is still excluded from the ordinary FS_BASICDETAILS whitelist/blacklist cloning in
    -- STEP 4 above (it's absent from p_basicdetails_included_binding_keys, and explicitly
    -- blacklisted for FS_PAYMENTINVOICE/FS_TRAVELPLAN) — this step is the sole source of
    -- PAP's own 'terms' template_question, referencing the shared, global 'terms'
    -- master_question row (binding_key = 'terms', see
    -- database/base-script/portal-base-master-data.sql:323) but with PAP's own label and
    -- markdownContent (p_terms_label / p_terms_markdown_content above), not HDB's or TAT's.
    -- =====================================================
    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,
                p_terms_label,
                '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 5: terms injected at display_order % in FS_BASICDETAILS', v_max_display_order;
        ELSE
            RAISE NOTICE 'STEP 5: terms already present in FS_BASICDETAILS, skipping insert';
        END IF;
    ELSE
        RAISE WARNING 'STEP 5: terms master question or FS_BASICDETAILS section not found — terms NOT added';
    END IF;

    -- =====================================================
    -- Final Report
    -- =====================================================
    RAISE NOTICE '======================================';
    RAISE NOTICE 'PAP TEMPLATE CREATION COMPLETE';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Source Program ID: %', p_source_program_id;
    RAISE NOTICE 'Template ID: %', v_template_id;
    RAISE NOTICE 'Template Status: PUBLISHED (includes PAP-specific Terms & Conditions question via STEP 5)';
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Included Sections: %', array_to_string(p_included_sections, ', ');
    RAISE NOTICE 'FS_BASICDETAILS Whitelist: %', array_to_string(p_basicdetails_included_binding_keys, ', ');
    RAISE NOTICE 'Excluded Questions (FS_PAYMENTINVOICE/FS_TRAVELPLAN): %', array_to_string(p_excluded_questions, ', ');
    RAISE NOTICE 'New Organisation Questions: %, %', p_org_name_binding_key, p_org_description_binding_key;
    RAISE NOTICE '--------------------------------------';
    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;

    IF v_questions_not_found > 0 THEN
        RAISE NOTICE 'WARNING: % questions could not be mapped to master — check logs 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 '======================================';
END $$;

-- ================================================
-- Explicitly out of scope for this migration:
--   - Legal review of the STEP 5 Terms & Conditions copy — added per explicit instruction (see
--     header), but its legal review status has not been confirmed here; verify with
--     Legal/Product before this goes live.
--   - The TAT-2026-specific customization steps listed in the file header (registrationForWhom
--     injection, field reorder, DOB validation, label overrides, dependsOn repoint) — not
--     requested for PAP; would need separate, explicit follow-up if PAP needs equivalents.
--   - Any should_send_terms / attachment wiring on the PAYMENT stage's success-screen
--     copy (see the workflow migration's Stage 2 comments) — unrelated to this file's
--     program_template/template_form_section/template_question scope.
-- ================================================
