<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.edu.ecnu.stu.bookstore.mapper.BookMapper">
    <insert id="insertTags">
        insert into t_book_tag(book_id, tag) values
        <foreach collection="tags" item="tag" separator=",">
            (#{book_id}, #{tag})
        </foreach>
    </insert>

    <insert id="insertPictures">
        insert into t_book_picture(book_id, picture) values
        <foreach collection="pictures" item="picture">
            (#{book_id}, #{picture})
        </foreach>
    </insert>
    <insert id="insert">
        insert into t_book(book_id, store_id, title, author, publisher,
            original_title, translator, pub_year, pages, price, binding, isbn
            <if test="book.stockLevel != null">
                , stock_level
            </if>
            )
            values (#{book.bookId}, #{book.storeId}, #{book.title}, #{book.author}, #{book.publisher}, #{book.originalTitle},
            #{book.translator}, #{book.pubYear}, #{book.pages}, #{book.price}, #{book.binding}, #{book.isbn}
            <if test="book.stockLevel != null">
                , #{book.stockLevel}
            </if>
            )
    </insert>

    <select id="batchSelect" resultType="cn.edu.ecnu.stu.bookstore.pojo.Book">
        select book_id, store_id, title, author, publisher, original_title, translator, pub_year, pages, price, binding, isbn, stock_level from t_book
            where store_id = #{storeId} and book_id in (
            <foreach collection="books" item="id" separator=",">
                #{id}
            </foreach>
            )
    </select>

    <resultMap id="bookResultMap" type="cn.edu.ecnu.stu.bookstore.pojo.Book">
        <id property="bookId" column="book_id"/>
        <result property="storeId" column="store_id"/>
        <result property="author" column="author"/>
        <result property="publisher" column="publisher"/>
        <result property="originalTitle" column="original_title"/>
        <result property="translator" column="translator"/>
        <result property="pages" column="pages"/>
        <result property="price" column="price"/>
        <result property="binding" column="binding"/>
        <result property="isbn" column="isbn"/>
        <result property="stockLevel" column="stock_level"/>
        <result property="authorIntro" column="author_intro"/>
        <result property="content" column="content"/>
        <result property="bookIntro" column="book_intro"/>
        <collection property="tags" ofType="String" select="selectTags" column="book_id"/>
        <collection property="pictures" ofType="String" select="selectPictures" column="book_id"/>
    </resultMap>

    <select id="selectTags" resultType="String">
        select tag from t_book_tag where book_id = #{book_id}
    </select>

    <select id="selectPictures" resultType="String">
        select picture from t_book_picture where book_id = #{book_id}
    </select>

    <select id="getBookByTitle" resultMap="bookResultMap">
        select b1.book_id, b1.store_id, b1.title, b1.author, b1.publisher, b1.original_title, b1.translator, b1.pub_year,
               b1.pages, b1.price, b1.binding, b1.isbn, b1.stock_level, b2.book_intro, b2.author_intro, b2.content
        from t_book b1
        join t_book_detail b2 on b1.book_id = b2.book_id
        where b1.title like "%"#{title}"%"
        <if test="storeId != null">
            and b1.store_id = #{storeId}
        </if>
        limit #{start}, #{size}
    </select>
    <select id="getBookByAuthor" resultMap="bookResultMap">
        select b1.book_id, b1.store_id, b1.title, b1.author, b1.publisher, b1.original_title, b1.translator, b1.pub_year,
        b1.pages, b1.price, b1.binding, b1.isbn, b1.stock_level, b2.book_intro, b2.author_intro, b2.content
        from t_book b1
        join t_book_detail b2 on b1.book_id = b2.book_id
        where b1.author = #{author}
        <if test="storeId != null">
            and b1.store_id = #{storeId}
        </if>
        limit #{start}, #{size}
    </select>

    <resultMap id="bookByTagResultMap" type="cn.edu.ecnu.stu.bookstore.pojo.Book">
        <id property="bookId" column="book_id"/>
        <result property="storeId" column="store_id"/>
        <result property="author" column="author"/>
        <result property="publisher" column="publisher"/>
        <result property="originalTitle" column="original_title"/>
        <result property="translator" column="translator"/>
        <result property="pages" column="pages"/>
        <result property="price" column="price"/>
        <result property="binding" column="binding"/>
        <result property="isbn" column="isbn"/>
        <result property="stockLevel" column="stock_level"/>
        <result property="authorIntro" column="author_intro"/>
        <result property="content" column="content"/>
        <result property="bookIntro" column="book_intro"/>
        <collection property="tags" ofType="String">
            <constructor><arg column="tag"/> </constructor>
        </collection>
        <collection property="pictures" ofType="String" select="selectPictures" column="book_id"/>
    </resultMap>
    <select id="getBookByTag" resultMap="bookByTagResultMap">
        select b1.book_id, b1.store_id, b1.title, b1.author, b1.publisher, b1.original_title, b1.translator, b1.pub_year,
        b1.pages, b1.price, b1.binding, b1.isbn, b1.stock_level, b2.book_intro, b2.author_intro, b2.content, t.tag
        from t_book_tag t
        join t_book b1 on t.book_id = b1.book_id
        join t_book_detail b2 on t.book_id = b2.book_id
        where t.tag = #{tag}
        <if test="storeId != null">
            and b1.store_id = #{storeId}
        </if>
        limit #{start}, #{size}
    </select>
    <select id="getBookByContent" resultMap="bookResultMap">
        select b1.book_id, b1.store_id, b1.title, b1.author, b1.publisher, b1.original_title, b1.translator, b1.pub_year,
        b1.pages, b1.price, b1.binding, b1.isbn, b1.stock_level, b2.book_intro, b2.author_intro, b2.content
        from t_book_detail b2
        join t_book b1 on b2.book_id = b1.book_id
        where match(author_intro, book_intro, content) against(#{content})
        <if test="storeId != null">
            and b1.store_id = #{storeId}
        </if>
        limit #{start}, #{size}
    </select>
</mapper>