<?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">

<!-- UIWS 이식: 업무일지 (tb_uiws_worklog / _dtl / _cmt). -->
<mapper namespace="com.zioinfo.esn.uiws.worklog.mapper.WorklogMapper">

    <!-- ===================== worklog 헤더 ===================== -->
    <insert id="insertWorklog" useGeneratedKeys="true" keyProperty="worklogId" keyColumn="worklog_id"
            parameterType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        INSERT INTO tb_uiws_worklog
            (title, writer_id, work_date, work_status_cd, progress_cd, repeat_yn,
             repeat_start_date, repeat_end_date, created_by, created_at)
        VALUES
            (#{title}, #{writerId}, #{workDate}, #{workStatusCd}, #{progressCd}, #{repeatYn},
             #{repeatStartDate}, #{repeatEndDate}, #{createdBy}, #{createdAt})
    </insert>

    <update id="updateWorklog" parameterType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        UPDATE tb_uiws_worklog SET
            title = #{title}, writer_id = #{writerId}, work_date = #{workDate},
            work_status_cd = #{workStatusCd}, progress_cd = #{progressCd}, repeat_yn = #{repeatYn},
            repeat_start_date = #{repeatStartDate}, repeat_end_date = #{repeatEndDate},
            updated_by = #{updatedBy}, updated_at = #{updatedAt}
        WHERE worklog_id = #{worklogId}
    </update>

    <select id="findWorklogById" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        SELECT * FROM tb_uiws_worklog WHERE worklog_id = #{worklogId}
    </select>

    <select id="existsWorklogById" resultType="boolean">
        SELECT EXISTS(SELECT 1 FROM tb_uiws_worklog WHERE worklog_id = #{worklogId})
    </select>

    <delete id="deleteWorklogById">
        DELETE FROM tb_uiws_worklog WHERE worklog_id = #{worklogId}
    </delete>

    <sql id="scope">
        <if test="!scopeAll">
            AND writer_id IN
            <foreach collection="ownerIds" item="oid" open="(" separator="," close=")">#{oid}</foreach>
        </if>
    </sql>

    <select id="searchList" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        SELECT * FROM tb_uiws_worklog
        WHERE work_date BETWEEN #{from} AND #{to}
        <if test="writerId != null">AND writer_id = #{writerId}</if>
        <if test="progressCd != null">AND progress_cd = #{progressCd}</if>
        <include refid="scope"/>
        ORDER BY work_date DESC, worklog_id DESC
        LIMIT #{limit} OFFSET #{offset}
    </select>

    <select id="countList" resultType="long">
        SELECT COUNT(*) FROM tb_uiws_worklog
        WHERE work_date BETWEEN #{from} AND #{to}
        <if test="writerId != null">AND writer_id = #{writerId}</if>
        <if test="progressCd != null">AND progress_cd = #{progressCd}</if>
        <include refid="scope"/>
    </select>

    <select id="findByMonth" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        SELECT * FROM tb_uiws_worklog
        WHERE work_date BETWEEN #{from} AND #{to}
        <if test="writerId != null">AND writer_id = #{writerId}</if>
        <include refid="scope"/>
        ORDER BY work_date ASC, worklog_id ASC
    </select>

    <select id="searchPopup" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        SELECT * FROM tb_uiws_worklog
        WHERE 1=1
        <if test="keyword != null">AND title LIKE '%' || #{keyword} || '%'</if>
        <if test="writerId != null">AND writer_id = #{writerId}</if>
        <include refid="scope"/>
        ORDER BY work_date DESC, worklog_id DESC
        LIMIT 50
    </select>

    <select id="progressSummary" resultType="map">
        SELECT progress_cd AS "progressCd", COUNT(*) AS "cnt"
        FROM tb_uiws_worklog
        WHERE work_date BETWEEN #{from} AND #{to}
        <include refid="scope"/>
        GROUP BY progress_cd
    </select>

    <!-- ===================== 상세(dtl) ===================== -->
    <insert id="insertDtl" useGeneratedKeys="true" keyProperty="dtlId" keyColumn="dtl_id"
            parameterType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogDtl">
        INSERT INTO tb_uiws_worklog_dtl
            (worklog_id, start_hour, end_hour, work_type_cd, company_id, work_content, issue_content,
             sort_ord, created_by, created_at)
        VALUES
            (#{worklogId}, #{startHour}, #{endHour}, #{workTypeCd}, #{companyId}, #{workContent}, #{issueContent},
             #{sortOrd}, #{createdBy}, #{createdAt})
    </insert>

    <update id="updateDtl" parameterType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogDtl">
        UPDATE tb_uiws_worklog_dtl SET
            start_hour = #{startHour}, end_hour = #{endHour}, work_type_cd = #{workTypeCd},
            company_id = #{companyId}, work_content = #{workContent}, issue_content = #{issueContent},
            sort_ord = #{sortOrd}, updated_by = #{updatedBy}, updated_at = #{updatedAt}
        WHERE dtl_id = #{dtlId}
    </update>

    <delete id="deleteDtlById">
        DELETE FROM tb_uiws_worklog_dtl WHERE dtl_id = #{dtlId}
    </delete>

    <select id="findDtlByWorklogId" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogDtl">
        SELECT * FROM tb_uiws_worklog_dtl
        WHERE worklog_id = #{worklogId}
        ORDER BY sort_ord ASC, start_hour ASC, dtl_id ASC
    </select>

    <!-- ===================== 댓글(cmt) ===================== -->
    <insert id="insertCmt" useGeneratedKeys="true" keyProperty="cmtId" keyColumn="cmt_id"
            parameterType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogCmt">
        INSERT INTO tb_uiws_worklog_cmt
            (worklog_id, cmt_content, writer_id, kakao_sent_yn, confirm_yn, created_by, created_at)
        VALUES
            (#{worklogId}, #{cmtContent}, #{writerId}, #{kakaoSentYn}, #{confirmYn}, #{createdBy}, #{createdAt})
    </insert>

    <select id="findCmtById" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogCmt">
        SELECT * FROM tb_uiws_worklog_cmt WHERE cmt_id = #{cmtId}
    </select>

    <update id="updateCmtKakaoSent">
        UPDATE tb_uiws_worklog_cmt SET kakao_sent_yn = 'Y' WHERE cmt_id = #{cmtId}
    </update>

    <update id="confirmCmt">
        UPDATE tb_uiws_worklog_cmt
        SET confirm_yn = 'Y', updated_by = #{actor}, updated_at = now()
        WHERE cmt_id = #{cmtId}
    </update>

    <select id="findCmtByWorklogId" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogCmt">
        SELECT * FROM tb_uiws_worklog_cmt
        WHERE worklog_id = #{worklogId}
        ORDER BY created_at ASC, cmt_id ASC
    </select>

    <select id="countCommentsByWorklogIds" resultType="map">
        SELECT worklog_id AS "worklogId", COUNT(*) AS "cnt"
        FROM tb_uiws_worklog_cmt
        WHERE worklog_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
        GROUP BY worklog_id
    </select>

    <select id="findUnconfirmedByWriter" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklogCmt">
        SELECT * FROM tb_uiws_worklog_cmt
        WHERE writer_id = #{writerId} AND confirm_yn = 'N'
        ORDER BY created_at DESC, cmt_id DESC
    </select>

    <!-- ===================== 사용자 라벨/이메일(코어 user 재사용) ===================== -->
    <select id="findUserNames" resultType="map">
        SELECT username AS "username", username AS "fullName"
        FROM esn_user
        WHERE username IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </select>

    <select id="findUserEmail" resultType="string">
        SELECT email FROM esn_user WHERE username = #{username}
    </select>

    <select id="findWorklogsByIds" resultType="com.zioinfo.esn.uiws.worklog.model.UiwsWorklog">
        SELECT * FROM tb_uiws_worklog
        WHERE worklog_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </select>

</mapper>
