-- ============================================================
-- PT_PAP — Repoint 'otherInfinitheismContact' / 'otherRM' dependsOn value
-- 2026-08-09
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
--
-- Dynamic version: resolves target rows by binding_key + PT_PAP program_type
-- scoping (mirrors 2026-08-08-04-pt-pap-label-overrides-update.sql), NOT by
-- hardcoded question ids. Repoints config.dependsOn[*].value to the id of
-- the user with phone_number '8939910100', replacing whatever id/array it
-- currently holds (handles both array and scalar value shapes — mirrors
-- database/rm-value-template-question.sql).
--
-- Applies to BOTH:
--   1) template_question — so future PT_PAP programs clone the right value
--   2) hdb_question      — existing PT_PAP program instances already cloned
--
-- CAUTION — hdb_question rows can be referenced by more than one program via
-- hdb_program_question. The hq.id IN (...) scoping below only requires the
-- question to be linked to *a* PT_PAP program; if the same hdb_question row
-- is also linked to a non-PT_PAP program, that program is affected too. Run
-- the verification SELECTs and review the program_id list before COMMIT.
-- ============================================================

-- Verification: confirm exactly one user matches the phone number
SELECT id, phone_number FROM users WHERE phone_number ILIKE '8939910100';

-- Verification: current template config (PT_PAP template)
SELECT tq.id, tq.binding_key, tq.config->'dependsOn' AS depends_on
FROM template_question tq
JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
JOIN program_type_v1 ptype ON ptype.id = ptpl.program_type_id
WHERE ptype.key = 'PT_PAP' AND ptype.deleted_at IS NULL
  AND ptpl.deleted_at IS NULL AND tfs.deleted_at IS NULL
  AND tq.binding_key IN ('otherRM', 'otherInfinitheismContact')
  AND tq.deleted_at IS NULL;

-- Verification: current program-question config + which programs are affected
SELECT hq.id, hq.binding_key, hq.config->'dependsOn' AS depends_on, hpq.program_id
FROM hdb_question hq
JOIN hdb_program_question hpq ON hpq.question_id = hq.id
JOIN program_v1 p ON p.id = hpq.program_id
JOIN program_type_v1 ptype ON ptype.id = p.program_type_id
WHERE ptype.key = 'PT_PAP' AND ptype.deleted_at IS NULL
  AND p.deleted_at IS NULL AND hpq.deleted_at IS NULL
  AND hq.binding_key IN ('otherRM', 'otherInfinitheismContact')
  AND hq.deleted_at IS NULL;

-- 1) PT_PAP templates
WITH target_user AS (
  SELECT id FROM users WHERE phone_number ILIKE '8939910100'
)
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[(SELECT id FROM target_user)]))
                   ELSE jsonb_set(elem, '{value}', to_jsonb((SELECT id FROM target_user)))
                 END
               )
        FROM jsonb_array_elements(tq.config->'dependsOn') AS elem
      )
    ),
    updated_at = NOW(),
    updated_by = -2
FROM template_form_section tfs
JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
JOIN program_type_v1 ptype ON ptype.id = ptpl.program_type_id
WHERE tq.template_form_section_id = tfs.id
  AND ptype.key = 'PT_PAP' AND ptype.deleted_at IS NULL
  AND ptpl.deleted_at IS NULL AND tfs.deleted_at IS NULL
  AND tq.binding_key IN ('otherRM', 'otherInfinitheismContact')
  AND tq.config ? 'dependsOn'
  AND tq.deleted_at IS NULL
  AND EXISTS (SELECT 1 FROM target_user)
  AND (SELECT COUNT(*) FROM users WHERE phone_number ILIKE '8939910100') = 1;

-- 2) Existing PT_PAP program questions
WITH target_user AS (
  SELECT id FROM users WHERE phone_number ILIKE '8939910100'
)
UPDATE hdb_question hq
SET config = jsonb_set(
      hq.config,
      '{dependsOn}',
      (
        SELECT jsonb_agg(
                 CASE
                   WHEN jsonb_typeof(elem->'value') = 'array'
                     THEN jsonb_set(elem, '{value}', to_jsonb(ARRAY[(SELECT id FROM target_user)]))
                   ELSE jsonb_set(elem, '{value}', to_jsonb((SELECT id FROM target_user)))
                 END
               )
        FROM jsonb_array_elements(hq.config->'dependsOn') AS elem
      )
    ),
    updated_at = NOW(),
    updated_by = -2
WHERE hq.binding_key IN ('otherRM', 'otherInfinitheismContact')
  AND hq.config ? 'dependsOn'
  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 ptype ON ptype.id = p.program_type_id
        WHERE ptype.key = 'PT_PAP' AND ptype.deleted_at IS NULL
          AND p.deleted_at IS NULL AND hpq.deleted_at IS NULL
  )
  AND EXISTS (SELECT 1 FROM target_user)
  AND (SELECT COUNT(*) FROM users WHERE phone_number ILIKE '8939910100') = 1;

-- Verification: template config after update
SELECT tq.id, tq.binding_key, tq.config->'dependsOn' AS depends_on
FROM template_question tq
JOIN template_form_section tfs ON tfs.id = tq.template_form_section_id
JOIN program_template ptpl ON ptpl.id = tfs.program_template_id
JOIN program_type_v1 ptype ON ptype.id = ptpl.program_type_id
WHERE ptype.key = 'PT_PAP'
  AND tq.binding_key IN ('otherRM', 'otherInfinitheismContact');

-- Verification: program-question config after update
SELECT hq.id, hq.binding_key, hq.config->'dependsOn' AS depends_on, hpq.program_id
FROM hdb_question hq
JOIN hdb_program_question hpq ON hpq.question_id = hq.id
JOIN program_v1 p ON p.id = hpq.program_id
JOIN program_type_v1 ptype ON ptype.id = p.program_type_id
WHERE ptype.key = 'PT_PAP'
  AND hq.binding_key IN ('otherRM', 'otherInfinitheismContact');

-- ----------------------------------------------------------------------------
-- Review the verification output above, then COMMIT to keep changes,
-- or ROLLBACK to discard.
-- ----------------------------------------------------------------------------
COMMIT;
-- ROLLBACK;
