-- Posts

	-- get all post 

	CREATE PROCEDURE getAll(
		IN start INT,               -- start offset
		IN limit INT,               -- number of posts
		IN search CHAR,             -- filter posts by search string
		IN like CHAR,               -- filter posts by search string using LIKE for partial search, slower
		IN username CHAR,           -- filter by author username
		IN status CHAR,             -- filter by author username
		IN taxonomy_item_slug CHAR, -- filter by taxonomy item slug
		IN post_id ARRAY,           -- include only posts with post_id
		IN taxonomy_item_id INT,    -- filter by taxonomy item id
		-- global	
		IN type CHAR,               -- post type, default 'post'
		IN site_id INT,             -- filter by site_id
		IN admin_id INT,            -- filter by admin_id
		IN language_id INT,         -- filter by site_id
		-- comment
		IN comment_count INT,       -- flag to include comment count
		IN comment_status INT,      -- flag to include comment status
		-- archive
		IN year INT,                -- filter by year
		IN month INT,               -- filter by month
		-- taxonomy
		IN categories INT,          -- flag to include post categories
		IN tags INT,                -- flag to include post tags
		IN taxonomy CHAR,           -- flag to include post taxonomy 

		IN order_by CHAR,           -- sort by column
		IN direction CHAR,          -- sort order, asc/desc

		-- return array of posts for posts query
		OUT fetch_all,
		-- return posts count for count query
		OUT fetch_one,
	)
	BEGIN

		SELECT pd.*,post.*,ad.username,ad.display_name,ad.admin_id,ad.email, ad.avatar, ad.bio, ad.first_name, ad.last_name, post.post_id as array_key
			@IF isset(:comment_count)
			THEN
				,(SELECT COUNT(c.comment_id) 
						FROM comment c 
					WHERE 
						post.post_id = c.post_id
					
						@IF isset(:comment_status)
						THEN
							AND c.status = :comment_status
						END @IF
					) 
				
				AS comment_count
			END @IF
			
			@IF isset(:search)
			THEN 
				,MATCH(pd.name, pd.content)
				AGAINST(
					:search
					 @IF isset(:search_boolean) && !empty(:search_boolean)
					 THEN 
						IN BOOLEAN MODE
					 END @IF	     
				) as score
			END @IF	

			-- categories
			@IF !empty(:categories) 
			THEN 
				-- ,(SELECT CONCAT('[', GROUP_CONCAT('{"taxonomy_item_id":', taxonomies.taxonomy_item_id, ',"name":"' , td.name, '","slug":"' , td.slug, '"}'), ']')
				,(SELECT JSON_ARRAYAGG( JSON_OBJECT('taxonomy_item_id', taxonomies.taxonomy_item_id, 'name' , td.name, 'slug' , td.slug) )
					FROM taxonomy_item AS taxonomies
				
					INNER JOIN taxonomy_to_site t2s ON (taxonomies.taxonomy_item_id = t2s.taxonomy_item_id AND t2s.site_id = site_id) 
					INNER JOIN taxonomy_item_content td ON (taxonomies.taxonomy_item_id = td.taxonomy_item_id AND td.language_id = :language_id)  
					INNER JOIN taxonomy t ON (taxonomies.taxonomy_id = t.taxonomy_id)  
					
					LEFT JOIN post_to_taxonomy_item pt ON (taxonomies.taxonomy_item_id = pt.taxonomy_item_id)  

					WHERE 
					
					td.language_id = :language_id AND t2s.site_id = :site_id AND pt.post_id = post.post_id AND t.type = "categories" AND pt.post_id = post.post_id
					
					LIMIT :categories


				) as categories
			END @IF	
			
			-- tags
			@IF !empty(:tags) 
			THEN 
			-- ,(SELECT CONCAT('[', GROUP_CONCAT('{"taxonomy_item_id":', taxonomies.taxonomy_item_id, ',"name":"' , td.name, '","slug":"' , td.slug, '"}'), ']')
				,(SELECT JSON_ARRAYAGG( JSON_OBJECT('taxonomy_item_id', taxonomies.taxonomy_item_id, 'name' , td.name, 'slug' , td.slug) )
					FROM taxonomy_item AS taxonomies
				
					INNER JOIN taxonomy_to_site t2s ON (taxonomies.taxonomy_item_id = t2s.taxonomy_item_id AND t2s.site_id = site_id) 
					INNER JOIN taxonomy_item_content td ON (taxonomies.taxonomy_item_id = td.taxonomy_item_id AND td.language_id = :language_id)  
					INNER JOIN taxonomy t ON (taxonomies.taxonomy_id = t.taxonomy_id)  
					
					LEFT JOIN post_to_taxonomy_item pt ON (taxonomies.taxonomy_item_id = pt.taxonomy_item_id)  

					WHERE 
					
					td.language_id = :language_id AND t2s.site_id = :site_id AND pt.post_id = post.post_id AND t.type = "tags" AND pt.post_id = post.post_id
					
					LIMIT :tags


				) as tags
			END @IF			
			
			-- custom taxonomy
			@IF !empty(:taxonomy) 
			THEN 
			-- ,(SELECT CONCAT('[', GROUP_CONCAT('{"taxonomy_item_id":', taxonomies.taxonomy_item_id, ',"name":"' , td.name, '","slug":"' , td.slug, '"}'), ']')
				,(SELECT JSON_ARRAYAGG( JSON_OBJECT('taxonomy_item_id', taxonomies.taxonomy_item_id, 'name' , td.name, 'slug' , td.slug) )
					FROM taxonomy_item AS taxonomies
				
					INNER JOIN taxonomy_to_site t2s ON (taxonomies.taxonomy_item_id = t2s.taxonomy_item_id AND t2s.site_id = site_id) 
					INNER JOIN taxonomy_item_content td ON (taxonomies.taxonomy_item_id = td.taxonomy_item_id AND td.language_id = :language_id)  
					INNER JOIN taxonomy t ON (taxonomies.taxonomy_id = t.taxonomy_id)  
					
					LEFT JOIN post_to_taxonomy_item pt ON (taxonomies.taxonomy_item_id = pt.taxonomy_item_id)  

					WHERE 
					
					td.language_id = :language_id AND t2s.site_id = :site_id AND pt.post_id = post.post_id AND t.type = :taxonomy AND pt.post_id = post.post_id


				) as taxonomy
			END @IF
			
		FROM post
			LEFT JOIN post_content pd ON (
				post.post_id = pd.post_id 
				
				@IF isset(:language_id)
				THEN
					AND pd.language_id = :language_id
				END @IF

			)  
			LEFT JOIN post_to_site ps ON (post.post_id = ps.post_id)  
			LEFT JOIN admin ad ON (post.admin_id = ad.admin_id)  
			
			@IF isset(:taxonomy_item_id) || isset(:taxonomy_item_slug)
			THEN
				LEFT JOIN post_to_taxonomy_item pt ON (post.post_id = pt.post_id)   
			END @IF			
		
		WHERE 1 = 1 
		
			@IF isset(:type) && !empty(:type)
			THEN
				AND post.type = :type
			END @IF			
			
			@IF isset(:status) && !empty(:status)
			THEN
				AND post.status = :status
			@ELSE
				AND post.status = 'publish'
			END @IF
			
			-- username/author
			@IF isset(:username)
			THEN
				AND post.admin_id = (SELECT admin_id FROM admin WHERE username = :username LIMIT 1)
			END @IF	
			
			-- admin_id
			@IF isset(:admin_id)
			THEN
				AND post.admin_id = :admin_id
			END @IF

            -- search
            @IF isset(:search) && !empty(:search)
			THEN 
				-- AND pd.name LIKE CONCAT('%',:search,'%')
				AND MATCH(pd.name, pd.content) 
				AGAINST(
					:search
					 @IF isset(:search_boolean) && !empty(:search_boolean)
					 THEN 
						IN BOOLEAN MODE
					 END @IF	     
				)
        	END @IF	     
            
			-- like
			@IF isset(:like) && !empty(:like)
			THEN 
				 AND pd.name LIKE CONCAT('%',:like,'%')
			END @IF 

            -- post_id
			@IF isset(:post_id) && count(:post_id) > 0
			THEN 
			
				AND post.post_id IN (:post_id)
				
			END @IF		

			@IF isset(:site_id) && !empty(:site_id)
			THEN
				AND ps.site_id = :site_id
			END @IF

			
			@IF isset(:taxonomy_item_id)
			THEN
				AND pt.taxonomy_item_id IN (:taxonomy_item_id)
			END @IF	
			
			
			@IF isset(:taxonomy_item_slug)
			THEN
				AND pt.taxonomy_item_id IN (
					SELECT ti.taxonomy_item_id 
						FROM taxonomy_item_content AS tic
						  -- include child categories
						LEFT JOIN taxonomy_item AS ti ON ti.parent_id = tic.taxonomy_item_id OR ti.taxonomy_item_id  = tic.taxonomy_item_id
					WHERE slug = :taxonomy_item_slug
				)
			END @IF

			-- month
			@IF isset(:month) && !empty(:month)
			THEN
				AND MONTH(post.created_at) = :month
			END @IF					

			-- year
			@IF isset(:year) && !empty(:year)
			THEN
				AND YEAR(post.created_at) = :year
			END @IF					

			-- ORDER BY parameters can't be binded, because they are added to the query directly they must be properly sanitized by only allowing a predefined set of values
			@IF isset(:order_by)
			THEN
				ORDER BY post.$order_by $direction		
			@ELSE
				ORDER BY post.post_id DESC
			END @IF
			
			-- limit
			@IF isset(:limit)
			THEN
				@SQL_LIMIT(:start, :limit)
			END @IF;


		-- SELECT FOUND_ROWS() as count;
		SELECT count(*) FROM (
			
			@SQL_COUNT(post.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 get(
		IN post_id INT,
		IN slug CHAR,
		IN language_id INT,
		IN comment_count INT,
		IN comment_status INT,
		IN admin_id INT,
		IN type CHAR,
		
		OUT fetch_row, -- post
		OUT fetch_all, -- content
		OUT fetch_all, -- meta
		OUT fetch_all, -- post_to_site
		OUT fetch_all, -- post_to_taxonomy_item
	)
	BEGIN

		SELECT _.*,pd.*,_.post_id,ad.admin_id,ad.username,ad.display_name,ad.email
		
		@IF isset(:comment_count)
			THEN
				,(SELECT COUNT(c.comment_id) 
						FROM comment c 
					WHERE 
						_.post_id = c.post_id
					
						@IF isset(:comment_status)
						THEN
							AND c.status = :comment_status
						END @IF
					) 
				
				AS comment_count
			END @IF 
			
			FROM post AS _
			LEFT JOIN post_content pd ON 
				(
					_.post_id = pd.post_id 

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

			LEFT JOIN admin ad ON (_.admin_id = ad.admin_id)  				
			
		WHERE 1 = 1

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

            @IF isset(:post_id) && :post_id > 0
			THEN
                AND _.post_id = :post_id
        	END @IF		
			
            @IF isset(:admin_id)
			THEN
                AND _.admin_id = :admin_id
        	END @IF			
			
			@IF isset(:type)
			THEN
                AND _.type = :type
        	END @IF			

        LIMIT 1; 
		
		-- content
		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_content 
		WHERE post_id = @result.post_id;
		
	 
		-- meta
		SELECT `key` as array_key,value as array_value FROM post_meta
			WHERE post_meta.post_id = @result.post_id;	 

		-- post_to_site
		SELECT site_id as array_key, site_id FROM post_to_site
			WHERE post_to_site.post_id = @result.post_id;		
			
		-- post_to_taxonomy_item
		SELECT taxonomy_item_id as array_key, taxonomy_item_id FROM post_to_taxonomy_item
			WHERE post_to_taxonomy_item.post_id = @result.post_id;	 
	 
	END

	-- Add new post

	CREATE PROCEDURE add(
		IN post ARRAY,
		IN post_content ARRAY,
		IN taxonomy_item_id ARRAY,
		IN post_field_value ARRAY,
		IN site_id ARRAY,
		OUT insert_id,
		OUT insert_id,
		OUT insert_id,
		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_content  = @FILTER(:post_content, post_content, false, true)


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

		@EACH(:taxonomy_item_id) 
			INSERT INTO post_to_taxonomy_item 
		
				( taxonomy_item_id, post_id )
			
			VALUES ( :each, @result.post)
			ON DUPLICATE KEY UPDATE taxonomy_item_id = :each;
			
		@EACH(:post_field_value) 
			INSERT INTO post_field_value 
		
				( @KEYS(:each), post_id )
			
			VALUES ( :each, @result.post)
			ON DUPLICATE KEY UPDATE value = :each.value;

		@EACH(:site_id) 
			INSERT INTO post_to_site 
			
				( post_id, site_id )
				
			VALUES ( @result.post, :each );		

	END

	-- Edit post

	CREATE PROCEDURE edit(
		IN post ARRAY,
		IN post_content ARRAY,
		IN taxonomy_item_id ARRAY,
		IN post_field_value ARRAY,
		IN post_id INT,
		IN site_id ARRAY,
		OUT insert_id,
		OUT affected_rows,
		OUT insert_id,
		OUT affected_rows,
		OUT insert_id,
		OUT affected_rows
	)
	BEGIN
	
		:post_content  = @FILTER(:post_content, post_content, false, true)
		
		@EACH(:post_content) 
			INSERT INTO post_content 
		
				( @KEYS(:each), post_id)
			
			VALUES ( :each, :post_id)
			ON DUPLICATE KEY UPDATE @LIST(:each);


		@IF isset(:taxonomy_item_id)
		THEN
			DELETE FROM post_to_taxonomy_item WHERE post_id = :post_id
		END @IF;

		@EACH(:taxonomy_item_id) 
			INSERT INTO post_to_taxonomy_item 
		
				( taxonomy_item_id, post_id)
			
			VALUES ( :each, :post_id)
			ON DUPLICATE KEY UPDATE taxonomy_item_id = :each;

		@EACH(:post_field_value) 
			INSERT INTO post_field_value 
		
				( @KEYS(:each), post_id )
			
			VALUES ( :each, :post_id)
			ON DUPLICATE KEY UPDATE value = :each.value;

		@IF isset(:site_id) 
		THEN
			DELETE FROM post_to_site WHERE post_id = :post_id
		END @IF;

		@EACH(:site_id) 
			INSERT INTO post_to_site 
			
				( post_id, site_id )
				
			VALUES ( :post_id, :each );			


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

	END
	
	-- Edit post content

	CREATE PROCEDURE editContent(
		IN post_content ARRAY,
		IN post_id INT,
		IN language_id INT,
		OUT affected_rows
	)
	BEGIN
	
		:post_content  = @FILTER(:post_content, post_content)
	
		UPDATE post_content 
			
			SET @LIST(:post_content) 
			
		WHERE post_id = :post_id AND language_id = :language_id
	END
	
	
	-- Delete post

	CREATE PROCEDURE delete(
		IN  post_id ARRAY,
		OUT affected_rows,
		OUT affected_rows,
		OUT affected_rows,
		OUT affected_rows
	)
	BEGIN
		
		DELETE FROM post_to_taxonomy_item WHERE post_id IN (:post_id);
		DELETE FROM post_to_site WHERE post_id IN (:post_id);
		DELETE FROM post_content WHERE post_id IN (:post_id);
		DELETE FROM post WHERE post_id IN (:post_id);
	 
	END
	
	
	
	-- Get tags

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

	CREATE PROCEDURE postCategories(
		IN  post_id INT,
		OUT affected_rows
	)
	BEGIN
	END
	
	-- Add categories
	CREATE PROCEDURE setPostTaxonomy(
		IN  post_id INT,
		IN  taxonomy_item ARRAY,
		OUT affected_rows
	)
	BEGIN
	
		DELETE FROM post_to_taxonomy_item WHERE post_id = :post_id;
	
		@EACH(:taxonomy_item) 
			INSERT IGNORE INTO post_to_taxonomy_item 
		
				( post_id, taxonomy_item_id)
			
			VALUES ( :post_id, :each);
	END


	-- Get archives

	CREATE PROCEDURE getArchives(
		IN start INT,
		IN limit INT,
		IN interval CHAR,
		IN type CHAR,
	)
	BEGIN
	
		SELECT 
			YEAR(archives.created_at) AS year 
			
			@IF isset(:interval) AND (:interval == "month" || :interval == "day")
			THEN
				,MONTH(archives.created_at) AS month 
			END @IF

			@IF isset(:interval) AND :interval == "day"
			THEN
				,DAYOFMONTH(archives.created_at) AS day 
			END @IF
			
			,count(archives.post_id) as count 
			
		FROM post AS archives
		LEFT JOIN post_to_site ps ON (archives.post_id = ps.post_id) 
		
		WHERE 1 = 1 
			
			@IF isset(:type)
			THEN
				AND type = :type
			END @IF
			
			@IF isset(:site_id) && !empty(:site_id)
			THEN
				AND ps.site_id = :site_id
			END @IF


		GROUP BY 
			YEAR(archives.created_at)
			
			@IF isset(:interval) AND (:interval == "month" || :interval == "day")
			THEN
				,MONTH(archives.created_at)
			END @IF

			@IF isset(:interval) AND :interval == "day"
			THEN
				,DAYOFMONTH(archives.created_at)
			END @IF
			
		ORDER BY 			
			YEAR(archives.created_at)
			
			@IF isset(:interval) AND (:interval == "month" || :interval == "day")
			THEN
				,MONTH(archives.created_at)
			END @IF

			@IF isset(:interval) AND :interval == "day"
			THEN
				,DAYOFMONTH(archives.created_at)
			END @IF


		-- limit
		@IF isset(:limit) AND :limit > 0
		THEN
			@SQL_LIMIT(:start, :limit)
		END @IF;		
	
	END