-- 마이페이지 > 쿠폰 목록
WITH TAB_COUPON AS (
    SELECT CC.CPN_ID                          /*쿠폰ID*/
         , C.CPN_NM                           /*쿠폰명*/
         , C.BUY_LIMIT_AMT                    /*구매제한금액*/
         , C.MAX_DC_AMT                       /*최대할인금액*/
         , CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
                WHEN #{frontGb} = 'M' THEN C.DC_MVAL
                WHEN #{frontGb} = 'A' THEN C.DC_AVAL
           END             AS DC_VAL          /*할인값*/
         , CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
                ELSE '%'
           END             AS DC_WAY          /*할인방법*/
         , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
                       ELSE CONCAT(C.BUY_LIMIT_AMT,'원 이상 구매 시 ')
                  END
                 ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
                       ELSE CONCAT('최대 ',C.MAX_DC_AMT,'원 할인')
                  END)     AS USE_CONDITION   /*사용조건*/
         , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
                ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
           END             AS ISSUE_CONDITION /*발급조건*/
         , CC.AVAIL_STDT                      /*유효시작일자*/
         , CC.AVAIL_EDDT                      /*유효종료일자*/
         , CC.CPN_CNT                         /*보유쿠폰수*/
         , CC.EXPIRE_YN                       /*만료여부*/
    FROM   (
            SELECT CC.CPN_ID
                 , DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d %H:%i') AS AVAIL_STDT  /*유효시작일자*/
                 , DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d %H:%i') AS AVAIL_EDDT  /*유효종료일자*/
                 , COUNT(*)                              AS CPN_CNT /*보유쿠폰수*/
                 , MAX(CASE WHEN NOW() > CC.AVAIL_EDDT THEN 'Y'
                            ELSE 'N'
                       END)                              AS EXPIRE_YN /*만료여부*/
            FROM   TB_CUST_COUPON CC
            WHERE  1 = 1
            AND    CC.CUST_NO = #{custNo}
            AND    CC.USED_DT IS NULL /*사용하지않은쿠폰만*/
            AND    CC.AVAIL_EDDT >= DATE_ADD(NOW(), INTERVAL -3 MONTH) /*최근3개월쿠폰만*/
            GROUP  BY CC.CPN_ID, DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d %H:%i'), DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d %H:%i')
           ) CC
         , TB_COUPON C
    WHERE  CC.CPN_ID = C.CPN_ID
    AND    C.CPN_STAT = 'G232_11' /*진행중인쿠폰*/
    AND    C.SITE_CD = #{siteCd}
)
, TAB_COUPON_REFVAL1 AS (
    SELECT CR.CPN_ID
         , CR.CPN_TARGET
         , CASE WHEN CR.CPN_TARGET = 'G260_10' /*상품*/ THEN (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = CR.REF_VAL AND GOODS_STAT = 'G008_90')
                WHEN CR.CPN_TARGET = 'G260_11' /*카테고리*/ THEN
                    CASE WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL),0) > 0 THEN (SELECT CATE1_NM FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL LIMIT 1)
                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL),0) > 0 THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL LIMIT 1)
                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL),0) > 0 THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL LIMIT 1)
                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL),0) > 0 THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL LIMIT 1)
                    END
                WHEN CR.CPN_TARGET = 'G260_12' /*브랜드*/ THEN (SELECT BRAND_KNM FROM TB_BRAND WHERE BRAND_CD = CR.REF_VAL AND USE_YN = 'Y')
                WHEN CR.CPN_TARGET = 'G260_13' /*업체*/ THEN (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY WHERE SUPPLY_COMP_CD = CR.REF_VAL AND USE_YN = 'Y' AND SUPPLY_STAT = 'G010_30')
           END AS REF_VAL
         , RANK() OVER(PARTITION BY CR.CPN_ID, CR.CPN_TARGET ORDER BY REF_VAL) AS RK
    FROM   TAB_COUPON C
         , TB_COUPON_REFVAL CR
    WHERE  C.CPN_ID = CR.CPN_ID
    AND    CR.CPN_TARGET != 'G260_14' /*제외상품아닌넘*/
)
, TAB_COUPON_REFVAL2 AS (
    SELECT CPN_ID
         , CPN_TARGET
         , GROUP_CONCAT(REF_VAL ORDER BY RK ASC SEPARATOR '/' ) AS REF_VAL
         , (SELECT COUNT(*)
            FROM   TAB_COUPON_REFVAL1
            WHERE  CPN_ID = CR.CPN_ID
            AND    CPN_TARGET = CR.CPN_TARGET)                  AS CNT
    FROM   TAB_COUPON_REFVAL1 CR
    WHERE  1 = 1
    AND    (
            (CPN_TARGET != 'G260_12' AND RK = 1) /*브랜드 외는 1개만 노출*/
            OR
            (CPN_TARGET = 'G260_12' AND RK <= 2) /*브랜드만 2개 노출*/
           )
    GROUP  BY CPN_ID, CPN_TARGET
)
SELECT C.CPN_ID                                /*쿠폰ID*/
     , C.CPN_NM                                /*쿠폰명*/
     , C.BUY_LIMIT_AMT                         /*구매제한금액*/
     , C.MAX_DC_AMT                            /*최대할인금액*/
     , C.DC_VAL                                /*할인값*/
     , C.DC_WAY                                /*할인방법*/
     , C.USE_CONDITION                         /*사용조건*/
     , C.ISSUE_CONDITION                       /*발급조건*/
     , C.AVAIL_STDT                            /*유효시작일자*/
     , C.AVAIL_EDDT                            /*유효종료일자*/
     , C.CPN_CNT                               /*보유쿠폰수*/
     , C.EXPIRE_YN                             /*만료여부*/
     , CONCAT(CR.REF_VAL,CASE WHEN CR.CPN_TARGET = 'G260_10' AND CR.CNT > 1 THEN ' 외'
                              WHEN CR.CPN_TARGET = 'G260_11' AND CR.CNT > 1 THEN ' 외'
                              WHEN CR.CPN_TARGET = 'G260_12' AND CR.CNT > 2 THEN ' 외'
                              WHEN CR.CPN_TARGET = 'G260_13' AND CR.CNT > 1 THEN ' 외'
                              ELSE ''
                         END) AS TGT_CONDITION /*대상조건*/
FROM   TAB_COUPON C
     , TAB_COUPON_REFVAL2 CR
WHERE  C.CPN_ID = CR.CPN_ID
ORDER  BY C.EXPIRE_YN, C.AVAIL_EDDT
;