-- Comments

	-- get all comment 

	CREATE PROCEDURE getAll(
		-- variables
		IN language_id INT,
		IN post_id INT,
		IN user_id INT,
		IN admin_id INT,
		IN status INT,
		IN post_title INT,

		-- pagination
		IN start INT,
		IN limit INT,
		IN cursor INT, -- current cursor (comment_id) for cursor pagination
		IN order CHAR,

		-- return
		OUT fetch_all, -- orders
		OUT fetch_one  -- count
	)
	BEGIN

		SELECT user.username, user.email, user.first_name,  user.last_name,  user.display_name, user.avatar, user.bio,  user.subscribe, comment.*, comment_id as array_key
			@IF isset(:post_title) AND :post_title
			THEN 
				,post_content.name, post_content.slug
			END @IF			
			
			FROM comment
			LEFT JOIN user ON (user.user_id = comment.user_id)	
			
			@IF isset(:admin_id) AND :admin_id
			THEN 
				LEFT JOIN post ON (post.post_id = comment.post_id)
			END @IF			
			
			@IF isset(:post_title) AND :post_title
			THEN 
				LEFT JOIN post_content ON (post_content.post_id = comment.post_id)
			END @IF
		WHERE 1 = 1
            
            -- post
            @IF isset(:post_id)
            THEN 
            	AND comment.post_id  = :post_id
            END @IF	            
            
			-- post slug
            @IF isset(:slug)
            THEN 
            	AND comment.post_id  = (SELECT post_id FROM post_content WHERE slug = :slug LIMIT 1) 
            END @IF

            -- user
            @IF isset(:user_id)
            THEN 
            	AND comment.user_id  = :user_id
            END @IF	 
			
            -- admin
            @IF isset(:admin_id)
            THEN 
            	AND post.admin_id  = :admin_id
            END @IF	            
			
            -- user
            @IF isset(:language_id) AND isset(:post_title)
            THEN 
            	AND post_content.language_id  = :language_id
            END @IF	              
            
            -- status
            @IF isset(:status)
            THEN 
            	AND comment.status  = :status
            END @IF	            

		-- cursor pagination

		@IF isset(:cursor)

		THEN

		    AND comment.comment_id > :cursor

		END @IF

		ORDER BY parent_id, comment_id 
		
		@IF isset(:order) AND :order == "desc"
		THEN 
			DESC
		END @IF	 
		
		LIMIT :limit OFFSET :start;
		
		SELECT count(*) FROM (
			
			@SQL_COUNT(comment_id, comment) -- this takes previous query removes limit and replaces select columns with parameter comment_id
			
		) as count;	 
	END
	

	-- get one comment

	CREATE PROCEDURE get(
		IN comment_id INT,
		IN admin_id INT,
		OUT fetch_row
	)
	BEGIN

		SELECT * 
			FROM comment AS _
			LEFT JOIN user ON user.user_id = _.user_id
			
		@IF isset(:admin_id)
		THEN
			INNER JOIN post ON post.post_id = _.post_id AND post.admin_id = :admin_id
        	END @IF	
			
		WHERE 1 = 1

            @IF isset(:comment_id)
			THEN
                AND _.comment_id = :comment_id
        	END @IF			

        LIMIT 1; 
		
		
		-- SELECT `key` as array_key,value as array_value FROM comment_meta as _
			-- WHERE _.comment_id = @result.comment_id
		
          
	END

	-- Add new comment

	CREATE PROCEDURE add(
		IN comment ARRAY,
		OUT insert_id
	)
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		@FILTER(:comment, comment)
		
		INSERT INTO comment 
			
			( @KEYS(:comment) )
			
	  	VALUES ( :comment )
        
	END

	-- Edit comment

	CREATE PROCEDURE edit(
		IN comment ARRAY,
		IN comment_id INT,
		IN admin_id INT,
		OUT affected_rows
	)
	BEGIN
		-- allow only table fields and set defaults for missing values
		@FILTER(:comment, comment)

		UPDATE comment 
		
			SET @LIST(:comment) 
			
		WHERE comment_id = :comment_id
		
		@IF isset(:admin_id)
		THEN
			AND comment.post_id IN (SELECT post_id FROM post WHERE post.admin_id = :admin_id)
		END @IF;
	END
	
	-- Delete comment

	CREATE PROCEDURE delete(
		IN comment_id ARRAY,
		IN admin_id INT,
		OUT affected_rows
	)
	BEGIN

		DELETE comment FROM comment 
		
		@IF isset(:admin_id)
		THEN
			INNER JOIN post ON (post.post_id = comment.post_id AND post.admin_id = :admin_id)
		END @IF

		WHERE comment_id IN (:comment_id)
	 
	END
