-- =====================================================
-- DETAILED LIST: Records that will be SKIPPED
-- (only basic fields: isRequired, dependsOn, prefill)
-- =====================================================
-- \echo ''
-- \echo '========================================'
-- \echo '  Records that will be SKIPPED'
-- \echo '  (Only basic fields - no flag added)'
-- \echo '========================================'
-- \echo ''
SELECT 
    id,
    binding_key,
    question_text,
    array_agg(DISTINCT key ORDER BY key) as config_keys
FROM master_question,
     LATERAL jsonb_object_keys(config) AS key
WHERE deleted_at IS NULL
AND config != '{}'::jsonb
AND NOT (config ? 'isAdvancedValidation')
AND EXISTS (
    SELECT 1 
    FROM jsonb_object_keys(config) AS k 
    WHERE k IN ('isRequired', 'dependsOn', 'prefill')
)
GROUP BY id, binding_key, question_text
ORDER BY id;



-- =====================================================
-- SCRIPT: Add isAdvancedValidation Flag to Config
-- Adds isAdvancedValidation flag to master_question config
-- Only for questions with advanced validation rules
-- =====================================================

DO $$
DECLARE
    rec RECORD;
    new_config jsonb;
    config_keys text[];
    key text;
    has_advanced_validation boolean;
    updated_count integer := 0;
    skipped_empty integer := 0;
    skipped_exists integer := 0;
    total_records integer := 0;
BEGIN
    RAISE NOTICE '======================================';
    RAISE NOTICE 'ADDING isAdvancedValidation FLAG';
    RAISE NOTICE '======================================';
    
    -- Get total count
    SELECT COUNT(*) INTO total_records
    FROM master_question
    WHERE deleted_at IS NULL;
    
    RAISE NOTICE 'Total records to process: %', total_records;
    RAISE NOTICE '--------------------------------------';
    
    -- =====================================================
    -- Process Each Record
    -- =====================================================
    
    FOR rec IN 
        SELECT id, config, binding_key, question_text
        FROM master_question 
        WHERE deleted_at IS NULL
        ORDER BY id
    LOOP
        -- Reset flag for each iteration
        has_advanced_validation := false;
        
        -- Check if isAdvancedValidation already exists
        IF rec.config ? 'isAdvancedValidation' THEN
            RAISE NOTICE '  ✓ Row % (%): Already has isAdvancedValidation, skipping', 
                rec.id, rec.binding_key;
            skipped_exists := skipped_exists + 1;
            CONTINUE;
        END IF;
        
        -- Handle NULL or empty config
        IF rec.config IS NULL OR rec.config = '{}'::jsonb THEN
            RAISE NOTICE '  − Row % (%): Empty config, skipping', 
                rec.id, rec.binding_key;
            skipped_empty := skipped_empty + 1;
            CONTINUE;
        END IF;
        
        -- Get all keys from config
        config_keys := ARRAY(SELECT jsonb_object_keys(rec.config));
        
        -- If no keys, skip
        IF array_length(config_keys, 1) IS NULL THEN
            RAISE NOTICE '  − Row % (%): No config keys, skipping', 
                rec.id, rec.binding_key;
            skipped_empty := skipped_empty + 1;
            CONTINUE;
        END IF;
        
        -- Check if any key is NOT one of: isRequired, dependsOn, prefill
        FOREACH key IN ARRAY config_keys
        LOOP
            IF key != 'isRequired' AND key != 'dependsOn' AND key != 'prefill' THEN
                has_advanced_validation := true;
                EXIT;
            END IF;
        END LOOP;
        
        -- If only basic fields, skip
        IF NOT has_advanced_validation THEN
            RAISE NOTICE '  − Row % (%): Only basic fields, skipping', 
                rec.id, rec.binding_key;
            skipped_empty := skipped_empty + 1;
            CONTINUE;
        END IF;
        
        -- Add isAdvancedValidation to config
        new_config := jsonb_set(
            rec.config,
            '{isAdvancedValidation}',
            to_jsonb(true)
        );
        
        -- Update the record
        UPDATE master_question
        SET 
            config = new_config,
            updated_at = CURRENT_TIMESTAMP,
            updated_by = -2
        WHERE id = rec.id;
        
        updated_count := updated_count + 1;
        
        RAISE NOTICE '  ✓ Row % (%): Added isAdvancedValidation: true', 
            rec.id, rec.binding_key;
    END LOOP;
    
    -- =====================================================
    -- Final Report
    -- =====================================================
    
    RAISE NOTICE '======================================';
    RAISE NOTICE 'MIGRATION COMPLETE';
    RAISE NOTICE '======================================';
    RAISE NOTICE 'Total Records Processed: %', total_records;
    RAISE NOTICE 'Updated (added flag): %', updated_count;
    RAISE NOTICE 'Skipped (empty/basic only): %', skipped_empty;
    RAISE NOTICE 'Skipped (already exists): %', skipped_exists;
    RAISE NOTICE '--------------------------------------';
    
    IF updated_count = 0 THEN
        RAISE NOTICE 'WARNING: No records updated!';
    ELSE
        RAISE NOTICE 'SUCCESS: % records updated with isAdvancedValidation flag', updated_count;
    END IF;
    
    RAISE NOTICE '======================================';
    
END $$;
