-- ================================================
-- PT_ENTRAINMENT program_type_v1 — Restore-if-deleted + full column refresh
-- 2026-08-10
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- No schema changes. Seed/config data only. Idempotent (guarded UPDATE-or-INSERT).
-- ================================================
--
-- WHY: per instruction — check whether PT_ENTRAINMENT's program_type_v1 row (id = 8) is
-- soft-deleted and restore it (deleted_at -> NULL) if so, then set EVERY column against
-- src/common/entities/program-type.entity.ts, borrowing invoice-related column values from
-- PT_PAP / PT_TAT where PT_ENTRAINMENT itself never had them set.
--
-- RESEARCH FINDING: nothing in this repo's tracked SQL history ever sets deleted_at on
-- program_type_v1 for PT_ENTRAINMENT — every write assumes it's live (every UPDATE is scoped
-- "WHERE key = 'PT_ENTRAINMENT' AND deleted_at IS NULL"). So the restore branch below is
-- believed to be a no-op today; it's included anyway, unconditionally, exactly as asked, and
-- because it's the only way to also cover the branch where the row doesn't exist at all.
--
-- VALUE SOURCING — every column is one of:
--   (a) latest known live value, per the most recent tracked write, in order:
--       database/entrainment-program-create.sql (git 1c28cbb8) ->
--       database/program-configuration/11_add_pt_missing_columns_and_data.sql ->
--       database/program-configuration/15_sync_program_type_master_data.sql (+ its
--       114abe8c banner_url bump) -- latest wins per column.
--   (b) a gap PT_ENTRAINMENT never had filled, now borrowed from PT_TAT/PT_PAP per instruction
--       (flagged inline with BORROWED FROM comments below): services_accounting_code,
--       invoice_code, invoice_sender_address.
--   (c) helpline_number / venue_name_in_emails: these were set to real values on 2026-04-24
--       (database/date-wise-scripts.sql) then nulled back out by the 2026-05-15
--       15_sync_program_type_master_data.sql full-column write. Restored here to the 04-24
--       values (RESTORED comments below) — flag if that null-out was actually intentional.
--
-- OPEN FLAGS — please confirm before running:
--   1. invoice_code = 'ENT26' is a NEW value (PT_ENTRAINMENT never had one) — chosen to follow
--      TAT's 'TAT09' / PAP's 'PAPSEP' season-code convention (program-code + season/year,
--      already widened to VARCHAR(6) by database/migrations/2026-08-09-01-pt-pap-invoice-code-update.sql).
--      This is a placeholder guess, not a confirmed business value — please replace with the
--      real invoice code before running.
--   2. services_accounting_code = '999293' — borrowed verbatim from what
--      2026-08-07-01-pt-pap-program-type-workflow.sql:170 calls "PT_TAT's actual value" (no
--      tracked SQL sets it for TAT directly, so this is one hop removed from source).
--   3. invoice_sender_address — PT_ENTRAINMENT's gst_number ('36AACCI7694H1Z5') matches TAT's
--      ORIGINAL Secunderabad-registered GSTIN, so this migration pairs it with TAT's original
--      Secunderabad address (same state, same logic PAP's own migration used pairing ITS
--      Chennai gst_number with TAT's Chennai address) — NOT PAP's Chennai address, which is
--      paired with a *different* GSTIN (33AACCI7694H1ZB). Confirm this state-pairing is still
--      correct for Entrainment.
--   4. should_send_terms / is_proforma_allowed set to true — no tracked SQL ever sets these for
--      PT_ENTRAINMENT, but database/migrations/2026-08-07-01-pt-pap-program-type-workflow.sql:146-148
--      states PT_PAP defaulted these to "ENTRAINMENT's pattern", implying Entrainment's actual
--      live value is true (known outside this repo's tracked SQL). Confirm.
--   5. max_session_duration_days = 1 / default_duration = '1 day' are reproduced verbatim from
--      11_add_pt_missing_columns_and_data.sql even though the EN26 workflow files describe a
--      multi-day residential event — this looks like a pre-existing stale value, not something
--      this migration invented. Flagging, not silently changing.
--   6. is_special_program = true — NOT an entity column (see entity-drift note in
--      02workflow-refresh.sql's sibling research), but exists on the
--      live program_type_v1 table (database/base-script/portal-base-schema-script.sql:1011).
--      Matches PT_ENTRAINMENT's own original 1c28cbb8 value and both TAT/PAP's.
-- ================================================

DO $$
DECLARE
    v_id INT;
BEGIN
    SELECT id INTO v_id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT';

    IF v_id IS NOT NULL THEN

        UPDATE program_type_v1 SET
            name                                = 'Entrainment',
            description                         = 'A residential spiritual retreat with Mahatria',
            mode_of_operation                   = 'offline',
            online_type                         = 'NA',
            max_session_duration_days           = 1,
            has_multiple_sessions                = false,
            frequency                           = 'yearly',
            default_start_time                  = '07:00:00',
            default_end_time                    = '21:00:00',
            default_duration                    = '1 day',
            requires_residence                  = true,
            involves_travel                     = true,
            has_goodies                         = false,
            has_checkin_checkout                = true,
            requires_payment                     = true,
            requires_attendance_all_sessions    = true,
            allows_minors                       = true,
            should_send_terms                   = true,   -- see OPEN FLAG 4
            is_proforma_allowed                 = true,    -- see OPEN FLAG 4
            parental_consent_enabled            = false,
            parental_consent_form_as_attachment = false,
            parental_consent_upload_deadline    = NULL,
            parental_consent_form_content       = NULL,
            allows_proxy_registration           = true,
            max_proxy_registrations             = 5,
            requires_approval                   = false,
            registration_level                  = 'program',
            is_active                           = true,
            no_of_session                       = 0,
            waitlist_applicable                 = true,
            max_capacity                        = 0,
            meta                                = NULL,
            venue                               = 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
            limited_seats                       = true,
            is_grouped_program                  = false,
            logo_url                            = 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
            banner_url                          = 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/program-type/entrainment/entrainment.jpg',
            igst                                = 18.00,
            cgst                                = 9.00,
            sgst                                = 9.00,
            gst_percentage                      = 18.00,
            tds_percent                         = 10.00,
            gst_number                          = '36AACCI7694H1Z5',
            services_accounting_code            = '999293',                -- BORROWED FROM PT_TAT (see OPEN FLAG 2)
            invoice_code                        = 'ENT26',                  -- BORROWED/DERIVED FROM PT_TAT/PT_PAP convention (see OPEN FLAG 1 — placeholder)
            tds_applicability                   = 'base_only',
            invoice_sender_name                 = 'infinitheism spiritual foundation private limited',
            invoice_sender_pan                  = 'AACCI7694H',
            invoice_sender_cin                  = 'U74900TN2011PTC083263',
            invoice_sender_address              = E'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally,\nSecunderabad - 500011, Telangana, India',  -- BORROWED FROM PT_TAT (see OPEN FLAG 3)
            helpline_number                     = '+919841660000',        -- RESTORED (see value-sourcing note c)
            email_sender_name                   = 'Entrainment Team',
            email_sender_address                = 'events@infinitheism.com',
            email_bcc_address                   = 'events@infinitheism.com',
            email_bcc_name                      = 'Entrainment Team',
            venue_name_in_emails                 = 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',  -- RESTORED (see value-sourcing note c)
            seeker_can_share_experience         = false,
            sub_program_type                    = 'PST_ENTRAINMENT',
            allow_save_as_draft                 = false,
            currency                            = 'INR',
            is_special_program                  = true,    -- non-entity column, see OPEN FLAG 6
            deleted_at                          = NULL,     -- explicit restore, per instruction
            updated_at                          = NOW(),
            updated_by                          = -2
        WHERE id = v_id;

        RAISE NOTICE 'PT_ENTRAINMENT program_type_v1 row (id=%) restored/refreshed', v_id;

    ELSE

        INSERT INTO program_type_v1 (
            name, description, mode_of_operation, online_type,
            max_session_duration_days, has_multiple_sessions, frequency,
            default_start_time, default_end_time, default_duration,
            requires_residence, involves_travel, has_goodies, has_checkin_checkout,
            requires_payment, requires_attendance_all_sessions, allows_minors,
            should_send_terms, is_proforma_allowed,
            parental_consent_enabled, parental_consent_form_as_attachment,
            parental_consent_upload_deadline, parental_consent_form_content,
            allows_proxy_registration, max_proxy_registrations,
            requires_approval, registration_level, is_active,
            no_of_session, waitlist_applicable, max_capacity, meta,
            venue, limited_seats, is_grouped_program,
            logo_url, banner_url, key,
            igst, cgst, sgst, gst_percentage, tds_percent,
            gst_number, services_accounting_code, invoice_code, tds_applicability,
            invoice_sender_name, invoice_sender_pan, invoice_sender_cin, invoice_sender_address,
            helpline_number, email_sender_name, email_sender_address,
            email_bcc_address, email_bcc_name, venue_name_in_emails,
            seeker_can_share_experience, sub_program_type, allow_save_as_draft, currency,
            is_special_program,
            created_at, updated_at, created_by, updated_by, deleted_at
        ) VALUES (
            'Entrainment', 'A residential spiritual retreat with Mahatria', 'offline', 'NA',
            1, false, 'yearly',
            '07:00:00', '21:00:00', '1 day',
            true, true, false, true,
            true, true, true,
            true, true,
            false, false,
            NULL, NULL,
            true, 5,
            false, 'program', true,
            0, true, 0, NULL,
            'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', true, false,
            'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
            'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/program-type/entrainment/entrainment.jpg',
            'PT_ENTRAINMENT',
            18.00, 9.00, 9.00, 18.00, 10.00,
            '36AACCI7694H1Z5', '999293', 'ENT26', 'base_only',
            'infinitheism spiritual foundation private limited', 'AACCI7694H', 'U74900TN2011PTC083263',
            E'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally,\nSecunderabad - 500011, Telangana, India',
            '+919841660000', 'Entrainment Team', 'events@infinitheism.com',
            'events@infinitheism.com', 'Entrainment Team',
            'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
            false, 'PST_ENTRAINMENT', false, 'INR',
            true,
            NOW(), NOW(), -2, -2, NULL
        );

        RAISE NOTICE 'PT_ENTRAINMENT program_type_v1 row inserted fresh (no prior row existed)';

    END IF;
END $$;

-- =====================================================
-- VERIFICATION QUERY (commented out — uncomment to run manually after migration)
-- =====================================================
-- SELECT id, key, name, deleted_at, invoice_code, services_accounting_code,
--        invoice_sender_address, helpline_number, venue_name_in_emails,
--        should_send_terms, is_proforma_allowed
-- FROM program_type_v1
-- WHERE key = 'PT_ENTRAINMENT';
