DROP FUNCTION IF EXISTS `GET_NAVER_ESCODE`;;
CREATE DEFINER=`root`@`%` FUNCTION `GET_NAVER_ESCODE`(
	`V_CODE` VARCHAR(50)
)
RETURNS text CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE RETURN_VALUE TEXT;
	
	BEGIN
		   
	SELECT concat(ID,'|', SHOPCATE) into RETURN_VALUE 
	 FROM CATEGORY
		WHERE 
	 JSON_EXTRACT(SHOPCATE, '$[5].cid') = V_CODE  AND LEVEL > 2 limit 1;
		   
	END;
 	SET RETURN_VALUE = IFNULL(RETURN_VALUE, '');
   RETURN (RETURN_VALUE);
END;;
CREATE TRIGGER `userinf_after_update` AFTER UPDATE ON `userinf` FOR EACH ROW BEGIN
    DECLARE first_run_date DATE;
    DECLARE today DATE;
    DECLARE user_exists INT;
    SET today = CURDATE();
    SELECT MIN(DATE(STARTUSE)) INTO first_run_date
      FROM userinf
     WHERE USERID = NEW.USERID
       AND YEAR(STARTUSE) = YEAR(today)
       AND MONTH(STARTUSE) = MONTH(today);
    IF first_run_date IS NOT NULL AND first_run_date = today THEN
	     SELECT COUNT(*) INTO user_exists FROM cloudinaryapiinfo WHERE USE_YN = 'N';
        IF user_exists > 0 THEN 
            UPDATE cloudinaryapiinfo SET USE_YN = 'Y' WHERE USE_YN = 'N';
        END IF;
    END IF;
END;;
CREATE TRIGGER `workmgrdetail_after_insert` AFTER INSERT ON `workmgrdetail` FOR EACH ROW BEGIN 
    IF NEW.status = '-1'
       AND NEW.message NOT LIKE '%out of total%'
       AND NEW.message NOT LIKE '%상품수%'
       AND NEW.message NOT LIKE '%최대 개수%'
       AND NEW.message NOT LIKE '%상품 수%'
    THEN
        UPDATE shopprodinfo
           SET SHOPSENDSTATUS = 9100
         WHERE compno = 4
           AND COMPAYNY_GOODS_CD = NEW.prodseq;
    END IF;
END;;

