<?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.czg.service.product.mapper.ProductMapper">
    <sql id="productQuery">
        SELECT t1.id,
               t1.days,
               t1.name,
               t1.sort,
               t1.type,
               t1.is_del,
               t1.is_hot,
               t1.images,
               t1.is_sale,
               t1.shop_id,
               t1.spec_id,
               t1.unit_id,
               t1.weight,
               t1.end_time,
               t1.is_stock,
               t1.pack_fee,
               t1.cover_img,
               t1.warn_line,
               t1.group_snap,
               t1.group_type,
               t1.start_time,
               t1.category_id,
               t1.create_time,
               t1.short_title,
               t1.update_time,
               t1.is_sold_stock,
               t1.stock_number,
               t1.is_refund_stock,
               t1.select_spec_info,
               t1.group_category_id,
               t1.is_allow_temp_modify_price,
               t2.name      AS unit_name,
               t3.name      AS category_name,
               t4.name      AS spec_name,
               t4.full_name AS spec_full_name
        FROM tb_product t1
                 LEFT JOIN tb_shop_prod_unit t2 ON t2.id = t1.unit_id
                 LEFT JOIN tb_shop_prod_category t3 ON t3.id = t1.category_id
                 LEFT JOIN tb_shop_prod_spec t4 ON t4.id = t1.spec_id
    </sql>
    <sql id="productQueryWhere">
        <where>
            and t1.shop_id = #{shopId}
            and t1.is_del = 0
            <if test="id != null">
                and t1.id = #{id}
            </if>
            <if test="name != null and name != ''">
                and t1.name like concat('%', #{name}, '%')
            </if>
            <if test="categoryId != null">
                and t1.category_id = #{categoryId}
            </if>
            <if test="specId != null">
                and t1.spec_id = #{specId}
            </if>
            <if test="createBeginTime != null and createBeginTime != ''">
                and t1.name >= #{createBeginTime}
            </if>
            <if test="createEndTime != null and createEndTime != ''">
                <![CDATA[
                and t1.name <= #{createEndTime}
                ]]>
            </if>
        </where>
    </sql>
    <sql id="shopProductQuery">
        select t1.id,
               t1.name,
               t1.short_title,
               t2.sku_id,
               t2.origin_price,
               t2.sale_price,
               t2.member_price,
               t1.cover_img,
               t1.images,
               t3.name                    as unit_name,
               t1.is_sold_stock,
               t1.sync_id as syncId,
               t1.stock_number,
               t1.type,
               t1.group_type,
               t1.days,
               t1.start_time,
               t1.end_time,
               t1.is_hot,
               ifnull(t2.suit_num, 1)     as suit_num,
               t1.select_spec_info,
               t1.group_snap,
               t1.pack_fee,
               ifnull(t4.sales_volume, 0) as sales_volume,
               t1.shop_id,
               t1.is_stock
        from tb_product t1
                 left join (select x.product_id,
                                   x.id              as sku_id,
                                   MIN(x.sale_price) as sale_price,
                                   x.origin_price,
                                   x.member_price,
                                   x.suit_num
                            from tb_prod_sku x
                            where x.is_del = 0
                              and x.is_grounding = 1
                              and x.shop_id = #{shopId}
                            group by x.product_id) t2 on t1.id = t2.product_id
                 left join tb_shop_prod_unit t3 on t1.unit_id = t3.id
                 left join (select product_id, sum(num) as sales_volume
                            from tb_order_detail
                            where pay_amount is not null
                            group by product_id) t4 on t1.id = t4.product_id
    </sql>
    <select id="selectProductPage" resultType="com.czg.product.dto.ProductDTO">
        <include refid="productQuery"/>
        ${qwSql}
        limit ${pageOffset}, ${pageSize}
    </select>
    <select id="selectProductPage_COUNT" resultType="long">
        select count(*)
        from tb_product t1 ${qwSql}
    </select>
    <select id="selectProductList" resultType="com.czg.product.dto.ProductDTO">
        <include refid="productQuery"/>
        ${qwSql}
    </select>

    <select id="selectHotsProductList" resultType="com.czg.product.vo.ShopProductVo">
        <include refid="shopProductQuery"/>
        <where>
            and t1.is_del = 0
            and t1.is_sale = 1
            and t1.is_hot = 1
            and t2.sale_price is not null
            and t1.shop_id = #{shopId}
        </where>
        order by t1.sort desc,t1.id desc
    </select>
    <select id="selectGroupProductList" resultType="com.czg.product.vo.ShopProductVo">
        <include refid="shopProductQuery"/>
        <where>
            and t1.is_del = 0
            and t1.is_sale = 1
            and t2.sale_price is not null
            and t1.shop_id = #{shopId}
        </where>
        order by t1.sort desc,t1.id desc
    </select>
    <select id="selectOneProductInfo" resultType="com.czg.product.vo.ShopProductInfoVo">
        <include refid="shopProductQuery"/>
        <where>
            and t1.is_del = 0
            and t1.is_sale = 1
            and t2.sale_price is not null
            and t1.shop_id = #{shopId}
            and t1.id = #{id}
        </where>
    </select>

    <select id="selectCouponProBySaleNum" resultType="com.czg.product.vo.RecommendProVO">
        SELECT a.name,
               b.origin_price,
               b.sale_price,
               a.cover_img,
               a.id,
               b.real_sales_number                          saleNum,
               round(b.sale_price / b.origin_price, 2) * 10 discount
        FROM tb_product AS a
                 LEFT JOIN tb_prod_sku AS b ON a.id = b.product_id
        WHERE a.type = 'coupon'
        ORDER BY b.real_sales_number
        LIMIT 2

    </select>
    <select id="selectRecommendProductList" resultType="com.czg.product.vo.RecommendProVO">
        select ifnull(
        ROUND(ST_Distance_Sphere(POINT(b.lng, b.lat), POINT(#{lng}, #{lat})) / 1000, 2), 0) AS distance,
        c.name, a.origin_price, a.sale_price, b.shop_name, b.districts, c.cover_img, b.logo, c.id,
        a.real_sales_number saleNum, (round(a.sale_price / a.origin_price, 2) * 10) AS discount,
        c.id productId, a.id skuId, b.id shopId
        from tb_prod_sku as a
        left join tb_product c on c.id=a.product_id
        left join tb_shop_info as b on a.shop_id=b.id
        where c.id is not null and c.group_category_id is not null and c.group_category_id != '[]'
        <if test="address != null and address != ''">
            and (b.cities like concat('%', #{address}, '%') or b.districts like concat('%', #{address}, '%'))
        </if>

        <if test="name != null and name != ''">
            and c.name like concat('%', #{name}, '%')
        </if>
        <if test="classify != null">
            and c.group_category_id like concat('%', #{classify}, '%')
        </if>
        <if test="distanceType != null">
            and ST_Distance_Sphere(POINT(b.lng, b.lat), POINT(#{lng}, #{lat})) &lt;= #{distanceType}
        </if>

        <choose>
            <when test="orderType == 0">
                ORDER BY distance
            </when>
            <when test="orderType == 1">
                ORDER BY a.real_sales_number DESC
            </when>
            <when test="orderType == 2">
                ORDER BY a.sale_price
            </when>
            <when test="orderType == 3">
                ORDER BY c.create_time DESC
            </when>
            <otherwise>
                ORDER BY c.create_time DESC  <!-- 默认排序，避免 orderType 为空时报错 -->
            </otherwise>
        </choose>

    </select>
    <select id="getTodayProInfo" resultType="com.czg.product.vo.RecommendProVO">
        select 1000 AS                                      distance,
               c.name,
               a.origin_price,
               a.sale_price,
               b.shop_name,
               b.districts,
               c.cover_img,
               b.logo,
               c.id,
               a.real_sales_number                          saleNum,
               round(a.sale_price / a.origin_price, 2) * 10 discount,
               c.id                                         productId,
               a.id                                         skuId,
               b.id                                         shopId
        from tb_prod_sku as a
                 left join tb_product c on c.id = a.product_id
                 left join tb_shop_info as b on a.shop_id = b.id
        where c.group_category_id is not null
          and c.group_category_id != '[]'
          and c.create_time >= date_format(now(), '%Y-%m-%d 00:00:00')
    </select>
    <select id="getShopName" resultType="java.lang.String">
        select shop_name
        from tb_shop_info
        where id = #{shopId}
    </select>
    <select id="productList" resultType="com.czg.product.vo.ProductVO">
        select a.*, min(b.sale_price) as lowPrice, min(b.member_price) as lowMemberPrice, unit.name as unitName
        from tb_product as a
        left join tb_prod_sku as b on a.id = b.product_id
        left join tb_shop_prod_unit unit on a.unit_id=unit.id
        where a.shop_id = #{shopId}
        <if test="productIds != null">
            and a.id in
            <foreach collection="productIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        GROUP BY a.id
    </select>
    <update id="updateProductStockNum">
        update tb_product
        <if test="type == 'add'">
            set stock_number = stock_number + #{num}
        </if>
        <if test="type == 'sub'">
            set stock_number = stock_number - #{num}
        </if>
        where id = #{id}
        and is_stock = 1
        and shop_id = #{shopId}
    </update>
</mapper>
