<?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.backendsys.mapper.B2c.B2cGoodSpecSkuMapper">

    <sql id="includeGoodsSpecSku">
        id,
        id good_spec_sku_id,
        good_id,
        spec_ids,
        price,
        stock,
        is_stock
    </sql>

    <sql id="includeGoodsSpecSkuBatch">
        sku.id id,
        sku.id good_spec_sku_id,

        sku.good_id good_id,
        g.good_name good_name,

        sku.spec_ids spec_ids,
        GROUP_CONCAT(gs.spec_name SEPARATOR ',') AS spec_names,
        GROUP_CONCAT(gs.spec_value SEPARATOR ',') AS spec_values,

        sku.price price,
        sku.stock stock,
        sku.is_stock is_stock
    </sql>

    <resultMap id="resultMapGoodsSpecSku" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="good_id" column="good_id" javaType="java.lang.Long"/>
        <result property="price" column="price" javaType="java.math.BigDecimal"/>
        <result property="good_spec_sku_id" column="good_spec_sku_id" javaType="java.lang.Long"/>
        <result property="stock" column="stock" javaType="java.lang.Integer"/>
        <result property="is_stock" column="is_stock" javaType="java.lang.Integer"/>
        <result property="spec_ids" column="spec_ids" />
    </resultMap>

    <resultMap id="resultMapGoodsSpecSkuBatch" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="good_id" column="good_id" javaType="java.lang.Long"/>
        <result property="good_name" column="good_name" />
        <result property="good_spec_sku_id" column="good_spec_sku_id" javaType="java.lang.Long"/>
        <result property="price" column="price" javaType="java.math.BigDecimal"/>
        <result property="stock" column="stock" javaType="java.lang.Integer"/>
        <result property="is_stock" column="is_stock" javaType="java.lang.Integer"/>
        <result property="spec_ids" column="spec_ids" />
        <result property="spec_names" column="spec_names" />
        <result property="spec_values" column="spec_values" />
    </resultMap>

    <select id="queryGoodSpecSkuList" resultMap="resultMapGoodsSpecSku">
        SELECT
        <include refid="includeGoodsSpecSku" />
        FROM b2c_goods_spec_sku
        WHERE good_id = #{good_id}
--         ORDER BY spec_ids
    </select>

    <!-- 批量获得规格信息 (包含 规格详情、商品详情) -->
    <select id="queryGoodSpecSkuBatch" resultMap="resultMapGoodsSpecSkuBatch">
        SELECT
        <include refid="includeGoodsSpecSkuBatch" />
        FROM b2c_goods_spec_sku sku
            LEFT JOIN b2c_goods g ON g.id = sku.good_id
            LEFT JOIN b2c_goods_spec gs ON FIND_IN_SET(gs.id, sku.spec_ids) > 0
        WHERE sku.id IN
        <foreach collection="sku_ids" item="sku_id" open="(" separator="," close=")">
            #{sku_id}
        </foreach>
        GROUP BY sku.id, sku.good_id, g.good_name, sku.spec_ids, sku.price, sku.stock, sku.is_stock
    </select>


    <select id="queryGoodSpecSkuDetail" resultMap="resultMapGoodsSpecSku">
        SELECT
        <include refid="includeGoodsSpecSku" />
        FROM b2c_goods_spec_sku
        WHERE id = #{good_spec_sku_id}
    </select>

    <insert id="insertGoodSpecSku" parameterType="com.backendsys.entity.B2c.B2cGoodSpecSkuDTO"
        useGeneratedKeys="true" keyProperty="good_spec_sku_id">
        INSERT INTO b2c_goods_spec_sku (good_id, spec_ids, price, stock, is_stock)
        VALUES (#{good_id}, #{spec_ids}, #{price}, #{stock}, #{is_stock})
    </insert>

    <update id="updateGoodSpecSku" parameterType="com.backendsys.entity.B2c.B2cGoodSpecSkuDTO"
        useGeneratedKeys="true" keyProperty="good_spec_sku_id">
        UPDATE b2c_goods_spec_sku
        SET good_id = #{good_id}, spec_ids = #{spec_ids}, price = #{price}, stock = #{stock}, is_stock = #{is_stock}
        WHERE id = #{good_spec_sku_id}
    </update>

    <delete id="deleteGoodSpecSku">
        DELETE FROM b2c_goods_spec_sku WHERE id = #{good_spec_sku_id}
    </delete>

</mapper>