<?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.zioinfo.esn.mapper.ProductMapper">

  <resultMap id="productMap" type="com.zioinfo.esn.domain.ProductVo">
    <id property="id"           column="id"/>
    <result property="tenantCode"   column="tenant_code"/>
    <result property="storeId"      column="store_id"/>
    <result property="storeName"    column="store_name"/>
    <result property="productCode"  column="product_code"/>
    <result property="productName"  column="product_name"/>
    <result property="category"     column="category"/>
    <result property="price"        column="price"/>
    <result property="salePrice"    column="sale_price"/>
    <result property="currency"     column="currency"/>
    <result property="active"       column="is_active"/>
    <result property="updatedAt"    column="updated_at"/>
    <result property="createdAt"    column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="productMap">
    SELECT p.id, p.tenant_code, p.store_id, s.store_name, p.product_code, p.product_name,
           p.category, p.price, p.sale_price, p.currency, p.is_active, p.updated_at, p.created_at
    FROM esn_product p
    LEFT JOIN esn_store s ON p.store_id = s.id
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND p.tenant_code = #{tenantCode}</if>
      <if test="storeId != null">AND p.store_id = #{storeId}</if>
      <if test="keyword != null and keyword != ''">
        AND (p.product_code ILIKE '%' || #{keyword} || '%' OR p.product_name ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY p.product_code LIMIT 500
  </select>

  <select id="findById" resultMap="productMap">
    SELECT p.id, p.tenant_code, p.store_id, s.store_name, p.product_code, p.product_name,
           p.category, p.price, p.sale_price, p.currency, p.is_active, p.updated_at, p.created_at
    FROM esn_product p
    LEFT JOIN esn_store s ON p.store_id = s.id
    WHERE p.id = #{id}
  </select>

  <select id="findByCode" resultMap="productMap">
    SELECT p.id, p.tenant_code, p.store_id, s.store_name, p.product_code, p.product_name,
           p.category, p.price, p.sale_price, p.currency, p.is_active, p.updated_at, p.created_at
    FROM esn_product p
    LEFT JOIN esn_store s ON p.store_id = s.id
    WHERE p.product_code = #{productCode} AND p.store_id = #{storeId}
  </select>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO esn_product (tenant_code, store_id, product_code, product_name,
                              category, price, sale_price, currency, is_active)
    VALUES (#{tenantCode}, #{storeId}, #{productCode}, #{productName},
            #{category}, #{price}, #{salePrice}, #{currency}, #{active})
  </insert>

  <update id="update">
    UPDATE esn_product SET
      product_name = #{productName},
      category     = #{category},
      price        = #{price},
      sale_price   = #{salePrice},
      currency     = #{currency},
      is_active    = #{active},
      updated_at   = NOW()
    WHERE id = #{id}
  </update>

  <delete id="delete">DELETE FROM esn_product WHERE id = #{id}</delete>

</mapper>
