<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mall.product.mapper.ProductMapper">

  <sql id="searchWhere">
    <where>
      <if test="categoryId != null">AND category_id = #{categoryId}</if>
      <if test="keyword != null and keyword != ''">AND (name ILIKE '%' || #{keyword} || '%' OR brand ILIKE '%' || #{keyword} || '%' OR description ILIKE '%' || #{keyword} || '%')</if>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="occasion != null and occasion != ''">AND occasion = #{occasion}</if>
      <if test="flowerType != null and flowerType != ''">AND flower_type = #{flowerType}</if>
      <if test="maxPrice != null">AND COALESCE(sale_price, price) &lt;= #{maxPrice}</if>
    </where>
  </sql>

  <select id="search" resultType="com.zioinfo.mall.product.MallProduct">
    SELECT * FROM mall_product
    <include refid="searchWhere"/>
    ORDER BY
    <choose>
      <when test="sort == 'price_asc'">COALESCE(sale_price, price) ASC</when>
      <when test="sort == 'price_desc'">COALESCE(sale_price, price) DESC</when>
      <when test="sort == 'sales'">sales_count DESC</when>
      <when test="sort == 'rating'">rating_avg DESC</when>
      <otherwise>id DESC</otherwise>
    </choose>
    LIMIT #{limit} OFFSET #{offset}
  </select>

  <select id="countSearch" resultType="int">
    SELECT COUNT(*) FROM mall_product <include refid="searchWhere"/>
  </select>

  <select id="findById" resultType="com.zioinfo.mall.product.MallProduct">SELECT * FROM mall_product WHERE id = #{id}</select>

  <insert id="insert" parameterType="com.zioinfo.mall.product.MallProduct" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_product (category_id, sku, name, brand, description, price, sale_price, status, stock, thumbnail,
                              occasion, flower_type, shelf_life_days)
    VALUES (#{categoryId}, #{sku}, #{name}, #{brand}, #{description}, #{price}, #{salePrice},
            COALESCE(#{status},'ON_SALE'), COALESCE(#{stock},0), #{thumbnail},
            #{occasion}, #{flowerType}, COALESCE(#{shelfLifeDays},5))
  </insert>

  <update id="update">
    UPDATE mall_product SET category_id=#{categoryId}, name=#{name}, brand=#{brand}, description=#{description},
      price=#{price}, sale_price=#{salePrice}, status=COALESCE(#{status},status), stock=COALESCE(#{stock},stock),
      thumbnail=#{thumbnail}, occasion=COALESCE(#{occasion},occasion), flower_type=COALESCE(#{flowerType},flower_type),
      shelf_life_days=COALESCE(#{shelfLifeDays},shelf_life_days) WHERE id=#{id}
  </update>
  <update id="updateStatus">UPDATE mall_product SET status=#{status} WHERE id=#{id}</update>
  <update id="incSales">UPDATE mall_product SET sales_count = COALESCE(sales_count,0) + #{qty}, stock = GREATEST(COALESCE(stock,0) - #{qty}, 0) WHERE id=#{id}</update>
  <delete id="delete">DELETE FROM mall_product WHERE id=#{id}</delete>

  <select id="findOptions" resultType="com.zioinfo.mall.product.MallProductOption">
    SELECT * FROM mall_product_option WHERE product_id = #{productId} ORDER BY id
  </select>
  <insert id="insertOption" parameterType="com.zioinfo.mall.product.MallProductOption" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_product_option (product_id, option_name, option_value, extra_price, stock, sku_suffix, option_type)
    VALUES (#{productId}, #{optionName}, #{optionValue}, COALESCE(#{extraPrice},0), COALESCE(#{stock},0), #{skuSuffix}, #{optionType})
  </insert>
  <delete id="deleteOptions">DELETE FROM mall_product_option WHERE product_id = #{productId}</delete>

  <select id="findSizes" resultType="com.zioinfo.mall.product.MallProductSize">
    SELECT * FROM mall_product_size WHERE product_id = #{productId} ORDER BY sort_order, id
  </select>
  <insert id="insertSize" parameterType="com.zioinfo.mall.product.MallProductSize" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_product_size (product_id, size_code, label, price, stem_count, sort_order)
    VALUES (#{productId}, #{sizeCode}, #{label}, #{price}, #{stemCount}, COALESCE(#{sortOrder},0))
  </insert>
  <delete id="deleteSizes">DELETE FROM mall_product_size WHERE product_id = #{productId}</delete>

  <select id="findImages" resultType="com.zioinfo.mall.product.MallProductImage">
    SELECT * FROM mall_product_image WHERE product_id = #{productId} ORDER BY sort_order, id
  </select>
  <insert id="insertImage" parameterType="com.zioinfo.mall.product.MallProductImage" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_product_image (product_id, url, sort_order, is_main)
    VALUES (#{productId}, #{url}, COALESCE(#{sortOrder},0), COALESCE(#{isMain},FALSE))
  </insert>
  <delete id="deleteImages">DELETE FROM mall_product_image WHERE product_id = #{productId}</delete>

</mapper>
