DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_degree`(IN `_list` MEDIUMTEXT, IN `doctor_id` INT)
BEGIN

DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;

iterator:
LOOP
-- exit the loop if the list seems empty or was null;
-- this extra caution is necessary to avoid an endless loop in the proc.
IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
LEAVE iterator;
END IF;

-- capture the next value from the list
SET _next = SUBSTRING_INDEX(_list,',',1);

-- save the length of the captured value; we will need to remove this
-- many characters + 1 from the beginning of the string 
-- before the next iteration
SET _nextlen = LENGTH(_next);

-- trim the value of leading and trailing spaces, in case of sloppy CSV strings
SET _value = TRIM(_next);

-- insert the extracted value into the target table
SET @rec_count = (select id from mst_degrees where degree_name=_value ORDER BY id LIMIT 1);
select _value, @rec_count,doctor_id;
if @rec_count is null then
INSERT INTO mst_degrees (degree_name) VALUES (_value);
set @rec_count=(select LAST_INSERT_ID());
end if;
insert into doctor_degrees(`user_id`, `degree_id`) values(doctor_id,@rec_count);


-- rewrite the original string using the `INSERT()` string function,
-- args are original string, start position, how many characters to remove, 
-- and what to "insert" in their place (in this case, we "insert"
-- an empty string, which removes _nextlen + 1 characters)
SET _list = INSERT(_list,1,_nextlen + 1,'');
END LOOP;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_import_doctor_list`()
BEGIN
DECLARE n BIGINT DEFAULT 0;
DECLARE i BIGINT DEFAULT 0;
DECLARE rec_count BIGINT DEFAULT 0;

SELECT COUNT(*) FROM doctor_list INTO n;

SET i=0;
SET rec_count=0;
-- SET n=20;

WHILE i<n DO 
 INSERT INTO `users`(`user_type_id`, `phone_code`, `phone_no`, `password`,  `is_phone_no_verified`, `status`) SELECT 2 ,'+880',`Personal Phone`,'$2b$10$warSQ2HhX34yJPt5ZCamRuiBuIbHXFiCouNqpnPpGH9ujKMFDDAci',1,'active' FROM doctor_list LIMIT i,1;
 -- SELECT 2 ,'+880',`Personal Phone`,'$2b$10$warSQ2HhX34yJPt5ZCamRuiBuIbHXFiCouNqpnPpGH9ujKMFDDAci',1,'active' FROM doctor_list LIMIT i,1;
 set @user_id=(select LAST_INSERT_ID());
 
 INSERT INTO `doctors`(`user_id`, `name`, `gender`, `profile_picture`,`registration_number`,`visiting_card_front`,`visiting_card_back`,`status`)  SELECT @user_id ,`Doctor's Name`,LOWER(`Gender`),
    '',
    '',
    '',
    '','approved' FROM doctor_list LIMIT i,1;
 
 SET @qual=(select `Qualifications` from doctor_list LIMIT i,1);
 SET @specialisation=(select `Specialist` from doctor_list LIMIT i,1);
 call insert_degree(@qual,@user_id);
 call insert_specialisation(@specialisation,@user_id);
 
 SET i = i + 1;
  -- SET rec_count=rec_count+1;
END WHILE;    

 select n;    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_specialisation`(IN `_list` MEDIUMTEXT, IN `doctor_id` INT)
BEGIN

DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;

iterator:
LOOP
-- exit the loop if the list seems empty or was null;
-- this extra caution is necessary to avoid an endless loop in the proc.
IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
LEAVE iterator;
END IF;

-- capture the next value from the list
SET _next = SUBSTRING_INDEX(_list,',',1);

-- save the length of the captured value; we will need to remove this
-- many characters + 1 from the beginning of the string 
-- before the next iteration
SET _nextlen = LENGTH(_next);

-- trim the value of leading and trailing spaces, in case of sloppy CSV strings
SET _value = TRIM(_next);

-- insert the extracted value into the target table
SET @rec_count = (select id from mst_specializations where mst_specializations.name=_value ORDER BY id LIMIT 1);
select _value, @rec_count,doctor_id;
if @rec_count is null then
INSERT INTO mst_specializations (name) VALUES (_value);
set @rec_count=(select LAST_INSERT_ID());
end if;
insert into doctor_specialisations(`user_id`, `specialization_id`) values(doctor_id,@rec_count);


-- rewrite the original string using the `INSERT()` string function,
-- args are original string, start position, how many characters to remove, 
-- and what to "insert" in their place (in this case, we "insert"
-- an empty string, which removes _nextlen + 1 characters)
SET _list = INSERT(_list,1,_nextlen + 1,'');
END LOOP;

END$$
DELIMITER ;