<?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.attachment.AttachmentMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.attachment.AttachmentVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.attachment.AttachmentResp"/>

    <sql id="joinCols">
        a.attachment_id, a.ref_type, a.ref_id,
        a.file_name, a.file_path, a.file_size, a.mime_type,
        a.uploaded_by, u.user_name AS uploader_name,
        a.uploaded_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM attachment a
        LEFT JOIN users u ON u.user_id = a.uploaded_by
        WHERE a.attachment_id = #{attachmentId}
    </select>

    <!-- ref_type + ref_id 기준 첨부 목록 — 인덱스 idx_att_ref 활용 -->
    <select id="selectByRef" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM attachment a
        LEFT JOIN users u ON u.user_id = a.uploaded_by
        WHERE a.ref_type = #{refType}
          AND a.ref_id   = #{refId}
        ORDER BY a.uploaded_at DESC
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM attachment a
        LEFT JOIN users u ON u.user_id = a.uploaded_by
        <where>
            <if test="refType != null and refType != ''">AND a.ref_type = #{refType}</if>
            <if test="refId != null">AND a.ref_id = #{refId}</if>
            <if test="uploadedBy != null">AND a.uploaded_by = #{uploadedBy}</if>
        </where>
        ORDER BY a.uploaded_at DESC, a.attachment_id DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="attachmentId">
        INSERT INTO attachment (ref_type, ref_id, file_name, file_path, file_size, mime_type, uploaded_by)
        VALUES (#{refType}, #{refId}, #{fileName}, #{filePath}, #{fileSize}, #{mimeType}, #{uploadedBy})
    </insert>

    <delete id="delete">
        DELETE FROM attachment WHERE attachment_id = #{attachmentId}
    </delete>

    <delete id="deleteByRef">
        DELETE FROM attachment WHERE ref_type = #{refType} AND ref_id = #{refId}
    </delete>

</mapper>
