-- Categories

	-- get one category



	CREATE PROCEDURE getCategory(
		IN category_id INT,
		OUT fetch_row, 
	)
	BEGIN
		-- category
		SELECT *
			FROM category as _ -- (underscore) _ means that data will be kept in main array
		WHERE category_id = :category_id LIMIT 1;

		--description
		SELECT *, language_id as _ -- (underscore) _ column means that this column (language_id) value will be used as array key when adding row to result array
			FROM category_description 
		WHERE category_id = :category_id;	 

		--images
		SELECT *, category_image_id  as _
			FROM category_image as images
		WHERE category_id = :category_id;	 

		 --SELECT *,category_option_id as _ 
			--FROM category_option  WHERE category_id = :category_id;
			--@EACH(category_option, category_option_value) 
				--SELECT *, category_option_value_id as _ FROM category_option_value pov 
					--WHERE category_option_id = :category_option[category_option_id];

	END
	



	-- Edit category

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

		DELETE FROM category_description WHERE category_id = :category_id;
		
		
		@EACH(:category_array.category_description) 
			INSERT INTO category_description 
		
				( @KEYS(:each), category_id, meta_title, meta_description, meta_keyword )
			
			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_array, category);
		
		UPDATE category 
			
			SET @LIST(:category_array) 
			
		WHERE category_id = :category_id
	END	



-- Add new category

	CREATE PROCEDURE addCategory(
		IN category_data ARRAY,
		OUT fetch_one
	)
	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 SET model = 'test 2', sku = '', upc = '', ean = '', jan = '', isbn = '', mpn = '', location = '', quantity = '1', minimum = '1', subtract = '1', stock_status_id = '6', date_available = '2017-05-14', manufacturer_id = '0', shipping = '1', price = '0', points = '0', weight = '0', weight_class_id = '1', length = '0', width = '0', height = '0', length_class_id = '1', status = '1', tax_class_id = '0', sort_order = '1', date_added = NOW()
		
		INSERT INTO category 
		
			( @KEYS(:category) )
			
		VALUES ( :category );
			

		-- SET :category_description.category_id = last_insert_id;
        SET @category_id = LAST_INSERT_ID();

		-- UPDATE category SET image = :image WHERE category_id = :category_id;
		
		-- :category  = @FILTER(:category_data, category);
		
		INSERT INTO category_description 
		
			( `category_id`, @KEYS(:category_description) )
			
		VALUES ( @category_id, @LIST(:category_description) );
	 
        SELECT @category_id as category_id;
	END


	-- get all categories 

	CREATE PROCEDURE getCategories(

		-- variables
		IN  language_id INT(11),
		IN  customer_group_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 * FROM category AS categories
		
			LEFT JOIN category_to_store p2s ON (categories.category_id = p2s.category_id) 
			LEFT JOIN category_description pd ON (categories.category_id = pd.category_id)  

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

			@IF isset(:search)
			THEN 
			
				AND pd.name LIKE :search
				
			END @IF			

			
		LIMIT :start, :count;
		
		SELECT count(*) FROM (
			
			@SQL_COUNT(categories.category_id, category) -- this takes previous query removes limit and replaces select columns with parameter product_id
			
		) as count;


	END


