-- =====================================================
-- PT_PAP — paymentMode: remove 'Online (Through Razorpay)' and 'Cash' options
-- 2026-08-08
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
-- Config only (option_config array element removal, both tables). Idempotent (re-running
-- finds nothing left to remove and is a no-op).
-- =====================================================
--
-- Scope: template_question AND hdb_question, both scoped to PT_PAP only.
--   - template_question: STEP 1 below, per explicit instruction ("check it in template").
--   - hdb_question: STEP 2 below, added per explicit follow-up ("in hdb question?"). hdb_question
--     was extended with its own option_config JSONB column by
--     database/program-configuration/00_master-template-schema-migration.sql STEP 8
--     (template_question_id, question_code, option_config, answer_type were all added there),
--     kept in sync with the linked template_question by
--     database/program-configuration/03_update-program-questions-from-template.sql
--     ("option_config = v_template_question.option_config") — NOT via the older
--     hdb_question_option_map -> hdb_option join table, which predates that column and is
--     what earlier/legacy hdb_question rows (like base-script's paymentMode question, id 28)
--     still use. So STEP 2 filters hdb_question.option_config directly, the same way as
--     template_question. Expected to affect 0 rows today — no PT_PAP program (program_v1)
--     exists yet off this template (per 02's own header notes) — but is included so a future
--     PT_PAP program's cloned hdb_question is covered too.
--
-- master_question is NOT touched — 'paymentMode' is a global binding_key shared with
-- PT_HDBMSD/PT_TAT (all cloned from the same source program 1082); removing the option there
-- would remove Razorpay as a payment method for every program type, not just PAP.
--
-- option_config shape (per database/program-configuration/01_master-data-migration.sql's
-- STEP 3, which is how these rows were originally populated from hdb_question_option_map):
-- a JSONB array of {"name", "value", "type", "order"} objects, one per hdb_option row.
-- STEP 0's preview (run against PT_PAP's actual template_question row) confirmed the live
-- set for this question is exactly 'Online (Through Razorpay)', 'Direct bank transfer
-- (NEFT/RTGS)', 'Cheque', 'Cash' (no 'Card swipe', unlike base-script's older seed data) —
-- this migration removes 'Online (Through Razorpay)' and 'Cash' from that set by matching on
-- 'name', leaving 'Direct bank transfer (NEFT/RTGS)' and 'Cheque'.
-- =====================================================

-- =====================================================
-- STEP 0: PREVIEW (read-only) — run these first and review option_config before the UPDATEs
-- =====================================================

-- 0a) PAP templates — current option_config for 'paymentMode'
SELECT tq.id, tq.binding_key, tq.template_form_section_id, tq.option_config
FROM template_question tq
WHERE tq.binding_key = 'paymentMode'
  AND tq.deleted_at IS NULL
  AND tq.template_form_section_id IN (
        SELECT tfs.id
        FROM template_form_section tfs
        JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
        JOIN program_type_v1   pt  ON pt.id   = ptpl.program_type_id
        WHERE pt.key = 'PT_PAP' AND pt.deleted_at IS NULL
          AND ptpl.deleted_at IS NULL AND tfs.deleted_at IS NULL
  );

-- 0b) PAP program questions — current option_config for 'paymentMode' (expected 0 rows today,
--     see header note on why)
SELECT hq.id, hq.binding_key, hq.option_config
FROM hdb_question hq
WHERE hq.binding_key = 'paymentMode'
  AND hq.deleted_at IS NULL
  AND hq.id IN (
        SELECT hpq.question_id
        FROM hdb_program_question hpq
        JOIN program_v1      p  ON p.id  = hpq.program_id
        JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE pt.key = 'PT_PAP' AND pt.deleted_at IS NULL
          AND p.deleted_at IS NULL AND hpq.deleted_at IS NULL
  );


-- =====================================================
-- STEP 1-2: UPDATES — run only after reviewing STEP 0's output
-- =====================================================

-- 1) PAP templates
UPDATE template_question tq
SET option_config = (
        SELECT COALESCE(jsonb_agg(elem ORDER BY (elem->>'order')::int), '[]'::jsonb)
        FROM jsonb_array_elements(tq.option_config) elem
        WHERE elem->>'name' NOT IN ('Online (Through Razorpay)', 'Cash')
    ),
    updated_at = NOW(),
    updated_by = -2
WHERE tq.binding_key = 'paymentMode'
  AND tq.deleted_at IS NULL
  AND (tq.option_config @> '[{"name": "Online (Through Razorpay)"}]'::jsonb
       OR tq.option_config @> '[{"name": "Cash"}]'::jsonb)
  AND tq.template_form_section_id IN (
        SELECT tfs.id
        FROM template_form_section tfs
        JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
        JOIN program_type_v1   pt  ON pt.id   = ptpl.program_type_id
        WHERE pt.key = 'PT_PAP' AND pt.deleted_at IS NULL
          AND ptpl.deleted_at IS NULL AND tfs.deleted_at IS NULL
  );

-- 2) PAP program questions
UPDATE hdb_question hq
SET option_config = (
        SELECT COALESCE(jsonb_agg(elem ORDER BY (elem->>'order')::int), '[]'::jsonb)
        FROM jsonb_array_elements(hq.option_config) elem
        WHERE elem->>'name' NOT IN ('Online (Through Razorpay)', 'Cash')
    ),
    updated_at = NOW(),
    updated_by = -2
WHERE hq.binding_key = 'paymentMode'
  AND hq.deleted_at IS NULL
  AND (hq.option_config @> '[{"name": "Online (Through Razorpay)"}]'::jsonb
       OR hq.option_config @> '[{"name": "Cash"}]'::jsonb)
  AND hq.id IN (
        SELECT hpq.question_id
        FROM hdb_program_question hpq
        JOIN program_v1      p  ON p.id  = hpq.program_id
        JOIN program_type_v1 pt ON pt.id = p.program_type_id
        WHERE pt.key = 'PT_PAP' AND pt.deleted_at IS NULL
          AND p.deleted_at IS NULL AND hpq.deleted_at IS NULL
  );
