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;;
DROP TRIGGER `userinf_after_update`;;
CREATE TRIGGER `userinf_after_update` AFTER INSERT 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
        UPDATE cloudinaryapiinfo SET apikey = '0' WHERE compno = 0;
    END IF;
END;;
DROP TRIGGER `workmgrdetail_after_insert`;;
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 '%상품 수%'
	   AND NEW.message NOT LIKE '%판매 중인 상품은 최대%'
	   AND NEW.message NOT LIKE '%상품 등록이 1분 이상 소요되어%'
    THEN
        UPDATE shopprodinfo
           SET SHOPSENDSTATUS = 9100
         WHERE compno = 4
           AND COMPAYNY_GOODS_CD = NEW.prodseq;
    END IF;
END;;
CREATE DEFINER=`root`@`%` PROCEDURE `usp_UpdateShopSendStatus`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE v_AffectedRows INT DEFAULT 0;
    UPDATE shopprodin AS a
    LEFT JOIN (
        SELECT 'N' AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shop11staddidtl
        UNION ALL
        SELECT 'N' AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shoplotteonadddtl
        UNION ALL
        SELECT IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopaddrkakaodtl
        UNION ALL
        SELECT IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, REMOVEIMGTEXTYN, SEQ FROM shopaddrnaverstoredtl
        UNION ALL
        SELECT IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopgmarket2adddtl
        UNION ALL
        SELECT IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopauction2adddtl
        UNION ALL
        SELECT IMAGERESIZEYN AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopalwayzadddtl
        UNION ALL
        SELECT 'N' AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shoptossshoppingadddtl
        UNION ALL
        SELECT 'N' AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, 'N' AS IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopcoupangaddidtl
        UNION ALL
        SELECT 'N' AS IMAGERESIZE, MIRRORIMGYN, CHANGESIZE, 'N' AS IMGTHREEONFOUR, 'N' AS REMOVEIMGTEXTYN, SEQ FROM shopmelchiaddidtl
    ) AS D ON D.SEQ = a.SEQ
    	SET a.SHOPSENDSTATUS = 7000
    WHERE a.SHOPSENDSTATUS != 7000 
      AND a.INSERTDT >= '2025-05-26' AND a.INSERTDT <  '2025-06-05'
      AND (D.IMAGERESIZE = 'Y' OR D.IMAGERESIZE = '02' OR D.MIRRORIMGYN = 'Y' OR D.CHANGESIZE = 'Y' OR D.IMGTHREEONFOUR = 'Y' OR D.REMOVEIMGTEXTYN = 'Y');
    SET v_AffectedRows = ROW_COUNT();
    SELECT v_AffectedRows AS ResultMessage;
END;;

DROP TRIGGER `trg_workmgrdetail_after_insert_joblog`;;
CREATE DEFINER=`root`@`%` TRIGGER `trg_workmgrdetail_after_insert_joblog` AFTER INSERT ON `workmgrdetail` FOR EACH ROW BEGIN
  DECLARE v_shopcd VARCHAR(50);
  DECLARE v_shopseq INT;
  DECLARE v_shopuserid VARCHAR(50);
  DECLARE v_nickname VARCHAR(50);
  DECLARE v_workcd VARCHAR(50);
  DECLARE v_jobtype VARCHAR(50);
  DECLARE v_jobmain VARCHAR(50);
  DECLARE v_jobsub VARCHAR(50);
  DECLARE v_logmessage TEXT;
  DECLARE v_shopprodno VARCHAR(50);
  DECLARE v_jobresult VARCHAR(10);

    SELECT workmgrmaster.SHOPCD, workmgrmaster.SHOPSEQ, workmgrmaster.WORKCD, workmgrmaster.SHOP_USER_ID
      INTO v_shopcd, v_shopseq, v_workcd, v_shopuserid
      FROM workmgrmaster
     WHERE workmgrmaster.WORKGRP = NEW.WORKGRP
     LIMIT 1;
    
    SELECT shopdtl.NICKNM1
      INTO v_nickname
      FROM shopdtl
     WHERE shopdtl.SHOPCD = v_shopcd
       AND shopdtl.SHOPSEQ = v_shopseq
     LIMIT 1;
    
	 IF v_workcd = '100' then
	 	SET v_jobtype = 'PRD_REG';
	 	SET v_jobmain = 'PRD';
	 	SET v_jobsub = 'REG';
	 	SET v_logmessage = NEW.`MESSAGE`;
		IF NEW.STATUS = 'W06' THEN
		    IF NEW.`MESSAGE` LIKE '%취소%' THEN
		        SET v_jobresult = '실패';
		    ELSE
		        SET v_jobresult = '성공';
		    END IF;
		ELSE
		    SET v_jobresult = '실패';
		END IF;
    ELSEIF v_workcd = '200' then
	 	SET v_jobtype = 'PRD_EDT';
 	 	SET v_jobmain = 'PRD';
 		SET v_jobsub = 'EDT';
 		SET v_logmessage = NEW.`MESSAGE`;
		IF NEW.STATUS = 'W06' THEN
		    IF NEW.`MESSAGE` LIKE '%취소%' THEN
		        SET v_jobresult = '실패';
		    ELSE
		        SET v_jobresult = '성공';
		    END IF;
		ELSE
		    SET v_jobresult = '실패';
		END IF;
    ELSEIF v_workcd = '700' then
	 	SET v_jobtype = 'PRD_DIS';
 	 	SET v_jobmain = 'PRD';
 		SET v_jobsub = 'DIS';
 		SET v_logmessage = NEW.`MESSAGE`;
		IF NEW.STATUS = 'W06' THEN
		    IF NEW.`MESSAGE` LIKE '%취소%' THEN
		        SET v_jobresult = '실패';
		    ELSE
		        SET v_jobresult = '성공';
		    END IF;
		ELSE
		    SET v_jobresult = '실패';
		END IF;
	ELSEIF v_workcd = '800' then
	 	SET v_jobtype = 'PRD_RSL';
 	 	SET v_jobmain = 'PRD';
 		SET v_jobsub = 'RSL';
 		SET v_logmessage = NEW.`MESSAGE`;
		IF NEW.STATUS = 'W06' THEN
		    IF NEW.`MESSAGE` LIKE '%취소%' THEN
		        SET v_jobresult = '실패';
		    ELSE
		        SET v_jobresult = '성공';
		    END IF;
		ELSE
		    SET v_jobresult = '실패';
		END IF;
	ELSEIF v_workcd IN ('600', '500') then
	 	SET v_jobtype = 'PRD_DEL';
  	 	SET v_jobmain = 'PRD';
 		SET v_jobsub = 'DEL';
		IF NEW.STATUS = 'W06' THEN
		    IF NEW.`MESSAGE` LIKE '%취소%' THEN
		        SET v_jobresult = '실패';
		        SET v_logmessage = NEW.`MESSAGE`;
		    ELSE
		        SET v_jobresult = '성공';
		        SET v_logmessage = '[작업관리] 상품이 삭제되었습니다.';
		    END IF;
		ELSE
		    SET v_jobresult = '실패';
		   	SET v_logmessage = NEW.`MESSAGE`;
		END IF;
    END IF;
   
   IF NEW.STATUS = 'W06' then
	   	SET v_shopprodno = TRIM(BOTH ']' 
	                    FROM SUBSTRING_INDEX(
	                           SUBSTRING_INDEX(new.MESSAGE, '[', -1), 
	                           ']', 
	                           1
	                         ));
   ELSE
   		SET v_shopprodno = '';
   END IF;	
  
	INSERT INTO joblog (
		JOBDATE, JOBTYPE, JOBMAIN, JOBSUB, JOBDETAIL, PRODCD, SHOPPRODNO, SHOPCD, 
		SHOPUSERID, NICKNAME, SHOPSEQ, ORDID, PRODUCTNAME, OPTIONNAME, SALECNT, SALECOST, 
		RECEIVE_NAME, JOBRESULT, LOGMESSAGE
	)
	VALUES (
		NOW(), v_jobtype, v_jobmain, v_jobsub, "", NEW.prodseq, v_shopprodno, v_shopcd, 
		v_shopuserid, v_nickname, v_shopseq, "", "", "", 0, 0,
		"", v_jobresult, v_logmessage
	);

END;;