<?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.TagBindingMapper">

  <resultMap id="bindingMap" type="com.zioinfo.esn.domain.TagBindingVo">
    <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="deviceId"       column="device_id"/>
    <result property="productCode"    column="product_code"/>
    <result property="productName"    column="product_name"/>
    <result property="locationCode"   column="location_code"/>
    <result property="templateCode"   column="template_code"/>
    <result property="displayData"    column="display_data"/>
    <result property="lastPrice"      column="last_price"/>
    <result property="lastUpdatedAt"  column="last_updated_at"/>
    <result property="bindStatus"     column="bind_status"/>
    <result property="deviceStatus"   column="device_status"/>
    <result property="batteryLevel"   column="battery_level"/>
    <result property="signalStrength" column="signal_strength"/>
    <result property="createdAt"      column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="bindingMap">
    SELECT b.id, b.tenant_code, b.store_id, s.store_name,
           b.device_id, b.product_code, p.product_name,
           b.location_code, b.template_code, b.display_data,
           b.last_price, b.last_updated_at, b.bind_status,
           h.status AS device_status, h.battery_level, h.signal_strength,
           b.created_at
    FROM esn_tag_binding b
    LEFT JOIN esn_store s         ON b.store_id = s.id
    LEFT JOIN esn_product p       ON b.store_id = p.store_id AND b.product_code = p.product_code
    LEFT JOIN esn_hcore_device h  ON b.device_id = h.device_id
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND b.tenant_code = #{tenantCode}</if>
      <if test="storeId != null">AND b.store_id = #{storeId}</if>
      <if test="bindStatus != null and bindStatus != ''">AND b.bind_status = #{bindStatus}</if>
      <if test="keyword != null and keyword != ''">
        AND (b.device_id ILIKE '%'||#{keyword}||'%'
          OR b.product_code ILIKE '%'||#{keyword}||'%'
          OR b.location_code ILIKE '%'||#{keyword}||'%'
          OR p.product_name ILIKE '%'||#{keyword}||'%')
      </if>
    </where>
    ORDER BY b.store_id, b.location_code
  </select>

  <select id="findById" resultMap="bindingMap">
    SELECT b.id, b.tenant_code, b.store_id, s.store_name,
           b.device_id, b.product_code, p.product_name,
           b.location_code, b.template_code, b.display_data,
           b.last_price, b.last_updated_at, b.bind_status,
           h.status AS device_status, h.battery_level, h.signal_strength,
           b.created_at
    FROM esn_tag_binding b
    LEFT JOIN esn_store s        ON b.store_id = s.id
    LEFT JOIN esn_product p      ON b.store_id = p.store_id AND b.product_code = p.product_code
    LEFT JOIN esn_hcore_device h ON b.device_id = h.device_id
    WHERE b.id = #{id}
  </select>

  <select id="findByDeviceId" resultMap="bindingMap">
    SELECT b.id, b.tenant_code, b.store_id, s.store_name,
           b.device_id, b.product_code, p.product_name,
           b.location_code, b.template_code, b.display_data,
           b.last_price, b.last_updated_at, b.bind_status,
           h.status AS device_status, h.battery_level, h.signal_strength,
           b.created_at
    FROM esn_tag_binding b
    LEFT JOIN esn_store s        ON b.store_id = s.id
    LEFT JOIN esn_product p      ON b.store_id = p.store_id AND b.product_code = p.product_code
    LEFT JOIN esn_hcore_device h ON b.device_id = h.device_id
    WHERE b.device_id = #{deviceId}
  </select>

  <select id="findByProductCode" resultMap="bindingMap">
    SELECT b.id, b.tenant_code, b.store_id, s.store_name,
           b.device_id, b.product_code, p.product_name,
           b.location_code, b.template_code, b.display_data,
           b.last_price, b.last_updated_at, b.bind_status,
           h.status AS device_status, h.battery_level, h.signal_strength,
           b.created_at
    FROM esn_tag_binding b
    LEFT JOIN esn_store s        ON b.store_id = s.id
    LEFT JOIN esn_product p      ON b.store_id = p.store_id AND b.product_code = p.product_code
    LEFT JOIN esn_hcore_device h ON b.device_id = h.device_id
    WHERE b.tenant_code = #{tenantCode}
      AND b.store_id = #{storeId}
      AND b.product_code = #{productCode}
      AND b.bind_status = 'ACTIVE'
  </select>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO esn_tag_binding
      (tenant_code, store_id, device_id, product_code, location_code,
       template_code, last_price, bind_status)
    VALUES
      (#{tenantCode}, #{storeId}, #{deviceId}, #{productCode}, #{locationCode},
       #{templateCode}, #{lastPrice}, 'ACTIVE')
  </insert>

  <update id="update">
    UPDATE esn_tag_binding SET
      product_code    = #{productCode},
      location_code   = #{locationCode},
      template_code   = #{templateCode},
      display_data    = #{displayData},
      last_price      = #{lastPrice},
      last_updated_at = NOW(),
      bind_status     = #{bindStatus}
    WHERE id = #{id}
  </update>

  <update id="unbind">
    UPDATE esn_tag_binding SET
      bind_status   = 'UNBOUND',
      product_code  = NULL,
      last_updated_at = NOW()
    WHERE id = #{id}
  </update>

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

  <select id="countAll" resultType="long">
    SELECT COUNT(*) FROM esn_tag_binding
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
    </where>
  </select>

  <select id="countActive" resultType="long">
    SELECT COUNT(*) FROM esn_tag_binding WHERE bind_status = 'ACTIVE'
    <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
  </select>

</mapper>
