-- =====================================================
-- SCRIPT: Strip hardcoded questionId from master_question configs
-- Run after: 03_7_migrate_hdb_question_config_to_unified_depends_on_format.sql
-- Run before: 04_* template scripts
--
-- What it does:
--   Removes the `questionId` field from every entry inside:
--     1) conditional_config.conditions[]  (all master_question rows)
--     2) config.dependsOn[]              (all master_question rows)
--     3) config.prefill[]                (all master_question rows)
--
--   questionBindingKey is the only runtime reference; hardcoded questionId
--   values cause cross-environment mismatches and must not exist in master data.
-- =====================================================

BEGIN;

DO $$
DECLARE
    v_conditional_config_updated INTEGER := 0;
    v_depends_on_updated         INTEGER := 0;
    v_prefill_updated            INTEGER := 0;
BEGIN

-- =====================================================
-- STEP 1: Strip questionId from config.dependsOn
-- =====================================================

WITH updated AS (
    UPDATE master_question
    SET
        config = jsonb_set(
            config,
            '{dependsOn}',
            (
                SELECT jsonb_agg(item - 'questionId')
                FROM jsonb_array_elements(config->'dependsOn') AS item
            )
        ),
        updated_at = NOW(),
        updated_by = -2
    WHERE config IS NOT NULL
      AND config ? 'dependsOn'
      AND jsonb_typeof(config->'dependsOn') = 'array'
      AND deleted_at IS NULL
      AND EXISTS (
          SELECT 1
          FROM jsonb_array_elements(config->'dependsOn') AS item
          WHERE item ? 'questionId'
      )
    RETURNING id
)
SELECT COUNT(*) INTO v_depends_on_updated FROM updated;

-- =====================================================
-- STEP 3: Strip questionId from config.prefill
-- =====================================================

WITH updated AS (
    UPDATE master_question
    SET
        config = jsonb_set(
            config,
            '{prefill}',
            (
                SELECT jsonb_agg(item - 'questionId')
                FROM jsonb_array_elements(config->'prefill') AS item
            )
        ),
        updated_at = NOW(),
        updated_by = -2
    WHERE config IS NOT NULL
      AND config ? 'prefill'
      AND jsonb_typeof(config->'prefill') = 'array'
      AND deleted_at IS NULL
      AND EXISTS (
          SELECT 1
          FROM jsonb_array_elements(config->'prefill') AS item
          WHERE item ? 'questionId'
      )
    RETURNING id
)
SELECT COUNT(*) INTO v_prefill_updated FROM updated;

RAISE NOTICE '======================================';
RAISE NOTICE 'master_question questionId strip complete';
RAISE NOTICE 'conditional_config rows updated : %', v_conditional_config_updated;
RAISE NOTICE 'config.dependsOn rows updated   : %', v_depends_on_updated;
RAISE NOTICE 'config.prefill rows updated     : %', v_prefill_updated;
RAISE NOTICE '======================================';

END $$;

COMMIT;

-- =====================================================
-- VERIFICATION
-- =====================================================

SELECT 'conditional_config with questionId remaining' AS check_name, COUNT(*) AS count
FROM master_question
WHERE conditional_config ? 'conditions'
  AND deleted_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(conditional_config->'conditions') AS cond
      WHERE cond ? 'questionId'
  )

UNION ALL

SELECT 'config.dependsOn with questionId remaining', COUNT(*)
FROM master_question
WHERE config ? 'dependsOn'
  AND deleted_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(config->'dependsOn') AS item
      WHERE item ? 'questionId'
  )

UNION ALL

SELECT 'config.prefill with questionId remaining', COUNT(*)
FROM master_question
WHERE config ? 'prefill'
  AND deleted_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(config->'prefill') AS item
      WHERE item ? 'questionId'
  );
