CREATE OR REPLACE PROCEDURE get_configurations_values_v1(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    current_category_id TEXT;
    user_rec RECORD;
    config_map JSONB := '{}'::JSONB;
    categories JSONB;
    input_user_id INT;
    latest_updated TIMESTAMP := NULL;
    temp_updated TIMESTAMP;
BEGIN
    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;
 
    -- Extract categories and user_id from the request JSONB
    categories := (request->>'categories')::JSONB;
 
    -- Safely extract user_id, handle null case
    IF request ? 'user_id' AND request->>'user_id' != '' AND request->>'user_id' != 'null' THEN
        BEGIN
            input_user_id := (request->>'user_id')::INT;
        EXCEPTION 
            WHEN others THEN
                input_user_id := NULL;
        END;
    ELSE
        input_user_id := NULL;
    END IF;
 
    -- Check if categories exists
    IF categories IS NULL THEN
        result := '{"error": "categories parameter is required"}'::JSONB;
        RETURN;
    END IF;
 
    -- Loop through each value in the categories object
    FOR current_category_id IN 
        SELECT jsonb_each_text.value 
        FROM jsonb_each_text(categories)
    LOOP
        -- 1. Fetch all default configs for this category
        FOR rec IN 
            SELECT ac.config_key, ac.config_value, ac.updated_at
            FROM app_configurations ac
            WHERE ac.category_id = current_category_id 
            ORDER BY ac.order_no ASC
        LOOP
            config_map := jsonb_set(
                config_map,
                ARRAY[rec.config_key],
                COALESCE(to_jsonb(rec.config_value), 'null'::JSONB),
   
 
                TRUE
            );
            -- Track latest updated_at
            IF rec.updated_at IS NOT NULL THEN
                IF latest_updated IS NULL OR rec.updated_at > latest_updated THEN
                    latest_updated := rec.updated_at;
                END IF;
            END IF;
        END LOOP;
 
        -- 2. If user_id is provided, fetch user configs and override/add
        IF input_user_id IS NOT NULL THEN
            FOR user_rec IN 
                SELECT auc.config_key, auc.config_value, auc.updated_at
                FROM app_user_configurations auc
                WHERE auc.category_id = current_category_id
                  AND auc.user_id = input_user_id
            LOOP
                config_map := jsonb_set(
                    config_map,
                    ARRAY[user_rec.config_key],
                    COALESCE(to_jsonb(user_rec.config_value), 'null'::JSONB),
    
 
 
                    TRUE
                );
                -- Track latest updated_at
                IF user_rec.updated_at IS NOT NULL THEN
                    IF latest_updated IS NULL OR user_rec.updated_at > latest_updated THEN
                        latest_updated := user_rec.updated_at;
                    END IF;
                END IF;
            END LOOP;
        END IF;
    END LOOP;
 
    -- Final result
    result := jsonb_set(result, '{data}', config_map, TRUE);
    IF latest_updated IS NOT NULL THEN
        result := jsonb_set(result, '{latest_updated_at}', to_jsonb(latest_updated::TEXT), TRUE);
    END IF;
END;
$$;