<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ga.core.mapper.notice.NoticeMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.notice.NoticeVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.notice.NoticeResp"/>

    <sql id="cols">
        n.notice_id, n.title, n.content, n.notice_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'NOTICE_TYPE' AND cc.code = n.notice_type) AS notice_type_name,
        n.start_date, n.end_date, n.is_active, n.created_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="cols"/> FROM notice n WHERE n.notice_id = #{noticeId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="cols"/> FROM notice n
        <where>
            <if test="noticeType != null and noticeType != ''">AND n.notice_type = #{noticeType}</if>
            <if test="isActive != null">AND n.is_active = #{isActive}</if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND n.title LIKE CONCAT('%', #{searchKeyword}, '%')
            </if>
        </where>
        ORDER BY n.start_date DESC, n.notice_id DESC
    </select>

    <!-- 오늘 기준 유효 공지 -->
    <select id="selectActive" resultMap="RespMap">
        SELECT <include refid="cols"/> FROM notice n
        WHERE n.is_active = TRUE
          AND n.start_date &lt;= CURRENT_DATE
          AND (n.end_date IS NULL OR n.end_date &gt;= CURRENT_DATE)
        ORDER BY n.start_date DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="noticeId">
        INSERT INTO notice (title, content, notice_type, start_date, end_date, is_active, created_by)
        VALUES (#{title}, #{content}, #{noticeType}, #{startDate}, #{endDate}, TRUE, #{createdBy})
    </insert>

    <update id="update">
        UPDATE notice
           SET title       = #{title},
               content     = #{content},
               notice_type = #{noticeType},
               start_date  = #{startDate},
               end_date    = #{endDate},
               updated_at  = NOW(),
               updated_by  = #{updatedBy}
         WHERE notice_id = #{noticeId}
    </update>

    <update id="updateActive">
        UPDATE notice SET is_active = #{isActive}, updated_at = NOW() WHERE notice_id = #{noticeId}
    </update>

    <delete id="delete">
        DELETE FROM notice WHERE notice_id = #{noticeId}
    </delete>

</mapper>
