<?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="go.kr.project.common.mapper.HtmlEditorMapper">

    <!-- 파일 정보 조회 -->
    <select id="selectHtmlEditorFile" parameterType="String" resultType="HtmlEditorFileVO">
        SELECT 
            FILE_ID,
            MODULE_ID,
            ORIGINAL_FILE_NM,
            STORED_FILE_NM,
            FILE_PATH,
            FILE_SIZE,
            FILE_EXT,
            FILE_TYPE,
            USE_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR,
            CASE
                WHEN FILE_SIZE > 1048576 THEN CONCAT(ROUND(FILE_SIZE/1048576, 2), ' MB')
                WHEN FILE_SIZE > 1024 THEN CONCAT(ROUND(FILE_SIZE/1024, 2), ' KB')
                ELSE CONCAT(FILE_SIZE, ' bytes')
            END AS FILE_SIZE_STR
        FROM 
            tb_common_html_editor_file
        WHERE 
            FILE_ID = #{fileId}
    </select>

    <!-- 모듈에 첨부된 파일 목록 조회 -->
    <select id="selectHtmlEditorFileListByModuleId" parameterType="String" resultType="HtmlEditorFileVO">
        SELECT 
            FILE_ID,
            MODULE_ID,
            ORIGINAL_FILE_NM,
            STORED_FILE_NM,
            FILE_PATH,
            FILE_SIZE,
            FILE_EXT,
            FILE_TYPE,
            USE_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR,
            CASE
                WHEN FILE_SIZE > 1048576 THEN CONCAT(ROUND(FILE_SIZE/1048576, 2), ' MB')
                WHEN FILE_SIZE > 1024 THEN CONCAT(ROUND(FILE_SIZE/1024, 2), ' KB')
                ELSE CONCAT(FILE_SIZE, ' bytes')
            END AS FILE_SIZE_STR
        FROM 
            tb_common_html_editor_file
        WHERE 
            MODULE_ID = #{moduleId}
            AND USE_YN = 'Y'
        ORDER BY 
            REG_DTTM DESC
    </select>

    <!-- 파일 목록 조회 -->
    <select id="selectHtmlEditorFileList" parameterType="HtmlEditorFileVO" resultType="HtmlEditorFileVO">
        SELECT 
            FILE_ID,
            MODULE_ID,
            ORIGINAL_FILE_NM,
            STORED_FILE_NM,
            FILE_PATH,
            FILE_SIZE,
            FILE_EXT,
            FILE_TYPE,
            USE_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR,
            CASE
                WHEN FILE_SIZE > 1048576 THEN CONCAT(ROUND(FILE_SIZE/1048576, 2), ' MB')
                WHEN FILE_SIZE > 1024 THEN CONCAT(ROUND(FILE_SIZE/1024, 2), ' KB')
                ELSE CONCAT(FILE_SIZE, ' bytes')
            END AS FILE_SIZE_STR
        FROM 
            tb_common_html_editor_file
        WHERE 
            1=1
            <if test="moduleId != null and moduleId != ''">
                AND MODULE_ID = #{moduleId}
            </if>
            <if test="fileType != null and fileType != ''">
                AND FILE_TYPE = #{fileType}
            </if>
            <if test="searchKeyword != null and searchKeyword != ''">
                <choose>
                    <when test="searchCondition == 'originalFileNm'">
                        AND ORIGINAL_FILE_NM LIKE CONCAT('%', #{searchKeyword}, '%')
                    </when>
                    <when test="searchCondition == 'fileExt'">
                        AND FILE_EXT LIKE CONCAT('%', #{searchKeyword}, '%')
                    </when>
                    <otherwise>
                        AND (
                            ORIGINAL_FILE_NM LIKE CONCAT('%', #{searchKeyword}, '%')
                            OR FILE_EXT LIKE CONCAT('%', #{searchKeyword}, '%')
                        )
                    </otherwise>
                </choose>
            </if>
            <if test="searchUseYn != null and searchUseYn != ''">
                AND USE_YN = #{searchUseYn}
            </if>
        ORDER BY 
            REG_DTTM DESC
        <if test="pagingYn != null and pagingYn.toString() == 'Y'.toString()">
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 파일 목록 총 개수 조회 -->
    <select id="selectHtmlEditorFileListTotalCount" parameterType="HtmlEditorFileVO" resultType="int">
        SELECT 
            COUNT(*)
        FROM 
            tb_common_html_editor_file
        WHERE 
            1=1
            <if test="moduleId != null and moduleId != ''">
                AND MODULE_ID = #{moduleId}
            </if>
            <if test="fileType != null and fileType != ''">
                AND FILE_TYPE = #{fileType}
            </if>
            <if test="searchKeyword != null and searchKeyword != ''">
                <choose>
                    <when test="searchCondition == 'originalFileNm'">
                        AND ORIGINAL_FILE_NM LIKE CONCAT('%', #{searchKeyword}, '%')
                    </when>
                    <when test="searchCondition == 'fileExt'">
                        AND FILE_EXT LIKE CONCAT('%', #{searchKeyword}, '%')
                    </when>
                    <otherwise>
                        AND (
                            ORIGINAL_FILE_NM LIKE CONCAT('%', #{searchKeyword}, '%')
                            OR FILE_EXT LIKE CONCAT('%', #{searchKeyword}, '%')
                        )
                    </otherwise>
                </choose>
            </if>
            <if test="searchUseYn != null and searchUseYn != ''">
                AND USE_YN = #{searchUseYn}
            </if>
    </select>

    <!-- 파일 ID 생성 -->
    <select id="generateFileId" resultType="String">
        SELECT CONCAT('HEDF', LPAD(NEXTVAL(seq_html_edit_file_id), 16, '0'))
    </select>

    <!-- 파일 정보 등록 -->
    <insert id="insertHtmlEditorFile" parameterType="HtmlEditorFileVO">
        INSERT INTO tb_common_html_editor_file (
            FILE_ID,
            MODULE_ID,
            ORIGINAL_FILE_NM,
            STORED_FILE_NM,
            FILE_PATH,
            FILE_SIZE,
            FILE_EXT,
            FILE_TYPE,
            USE_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR
        ) VALUES (
            #{fileId},
            #{moduleId},
            #{originalFileNm},
            #{storedFileNm},
            #{filePath},
            #{fileSize},
            #{fileExt},
            #{fileType},
            #{useYn},
            NOW(),
            #{rgtr},
            NOW(),
            #{mdfr}
        )
    </insert>

    <!-- 파일 정보 수정 -->
    <update id="updateHtmlEditorFile" parameterType="HtmlEditorFileVO">
        UPDATE 
            tb_common_html_editor_file
        SET 
            <if test="moduleId != null">
                MODULE_ID = #{moduleId},
            </if>
            <if test="originalFileNm != null">
                ORIGINAL_FILE_NM = #{originalFileNm},
            </if>
            <if test="fileType != null">
                FILE_TYPE = #{fileType},
            </if>
            <if test="useYn != null">
                USE_YN = #{useYn},
            </if>
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE 
            FILE_ID = #{fileId}
    </update>

    <!-- 파일 정보 삭제 -->
    <delete id="deleteHtmlEditorFile" parameterType="String">
        DELETE FROM 
            tb_common_html_editor_file
        WHERE 
            FILE_ID = #{fileId}
    </delete>

    <!-- 모듈에 첨부된 파일 정보 삭제 -->
    <delete id="deleteHtmlEditorFileByModuleId" parameterType="String">
        DELETE FROM 
            tb_common_html_editor_file
        WHERE 
            MODULE_ID = #{moduleId}
    </delete>

</mapper>
