-- =====================================================
-- SCRIPT: Normalize dependsOn entries in master_question
-- Run after: 03_5_add_conditional_config_and_registration_for_whom.sql
-- Run before: 04_* template scripts
--
-- What it does:
--   1) Ensures every dependsOn value field is an array (wraps scalars)
--   2) Merges multiple dependsOn entries for the same question
--      (same questionId + questionBindingKey) into a single entry
--      with a combined values array.
--   e.g. {value:"yes"} + {value:"no"} → {value:["yes","no"]}
--   e.g. {value:"Card swipe"} → {value:["Card swipe"]}
-- =====================================================

BEGIN;

DO $$
DECLARE
    v_normalized INTEGER := 0;
    v_merged     INTEGER := 0;
BEGIN

-- =====================================================
-- STEP 1: Wrap any scalar value fields in dependsOn to arrays
-- =====================================================

WITH updated AS (
    UPDATE master_question
    SET
        config = jsonb_set(
            config,
            '{dependsOn}',
            (
                SELECT jsonb_agg(
                    CASE
                        WHEN item ? 'value' AND jsonb_typeof(item->'value') <> 'array'
                            THEN item - 'value' || jsonb_build_object('value', jsonb_build_array(item->'value'))
                        ELSE item
                    END
                )
                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 ? 'value'
            AND jsonb_typeof(item->'value') <> 'array'
      )
    RETURNING id
)
SELECT COUNT(*) INTO v_normalized FROM updated;

-- =====================================================
-- STEP 2: Merge duplicate entries for same question into one
-- =====================================================

WITH entries AS (
    SELECT
        mq.id,
        elem                        AS dep_entry,
        elem->>'questionId'         AS question_id,
        elem->>'questionBindingKey' AS question_binding_key
    FROM master_question mq,
    jsonb_array_elements(mq.config->'dependsOn') AS elem
    WHERE mq.config IS NOT NULL
      AND mq.config ? 'dependsOn'
      AND jsonb_typeof(mq.config->'dependsOn') = 'array'
      AND mq.deleted_at IS NULL
),
values_expanded AS (
    SELECT
        e.id,
        e.question_id,
        e.question_binding_key,
        e.dep_entry,
        v AS val
    FROM entries e,
    jsonb_array_elements(
        CASE
            WHEN e.dep_entry ? 'value' AND jsonb_typeof(e.dep_entry->'value') = 'array'
                THEN e.dep_entry->'value'
            WHEN e.dep_entry ? 'value'
                THEN jsonb_build_array(e.dep_entry->'value')
            ELSE '[]'::jsonb
        END
    ) AS v
),
grouped AS (
    SELECT
        id,
        question_id,
        question_binding_key,
        (array_agg(dep_entry ORDER BY dep_entry::text))[1] AS first_entry,
        jsonb_agg(DISTINCT val)                             AS all_values
    FROM values_expanded
    GROUP BY id, question_id, question_binding_key
),
rebuilt AS (
    SELECT
        id,
        jsonb_agg(first_entry - 'value' || jsonb_build_object('value', all_values)) AS new_depends_on
    FROM grouped
    GROUP BY id
),
has_duplicates AS (
    SELECT DISTINCT mq.id
    FROM master_question mq
    WHERE mq.config IS NOT NULL
      AND mq.config ? 'dependsOn'
      AND jsonb_typeof(mq.config->'dependsOn') = 'array'
      AND mq.deleted_at IS NULL
      AND (
          SELECT COUNT(*) > COUNT(DISTINCT (elem->>'questionId', elem->>'questionBindingKey'))
          FROM jsonb_array_elements(mq.config->'dependsOn') AS elem
      )
),
updated AS (
    UPDATE master_question mq
    SET
        config     = jsonb_set(config, '{dependsOn}', rebuilt.new_depends_on),
        updated_at = NOW(),
        updated_by = -2
    FROM rebuilt
    JOIN has_duplicates hd ON hd.id = rebuilt.id
    WHERE mq.id = rebuilt.id
    RETURNING mq.id
)
SELECT COUNT(*) INTO v_merged FROM updated;

RAISE NOTICE '======================================';
RAISE NOTICE 'master_question dependsOn normalization complete';
RAISE NOTICE 'Rows with scalar values wrapped : %', v_normalized;
RAISE NOTICE 'Rows with duplicate entries merged : %', v_merged;
RAISE NOTICE '======================================';

END $$;

COMMIT;

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

SELECT 'scalar values remaining' AS check_name, COUNT(*) AS count
FROM master_question mq
WHERE config ? 'dependsOn'
  AND deleted_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(mq.config->'dependsOn') AS item
      WHERE item ? 'value'
        AND jsonb_typeof(item->'value') <> 'array'
  )

UNION ALL

SELECT 'duplicate question entries remaining', COUNT(*)
FROM master_question mq
WHERE config ? 'dependsOn'
  AND deleted_at IS NULL
  AND (
      SELECT COUNT(*) > COUNT(DISTINCT (elem->>'questionId', elem->>'questionBindingKey'))
      FROM jsonb_array_elements(mq.config->'dependsOn') AS elem
  );
