DROP TABLE IF EXISTS `babyfeed`.`tbl_blog_comment` ;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_blog_comment` (
  `bc_id` INT NOT NULL AUTO_INCREMENT,
  `p_bc_id` INT NOT NULL DEFAULT 0,
  `b_no` INT NOT NULL DEFAULT 0,
  `title` VARCHAR(300) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `member_id` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`bc_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1;
        
        
DROP FUNCTION IF EXISTS fn_b_comment;


DELIMITER $$ 
CREATE FUNCTION fn_b_comment(v_b_no INT, s_p_bc_id INT) RETURNS INT 
NOT DETERMINISTIC 
READS SQL DATA 
BEGIN 
    DECLARE v_bc_id INT;
    DECLARE v_p_bc_id INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @bc_id = NULL;
 
    SET v_p_bc_id = @bc_id;   
    SET v_bc_id = -1;                                                              
 
    IF @bc_id IS NULL THEN
        RETURN NULL;
    END IF;
 
    FIND:LOOP   
		SELECT MIN(bc_id) INTO @bc_id 
		FROM tbl_blog_comment
		WHERE p_bc_id = v_p_bc_id                                                      
		  AND bc_id > v_bc_id
		  AND b_no = v_b_no;

		IF (@bc_id IS NOT NULL) OR (v_p_bc_id = @start_with) THEN
			SET @level = @level + 1;
			RETURN @bc_id;		
		END IF;
        
		SET @level := @level - 1;    
	 
		SELECT bc_id, p_bc_id INTO v_bc_id, v_p_bc_id 
		FROM tbl_blog_comment
		WHERE bc_id = v_p_bc_id
         AND b_no = v_b_no;
    END LOOP; 
END 
$$ 
DELIMITER ;

select fn_b_comment(2)

select * from tbl_blog_comment

insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 1,'안녕1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 2,'안녕2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 3,'안녕3', now(), 'renamaestro');

insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,4,2, '안녕2-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,5,2, '안녕2-2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,6,4, '안녕2-4-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,7,3, '안녕2-2-3', now(), 'renamaestro');

insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,8,3, '안녕3-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,9,7, '안녕3-7-1', now(), 'renamaestro');

insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (2, 10,'글 2번', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,11,10, '독-10-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,12,10, '독-10-2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,13,10, '독-10-3', now(), 'renamaestro');

insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,14,12, '독-10-12-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,15,14, '독-10-12-14-1', now(), 'renamaestro');

insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (6,16,'고흐의 별이 빛나는 밤에', now(), 'renamaestro');


SELECT 
	CASE WHEN LEVEL-1 > 0
    THEN CONCAT(CONCAT(REPEAT('    ', level  - 1),'┗'), bc.title)
	ELSE bc.title END AS title_step
    , bc.title
	, bc.b_no
	, bc.bc_id
	, bc.p_bc_id
	, result.level
FROM (
	SELECT fn_b_comment(1,3) AS bc_id, @level AS level, bc.b_no
	  FROM (
			SELECT @start_with:=3, @bc_id:=@start_with, @level:=0
            ) tbl 
			JOIN tbl_blog_comment bc ON bc.b_no = 1
	 ) result
LEFT OUTER JOIN tbl_blog_comment bc ON bc.bc_id = result.bc_id
WHERE bc.bc_id IS NOT NULL