<?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.tasteby.mapper.MemoMapper">

    <resultMap id="memoResultMap" type="com.tasteby.domain.Memo">
        <id property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="restaurantId" column="restaurant_id"/>
        <result property="rating" column="rating"/>
        <result property="memoText" column="memo_text" typeHandler="com.tasteby.config.ClobTypeHandler"/>
        <result property="visitedAt" column="visited_at"/>
        <result property="createdAt" column="created_at"/>
        <result property="updatedAt" column="updated_at"/>
        <result property="restaurantName" column="restaurant_name"/>
    </resultMap>

    <select id="findByUserAndRestaurant" resultMap="memoResultMap">
        SELECT id, user_id, restaurant_id, rating, memo_text,
               visited_at, created_at, updated_at
        FROM user_memos
        WHERE user_id = #{userId} AND restaurant_id = #{restaurantId}
    </select>

    <insert id="insertMemo">
        INSERT INTO user_memos (id, user_id, restaurant_id, rating, memo_text, visited_at)
        VALUES (#{id}, #{userId}, #{restaurantId}, #{rating}, #{memoText},
                <choose>
                    <when test="visitedAt != null">TO_DATE(#{visitedAt}, 'YYYY-MM-DD')</when>
                    <otherwise>NULL</otherwise>
                </choose>)
    </insert>

    <update id="updateMemo">
        UPDATE user_memos SET
            rating = #{rating},
            memo_text = #{memoText},
            visited_at = <choose>
                <when test="visitedAt != null">TO_DATE(#{visitedAt}, 'YYYY-MM-DD')</when>
                <otherwise>NULL</otherwise>
            </choose>,
            updated_at = SYSTIMESTAMP
        WHERE user_id = #{userId} AND restaurant_id = #{restaurantId}
    </update>

    <delete id="deleteMemo">
        DELETE FROM user_memos WHERE user_id = #{userId} AND restaurant_id = #{restaurantId}
    </delete>

    <select id="findByUser" resultMap="memoResultMap">
        SELECT m.id, m.user_id, m.restaurant_id, m.rating, m.memo_text,
               m.visited_at, m.created_at, m.updated_at,
               r.name AS restaurant_name
        FROM user_memos m
        LEFT JOIN restaurants r ON r.id = m.restaurant_id
        WHERE m.user_id = #{userId}
        ORDER BY m.updated_at DESC
    </select>

</mapper>
