-- Categories

	-- get all categories 

	CREATE PROCEDURE getCategories(

		-- variables
		IN  language_id INT(11),
		IN  store_id INT(11),
		IN  search CHAR,
		
		-- pagination
		IN  start INT(11),
		IN  count INT(11),
		
		-- return array of categories for categories query
		OUT fetch_all,
		-- return categories count for count query
		OUT fetch_one,
	)
	BEGIN

		SELECT  SQL_CALC_FOUND_ROWS *

		FROM category AS categories
		
			LEFT JOIN category_to_store c2s ON (categories.category_id = c2s.category_id) 
			LEFT JOIN category_description pd ON (categories.category_id = pd.category_id)  

			WHERE 
			
			pd.language_id = :language_id AND c2s.store_id = :store_id

			@IF isset(:search)
			THEN 
			
				AND pd.name LIKE :search
				
			END @IF				
			
		LIMIT :start, :count;
		
		SELECT FOUND_ROWS() as count;

	END

	-- get one category

	CREATE PROCEDURE getCategory(
		IN  language_id INT(11),
		IN  store_id INT(11),

		IN category_id INT,
		IN slug CHAR,
		
		OUT fetch_row
	)
	BEGIN
	
	
		SELECT  SQL_CALC_FOUND_ROWS *

		FROM category AS _
		
			LEFT JOIN category_to_store c2s ON (_.category_id = c2s.category_id) 
			LEFT JOIN category_description pd ON (_.category_id = pd.category_id)  

			WHERE 
			
			pd.language_id = :language_id AND c2s.store_id = :store_id
			

            @IF isset(:slug)
			THEN 
				AND pd.slug = :slug 
        	END @IF			

            @IF isset(:category_id)
			THEN 
                AND _.category_id = :category_id
        	END @IF		
        
		LIMIT 1
	
	END
	



	-- Edit category

	CREATE PROCEDURE editCategory(
		IN category_data ARRAY,
		IN category_id INT(11),
		OUT insert_id
	)
	BEGIN

		DELETE FROM category_description WHERE category_id = :category_id;
		
		@EACH(:category_data.category_description) 
			INSERT INTO category_description 
		
				( @KEYS(:each), category_id )
			
			VALUES ( :each, :category_id);


		--SELECT * FROM category_option WHERE category_id = :category_id;

		-- allow only table fields and set defaults for missing values
		@FILTER(:category_data, category);
		
		UPDATE category 
			
			SET @LIST(:category_data) 
			
		WHERE category_id = :category_id
	END	



    -- Add new category

    CREATE PROCEDURE addCategory(
		IN  category_data ARRAY,
        IN  store_id INT(11),
        OUT insert_id,
        OUT insert_id,
        OUT @result.category
	)
    
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		:category  = @FILTER(:category_data, category)
		:category_description = @FILTER(:category_data, category_description)
		
		INSERT INTO category 
		
			( @KEYS(:category) )
			
		VALUES ( :category );
        
		INSERT INTO category_to_store 
		
			( `category_id`, `store_id`)
			
		VALUES ( @result.category, :store_id );

		INSERT INTO category_description 
		
			( `category_id`, @KEYS(:category_description) )
			
		VALUES ( @result.category, :category_description );
        
	END
    
    