-- Posts

	-- get all post 

	CREATE PROCEDURE getPosts(
		IN start INT,
		IN limit INT,
		IN type CHAR,
		IN search CHAR,
		IN post_id ARRAY,
		
		-- return array of posts for posts query
		OUT fetch_all,
		-- return posts count for count query
		OUT fetch_one,
	)
	BEGIN

		SELECT *
		FROM post AS posts
			LEFT JOIN post_description pd ON (posts.post_id = pd.post_id AND pd.language_id = 1)  
			LEFT JOIN admin u ON (posts.user_id = u.admin_id)  
		WHERE 1 
		
			@IF isset(:type)
			THEN
				AND posts.type = :type
			END @IF


            -- search
            @IF isset(:search)
			THEN 
				AND pd.name LIKE CONCAT('%',:search,'%')
        	END @IF	     
            
            -- post_id
			@IF isset(:post_id) && count(:post_id) > 0
			THEN 
			
				AND posts.post_id IN (:post_id)
				
			END @IF		
			
			-- limit
			@IF isset(:limit)
			THEN
				LIMIT :start, :limit
			END @IF;		

		--SELECT FOUND_ROWS() as count;
		SELECT count(*) FROM (
			
			@SQL_COUNT(posts.post_id, post) -- this takes previous query removes limit and replaces select columns with parameter product_id
			
		) as count;
	 
	END
	

	-- get one post

	CREATE PROCEDURE getPost(
		IN post_id INT(11),
		OUT fetch_row,
	)
	BEGIN

		SELECT * 
			FROM post AS _
		WHERE post_id = :post_id;
		
		--description
		
		SELECT *, language_id as array_key -- (underscore) _ column means that this column (language_id) value will be used as array key when adding row to result array
			FROM post_description 
		WHERE post_id = :post_id;	 
	 
	END

	-- Add new post

	CREATE PROCEDURE addPost(
		IN post ARRAY,
		OUT insert_id
	)
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		:post_data  = @FILTER(:post, post);
		
		
		INSERT INTO post 
			
			( @KEYS(:post_data) )
			
	  	VALUES ( :post_data );

		:post_description  = @FILTER(:post_description, post_description, false, true);


		@EACH(:post.post_description) 
			INSERT INTO post_description 
		
				( @KEYS(:each), post_id)
			
			VALUES ( :each, @result.post);

	END

	-- Edit post

	CREATE PROCEDURE editPost(
		IN post_array ARRAY,
		IN  post_id INT(11),
		OUT insert_id
	)
	BEGIN

		DELETE FROM post_description WHERE post_id = :post_id;
		
		@EACH(:post_array.post_description) 
			INSERT INTO post_description 
		
				( @KEYS(:each), post_id, excerpt)
			
			VALUES ( :each, :post_id, '');

		-- allow only table fields and set defaults for missing values
		@FILTER(:post_array, post)
		
		
		@IF !empty(:post_array) 
		THEN
			UPDATE post 
				
				SET @LIST(:post_array) 
				
			WHERE post_id = :post_id
		END @IF


	END
	
	
	-- Delete post

	CREATE PROCEDURE deletePost(
		IN  id_post INT(11),
		OUT affected_rows
	)
	BEGIN

		DELETE FROM post WHERE post_id = :id_post
	 
	END
	
	
	
	-- Get tags

	CREATE PROCEDURE postTags(
		IN  id_post INT(11),
		OUT affected_rows
	)
	BEGIN
	END
	
	
	-- Get categories

	CREATE PROCEDURE postCategories(
		IN  id_post INT(11),
		OUT affected_rows
	)
	BEGIN
	END
